Table Files

Parameters

Supported Operations

Examples

SSIS

Use Google Drive Connector in API Source component to read data or in API Destination component to read/write data:

Read from Files table using API Source

Google Drive

Files

Read/write to Files table using API Destination

Google Drive

Files

Select

ODBC application

Use these SQL queries in your ODBC application data source:

List / search files from my drive

List or search files from my drive (use search query expression). Uncomment Query part to invoke filter

SELECT * FROM Files
WITH(
	--Query='name  contains "ZappySys*.*"', --includes files where name contains "zappysys" word
)

List / search files from a single shared drive

List or search files from a specific shared drive by driveId and search query expression. Uncomment Query part to invoke filter

SELECT * FROM Files
WITH(
	--Query='name  contains "ZappySys*.*"', --includes files where name contains "zappysys" word
	DriveId='0AFWIggplk2z6Uk9PVA', 
	DriveType='drive',
	SupportsAllDrives='true',
	IncludeItemsFromAllDrives='true'
)

List / search files from all shared drives

List or search files from all shared drives by driveId and search query expression. Uncomment Query part to invoke filter

SELECT * FROM Files
WITH(
	--Query='name  contains "ZappySys*.*"', --includes files where name contains "zappysys" word
	DriveType='allDrives',
	SupportsAllDrives='true',
	IncludeItemsFromAllDrives='true'
)

Get file list

Downloads a file

SELECT * FROM Files

Update a file

Updates a file

UPDATE Files
  WITH (Id='1EVzXFVBQIh3LcJvfcLhI1UWhW3qUxFkM'
  ,DiskFilePath='C:\MyFiles\myfile.ext')
  --You can get file Id by selecting from 'list_files' endpoint

Delete a file

Deletes a file

DELETE FROM Files
  WITH (Id='1gcu0hV34OTfgvcu9CFhr7Go4b35j5Mop')
  --You can get file Id by selecting from 'list_files' endpoint

Delete a file by name from a shared drive

Delete a file by name from a shared drive

DELETE FROM Files where Name='dump.csv' and Trashed=0 WITH(DriveId='0AC00HWQH144cUk9PVA', DriveType='drive')
  --You can get file Id by selecting from 'list_files' endpoint

Upload a file with INSERT keyword

Upload a file with INSERT keyword. Advantage of INSERT is you can use Bulk option using SOURCE clause. This way you can upload many files

INSERT INTO Files
  WITH (FileName='My filename'
      ,DiskFilePath='C:\MyFolder\Mymyfile.ext'
      ,ParentFolderId='root'
	  )
  --You can get ParentFolderId by selecting from 'list_folders' endpoint or just use 'root'

SQL Server

Use these SQL queries in SQL Server after you create a data source in Data Gateway:

List / search files from my drive

List or search files from my drive (use search query expression). Uncomment Query part to invoke filter

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Files
WITH(
	--Query=''name  contains "ZappySys*.*"'', --includes files where name contains "zappysys" word
)';

EXEC (@MyQuery) AT [LINKED_SERVER_TO_GOOGLE_DRIVE_IN_DATA_GATEWAY];

List / search files from a single shared drive

List or search files from a specific shared drive by driveId and search query expression. Uncomment Query part to invoke filter

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Files
WITH(
	--Query=''name  contains "ZappySys*.*"'', --includes files where name contains "zappysys" word
	DriveId=''0AFWIggplk2z6Uk9PVA'', 
	DriveType=''drive'',
	SupportsAllDrives=''true'',
	IncludeItemsFromAllDrives=''true''
)';

EXEC (@MyQuery) AT [LINKED_SERVER_TO_GOOGLE_DRIVE_IN_DATA_GATEWAY];

List / search files from all shared drives

List or search files from all shared drives by driveId and search query expression. Uncomment Query part to invoke filter

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Files
WITH(
	--Query=''name  contains "ZappySys*.*"'', --includes files where name contains "zappysys" word
	DriveType=''allDrives'',
	SupportsAllDrives=''true'',
	IncludeItemsFromAllDrives=''true''
)';

EXEC (@MyQuery) AT [LINKED_SERVER_TO_GOOGLE_DRIVE_IN_DATA_GATEWAY];

Get file list

Downloads a file

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Files';

EXEC (@MyQuery) AT [LINKED_SERVER_TO_GOOGLE_DRIVE_IN_DATA_GATEWAY];

Update a file

Updates a file

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE Files
  WITH (Id=''1EVzXFVBQIh3LcJvfcLhI1UWhW3qUxFkM''
  ,DiskFilePath=''C:\MyFiles\myfile.ext'')
  --You can get file Id by selecting from ''list_files'' endpoint';

EXEC (@MyQuery) AT [LINKED_SERVER_TO_GOOGLE_DRIVE_IN_DATA_GATEWAY];

Delete a file

Deletes a file

DECLARE @MyQuery NVARCHAR(MAX) = 'DELETE FROM Files
  WITH (Id=''1gcu0hV34OTfgvcu9CFhr7Go4b35j5Mop'')
  --You can get file Id by selecting from ''list_files'' endpoint';

EXEC (@MyQuery) AT [LINKED_SERVER_TO_GOOGLE_DRIVE_IN_DATA_GATEWAY];

Delete a file by name from a shared drive

Delete a file by name from a shared drive

DECLARE @MyQuery NVARCHAR(MAX) = 'DELETE FROM Files where Name=''dump.csv'' and Trashed=0 WITH(DriveId=''0AC00HWQH144cUk9PVA'', DriveType=''drive'')
  --You can get file Id by selecting from ''list_files'' endpoint';

EXEC (@MyQuery) AT [LINKED_SERVER_TO_GOOGLE_DRIVE_IN_DATA_GATEWAY];

Upload a file with INSERT keyword

Upload a file with INSERT keyword. Advantage of INSERT is you can use Bulk option using SOURCE clause. This way you can upload many files

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO Files
  WITH (FileName=''My filename''
      ,DiskFilePath=''C:\MyFolder\Mymyfile.ext''
      ,ParentFolderId=''root''
	  )
  --You can get ParentFolderId by selecting from ''list_folders'' endpoint or just use ''root''';

EXEC (@MyQuery) AT [LINKED_SERVER_TO_GOOGLE_DRIVE_IN_DATA_GATEWAY];