Gudu SQLFlow Product Docs
  • 1. Introduction
    • What is Gudu SQLFlow?
      • What SQLFlow can do
      • Architecture Overview
    • Getting Started
      • Sign up a new account
        • Team Management
        • Delete My Account
        • Activate by entering a coupon
      • How to use SQLFlow
      • Different modes in Gudu SQLFlow
        • Query mode
        • Job mode
      • Basic Usage
      • Convert SQL to E-R Diagram
      • Colors in SQLFlow Diagram
      • Show call relationship
    • Installation
      • Version and Users
        • Cloud and On-Premise version
        • SQLFlow before Version 6
          • For older version SQLFlow under Linux
          • For older version SQLFlow under MacOS
          • For older version SQLFlow under Windows
      • Linux
      • MacOS
      • Windows
      • Docker
      • Clickhouse Installation
        • Clickhouse For CentOs
        • Clickhouse For Ubuntu/Debian/RHEL
      • Troubleshooting
      • Upgrade
      • Third Party Components
      • Renew License File
    • UI
      • SQLText Editor
      • Schema Explorer
      • Diagram Panel
      • Settings
      • Job Management
        • Job Sources
    • Dlineage Tool
      • Overview
      • Usage
        • Analyze data linege from SQL files
        • Analyze data linege from a database
        • Resolve the ambiguous columns in SQL query
        • Map the DataFlowAnalyzer and the settings on SQLFlow UI
        • Settings
      • Dataflow.xml structure
      • FAQ
  • 2. CONCEPTS
    • Data Lineage Basics
      • Dataflow
        • Relations generated by SQLFlow
      • Direct Dataflow
      • Indirect Dataflow
      • Aggregate function and Dataflow
      • Dataflow chain
    • Data Lineage Format Reference
  • 3. API Docs
    • Prerequisites
    • Using the Rest API
    • SQLFlow Rest API reference
      • User Interface
      • Generation Interface
        • /sqlflow
        • /sqlflow/selectedgraph/table_level_lineage
        • /sqlflow/selectedgraph/image
        • /sqlflow/graph
        • /sqlflow/graph/table_level_lineage
        • /sqlflow/graph/image
        • /sqlflow/downstreamGraph
        • /sqlflow/upstreamGraph
        • /sqlflow/erdiagramSelectGraph
        • /sqlflow/leftMostSourceTableGraph
      • Job Interface
        • /submitUserJob
        • /displayUserJobSummary
        • /displayUserJobsSummary
        • /exportLineageAsJson
        • /exportFullLineageAsJson
        • /exportLineageAsGraphml
        • /submitPersistJob
        • /displayUserLatestJobTableLevelLineage
      • Export Image
      • Export CSV
        • /sqlflow/exportFullLineageAsCsv
        • /job/exportFullLineageAsCsv
    • Swagger UI
    • Export the data lineage result
    • Python
      • Basic Usage
      • Advanced Usage
    • SQL Parser API
      • checkSyntax
  • 4. SQLFlow Widget
    • Widget Get started
    • Usages
    • Widget API Reference
  • 5. Databases
    • Database Objects
      • Azure
      • DB2
  • 6. SQLFlow-ingester
    • Introduction
      • SQLFlow-Exporter
      • SQLFlow-Extractor
      • SQLFlow-Submitter
    • Get Started
      • SQL Server
    • SQLFlow-Ingester Java API Usage
    • Understand the format of exported data
      • Oracle
      • Microsoft SQL Server
      • MySQL
      • PostgreSQL
    • List of Supported dbVendors
    • Git Repo
    • Third Party Components
  • 7. Reference
    • Lineage Model
      • Json Format Lineage Model
      • XML Format Lineage Model
      • Data Lineage Elements
    • Database Model
  • 8. other
    • FAQ
      • Handling Internal Database
      • Delete Your Account
      • Table Form Data Without Intermediates
      • Not all schema exported from Oracle
      • Lineage Customization
    • Roadmap
    • SQL Samples
      • Exchange table partition
      • Generate relationship for renamed table
      • Snowflake table function lineage detection
    • Change Logs
    • SQLFlow with Oracle XML functions
    • Major Organizations Utilizing SQLFlow
Powered by GitBook
On this page
  • Sample
  • Sample Indication
  • Get More Details
  1. 6. SQLFlow-ingester
  2. Understand the format of exported data

Microsoft SQL Server

metadata.json exported by ingester has the same structure as the dbobjs.servers part of the Dlineage tool result

This page gives a sample metadata for Microsoft SQL Server.

Sample

{
    "createTime": "2022-11-16 21:05:32",
    "createdBy": "sqlflow-ingester v1.1.7",
    "physicalInstance": "115.159.xx.xx",
    "servers": [
        {
            "databases": [
                {
                    "name": "HR",
                    "schemas": [
                        {
                            "name": "[dbo]",
                            "synonyms": [
                                {
                                    "database": "[HR]",
                                    "name": "OFFICES",
                                    "schema": "[dbo]",
                                    "sourceDbLinkName": "HR",
                                    "sourceName": "LOCATIONS",
                                    "sourceSchema": "DBO"
                                }
                            ],
                            "tables": [
                                {
                                    "columns": [
                                        {
                                            "comment": "",
                                            "dataType": "char(2)",
                                            "name": "COUNTRY_ID"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(40)",
                                            "name": "COUNTRY_NAME"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "REGION_ID"
                                        }
                                    ],
                                    "databaseName": "[HR]",
                                    "name": "COUNTRIES",
                                    "schemaName": "[dbo]",
                                    "type": "table"
                                },
                                {
                                    "columns": [
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "DEPARTMENT_ID"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(30)",
                                            "name": "DEPARTMENT_NAME"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "MANAGER_ID"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "LOCATION_ID"
                                        }
                                    ],
                                    "databaseName": "[HR]",
                                    "name": "DEPARTMENTS",
                                    "schemaName": "[dbo]",
                                    "type": "table"
                                },
                                {
                                    "columns": [
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "EMPLOYEE_ID"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(20)",
                                            "name": "FIRST_NAME"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(25)",
                                            "name": "LAST_NAME"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(25)",
                                            "name": "EMAIL"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(20)",
                                            "name": "PHONE_INT"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "date",
                                            "name": "HIRE_DATE"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(10)",
                                            "name": "JOB_ID"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "SALARY"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "COMMISSION_PCT"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "MANAGER_ID"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "DEPARTMENT_ID"
                                        }
                                    ],
                                    "databaseName": "[HR]",
                                    "name": "EMPLOYEES",
                                    "schemaName": "[dbo]",
                                    "type": "table"
                                },
                                {
                                    "columns": [
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "EMPLOYEE_ID"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "date",
                                            "name": "START_DATE"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "date",
                                            "name": "END_DATE"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(10)",
                                            "name": "JOB_ID"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "DEPARTMENT_ID"
                                        }
                                    ],
                                    "databaseName": "[HR]",
                                    "name": "JOB_HISTORY",
                                    "schemaName": "[dbo]",
                                    "type": "table"
                                },
                                {
                                    "columns": [
                                        {
                                            "comment": "",
                                            "dataType": "varchar(10)",
                                            "name": "JOB_ID"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(35)",
                                            "name": "JOB_TITLE"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "MIN_SALARY"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "MAX_SALARY"
                                        }
                                    ],
                                    "databaseName": "[HR]",
                                    "name": "JOBS",
                                    "schemaName": "[dbo]",
                                    "type": "table"
                                },
                                {
                                    "columns": [
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "LOCATION_ID"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(40)",
                                            "name": "STREET_ADDRESS"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(12)",
                                            "name": "POSTAL_CODE"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(30)",
                                            "name": "CITY"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(25)",
                                            "name": "STATE_PROVINCE"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "char(2)",
                                            "name": "COUNTRY_ID"
                                        }
                                    ],
                                    "databaseName": "[HR]",
                                    "name": "LOCATIONS",
                                    "schemaName": "[dbo]",
                                    "type": "table"
                                },
                                {
                                    "columns": [
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "REGION_ID"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(25)",
                                            "name": "REGION_NAME"
                                        }
                                    ],
                                    "databaseName": "[HR]",
                                    "name": "REGIONS",
                                    "schemaName": "[dbo]",
                                    "type": "table"
                                }
                            ],
                            "views": [
                                {
                                    "columns": [
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "[employee_id]"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(10)",
                                            "name": "[job_id]"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "[manager_id]"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "[department_id]"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "[location_id]"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "char(2)",
                                            "name": "[country_id]"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(20)",
                                            "name": "[first_name]"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(25)",
                                            "name": "[last_name]"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "[salary]"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "int",
                                            "name": "[commission_pct]"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(30)",
                                            "name": "[department_name]"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(35)",
                                            "name": "[job_title]"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(30)",
                                            "name": "[city]"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(25)",
                                            "name": "[state_province]"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(40)",
                                            "name": "[country_name]"
                                        },
                                        {
                                            "comment": "",
                                            "dataType": "varchar(25)",
                                            "name": "[region_name]"
                                        }
                                    ],
                                    "databaseName": "[HR]",
                                    "name": "[EMP_DETAILS_VIEW]",
                                    "schemaName": "[dbo]",
                                    "type": "view"
                                }
                            ]
                        }
                    ]
                }
            ],
            "dbLinks": [],
            "dbVendor": "dbvmssql",
            "name": "115.159.xx.xx",
            "queries": [
                {
                    "database": "DWDB",
                    "groupName": "",
                    "name": "SPREPORTER_COMPARE_OVERVIEW_BATCHUNIQUEHASHINFO",
                    "schema": "READTRACE",
                    "sourceCode": "\r\ncreate procedure ReadTrace.spReporter_Compare_Overview_BatchUniqueHashInfo\r\nas\r\nbegin\r\n\tset nocount on\r\n\r\n\tselect 'Matching' as [Desc],\r\n\t\t\tcount_big(*) as [Count]\r\n\t\t\tfrom ReadTrace.tblUniqueBatches b\r\n\t\t\tinner join ReadTraceCompare.tblUniqueBatches c\r\n\t\t\t\ton b.HashID = c.HashID\r\n\t\r\n\tunion all\r\n\t\tselect 'BO' as [Desc],\r\n\t\t\t\tcount_big(*) \r\n\t\t\t\tfrom ReadTrace.tblUniqueBatches b\r\n\t\t\t\tleft outer join ReadTraceCompare.tblUniqueBatches c\r\n\t\t\t\t\ton b.HashID = c.HashID\r\n\t\t\t\twhere c.HashID is NULL\r\n\r\n\tunion all\r\n\t\tselect 'CO' as [Desc],\r\n\t\t\t\tcount_big(*) \r\n\t\t\t\tfrom ReadTrace.tblUniqueBatches b\r\n\t\t\t\tright outer join ReadTraceCompare.tblUniqueBatches c\r\n\t\t\t\t\ton b.HashID = c.HashID\r\n\t\t\t\twhere b.HashID is NULL\r\n\torder by [Desc]\r\nend ",
                    "type": "procedure"
                }
            ],
            "supportsCatalogs": true,
            "supportsSchemas": true
        }
    ]
}

Sample Indication

The above sample has the same structure as

{
    "createTime":"", //export time
    "createdBy":"sqlflow-exporter",//name of the export tool
    "physicalInstance":"",//server address
    "servers":[
        {
            "name":"",//server name
            "dbVendor":"",//database type,possible values are: dbvathena,dbvazuresql,dbvbigquery,dbvcouchbase,dbvdb2,dbvgreenplum,dbvhana,dbvhive,dbvimpala,dbvinformix,dbvmdx,dbvmysqldbvnetezza,dbvopenedge,dbvoracle,dbvpostgresql,dbvpresto,dbvredshift,dbvsnowflake,dbvsparksql,dbvmssql,dbvsybase,dbvteradata,dbvvertica
            "supportsCatalogs":true,// has database layer
            "supportsSchemas":true,// has schema layer
            "databases":[ // No extra db unit field outside the database list because all units belong to the specific schema and database.
                {
                    "name":"",//database name
                    "schemas":[
                        {
                            "name":"",//schema name
                            "synonyms":[
                                {
                                    "name":"",
                                    "sourceName":"",
                                    "sourceSchema":"",
                                    "sourceDbLinkName":""
                                }
                            ],
                            "sequences":[
                                {
                                    "name":"",
                                    "incrementBy":""
                                }
                            ],
                            "tables":[
                                {
                                    "name":"",//table name 
                                    "columns":[
                                        {
                                            "dataType":"",//column data type
                                            "name":"",//column name
                                            "comment":""//column comment
                                        }
                                    ]
                                }
                            ],
                            "views":[ 
                              {
                                    "name":"",//view name
                                    "columns":[
                                        {
                                            "dataType":"",
                                            "name":"",
                                            "comment":""
                                        }
                                    ]
                                }
                            ],
                            "others":[//others, including resultset, variable, path etc 
                                {
                                    "name":"",
                                    "columns":[
                                        {
                                            "dataType":"",
                                            "name":"",
                                            "comment":""
                                        }
                                    ]
                                }
                            ],
                            "packages":[
                                {
                                    "name":"",//package name 
                                    "procedures":[//prcedures in the package
                                    ],
                                    "functions":[//functions in the package
                                    ],
                                    "triggers":[//triggers in the package
                                    ]
                                }
                            ],
                            "procedures":[
                                {
                                    "name":"",
                                    "type":"",
                                    "arguments":[
                                        {
                                            "name":"",
                                            "dataType":"",
                                            "inout":""
                                        }
                                    ]
                                }
                            ],
                            "functions":[
                                {
                                    "name":"",
                                    "type":"",
                                    "arguments":[
                                        {
                                            "name":"",
                                            "dataType":"",
                                            "inout":""
                                        }
                                    ]
                                }
                            ],
                            "triggers":[
                                {
                                    "name":"",
                                    "type":"",
                                    "arguments":[
                                        {
                                            "name":"",
                                            "dataType":"",
                                            "inout":""
                                        }
                                    ]
                                }
                            ]
                        }
                    ],
            "dbLinks":[
                {
                    "owner":"",
                    "name":"",
                    "userName":"",
                    "host":""
                }
            ],
            "queries":[//DDL scripts in database
                {
                    "database":"",
                    "schema":"",
                    "name":"",
                    "type":"",
                    "sourceCode":"",
                    "groupName":""
                }
            ]
        }
    ],
    "errorMessages":[//errors during the exporting 
        {
            "errorMessage":"",
            "errorType":"",
            "file":""
        }
    ]
  }

Get More Details

PreviousOracleNextMySQL

Last updated 2 years ago

You can refer to to understand more about what does each field mean.

this section