Compose Queries API

Description

Queries in Alation are documents of SQL text. They can have multiple SQL statements, and their content is editable by their creator. They are associated with a single data source on creation and cannot be moved to a different data source.

Queries can be in a draft state or published.

When a query is executed, each statement (CREATE, UPDATE, INSERT, SELECT, etc) is executed in turn by the database, and Alation stores metadata in an execution event. If the statement returns a result, Alation caches up to 16MB of that result on the server (unless the user is running an export, in which case only a 1000 row result preview is cached). These cached results (sometimes whole, sometimes partial) are available via API or in the Alation catalog until they expire. The expiration period is configurable on your server, but defaults to 1 week.

In addition to an execution event, there is also a session and a batch associated with the execution of a query. If a query contains multiple statements, then multiple events—one event per query statement—would be created and would belong to a single execution batch. Each execution of a query in Compose will create a new batch; a single session could have many such batches. In the case of scheduled queries, a new session is created for each run and each such session will have only a single batch; each scheduled query is run via a designated Celery task.

Create Query

This API allows an application to create a new query in Alation with the specified content on behalf of any user (if authenticated as an admin).

Note: This API is available in version 4.11 and later.

URL

This API may be called from a server-side application with an authentication token. If the authentication token of an admin user is used, the query can be assigned to any author and will appear under their account.

This API may be called from the browser as either a POST or a GET. It will authenticate the user based on their browser session. If the user is not logged in, they will be prompted to log in, and it will complete the request after login.

URL Parameters

For the /query/import/ (browser) version only:

Headers

For the /query/ (server-side) version only:

Replace <your_token> with API Token which can be obtained from getToken API call (Get API Token).

Body

{
    "datasource_id": 1,
    "content": "SELECT count(*) FROM users;\nSELECT TOP 10 * FROM users;",
    "saved": true,
    "published": true,
    "title": "Top 10 Users",
    "author": {
       "id": 1,
       "email": "[email protected]",
       "username": "author"
    }
}

If issued as a GET, these fields are passed as query parameters. In this case, author is omitted, and the currently logged-in user becomes the author.

/integration/v1/query/import/?return_to=compose&datasource_id=1&content=SELECT%20*&saved=true

Success Response

Content-Type: application/json

Status: 201 CREATED

Headers:

Code Samples

HTML (Browser Version)

<form action="https://alation.yourcompany.com/integration/v1/query/import/">
  <input type="hidden" name="datasource_id" value="10" />
  <textarea name="content" />

  <button>Submit</button>
</form>

cURL (Server-Side Version)

#!/bin/bash

# This is an example token. Please replace this with your token.
API_TOKEN="2abcd-4c04-4c21-8692-eda27a877f90"

URL="https://alation.yourcompany.com/integration/v1/query/"

curl -X POST "${URL}" -H 'Content-Type: application/json' -H "Token: ${API_TOKEN}" -d '{
    "datasource_id": 1,
    "content": "SELECT count(*) FROM users;\nSELECT TOP 10 * FROM users;",
    "saved": true,
    "author": {
       "username": "author"
    }
}'

Python (Server-Side Version)

import csv
import pprint
import requests


# This is an example token. Please replace this with your token.
headers = {'Token': '2abcd-4c04-4c21-8692-eda27a877f90'}
BASE_URL = "https://alation.yourcompany.com/integration/v1/query/"

# Get query result for result with id 321 in CSV format
execution_result_id = 321
# download and parse response as csv
with requests.Session() as s:
    response = requests.get(BASE_URL.format(execution_result_id), headers=headers)
    decoded_content = response.content.decode('utf-8')
    csv_reader = csv.reader(decoded_content.splitlines(), delimiter=',')
    pprint.pprint(list(csv_reader))

Get Query Text by ID

This API allows you to get SQL text of a query by its id. The most-recently saved content of the query is returned as plain text. All whitespace is retained.

NOTE: This API is available in version 4.16 and later. For versions prior to 4.16, please refer to the previous Query API documentation.

URL

Replace <query_id> with Alation ID of the query. This ID can be found in the URL for the query on Alation.

Headers

Replace <your_token> with API Token which can be obtained from getToken API call (Get API Token).

Success Response

Content-Type: text/plain

Status: 200 OK

Error Response

Invalid Token, Missing Token Header, or Lack of permission to view the sql text

Status: 403 Forbidden

Bad Query ID

Status: 404 Not Found

Code Samples

cURL

#!/bin/bash

# This is an example token. Please replace this with your token.
API_TOKEN="2abcd-4c04-4c21-8692-eda27a877f90"

QUERY_ID=123
BASE_URL="https://alation.yourcompany.com/integration/v1/query/$QUERY_ID/sql/"

# Get sql text for query with id 123
curl -H "TOKEN: ${API_TOKEN}" -g "${BASE_URL}"

Python

import requests


# This is an example token. Please replace this with your token.
headers = {'Token': '2abcd-4c04-4c21-8692-eda27a877f90'}
BASE_URL = "https://alation.yourcompany.com/integration/v1/query/{}/sql/"

# Get sql text for query with id 123
query_id = 123
response = requests.get(BASE_URL.format(query_id), headers=headers)

print response.content

Sample Request and Response

URL

Get SQL Text for query with id 123

Success Response

SELECT * FROM my_schema.my_table WHERE my_column is not NULL;

Get Query Result by ID

This API call allows you to get query result by execution_result_id in CSV format.

NOTE: This API is available in version 4.16 and later. For versions prior to 4.16, please refer to the previous Query API documentation.

The returned CSV includes a header with result column names. Note that the data may only be a partial result. There are two scenarios where the result returned here is incomplete:

1. The result was over 16MB. Alation stores only 16MB of data for each result and discards the rest.
2. The result came from clicking "Run & Export" in Compose. In this case, Alation stores only a result preview, consisting of the first 1000 rows.

URL:

Replace <execution_result_id> with the Alation ID of the execution result. This ID can be found in the URL for the result on Alation.

Headers

Replace <your_token> with API Token which can be obtained from getToken API call (Get API Token).

Success Response

Content-Type: application/json

Status: 200 OK

Error Response

Invalid Token, Missing Token Header, or Lack of permission to view the sql text

Status: 403 Forbidden

Bad Execution ID

Status: 404 Not Found

Code Samples

cURL

#!/bin/bash

# This is an example token. Please replace this with your token.
API_TOKEN="2abcd-4c04-4c21-8692-eda27a877f90"

EXECUTION_RESULT_ID=321
BASE_URL="https://alation.yourcompany.com/integration/v1/result/$EXECUTION_RESULT_ID/csv/"

# Get query result for result with id 321 in CSV format
curl -H "TOKEN: ${API_TOKEN}" -g "${BASE_URL}"

Python

import csv
import pprint
import requests


# This is an example token. Please replace this with your token.
headers = {'Token': '2abcd-4c04-4c21-8692-eda27a877f90'}
BASE_URL = "https://alation.yourcompany.com/integration/v1/result/{}/csv/"

# Get query result for result with id 321 in CSV format
execution_result_id = 321
# download and parse response as csv
with requests.Session() as s:
    response = requests.get(BASE_URL.format(execution_result_id), headers=headers)
    decoded_content = response.content.decode('utf-8')
    csv_reader = csv.reader(decoded_content.splitlines(), delimiter=',')
    pprint.pprint(list(csv_reader))

Sample Request and Response

URL

Get query result for result with id 321 in CSV format

Success Response

my_column,sum,name\n
True,365,Days in a Year\n
True,24,Hours in a Day\n
True,3600,Seconds in an Hour

Get Latest Query Result ID from Query ID

This API allows you to GET latest query result ID as well as corresponding execution event information for a given query ID.

NOTE: This API is available in version 4.16 and later.

URL

Replace <query_id> with Alation ID of the query.

Headers

Replace <your_token> with API Token which can be obtained from getToken API call (Get API Token).

Success Response

Content-Type: application/json

Status: 200 OK

Error Response

Invalid Token, Missing Token Header

Status: 403 Forbidden

Query corresponding to the given ID does not exist

Status: 404 Not Found

{"detail":"Not found."}

No available or visible results

Status: 404 Not Found

{"detail":"Could not get latest query result ID for query ID 15"}

Code Samples

cURL

#!/bin/bash

# This is an example token. Please replace this with your token.
API_TOKEN="2abcd-4c04-4c21-8692-eda27a877f90"

QUERY_ID=15
BASE_URL="https://alation.yourcompany.com/integration/v1/query/$QUERY_ID/result/latest/"

# Get latest result ID for query with ID 15
curl -H "TOKEN: ${API_TOKEN}" -g "${BASE_URL}""

Python

import json
import pprint
import requests


# This is an example token. Please replace this with your token.
headers = {'Token': '2abcd-4c04-4c21-8692-eda27a877f90'}
BASE_URL = "https://alation.yourcompany.com/integration/v1/query/{}/result/latest/"

# Get latest result ID for query with ID 15
query_id = 15
response = requests.get(BASE_URL.format(query_id), headers=headers)
# Parse response as json
json_response = json.loads(response.content)

pprint.pprint(json_response)

Sample Request and Response

URL

Get latest query result ID for query with ID 15

Success Response

Body

  {
      "id": 79,
      "truncated": false,
      "execution_event": {
          "executed_at": "2017-10-18 01:19:09.855398+00:00",
          "canceled": false,
          "execution_error": null,
          "finished": true,
          "finished_at": "2017-10-18 01:19:09.976501+00:00",
          "seconds_taken": 0.121132
        }
    }

The JSON response includes the latest query result metadata for a query.

The result has three main attributes; ID, truncated, and execution_event object:

Get Query Details

This API allows retrieval of details for queries, including scheduling information and details regarding the latest scheduled execution of the query if the query had been sheduled to run and has since done so.

Note: This API is available in Alation V R2 or later. An earlier version of this API was available as early as Alation version 4.11 but with a reduced set of fields returned in the response; that reduced set of information is still emitted by this API in the same form.

URLs

Retrieves details for (potentially) multiple queries. Replace with the URL parameters listed in the following section.

Retrieves details for a single query. Replace <query_id> with the unique identifier of the query.

URL Parameters

See the section below for details on what operations can be performed on URL parameters.

Headers

Replace <your_token> with API Token which can be obtained from getToken API call (Get API Token).

Success Response

Content-Type: application/json

Status: 200 OK

Body

[
  {
    "datasource_id": 1,
    "autosave_content": "SELECT count(*) FROM users;\nSELECT TOP 10 * FROM users;",
    "content": "SELECT count(*) FROM users;\nSELECT TOP 10 * FROM users;",
    "saved": true,
    "published": false,
    "url": "/integration/query/123/",
    "id": 123,
    "title": "Query Title",
    "description": "Query description.",
    "datasource": {
      "title": "Datasource Title",
      "id": 1,
      "url": "/data/1/",
      "uri": "dbtype://server:port/?param=val"
    },
    "ts_last_saved": "2018-01-23T00:00:00.123456Z",
    "has_unsaved_changes": false,
    "catalog_url": "/query/123/",
    "compose_url": "/compose/query/123/",
    "schedules": [
      {
        "enabled": false,
        "cron_expression": "17 1 * * *",
        "ts_last_attempt": "2018-01-23T08:17:00.123456Z",
        "overdue": false,
        "ts_next_run": "2018-01-24T08:17:00.123456Z",
        "celery_queue": "run_scheduling",
        "celery_task_name": "rosemeta.tasks.query_scheduling.run_query_123",
        "user": {
          "username": "[email protected]",
          "is_active": true
        },
        "db_connection": {
          "uri": "dbtype://server:port/?param=val",
          "username": "dbusername",
          "ts_modified": "2018-01-01T15:00:00.123456Z",
          "ts_last_used": "2018-01-23T08:17:00.123456Z"
        },
        "latest_session": {
          "id": 1234,
          "client_session_id": "2018-01-23T01:17:00.123456Z#0a1b3c4d",
          "query_id": 123,
          "ts_start": "2018-01-23T08:17:00.123456Z",
          "sandbox_id": "schedule-123",
          "batch_ids": [
            1234
          ]
        }
      }
    ]
  }
]

Note: with the <query_id> URL path param, a single JSON object is returned without the enclosing array.

Error Response

If an ordering is specified with any query parameter that starts with schedule_, a bad request error will be thrown.

See the section below for more details regarding error responses.

Code Samples

See the section below for details.

Get Execution Session Details

This API allows retrieval of details for query execution sessions. A single execution session corresponds to either a single scheduled execution of a query or potentially many manual executions of a query initiated from Compose.

Note: This API is available in Alation V R2 or later.

URLs

Retrieves details for (potentially) multiple execution sessions. Replace with the URL parameters listed in the following section.

Retrieves details for a single execution session. Replace <session_id> with the unique identifier of the session.

Retrieves details for (potentially) multiple execution sessions relating to a single query. Replace <query_id> with the unique identifier of the query and with the URL parameters listed in the following section.

URL Parameters

See the section below for details on what operations can be performed on URL parameters.

Headers

Replace <your_token> with API Token which can be obtained from getToken API call (Get API Token).

Success Response

Content-Type: application/json

Status: 200 OK

Body

[
  {
    "id": 1234,
    "client_session_id": "2018-01-23T01:17:00.123456Z#0a1b3c4d",
    "query_id": 123,
    "ts_start": "2018-01-23T08:17:00.123456Z",
    "sandbox_id": "schedule-123",
    "is_scheduled": true,
    "batch_ids": [
      1234
    ]
  }
]

Error Response

See the section below for details.

Code Samples

See the section below for details.

Get Execution Batch Details

This API allows retrieval of details for query execution batches. A single batch corresponds to a single execution of a query -- regardless of whether the query execution was scheduled to run or was manually initiated via Compose. For scheduled queries, a single batch will belong exclusively to a session; whereas for queries run from Compose, many batches can belong to a single session. A batch may have one or more execution events, each of which corresponds to a single statement, one of potentially many for a query.

Note: This API is available in Alation V R2 or later.

URLs

Retrieves details for (potentially) multiple execution batches. Replace with the URL parameters listed in the following section.

Retrieves details for a single execution batch. Replace <batch_id> with the unique identifier of the batch.

Retrieves details for (potentially) multiple execution batches relating to a single query. Replace <query_id> with the unique identifier of the query and with the URL parameters listed in the following section.

URL Parameters

See the section below for details on what operations can be performed on URL parameters.

Headers

Replace <your_token> with API Token which can be obtained from getToken API call (Get API Token).

Success Response

Content-Type: application/json

Status: 200 OK

Body

[
  {
    "id": 1,
    "query_id": 1,
    "session_id": 1,
    "events": [
      {
        "id": 1,
        "index_in_batch": 0
      }
    ]
  }
]

Error Response

See the section below for details.

Code Samples

See the section below for details.

Get Execution Event Details

This API allows retrieval of details for query execution events. A single execution event corresponds to one statement of potentially many for a query. One or many execution events belong to a batch, one per statement executed in sequence.

Note: This API is available in Alation V R2 or later.

URLs

Retrieves details for (potentially) multiple execution events. Replace with the URL parameters listed in the following section.

Retrieves details for a single execution event. Replace <batch_id> with the unique identifier of the event.

Retrieves details for (potentially) multiple execution events relating to a single query. Replace <query_id> with the unique identifier of the query and with the URL parameters listed in the following section.

URL Parameters

See the section below for details on what operations can be performed on URL parameters.

Headers

Replace <your_token> with API Token which can be obtained from getToken API call (Get API Token).

Success Response

Content-Type: application/json

Status: 200 OK

Body

[
  {
    "id": 12345,
    "result": {
      "id": 12345,
      "title": "Result Title",
      "ts_created": "2018-01-23T08:17:00.123456Z",
      "row_count": 250000,
      "byte_count": 17500000,
      "ts_expiration": "2018-02-23T08:17:00.123456Z",
      "truncated": false,
      "expired": false,
      "deleted": false,
      "url": "/execution_result/12345/",
      "storage_status": "STORED_PART",
      "data_schema": [
        {
          "norm_type": "INT",
          "type": "int",
          "original_name": "x.y",
          "name": "y"
        }
      ],
      "query": {
        "id": 123,
        "title": "Query Title",
        "description": "Query description."
      }
    },
    "num_result_rows": 250000,
    "execution_error": null,
    "canceled": false,
    "ts_executed": "2018-01-23T08:17:00.123456Z",
    "elapsed_seconds": 7.654321,
    "index_in_batch": 0,
    "batch_id": 1234,
    "session_id": 1234,
    "query_id": 123,
    "db_username": "alation",
    "statement_content": "select * from default.employee",
    "datasource": {
      "title": "Datasource Title",
      "id": 1,
      "url": "/data/1/",
      "uri": "dbtype://server:port/?param=val"
    }
  }
]

Error Response

If an invalid value is passed to result_storage_status, a bad request error will be thrown.

See the section below for more details regarding error responses.

Code Samples

See the section below for details.

Get Scheduled Run Details

This API allows retrieval of details for scheduled runs of queries. Each such run corresponds to a single Celery task, created by celery.beat and picked up by a Celery worker. The date and time of when the task was sent to a worker, when the worker started to work on the scheduled query task, and when the task completed are all available via this API. The associated execution session and query can also be provided (Note: this feature is not enabled by default. See the section below below on enabling this feature.)

Note: This API is available in Alation V R2 or later.

URLs

Retrieves details for (potentially) multiple scheduled runs. Replace with the URL parameters listed in the following section.

Retrieves details for (potentially) multiple scheduled runs relating to a single query. Replace <query_id> with the unique identifier of the query and with the URL parameters listed in the following section.

URL Parameters

See the section below for details on what operations can be performed on URL parameters.

Headers

Replace <your_token> with API Token which can be obtained from getToken API call (Get API Token).

Success Response

Content-Type: application/json

Status: 200 OK

Body

[
  {
    "session_id": 1234,
    "query_id": 123,
    "query_title": "Query Title",
    "task_id": "af406fcd-8c9d-4837-a34e-5b5e88eca923",
    "ts_sent_to_broker": "2018-01-23T08:17:00.123456Z",
    "ts_prerun": null,
    "ts_postrun": null
  }
]

Note: session_id, query_id, and query_title will be null so long as the scheduled run executed without the aforementioned feature having been enabled. See the section below below on enabling this feature for details.

Error Response

See the section below for details.

Code Samples

See the section below for details.

Error Response

The following error responses apply to the above endpoints that refer to this section explicitly.

Bad Request

Status: 400 Bad Request

Body

{
  "error": "Invalid value(s) [...] for URL parameter '...'"
}

Or

{
  "error": "Ordering with \"...\" may not be used with filtering on \"...\" You may either remove the filter(s) or remove the ordering parameter."
}

Invalid Token

Status: 403 Forbidden

Body

{
   "detail": "Invalid API token."
}

Missing Token Header

Status: 403 Forbidden

Body

{
    "detail": "Missing API token."
}

Not Found

Status: 404 Not Found

Body

{
    "detail": "Not found."
}

Accessing Session and Query for Scheduled Run

The API will not provide session or query details for a scheduled run without first enabling Alation to store these details. The scheduled run API will emit null for session_id, query_id, and query_title when such details are unavailable.

This feature of storing query and session details against scheduled runs can be enabled via a feature flag, namely alation.feature_flags.enable_celery_task_for_compose_execution_session. This feature is disabled by default.

Once the feature is enabled, the API will emit session and query information only for scheduled runs that have started since the feature was (and so long as the feature remains) enabled.

Code Samples

cURL

#!/bin/bash

# This is an example token. Please replace this with your token obtained from getToken API call.
API_TOKEN="2abcd-4c04-4c21-8692-eda27a877f90"

BASE_URL="https://alation.yourcompany.com/integration/v1/query"

echo 'Get all queries'
curl -H "TOKEN: ${API_TOKEN}" "${BASE_URL}/"

echo 'Get a single query'
curl -H "TOKEN: ${API_TOKEN}" "${BASE_URL}/1/"

echo 'Get all execution sessions'
curl -H "TOKEN: ${API_TOKEN}" "${BASE_URL}/execution_session/"

echo 'Get all execution sessions for a given query'
curl -H "TOKEN: ${API_TOKEN}" "${BASE_URL}/1/execution_session/"

echo 'Get details for a single execution session'
curl -H "TOKEN: ${API_TOKEN}" "${BASE_URL}/execution_session/1/"

echo 'Get all execution batches'
curl -H "TOKEN: ${API_TOKEN}" "${BASE_URL}/execution_batch/"

echo 'Get all execution batches for a given query'
curl -H "TOKEN: ${API_TOKEN}" "${BASE_URL}/1/execution_batch/"

echo 'Get details for a single execution batch'
curl -H "TOKEN: ${API_TOKEN}" "${BASE_URL}/execution_batch/1/"

echo 'Get all execution events'
curl -H "TOKEN: ${API_TOKEN}" "${BASE_URL}/execution_event/"

echo 'Get all execution events for a given query'
curl -H "TOKEN: ${API_TOKEN}" "${BASE_URL}/1/execution_event/"

echo 'Get details for a single execution event'
curl -H "TOKEN: ${API_TOKEN}" "${BASE_URL}/execution_event/1/"

echo 'Get all scheduled runs'
curl -H "TOKEN: ${API_TOKEN}" "${BASE_URL}/scheduled_run/"

echo 'Get all scheduled runs for a given query'
curl -H "TOKEN: ${API_TOKEN}" "${BASE_URL}/1/scheduled_run/"

echo 'Get all scheduled runs or a given time period'
curl -H "TOKEN: ${API_TOKEN}" "${BASE_URL}/scheduled_run/?ts_sent_to_broker__gt=2018-01-01&ts_sent_to_broker__lt=2018-01-02"

Python

#!/usr/bin/python

import requests
import json

# This is an example token. Please replace this with your token obtained from getToken API call.
headers = {'Token': '2abcd-4c04-4c21-8692-eda27a877f90'}

base_url='https://alation.yourcompany.com/integration/v1/query'

print '##### Get all queries #####'
response = requests.get('%s/'%(base_url,), headers=headers)
queries = json.loads(response.text)
for query in queries:
    print "ID: %s, Title: %s" % (query['id'], query['title'])

print '##### Get details for a single query #####'
response = requests.get('%s/%d/'%(base_url,1), headers=headers)
query = json.loads(response.text)
print "ID: %s, Title: %s" % (query['id'], query['title'])

print '##### Get all execution sessions #####'
response = requests.get('%s/execution_session/'%(base_url), headers=headers)
sessions = json.loads(response.text)
for session in sessions:
  print "ID: %s, Client-session-ID: %s" % (session['id'], session['client_session_id'])

print '##### Get all execution sessions for a given query #####'
response = requests.get('%s/%d/execution_session/'%(base_url, 1), headers=headers)
sessions = json.loads(response.text)
for session in sessions:
  print "ID: %s, Client-session-ID: %s" % (session['id'], session['client_session_id'])

print '##### Get details for a single session #####'
response = requests.get('%s/execution_session/%d/'%(base_url,1), headers=headers)
session = json.loads(response.text)
print "ID: %s, Client-session-ID: %s" % (session['id'], session['client_session_id'])

print '##### Get all execution batches #####'
response = requests.get('%s/execution_batch/'%(base_url), headers=headers)
batches = json.loads(response.text)
for batch in batches:
  print "ID: %s, Query-ID: %s, Session-ID: %s" % (batch['id'], batch['query_id'], batch['session_id'])

print '##### Get all execution batches for a given query #####'
response = requests.get('%s/%d/execution_batch/'%(base_url, 1), headers=headers)
batches = json.loads(response.text)
for batch in batches:
  print "ID: %s, Query-ID: %s, Session-ID: %s" % (batch['id'], batch['query_id'], batch['session_id'])

print '##### Get details for a single batch #####'
response = requests.get('%s/execution_batch/%d/'%(base_url,1), headers=headers)
batch = json.loads(response.text)
print "ID: %s, Query-ID: %s, Session-ID: %s" % (batch['id'], batch['query_id'], batch['session_id'])

print '##### Get all execution events for a given query #####'
response = requests.get('%s/%d/execution_event/'%(base_url, 1), headers=headers)
events = json.loads(response.text)
for event in events:
  print "ID: %s, Query-ID: %s, Session-ID: %s, Time-Executed: %s" % (event['id'], event['query_id'], event['session_id'], event['ts_executed'])

print '##### Get all execution events #####'
response = requests.get('%s/execution_event/'%(base_url), headers=headers)
events = json.loads(response.text)
for event in events:
  print "ID: %s, Query-ID: %s, Session-ID: %s, Time-Executed: %s" % (event['id'], event['query_id'], event['session_id'], event['ts_executed'])

print '##### Get details for a single event #####'
response = requests.get('%s/execution_event/%d/'%(base_url,1), headers=headers)
event = json.loads(response.text)
print "ID: %s, Query-ID: %s, Session-ID: %s, Time-Executed: %s" % (event['id'], event['query_id'], event['session_id'], event['ts_executed'])

print '##### Get all scheduled runs #####'
response = requests.get('%s/scheduled_run/'%(base_url), headers=headers)
runs = json.loads(response.text)
for run in runs:
  print "Task-ID: %s, Query-ID: %s, Session-ID: %s" % (run['task_id'], run['query_id'], run['session_id'])

print '##### Get all scheduled runs for a given query #####'
response = requests.get('%s/%d/scheduled_run/'%(base_url, 1), headers=headers)
runs = json.loads(response.text)
for run in runs:
  print "Task-ID: %s, Query-ID: %s, Session-ID: %s" % (run['task_id'], run['query_id'], run['session_id'])

print '##### Get all scheduled runs for a given time period #####'
response = requests.get('%s/scheduled_run/%s'%(base_url, '?ts_sent_to_broker__gt=2018-01-01&ts_sent_to_broker__lt=2018-01-02'), headers=headers)
runs = json.loads(response.text)
for run in runs:
  print "Task-ID: %s, Query-ID: %s, Session-ID: %s" % (run['task_id'], run['query_id'], run['session_id'])

Operations with URL Parameters

Various operations on query parameters can be performed to filter results from the above endpoints; an operator is appended to a query parameter with a __ (double underscore) as follows: /endpoint/?a_param__operator=argument.

Operators by type

Which operators are available depends on the expected argument type for a parameter. Below are some examples grouped by parameter type.

String Parameters

• /integration/v1/query/execution_event/?execution_error__isnull=false
• /integration/v1/query/execution_event/?db_username__exact=Xyz
• /integration/v1/query/execution_event/?db_username__iexact=Xyz
• /integration/v1/query/?content__contains=AbCxYz
• /integration/v1/query/?content__icontains=update
• /integration/v1/query/?title__startswith=Q1
• /integration/v1/query/?title__istartswith=Q1
• /integration/v1/query/?title__endswith=Results
• /integration/v1/query/?title__iendswith=Results
• /integration/v1/query/?content__regex=^select.*table$
• /integration/v1/query/?content__iregex=^select.*table$

Numeric Parameters

• /integration/v1/query/?id=3
• /integration/v1/query/execution_session/?query_id__gt=50
• /integration/v1/query/execution_batch/?session_id__lt=200
• /integration/v1/query/execution_batch/?session_id__gte=500
• /integration/v1/query/execution_event/?result_byte_count__lte=500000
• /integration/v1/query/execution_event/?result_byte_count__lte=500000

Enumerated Value Parameters

• /integration/v1/query/execution_event/?result_storage_status=STORED_PART

Date Parameters

• /integration/v1/query/?schedule_ts_last_attempt__year=2018&schedule_ts_last_attempt__month=3&schedule_ts_last_attempt__day=30
• /integration/v1/query/execution_session/?ts_start__hour=0&ts_start__minute=35
• /integration/v1/query/execution_event/?result_ts_expiration__gte=2017-12-15
• /integration/v1/query/execution_event/?result_ts_created__gte=2018-04-13T22:00:00Z
• /integration/v1/query/execution_event/?ts_executed__gte=2018-04-13T22:00:00.000001Z

Boolean Parameters

• /integration/v1/query/?schedule_enabled=true
• /integration/v1/query/?has_unsaved_changes=false

Paging

Paging of API results can be controlled via the limit and skip parameters:
/integration/v1/query/execution_event/?limit=10&skip=20

Ordering

Ordering of results can be controlled as indicated above in the list of query parameters specific to each endpoint using the order_by parameter.