[Version: 2024 - April]
[Build: 24.05.03003]
If you know how to create and work with APIs with the Postman platform, then it should be quite easy for you to adapt that knowledge to TARGIT's Generic REST API.
It is available in the TARGIT InMemory ETL Studio and in TARGIT Data Discovery as a data source:
Example: Simple GET with fixed page parameter:
For preview purposes, you may click the 'Send' button to see the result of the API call:
In InMemory ETL, use the REST API data source in an Import task:
This will create an InMemory table with content from the response (here viewed with the InMemory Query tool):
Example: GET with variable page parameter
Variables can be used with the syntax %%[variable_name]%%.
The ETL project may look like this:
Notice the PARAMETERS that you must add to the IMPORT task:
DECLARE @PageNumber as int
SET @PageNumber=1
WHILE @PageNumber<=3
IMPORT [ZendeskTopics] = [Zendesk Posts].{select *
from [] (OPTIONS: depth=5)(PARAMETERS:Page_Number=@@PageNumber)}
SET @PageNumber = @PageNumber +1
LOOP
SAVE
Example: GET with variable URL
If the API provides next_page as a full URL, you can also use a variable to replace the GET URL.
The ETL project may look like this:
Notice the PARAMETERS that you must add to the IMPORT task:
DECLARE @NextPage as string
SET @NextPage='https://targithelp.zendesk.com/api/v2/help_center/community/posts.json?page=1&per_page=100'
WHILE LEN(@NextPage)>0
IMPORT [tmpZendeskPosts] = [Zendesk Posts].{select
[next_page],
[per_page],
[page_count],
[count],
[posts id],
[posts title],
[posts details]
from [] (OPTIONS: depth=5)(PARAMETERS:NextPageURL=@@NextPage)}
IMPORT [ZendeskPosts] = [ME].{SELECT * FROM [tmpZendeskPosts]}
SET @NextPage=tmpZendeskPosts.next_page
DROP [tmpZendeskPosts]
LOOP
SAVE
Example: Request token, and then use token variable in another API request
Note: Obtaining a token may vary from data source to data source. You will also need to apply the proper Authorization credentials.
The ETL project may look like this:
Notice the PARAMETERS that you must add to the IMPORT task:
DECLARE @token as string
IMPORT [Token_table] = [API access token].{select * from [] (OPTIONS: depth=5)}
SET @token = {SELECT access_token FROM Token_table}
IMPORT [Courses_table] = [API courses].{select * from [] (OPTIONS: depth=5) (PARAMETERS:BEARER_TOKEN=@@token)}
SAVE
Comments
Dear Community,
In the comments, please add your own API examples. This could be APIs from data providers that you are already extracting data from - whether that is through TARGIT or by other means.
Or it could be URLs to APIs from data providers that may of general interest to yourself or to others.
BR / Ole
Hi OD
We will soon be importing data from an external data source. In the output we have a FROM date and a TO date. Is it possible to set the FROM date dynamically?
BR
/OA
Hi Ole Arp,
Sometimes the API provider has an option or a specific syntax to work with Today's date.
If this is not the case, and you need to supply a specific date in the format e.g., dd-mm-yyyy, then you can only do it with the InMemory ETL Studio, where you can work with variables, similar to my %%PageNumber%% example above.
I.e., in InMemory ETL, create a variable where you feed in today's date with the GETDATE() function. https://community.targit.com/hc/en-us/articles/360017716297-GETDATE
BR / Ole
Please sign in to leave a comment.