Deep Dives

Programmatically Retrieve SQL results

This tutorial focuses on programmatically retrieving SQL query results to extract metadata using the Coactive API and Coactive SQL Engine functionality.

Step 1: Setting Up Authorization

Retrieve and setup API Credentials

  1. Refer to our API Authenication page

Step 2: Submitting a Query

To automate queries, use the Coactive API’s asynchronous query submission functionality.

Example Query Submission

Use the following curl command to submit a SQL query:

Curl
$curl -X POST https://app.coactive.ai/api/v1/queries \
>        -H "Authorization: Bearer <CLIENT_ID>:<CLIENT_SECRET>" \
>        -H "Content-Type: application/json" \
>        -d '{"query": "SELECT * FROM coactive_table", \
>              "datasetId": "<DATASET_ID>" \
>            }'

API Response

The API will respond with details including a query_id:

API_Response
${    "queryId": <QUERY_ID>,    
>        "status": "Queued",
>        "datasetId": "<DATASET_ID>"  }

Step 3: Checking Query Status

To check the status of your query, use the queryId returned in the previous response:

Curl
$curl https://app.coactive.ai/api/v1/queries/<QUERY_ID> \
>      -H "Authorization: Bearer <CLIENT_ID>:<CLIENT_SECRET>"

Example Status Response

wordwrap
${"queryId": "<QUERY_ID>",
>  "status": "Completed",
>  "datasetId": "<DATASET_ID>",
>  "resultCount": 100  }

Step 4: Paginating Through Large Result Sets

Queries return a maximum of 10,000 results. To retrieve more, use SQL’s LIMIT and OFFSET clauses across multiple queries.

Using LIMIT and OFFSET

For example, if your query returns 50,000 matching rows, you would run 5 separate queries:

SQL Query Examples
1-- First 10,000 results
2SELECT * FROM coactive_table LIMIT 10000 OFFSET 0
3
4-- Next 10,000 results
5SELECT * FROM coactive_table LIMIT 10000 OFFSET 10000
6
7-- Next 10,000 results
8SELECT * FROM coactive_table LIMIT 10000 OFFSET 20000
9
10-- Next 10,000 results
11SELECT * FROM coactive_table LIMIT 10000 OFFSET 30000
12
13-- Final 10,000 results
14SELECT * FROM coactive_table LIMIT 10000 OFFSET 40000

Increment OFFSET by 10,000 with each query. When resultCount in the status response is less than 10,000, you’ve reached the final page.

Step 5: Downloading Results as CSV

Alternatively, you can download the results as a CSV file:

Curl
$curl https://app.coactive.ai/api/v1/queries/csv/url \
>      -H "Authorization: Bearer <CLIENT_ID>:<CLIENT_SECRET>"

Example Response

The response provides a temporary link to the CSV file:

API Response
${    "queryId": "<QUERY_ID>",
>     "downloadUrl": "https://app.coactive.ai/presigned/...."  }

Notes

  • The download link expires 15 minutes after it is generated.

Example: Full Query Execution Workflow

  1. Submit a query via POST.

  2. Use the queryId to check the status of the query.

  3. Once the status is “Completed,” download the results as a CSV file.