Grabit yes Salmon data kinship analyzer It collects SQL scripts from various data sources and then uploads them to salmon for data kinship analysis. The analysis results can be viewed in salmon. At the same time, the data kinship results will be extracted into the local directory.

At present, grab supports two modes of operation: graphical interface and command line. This paper mainly introduces how grab uses the command line to obtain DDL from the database.

Get DDL

• download Grabit
• In the Grbit directory, refer to conf template / dbname config template JSON configuration file, create a new configuration file, and fill in the database connection information, databaseServer object, salmon server connection information, SQLFlowServer, database type, databaseType and other parameters. Please refer to the official document for the specific meaning of the parameters: Grabit Using Document
• Specify the configuration file path / F conf path and run the start script
• In data / job / metadata / metadata JSON file to view the metadata data exported from the database
• In logs / grab Log to view the operation log. After success, view the results on the Joblist page on the salmon

Custom SQL get DDL

The specified DDL is obtained from the database maintained in SQL SQL conf Download these SQL.
The conf directory is the SQL executed by grab to obtain the DDL of each database. By executing these SQL, grab the table, view and stored procedure DDL.

For example, get oracle's query SQL of SQL script:

SELECT
	v.text sourceCode,
	'view' as dbOjbType,
	'"'||v.VIEW_NAME||'"' as dbObjName,
	'"'||v.OWNER||'"."'||v.VIEW_NAME||'"' as groupName,
	'"'||'%database'||'"' as databaseName,
	'"'||v.OWNER||'"' as schemaName
FROM all_views v where v.OWNER = '%schema' and v.OWNER not in ('SYS', 'SYSTEM');

SELECT
	v.text sourceCode,
	'procedure' as dbOjbType,
	'"'||v.name||'"' as dbObjName,
	'"'||v.OWNER||'"."'||v.name||'"' as groupName,
	'"'||'%database'||'"' as databaseName,
	'"'||v.OWNER||'"' as schemaName
FROM all_source v where type='PROCEDURE' and v.OWNER = '%schema' and v.OWNER not in ('SYS', 'SYSTEM')
UNION ALL
SELECT
	v.text sourceCode,
	'trigger' as dbOjbType,
	'"'||v.name||'"' as dbObjName,
	'"'||v.OWNER||'"."'||v.name||'"' as groupName,
	'"'||'%database'||'"' as databaseName,
	'"'||v.OWNER||'"' as schemaName
FROM all_source v where type='TRIGGER' and v.OWNER = '%schema' and v.OWNER not in ('SYS', 'SYSTEM')
UNION ALL
SELECT
	v.text sourceCode,
	'function' as dbOjbType,
	'"'||v.name||'"' as dbObjName,
	'"'||v.OWNER||'"."'||v.name||'"' as groupName,
	'"'||'%database'||'"' as databaseName,
	'"'||v.OWNER||'"' as schemaName
FROM all_source v where type='FUNCTION' and v.OWNER = '%schema' and v.OWNER not in ('SYS', 'SYSTEM');

SELECT
	mv.QUERY sourceCode,
	'materialized view' as dbOjbType,
	'"'||mv.MVIEW_NAME||'"' as dbObjName,
	'"'||mv.OWNER||'"."'||mv.MVIEW_NAME||'"' as groupName,
	'"'||'%database'||'"' as databaseName,
	'"'||mv.OWNER||'"' as schemaName
FROM all_mviews mv where mv.OWNER = '%schema' and mv.OWNER not in ('SYS', 'SYSTEM');

Of course, grait also supports customizing these SQL. If you want to customize SQL to obtain DDL, you need to write SQL according to the following result set structure.

1. The result of each SQL query must contain five structures:

• DDL text obtained by sourceCode
• dbOjbType type, such as table, view, function, etc
• dbObjName name
• groupName group name
• The name of the database to which databaseName belongs
• schema name

For example:

SELECT
	v.text sourceCode,
	'function' as type,
	'"'||v.name||'"' as name,
	'"'||v.OWNER||'"."'||v.name||'"' as groupName,
	'"'||'%database'||'"' as databaseName,
	'"'||v.OWNER||'"' as schemaName
FROM all_source v where type='FUNCTION' and v.OWNER = '%schema' and v.OWNER not in ('SYS', 'SYSTEM')

2. Name the SQL script as the name of the SQL script file in the conf template directory you want to overwrite
3. Create a new conf/dbname directory under the current graphite working directory, where dbname is the database name of the script you want to replace, which is consistent with the name in the template directory, such as oracle
4. Finally, put the customized script in the current conf/dbname / directory.

For example, you want to modify / conf / Oracle / query SQL script, you need to put the customized SQL in conf / Oracle / query SQL file.
Refer to conf template / dbname config template JSON configuration file, create a new configuration file, fill in the connection information of the database, and finally run the start script.

reference resources

Salmon data kinship analysis tool Chinese website: https://www.sqlflow.cn

Ha fish online blood relationship analysis tool: https://sqlflow.gudusoft.com

Document used by Grabit: https://github.com/sqlparser/sqlflow_public/tree/master/grabit