Connecting to REST Data Services

Concept Information

ACCESSDATA command

REpresentational State Transfer (REST) is an architectural style for standardizing communication between computer systems on the web. Rest-compliant, or RESTful, systems are able to communicate with each other easily.

You can load data from RESTful systems into Analytics by hitting endpoints that use GET methods. Other types of HTTP connections like POST and PUT, which are designed to send data to a resource, are not supported.

Caution

In Analytics 15, the REST connector is enhanced to support more authentication methods and corresponding connection fields are also updated. However, scripts from previous versions will not work in this version after upgrade. If you upgrade to the Analytics 15 version, you must re-configure the connector to connect to a RESTful system. If you have previous versions of REST connector scripts running in Robots or Analytics Exchange, you must upload the re-configured scripts after upgrading to Analytics 15.

Before you start

To connect Analytics to a RESTful system, you need the following:

  • The URI endpoint of the RESTful system you want to connect to.
  • Connection credentials for that system, if they are required. In some cases, that will simply be a username and password. Some systems use more substantial credentials like OAuth. If you are not sure what credentials you need, contact the administrator of your REST service in your company. If your administrator cannot help you, consult the support services or help content for the system you want to connect to.

Create a REST connection

  1. From the Analytics main menu, select Import > Database and application.
  2. From the New Connections tab, in the ACL Connectors section, select REST.

    Tip

    You can filter the list of available connectors by entering a search string in the Filter connections box. Connectors are listed alphabetically.

  3. In the Data Connection Settings panel, enter the connection settings and at the bottom of the panel, click Save and Connect.

    You can accept the default Connection Name, or enter a new one.

The connection for REST is saved to the Existing Connections tab. In the future, you can reconnect to REST from the saved connection.

Once the connection is established, the Data Access window opens to the Staging Area and you can begin importing data. For help importing data from REST, see Working with the Data Access window.

Connection settings

Basic settings

Setting Description Example
URI

The Uniform Resource Identifier (URI) or absolute file path of the RESTful resource.

https://jsonplaceholder.typicode.com/users/
Format Specifies whether the data is in XML or JSON format. JSON
Authorization Type

The scheme used for authentication. Authentication types available are as follows:

  • No Auth The driver uses no authentication.
  • API Key The driver uses an API key for authentication.
  • Bearer Token The driver uses a bearer token for authentication.
  • Basic Auth The driver uses a basic authentication with a username and password.
  • OAuth 1.0 The driver uses OAuth 1.0 for authentication.
  • OAuth 2.0 The driver uses OAuth 2.0 for authentication.
No Auth
Key

Specifies the API key to authenticate.

This field is enabled when you select API Key in the Authorization Type field.

 
Value

The value in this field is used with the API Key provided to authenticate to the server.

This field is enabled when you select API Key in the Authorization Type field.

 
Add To Specifies whether to append the API key-value pair in the request header or query parameters. Header
Token

Specifies the bearer token to authenticate to the server. The token can be an access key, such as a JSON Web Token (JWT) that is included in the request header.

This field is enabled when you select Bearer Token in the Authorization Type field.

 
User

The username that will be used to connect to a REST data source.

This field is enabled when you select Basic Auth in the Authorization Type field.

jgibbons
Password

The password for the username to connect to a REST data source.

This field is enabled when you select Basic Auth in the Authorization Type field.

 
OAuth Authorization URL The authorization URL for the OAuth service. https://login.example.com/services/oauth2/authorize
OAuth Access Token URL The URL to retrieve the OAuth access token from. In OAuth 1.0, the authorized request token is exchanged for the access token at this URL. https://login.example.com/services/oauth2/access
OAuth Request Token URL The URL to retrieve request tokens from. https://login.example.com/services/oauth2/token
OAuth Client Id The client ID assigned when you register your REST data source with an OAuth authorization server. ZYDPLLBWSK3MVQJSIYHB1OR2JXCY0X2C5UJ2QAR2MAAIT5Q
OAuth Client Secret The client secret assigned when you register your REST data source with an OAuth authorization server.  
OAuth Refresh Token URL The URL to refresh the OAuth token from. In OAuth 2.0, this URL is where the refresh token is exchanged for a new access token when the old access token expires. https://login.example.com/services/oauth2/refresh
OAuth Grant Type

The grant type for the OAuth flow. Options available are as follows:

  • CODE

  • CLIENT

  • PASSWORD

CODE
Callback URL The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings. https://www.example.com/api/billing/123

Advanced settings

Setting Description Example
Custom Headers

This property can be set to a string of HTTP headers to be appended to the HTTP request headers created from other properties, like Content-Type, From, and so on.

The headers must be of the format "header: value" as described in the HTTP specifications. Each header should go on its own line.

Use this option with caution. If it contains invalid headers, HTTP requests might fail. This property is useful for fine-tuning to integrate with specialized or nonstandard APIs.

Content-Type: text/html; charset=utf-8

Connection: keep-alive

Custom Url Params The custom query string to be included in the request. The parameters must be encoded as a query string. The values in the query string must be URL encoded. field1=value1&field2=value2&field3=value3
Access Key Your AWS account access key. This value is accessible from your AWS security credentials page. AKIAIOSFODNN7EXAMPLE
Secret Key Your AWS account secret key. This value is accessible from your AWS security credentials page. wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY
Region The hosting region for your Amazon Web Services. NORTHERNCALIFORNIA
Other

Hidden properties needed only in specific use cases. Normally, you do not need to enter anything here. Specify multiple properties in a semicolon-separated list.

Caching Configuration

  • CachePartial=True Caches only a subset of columns, which you can specify in your query.
  • QueryPassthrough=True Passes the specified query to the cache database instead of using the SQL parser of the driver.

Integration and Formatting

  • SupportAccessLinkedMode In Access' linked mode, it is generally a good idea to always use a cache as most data sources do not support multiple Id queries. However if you want to use the driver in Access but not in linked mode, this property must be set to False to avoid using a cache of a SELECT * query for the given table.
  • DefaultColumnSize Sets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000.
  • ConvertDateTimeToGMT Whether to convert date-time values to GMT (UTC), instead of the local time of the machine.
  • RecordToFile=filename Records the underlying socket data transfer to the specified file.

OAuth Properties

  • InitiateOAuth Set this property to initiate the process to obtain or refresh the OAuth access token when you connect. The following options are available:

    • OFF Indicates that the OAuth flow will be handled entirely by you. An OAuthAccessToken will be required to authenticate.

    • GETANDREFRESH Indicates that the entire OAuth flow will be handled by the driver. If no token currently exists, it will be obtained by prompting you via the browser. If a token exists, it will be refreshed when applicable.

    • REFRESH Indicates that the driver will only handle refreshing the OAuthAccessToken. You will never be prompted by the driver to authenticate via the browser. You must handle obtaining the OAuthAccessToken and OAuthRefreshToken initially.

  • OAuthSettingsLocation The location of the settings file where OAuth values are saved when InitiateOAuth is set to true. When InitiateOAuth is enabled, the driver saves OAuth values to a settings file to avoid requiring the user to manually enter OAuth connection properties. When your OAuth session expires, the driver will automatically obtain a new access token if InitiateOAuth is set. If InitiateOAuth is enabled but OAuthSettingsLocation is not defined, the driver uses a default settings file, %AppData%\CData\REST Data Provider\OAuthSettings.txt on Windows. On macOS, this file is located in ~/Library/Application Support/CData/REST Data Provider/OAuthSettings.txt. On Linux, ~/cdata/.config.

  • OAuthAcccessToken The OAuthAccessToken is retrieved from the OAuth server as part of the authentication process. It has a server-dependent timeout and can be reused between requests.The access token is used in place of your username and password. The access token protects your credentials by keeping them on the server.

  • OAuthAccessTokenSecret The OAuthAccessTokenSecret is retrieved from the OAuth server as part of the authentication process. It is used with the OAuthAccessToken and can be used for multiple requests until it times out.

  • OAuthRefreshToken The OAuthRefreshToken property is used to refresh the OAuthAccessToken when using OAuth authentication.

CachePartial=True; QueryPassthrough=True
Data Model

Specifies the data model to use when parsing JSON or XML documents and generating the metadata.

  • Document A single table representing a row per document will be returned. In this data model, any nested documents (object arrays) will not be flattened and will be returned as aggregates. Unless a XPath value is explicitly specified, the driver will identify and use the top-most document (object array) found as the XPath.
  • FlattenedDocuments A single table representing a JOIN of the available documents in the JSON or XML file will be returned. In this data model, nested XPath values will act in the same manner as a SQL JOIN. Additionally nested sibling XPath values (child paths at the same height), will be treated as a SQL CROSS JOIN. Unless explicitly specified, the driver will identify the XPath values available by parsing the JSON or XML file and identifying the available documents (including nested documents).
  • Relational Multiple tables will be returned, one for each XPath value specified. In this data model, any nested documents (object arrays) will be returned as relational tables with primary and foreign keys. Unless explicitly specified, the driver will identify the XPath values available by parsing the JSON or XML file and identifying the available documents (including nested documents).
Document
Data Source This property specifies a URI for the REST resource location. s3://remotePath/file.json
Flatten Arrays

By default, nested arrays are returned as strings of JSON or XML. The FlattenArrays property can be used to flatten the elements of nested arrays into columns of their own. Set FlattenArrays to the number of elements you want to return from nested arrays.

Setting FlattenArrays to -1 will flatten all the elements of nested arrays.

1
Flatten Objects Set FlattenObjects to true to flatten object properties into columns of their own. Otherwise, objects nested in arrays are returned as strings of JSON or XML. true
JSON Format Specifies the format of the JSON document. JSON
Generate Schema Files

Specifies whether to generate a schema file (RSD) from the parsed document.

  • Never A schema file (RSD) will never be generated.
  • OnUse Aschema file (RSD) will be generated the first time a table is referenced, provided the schema file (RSD) for the table does not already exist.
  • OnStart A schema file (RSD) will be generated at connect time for any tables that do not currently have a schema file (RSD).

This property is used in conjuction with Format, URI, XPath, and Location.

Never
XPath

The XPath of an element that repeats at the same height within the XML/JSON document (used to split the document into multiple rows).

Multiple paths can be specified using a semi-colon separated list. The DataModel setting allows you to configure how the XPath values will be used to create tables and display data.

$.store.book[0].title
Kerberos Keytab File The Keytab file containing your pairs of Kerberos principals and encrypted keys. /path_to_keytab_file/filename.keytab
Kerberos SPN If the service principle name (SPN) on the Kerberos Domain Controller is not the same as the URL that you are authenticating to, use this property to set the SPN. HTTP/TimeOffWebPortal
Row Scan Depth

The number of rows to scan when dynamically determining columns for the table. Columns are dynamically determined when a schema (RSD) file is not available for the table, such as when using GenerateSchemaFiles. Higher values will result in a longer request, but will be more accurate.

Setting this value to 0 (zero) will parse the entire document.

100
SSL Server Cert

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 will be rejected.

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

Use '*' to signify to accept all certificates (not recommended for security concerns).

  • -----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE-----
  • C:\cert.cer
  • -----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY-----
  • ecadbdda5a1529c58a1e9e09828d70e4
  • 34a929226ae0819f2ec14b4a3d904f801cbb150d

 

Limit Key Size

The maximum length of a primary key column.

In some ODBC tools, the length of the primary key column cannot be larger than a specific value. This property makes the ODBC Driver override the reported length of all the primary key columns. It is especially useful when using the ODBC Driver as a Microsoft Access Linked Data Source.

Setting the LimitKeySize to zero will make the key length revert to the original length.

255
Map To Long Varchar

This property controls whether or not a column is returned as SQL_LONGVARCHAR.

Some applications require all text data larger than a certain number of characters to be reported as SQL_LONGVARCHAR. Use this property to map any column larger than the specified size so they are reported as SQL_LONGVARCHAR instead of SQL_VARCHAR.

-1
Map To WVarchar This property controls whether or not string types map to SQL_WVARCHAR instead of SQL_VARCHAR. It is set by default. true
Pseudo Columns

Indicates whether or not to include pseudo columns as columns to the table.

This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column.

You can use an asterisk (*) character to include all tables and all columns. For example: *=*.

Table1=Column1, Table1=Column2, Table2=Column3

Upper Case Identifiers Report all identifiers in uppercase. This is the default for Oracle databases and thus allows better integration with Oracle tools such as the Oracle Database Gateway. false
Proxy Auth Scheme

This value specifies the authentication type to use to authenticate to the HTTP proxy specified by ProxyServer and ProxyPort.

Note that the driver will use the system proxy settings by default, without further configuration needed; if you want to connect to another proxy, you will need to set ProxyAutoDetect to false, in addition to ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.

The authentication type can be one of the following:

  • BASIC The driver performs HTTP BASIC authentication.
  • DIGEST The driver performs HTTP DIGEST authentication.
  • NONE
  • NEGOTIATE The driver retrieves an NTLM or Kerberos token based on the applicable protocol for authentication.
  • NTLM
  • PROPRIETARY The driver does not generate an NTLM or Kerberos token. You must supply this token in the Authorization header of the HTTP request.
BASIC
Proxy Auto Detect This indicates whether to use the system proxy settings or not. Set ProxyAutoDetect to FALSE to use custom proxy settings. This takes precedence over other proxy settings. true
Proxy User A user name to be used to authenticate to the ProxyServer proxy. jgibbons
Proxy Password A password to be used to authenticate to the ProxyServer proxy. UsaPhone897Batteries!Tokyo
Proxy Server The hostname or IP address of a proxy to route HTTP traffic through. 192.168.1.100
Proxy Port The TCP port the ProxyServer proxy is running on. 80
Proxy SSL Type

The SSL type to use when connecting to the ProxyServer proxy.

  • AUTO Default setting. If the URL is an HTTPS URL, the driver will use the TUNNEL option. If the URL is an HTTP URL, the component will use the NEVER option.
  • ALWAYS The connection is always SSL enabled.
  • NEVER The connection is not SSL enabled.
  • TUNNEL The connection is through a tunneling proxy: The proxy server opens a connection to the remote host and traffic flows back and forth through the proxy.
AUTO
Proxy Exception

A semicolon separated list of hosts or IPs that will be exempt from connecting through the ProxyServer .

The driver will use the system proxy settings by default, without further configuration needed. If you want to explicitly configure proxy exceptions for this connection, you will need to set ProxyAutoDetect to false, and configure ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.

172.16.254.1;192.0.2.1