HomeGuidesRecipesAPI ReferencePython SDK
Alation Help Center
API Reference

Queries API

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

POST /integration/v1/query/

This endpoint 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.

GET/POST /integration/v1/query/import/?return_to=**<return>**

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

NameValue
return_toEither compose or catalog. If compose, the user will be directed to this query in the compose web application. If catalog, the user will be directed to the catalog page for this query.

Headers

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

HTTP HeaderValue
Token<your_token>.

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",
    "description": "Counts the number of users and gives the top 10 users.",
    "tag_names": ["@tag_name"],
    "domain_ids": [1],
    "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
Body AttributeRequiredTypeDescription
authorNoObjectWhich user to associate the query with. If blank, the user making the API call is used. Only admins may specify this field. Object must contain one of id, email, or username to identify the user.
contentYesStringSQL content of the query
datasource_idYesIntegerID of the data source this query is on
descriptionNoStringThe description of the query
domain_idsNoArray of integersA list of domain IDs for the domains the query will belong to
publishedNoBooleanWhether the query should be published or unpublished. False by default.
savedNoBooleanWhether the query should be shown as Saved or a Draft. True by default.
tag_namesNoArray of stringsA list of tag names to apply to the query
titleNoStringThe title of the query

Success Response

Content-Type: application/json

Status: 201 CREATED

Headers:

HTTP HeaderValue
Location/integration/v1/query//

Example Response:

{
    "datasource_id": 1,
    "autosave_content": "SELECT count(*) FROM users;\nSELECT TOP 10 * FROM users;",
    "content": "SELECT count(*) FROM users;\nSELECT TOP 10 * FROM users;",
    "title": "Top 10 Users",
    "saved": true,
    "published": true,
    "description": "Counts the number of users and gives the top 10 users.",
    "url": "/integration/v1/query/6/",
    "id": 6,
    "domains": [
        {
            "title": "domain title",
            "id": 1,
            "description": "domain description"
        }
    ],
    "tags": [
        {
            "id": 1,
            "name": "@tag_name",
            "description": "tag description",
            "ts_created": "2024-04-12T11:58:56.176079Z",
            "url": "/tag/1/",
            "ts_updated": "2024-04-12T12:03:40.884535Z"
        }
    ],
    "datasource": {
        "id": 1,
        "title": "OCF snowflake",
        "uri": "",
        "url": "/data/1/"
    },
    "ts_last_saved": "2024-04-12T12:03:40.704437Z",
    "has_unsaved_changes": false,
    "catalog_url": "/query/6/",
    "compose_url": "/compose/query/6/",
    "schedules": []
}

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

GET /integration/v1/query/**<query_id>**/sql/

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

Headers

HTTP HeaderValue
Token<your_token>

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

GET /integration/v1/query/123/sql/

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:

GET /integration/v1/result/**<execution_result_id>**/csv/

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

HTTP HeaderValue
Token<your_token>

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

GET /integration/v1/result/321/csv/

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

GET /integration/v1/query/**<query_id>**/result/latest/

Replace <query_id> with Alation ID of the query.

Headers

HTTP HeaderValue
Token<your_token>

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

GET /integration/v1/query/15/result/latest/

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:

Result AttributeDescription
idThe latest result ID for a Query. This ID can be used as input to the Get Query Result by ID API to retrieve the actual result content / SQL.
truncatedTrue / False attribute indicating whether the result stored is truncated. Alation stores 16MB of the result data.
execution_eventContains information such as timestamps of when result was executed, whether there were any errors, how long the statement took to execute, etc
Execution Event AttributeDescription
executed_atDate and time of when the statement execution began
canceledTrue / False attribute indicating whether the execution was canceled
execution_errorContains any errors from the database
finishedTrue / False attribute indicating whether the statement execution completed
finished_atDate and time of when the statement execution ended
seconds_takenSeconds taken for the statement execution to finish

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

GET /integration/v1/query/?**<params>**

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

GET /integration/v1/query/**<query_id>**/

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

URL Parameters

NameDescription
idThe numeric identifier of the query
titleThe query title
descriptionThe query description
ts_last_savedThe date and time when the query was last saved
autosave_contentAny unsaved content of the query
contentThe saved content of the query
savedWhether the query has been saved
publishedWhether the query has been published
datasource_idThe numeric identifer of the query's associated data source
datasource_titleThe title of the query's associated data source
datasource_uriThe URI of the query's associated data source
schedule_enabledWhether an associated schedule for the query is enabled
schedule_cron_expressionThe cron expression for a query's schedule
schedule_ts_last_attemptWhen a scheduled execution of a query was last attempted
schedule_celery_task_nameThe associated Celery task for the query's scheduled execution
schedule_usernameThe name of the user who scheduled the query
schedule_user_is_activeWhether the user who scheduled the query is an active user
schedule_db_uriThe URI to be used when the query is scheduled to execute
schedule_db_ts_last_usedThe date and time of the last usage of the database credentials to be used when the query is scheduled to execute
order_byOrder results by 'id', 'title', 'description', 'last_saved_at', 'autosave_content', 'content', 'saved', or 'published'

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

Headers

HTTP HeaderValue
Token<your_token>.

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

GET /integration/v1/query/execution_session/?**<params>**

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

GET /integration/v1/query/execution_session/**<session_id>**/

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

GET /integration/v1/query/**<query_id>**/execution_session/?**<params>**

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

NameDescription
idThe numeric identifier of the session
client_session_idA unique string representation of the session
query_idThe numeric identifier of the associated query
ts_startThe start data and time of the session
sandbox_idThe connector sandbox ID for the session
is_scheduledWhether the session was the result of a scheduled execution
batch_idThe numeric identifier of batch(es) associated with the session

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

Headers

HTTP HeaderValue
Token<your_token>.

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

GET /integration/v1/query/execution_batch/?**<params>**

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

GET /integration/v1/query/execution_batch/**<batch_id>**/

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

GET /integration/v1/query/**<query_id>**/execution_batch/?**<params>**

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

NameDescription
idThe numeric identifier of the batch
query_idThe numeric identifier of the associated query
session_idThe numeric identifier of the associated session
event_idThe numeric identifier of the associated execution event(s)

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

Headers

HTTP HeaderValue
Token<your_token>.

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

GET /integration/v1/query/execution_event/?**<params>**

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

GET /integration/v1/query/execution_event/**<batch_id>**/

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

GET /integration/v1/query/**<query_id>**/execution_event/?**<params>**

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

NameDescription
idThe numeric identifier of the event
query_idThe numeric identifier of the query
session_idThe numeric identifier of the session
batch_idThe numeric identifier of the batch
index_in_batchA numeric, ordinal value indicating which statement among potentially several in a batch this execution event corresponds to
num_result_rowsThe number of rows returned from a query
execution_errorAny execution error reported by the data source
canceledWhether the query was canceled before execution completed
ts_executedThe date and time when query execution began
elapsed_secondsThe total number of seconds elapsed during query execution
db_usernameThe database username used when this query executed
session_is_scheduledWhether the query execution was scheduled
datasource_idThe numeric identifier of the datasource used for query execution
datasource_titleThe title of the datasource used for query execution
result_idThe numeric identifier of the execution results
result_titleThe title assigned to the results
result_ts_createdThe data and time the results were processed
result_row_countThe number of rows affected by the query exectuion
result_byte_countThe number of bytes stored of the results
result_ts_expirationThe date and time of planned expiration
result_truncatedWhether the stored results were truncated
result_expiredWhether the results have expired
result_deletedWhether the results have been deleted
result_storage_statusOne of 4 possible result storage statuses: IN_PROGRESS, STORED_PART, STORED_ALL, STORED_PREVIEW

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

Headers

HTTP HeaderValue
Token<your_token>.

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

GET /integration/v1/query/scheduled_run/?**<params>**

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

GET /integration/v1/query/**<query_id>**/scheduled_run/?**<params>**

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

NameDescription
session_idThe numeric identifier of the session, if available
query_idThe numeric identifier of the query, if available
query_titleThe title of the query, if available
task_idThe unique string identifier of the Celery task
ts_sent_to_brokerThe date and time when Celery beat enqueued the task
ts_prerunThe date and time when a Celery worker began executing an enqueued task
ts_postrunThe date and time when the Clery worker completed work on the task
order_byorder results by 'ts_sent_to_broker' (the default), 'ts_prerun', or 'ts_postrun'

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

Headers

HTTP HeaderValue
Token<your_token>

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.