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 -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:

$
{    "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 https://app.coactive.ai/api/v1/queries/<QUERY_ID> \
>
      -H "Authorization: Bearer <CLIENT_ID>:<CLIENT_SECRET>"

Example Status Response

$
{"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:

1
-- First 10,000 results
2
SELECT * FROM coactive_table LIMIT 10000 OFFSET 0
3
4
-- Next 10,000 results
5
SELECT * FROM coactive_table LIMIT 10000 OFFSET 10000
6
7
-- Next 10,000 results
8
SELECT * FROM coactive_table LIMIT 10000 OFFSET 20000
9
10
-- Next 10,000 results
11
SELECT * FROM coactive_table LIMIT 10000 OFFSET 30000
12
13
-- Final 10,000 results
14
SELECT * 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 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:

$
{    "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.

Key Links

• Coactive API Documentation – Create query

• Coactive API Documentation – Query Results - CSV