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///
-
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
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
.
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
.
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=1Sample 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///
-
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:
- 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"}'
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)