Purpose of Lionfish is to help you with:

  1. extracting SQL statements from your database
  2. 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:

SELECT	
  v.text sourceCode,
  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.

SELECT
  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
FROM
  ETL_LOG

Create your own SQL query and the result will be send to our server.

sourceCode

Needs to be a valid SQL statement as executed on the database.

name (optional)

Is displayed as a query name in the UI.

groupName (optional)

Currently ignored in the UI.

databaseName

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.

schemaName

Important to resolve default schema lookup. For example if you submit:

SELECT ACC_ID
FROM ACCOUNTS

We cannot tell in which schema the table ACCOUNTS resides. With schemaName specified ( like 'SCOTT') we perform lookup for SCOTT.ACCOUNTS 

Did this answer your question?