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:

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

The physical metadata for an object includes:

Object name : Name of the object
Data type : Data type of a column
Whether the object is a view

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

URL

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

GET
/integration/v1/<otype>/<id>/
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

Name	Description	Applies to
title	Title given to the object, allow case-insensitive, partial match Example: /integration/v1/datasource/?title=Employee	All types
name	Name of the object, allow case-insensitive, partial match Example: /integration/v1/table/?name=employee_table	Schema Table Column
ds_id	Unique identifier of the data source the objects belong to Example: /integration/v1/schema/?ds_id=1	Schema Table Column
schema_id	Unique identifier of the schema the objects belong to Example: /integration/v1/table/?schema_id=1	Table Column
schema_name	Name of the schema the tables belong to, allow case-insensitive, partial match Example: /integration/v1/table/?schema_name=emp_schema	Table
data_type	The data type of a column Example: /integration/v1/column/?data_type=number	Column
nullable	The 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_id	Unique identifier of the table the columns belong to Example: /integration/v1/column/?table_id=1	Column
table_name	Name of the table the columns belong to, allow case-insensitive, partial match Example: /integration/v1/column/?table_name=emp_schema.emp_table	Column
limit	Limit the number of objects returned Example: /integration/v1/datasource/?limit=10	All types
skip	Skip 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 Header	Value
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

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

DELETE
/integration/v1/<otype>/<id>/
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:

Object names are case-insensitive.
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.
A request body should contain JSON data of a single otype.

Headers

HTTP Header	Value
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"}'

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