Programmatically Retrieve SQL results

Tutorial: Programmatically retrieve SQL Query 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 Results

For large datasets, you may need to paginate through the results:

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

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.

Built with