Jet Analytics REST Data Source

With the Jet Analytics REST data source, you can connect to REST APIs. Many APIs have unique setups and specific behaviors — this article describes the settings available to connect to a wide variety of APIs.

Connect to REST APIs using various authentication methods such as Header options, Bearer Tokens, OAuth, and more. If the REST API contains multiple resources such as users, posts, and comments, you can connect to each as individual endpoints by providing the base URL. The data source includes pagination options to handle multi-page endpoints, and supports dynamic values for endpoints that require a date or an ID from another endpoint.

At the bottom of this article are worked examples showing how to connect to publicly accessible APIs, demonstrating specific authentication options, dynamic values, and pagination approaches.

Jet Analytics REST Data Source Settings

Add a data source in the Jet Analytics portal and select the Jet Analytics REST data source.

The Jet Analytics portal is managed by the insightsoftware Support team. For assistance or additional information, reach out to Product Support.

Enter a name and description for the data source.

Connection Settings

Enter the base URL for the API. The base URL is the root portion — for example, in https://gorest.co.in/public/v2/users, the base URL is https://gorest.co.in and /public/v2/users is the endpoint path used for the Users endpoint.

Import Swagger

This option becomes available once the data source has been mapped to an Ingest instance. Clicking the icon displays the Import Swagger menu.

The Import Swagger feature parses an OpenAPI definition and automatically creates settings and endpoints. There are three ways to provide a definition:

• URL: A URL pointing directly to the definition, accessed from the selected Ingest service.
• Local file path: A path to a file accessible from the Ingest service that contains the definition.
• JSON: Paste the definition directly into the window. This option has a size limit of 250,000 characters.

Click Next to process the definition, then select what to import on the following page.

Additional Configurations

Authentication

Click on the Authentication step to expand it.

Select one of the following authentication options from the dropdown. Note that API key-based authentication should be configured as a header instead.

• None — No authentication is applied.
• Basic — Authenticate using a username and password.

  

• Bearer — Authenticate using a non-expiring Bearer Token.

  

• OAuth2 — Authenticate using OAuth 2.0.

  

  Provide an Access URL that returns the token. Configure how the request is sent — either Credentials in the body or Basic authentication header. Choose how the Authorization token is returned — either in a Request header or a Request URL. Add parameters such as client_id and client_secret using the Add button. Each parameter can be marked as Is sensitive to obscure the value in previews.

  

• OAuth refresh token — Use when a refresh token is needed to maintain access.

  

  The setup is similar to OAuth2, with the addition of a Refresh token working directory path for storing refresh tokens. The Initial access token is the value of the initial access token. The Initial access token created is the UNIX timestamp in seconds for when the token was created. The Initial access token expires is the duration in seconds for which the initial access token is valid from creation.

• Authentication endpoint — Authenticate using an endpoint defined in the data source.

  

  Select an Endpoint from the dropdown. Only one endpoint can be selected, but it can depend on other endpoints which will be executed automatically. In the Endpoint data section, define sources of data to retrieve from the selected endpoint after it executes.

  The Name entered here is available as a dynamic value in the Apply data section. The Type is either XPath or Header. When using XPath, data is extracted from the result body or from flattened data — select a Table flattening name if required.

  The Manipulation query can alter the data extracted in Endpoint data. This is a SQL query running in SQLite, and endpoint data can be referenced using curly braces — for example: select '{MyHeaderValue} some change' as AlteredMyHeaderValue.

  The Apply data section defines where and how the endpoint data values are used:

  • Apply as dynamic values: Makes the named endpoint data and manipulation query columns available as dynamic values in all other endpoints.
  • Add as header: Adds all listed headers to all requests in all other endpoints.
  • Add as query parameters: Adds all listed parameters to all requests in all other endpoints.

  Behavior defines when the authentication endpoint executes:

  • Run once and on demand: Runs before the first request and re-executes only if a request returns a 401. Sufficient for most use cases.
  • Run every x minutes: Runs before the first request and then at the specified interval during execution.
  • Run before every request: Runs before the first request and before every subsequent request.
• Azure service principal (certificate) — Specialized authentication for Azure service principals that use certificates instead of a client secret.

  

Headers

Add all headers the API requires to return the desired output.

Pagination

Many APIs limit the number of rows returned per request. Pagination settings ensure all rows are retrieved.

• Parameters — Add the necessary parameters such as page number or page size.

  

  Set the parameter type to XPath (e.g. /meta/pagination/page) or Header (e.g. links-next). If any parameter is not found, pagination stops.

• Manipulation Query — Create queries that return values for use in parameters, URLs, or request bodies.
• SQL Expression — Create a SQL query that returns a value. Requires a connection string, username, and password.
• Target type — Set SQL or Snowflake as the target to run the query against.

  

• Connection string — Enter the connection string. Username and password can be included inline or in separate fields.

  For SQL:

  server=<sql servername>;database=<database name>

  For Snowflake (use the account identifier, not the account name):

  account=<account identifier>;user=<xxxxxx>;password=<xxxxxx>;db=<DBNAME>;

• Built-in value options — Two built-in values assist with incrementing pagination: {TX_NextPage} and {TX_CurrentPage}. Both can be used as query parameter values (e.g. page={TX_NextPage}) or in SQL expressions (e.g. SELECT {TX_CurrentPage} * 1000 AS offsetValue). When both are used on the same page and {TX_NextPage} equals 2, then {TX_CurrentPage} equals 1.
• Apply parameters — Defines what to do with the value returned by a parameter or SQL Expression:
  • Don't do anything — No action is taken.
  • Replace URL — Replaces the current URL with the returned value.

    

  • Add as query parameter — Appends the value (e.g. a page number) to the URL.

    

  • Replace post body — Configures the post body for all subsequent requests. Must be configured per endpoint.

Certificates

Add a certificate to all requests, typically used instead of standard authentication methods such as bearer tokens. Supported file types are .pfx and .pem. The password field is optional.

Connection variables

Connection variables are values available for use in each endpoint, similar to how dynamic values work within a single endpoint. If a connection variable is marked as Is Sensitive, the value cannot be used in queries or table flattening.

Table flattening

Table flattening configurations added here are applied to all endpoints. The dynamic value {TX_EndpointName} can be used to insert the endpoint name into the flattening name or into the XSLT itself, which is useful when transformations need to differ between endpoints.

Global table flattening can be overridden at the endpoint level by selecting Only use endpoint table flattenings on the relevant endpoint.

Caching

Caching options control how downloaded data is handled. When In Memory is selected, all downloaded data and processing (such as XSLT) occurs in memory. For large datasets, this can exhaust available memory — switch to File type caching if this occurs.

File-based caching also helps identify the output being returned and assists troubleshooting during initial setup. Specifying a cache path stores all files in a folder named after the data source.

Request limits

Some APIs impose call rate limits. This setting manages API call frequency by setting limits per second, minute, hour, or day.

Culture

The culture setting affects how number and date formats are interpreted.

Misc

Configure retries and timeouts for API calls as needed.

Debug Logging

When enabled, the data source writes a detailed log file to disk at the specified path. This log is useful for understanding the behaviour of complex endpoints and for providing detailed diagnostic information to support. Enable only when needed, as log files can become very large.

Once enabled, specify the folder path for the log file. The file is named after the data source and the time the task started.

Endpoints

An endpoint is a specific part of the API being accessed. It is the path that follows the base URL. Most APIs document their available endpoints. At least one endpoint must be added for the data source to function.

When you enter the path of an endpoint (e.g. API/users), the name is automatically set from the path (e.g. users). This can be edited afterward to something more descriptive, such as All Users.

Click Add endpoint to configure the endpoint further.

Data Format

Data Format defines what format the REST endpoint returns. The following options are available:

• Auto: Automatically determines whether the format is JSON or XML. If neither can be identified, defaults to Text.
• JSON: Forces the data source to read the data as JSON.
• XML: Forces the data source to read the data as XML.
• CSV: Forces the data source to read the data as CSV. Additional CSV-specific settings appear when this option is selected.

  

• Text: Forces the data source to read the data as plain text. The entire content is saved as a single-row, single-column result without modification.

Dynamic values

There are three dynamic value source options.

The Query option loops over a set of values. Each row in the dynamic values results in one REST call (or multiple calls if pagination is involved). Reference column names using curly brackets — for example, {MyColumn}. Dynamic values can be used in Query Parameters (value), Post Body, Headers (name and value), Pagination (SQL Expression, Replaced URL, and Replaced Post Body), Endpoint Path, and Table Flattening (XSLT).

Target type — Set SQL or Snowflake as the target to run the query against.

Connection string — Enter the connection string. For SQL:

server=<sql servername>;database=<database name>

For Snowflake:

account=<account identifier>;user=<xxxxxx>;password=<xxxxxx>;db=<DBNAME>;

The example below uses a SQL query to set dynamic values using yesterday's date as an endpoint value.

The endpoint path references the query result column name in curly brackets (e.g. {yesterday}) — as long as the SQL query returns a column with that name, it is applied. For a date range (start and end date), ensure the SQL query returns two separate date columns.

Changing the Dynamic values source to Endpoint query allows you to select one or more Source Endpoints (and optionally a table flattening). The data from these endpoints can then be queried using a SQLite query, and the result is used as dynamic values. All columns are nvarchar and table names match those shown in Jet Analytics Data Integration.

Changing the source to From the endpoint table lets you choose an already-added data source endpoint. The column name referenced in curly brackets must match exactly — including case — a field in the chosen endpoint.

Query Parameters

Add additional values appended to the URL, such as an access key. Parameters are added in the format ?access_key=<value>, with additional parameters separated by &.

Table flattening

A table flattening is an XSLT transformation used to transform — most commonly to flatten — the data returned from the API. Use the Table Builder to generate an XSLT document. The name assigned to the flattening becomes the table name.

See Table Builder for Data Sources for full instructions.

HTTP request

• HTTP method — Set to GET, POST, or PUT. When set to POST, a post body must be configured.

Search and replace

Perform one or more regex replace operations on the raw data before it is loaded or used to interpret metadata. Useful when the data contains special characters that are not handled correctly.

Override headers

Override existing headers with values specific to this endpoint.

Override pagination

Override the data source-level pagination settings for this endpoint specifically.

Misc

• Perform exhaustive metadata scan: When checked, all pages and all dynamic values are executed during metadata operations. If unchecked, only the first page and first dynamic value are executed.
• Use endpoint name as schema name: Adds the endpoint name as the schema name. Beneficial when the data source returns identically structured data from different endpoint paths. Be aware that enabling this after the initial synchronize task has run will generate a new table.
• Empty fields as null: When enabled, empty field values are converted to null.
• Disable URL encoding of query parameters: When enabled, query parameters are not encoded when added to the REST request.

Override caching

Override the data source-level caching settings for this endpoint specifically.

Delay

Configure delays for an endpoint. Delay between requests in milliseconds adds a delay between each request. Delay before first request in milliseconds adds a delay before the first request. An empty or zero value means no delay.

Real examples for the Jet Analytics REST data source

Dynamic value from SQL query

The following article demonstrates how to connect to the Fixer IO API, using SQL queries and XSLT to make the most of the available options.

Connect to Fixer IO

Dynamic value from endpoint table

The following article demonstrates how to connect to the Jet Analytics API, showing how to use dynamic values from another endpoint and how to apply a header for authorization.

Connect to the Jet Analytics API

Applying pagination, request limits, and using a bearer token for authentication

The following article demonstrates how to connect to the GoREST API, showing how to configure header-based pagination, set request limits, and apply a bearer token for authentication.

Connect to GoREST

Using OAuth 2.0 client authentication and token-based pagination

The following article demonstrates how to connect to the Microsoft Graph API, showing how to configure URL-based pagination from a result field and how to use OAuth 2.0 client authentication.

Connect to Graph API

Using {TX_NextPage} for pagination when no next or last page is known

The following article demonstrates how to connect to the openbrewerydb API, showing how to configure page-and-page-size pagination when no last or next page information is available.

Connect to openbrewerydb

Using {TX_NextPage} for pagination when current page and max pages are in the result

When the API returns both a CurrentPage and MaxPages field, pagination should stop when these two values are equal.

Use the XPath not() function to negate the equality check. The following XPath expression locates the CurrentPage element and stops pagination when it equals the sibling MaxPages value:

/*/*/CurrentPage[not(. = ../MaxPages)]

This evaluates as: find CurrentPage nodes where the text content does not equal that of the sibling MaxPages node. This results in the following pagination setup:

Using {TX_CurrentPage} for pagination when no last offset or next starting point is known

The following article demonstrates how to connect to the soda.demo.socrata.com API, showing how to configure offset-and-limit pagination when no information about the final offset value is available.

Connect to soda Socrata