Json Format Lineage Model
This page gives a detail reference of the data lineage response in Json format. Json lineage model can be returned by SQLFlow server or Dlineage tool with
/json
flag. The SQLFlow UI also get this result from the /sqlflow/generation/sqlflow/graph endpoint.
Please refer to here for the XML response.Let's get into details and check the data lineage json resposne:
{
"code": 200,
"data": {
"mode": "global",
"summary": {
},
"sqlflow": {
},
"graph": {
}
},
"sessionId": "3bf4a61f5d41a016d0f91f28c2c1791d7100e1c159c31dab4e1f3bce603afd1c_1663684880303",
"jobId": "JobId",
"error": "error msgs",
}
- code: Http Status code, 200 for OK. 4XX **** for cases in which the client seems to have erred such as no authorization or bad request. 500 for internal server error. Error messages would be present in
error
If the code is not 200 (request is not a success). - data: data payload
- mode: data mode. Could be
global
orsummary
. Will be set to _summary
_ mode when the relation number exceeds therelation_limit
global
show all datasummary
only share the statics information and there's no graph information. No field data in the table and only table info. Users need to invoke REST Api to get the field data in detail.
- error: contains error messages if the status code is not 200
"summary": {
"schema": 1,
"process": 2,
"database": 0,
"view": 1,
"mostRelationTables": [{
"table": "SMALL_ORDERS"
}, {
"table": "MEDIUM_ORDERS"
}, {
"table": "LARGE_ORDERS"
}],
"column": 43,
"relationship": 41,
"table": 7
}
- database: database number
- schema: schema number
- table: table number
- view: view number
- column: column number
- relationship: relationship number
- process: process number
- mostRelationTables: the top three tables which contain the most relationships
"sqlflow": {
"dbobjs": {
"createdBy": "grabit v1.7.0",
"servers": [{}]
},
"relationships": [{}]
}
sqlflow payload contains two nodes. dbojbs and relationship.
- dbojbs: metadata, contains information of instance, db, schema, table, view, storage procedure, function, trigger, dblink, sequence, ddl etc..
{
"servers": [
{
"name": "default_server",
"dbVendor": "dbvmysql",
"supportsCatalogs": true,
"supportsSchemas": false,
"databases": [{
"name": "DEFAULT",
"tables": [{
"columns": [{
"name": "field9",
"id": "110"
}, {
"name": "field1",
"id": "111"
}, {
"name": "field5",
"id": "116"
}],
"id": "96",
"name": "table1",
"type": "table"
}]
}]
}, {
"dbVendor": "dbvmssql",
"name": "default_server",
"supportsCatalogs": true,
"supportsSchemas": true,
"databases": [{
"name": "DEFAULT",
"schemas": [{
"name": "HumanResources",
"tables": [{
"columns": [{
"name": "BusinessEntityID",
"id": "13"
}, {
"name": "HireDate",
"id": "14"
}],
"id": "12",
"name": "Employee",
"type": "table"
}]
}]
}]
}, {
"dbVendor": "dbvsupportschemaonly",
"name": "DB_SERVER_SCHEMA_ONLY",
"supportsCatalogs": false,
"supportsSchemas": true,
"schemas": [{
"name": "OE",
"tables": [{
"columns": [{
"name": "ORDER_ID",
"id": "57"
}],
"id": "56",
"name": "ORDERS",
"type": "table"
}],
"packages": [{
"id": "26",
"name": "package1",
"procedures": [{
"name": "procedure1",
"type": "procedure"
}]
}, {
"id": "42",
"name": "package2",
"procedures": [{
"name": "procedure1",
"type": "procedure"
}]
}]
}]
},
]
}
The top element of the dbobjs payload is an array and the array representing different server instances. For each server instance, we will have:
- 1.dbVendor: database type
- 2.name: instance name
- 3.
- 4.
- 5.databases: present if support database
- 6.schemas: present if database is not supported and only schema is supported.
- 7.dbLinks: dbLinks will be present if the resposne json is generated from metadata. Will not be present If the response json is generated from dataflow
- 8.queries: present if the response json is generated from metadata
- 9.
tips: the above structure is same as the
servers
part of the metadata result from Dlineage tool as well as Ingester.- 1.if supportsCatalogs=true,supportsSchemas=true:
- server-->database-->schema-->tables/views/others/packages/procedures/functions/triggers
- 2.if supportsCatalogs=true,supportsSchemas=false:
- server-->database-->tables/views/others/packages/procedures/functions/triggers
- 3.if supportsCatalogs = false, supportsSchemas = true:
- server --> schema --> tables/views/others/packages/procedures/functions/triggers
Database node and Schema node may contain other information indicating the data of
procedure
, trigger
, function
"others": [
{
"id": "85",
"name": "INSERT-SELECT-1",
"type": "insert-select",
"columns": [
{
"id": "84",
"name": "RelationRows",
"coordinates": [
{
"hashCode": "0",
"x": 30,
"y": 8
},
{
"hashCode": "0",
"x": 31,
"y": 56
}
],
"source": "system",
"uiVisible": false
}, ...
]
Relationship is the atom unit of the data lineage. Relationship builds a link between the source and target column (column-level lineage).
A relation includes the
type
, target
, sources
and other attributes."relationships": [{
"id": "56",
"type": "fdd",
"effectType": "insert",
"target": {
"id": "102",
"column": "CL",
"parentId": "52",
"parentName": "MEDIUM_ORDERS",
"coordinates": [{
"hashCode": "0",
"x": 31,
"y": 36
}, {
"hashCode": "0",
"x": 31,
"y": 38
}]
},
"sources": [{
"id": "90",
"column": "CL",
"parentId": "85",
"parentName": "INSERT-SELECT-1",
"coordinates": [{
"hashCode": "0",
"x": 31,
"y": 21
}, {
"hashCode": "0",
"x": 31,
"y": 38
}]
}],
"processId": "48"
}]
- id: relation id
- type: relation type, could be
fdd
,fdr
,join
, orcall
- function: present if the relationship is about function
- effectType: effect type of the relation, based on STMT
- processId: process id by which the relation is generated
- timestampMin: the earliest time when the relationship is generated
- timestampMax: the latest time when the relationship is generated
- id
- name
- column
- columnType
- sourceId
- sourceName
- parentId
- parentName
- clauseType
- function
- type
- coordinates
Check here for more details
"graph": {
"relationshipIdMap": {
"e0": ["5519", "fdd"],
},
"elements": {
"tables": [{
"columns": [{
"height": 16.0,
"id": "n0::n0",
"label": {
"content": "oid",
"fontFamily": "Segoe UI Symbol",
"fontSize": "12",
"height": 13.96875,
"width": 35.0,
"x": 0.0,
"y": 0.0
},
"width": 160.0,
"x": 575.0,
"y": -565.9073
}],
"id": "n4",
"label": {
"content": "customers",
"fontFamily": "Segoe UI Symbol",
"fontSize": "12",
"height": 17.96875,
"width": 162.0,
"x": 0.0,
"y": 0.0
},
"width": 162.0,
"height": 57.96875,
}],
"edges": [{
"id": "e0",
"sourceId": "n9::n5",
"targetId": "n5::n5"
}
},
"tooltip": {},
"listIdMap": {
"n0": ["68"],
"n0::n0": ["110"],
}
}
- relationIdMap:
- Mapping list between the graph ui id and the relationship id
- listIdMap:
- Mapping list between graph ui id and the graph model id
- elements:
- tables
- id: table id, will be generated into UI model by mappings in the listIdMap
- table: table name
- width: table width
- height: table height
- x:table x-axis (horizontal) coordinate
- y:table y-axis (vertical) coordinate
- columns
- id: columns id, will be generated into UI model by mappings in the listIdMap
- x:column x-axis (horizontal) coordinate
- y:column y-axis (vertical) coordinate
- edges
- id: edge id, mapped with relationship id and type through relationIdMap
- sourceId: source column id
- targetId: target column id
Last modified 3mo ago