What is SQL API?

SQL API is an Action you can add to your data pipeline to query your data directly from your application.

For example, if you are a company that needs to have information in real-time such as a financial score for a specific user, you could use the SQL API Action to create a query that will retrieve this information. With the SQL API, you can execute the query on-demand from your applications and additionally, execute it in real-time.

How to use the SQL API?

Based on the example mentioned above, we will walk you through the process of finding a parameter, a value under a specific field inside of a table with data. We will run a model and apply an SQL API Action with parameters of field, limit, and offset. This process will be done by extracting the data from a sample integration and the request will be sent to a sample application.

Go to Pipelines and choose the one you want to use with the SQL API Action. You can use the Regression Demo pipeline provided by Datagran. Make sure the pipeline is working properly prior to adding the SQL API. Learn more about pipelines here.

Once you are sure everything is working, scroll all the way to the bottom of Actions, and click on the SQL API from the right-hand side menu. Then, drag it to the pipeline canvas.

Hover over the SQL API element and press the edit button.

A new window will open with a SQL editor you can use to type a query to allow you to specify the variables you want to find in your dataset. Make sure you take note of the operator’s name. In this example, the name is SQL API. You will need this name once you are in the API section in step 9.

In this example, we want to pull information from {{fields}} with a LIMIT of {{limit}} and an OFFSET of {{offset}}.

Sample query:

SELECT {{fields}} FROM `operator_6144fbd6fd03cde3c3383f9c__sql_output` LIMIT {{limit}} OFFSET {{offset}}

Tip: Make sure the parameters is formatted correctly. You must enter: {{customer_id}} (double curly brackets) for the API parameters to populate underneath the SQL editor.

The table with the query results will show up below the SQL editor. Press Save to return to your pipeline.

Once back in your pipeline canvas, press the + sign and click on Save.

Next, head over to the API section located on the left-side menu. Find your SQL API operator label. Once you locate it, you will see there are two options: POST and GET.

You will be provided with the API Key and the URL to send the request from your application, including the values that you need to assign to the dynamically created parameters

The endpoint will return the bg_task_id (Background Task ID) which you will need when using the GET endpoint.