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://api.coactive.ai/api/v1/queries \
>        -H "Authorization: Bearer <CLIENT_ID>:<CLIENT_SECRET>" \
>        -H "Content-Type: application/json" \
>        -d '{"query": "SELECT * FROM coactive_table", \
>              "dataset_id": "<DATASET_ID>" \
>            }'

API Response

The API responds with query details including the query_id:

API_Response
${    "query_id": "<QUERY_ID>",    
>        "status": "Queued",
>        "dataset_id": "<DATASET_ID>"  }

Step 3: Checking Query Status

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

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

Example Status Response

wordwrap
${"query_id": "<QUERY_ID>",
>  "status": "Complete",
>  "dataset_id": "<DATASET_ID>",
>  "total_row_count": 100  }

Step 4: Downloading Results

Once the query status is “Complete,” retrieve the results download URL:

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

Example Response

The response provides a temporary link to download the results:

API Response
${    "query_id": "<QUERY_ID>",
>     "download_url": "https://api.coactive.ai/presigned/...."  }

Notes

  • The download link expires 15 minutes after it is generated (configurable via the expiration_seconds query parameter).

Example: Full Query Execution Workflow

  1. Submit a query via POST.

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

  3. Once the status is “Complete,” download the results.