HomeGuidesRecipesAPI ReferencePython SDK
Alation Help Center
API Reference
These docs are for v2023.3.5. Click to read the latest docs for v2024.3.5.

Relational Integration v1

Accessing Relational Database type Data Objects

Description:

The APIs below allow you to access relational database type data objects in Alation catalog.
NOTE: These APIs are available in v5.9.0 or later.

GET

This API lets you get physical metadata of data objects.

Metadata Explanation

The logical metadata for an object includes:

  1. Title : The title given to an object
  2. Description : The description given to an object
  3. Custom Fields : Custom Fields added to an object. For Example: Steward, Last Updated etc.

The physical metadata for an object includes:

  1. Object name : Name of the object
  2. Data type : Data type of a column
  3. Whether the object is a view

NOTE: Object could be any of the following:
1. Data Source
2. Schema
3. Table
4. Column

URL

  1. Get the details of one specific data object whose id you already know.

    GET
    /integration/v1/<otype>/<id>/

  2. Get the details of all the data objects filtered by query parameters.

    GET
    /integration/v1/<otype>/?<params>

Replace <otype> with one of the following:

datasource

schema

table

column

Replace <params> with your list of parameters. Multiple parameters can be combined as PARAM1&PARAM2

URL Parameters

NameDescriptionApplies to
titleTitle given to the object, allow case-insensitive, partial match
Example: /integration/v1/datasource/?title=Employee
All types
nameName of the object, allow case-insensitive, partial match
Example: /integration/v1/table/?name=employee_table
Schema
Table
Column
ds_idUnique identifier of the data source the objects belong to
Example: /integration/v1/schema/?ds_id=1
Schema
Table
Column
schema_idUnique identifier of the schema the objects belong to
Example: /integration/v1/table/?schema_id=1
Table
Column
schema_nameName of the schema the tables belong to, allow case-insensitive, partial match
Example: /integration/v1/table/?schema_name=emp_schema
Table
data_typeThe data type of a column
Example: /integration/v1/column/?data_type=number
Column
nullableThe value for this field tells whether a column supports null value
Example: /integration/v1/column/?nullable=false
NOTE: This parameter supports only two values:
true
false
Column
table_idUnique identifier of the table the columns belong to
Example: /integration/v1/column/?table_id=1
Column
table_nameName of the table the columns belong to, allow case-insensitive, partial match
Example: /integration/v1/column/?table_name=emp_schema.emp_table
Column
limitLimit the number of objects returned
Example: /integration/v1/datasource/?limit=10
All types
skipSkip this number of records and return the rest
Example: /integration/v1/datasource/?skip=10
All types

NOTE: None of the above parameters are required to place a GET request for the API.

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

[ { "description": "This is schema for climate", "ds_id": 1, "db_comment": null, "id": 1, "name": "schema_climate", "title": "Climate Schema", "url": "/schema/1/" } ]

NOTE: This is a sample response for /integration/v1/schema/1/ API call.

Error Response

Invalid Token

Status: 403 Forbidden

Body

{ "detail": "Authentication failed" }

Missing Token Header

Status: 403 Forbidden

Body

{ "detail": "Authentication credentials were not provided." }

Code Samples

cURL

#!/bin/bash

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

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

# Get physical metadata of all data sources in Alation catalog.
curl -H "TOKEN: ${API_TOKEN}" "${BASE_URL}datasource/"

# Get physical metadata of all schemas in Alation catalog.
curl -H "TOKEN: ${API_TOKEN}" "${BASE_URL}schema/"

# Get physical metadata of all tables in Alation catalog.
curl -H "TOKEN: ${API_TOKEN}" "${BASE_URL}table/"

# Get physical metadata of all columns in Alation catalog.
curl -H "TOKEN: ${API_TOKEN}" "${BASE_URL}column/"

Python

# This example prints only id and title of a data source, to know all the parameters in a response, please refer to the sample responses below. import requests import json # 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/' # Get physical metedata of all data sources in Alation catalog. response = requests.get(BASE_URL + 'datasource/', headers=headers) datasources = json.loads(response.text) for datasource in datasources: print "ID: %s, Title: %s" % (datasource['id'], datasource['title']) # Get physical metadata of all schemas in Alation catalog. response = requests.get(BASE_URL + 'schema/', headers=headers) schemas = json.loads(response.text) for schema in schemas: print "ID: %s, Title: %s" % (schema['id'], schema['title']) # Get physical metadata of all tables in Alation catalog. response = requests.get(BASE_URL + 'table/', headers=headers) tables = json.loads(response.text) for table in tables: print "ID: %s, Title: %s" % (table['id'], table['title']) # Get physical metadata of all columns in Alation catalog. response = requests.get(BASE_URL + 'column/', headers=headers) columns = json.loads(response.text) for column in columns: print "ID: %s, Title: %s" % (column['id'], column['title'])

Sample Request and Response

URL

Get a data source whose unique identifier is 10.

GET /integration/v1/datasource/10/

Sample Success Response

Body

[ { "host": "teradata.alationdata.com", "port": 1025, "deployment_setup_complete": false, "db_username": "***", "dbname": null, "supports_explain": true, "has_aws_glue_metastore": false, "supports_qli_daterange": true, "icon": "db", "qli_aws_access_key_id": "", "private": true, "data_upload_disabled_message": "Your administrator has disabled the Data Uploader for this Data Source.", "obfuscate_literals": null, "qli_aws_access_key_secret": "", "is_virtual": false, "metastore_type": 0, "latest_extraction_successful": false, "is_hidden": false, "id": 10, "owner_ids": [ 6 ], "exclude_additional_columns_in_qli": false, "disable_auto_extraction": true, "hive_logs_source_type": 0, "metastore_uri": null, "qualified_name": "", "is_hive": false, "title": "teradata", "dbtype": "teradata", "uri": "teradata://teradata.alationdata.com/DBS_PORT=1025,COP=OFF,CHARSET=UTF8", "qli_aws_region": "", "is_gone": true, "favorited_by_list": null, "webhdfs_server": "asdfasdfasdf", "enabled_in_compose": true, "supports_profiling": true, "supports_qli_diagnostics": true, "is_presto_hive": false, "nosql_mde_sample_size": 100, "qli_hive_connection_source": 0, "cron_extraction": "58 4 * * 1", "negative_filter_words": null, "supports_compose": true, "hive_logs_source": null, "has_hdfs_based_qli": false, "has_aws_s3_based_qli": false, "can_data_upload": true, "description": "test teradata", "enable_designated_credential": false, "all_schemas": null, "deleted": false, "supports_md_diagnostics": true, "aws_region": null, "aws_access_key_secret": null, "limit_schemas": null, "has_previewable_qli": true, "hive_tez_logs_source": null, "remove_filtered_schemas": false, "profiling_tip": null, "aws_access_key_id": null, "has_metastore_uri": false, "webhdfs_username": null, "webhdfs_port": null, "latest_extraction_time": "2018-05-11T01:51:27.101Z", "url": "/data/10/", "jdbc_driver": null, "builtin_datasource": null, "otype": "data", "can_toggle_ds_privacy": true, "exclude_schemas": null } ]

URL

Get all the schemas whose title matches employee.

GET /integration/v1/schema/?title=employee

Sample Success Response

Body

[ { "description": "This is a schema 1", "ds_id": 1, "db_comment": null, "id": 1, "name": "EMP_SCHEMA_ONE", "title": "Employee schema", "url": "/schema/1/" }, { "description": "This is a schema 2", "ds_id": 1, "db_comment": null, "id": 1, "name": "EMP_SCHEMA_TWO", "title": "Schema for employees", "url": "/schema/2/" } ]

URL

Get all the tables in schemas with name matching emp_schema.

GET /integration/v1/table/?schema_name=emp_schema

Sample Success Response

Body

[ { "description": "<p>this is employee details table</p>", "ds_id": 1, "id": 1, "is_view": false, "is_synonym": false, "name": "EMPLOYEE", "schema_id": 1, "schema_name": "emp_schema", "title": "Employee", "url": "/table/1/", "synonyms": { "table_name": "public_emp_schema", "schema_name": "public" }, "base_table": null, "db_comment": null }, { "description": "<p>this is another employee details table</p>", "ds_id": 1, "id": 2, "is_view": false, "is_synonym": true, "name": "NEW EMPLOYEES", "schema_id": 2, "schema_name": "new_emp_schema", "title": "New employees", "url": "/table/2/", "synonyms": null, "base_table": { "table_name": "emp_table", "schema_name": "emp_schema" }, "db_comment": null }, ]

NOTE: The <p>..</p> is appended to the description because the description is a paragraph. As the description can have links, images or paragraphs, we return it as html.

URL

Get all the columns in the table whose unique identifier is 1.

GET /integration/v1/column/?table_id=1

Sample Success Response

Body

[ { "data_type": "number(10)", "description": "employee id", "ds_id": 1, "db_comment": null, "id": 1, "is_primary_key": true, "is_foreign_key": false, "name": "emp_id", "nullable": false, "schema_id": 1, "table_id": 1, "table_name": "emp_schema.EMPLOYEE", "title": "Employee ID", "position": 2, "url": "/attribute/1/" }, { "data_type": "varchar2(100)", "description": "", "ds_id": 1, "db_comment": null, "id": 2, "is_primary_key": false, "is_foreign_key": false, "name": "first_name", "nullable": true, "schema_id": 1, "table_id": 1, "table_name": "emp_schema.EMPLOYEE", "title": "First Name", "position": 1, "url": "/attribute/2/" } ]

DELETE

This API lets you delete data objects from Alation catalog.

URL

  1. Delete the data object of type=otype and id=id, and its descendants, if any.

    DELETE
    /integration/v1/<otype>/<id>/

  2. Delete all the data objects of type=otype and ids specified in the request body, together with their descendants, if any.

    DELETE
    /integration/v1/<otype>/

Replace <otype> with one of the following:

schema

table

column

The request body for 2 should look like

{"key":"<ds_id>.<schema_name>"} (for schema)
{"key":"<ds_id>.<schema_name>.<table_name>"} (for table)
{"key":"<ds_id>.<schema_name>.<table_name>.<column_name>"} (for column)

NOTE:

  1. Object names are case-insensitive.
  2. A request body can have multiple JSON objects, where each line should be a JSON object specifying a data object to delete. To avoid JSON parsing errors, do not break a line in the middle of a JSON object.
  3. A request body should contain JSON data of a single otype.

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

{ "job_id": 1 }

NOTE: The response is the identifier of a job record that tracks the status of the job triggered after a successful call to the API. This job is responsible for uploading lineages to Alation. To know the status of the job, please refer Job Status API (/api/v1/bulk_metadata/job/?id=<job_id>).

Error Response

Invalid Token

Status: 403 Forbidden

Body

{ "detail": "Authentication failed" }

Missing Token Header

Status: 403 Forbidden

Body

{ "detail": "Authentication credentials were not provided." }

Code Samples

cURL

#!/bin/bash

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

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

curl -X DELETE "${BASE_URL}schema/" -H 'content-type: application/json' -H "TOKEN: ${API_TOKEN}" -d $'{"key":"1.test_schema"}'

or

curl -X DELETE "${BASE_URL}schema/3" -H "TOKEN: ${API_TOKEN}"

Python

import requests import json # This is an example token. Please replace this with yours. headers = {'Token': '2abcd-4c04-4c21-8692-eda27a877f90', 'content-type': 'application/json'} data = '{"key":"1.test_schema"}' # Delete test_schema. response = requests.delete('https://alation.yourcompany.com/integration/v1/schema/', data=data, headers=headers) job_id = json.loads(response.content)['job_id'] print "Job id: %s" % job_id # Check the status of the job response = requests.get('https://alation.yourcompany.com/api/v1/bulk_metadata/job/?id=%s' % job_id, headers=headers) job_details = json.loads(response.text) print job_details