SQLFlow-exporter is able to get the metadata from different databases.

SQLFlow-Exporter Usage

Under Linux:

./exporter.sh -host -port 1521 -db orcl -user scott -pwd tiger -save /tmp/sqlflow-ingester -dbVendor dbvoracle

Under Windows:

exporter.bat -host -port 1521 -db orcl -user scott -pwd tiger -save c:\tmp\sqlflow-ingester -dbVendor dbvoracle

Or you can directly execute the .jar package, the jar packages are under the ./lib folder:

java -jar sqlflow-exporter-1.0.jar  -host 106.54.xx.xx -port 1521 -db orcl -user username -pwd password -save d:/ -dbVendor dbvoracle

A metadata.json file will be generated after successfully export metadta from the database.

Example for the success output:

exporter metadata success: <-save>/metadata.json


-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) -host: Database host name (ip address or domain name) -port: Port number -db: Database name -user: User name -pwd: User password -save: Destination folder path where we put the exported metadata json file. The exported file will be in name as metadata.json. -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. -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. -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. -merge: Merge the metadata results which are exported in different process. Use comma to split files to merge. Check here for more details.

Access Control in Oracle

Since SQLFlow-Ingester 1.2.2, a user with limited access can be used to export database metadata for Oracle with SELECT_CATALOG_ROLE.

create user grq identified by 123456;

A user with SELECT_CATALOG_ROLE will have access to all DBA_TABLES / DBA_VIEWS but he cannot access to the content of any table. This helps to avoid creating a powerful user and to give only necessary access.

Improve Export Performance

Time consumed during the export from database could be very long if the data volume is huge. Following actions are made to improve the Ingester export performance:

  • JDBC fetchsize is set to 1000 for table and view. For sql of view and process the fetchsize is set to 500. (check this oracle doc for what is jdbc fetchsize: https://docs.oracle.com/middleware/1212/toplink/TLJPA/q_jdbc_fetch_size.htm#TLJPA647)

  • Parameters extractedDbsSchemas, excludedDbsSchemas and extractedViews are improved for oracle and postgresql. A plan has already been made to improve the implementation of these parameters for other databases.

-merge can be used to merge the metadata results. Use comma to split files to merge. All files under the folder will be merged if the given value is the folder path.

exporter.bat -dbVendor dbvpostgresql -extractedDbsSchemas kingland.dbt%,kingland.pub%  -host -port 5432 -db kingland -user bigking -pwd Cat_*** -save D:/out/1.json
exporter.bat -dbVendor dbvpostgresql -extractedDbsSchemas kingland.sqlflow  -host -port 5432 -db kingland -user bigking -pwd Cat_*** -save D:/out/2.json
exporter.bat -merge  D:/out/1.json,D:/out/2.json    -save D:/out/merge.json
exporter.bat -merge  D:/out/   -save D:/out/merge.json

Last updated