HomeGuidesRecipesAPI ReferencePython SDK
Alation Help Center
API Reference

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///

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

    GET
    /integration/v1//?

Replace with one of the following:

datasource

schema

table

column

Replace 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///

  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//

Replace 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)