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 Jitterbit 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