{"id":2599,"date":"2023-01-08T03:06:48","date_gmt":"2023-01-08T08:06:48","guid":{"rendered":"https:\/\/www.dpriver.com\/blog\/?p=2599"},"modified":"2023-01-15T02:50:00","modified_gmt":"2023-01-15T07:50:00","slug":"get-data-lineage-from-an-internal-database-with-sqlflow","status":"publish","type":"post","link":"https:\/\/www.dpriver.com\/blog\/2023\/01\/get-data-lineage-from-an-internal-database-with-sqlflow\/","title":{"rendered":"Get data lineage from an internal database with SQLFlow"},"content":{"rendered":"\n

Gudu SQLFlow is an automated data lineage tool that specializes in analyzing SQL queries to discover and visualize data lineage that shows the complete data cycle. You can refer to this blog<\/a> for the introduction.<\/p>\n\n\n\n

SQLFLow UI site<\/figcaption><\/figure>\n\n\n\n

The SQLFlow UI site<\/a> process thousands of SQL every day and generates data lineages. It is able to read the data source from the online SQL editor, from the SQL scripts uploaded or from client’s database.<\/p>\n\n\n\n

One of the most frequent asked is how to use SQLFlow to retrieve data from an internal database where no external network is available<\/strong>. This is a typical usage scenario for our industry customers.<\/p>\n\n\n\n

One of the most obvious solutions for the above question is apparently to use Gudu SQLFlow On-Premise on your own server. To archive that, you will need to install and deploy the SQLFlow. <\/p>\n\n\n\n

However, what if you prefer not to install any third party software on your internal server? <\/p>\n\n\n\n

SQLFlow-Ingester is a tool that helps you to extract metadata from various database. Thanks to SQLFlow-Ingester, we can easily export the database metadata file. The metadata file can be used by Dlineage tool or you can upload the metadata file to SQLFlow UI site<\/a> to generate data lineage.<\/p>\n\n\n\n

## Retrieve metadata file<\/h2>\n\n\n\n

#### SQLFlow-Ingester script<\/h4>\n\n\n\n

Let’s say your server is on Linux:<\/p>\n\n\n\n

####

####

##

####

####

##

    sudo bash exporter.sh \n  -host 127.0.0.1 \n  -port 1521 \n  -db orcl \n  -user your_username\n  -pwd your_password \n  -save \/tmp\/sqlflow-ingester \n  -dbVendor dbvoracle<\/code><\/pre>\n\n\n\nAfter the execution of the script, you will have your internal database metadata file in the \/tmp\/sqlflow-ingester<\/code> folder. <\/p>\n\n\n\n                                                                    sqlflow-ingester<\/figcaption><\/figure>\n\n\n\nIf the database server is on Windows, the command is similar:<\/p>\n\n\n\nexporter.bat \n  -host 127.0.0.1 \n  -port 1521 \n  -db orcl \n  -user scott \n  -pwd tiger \n  -save c:\\tmp\\sqlflow-ingester \n  -dbVendor dbvoracle<\/code><\/pre>\n\n\n\nEmbed the SQLFlow-Ingester in your Java code<\/h4>\n\n\n\nIf you intend to analyze the database and get the data lineage on code level, you can embed the SQLFlow-Ingester in your Java programme:<\/p>\n\n\n\n\/\/1.import the following Ingester library \nimport com.gudu.sqlflow.ingester.exporter.SqlflowExporter;\nimport com.gudu.sqlflow.ingester.library.domain.DataSource;\nimport com.gudu.sqlflow.ingester.library.domain.DbVendor;\nimport com.gudu.sqlflow.ingester.library.domain.sqlflow.SQLFlow;\nimport com.gudu.sqlflow.ingester.library.result.Result;\n\n\/\/2.Give the database parameters before invoking the Ingester\nDataSource source = new DataSource();\n\/\/database host name (ip address or domain name) \nsource.setHostname(\"localhost\");\n\/\/port\nsource.setPort(\"3306\");\n\/\/database name\nsource.setDatabase(\"test\");\n\/\/user namer\nsource.setAccount(\"root\");\n\/\/database password\nsource.setPassword(\"123456\");\n\/\/Database type and version number, Check \"List of Supported dbVerdors\" section for a full list of the supported databases.\n\/\/The second parameter can be null if no specific version need to be provided\nDbVendor dbVendor = new DbVendor(\"dbvmysql\", \"5.7\");\nsource.setDbVendor(dbVendor);\n\/\/Export the metadata, the result can be in object\/json string.\nSqlflowExporter exec = new SqlflowExporter();\n\/\/Take the result in SQLFLow object\nResult<SQLFlow> result1 = exec.exporterMetadata(source);\n\/\/Take the result in json string\nResult<String> result2 = exec.exporterMetadataString(source);<\/code><\/pre>\n\n\n\nJar package<\/h4>\n\n\n\nThe SQLFlow-Ingester is in Java so you can directly execute the .jar package if you have Java installed on your server, the jar packages are under the .\/lib<\/code> folder:<\/p>\n\n\n\njava -jar sqlflow-exporter-1.0.jar  -host 106.54.xx.xx-port 1521 -db orcl -user username -pwd password -save d:\/ -dbVendor dbvoracle<\/code><\/pre>\n\n\n\nFollowing are the description for the parameters we support with the tool.<\/p>\n\n\n\n-dbVendor: Database type, Check here for a full list of the supported databases. Use colon to split dbVendor and version if specific version is required. (<dbVendor>:<version>, such as dbvmysql:5.7) \n-host: Database host name (ip address or domain name) \n-port: Port number\n-db: Database name\n-user: User name \n-pwd: User password \n-save: Destination folder path where we put the exported metadata json file. The exported file will be in name as metadata.json. \n-extractedDbsSchemas: Export metadata under the specific schema. Use comma to split if multiple schema required (such as <schema1>,<schema2>). We can use this flag to improve the export performance.\n-excludedDbsSchemas:  Exclude metadata under the specific schema during the export. Use comma to split if multiple schema required (such as <schema1>,<schema2>). We can use this flag to improve the export performance.\n-extractedViews: Export metadata under the specific view. Use comma to split if multiple views required (such as <view1>,<view2>). We can use this flag to improve the export performance. \n-merge: Merge the metadata results which are exported in different process. Use comma to split files to merge. Check here for more details.<\/code><\/pre>\n\n\n\nYou will receive the message if the export is succeeded:<\/p>\n\n\n\nexporter metadata success: \/home\/leo\/workspace\/tmp\/gudu_ingester\/metadata.json<\/code><\/pre>\n\n\n\n                                                                       export success<\/figcaption><\/figure>\n\n\n\nGet data lineage from the metadata file<\/h2>\n\n\n\nSQLFlow UI<\/h4>\n\n\n\nWith the exported metadata.json<\/code>, we are now ready to retrieve data lineage.<\/p>\n\n\n\nOpen your browser and go to our SQLFlow UI<\/a>. Create a Job with the Job Source<\/code> as upload file<\/code>:<\/p>\n\n\n\n                                                        create job<\/figcaption><\/figure>\n\n\n\nCheck the lineage overview\/detail once the job is finished.<\/p>\n\n\n\n<\/figure>\n\n\n\nDlineage Tool<\/h4>\n\n\n\nAn alternative way to get the data lineage from the metadata file is to use Dlienage tool:<\/p>\n\n\n\njava -jar gudusoft.dlineage.jar \/t oracle \/f metadata.json<\/code><\/pre>\n\n\n\nConclusion<\/h2>\n\n\n\nWe can use sqlflow-ingester to export the metadata file from an internal database. The metadata file can be later used in SQLFlow UI<\/a> or Dlineage tool to retrieve the data lineage.<\/p>\n\n\n\nSQLFlow-Ingester download address: https:\/\/github.com\/sqlparser\/sqlflow_public\/releases <\/p>\n\n\n\nCheck https:\/\/www.gudusoft.com\/ for more information and find more extraordinary features of Gudu SQLFlow<\/p>\n","protected":false},"excerpt":{"rendered":"Gudu SQLFlow is an automated data lineage tool that specializes in analyzing SQL queries to discover and visualize data lineage that shows the complete data cycle. You can refer to this blog for the introduction. The SQLFlow UI site process thousands of SQL every day and generates data lineages. It is able to read the data source from the online SQL editor, from the SQL scripts uploaded or from client’s database. One of the most frequent asked is how to use SQLFlow to retrieve data from an internal database where no external network is available. This is a typical usage\u2026<\/p>\n","protected":false},"author":6,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[66,93],"tags":[35,27,115],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":5}},"yoast_head":"\nGet data lineage from an internal database with SQLFlow<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n