JDBC Driver for REST

Build 20.0.7654

Connecting to REST Data Sources

After connecting to your data source, set Format to "XML" or "JSON" and set DataModel to more closely match the data representation to the structure of your data.

Connecting to REST

Below are example connection strings to XML/JSON files or streams, using the driver's default data modeling configuration (see below)

Service provider URI formats Connection example
Local localPath

file:///localPath/file.json

URI=C:/folder1/file.json;
HTTP or HTTPS http://remoteStream

https://remoteStream

URI=http://www.host1.com/streamname1;
Amazon S3 s3://bucket1/folder1/file.json URI=s3://bucket1/folder1/file.json; AWSSecretKey=secret1; AWSRegion=OHIO;
Azure Blob Storage azureblob://mycontainer/myblob/ URI=azureblob://mycontainer/myblob/; AzureAccount=myAccount; AzureAccessKey=myKey;
Google Drive gdrive://remotePath/file.json

gdrive://SharedWithMe/remotePath/file.json

URI=gdrive://folder1/file.json;InitiateOAuth=GETANDREFRESH; AuthScheme=OAuth;

URI=gdrive://SharedWithMe/folder1/file.json;InitiateOAuth=GETANDREFRESH; AuthScheme=OAuth;

OneDrive onedrive://remotePath/file.json

onedrive://SharedWithMe/remotePath/file.json

URI=onedrive://folder1/file.json;InitiateOAuth=GETANDREFRESH; AuthScheme=OAuth;

URI=onedrive://SharedWithMe/folder1/file.json;InitiateOAuth=GETANDREFRESH; AuthScheme=OAuth;

Box box://remotePath/file.json URI=box://folder1/file.json; InitiateOAuth=GETANDREFRESH; AuthScheme=OAuth;
Dropbox dropbox://remotePath/file.json URI=dropbox://folder1/file.json; InitiateOAuth=GETANDREFRESH; AuthScheme=OAuth; OAuthClientId=oauthclientid1; OAuthClientSecret=oauthcliensecret1; CallbackUrl=http://localhost:12345;
Sharepoint SOAP sp://remotePath/file.json URI=sp://Documents/folder1/file.json; User=user1; Password=password1; SharepointUrl=https://subdomain.sharepoint.com;
Sharepoint REST sprest://remotePath/file.json URI=sprest://Documents/folder1/file.json; InitiateOAuth=GETANDREFRESH; AuthScheme=OAuth; SharepointUrl=https://subdomain.sharepoint.com;
FTP or FTPS ftp://server:port/remotePath/file.json

ftps://server:port/remotepath/file.json

URI=ftps://localhost:990/folder1/file.json; User=user1; Password=password1;
SFTP sftp://server:port/remotePath/file.json URI=sftp://127.0.0.1:22/folder1/file.json User=user1; Password=password1;

URI=sftp://127.0.0.1:22/folder1/file.json SSHAuthmode=PublicKey; SSHClientCert=myPrivateKey

Azure Data Lake Store Gen1 adl://remotePath/file.json

adl://Account.azuredatalakestore.net@remotePath/file.json

URI=adl://folder1/file.json; InitiateOAuth=GETANDREFRESH; AuthScheme=OAuth; AzureAccount=myAccount; AzureTenant=tenant;

URI=adl://myAccount.azuredatalakestore.net@folder1/file.json; InitiateOAuth=GETANDREFRESH; AuthScheme=OAuth; AzureTenant=tenant;

AzureDataLakeStoreGen2 abfs://myfilesystem/remotePath/file.json

abfss://myfilesystem@accountName.dfs.core.windows.net/remotepath/file.json

URI=abfs://myfilesystem/folder1/file.json; AzureAccount=myAccount; AzureAccessKey=myKey;

URI=abfss://myfilesystem@myAccount.dfs.core.windows.net/folder1/file.json; AzureAccessKey=myKey;

AzureDataLakeStoreGen2 with SSL abfss://myfilesystem/remotePath/file.json

abfs://myfilesystem@accountName.dfs.core.windows.net/remotepath/file.json

URI=abfss://myfilesystem/folder1/file.json; AzureAccount=myAccount; AzureAccessKey=myKey;

URI=abfss://myfilesystem@myAccount.dfs.core.windows.net/folder1/file.json; AzureAccessKey=myKey;

Wasabi wasabi://bucket/remotePath/file.json URI=wasabi://bucket/folder1/file.json; AccessKey=token1; SecretKey=secret1; Region='us-west-1';
Google Cloud Storage gs://bucket/remotePath/file.json URI=gs://bucket/folder1/file.json; InitiateOAuth=GETANDREFRESH; AuthScheme=OAuth; ProjectId=test;
Oracle Cloud Storage os://bucket/remotePath/file.json URI=os://bucket/folder1/file.json; AccessKey='myKey'; SecretKey='mySecretKey'; OracleNameSpace='myNameSpace' Region='us-west-1';

Modeling XML/JSON Data

The DataModel property is the controlling property over how your data is represented into tables and toggles the following basic configurations.

  • Document (default): Model a top-level, document view of your REST data. The driver returns nested object arrays as aggregated XML/JSON objects.
  • FlattenedDocuments: Implicitly join nested array objects and parent objects into a single table.
  • Relational: View nested object arrays as individual, related tables containing a primary key and a foreign key that links to the parent document.

Next Steps

  • See Parsing Hierarchical Data for examples showing how to query an example dataset in each DataModel configuration.
  • See Modeling REST Data for information on customizing schema discovery and executing SQL to REST.
  • See Advanced Settings for more advanced connection settings: fine-tune the default data modeling settings, connect through a firewall, or troubleshoot connections.

Copyright (c) 2020 CData Software, Inc. - All rights reserved.
Build 20.0.7654