API
Version 23.4.8843
Version 23.4.8843
API
The CData Sync API connector provides an easy and fully customizable way to connect directly to an API. After you establish a connection, use the built-in wizard to configure your API response data in the form of tables that you can use later in a job.
Add the API Connector
To enable Sync to use data from API, you first must add the connector, as follows:
-
Open the Connections page of the Sync dashboard.
-
Click Add Connection to open the Select Connectors page.
-
Click the Sources tab and locate the API row.
-
Click the Configure Connection icon at the end of that row to open the New Connection page. If the Configure Connection icon is not available, click the Download Connector icon to install the API connector. For more information about installing new connectors, see Connections.
Authenticate to API
After you add the connector, you need to set the required properties.
On the New Connection page, enter the connection name of your choice.
CData Sync supports authenticating to API in several ways. Select your authentication method below to proceed to the relevant section that contains the authentication details.
None
To connect without authentication, select None for Auth Scheme. No additional properties are required.
Basic
To connect with your user credentials, specify the following properties:
-
Auth Scheme – Select Basic.
-
User - Enter the username that you use to authenticate to your API account.
-
Password - Enter the password that you use to authenticate to your API account.
Digest
To connect with your user credentials, specify these settings:
-
Auth Scheme - Select Digest.
-
User - Enter the username that you use to authenticate to your API account.
-
Password - Enter the password that you use to authenticate to your API account.
Bearer Token
To connect with an OAuth access token, specify these settings:
-
Auth Scheme - Select Bearer Token.
-
OAuth Access Token - Enter the access token that you use for connecting when you use OAuth.
OAuth
To connect with OAuth custom credentials, specify the following properties:
-
Auth Scheme – Select OAuth.
-
OAuth Client Id - Enter the client Id that you were assigned when you registered your application with an OAuth authorization server.
-
OAuth Client Secret - Enter the client secret that you were assigned when you registered your application with an OAuth authorization server.
-
OAuth Authorization URL - Enter the OAuth authorization URL that you need to authorize request tokens when you sign in.
-
OAuth Access Token URL - Enter the URL where the request for the access token is made.
Note: In OAuth 1.0 the authorized request token is exchanged for the access token.
-
OAuth Refresh Token URL - Enter the URL where the refresh token is exchanged for a new access token when the old one expires.
Note: For your data source, this URL might be the same as the access-token URL.
-
Scope (optional) - Enter the scope that is required in order to obtain the initial access token and the refresh token.
OAuthPassword
To connect with OAuth custom credentials, specify the following properties:
-
Auth Scheme – Select OAuthPassword.
-
User - Enter the username that you use to authenticate to your API account.
-
Password - Enter the password that you use to authenticate to your API account.
-
OAuth Client Id - Enter the client Id that you were assigned when you registered your application with an OAuth authorization server.
-
OAuth Client Secret - Enter the client secret that you were assigned when you registered your application with an OAuth authorization server.
-
OAuth Access Token URL - Enter the URL where the request for the access token is made.
Note: In OAuth 1.0 the authorized request token is exchanged for the access token.
-
OAuth Refresh Token URL (optional) - Enter the URL where the refresh token is exchanged for a new access token when the old one expires.
Note: For your data source, this URL might be the same as the access-token URL.
Scope (optional) - Enter the scope that is required in order to obtain the initial access token and the refresh token.
OAuthClient
To connect with OAuth custom credentials, specify the following properties:
-
Auth Scheme – Select OAuthClient.
-
OAuth Client Id - Enter the client Id that you were assigned when you registered your application with an OAuth authorization server.
-
OAuth Client Secret - Enter the client secret that you were assigned when you registered your application with an OAuth authorization server.
-
OAuth Access Token URL - Enter the URL where the request for the access token is made.
Note: In OAuth 1.0 the authorized request token is exchanged for the access token.
-
OAuth Refresh Token URL (optional) - Enter the URL where the refresh token is exchanged for a new access token when the old one expires.
Note: For your data source, this URL might be the same as the access-token URL.
-
Scope (optional) - Enter the scope that is required in order to obtain the initial access token and the refresh token.
Complete Your Connection
To complete your connection:
-
For Test URL, enter a URL in order to verify the authentication scheme.
-
Define global headers that apply both to your connection and to any tables that you create. For detailed steps, see Add Headers.
-
Specify, if necessary, any additional settings for the Other property (under the Miscellaneous section). This property is not required in normal use and functionality. However, if it is required, enter a semicolon-separated list of additional settings that are required for your connection.
-
Define advanced connection settings on the Advanced tab. (In most cases, though, you should not need these settings.)
-
If you authenticate with OAuth, click Connect to API to connect to your API account.
-
Click Create & Test to create your connection.
After you successfully create your connection, you can add tables, pseudocolumns, filters, and pagination, as described in these sections:
Add Headers
The Add Headers section on the connection page enables you to define global headers that apply both to your connection and to any tables that you create.
To add HTTP headers that are sent with all API calls, specify the following properties:
-
Name - Enter the header name in the Name field (for example, Token).
-
Value - Enter a value for the header (for example, a token).
If you want to add additional headers, click Add Header and specify the header name and value.
Add Tables
After you connect to your API, you can create, view, and configure the API data in the form of tables.
To create a table:
-
Open your API connection and click the Tables tab to open the New Table page.
-
Click Add Table to begin creating a new table.
-
In the Request URL section:
-
Select the request method (GET or POST) from the Request URL list.
-
Enter the URL of your API endpoint in the blank field to the right of the method that you selected, as shown in this example:
Note: If you select POST as your method, you must supply both the body and the content type.
-
-
Select an option for Format (either JSON or XML).
-
Add URL parameters that will be sent with all API requests for this table.
To add parameters:
-
Click Add Parameter on the Parameters tab.
-
Enter a parameter name (for example, Country) in the Name field.
-
Enter a parameter value (for example, United States) in the Value field.
To add additional parameters, repeat the steps above.
-
-
Add HTTP headers that are sent with all API requests for your table. These headers are in addition to those that you add when you configure the API connector itself.
To add headers:
-
Click Add Header on the Headers tab.
-
Enter a name (for example, Year) in the Name field.
-
Enter a value (for example, 2020) in the Value field.
To add additional headers, repeat the steps above.
-
-
In the Table Details category, click the Configure Table button. This action opens the Configure Table dialog box where you can preview the API response, as shown in this example:
-
Click Next.
-
Select at least one repeat element for your table. A repeat element is the path of the element in the API response that contains repeating items that are to be used as rows. Sync returns each detected array in the response data, which allows you to choose multiple paths. In this example, source is the repeat element.
While Sync tries to intelligently determine the possible repeat elements, it cannot detect them in certain scenarios. Such cases require that you set a custom Repeat Element path, as follows:
-
Click Use Custom Repeat Element (upper right of the dialog box).
-
Click Add Repeat Element.
-
Enter the path of the element that you want to repeat.
-
-
Click Next.
-
Select the specific columns that you want to include.
To include all columns, click Select All at the upper right of the columns list.
-
Click Next. This opens a Preview Table tab in the dialog box where you can see the table with the columns that you selected.
-
Enter a name for your table in the Table Name field.
-
Click Confirm to save the selections for your table.
-
Click Save (upper right corner of the page of the New Table page) to save a link to your table on the API connector page.
Add Pseudocolumns
A pseudocolumn imitates the behavior of a table column, but the pseudocolumn is not stored physically in your source table or API. These columns enable you modify the requests that are sent to your API (via an SQL WHERE clause), but the values will not be replicated to your destination table.
To create a pseudocolumn in Sync:
-
From your API connector, select Tables > Tasks.
-
Navigate to the Table Details section at the bottom of the Tasks page and click the Pseudocolumns tab.
-
Click Add Pseudocolumn.
-
Enter a name for your pseudocolumn in the COLUMN NAME text box. Then, select an appropriate data type from the DATA TYPE list.
You can choose to output a pseudocolumn within the result of your API table by selecting the Output check box, as shown in the image above. In addition, you can select the Required check box if you want to mandate the inclusion of your pseudocolumn in the WHERE clause of your task. Then, if the task does not include the pseudocolumn in the WHERE clause, Sync generates an error message similar to this example:
To delete a pseudocolumn, click the Delete icon at the end of the pseudocolumn row.
-
Click Save.
Add Filters
After you create a table with columns, you can create filters that define the behavior of the API connector when a WHERE clause is issued during a query. Filters improve performance by delegating the filtering process to the API on the server side. If you do not include filters, the query options are processed by Sync instead.
Creating a Filter
You can create filters for each column in your table.
To create a filter:
-
Click the Filters tab on the New Table page, as shown below.
-
Click Add Filter under the Columns list.
-
Select the column (from the Columns list) that you want to filter.
-
Select criteria from the Operator list. You can choose from these options:
-
Is Equal to
-
Is Not Equal to
-
Is Less Than
-
Is Less Than or Equal to
-
Is Greater Than
-
Is Greater Than or Equal to
-
-
Select a type (Parameter or Header) from the Type list. Then enter the name of the parameter or header if it does not match the column name.
-
Click Save (top right of the page) to save your filter.
The following example illustrates how to create a filter that processes a query for filtering records in the Reports table by the date that the record was created.
Example Query
SELECT * FROM Reports Where CreatedDate > 2022-12-31
If no filter is defined for this table, the connector processes this filter in memory by reading all the rows from the API and by filtering the list in memory by the specified CreatedDate column. However, the API for this example supports this type of filtering on the server side, which can improve performance substantially.
The following example shows what this query looks like to the API:
https://www.mycustomapi.org/api/reports?$startdate=2022-12-31
To specify how the connector should send this filter to the API, you need to specify the following fields:
-
Column: CreatedDate
-
Operator: Greater Than
-
Filter Type: Parameter
-
Parameter Name: startdate
These specifications instruct the connect to include the startDate URL parameter with the filter value in the API request when a Greater Than operator is specified for the CreatedDate column.
Using the Request Per Row Feature to Filter Data
In the Sync application, you can now subset your data by using the Request Per Row feature. Request Per Row enables Sync to pass separate requests for each filter value by using an SQL IN clause. This feature is useful when you query a child dataset and that query requires the identifier (Id) of the parent dataset. For example, you might have an invoice dataset (parent) from which you want to split out line items (child) as separate rows into a single destination table. The following query dynamically passes separate requests for an invoice Id value:
REPLICATE [InvoiceLineItems] SELECT * FROM [InvoiceLineItems] WHERE InvoiceID IN ('1', '2', '3')`
In addition, you can use an inner SELECT query in the filter to pass a dynamic list of invoice Ids:
REPLICATE [InvoiceLineItems] SELECT * FROM [InvoiceLineItems] WHERE InvoiceID IN (SELECT Id FROM [Invoices])
To activate the Request Per Row feature:
-
Modify the request URL to include the name of the unique record-identifier column inside curly braces. After you add the column name in curly braces, the message Request Per Row Detected is displayed above the right end of the Request URL text box.
-
Click Configure Table.
-
Enter an example value for the unique record-identifier column.
-
Follow steps 7-13 in the “Add Tables” section.
Note: Ensure that a column or pseudocolumn exists that matches the unique record-identifier column (case-sensitive). If they do not match, Sync displays a warning.
-
Click Save (top right of the New Tables page).
Add Pagination
Sync supports multiple types of paging implementations. To add a pagination type:
-
Click the Pagination tab.
-
Select a pagination type from the Type list. You can choose from these types:
-
None - Select this type if the API request returns the full data set in one response.
-
Offset - Select this type when the API request provides a record offset parameter for paging. For more details, see Offset Type.
-
Number - Select this type when the API request includes a parameter to specify the current page number. For more details, see Number Type.
-
Token - Select this type when the API response includes a token for the next page. For more details, see Token Type.
-
URL - Select this type when the API response includes a URL for the next page. For more details, see URL Type.
-
-
Click Save (top right of the page) to save your pagination setting.
Offset
When you select Offset, you must specify the following properties:
-
Offset Parameter - Enter the name of the URL parameter that defines the offset in the API request.
-
Page Size - Enter the number of records that you want to retrieve per page. The API connector uses this value to calculate the offset.
-
Page Size Parameter (optional) - Enter the name of the URL parameter that defines how many records to retrieve per page. If there is no parameter to control page size, the Page Size Parameter does not need to be set. However, in that case, you must set Page Size to the default page size.
Example
In the following request, Offset Parameter is set to pageOffset, Page Size Parameter is set to pageSize, and Page Size is set to 1000. This example shows the request for page 6 of the data because the connector automatically increments the offset.
https://myapi?pageOffset=5000&pageSize=1000
Number
When you select Number, you must also specify the following settings:
-
Page Number Parameter - Enter the name of the URL parameter that defines the page number.
-
Page Size Parameter (optional) - Enter the name of the URL parameter that defines the page size.
-
Page Size (optional) - Enter the number of records that you want to retrieve per page.
Example
In the following request, Page Number Parameter is set to pageNum, Page Size Parameter is set to pageSize, and Page Size is set to 1000. This example shows the request for page 6 of the data because the connector increments the page automatically.
https://myapi?pageNum=6&pageSize=1000
Token
When you select Token, you must specify the following settings:
-
Token Path - Enter the path in the API response that defines the next-page token.
-
Has More Path (optional) - Enter the path in the API response that defines whether records are available.
-
Token Source - This setting determines whether the token should be sent in the request as a URL parameter or whether it should be sent in the request body. Select the option that you want, as specified below:
-
Request Path - Enter the path in the request body where Sync should set the paging token.
-
URL Parameter - Enter the name of the URL parameter that Sync should pass to the paging token.
-
Example Using the URL Parameter
In the following request, URL Parameter is set to pageToken, and Token Path is set to /results/nextpagetoken.
Request: https://myapi?pageToken=123456
Response:
```
{
"results": [
{
"rows": [
{
"id": "123",
"name": "Acme",
"country": "United States",
"no_employees": 500
},
. . .*more code lines*. . .
],
"nextpagetoken": 123457
}
]
}
```
<a name=”request-path-example></a>Example Using the Request Path
In the following request, Request Path is set to /request/pageToken, Token Path is set to /results/nextpagetoken, and Has More Path is set to /results/morePages.
Request Body
{
"request": [
{
"country": "United States",
"pageToken": 123456
}
]
}
Response
{
"results": [
{
"rows": [
{
"id": "123",
"name": "Acme",
"country": "United States",
"no_employees": 500
},
. . .*more code lines*. . .
],
"nextpagetoken": 123457,
"morePages": true
}
]
}
URL
When you select URL, you also need to select the URL source:
-
Header Name - Enter a header name if the next page URL is passed in the response headers along with a link header.
-
Request Path - Enter the path in the API response that defines the next page URL. This value should be supplied in XPath notation. See the following example.
Example Using the Request Path
In the following example, the Request Path is set to /results/nextpageurl.
Response:
{
"results": [
{
"rows": [
{
"id": "123",
"name": "Acme",
"country": "United States",
"no_employees": 500
},
...
],
"nextpageurl": "https://myapi?nextpage=81a3ebdb-1483-45cd-84d1-f711d1308698"
}
]
}