Purpose of Lionfish is to help you with:
- extracting SQL statements from your database
- uploading the result to our API
By default Lionfish reads the database catalogue only (definitions of stored procedures, views, database links, synonyms, etc.). You can modify such default behaviour to include the custom SQL statements as well.
Before we start: how Lionfish works?
It connects to database and executes SQL statements located in
sql directory. For example to extract all Views definitions from Oracle it executes:
v.VIEW_NAME as name,
v.OWNER||'.'||v.VIEW_NAME as groupName,
'##DBNAME##' as databaseName,
v.OWNER as schemaName
FROM all_views v
note: see the full statement in your Lionfish directory or on GitHub
The result is stored into a JSON file on your computer. When you click
Send file(s) it gets uploaded to our API.
How to add a custom SQL code?
Let's say you have a table called
ETL_LOG. In it you store SQL queries executed by your ETL scheduler. Your goal is to tell Lionfish how to locate your custom table.
All you have to do is modify a file:
sql/oracle/queries/custom/cmd.sql (see it on GitHub)
EXAMPLE: extract SQL statements from a table called ETL_LOG
Lionfish will run the following query and append the result automatically into a JSON file. The only limitation is that the result set must have 5 column. Explanation of each column is bellow and the order of columns must be kept.
sql_query_stmt as sourceCode,
sql_name as name, -- can be left empty
sql_group_name as groupName, -- can be empty
'my-database-name' as databaseName
'SCOTT' as schemaName
Create your own SQL query and the result will be send to our server.
Needs to be a valid SQL statement as executed on the database.
Is displayed as a query name in the UI.
Currently ignored in the UI.
Place name of the database on which the SQL statement was originally executed.
note: you can use
##DBNAME## and Lionfish will replace this automatically with the name of the database it connected to.
Important to resolve default schema lookup. For example if you submit:
We cannot tell in which schema the table
ACCOUNTS resides. With schemaName specified ( like 'SCOTT') we perform lookup for