Microsoft OneDrive Connection Details¶

Introduction¶

Connector Version

This documentation is based on version 23.0.8804 of the connector.

Get Started¶

Microsoft OneDrive Version Support

Microsoft OneDrive and OneDrive for Business accounts are supported via the Microsoft Graph API v1.0.

Establish a Connection¶

Connect to Microsoft OneDrive¶

Authenticate using Azure Service Principal¶

Azure Service Principal is a connection type that goes through OAuth. Set your AuthScheme to AzureServicePrincipal and see Using Azure Service Principal Authentication for an authentication guide.

Use Azure Service Principal¶

Azure Service Principal¶

Azure Service Principal is role-based application-based authentication. This means that authentication is done per application, rather than per user. All tasks taken by the app are done without a default user context, but based on the assigned roles. The application access to the resources is controlled through the assigned roles' permissions.

To use Azure Service Principal authentication, you must:

• Set up the ability to assign a role to the authentication application. To do this, create a custom OAuth AD application, as described in

  Creating a Custom OAuth Application¶

  Microsoft OneDrive supports authentication using Azure AD and Azure Service Principal, both of which are OAuth-based.

  This topic describes how to:

  • create and register custom OAuth application for Azure AD or Azure Service Principal
  • provide Admin Consent to a custom OAuth application

  Azure AD¶

  In portal.azure.com:

  1. Log in to https://portal.azure.com.
  2. In the left-hand navigation pane, select Azure Active Directory, then applicationRegistrations.
  3. Click New registration.
  4. Enter a name for the application.
  5. Select the desired tenant setup: single- or multi-tenant, and public or private use.
     • If you select the default option, "Accounts in this organizational directory only", you must set the AzureTenant connection property to the ID of the Azure AD Tenant when establishing a connection with the Microsoft OneDrive connector. Otherwise, the authentication attempt fails with an error.
     • If your application is for private use only, specify Accounts in this organization directory only.
     • If you want to distribute your application, choose one of the multi-tenant options.
  6. Set the redirect URL to http://localhost:33333 (the connector's default) OR specify a different port and set CallbackURL to the exact reply URL you defined.
  7. Click Register to register the new application. An application management screen displays.
     Note the value in Application (client) ID as the OAuthClientId and the Directory (tenant) ID as the AzureTenant.
  8. Navigate to Certificates & Secrets and define the application authentication type. There are two types of authentication available: certificate (recommended) or client secret.
     • For certificate authentication: In Certificates & Secrets, select Upload certificate, then upload the certificate from your local machine.
     • For creating a new client secret: In Certificates & Secrets, select New Client Secret for the application and specify its duration. After the client secret is saved, Microsoft OneDrive displays the key value. Copy this value, as it is displayed only once. This value becomes the OAuthClientSecret.
  9. Select API Permissions > Add > Delegated permissions.
  10. Select the Microsoft Graph API and then add the delegated permissions Files.ReadWrite.All or Files.Read.All. Hit the 'Grant admin consent' button afterwards for the new permissions to take effect.
  11. Save your changes.
  12. If you have specified the use of permissions that require admin consent, you can grant them from the current tenant on the API Permissions page.

  Azure Service Principal¶

  To use Azure Service Principal authentication, you must set up the ability to assign a role to the authentication application, then register an application with the Azure AD tenant to create a new Service Principal. That new Service Principal can then leverage the assigned role-based access

  control to access resources in your subscription.

  In portal.azure.com:

  1. Create a custom OAuth AD application, as described above.
  2. Use the search bar to search for the Subscriptions service.
  3. Open the Subscriptions page.
  4. Select the subscription to which to assign the application.
  5. Open the Access control (IAM).
  6. Select Add > Add role assignment. Microsoft OneDrive opens the Add role assignment page.
  7. Assign your custom Azure AD application the role of Owner.

  Admin Consent¶

  Some custom applications require administrative permissions to operate within an Azure Active Directory tenant. Admin consent can be granted when creating a new custom OAuth application, by adding relevant permissions that are already marked with "Admin Consent Required". Admin consent is also required to use Client Credentials in the OAuth flow.

  To grant admin consent:

  1. Have an admin log in to portal.azure.com.
  2. Navigate to App Registrations and find the custom OAuth application you created.
  3. Under API Permissions, click Grant Consent.

  This gives your application permissions on the tenant under which it was created.

  .

• Register an application with an Azure AD tenant, to create a new service principal that can be used with the role-based access control, to access resources in your subscription.

Do the following:

1. Create a custom Azure AD application, as described in

   Creating a Custom OAuth Application¶

   Microsoft OneDrive supports authentication using Azure AD and Azure Service Principal, both of which are OAuth-based.

   This topic describes how to:

   • create and register custom OAuth application for Azure AD or Azure Service Principal
   • provide Admin Consent to a custom OAuth application

   Azure AD¶

   In portal.azure.com:

   1. Log in to https://portal.azure.com.
   2. In the left-hand navigation pane, select Azure Active Directory, then applicationRegistrations.
   3. Click New registration.
   4. Enter a name for the application.
   5. Select the desired tenant setup: single- or multi-tenant, and public or private use.
      • If you select the default option, "Accounts in this organizational directory only", you must set the AzureTenant connection property to the ID of the Azure AD Tenant when establishing a connection with the Microsoft OneDrive connector. Otherwise, the authentication attempt fails with an error.
      • If your application is for private use only, specify Accounts in this organization directory only.
      • If you want to distribute your application, choose one of the multi-tenant options.
   6. Set the redirect URL to http://localhost:33333 (the connector's default) OR specify a different port and set CallbackURL to the exact reply URL you defined.
   7. Click Register to register the new application. An application management screen displays.
      Note the value in Application (client) ID as the OAuthClientId and the Directory (tenant) ID as the AzureTenant.
   8. Navigate to Certificates & Secrets and define the application authentication type. There are two types of authentication available: certificate (recommended) or client secret.
      • For certificate authentication: In Certificates & Secrets, select Upload certificate, then upload the certificate from your local machine.
      • For creating a new client secret: In Certificates & Secrets, select New Client Secret for the application and specify its duration. After the client secret is saved, Microsoft OneDrive displays the key value. Copy this value, as it is displayed only once. This value becomes the OAuthClientSecret.
   9. Select API Permissions > Add > Delegated permissions.
   10. Select the Microsoft Graph API and then add the delegated permissions Files.ReadWrite.All or Files.Read.All. Hit the 'Grant admin consent' button afterwards for the new permissions to take effect.
   11. Save your changes.
   12. If you have specified the use of permissions that require admin consent, you can grant them from the current tenant on the API Permissions page.

   Azure Service Principal¶

   To use Azure Service Principal authentication, you must set up the ability to assign a role to the authentication application, then register an application with the Azure AD tenant to create a new Service Principal. That new Service Principal can then leverage the assigned role-based access

   control to access resources in your subscription.

   In portal.azure.com:

   1. Create a custom OAuth AD application, as described above.
   2. Use the search bar to search for the Subscriptions service.
   3. Open the Subscriptions page.
   4. Select the subscription to which to assign the application.
   5. Open the Access control (IAM).
   6. Select Add > Add role assignment. Microsoft OneDrive opens the Add role assignment page.
   7. Assign your custom Azure AD application the role of Owner.

   Admin Consent¶

   Some custom applications require administrative permissions to operate within an Azure Active Directory tenant. Admin consent can be granted when creating a new custom OAuth application, by adding relevant permissions that are already marked with "Admin Consent Required". Admin consent is also required to use Client Credentials in the OAuth flow.

   To grant admin consent:

   1. Have an admin log in to portal.azure.com.
   2. Navigate to App Registrations and find the custom OAuth application you created.
   3. Under API Permissions, click Grant Consent.

   This gives your application permissions on the tenant under which it was created.

   .

2. Assign a role to the application:

   1. Use the search bar to search for the Subscriptions service.
   2. Open the Subscriptions page.
   3. Select the subscription to which to assign the application.
   4. Open the Access control (IAM).
   5. Select Add > Add role assignment. Microsoft OneDrive opens the Add role assignment page.
   6. Assign your custom Azure AD application the role of Owner.

Admin Consent¶

Admin consent occurs when the Admin for an Azure Active Directory tenant grants permissions to a custom application that explicitly requires an admin to consent to the use case.

When creating a new Azure AD application in the Azure Portal, you must specify which permissions the application requires. Some permissions may be marked as "Admin Consent Required". For example, all Groups permissions require Admin Consent. If your application requires admin consent, there are two ways you can do this.

The easiest way to grant admin consent is to have an admin log into portal.azure.com and navigate to the application you have created in App Registrations. Under API Permissions, click Grant Consent. This gives your application permissions on the tenant under which it was created.

If your organization has multiple tenants or you must grant application permissions for other tenants outside your organization, use the GetAdminConsentURL stored procedure to generate the Admin Authorization URL. Unlike the GetOAuthAuthorizationURL stored procedure, no important information is returned from this endpoint. Rather, after the OAuth application is successfully authorized, it returns a Boolean indicating that permissions have been granted.

After the administrator has approved the OAuth Application, you can continue to authenticate.

Create a Custom OAuth Application¶

Create a Custom OAuth Application¶

Microsoft OneDrive supports authentication using Azure AD and Azure Service Principal, both of which are OAuth-based.

This topic describes how to:

• create and register custom OAuth application for Azure AD or Azure Service Principal
• provide Admin Consent to a custom OAuth application

Azure AD¶

In portal.azure.com:

1. Log in to https://portal.azure.com.
2. In the left-hand navigation pane, select Azure Active Directory, then applicationRegistrations.
3. Click New registration.
4. Enter a name for the application.
5. Select the desired tenant setup: single- or multi-tenant, and public or private use.
   • If you select the default option, "Accounts in this organizational directory only", you must set the AzureTenant connection property to the ID of the Azure AD Tenant when establishing a connection with the Microsoft OneDrive connector. Otherwise, the authentication attempt fails with an error.
   • If your application is for private use only, specify Accounts in this organization directory only.
   • If you want to distribute your application, choose one of the multi-tenant options.
6. Set the redirect URL to http://localhost:33333 (the connector's default) OR specify a different port and set CallbackURL to the exact reply URL you defined.
7. Click Register to register the new application. An application management screen displays.
   Note the value in Application (client) ID as the OAuthClientId and the Directory (tenant) ID as the AzureTenant.
8. Navigate to Certificates & Secrets and define the application authentication type. There are two types of authentication available: certificate (recommended) or client secret.
   • For certificate authentication: In Certificates & Secrets, select Upload certificate, then upload the certificate from your local machine.
   • For creating a new client secret: In Certificates & Secrets, select New Client Secret for the application and specify its duration. After the client secret is saved, Microsoft OneDrive displays the key value. Copy this value, as it is displayed only once. This value becomes the OAuthClientSecret.
9. Select API Permissions > Add > Delegated permissions.
10. Select the Microsoft Graph API and then add the delegated permissions Files.ReadWrite.All or Files.Read.All. Hit the 'Grant admin consent' button afterwards for the new permissions to take effect.
11. Save your changes.
12. If you have specified the use of permissions that require admin consent, you can grant them from the current tenant on the API Permissions page.

Azure Service Principal¶

To use Azure Service Principal authentication, you must set up the ability to assign a role to the authentication application, then register an application with the Azure AD tenant to create a new Service Principal. That new Service Principal can then leverage the assigned role-based access

control to access resources in your subscription.

In portal.azure.com:

1. Create a custom OAuth AD application, as described above.
2. Use the search bar to search for the Subscriptions service.
3. Open the Subscriptions page.
4. Select the subscription to which to assign the application.
5. Open the Access control (IAM).
6. Select Add > Add role assignment. Microsoft OneDrive opens the Add role assignment page.
7. Assign your custom Azure AD application the role of Owner.

Admin Consent¶

Some custom applications require administrative permissions to operate within an Azure Active Directory tenant. Admin consent can be granted when creating a new custom OAuth application, by adding relevant permissions that are already marked with "Admin Consent Required". Admin consent is also required to use Client Credentials in the OAuth flow.

To grant admin consent:

1. Have an admin log in to portal.azure.com.
2. Navigate to App Registrations and find the custom OAuth application you created.
3. Under API Permissions, click Grant Consent.

This gives your application permissions on the tenant under which it was created.

Fine-Tuning Data Access¶

Fine-tune Data Access¶

• DirectoryRetrievalDepth: How many folders deep you want to get results for Folders, Files, SharedResources, and Permissions tables.
• DriveId: The ID of the Drive that you want to use when getting data for tables Files, Folders, SharedResources, and Permissions.
• ListGroupDrives: Indicates if you would like to list your group drives or not when querying the Drives table. Useful if you're using a OneDrive for Business account.

Important Notes¶

Configuration Files and Their Paths¶

• All references to adding configuration files and their paths refer to files and locations on the Harmony Agent where the connector is installed. These paths are to be adjusted as appropriate depending on the agent and the operating system. If multiple agents are used in an agent group, identical files will be required on each agent.

Advanced Features¶

This section details a selection of advanced features of the Microsoft OneDrive connector.

User Defined Views

The connector allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.

SSL Configuration

Use SSL Configuration to adjust how connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.

Proxy

To configure the connector using Private Agent proxy settings, select the Use Proxy Settings checkbox on the connection configuration screen.

Query Processing

The connector offloads as much of the SELECT statement processing as possible to Microsoft OneDrive and then processes the rest of the query in memory (client-side).

User Defined Views¶

The Microsoft OneDrive connector allows you to define a virtual table whose contents are decided by a pre-configured query. These are called User Defined Views, which are useful in situations where you cannot directly control the query being issued to the driver, e.g. when using the driver from Jitterbit. The User Defined Views can be used to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.

There are two ways to create user defined views:

• Create a JSON-formatted configuration file defining the views you want.
• DDL statements.

Define Views Using a Configuration File¶

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.

This User Defined View configuration file is formatted as follows:

• Each root element defines the name of a view.
• Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
    "MyView": {
        "query": "SELECT * FROM Files WHERE MyColumn = 'value'"
    },
    "MyView2": {
        "query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
    }
}

Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:

"UserDefinedViews", "C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json"

Define Views Using DDL Statements¶

The connector is also capable of creating and altering the schema via DDL Statements such as CREATE LOCAL VIEW, ALTER LOCAL VIEW, and DROP LOCAL VIEW.

Create a View¶

To create a new view using DDL statements, provide the view name and query as follows:

CREATE LOCAL VIEW [MyViewName] AS SELECT * FROM Customers LIMIT 20;

If no JSON file exists, the above code creates one. The view is then created in the JSON configuration file and is now discoverable. The JSON file location is specified by the UserDefinedViews connection property.

Alter a View¶

To alter an existing view, provide the name of an existing view alongside the new query you would like to use instead:

ALTER LOCAL VIEW [MyViewName] AS SELECT * FROM Customers WHERE TimeModified > '3/1/2020';

The view is then updated in the JSON configuration file.

Drop a View¶

To drop an existing view, provide the name of an existing schema alongside the new query you would like to use instead.

DROP LOCAL VIEW [MyViewName]

This removes the view from the JSON configuration file. It can no longer be queried.

Schema for User Defined Views¶

User Defined Views are exposed in the UserViews schema by default. This is done to avoid the view's name clashing with an actual entity in the data model. You can change the name of the schema used for UserViews by setting the UserViewsSchemaName property.

Work with User Defined Views¶

For example, a SQL statement with a User Defined View called UserViews.RCustomers only lists customers in Raleigh:

SELECT * FROM Customers WHERE City = 'Raleigh';

An example of a query to the driver:

SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';

Resulting in the effective query to the source:

SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';

That is a very simple example of a query to a User Defined View that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.

SSL Configuration¶

Customize the SSL Configuration¶

By default, the connector attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Data Model¶

The connector models the Microsoft OneDrive API as relational tables. Any changes to the remote data are immediately reflected in your queries; the table definitions are dynamically retrieved. When you connect, the connector connects to Microsoft OneDrive and gets the list of tables and the metadata for the tables by calling the appropriate Web services.

Key Features

• The connector models Microsoft OneDrive entities like Files and Folders as relational views, allowing you to write SQL to query Microsoft OneDrive data.
• Stored procedures allow you to execute operations to Microsoft OneDrive, including retrieving the access token and keeping it refreshed in OAuth 2.0.
• Live connectivity to these objects means any changes to your Microsoft OneDrive account are immediately reflected when using the connector.

Views

Views shows definitions from a sample OneDrive site. The actual data model

will be obtained dynamically based on your user credentials and site.

Stored Procedures

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

Views¶

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.

Microsoft OneDrive Connector Views¶

Drives¶

The OneDrive view Drives, which lists all the drives the user has access to.

Table Specific Information¶

Due to API limitations, all operators on this view are executed client-side.

Columns¶

Files¶

The OneDrive table Files in which you can view all the files in a drive.

Table Specific Information¶

Projection is done server-side. Also, queries that include the primary key ID and IN are executed server-side, e.g.:

SELECT Id, Name, audio_album, audio_title FROM Files WHERE ID IN ('B1E6BJ084A9133KB1!101', 'APF9D2K6WFA524Y')

Include ParentReference_DriveId and ParentReference_Id in the where clause to get the result for a particular folder, e.g.:

SELECT * FROM Files WHERE  ParentReference_DriveId = 'b!3Yqza-5FJ0usfhyH-2QafkQpf0OC0jlNh8LAETWPK6dNROPDScevTKbV-drnCFHV' And ParentReference_id = '01QFG2V52Z62HOXS5F6RHIHVT36T3Q5GKH'

File listing is done recursively via the DirectoryRetrievalDepth property. This property's default value is 5, which means it will get data only for the first 6 levels of the 'Drive' you've chosen (because the first level is located in DirectoryRetrievalDepth=0).

You can specify the value -1 to get all the files in the 'Drive' no matter the depth they're located in.

While files may be listed in this view, the contents of the files may be worked with by using stored procedures such as:

• CopyResource - Copy a file/folder from one directory in your drive to another.
• DeleteResource - Delete a file/folder from your drive.
• DownloadFile - Download an existing file from your drive.
• MoveResource - Move a file/folder from one directory in your drive to another.
• UploadFile - Upload a new file or update content to an existing file.

Columns¶

FileVersions¶

The OneDrive table Files in which you can view all the files in a drive.

Table Specific Information¶

SELECT¶

The connector will use the Microsoft OneDrive API to process WHERE clause conditions built with the following column and operator.

• FileId supports the '='.
• DriveId supports the '='.
• UserId supports the '='.

For example:

SELECT * FROM FileVersions WHERE FileId = '01CYQN2MFPR6NIWVZNMNFL3SL4E3KF7IHY' AND DriveId = 'b!GrEGWax2VE-ssnJNr1E8llqoLptvK-BGjFRpceFzFg5Nwp9Bw2xbR62J0NAouP6z'

SELECT * FROM FileVersions WHERE FileId = '01CYQN2MFPR6NIWVZNMNFL3SL4E3KF7IHY' AND createdBy_user_id = '472df319-2978-49d4-9445-9e7019def16f'

Columns¶

Folders¶

The OneDrive table Folders, which shows all the folders on a drive.

Table Specific Information¶

Note: See CreateFolder to create a folder.

Projection is done server-side. Also, queries that include the primary key ID and IN are executed server-side, e.g.:

SELECT Id, Name, Size, Description FROM Folders WHERE ID IN ('B1E6BJ084A9133KB1!122', 'APF9D2K6WFAAPX9')

Folder listing is done recursively via the DirectoryRetrievalDepth property. This property's default value is 5, which means it will get data only for the first 6 levels of the 'Drive' you've chosen (because the first level is located in DirectoryRetrievalDepth=0).

You can specify the value -1 to get all the folders in the 'Drive' no matter the depth they're located in.

While folders may be listed in this view, the contents of the folders may be worked with by using stored procedures such as:

• CopyResource - Copy a file/folder from one directory in your drive to another.
• DeleteResource - Delete a file/folder from your drive.
• MoveResource - Move a file/folder from one directory in your drive to another.

Columns¶

Permissions¶

The OneDrive table Permissions, which shows all the permissions given to a resource of the drive.

Table Specific Information¶

Due to API limitations, all operators on this view are executed client-side.

Columns¶

SharedResources¶

The OneDrive table SharedResources, which shows all the shared resources on a drive.

Table Specific Information¶

Projection is done server-side. Also, queries that include the primary key ID and IN are executed server-side, e.g.:

SELECT Id, Name, Size, Shared_Scope FROM SharedResources WHERE ID IN ('B1E6BJ084A9133KB1!122', 'APF9D2K6WFAAPX9')

SharedResources listing is done recursively via the DirectoryRetrievalDepth property. This property's default value is 5, which means it will get data only for the first 6 levels of the 'Drive' you've chosen (because the first level is located in DirectoryRetrievalDepth=0).

You can specify the value -1 to get all the shared resources in the 'Drive' no matter the depth they're located in.

Columns¶

Users¶

The OneDrive table Users in which an admin user can view the users details.

Users Information¶

Projection is done server-side. Basic select query is supported e.g.:

SELECT * FROM Users

Using this view an admin user can list all other users details.

Columns¶

Stored Procedures¶

Stored procedures are function-like interfaces that extend the functionality of the connector beyond simple SELECT operations with Microsoft OneDrive.

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

Microsoft OneDrive Connector Stored Procedures¶

CopyResource¶

Copy a resource from one directory in your drive to another.

Input¶

Result Set Columns¶

CreateFolder¶

Create a new folder in your drive.

Input¶

Result Set Columns¶

CreateSubscription¶

Subscribes a listener application to receive notifications when data changes.

Input¶

Result Set Columns¶

DeleteResource¶

Delete a resource from your drive.

Input¶

Result Set Columns¶

DeleteSubscription¶

Subscribes a listener application to receive notifications when data changes.

Input¶

Result Set Columns¶

DownloadFile¶

Download an existing file from your drive.

Input¶

Result Set Columns¶

FetchAdditionalUserFields¶

Fetch all T1, T2, and T3 fields for a specified user.

Input¶

Result Set Columns¶

GetAdminConsentURL¶

Gets the admin consent URL that must be opened separately by an admin of a given domain to grant access to your application. Only needed when using custom OAuth credentials.

Input¶

Result Set Columns¶

GetOAuthAccessToken¶

Gets an authentication token from OneDrive.

Input¶

Result Set Columns¶

GetOAuthAuthorizationURL¶

Gets the authorization URL that must be opened separately by the user to grant access to your application. Only needed when developing Web apps. You will request the auth token from this URL.

Input¶

Result Set Columns¶

ModifySubscription¶

Subscribes a listener application to receive notifications when data changes.

Input¶

Result Set Columns¶

MoveResource¶

Move a resource from one directory in your drive to another.

Input¶

Result Set Columns¶

RefreshOAuthAccessToken¶

Refreshes the OAuth access token used for authentication with various Office 365 services.

Input¶

Result Set Columns¶

RenameResource¶

Rename files from your drive.

Input¶

Result Set Columns¶

UploadFile¶

Upload a new file or update content to an existing file.

Input¶

Result Set Columns¶

System Tables¶

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 Microsoft OneDrive:

• 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 connector 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.

sys_catalogs¶

Lists the available databases.

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

SELECT * FROM sys_catalogs

Columns¶

sys_schemas¶

Lists the available schemas.

The following query retrieves all available schemas:

SELECT * FROM sys_schemas

Columns¶

sys_tables¶

Lists the available tables.

The following query retrieves the available tables and views:

SELECT * FROM sys_tables

Columns¶

sys_tablecolumns¶

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¶

sys_procedures¶

Lists the available stored procedures.

The following query retrieves the available stored procedures:

SELECT * FROM sys_procedures

Columns¶

sys_procedureparameters¶

Describes stored procedure parameters.

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

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

Columns¶

sys_keycolumns¶

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¶

sys_foreignkeys¶

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¶

sys_primarykeys¶

Describes the primary keys.

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

SELECT * FROM sys_primarykeys

Columns¶

sys_indexes¶

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¶

sys_connection_props¶

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

When querying this table, the config connection string should be used:

jdbc:cdata:onedrive:config:

This connection string enables you to query this table without a valid connection.

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¶

sys_sqlinfo¶

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

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.

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¶

sys_identity¶

Returns information about attempted modifications.

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

SELECT * FROM sys_identity

Columns¶

Data Type Mapping¶

Data Type Mappings¶

The connector maps types from the data source to the corresponding data type available in the schema. The table below documents these mappings.

Advanced Configurations Properties¶

The advanced configurations 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. Click the links for further details.

Authentication

Azure Authentication

OAuth

SSL

Schema

Miscellaneous