This page provides a guide to Establishing a Connection to Google Drive in CData Cloud, as well as information on the available resources, and a reference to the available connection properties.

Connecting to Google Drive

Establishing a Connection shows how to authenticate to Google Drive and configure any necessary connection properties to create a database in CData Cloud

Accessing Data from CData Cloud Services

Accessing data from Google Drive through the available standard services and CData Cloud administration is documented in further details in the CData Cloud Documentation.

Connect to Google Drive by selecting the corresponding icon in the Database tab. Required properties are listed under Settings. The Advanced tab lists connection properties that are not typically required.

Authenticating to Google Drive

The Cloud supports using user accounts and GCP instance accounts for authentication.

The following sections discuss the available authentication schemes for Google Drive:

• User Accounts (OAuth)
• Service Account (OAuthJWT)
• GCP Instance Account

User Accounts (OAuth)

AuthScheme must be set to OAuth in all user account flows.

Web Applications

When connecting via a Web application, you need to create and register a custom OAuth application with Google Drive. You can then use the Cloud to acquire and manage the OAuth token values. See Creating a Custom OAuth App for more information about custom applications.

Get an OAuth Access Token

Set the following connection properties to obtain the OAuthAccessToken:

• OAuthClientId: Set this to the Client Id in your application settings.
• OAuthClientSecret: Set this to the Client Secret in your application settings.

Then call stored procedures to complete the OAuth exchange:

1. Call the GetOAuthAuthorizationURL stored procedure. Set the CallbackURL input to the Callback URL you specified in your application settings. The stored procedure returns the URL to the OAuth endpoint.
2. Navigate to the URL that the stored procedure returned in Step 1. Log in to the custom OAuth application and authorize the web application. Once authenticated, the browser redirects you to the callback URL.
3. Call the GetOAuthAccessToken stored procedure. Set AuthMode to WEB and the Verifier input to the "code" parameter in the query string of the callback URL.

Once you have obtained the access and refresh tokens, you can connect to data and refresh the OAuth access token either automatically or manually.

Automatic Refresh of the OAuth Access Token

To have the driver automatically refresh the OAuth access token, set the following on the first data connection:

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: Set this to the Client Id in your application settings.
• OAuthClientSecret: Set this to the Client Secret in your application settings.
• OAuthAccessToken: Set this to the access token returned by GetOAuthAccessToken.
• OAuthRefreshToken: Set this to the refresh token returned by GetOAuthAccessToken.
• OAuthSettingsLocation: Set this to the location where the Cloud saves the OAuth token values, which persist across connections.

On subsequent data connections, the values for OAuthAccessToken and OAuthRefreshToken are taken from OAuthSettingsLocation.

Manual Refresh of the OAuth Access Token

The only value needed to manually refresh the OAuth access token when connecting to data is the OAuth refresh token.

Use the RefreshOAuthAccessToken stored procedure to manually refresh the OAuthAccessToken after the ExpiresIn parameter value returned by GetOAuthAccessToken has elapsed, then set the following connection properties:

• OAuthClientId: Set this to the Client Id in your application settings.
• OAuthClientSecret: Set this to the Client Secret in your application settings.

Then call RefreshOAuthAccessToken with OAuthRefreshToken set to the OAuth refresh token returned by GetOAuthAccessToken. After the new tokens have been retrieved, open a new connection by setting the OAuthAccessToken property to the value returned by RefreshOAuthAccessToken.

Finally, store the OAuth refresh token so that you can use it to manually refresh the OAuth access token after it has expired.

Headless Machines

To configure the driver to use OAuth with a user account on a headless machine, you need to authenticate on another device that has an internet browser.

1. Choose one of two options:
   • Option 1: Obtain the OAuthVerifier value as described in "Obtain and Exchange a Verifier Code" below.
   • Option 2: Install the Cloud on a machine with an Internet browser and transfer the OAuth authentication values after you authenticate through the usual browser-based flow, as described in "Transfer OAuth Settings" below.
2. Then configure the Cloud to automatically refresh the access token on the headless machine.

Option 1: Obtain and Exchange a Verifier Code

To obtain a verifier code, you must authenticate at the OAuth authorization URL.

Follow the steps below to authenticate from the machine with an Internet browser and obtain the OAuthVerifier connection property.

1. Choose one of these options:
   • If you are using the Embedded OAuth Application click Google Drive OAuth endpoint to open the endpoint in your browser.
   • If you are using a custom OAuth application, create the Authorization URL by setting the following properties:
     • InitiateOAuth: Set to OFF.
     • OAuthClientId: Set to the client Id assigned when you registered your application.
     • OAuthClientSecret: Set to the client secret assigned when you registered your application.
     Then call the GetOAuthAuthorizationURL stored procedure with the appropriate CallbackURL. Open the URL returned by the stored procedure in a browser.
2. Log in and grant permissions to the Cloud. You are then redirected to the callback URL, which contains the verifier code.
3. Save the value of the verifier code. Later you will set this in the OAuthVerifier connection property.

Next, you need to exchange the OAuth verifier code for OAuth refresh and access tokens. Set the following properties:

On the headless machine, set the following connection properties to obtain the OAuth authentication values:

• InitiateOAuth: Set this to REFRESH.
• OAuthVerifier: Set this to the verifier code.
• OAuthClientId: (custom applications only) Set this to the Client Id in your custom OAuth application settings.
• OAuthClientSecret: (custom applications only) Set this to the Client Secret in the custom OAuth application settings.
• OAuthSettingsLocation: Set this to persist the encrypted OAuth authentication values to the specified location.

After the OAuth settings file is generated, you need to re-set the following properties to connect:

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: (custom applications only) Set this to the client Id assigned when you registered your application.
• OAuthClientSecret: (custom applications only) Set this to the client secret assigned when you registered your application.
• OAuthSettingsLocation: Set this to the location containing the encrypted OAuth authentication values. Make sure this location gives read and write permissions to the Cloud to enable the automatic refreshing of the access token.

Option 2: Transfer OAuth Settings

Prior to connecting on a headless machine, you need to create and install a connection with the driver on a device that supports an Internet browser. Set the connection properties as described in "Desktop Applications" above.

After completing the instructions in "Desktop Applications", the resulting authentication values are encrypted and written to the location specified by OAuthSettingsLocation. The default filename is OAuthSettings.txt.

Once you have successfully tested the connection, copy the OAuth settings file to your headless machine.

On the headless machine, set the following connection properties to connect to data:

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: (custom applications only) Set this to the client Id assigned when you registered your application.
• OAuthClientSecret: (custom applications only) Set this to the client secret assigned when you registered your application.
• OAuthSettingsLocation: Set this to the location of your OAuth settings file. Make sure this location gives read and write permissions to the Cloud to enable the automatic refreshing of the access token.

GCP Instance Accounts

When running on a GCP virtual machine, the Cloud can authenticate using a service account tied to the virtual machine. To use this mode, set AuthScheme to GCPInstanceAccount.

Creating a Custom OAuth Application

CData embeds OAuth Application Credentials with CData branding that can be used when connecting to Google Drive via a desktop application or a headless machine.

(For information on getting and setting the OAuthAccessToken and other configuration parameters, see the Desktop Authentication section of "Connecting to Google Drive".)

However, you must create a custom OAuth application to connect to Google Drive via the Web. And since custom OAuth applications seamlessly support all three commonly-used auth flows, you might want to create custom OAuth applications (use your own OAuth Application Credentials) for those auth flows anyway.

Custom OAuth applications are useful if you want to:

• control branding of the authentication dialog
• control the redirect URI that the application redirects the user to after the user authenticates
• customize the permissions that you are requesting from the user

The following sections describe how to enable the Directory API and create custom OAuth applications for user accounts (OAuth) and Service Accounts (OAuth/JWT).

Enable the Google Drive API

Follow these steps to enable the Google Drive API:

1. Navigate to the Google Cloud Console.
2. Select Library from the left-hand navigation menu. This opens the Library page.
3. In the search field, enter "Google Drive API" and select Google Drive API from the search results.
4. On the Google Drive API page, click ENABLE.

Create an OAuth Application

To create custom OAuth applications that retrieve the necessary OAuth connection properties, follow these procedures.

User Accounts (OAuth)

For users whose AuthScheme is OAuth and who need to authenticate over a web application, you must always create a custom OAuth application. (For desktop and headless flows, creating a custom OAuth application is optional.)

Do the following:

1. Navigate to the Google Cloud Console.
2. Create a new project or select an existing project.
3. At the left-hand navigation menu, select Credentials.
4. If this project does not already have a consent screen configured, click CONFIGURE CONSENT SCREEN to create one. If you are not using a Google Workspace account, you are restricted to creating an External-type Consent Screen, which requires specifying a support email and developer contact email. Additional info is optional.
5. On the Credentials page, select Create Credentials > OAuth Client ID.
6. In the Application Type menu, select Web application.
7. Specify a name for your custom OAuth application.
8. Under Authorized redirect URIs, click ADD URI and enter a redirect URI.
9. Click Enter, then CREATE. The Cloud Console returns you to the Credentials page.
   A window opens that displays your client Id and client secret.
10. Record the client Id and Client Secret for later use as the OAuthClientId and OAuthClientSecret connection properties.

Note: The client secret remains accessible from from the Google Cloud Console.

Service Accounts (OAuthJWT)

Service accounts (AuthScheme OAuthJWT) can be used in an OAuth flow to access Google APIs on behalf of users in a domain. A domain administrator can delegate domain-wide access to the service account.

To create a new service account:

1. Navigate to the Google Cloud Console.
2. Create a new project or select an existing project.
3. At the left-hand navigation menu, select Credentials.
4. Select Create Credentials > Service account.
5. On the Create service account page, enter the Service account name, the Service account ID, and, optionally, a description.
6. Click DONE. The Cloud Console redisplays the Credentials page.
7. In the Service Accounts section, select the service account you just created.
8. Click the KEYS tab.
9. Click ADD KEY > Create new key.
10. Select any supported Key type (see OAuthJWTCert and OAuthJWTCertType).
11. Click CREATE. The key is automatically downloaded to your device, and any additional information specific to the key is displayed.
12. Record the additional information for future use.

To complete the service account flow, generate a private key in the Google Cloud Console. In the service account flow, the driver exchanges a JSON Web token (JWT) for the OAuthAccessToken. The private key is required to sign the JWT. The driver will have the same permissions granted to the service account.

Customizing the SSL Configuration

By default, the Cloud attempts to negotiate TLS with the server. The server certificate is validated against the default system trusted certificate store. You can override how the certificate gets validated using the SSLServerCert connection property.

To specify another certificate, see the SSLServerCert connection property.

Connecting Through a Firewall or Proxy

HTTP Proxies

To authenticate to an HTTP proxy, set the following:

• ProxyServer: the hostname or IP address of the proxy server that you want to route HTTP traffic through.
• ProxyPort: the TCP port that the proxy server is running on.
• ProxyAuthScheme: the authentication method the Cloud uses when authenticating to the proxy server.
• ProxyUser: the username of a user account registered with the proxy server.
• ProxyPassword: the password associated with the ProxyUser.

Other Proxies

Set the following properties:

• To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
• To tunnel the connection, set FirewallType to TUNNEL.
• To authenticate, specify FirewallUser and FirewallPassword.
• To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.

The CData Cloud models Google Drive APIs as relational Tables, Views, and Stored Procedures. These are defined in schema files, which are simple, text-based configuration files. API limitations and requirements are documented in this section; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.

Tables

Tables describes the available tables.

Views

Views are tables that cannot be modified. Typically, data that are read-only and cannot be updated are shown as views.

Stored Procedures

Stored Procedures are function-like interfaces to the data source. They can be used to search, update, and modify information in the data source.

The Cloud models the data in Google Drive as a list of tables in a relational database that can be queried using standard SQL statements.

CData Cloud - Google Drive Tables

Name	Description
Drives	Create, delete, and query the available Drives for a specific user.
Files	Create, update, delete, and query the files and folders contained in a user's Google Drive.
Permissions	Create, update, delete, and query permissions for resources in a user's Google Drive.

Create, delete, and query the available Drives for a specific user.

Columns

Name	Type	ReadOnly	Description
Id [KEY]	String	True	The Id of the drive.
Name	String	False	The name of the drive.
Capabilities	String	True	This field describes the effective capabilities that the current user has for the drive.
CreatedTime	Datetime	True	The creation date of the drive.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
DomainAdminAccess	Boolean	If this field is set to true, then all Drives of the domain in which you are an administrator are returned.

Create, update, delete, and query the files and folders contained in a user's Google Drive.

Select

The Files table supports only a subset of columns for filtering. Below is a table containing those columns with their supported operations. All filters can be connected with 'OR' or 'AND' operators.

Column	Supported Operators
Name	contains, =, !=
MIMEType	contains, =, !=
ModifiedTime	<=, <, =, !=, >, >=
Trashed	=, !=
Starred	=, !=
ParentIds	in
OwnerEmail	in

The contains operator only performs prefix matching for a name. For example, the name "HelloWorld" would match for name contains 'Hello' but not name contains 'World'.

SELECT * FROM [Files] WHERE ModifiedTime > '2017-01-01' OR CONTAINS(Name, 'CData')

SELECT * FROM [Files] WHERE OwnerEmail IN ('[email protected]') AND Starred = true

SELECT * FROM [Files] WHERE Starred = true

SELECT * FROM [Files] WHERE DriveId = '0ACkq0ZiV0yJCUk9PVA'

Note: You must set the connection property SupportsAllDrives to 'true', in order to query from a specific Drive.

Insert

You must specify values at least for Name and one of LocalFile or FileData.

INSERT INTO Files (Name, LocalFile) VALUES ('MyFile', 'C:\\\\file.txt')

Update

Id is required for updating a File.

UPDATE Files SET Name = 'UpdatedName' WHERE Id = '19YFv8wmvKixCYaJJAeE8jN3ROt7x1ZicvXwflswVOrw'

Also the content of the file can be updated. Note that this will replace the actual content.

UPDATE Files SET LocalFile = 'C:\\\\file.txt' WHERE Id = '19YFv8wmvKixCYaJJAeE8jN3ROt7x1ZicvXwflswVOrw'

Delete

To delete a File, the Id is required.

DELETE FROM [Files] WHERE Id = '1Dx6GTyhgTmTjtoy8GuG0n0qaOsKyhwrOG6MG8A2QQYA'

Columns

Name	Type	ReadOnly	Description
Id [KEY]	String	True	The ID of the file.
Name	String	False	The name of the file. This is not necessarily unique within a folder. Note that for immutable items such as the top level folders of Team Drives, My Drive root folder, and Application Data folder the name is constant.
DriveId	String	True	The Id of the Drive.
Description	String	False	A short description of the file or folder.
Extension	String	True	The extension of the file.
MIMEType	String	False	The MIME type of the file.
CreatedTime	Datetime	True	The creation date of the file or folder.
ModifiedTime	Datetime	True	The last modified date of the file or folder.
Size	Long	True	The size of the file in bytes.
OwnerName	String	True	The name of the resource's owner.
OwnerEmail	String	True	The email of the resource's owner.
Folder	Boolean	True	This field shows whether or not the resource is a folder.
Starred	Boolean	False	This field sets whether or not the resource is starred.
Trashed	Boolean	True	This field sets whether or not the resource has been moved to the trash.
Viewed	Boolean	True	This field sets whether or not the resource has been viewed by the current user.
ParentIds	String	True	A comma-separated list of parent folder Ids.
ChildIds	String	True	A semicolon-separated list of child resource Ids.
ChildLinks	String	True	A semicolon-separated list of child resource links.
Query	String	True	This field accepts a valid Google Drive SDK query, which overrides conditionals in the WHERE clause.
LocalFile	String	False	The local file path, including file name, of the file to be uploaded. Required when FileData is not specified. Used only for inserting and updating a file.
FileData	String	False	If the LocalFile input is empty, file data will be output in the format specified by the Encoding input. Used only for inserting and updating a file.
Encoding	String	False	The FileData input encoding type. Used only for inserting and updating a file. The allowed values are NONE, BASE64. The default value is BASE64.

Create, update, delete, and query permissions for resources in a user's Google Drive.

Select

The ResourceId field must be specified to get data from this table. This is the only supported filter.

SELECT * FROM Permissions WHERE ResourceId = '0B5AH3NIqjXDKX3pJS3NncTZJa01'

Insert

To insert into Permissions, you must specify values at least for the ResourceId, Role, Type, and EmailAddress fields.

INSERT INTO Permissions (ResourceId, Role, Type, EmailAddress) VALUES ('0B5AH3NIqjXDKX3pJS3NncTZJa01', 'WRITER', 'USER', '[email protected]')

Update

The PermissionId and ResourceId fields are required for updating a Permission.

UPDATE Permissions SET Role = 'organizer' WHERE PermissionId = '3NIqjXDK' AND ResourceId = '0B5AH3NIqjXDKX3pJS3NncTZJa01'

Delete

To delete a Permission, the PermissionId and ResourceId fields are required.

Delete From Permissions WHERE PermissionId = '3NIqjXDK' AND ResourceId = '0B5AH3NIqjXDKX3pJS3NncTZJa01'

Columns

Name	Type	ReadOnly	Description
PermissionId [KEY]	String	True	The Id of the permission for the resource.
ResourceId [KEY]	String	True	The Id of the resource (a file or folder).
Role	String	False	The role specified for the permission. It can take only these values: 'READER', 'COMMENTER', 'WRITER', 'OWNER', 'FILE_ORGANIZER', 'ORGANIZER'.
Type	String	False	The type entity to which the permission applies. It can take only these values: 'USER', 'GROUP', 'DOMAIN', 'ANYONE'.
EmailAddress	String	False	The email address of the user or group to which this permission refers.
Domain	String	False	The domain to which this permission refers.
AllowFileDiscovery	Boolean	False	Whether the permission allows the file or folder to be discovered through search. This is only applicable for permissions of type 'DOMAIN' or 'ANYONE'. The default value for this field is 'false'

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

CData Cloud - Google Drive Views

Name	Description
Docs	Query the Google Docs contained in a user's Google Drive.
Folders	Query the folders contained in a user's Google Drive.
Photos	Query the photos contained in a user's Google Drive.
Sheets	Query the Google Sheets contained in a user's Google Drive.
Videos	Query the Google Videos contained in a user's Google Drive.

Query the Google Docs contained in a user's Google Drive.

Select

It is also possible to get all the docs from a Drive.
Note: You must set the connection property SupportsAllDrives to 'true', in order to query from a specific Drive.

SELECT * FROM Docs WHERE DriveId = '0ACkq0ZiV0yJCUk9PVA'

Columns

Name	Type	Description
Id [KEY]	String	The ID of the file.
Name	String	The name of the file. This is not necessarily unique within a folder. Note that for immutable items such as the top level folders of Team Drives, My Drive root folder, and Application Data folder the name is constant.
DriveId	String	The Id of the Drive.
Description	String	A short description of the file or folder.
Extension	String	The extension of the file.
CreatedTime	Datetime	The creation date of the file or folder.
ModifiedTime	Datetime	The last modified date of the file or folder.
Size	Long	The size of the file in bytes.
OwnerName	String	The name of the resource's owner.
OwnerEmail	String	The email of the resource's owner.
Starred	Boolean	This field sets whether or not the resource is starred.
Trashed	Boolean	This field sets whether or not the resource has been moved to the trash.
Viewed	Boolean	This field sets whether or not the resource has been viewed by the current user.
ParentIds	String	A comma-separated list of parent folder Ids.
ChildIds	String	A semicolon-separated list of child resource Ids.
ChildLinks	String	A semicolon-separated list of child resource links.
Query	String	This field accepts a valid Google Drive SDK query, which overrides conditionals in the WHERE clause.

Query the folders contained in a user's Google Drive.

Select

It is also possible to get all the folders from a Drive. You must set the connection property SupportsAllDrives to 'true', in order to query from a specific Drive.

SELECT * FROM Folders WHERE DriveId = '0ACkq0ZiV0yJCUk9PVA'

NOTE: specifying a large set of fields may result in lower performance, so we recommend that you restrict your query to only the columns you need for best results.

Columns

Name	Type	Description
Id [KEY]	String	The ID of the file.
Name	String	The name of the file. This is not necessarily unique within a folder. Note that for immutable items such as the top level folders of Team Drives, My Drive root folder, and Application Data folder the name is constant.
DriveId	String	The Id of the Drive.
Description	String	A short description of the file or folder.
CreatedTime	Datetime	The creation date of the file or folder.
ModifiedTime	Datetime	The last modified date of the file or folder.
Size	Long	The size of the file in bytes.
OwnerName	String	The name of the resource's owner.
OwnerEmail	String	The email of the resource's owner.
Starred	Boolean	This field sets whether or not the resource is starred.
Trashed	Boolean	This field sets whether or not the resource has been moved to the trash.
Viewed	Boolean	This field sets whether or not the resource has been viewed by the current user.
ParentIds	String	A comma-separated list of parent folder Ids.
ChildIds	String	A semicolon-separated list of child resource Ids.
ChildLinks	String	A semicolon-separated list of child resource links.
Query	String	This field accepts a valid Google Drive SDK query, which overrides conditionals in the WHERE clause.

Query the photos contained in a user's Google Drive.

Select

It is also possible to get all the photos from a Drive.
Note: You must set the connection property SupportsAllDrives to 'true', in order to query from a specific Drive.

SELECT * FROM Photos WHERE DriveId = '0ACkq0ZiV0yJCUk9PVA'

Columns

Name	Type	Description
Id [KEY]	String	The ID of the file.
Name	String	The name of the file. This is not necessarily unique within a folder. Note that for immutable items such as the top level folders of Team Drives, My Drive root folder, and Application Data folder the name is constant.
DriveId	String	The Id of the Drive.
Description	String	A short description of the file or folder.
Extension	String	The extension of the file.
CreatedTime	Datetime	The creation date of the file or folder.
ModifiedTime	Datetime	The last modified date of the file or folder.
Size	Long	The size of the file in bytes.
OwnerName	String	The name of the resource's owner.
OwnerEmail	String	The email of the resource's owner.
Starred	Boolean	This field sets whether or not the resource is starred.
Trashed	Boolean	This field sets whether or not the resource has been moved to the trash.
Viewed	Boolean	This field sets whether or not the resource has been viewed by the current user.
ParentIds	String	A comma-separated list of parent folder Ids.
ChildIds	String	A semicolon-separated list of child resource Ids.
ChildLinks	String	A semicolon-separated list of child resource links.
Query	String	This field accepts a valid Google Drive SDK query, which overrides conditionals in the WHERE clause.

Query the Google Sheets contained in a user's Google Drive.

Select

It is also possible to get all the sheets from a Drive.
Note: You must set the connection property SupportsAllDrives to 'true', in order to query from a specific Drive.

SELECT * FROM Sheets WHERE DriveId = '0ACkq0ZiV0yJCUk9PVA'

Columns

Name	Type	Description
Id [KEY]	String	The ID of the file.
Name	String	The name of the file. This is not necessarily unique within a folder. Note that for immutable items such as the top level folders of Team Drives, My Drive root folder, and Application Data folder the name is constant.
DriveId	String	The Id of the Drive.
Description	String	A short description of the file or folder.
Extension	String	The extension of the file.
CreatedTime	Datetime	The creation date of the file or folder.
ModifiedTime	Datetime	The last modified date of the file or folder.
Size	Long	The size of the file in bytes.
OwnerName	String	The name of the resource's owner.
OwnerEmail	String	The email of the resource's owner.
Starred	Boolean	This field sets whether or not the resource is starred.
Trashed	Boolean	This field sets whether or not the resource has been moved to the trash.
Viewed	Boolean	This field sets whether or not the resource has been viewed by the current user.
ParentIds	String	A comma-separated list of parent folder Ids.
ChildIds	String	A semicolon-separated list of child resource Ids.
ChildLinks	String	A semicolon-separated list of child resource links.
Query	String	This field accepts a valid Google Drive SDK query, which overrides conditionals in the WHERE clause.

Query the Google Videos contained in a user's Google Drive.

Select

It is also possible to get all the videos from a Drive.
Note: You must set the connection property SupportsAllDrives to 'true', in order to query from a specific Drive.

SELECT * FROM Videos WHERE DriveId = '0ACkq0ZiV0yJCUk9PVA'

Columns

Name	Type	Description
Id [KEY]	String	The ID of the file.
Name	String	The name of the file. This is not necessarily unique within a folder. Note that for immutable items such as the top level folders of Team Drives, My Drive root folder, and Application Data folder the name is constant.
DriveId	String	The Id of the Drive.
Description	String	A short description of the file or folder.
Extension	String	The extension of the file.
CreatedTime	Datetime	The creation date of the file or folder.
ModifiedTime	Datetime	The last modified date of the file or folder.
Size	Long	The size of the file in bytes.
OwnerName	String	The name of the resource's owner.
OwnerEmail	String	The email of the resource's owner.
Starred	Boolean	This field sets whether or not the resource is starred.
Trashed	Boolean	This field sets whether or not the resource has been moved to the trash.
Viewed	Boolean	This field sets whether or not the resource has been viewed by the current user.
ParentIds	String	A comma-separated list of parent folder Ids.
ChildIds	String	A semicolon-separated list of child resource Ids.
ChildLinks	String	A semicolon-separated list of child resource links.
Query	String	This field accepts a valid Google Drive SDK query, which overrides conditionals in the WHERE clause.

Stored procedures are function-like interfaces that extend the functionality of the Cloud beyond simple SELECT/INSERT/UPDATE/DELETE operations with Google Drive.

Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from Google Drive, along with an indication of whether the procedure succeeded or failed.

CData Cloud - Google Drive Stored Procedures

Name	Description
CopyResource	Copies a resource from the user's Google Drive to a specified location.
CreateFolder	Creates a folder in the user's Google Drive.
DeleteResource	Deletes a resource from the user's Google Drive.
EmptyTrash	Empties the user's trash.
GetAuthenticatedUserInfo	Provides information about the authenticated user.
MoveResource	Moves a resource to the specified list of parentIDs
RevokeToken	Revoke access given to an application.
StopWatchingResources	Stops receiving notifications for a particular channel before it expires. If successful, this method returns an empty response.
SubscribeToFileChanges	Sets up a notification channel to start watching for changes to a single File resource.
SubscribeToUserChanges	Sets up a notification channel to start watching for all Changes resources.
UpdateResource	Updates a resource in the user's Google Drive.

Copies a resource from the user's Google Drive to a specified location.

Input

Name	Type	Description
Id	String	The Id of the resource to be copied.
NewName	String	The NewName parameter defines the name of the copied file.
ParentIDs	String	The ParentIds parameter should be a comma-separated list of existing folder Ids to use as parents for the copied file. If left blank, the copied file will inherit the same parents of the original.
IgnoreDefaultVisibility	Boolean	Whether to ignore the domain's default visibility settings for the created file. Domain administrators can choose to make all uploaded files visible to the domain by default; this parameter bypasses that behavior for the request. Permissions are still inherited from parent folders. The default value is false.
IncludeLabels	String	A comma-separated list of IDs of labels to include in the labelInfo part of the response
IncludePermissionsForView	String	Specifies which additional view's permissions to include in the response. Only 'published' is supported.
KeepRevisionForever	Boolean	Whether to set the 'keepForever' field in the new head revision. This is only applicable to files with binary content in Google Drive. Only 200 revisions for the file can be kept forever. If the limit is reached, try deleting pinned revisions. The default value is false.
OCRLanguage	String	A language hint for OCR processing during image import (ISO 639-1 code).
SupportsAllDrives	Boolean	Whether the requesting application supports both My Drives and shared drives. The default value is false.

Result Set Columns

Name	Type	Description
CopiedFileID	String	The id of the copied file
Success	String	This value shows a boolean indication of whether the operation was successful or not.

Creates a folder in the user's Google Drive.

Input

Name	Type	Description
Name	String	The title for the folder.
Description	String	The description for the folder.
Starred	Boolean	This parameter sets whether or not the resource is starred. The default value is FALSE.
ParentIds	String	The comma-separated Ids of the parent folders for the new folder.

Result Set Columns

Name	Type	Description
Success	String	This parameter sets whether the operation was successful or not.
Id	String	The ID of the new folder.

Deletes a resource from the user's Google Drive.

Input

Name	Type	Description
Id	String	The Id of the resource to be deleted.

Result Set Columns

Name	Type	Description
Success	String	This value shows a boolean indication of whether the operation was successful or not.

Empties the user's trash.

Result Set Columns

Name	Type	Description
Success	String	This parameter sets whether the operation was successful or not.

Provides information about the authenticated user.

Result Set Columns

Name	Type	Description
Emails	String	Email list of the authenticated user.
Name	String	The name of the authenticated user.
PhotoUrl	String	The profile picture of the authenticated user.

Moves a resource to the specified list of parentIDs

Input

Name	Type	Description
Id	String	The Id of the resource to be deleted.
ParentIDs	String	A comma separated list of IDs to be used as parents. Note that resources within a shared drive must always have exactly one parent, and that only shared drive members with the 'file organizer' or 'organizer' roles can move files and folders within a shared drive.

Result Set Columns

Name	Type	Description
Success	String	This value shows a boolean indication of whether the operation was successful or not.

Revoke access given to an application.

Input

Name	Type	Description
TokenToRevoke	String	The token to revoke. The token can be an access token or a refresh token. If the token is an access token and it has a corresponding refresh token, the refresh token will also be revoked. Set this to 'thisToken' to revoke the current access token.

Result Set Columns

Name	Type	Description
Success	String	This value shows whether the operation was successful.

Stops receiving notifications for a particular channel before it expires. If successful, this method returns an empty response.

Input

Name	Type	Description
Id	String	A required property string that uniquely identifies this new notification channel within your project.
ResourceId	String	A required opaque ID that identifies the resource being watched on this channel.
Address	String	An optional property string set to the URL that listens and responds to notifications for this notification channel. This is your Webhook callback URL, and it must use HTTPS.
ChannelToken	String	An optional token property that specifies an arbitrary string value to use as a channel token.
Expiration	String	An optional property string set to a Unix timestamp (in ms) of the date and time when you want the Drive API to stop sending messages for this notification channel.
ResourceURI	String	An optional version-specific identifier for the watched resource.
Payload	Boolean	An optional boolean value to indicate whether payload is wanted.
AdditionalParameters	Object	An optional json object used for specifying additional parameters that controll delivery channel behavior. Example : {

Sets up a notification channel to start watching for changes to a single File resource.

Input

Name	Type	Description
FileId	String	Required. The file id to subscribe changes to.
SupportsAllDrives	Boolean	Whether the requesting application supports both My Drives and shared drives. The default value is false.
Id	String	A required property string that uniquely identifies this new notification channel within your project.
Address	String	A required property string set to the URL that listens and responds to notifications for this notification channel.
ChannelToken	String	An optional token property that specifies an arbitrary string value to use as a channel token.
Expiration	String	An optional property string set to a Unix timestamp (in ms) of the date and time when you want the Drive API to stop sending messages for this notification channel.
ResourceId	String	An optional opaque ID that identifies the resource being watched on this channel.
ResourceURI	String	An optional version-specific identifier for the watched resource.
Payload	Boolean	An optional boolean value to indicate whether payload is wanted.
AdditionalParameters	Object	Optional. A json object used for specifying additional parameters that controll delivery channel behavior. Example : {

Result Set Columns

Name	Type	Description
Kind	String	The id for the file which was uploaded or updated.
Id	String	ID you specified for this channel.
ResourceId	String	ID of the watched resource.
ResourceUri	String	Version-specific ID of the watched resource.
Token	String	Present only if one was provided.
Expiration	String	Actual expiration time as Unix timestamp (in ms), if applicable.

Sets up a notification channel to start watching for all Changes resources.

Input

Name	Type	Description
Id	String	Requried. A property string that uniquely identifies this new notification channel within your project.
Address	String	Requried. A property string set to the URL that listens and responds to notifications for this notification channel.
ChannelToken	String	A token property that specifies an arbitrary string value to use as a channel token.
Expiration	String	A property string set to a Unix timestamp (in ms) of the date and time when you want the Drive API to stop sending messages for this notification channel.
ResourceId	String	An opaque ID that identifies the resource being watched on this channel.
ResourceURI	String	A version-specific identifier for the watched resource.
Payload	Boolean	An optional boolean value to indicate whether payload is wanted.
AdditionalParameters	Object	Optional. A json object used for specifying additional parameters that controll delivery channel behavior. Example : {

Result Set Columns

Name	Type	Description
Kind	String	The id for the file which was uploaded or updated.
Id	String	ID you specified for this channel.
ResourceId	String	ID of the watched resource.
ResourceUri	String	Version-specific ID of the watched resource.
Token	String	Present only if one was provided.
Expiration	String	Actual expiration time as Unix timestamp (in ms), if applicable.

Updates a resource in the user's Google Drive.

Input

Name	Type	Description
Id	String	The Id of the resource to be updated.
Name	String	The NewName parameter defines the name of the updated file.
Description	String	A short description of the file or folder.
MIMEType	String	The MIME type of the file.
Starred	String	This field sets whether or not the resource is starred.
LocalFile	String	The local file path, including file name, of the file to be uploaded. Required when FileData is not specified.
FileData	String	If the LocalFile input is empty, file data will be output in the format specified by the Encoding input.
Encoding	String	The FileData input encoding type. Used only for inserting and updating a file. The allowed values are NONE, BASE64. The default value is BASE64.
CopyRequiresWriterPermission	Boolean	Whether the options to copy, print, or download this file, should be disabled for readers and commenters. The default value is FALSE.

Result Set Columns

Name	Type	Description
Success	String	This value shows a boolean indication of whether the operation was successful or not.

You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.

Schema Tables

The following tables return database metadata for Google Drive:

• sys_catalogs: Lists the available databases.
• sys_schemas: Lists the available schemas.
• sys_tables: Lists the available tables and views.
• sys_tablecolumns: Describes the columns of the available tables and views.
• sys_procedures: Describes the available stored procedures.
• sys_procedureparameters: Describes stored procedure parameters.
• sys_keycolumns: Describes the primary and foreign keys.
• sys_indexes: Describes the available indexes.

Data Source Tables

The following tables return information about how to connect to and query the data source:

• sys_connection_props: Returns information on the available connection properties.
• sys_sqlinfo: Describes the SELECT queries that the Cloud can offload to the data source.

Query Information Tables

The following table returns query statistics for data modification queries:

• sys_identity: Returns information about batch operations or single updates.

Lists the available databases.

The following query retrieves all databases determined by the connection string:

SELECT * FROM sys_catalogs

Columns

Name	Type	Description
CatalogName	String	The database name.

Lists the available schemas.

The following query retrieves all available schemas:

          SELECT * FROM sys_schemas
          

Columns

Name	Type	Description
CatalogName	String	The database name.
SchemaName	String	The schema name.

Lists the available tables.

The following query retrieves the available tables and views:

          SELECT * FROM sys_tables
          

Columns

Name	Type	Description
CatalogName	String	The database containing the table or view.
SchemaName	String	The schema containing the table or view.
TableName	String	The name of the table or view.
TableType	String	The table type (table or view).
Description	String	A description of the table or view.
IsUpdateable	Boolean	Whether the table can be updated.

Describes the columns of the available tables and views.

The following query returns the columns and data types for the Files table:

SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Files' 

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the table or view.
SchemaName	String	The schema containing the table or view.
TableName	String	The name of the table or view containing the column.
ColumnName	String	The column name.
DataTypeName	String	The data type name.
DataType	Int32	An integer indicating the data type. This value is determined at run time based on the environment.
Length	Int32	The storage size of the column.
DisplaySize	Int32	The designated column's normal maximum width in characters.
NumericPrecision	Int32	The maximum number of digits in numeric data. The column length in characters for character and date-time data.
NumericScale	Int32	The column scale or number of digits to the right of the decimal point.
IsNullable	Boolean	Whether the column can contain null.
Description	String	A brief description of the column.
Ordinal	Int32	The sequence number of the column.
IsAutoIncrement	String	Whether the column value is assigned in fixed increments.
IsGeneratedColumn	String	Whether the column is generated.
IsHidden	Boolean	Whether the column is hidden.
IsArray	Boolean	Whether the column is an array.
IsReadOnly	Boolean	Whether the column is read-only.
IsKey	Boolean	Indicates whether a field returned from sys_tablecolumns is the primary key of the table.

Lists the available stored procedures.

The following query retrieves the available stored procedures:

          SELECT * FROM sys_procedures
          

Columns

Name	Type	Description
CatalogName	String	The database containing the stored procedure.
SchemaName	String	The schema containing the stored procedure.
ProcedureName	String	The name of the stored procedure.
Description	String	A description of the stored procedure.
ProcedureType	String	The type of the procedure, such as PROCEDURE or FUNCTION.

Describes stored procedure parameters.

The following query returns information about all of the input parameters for the UploadFile stored procedure:

SELECT * FROM sys_procedureparameters WHERE ProcedureName='UploadFile' AND Direction=1 OR Direction=2

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the stored procedure.
SchemaName	String	The name of the schema containing the stored procedure.
ProcedureName	String	The name of the stored procedure containing the parameter.
ColumnName	String	The name of the stored procedure parameter.
Direction	Int32	An integer corresponding to the type of the parameter: input (1), input/output (2), or output(4). input/output type parameters can be both input and output parameters.
DataTypeName	String	The name of the data type.
DataType	Int32	An integer indicating the data type. This value is determined at run time based on the environment.
Length	Int32	The number of characters allowed for character data. The number of digits allowed for numeric data.
NumericPrecision	Int32	The maximum precision for numeric data. The column length in characters for character and date-time data.
NumericScale	Int32	The number of digits to the right of the decimal point in numeric data.
IsNullable	Boolean	Whether the parameter can contain null.
IsRequired	Boolean	Whether the parameter is required for execution of the procedure.
IsArray	Boolean	Whether the parameter is an array.
Description	String	The description of the parameter.
Ordinal	Int32	The index of the parameter.

Describes the primary and foreign keys.

The following query retrieves the primary key for the Files table:

         SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Files' 
          

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the key.
SchemaName	String	The name of the schema containing the key.
TableName	String	The name of the table containing the key.
ColumnName	String	The name of the key column.
IsKey	Boolean	Whether the column is a primary key in the table referenced in the TableName field.
IsForeignKey	Boolean	Whether the column is a foreign key referenced in the TableName field.
PrimaryKeyName	String	The name of the primary key.
ForeignKeyName	String	The name of the foreign key.
ReferencedCatalogName	String	The database containing the primary key.
ReferencedSchemaName	String	The schema containing the primary key.
ReferencedTableName	String	The table containing the primary key.
ReferencedColumnName	String	The column name of the primary key.

Describes the foreign keys.

The following query retrieves all foreign keys which refer to other tables:

         SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'
          

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the key.
SchemaName	String	The name of the schema containing the key.
TableName	String	The name of the table containing the key.
ColumnName	String	The name of the key column.
PrimaryKeyName	String	The name of the primary key.
ForeignKeyName	String	The name of the foreign key.
ReferencedCatalogName	String	The database containing the primary key.
ReferencedSchemaName	String	The schema containing the primary key.
ReferencedTableName	String	The table containing the primary key.
ReferencedColumnName	String	The column name of the primary key.
ForeignKeyType	String	Designates whether the foreign key is an import (points to other tables) or export (referenced from other tables) key.

Describes the primary keys.

The following query retrieves the primary keys from all tables and views:

         SELECT * FROM sys_primarykeys
          

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the key.
SchemaName	String	The name of the schema containing the key.
TableName	String	The name of the table containing the key.
ColumnName	String	The name of the key column.
KeySeq	String	The sequence number of the primary key.
KeyName	String	The name of the primary key.

Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.

The following query retrieves all indexes that are not primary keys:

          SELECT * FROM sys_indexes WHERE IsPrimary='false'
          

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the index.
SchemaName	String	The name of the schema containing the index.
TableName	String	The name of the table containing the index.
IndexName	String	The index name.
ColumnName	String	The name of the column associated with the index.
IsUnique	Boolean	True if the index is unique. False otherwise.
IsPrimary	Boolean	True if the index is a primary key. False otherwise.
Type	Int16	An integer value corresponding to the index type: statistic (0), clustered (1), hashed (2), or other (3).
SortOrder	String	The sort order: A for ascending or D for descending.
OrdinalPosition	Int16	The sequence number of the column in the index.

Returns information on the available connection properties and those set in the connection string.

The following query retrieves all connection properties that have been set in the connection string or set through a default value:

SELECT * FROM sys_connection_props WHERE Value <> ''

Columns

Name	Type	Description
Name	String	The name of the connection property.
ShortDescription	String	A brief description.
Type	String	The data type of the connection property.
Default	String	The default value if one is not explicitly set.
Values	String	A comma-separated list of possible values. A validation error is thrown if another value is specified.
Value	String	The value you set or a preconfigured default.
Required	Boolean	Whether the property is required to connect.
Category	String	The category of the connection property.
IsSessionProperty	String	Whether the property is a session property, used to save information about the current connection.
Sensitivity	String	The sensitivity level of the property. This informs whether the property is obfuscated in logging and authentication forms.
PropertyName	String	A camel-cased truncated form of the connection property name.
Ordinal	Int32	The index of the parameter.
CatOrdinal	Int32	The index of the parameter category.
Hierarchy	String	Shows dependent properties associated that need to be set alongside this one.
Visible	Boolean	Informs whether the property is visible in the connection UI.
ETC	String	Various miscellaneous information about the property.

Describes the SELECT query processing that the Cloud can offload to the data source.

See SQL Compliance for SQL syntax details.

Discovering the Data Source's SELECT Capabilities

Below is an example data set of SQL capabilities. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.

Name	Description	Possible Values
AGGREGATE_FUNCTIONS	Supported aggregation functions.	AVG, COUNT, MAX, MIN, SUM, DISTINCT
COUNT	Whether COUNT function is supported.	YES, NO
IDENTIFIER_QUOTE_OPEN_CHAR	The opening character used to escape an identifier.	[
IDENTIFIER_QUOTE_CLOSE_CHAR	The closing character used to escape an identifier.	]
SUPPORTED_OPERATORS	A list of supported SQL operators.	=, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR
GROUP_BY	Whether GROUP BY is supported, and, if so, the degree of support.	NO, NO_RELATION, EQUALS_SELECT, SQL_GB_COLLATE
OJ_CAPABILITIES	The supported varieties of outer joins supported.	NO, LEFT, RIGHT, FULL, INNER, NOT_ORDERED, ALL_COMPARISON_OPS
OUTER_JOINS	Whether outer joins are supported.	YES, NO
SUBQUERIES	Whether subqueries are supported, and, if so, the degree of support.	NO, COMPARISON, EXISTS, IN, CORRELATED_SUBQUERIES, QUANTIFIED
STRING_FUNCTIONS	Supported string functions.	LENGTH, CHAR, LOCATE, REPLACE, SUBSTRING, RTRIM, LTRIM, RIGHT, LEFT, UCASE, SPACE, SOUNDEX, LCASE, CONCAT, ASCII, REPEAT, OCTET, BIT, POSITION, INSERT, TRIM, UPPER, REGEXP, LOWER, DIFFERENCE, CHARACTER, SUBSTR, STR, REVERSE, PLAN, UUIDTOSTR, TRANSLATE, TRAILING, TO, STUFF, STRTOUUID, STRING, SPLIT, SORTKEY, SIMILAR, REPLICATE, PATINDEX, LPAD, LEN, LEADING, KEY, INSTR, INSERTSTR, HTML, GRAPHICAL, CONVERT, COLLATION, CHARINDEX, BYTE
NUMERIC_FUNCTIONS	Supported numeric functions.	ABS, ACOS, ASIN, ATAN, ATAN2, CEILING, COS, COT, EXP, FLOOR, LOG, MOD, SIGN, SIN, SQRT, TAN, PI, RAND, DEGREES, LOG10, POWER, RADIANS, ROUND, TRUNCATE
TIMEDATE_FUNCTIONS	Supported date/time functions.	NOW, CURDATE, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, MONTH, QUARTER, WEEK, YEAR, CURTIME, HOUR, MINUTE, SECOND, TIMESTAMPADD, TIMESTAMPDIFF, DAYNAME, MONTHNAME, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, EXTRACT
REPLICATION_SKIP_TABLES	Indicates tables skipped during replication.
REPLICATION_TIMECHECK_COLUMNS	A string array containing a list of columns which will be used to check for (in the given order) to use as a modified column during replication.
IDENTIFIER_PATTERN	String value indicating what string is valid for an identifier.
SUPPORT_TRANSACTION	Indicates if the provider supports transactions such as commit and rollback.	YES, NO
DIALECT	Indicates the SQL dialect to use.
KEY_PROPERTIES	Indicates the properties which identify the uniform database.
SUPPORTS_MULTIPLE_SCHEMAS	Indicates if multiple schemas may exist for the provider.	YES, NO
SUPPORTS_MULTIPLE_CATALOGS	Indicates if multiple catalogs may exist for the provider.	YES, NO
DATASYNCVERSION	The CData Data Sync version needed to access this driver.	Standard, Starter, Professional, Enterprise
DATASYNCCATEGORY	The CData Data Sync category of this driver.	Source, Destination, Cloud Destination
SUPPORTSENHANCEDSQL	Whether enhanced SQL functionality beyond what is offered by the API is supported.	TRUE, FALSE
SUPPORTS_BATCH_OPERATIONS	Whether batch operations are supported.	YES, NO
SQL_CAP	All supported SQL capabilities for this driver.	SELECT, INSERT, DELETE, UPDATE, TRANSACTIONS, ORDERBY, OAUTH, ASSIGNEDID, LIMIT, LIKE, BULKINSERT, COUNT, BULKDELETE, BULKUPDATE, GROUPBY, HAVING, AGGS, OFFSET, REPLICATE, COUNTDISTINCT, JOINS, DROP, CREATE, DISTINCT, INNERJOINS, SUBQUERIES, ALTER, MULTIPLESCHEMAS, GROUPBYNORELATION, OUTERJOINS, UNIONALL, UNION, UPSERT, GETDELETED, CROSSJOINS, GROUPBYCOLLATE, MULTIPLECATS, FULLOUTERJOIN, MERGE, JSONEXTRACT, BULKUPSERT, SUM, SUBQUERIESFULL, MIN, MAX, JOINSFULL, XMLEXTRACT, AVG, MULTISTATEMENTS, FOREIGNKEYS, CASE, LEFTJOINS, COMMAJOINS, WITH, LITERALS, RENAME, NESTEDTABLES, EXECUTE, BATCH, BASIC, INDEX
PREFERRED_CACHE_OPTIONS	A string value specifies the preferred cacheOptions.
ENABLE_EF_ADVANCED_QUERY	Indicates if the driver directly supports advanced queries coming from Entity Framework. If not, queries will be handled client side.	YES, NO
PSEUDO_COLUMNS	A string array indicating the available pseudo columns.
MERGE_ALWAYS	If the value is true, The Merge Mode is forcibly executed in Data Sync.	TRUE, FALSE
REPLICATION_MIN_DATE_QUERY	A select query to return the replicate start datetime.
REPLICATION_MIN_FUNCTION	Allows a provider to specify the formula name to use for executing a server side min.
REPLICATION_START_DATE	Allows a provider to specify a replicate startdate.
REPLICATION_MAX_DATE_QUERY	A select query to return the replicate end datetime.
REPLICATION_MAX_FUNCTION	Allows a provider to specify the formula name to use for executing a server side max.
IGNORE_INTERVALS_ON_INITIAL_REPLICATE	A list of tables which will skip dividing the replicate into chunks on the initial replicate.
CHECKCACHE_USE_PARENTID	Indicates whether the CheckCache statement should be done against the parent key column.	TRUE, FALSE
CREATE_SCHEMA_PROCEDURES	Indicates stored procedures that can be used for generating schema files.

The following query retrieves the operators that can be used in the WHERE clause:

SELECT * FROM sys_sqlinfo WHERE Name = 'SUPPORTED_OPERATORS'

Note that individual tables may have different limitations or requirements on the WHERE clause; refer to the Data Model section for more information.

Columns

Name	Type	Description
NAME	String	A component of SQL syntax, or a capability that can be processed on the server.
VALUE	String	Detail on the supported SQL or SQL syntax.

Returns information about attempted modifications.

The following query retrieves the Ids of the modified rows in a batch operation:

         SELECT * FROM sys_identity
          

Columns

Name	Type	Description
Id	String	The database-generated Id returned from a data modification operation.
Batch	String	An identifier for the batch. 1 for a single operation.
Operation	String	The result of the operation in the batch: INSERTED, UPDATED, or DELETED.
Message	String	SUCCESS or an error message if the update in the batch failed.

Describes the available system information.

The following query retrieves all columns:

SELECT * FROM sys_information

Columns

Name	Type	Description
Product	String	The name of the product.
Version	String	The version number of the product.
Datasource	String	The name of the datasource the product connects to.
NodeId	String	The unique identifier of the machine where the product is installed.
HelpURL	String	The URL to the product's help documentation.
License	String	The license information for the product. (If this information is not available, the field may be left blank or marked as 'N/A'.)
Location	String	The file path location where the product's library is stored.
Environment	String	The version of the environment or rumtine the product is currently running under.
DataSyncVersion	String	The tier of CData Sync required to use this connector.
DataSyncCategory	String	The category of CData Sync functionality (e.g., Source, Destination).

The connection string properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure in the connection string for this provider. Click the links for further details.

For more information on establishing a connection, see Establishing a Connection.

Authentication

---

Property	Description
AuthScheme	The type of authentication to use when connecting to Google Drive.

OAuth

---

Property	Description
OAuthClientId	Specifies the client Id that was assigned the custom OAuth application was created. (Also known as the consumer key.) This ID registers the custom application with the OAuth authorization server.
OAuthClientSecret	Specifies the client secret that was assigned when the custom OAuth application was created. (Also known as the consumer secret ). This secret registers the custom application with the OAuth authorization server.
DelegatedServiceAccounts	A space-delimited list of service account emails for delegated requests.
RequestingServiceAccount	A service account email to make a delegated request.

JWT OAuth

---

Property	Description
OAuthJWTCert	The JWT Certificate store.
OAuthJWTCertType	The type of key store containing the JWT Certificate.
OAuthJWTCertPassword	The password for the OAuth JWT certificate used to access a certificate store that requires a password. If the certificate store does not require a password, leave this property blank.
OAuthJWTCertSubject	The subject of the OAuth JWT certificate used to locate a matching certificate in the store. Supports partial matches and the wildcard '*' to select the first certificate.
OAuthJWTIssuer	The issuer of the Java Web Token.
OAuthJWTSubject	The user subject for which the application is requesting delegated access.

SSL

---

Property	Description
SSLServerCert	Specifies the certificate to be accepted from the server when connecting using TLS/SSL.

Logging

---

Property	Description
Verbosity	Specifies the verbosity level of the log file, which controls the amount of detail logged. Supported values range from 1 to 5.

Schema

---

Property	Description
BrowsableSchemas	Optional setting that restricts the schemas reported to a subset of all available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC .

Miscellaneous

---

Property	Description
AWSWorkloadIdentityConfig	Configuration properties to provide when using Workload Identity Federation via AWS.
MaxRows	Specifies the maximum rows returned for queries without aggregation or GROUP BY.
PseudoColumns	Specifies the pseudocolumns to expose as table columns. Use the format 'TableName=ColumnName;TableName=ColumnName'. The default is an empty string, which disables this property.
SupportsAllDrives	Determines whether or not to enable All Drive support.
Timeout	Specifies the maximum time, in seconds, that the provider waits for a server response before throwing a timeout error. The default is 60 seconds. Set to 0 to disable the timeout.
WorkloadPoolId	The ID of your Workload Identity Federation pool.
WorkloadProjectId	The ID of the Google Cloud project that hosts your Workload Identity Federation pool.
WorkloadProviderId	The ID of your Workload Identity Federation pool provider.

This section provides a complete list of the Authentication properties you can configure in the connection string for this provider.

---

Property	Description
AuthScheme	The type of authentication to use when connecting to Google Drive.

The type of authentication to use when connecting to Google Drive.

Possible Values

OAuth, OAuthJWT, AWSWorkloadIdentity

Data Type

string

Default Value

"OAuth"

Remarks

• OAuth: Set this to perform OAuth authentication using a standard user account.
• OAuthJWT: Set this to perform OAuth authentication using an OAuth service account.
• GCPInstanceAccount: Set this to get Access Token from Google Cloud Platform instance.
• AWSWorkloadIdentity: Set this to authenticate using Workload Identity Federation. The Cloud authenticates to AWS according to the AWSWorkloadIdentityConfig and provides Google Security Token Serivce with an authentication token. The Google STS validates this token and produces an OAuth token that can access Google services.

This section provides a complete list of the OAuth properties you can configure in the connection string for this provider.

---

Property	Description
OAuthClientId	Specifies the client Id that was assigned the custom OAuth application was created. (Also known as the consumer key.) This ID registers the custom application with the OAuth authorization server.
OAuthClientSecret	Specifies the client secret that was assigned when the custom OAuth application was created. (Also known as the consumer secret ). This secret registers the custom application with the OAuth authorization server.
DelegatedServiceAccounts	A space-delimited list of service account emails for delegated requests.
RequestingServiceAccount	A service account email to make a delegated request.

Specifies the client Id that was assigned the custom OAuth application was created. (Also known as the consumer key.) This ID registers the custom application with the OAuth authorization server.

Data Type

string

Default Value

""

Remarks

OAuthClientId is one of a handful of connection parameters that need to be set before users can authenticate via OAuth. For details, see Establishing a Connection.

Specifies the client secret that was assigned when the custom OAuth application was created. (Also known as the consumer secret ). This secret registers the custom application with the OAuth authorization server.

Data Type

string

Default Value

""

Remarks

OAuthClientSecret is one of a handful of connection parameters that need to be set before users can authenticate via OAuth. For details, see Establishing a Connection.

A space-delimited list of service account emails for delegated requests.

Data Type

string

Default Value

""

Remarks

The service account emails must be specified in a space-delimited list.

Each service account must be granted the roles/iam.serviceAccountTokenCreator role on its next service account in the chain.

The last service account in the chain must be granted the roles/iam.serviceAccountTokenCreator role on the requesting service account. The requesting service account is the one specified in the RequestingServiceAccount property.

Note that for delegated requests, the requesting service account must have the permission iam.serviceAccounts.getAccessToken, which can also be granted through the serviceAccountTokenCreator role.

A service account email to make a delegated request.

Data Type

string

Default Value

""

Remarks

The service account email of the account for which the credentials are requested in a delegated request. With the list of delegated service accounts in DelegatedServiceAccounts, this property is used to make a delegated request.

You must have the IAM permission iam.serviceAccounts.getAccessToken on this service account.

This section provides a complete list of the JWT OAuth properties you can configure in the connection string for this provider.

---

Property	Description
OAuthJWTCert	The JWT Certificate store.
OAuthJWTCertType	The type of key store containing the JWT Certificate.
OAuthJWTCertPassword	The password for the OAuth JWT certificate used to access a certificate store that requires a password. If the certificate store does not require a password, leave this property blank.
OAuthJWTCertSubject	The subject of the OAuth JWT certificate used to locate a matching certificate in the store. Supports partial matches and the wildcard '*' to select the first certificate.
OAuthJWTIssuer	The issuer of the Java Web Token.
OAuthJWTSubject	The user subject for which the application is requesting delegated access.

The JWT Certificate store.

Data Type

string

Default Value

""

Remarks

The name of the certificate store for the client certificate.

The OAuthJWTCertType field specifies the type of the certificate store specified by OAuthJWTCert. If the store is password protected, specify the password in OAuthJWTCertPassword.

OAuthJWTCert is used in conjunction with the OAuthJWTCertSubject field in order to specify client certificates. If OAuthJWTCert has a value, and OAuthJWTCertSubject is set, a search for a certificate is initiated. Please refer to the OAuthJWTCertSubject field for details.

Designations of certificate stores are platform-dependent.

The following are designations of the most common User and Machine certificate stores in Windows:

MY	A certificate store holding personal certificates with their associated private keys.
CA	Certifying authority certificates.
ROOT	Root certificates.
SPC	Software publisher certificates.

In Java, the certificate store normally is a file containing certificates and optional private keys.

When the certificate store type is PFXFile, this property must be set to the name of the file. When the type is PFXBlob, the property must be set to the binary contents of a PFX file (i.e. PKCS12 certificate store).

The type of key store containing the JWT Certificate.

Possible Values

PFXBLOB, JKSBLOB, PEMKEY_BLOB, PUBLIC_KEY_BLOB, SSHPUBLIC_KEY_BLOB, XMLBLOB, BCFKSBLOB, GOOGLEJSONBLOB

Data Type

string

Default Value

"GOOGLEJSONBLOB"

Remarks

This property can take one of the following values:

USER	For Windows, this specifies that the certificate store is a certificate store owned by the current user. Note: This store type is not available in Java.
MACHINE	For Windows, this specifies that the certificate store is a machine store. Note: this store type is not available in Java.
PFXFILE	The certificate store is the name of a PFX (PKCS12) file containing certificates.
PFXBLOB	The certificate store is a string (base-64-encoded) representing a certificate store in PFX (PKCS12) format.
JKSFILE	The certificate store is the name of a Java key store (JKS) file containing certificates. Note: this store type is only available in Java.
JKSBLOB	The certificate store is a string (base-64-encoded) representing a certificate store in Java key store (JKS) format. Note: this store type is only available in Java.
PEMKEY_FILE	The certificate store is the name of a PEM-encoded file that contains a private key and an optional certificate.
PEMKEY_BLOB	The certificate store is a string (base64-encoded) that contains a private key and an optional certificate.
PUBLIC_KEY_FILE	The certificate store is the name of a file that contains a PEM- or DER-encoded public key certificate.
PUBLIC_KEY_BLOB	The certificate store is a string (base-64-encoded) that contains a PEM- or DER-encoded public key certificate.
SSHPUBLIC_KEY_FILE	The certificate store is the name of a file that contains an SSH-style public key.
SSHPUBLIC_KEY_BLOB	The certificate store is a string (base-64-encoded) that contains an SSH-style public key.
P7BFILE	The certificate store is the name of a PKCS7 file containing certificates.
PPKFILE	The certificate store is the name of a file that contains a PPK (PuTTY Private Key).
XMLFILE	The certificate store is the name of a file that contains a certificate in XML format.
XMLBLOB	The certificate store is a string that contains a certificate in XML format.
BCFKSFILE	The certificate store is the name of a file that contains an Bouncy Castle keystore.
BCFKSBLOB	The certificate store is a string (base-64-encoded) that contains a Bouncy Castle keystore.
GOOGLEJSON	The certificate store is the name of a JSON file containing the service account information. Only valid when connecting to a Google service.
GOOGLEJSONBLOB	The certificate store is a string that contains the service account JSON. Only valid when connecting to a Google service.

The password for the OAuth JWT certificate used to access a certificate store that requires a password. If the certificate store does not require a password, leave this property blank.

Data Type

string

Default Value

""

Remarks

This property specifies the password needed to open the certificate store, but only if the store type requires one. To determine if a password is necessary, refer to the documentation or configuration for your specific certificate store.

This is not required when using the GOOGLEJSON OAuthJWTCertType. Google JSON keys are not encrypted.

The subject of the OAuth JWT certificate used to locate a matching certificate in the store. Supports partial matches and the wildcard '*' to select the first certificate.

Data Type

string

Default Value

"*"

Remarks

The value of this property is used to locate a matching certificate in the store. The search process works as follows:

• If an exact match for the subject is found, the corresponding certificate is selected.
• If no exact match is found, the store is searched for certificates whose subjects contain the property value.
• If no match is found, no certificate is selected.

You can set the value to '*' to automatically select the first certificate in the store. The certificate subject is a comma-separated list of distinguished name fields and values. For example: CN=www.server.com, OU=test, C=US, [email protected]. Common fields include:

Field	Meaning
CN	Common Name. This is commonly a host name like www.server.com.
O	Organization
OU	Organizational Unit
L	Locality
S	State
C	Country
E	Email Address

If a field value contains a comma, enclose it in quotes. For example: "O=ACME, Inc.".

The issuer of the Java Web Token.

Data Type

string

Default Value

""

Remarks

The issuer of the Java Web Token. Enter the value of the service account email address.

This is not required when using the GOOGLEJSON OAuthJWTCertType. Google JSON keys contain a copy of the issuer account.

The user subject for which the application is requesting delegated access.

Data Type

string

Default Value

""

Remarks

The user subject for which the application is requesting delegated access. Enter the email address of the user for which the application is requesting delegated access.

This section provides a complete list of the SSL properties you can configure in the connection string for this provider.

---

Property	Description
SSLServerCert	Specifies the certificate to be accepted from the server when connecting using TLS/SSL.

Specifies the certificate to be accepted from the server when connecting using TLS/SSL.

Data Type

string

Default Value

""

Remarks

If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.

This property can take the following forms:

Description	Example
A full PEM Certificate (example shortened for brevity)	-----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE-----
A path to a local file containing the certificate	C:\cert.cer
The public key (example shortened for brevity)	-----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY-----
The MD5 Thumbprint (hex values can also be either space or colon separated)	ecadbdda5a1529c58a1e9e09828d70e4
The SHA1 Thumbprint (hex values can also be either space or colon separated)	34a929226ae0819f2ec14b4a3d904f801cbb150d

If not specified, any certificate trusted by the machine is accepted.

Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.

This section provides a complete list of the Logging properties you can configure in the connection string for this provider.

---

Property	Description
Verbosity	Specifies the verbosity level of the log file, which controls the amount of detail logged. Supported values range from 1 to 5.

Specifies the verbosity level of the log file, which controls the amount of detail logged. Supported values range from 1 to 5.

Data Type

string

Default Value

"1"

Remarks

This property defines the level of detail the Cloud includes in the log file. Higher verbosity levels increase the detail of the logged information, but may also result in larger log files and slower performance due to the additional data being captured.

The default verbosity level is 1, which is recommended for regular operation. Higher verbosity levels are primarily intended for debugging purposes. For more information on each level, refer to Logging.

When combined with the LogModules property, Verbosity can refine logging to specific categories of information.

This section provides a complete list of the Schema properties you can configure in the connection string for this provider.

---

Property	Description
BrowsableSchemas	Optional setting that restricts the schemas reported to a subset of all available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC .

Optional setting that restricts the schemas reported to a subset of all available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC .

Data Type

string

Default Value

""

Remarks

Listing all available database schemas can take extra time, thus degrading performance. Providing a list of schemas in the connection string saves time and improves performance.

This section provides a complete list of the Miscellaneous properties you can configure in the connection string for this provider.

---

Property	Description
AWSWorkloadIdentityConfig	Configuration properties to provide when using Workload Identity Federation via AWS.
MaxRows	Specifies the maximum rows returned for queries without aggregation or GROUP BY.
PseudoColumns	Specifies the pseudocolumns to expose as table columns. Use the format 'TableName=ColumnName;TableName=ColumnName'. The default is an empty string, which disables this property.
SupportsAllDrives	Determines whether or not to enable All Drive support.
Timeout	Specifies the maximum time, in seconds, that the provider waits for a server response before throwing a timeout error. The default is 60 seconds. Set to 0 to disable the timeout.
WorkloadPoolId	The ID of your Workload Identity Federation pool.
WorkloadProjectId	The ID of the Google Cloud project that hosts your Workload Identity Federation pool.
WorkloadProviderId	The ID of your Workload Identity Federation pool provider.

Configuration properties to provide when using Workload Identity Federation via AWS.

Data Type

string

Default Value

""

Remarks

The properties are formatted as a semicolon-separated list of Key=Value properties, where the value is optionally quoted. For example, this setting authenticates in AWS using a user's root keys:

AWSWorkloadIdentityConfig="AuhtScheme=AwsRootKeys;AccessKey='AKIAABCDEF123456';SecretKey=...;Region=us-east-1"

Specifies the maximum rows returned for queries without aggregation or GROUP BY.

Data Type

int

Default Value

-1

Remarks

This property sets an upper limit on the number of rows the Cloud returns for queries that do not include aggregation or GROUP BY clauses. This limit ensures that queries do not return excessively large result sets by default.

When a query includes a LIMIT clause, the value specified in the query takes precedence over the MaxRows setting. If MaxRows is set to "-1", no row limit is enforced unless a LIMIT clause is explicitly included in the query.

This property is useful for optimizing performance and preventing excessive resource consumption when executing queries that could otherwise return very large datasets.

Specifies the pseudocolumns to expose as table columns. Use the format 'TableName=ColumnName;TableName=ColumnName'. The default is an empty string, which disables this property.

Data Type

string

Default Value

""

Remarks

This property allows you to define which pseudocolumns the Cloud exposes as table columns.

To specify individual pseudocolumns, use the following format: "Table1=Column1;Table1=Column2;Table2=Column3"

To include all pseudocolumns for all tables use: "*=*"

Determines whether or not to enable All Drive support.

Data Type

bool

Default Value

false

Remarks

If you set this property to 'true', you can query from a specific Drive using the DriveId as a filter.

Specifies the maximum time, in seconds, that the provider waits for a server response before throwing a timeout error. The default is 60 seconds. Set to 0 to disable the timeout.

Data Type

int

Default Value

60

Remarks

This property controls the maximum time, in seconds, that the Cloud waits for an operation to complete before canceling it. If the timeout period expires before the operation finishes, the Cloud cancels the operation and throws an exception.

The timeout applies to each individual communication with the server rather than the entire query or operation. For example, a query could continue running beyond 60 seconds if each paging call completes within the timeout limit.

Setting this property to 0 disables the timeout, allowing operations to run indefinitely until they succeed or fail due to other conditions such as server-side timeouts, network interruptions, or resource limits on the server. Use this property cautiously to avoid long-running operations that could degrade performance or result in unresponsive behavior.

The ID of your Workload Identity Federation pool.

Data Type

string

Default Value

""

Remarks

The ID of your Workload Identity Federation pool.

The ID of the Google Cloud project that hosts your Workload Identity Federation pool.

Data Type

string

Default Value

""

Remarks

The ID of the Google Cloud project that hosts your Workload Identity Federation pool.

The ID of your Workload Identity Federation pool provider.

Data Type

string

Default Value

""

Remarks

The ID of your Workload Identity Federation pool provider.