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 | Single File Path (One table)
file://localPath/file.json Directory Path (one table per file) file://localPath | URI=C:/folder1/file.json; |
| HTTP or HTTPS | http://remoteStream
https://remoteStream | URI=http://www.host1.com/streamname1; |
| Amazon S3 | Single File Path (One table)
s3://remotePath/file.json Directory Path (one table per file) s3://remotePath | URI=s3://bucket1/folder1/file.json; AWSSecretKey=secret1; AWSRegion=OHIO; |
| Azure Blob Storage | azureblob://mycontainer/myblob/ | URI=azureblob://mycontainer/myblob/; AzureStorageAccount=myAccount; AzureAccessKey=myKey;
URI=azureblob://mycontainer/myblob/; AzureStorageAccount=myAccount; InitiateOAuth=GETANDREFRESH; AuthScheme=OAuth; |
| Google Drive | Single File Path (One table)
gdrive://remotePath/file.json gdrive://SharedWithMe/remotePath/file.json Directory Path (one table per file) gdrive://remotePath gdrive://SharedWithMe/remotePath | URI=gdrive://folder1/file.json;InitiateOAuth=GETANDREFRESH; AuthScheme=OAuth;
URI=gdrive://SharedWithMe/folder1/file.json;InitiateOAuth=GETANDREFRESH; AuthScheme=OAuth; |
| OneDrive | Single File Path (One table)
onedrive://remotePath/file.json onedrive://SharedWithMe/remotePath/file.json Directory Path (one table per file) onedrive://remotePath onedrive://SharedWithMe/remotePath | URI=onedrive://folder1/file.json;InitiateOAuth=GETANDREFRESH; AuthScheme=OAuth;
URI=onedrive://SharedWithMe/folder1/file.json;InitiateOAuth=GETANDREFRESH; AuthScheme=OAuth; |
| Box | Single File Path (One table)
box://remotePath/file.json Directory Path (one table per file) box://remotePath | URI=box://folder1/file.json; InitiateOAuth=GETANDREFRESH; AuthScheme=OAuth; |
| Dropbox | Single File Path (One table)
dropbox://remotePath/file.json Directory Path (one table per file) dropbox://remotePath | URI=dropbox://folder1/file.json; InitiateOAuth=GETANDREFRESH; AuthScheme=OAuth; OAuthClientId=oauthclientid1; OAuthClientSecret=oauthcliensecret1; CallbackUrl=http://localhost:12345; |
| SharePoint SOAP | Single File Path (One table)
sp://remotePath/file.json Directory Path (one table per file) sp://remotePath | URI=sp://Documents/folder1/file.json; User=user1; Password=password1; StorageBaseURL=https://subdomain.sharepoint.com; |
| SharePoint REST | Single File Path (One table)
sprest://remotePath/file.json Directory Path (one table per file) sprest://remotePath | URI=sprest://Documents/folder1/file.json; InitiateOAuth=GETANDREFRESH; AuthScheme=OAuth; StorageBaseURL=https://subdomain.sharepoint.com; |
| FTP or FTPS | Single File Path (One table)
ftp://server:port/remotePath/file.json ftps://server:port/remotepath/file.json Directory Path (one table per file) ftp://server:port/remotePath ftps://server:port/remotepath; | URI=ftps://localhost:990/folder1/file.json; User=user1; Password=password1; |
| SFTP | Single File Path (One table)
sftp://server:port/remotePath/file.json Directory Path (one table per file) sftp://server:port/remotePath | 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; AzureStorageAccount=myAccount; AzureTenant=tenant;
URI=adl://myAccount.azuredatalakestore.net@folder1/file.json; InitiateOAuth=GETANDREFRESH; AuthScheme=OAuth; AzureTenant=tenant; |
| AzureDataLakeStoreGen2 | abfs://myfilesystem/remotePath/file.json
abfs://[email protected]/remotepath/file.json
| URI=abfs://myfilesystem/folder1/file.json; AzureStorageAccount=myAccount; AzureAccessKey=myKey;
URI=abfs://[email protected]/folder1/file.json; AzureAccessKey=myKey; |
| AzureDataLakeStoreGen2 with SSL | abfss://myfilesystem/remotePath/file.json
abfss://[email protected]/remotepath/file.json
| URI=abfss://myfilesystem/folder1/file.json; AzureStorageAccount=myAccount; AzureAccessKey=myKey;
URI=abfss://[email protected]/folder1/file.json; AzureAccessKey=myKey; |
| Wasabi | Single File Path (One table)
wasabi://bucket1/remotePath/file.json Directory Path (one table per file) wasabi://bucket1/remotePath | URI=wasabi://bucket/folder1/file.json; AccessKey=token1; SecretKey=secret1; Region='us-west-1'; |
| Google Cloud Storage | Single File Path (One table)
gs://bucket/remotePath/file.json Directory Path (one table per file) gs://bucket/remotePath | URI=gs://bucket/folder1/file.json; InitiateOAuth=GETANDREFRESH; AuthScheme=OAuth; ProjectId=test; |
| Oracle Cloud Storage | Single File Path (One table)
os://bucket/remotePath/file.json Directory Path (one table per file) os://bucket/remotePath | URI=os://bucket/folder1/file.json; AccessKey='myKey'; SecretKey='mySecretKey'; OracleNameSpace='myNameSpace' Region='us-west-1'; |
| Azure File | Single File Path (One table)
azurefile://fileShare/remotePath/file.json Directory Path (one table per file) azurefile://fileShare/remotePath | URI=azurefile://bucket/folder1/file.json; AzureStorageAccount='myAccount'; AzureAccessKey='mySecretKey';
URI=azurefile://bucket/folder1/file.json; AzureStorageAccount='myAccount'; AzureSharedAccessSignature='mySharedAccessSignature'; |
| IBM Object Storage Source | Single File Path (One table)
ibmobjectstorage://bucket1/remotePath/file.json Directory Path (one table per file) ibmobjectstorage://bucket1/remotePath | URI=ibmobjectstorage://bucket/folder1/file.json; AuthScheme='HMAC'; AccessKey=token1; SecretKey=secret1; Region='eu-gb';
URI=ibmobjectstorage://bucket/folder1/file.json; ApiKey=key1; Region='eu-gb'; AuthScheme=OAuth; InitiateOAuth=GETANDREFRESH; |
| Hadoop Distributed File System | Single File Path (One table)
webhdfs://host:port/remotePath/file.json Directory Path (one table per file) webhdfs://host:port/remotePath | URI=webhdfs://host:port/folder1/file.json |
| Secure Hadoop Distributed File System | Single File Path (One table)
webhdfss://host:port/remotePath/file.json Directory Path (one table per file) webhdfss://host:port/remotePath | URI=webhdfss://host:port/folder1/file.json |
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 Fine-Tuning Data Access for more advanced connection settings: fine-tune the default data modeling settings, connect through a firewall, or troubleshoot connections.