Guidelines for Creating Queries for MDE

Query Structure

During MDE, Alation will query the system catalog tables of your database to retrieve the metadata. Find out which tables in your database have the metadata types you want to extract. The queries you will write will use your database type-specific syntax, but they all must include the required result set columns expected by Alation. These result set columns are mandatory. If a required column is not relevant to your data source, it still should be included in the query with the null value. Not including the required columns into a query will produce an error when you try to save the query.

For specific database types, the mandatory result set column names, such as CATALOG or SCHEMA may be part of reserved keywords. Use the appropriate escape characters to enclose the result set columns.

Example

For Oracle data sources, the result set column names should be escaped in double quotes:

SELECT  ''  AS "CATALOG",
   USERNAME AS "SCHEMA"
FROM SYS.ALL_USERS;

For MySQL, you can escape with backquotes :

SELECT
   catalog_name AS `CATALOG`,
   schema_name AS `SCHEMA`
FROM information_schema.schemata
   WHERE schema_name NOT IN ('information_schema');

See the tables below for the list of required result set columns for each metadata type that can be extracted and for the skeleton structure of the queries. For more examples, see Example Queries for Query-Based MDE.

Note

Some data sources, for example, DB2, do not have a “Catalog” concept. In such cases, an empty string '' will work.

In “Query Structure” sections below, such entities as <CATALOG> or <sys_schema.table> represent the columns in the system catalog tables that store the corresponding metadata or your query-specific data objects.

SCHEMA

Result Set Column (Mandatory)	Description	Data type
CATALOG	Name of the column that stores the required catalog	String
SCHEMA	Name of the column that stores schema names	String

Query Structure

SELECT <CATALOG> AS CATALOG
  <SCHEMA> AS SCHEMA
FROM <sys_schema.table>  WHERE <clause>  ORDER BY <clause>;

Example (MySQL)

SELECT
  catalog_name AS `CATALOG`,
  schema_name AS `SCHEMA`
FROM information_schema.schemata
  WHERE schema_name NOT IN ('information_schema', 'performance_schema', 'mysql', 'innodb', 'sys');

TABLE

Result Set Column (Mandatory)	Description	Data type
CATALOG	Name of the column that stores the required catalog	String
SCHEMA	Name of the column that stores schema names	String
TABLE	Name of the column that stores table names	String
TABLE_TYPE	Table type (Expected values are “TABLE” or “VIEW”))	String
REMARKS	Description/Remarks	String

Query Structure

SELECT <CATALOG> AS CATALOG,
  <SCHEMA> AS SCHEMA,
  <TABNAME> AS TABLE,
  <TABTYPE> AS TABLE_TYPE,
  <REMARKS> AS REMARKS
FROM <sys_schema.table>  WHERE <clause>  ORDER BY <clause>;

Example (MySQL)

SELECT
   table_catalog AS `CATALOG`,
   table_schema AS `SCHEMA`,
   table_name AS `TABLE`,
   CASE WHEN table_type = 'BASE TABLE' THEN `TABLE` ELSE `VIEW` END AS TABLE_TYPE,
   table_comment AS REMARKS
FROM information_schema.tables
    WHERE table_schema NOT IN ('information_schema' , 'performance_schema' , 'mysql' , 'innodb' , 'sys');

COLUMN

Result Set Column (Mandatory)	Description	Data type
CATALOG	Name of the column that stores the required catalog	String
SCHEMA	Name of the column that stores schema names	String
TABLE	Name of the column that stores table names	String
COLUMN	Name of the column that stores column names	String
TYPE_NAME	Name of the column that stores the column type	String
DATA_TYPE	Name of the column that stores the column data type;	String
ORDINAL_POSITION	Position of the column in the table	String
IS_NULLABLE	Column can/cannot have null values	Boolean
COLUMN_DEF	Name of the column that stores table	String
REMARKS	Description/Remarks	String

Query Structure

SELECT <CATALOG> AS CATALOG,
  <SCHEMA> AS SCHEMA,
  <TABNAME> AS TABLE,
  <COLNAME> AS COLUMN,
  <TYPENAME> AS TYPE_NAME,
  <TYPENAME> AS DATA_TYPE,
  <COLNUM> AS ORDINAL_POSITION,
  <NULLS> AS IS_NULLABLE,
  <DEFAULT> AS COLUMN_DEF,
  <REMARKS> AS REMARKS
FROM <sys_schema.table>  WHERE <clause>  ORDER BY <clause>;

Example (MySQL)

 SELECT
   null AS `CATALOG`,
   c.table_schema AS `SCHEMA`,
   c.table_name AS `TABLE`,
   c.column_name AS `COLUMN`,
   c.data_type AS 'DATA_TYPE',
   c.column_type AS 'TYPE_NAME',
   c.is_nullable AS 'IS_NULLABLE',
   c.ordinal_position AS 'ORDINAL_POSITION',
   c.column_default AS 'COLUMN_DEF',
   c.column_comment AS REMARKS
FROM information_schema.columns AS c, information_schema.tables AS t
   WHERE
     c.table_schema = t.table_schema AND c.table_name = t.table_name AND  c.table_schema NOT IN ('information_schema', 'performance_schema', 'mysql', 'innodb', 'sys')

PRIMARY KEY

Result Set Column (Mandatory)	Description	Data type
CATALOG	Name of the column that stores the required catalog	String
SCHEMA	Name of the column that stores schema names	String
TABLE	Name of the column that stores table names	String
PK_NAME	Name of the primary key constraint	String
PK_COLUMN	Column name of the primary key field	String
ORDINAL_POSITION	Position of the primary key column in the table	String

Query Structure

SELECT <CATALOG> AS CATALOG,
  <SCHEMA> AS SCHEMA,
  <TABNAME> AS TABLE,
  <PK_COLUMN> AS PK_COLUMN,
  <PK_NAME> AS PK_NAME,
  <KEY_SEQ> AS ORDINAL_POSITION
FROM <sys_schema.table>   WHERE <clause>  ORDER BY <clause>;

Example (MySQL)

SELECT
  '' AS CATALOG,
  k.table_schema AS 'SCHEMA',
  k.table_name AS 'TABLE',
  k.ordinal_position AS 'ORDINAL_POSITION',
  k.constraint_name AS 'PK_NAME',
  k.column_name AS 'PK_COLUMN'
FROM information_schema.table_constraints t
JOIN information_schema.key_column_usage k
  USING(constraint_name,table_schema,table_name)
    WHERE t.constraint_type='PRIMARY KEY'

FOREIGN KEY

Result Set Column (Mandatory)	Description	Data type
CATALOG	Name of the column that stores the required catalog	String
SCHEMA	Name of the column that stores schema names	String
TABLE	Name of the column that stores table names	String
PK_CATALOG	Catalog of the referenced primary key	String
PK_SCHEMA	Schema of the referenced primary key	String
PK_TABLE	Table of the referenced primary key	String
PK_COLUMN	Column of the referenced primary key	String
PK_NAME	Name of the primary key constraint	String
FK_NAME	Name of the foreign key constraint	String
FK_COLUMN	Column name of the foreign key field	String

Query Structure

SELECT <CATALOG> AS CATALOG,
  <SCHEMA> AS SCHEMA,
  <TABNAME> AS TABLE,
  <FK_COLUMN> AS FK_COLUMN,
  <PK_CATALOG> AS PK_CATALOG,
  <PK_SCHEMA> AS PK_SCHEMA,
  <PK_TABNAME> AS PK_TABLE,
  <PK_COLUMN> AS PK_COLUMN,
  <PK_NAME> AS PK_NAME,
  <FK_NAME> AS FK_NAME
FROM <sys_schema.table> WHERE <clause> ORDER BY <clause>;

Example (MySQL)

SELECT
  '' AS CATALOG,
  t.table_schema AS 'SCHEMA',
  k.table_name AS 'TABLE',
  k.ordinal_position AS 'ORDINAL_POSITION',
  k.CONSTRAINT_NAME AS 'FK_NAME',
  k.column_name as 'FK_COLUMN',
  '' AS 'PK_CATALOG',
  k.REFERENCED_TABLE_SCHEMA AS 'PK_SCHEMA',
  k.REFERENCED_TABLE_NAME AS 'PK_TABLE',
  'PRIMARY' AS 'PK_NAME',
  k.REFERENCED_COLUMN_NAME AS 'PK_COLUMN'
FROM information_schema.table_constraints t
JOIN information_schema.key_column_usage k
  USING(constraint_name,table_schema,table_name)
      WHERE t.constraint_type='FOREIGN KEY'

INDEX

Result Set Column (Mandatory)	Description	Data type
CATALOG	Name of the column that stores the required catalog	String
SCHEMA	Name of the column that stores schema names	String
TABLE	Name of the column that stores table names	String
INDEX_NAME	Name of the column that stores index names	String
COLUMN	Name of the column that stores column names	String
TYPE	Index Type	String
ORDINAL_POSITION	Position of the index column in the table	Integer
ASC_OR_DESC	Ascending or Descending order	String
FILTER_CONDITION	Filter condition used on the index	String

Query Structure

SELECT <CATALOG> AS CATALOG,
  <SCHEMA> AS SCHEMA,
  <TABNAME> AS TABLE,
  <COLNAME> AS COLUMN,
  <INDEXNAME> AS INDEX_NAME,
  <INDEXTYPE> AS INDEX_TYPE,
  <KEY_SEQ> AS ORDINAL_POSITION,
  <ASC_OR_DESC> AS ASC_OR_DESC,
  <FILTER> AS FILTER_CONDITION
FROM <sys_schema.table>  WHERE <clause> ORDER BY <clause>;

Example

SELECT
  '' AS `CATALOG`,
  table_schema AS `SCHEMA`,
  table_name AS `TABLE`,
  index_name AS 'INDEX_NAME',
  index_type AS 'TYPE',
  column_name AS 'COLUMN',
  '' AS 'FILTER_CONDITION',
  seq_in_index AS 'ORDINAL_POSITION',
  collation AS 'ASC_OR_DESC'
FROM information_schema.statistics
  WHERE table_schema NOT IN ('information_schema' , 'performance_schema' , 'mysql', 'innodb' , 'sys')

VIEW

Result Set Column (Mandatory)	Description	Data type
CATALOG	Name of the column that stores the required catalog	String
SCHEMA	Name of the column that stores schema names	String
VIEW_NAME	Name of the column that stores view names	String
VIEW_CREATE_STATEMENT	Statement used to create the view	String

Query Structure

SELECT <CATALOG> AS CATALOG,
  <SCHEMA> AS SCHEMA,
  <VIEW_NAME> AS VIEW_NAME,
  <VIEW_SQL> AS VIEW_CREATE_STATEMENT
FROM <sys_schema.table>   WHERE <clause>  ORDER BY <clause>;

Example (MySQL)

SELECT
  null AS `CATALOG`,
  table_schema AS `SCHEMA`,
  table_name AS 'VIEW_NAME',
  view_definition AS 'VIEW_CREATE_STATEMENT'
FROM information_schema.views
  WHERE table_schema NOT IN ('information_schema' , 'performance_schema' , 'mysql', 'innodb' , 'sys')

FUNCTION

Result Set Column (Mandatory)	Description	Data type
CATALOG	Name of the column that stores the required catalog	String
SCHEMA	Name of the column that stores schema names	String
FUNCTION_NAME	Name of the column that stores function names	String
SPECIFIC_NAME	Specific Name	String
REMARKS	Definition of the function	String

Query Structure

SELECT <CATALOG> AS CATALOG,
  <SCHEMA> AS SCHEMA,
  <FUNCTION_NAME> AS FUNCTION_NAME,
  <SPECIFIC_NAME> AS SPECIFIC_NAME,
  <REMARKS> AS REMARKS
FROM <sys_schema.table> WHERE <clause> ORDER BY <clause>;

Example (MySQL)

SELECT
   '' AS `CATALOG`,
   routine_schema AS `SCHEMA`,
   routine_type AS 'FUNCTION_TYPE',
   routine_name AS 'FUNCTION_NAME',
   specific_name AS 'SPECIFIC_NAME',
   routine_definition AS 'REMARKS'
FROM information_schema.routines
    WHERE routine_schema NOT IN ('information_schema' , 'performance_schema' , 'mysql', 'innodb' , 'sys')

PARTITION

Result Set Column (Mandatory)	Description	Data type
CATALOG	Name of the column that stores the required catalog	String
SCHEMA	Name of the column that stores schema names	String
TABLE	Name of the column that stores table names	String
PARTITION_NAME	Name of the Partition	String
PARTITION_ORDINAL_POSITION	Position of the partition column in the table	Integer
PARTITION_DESCRIPTION	Partition constraint texts	String

Query Structure

SELECT <CATALOG> AS CATALOG,
  <SCHEMA> AS SCHEMA,
  <TABNAME> AS TABLE,
  <PART_NAME> AS PARTITION_NAME,
  <PART_POSITION> AS PARTITION_ORDINAL_POSITION,
  <PART_DESCRIPTION> AS PARTITION_DESCRIPTION
FROM <sys_schema.table> WHERE <clause> ORDER BY <clause>;

Example (MySQL)

SELECT
  '' AS `CATALOG`,
  table_schema AS `SCHEMA`,
  table_name AS `TABLE`,
  partition_name AS 'PARTITION_NAME',
  partition_ordinal_position AS 'PARTITION_ORDINAL_POSITION',
  partition_expression AS 'PARTITION_DESCRIPTION'
FROM information_schema.partitions
  WHERE table_schema NOT IN ('information_schema' , 'performance_schema' , 'mysql', 'innodb' , 'sys')

SYNONYMS

Result Set Column (Mandatory)	Description	Data type
CATALOG	Name of the column that stores the required catalog	String
SCHEMA	Name of the column that stores schema names	String
TABLE	Name of the column that stores table names	String
SYNONYM_NAME	Name of the column that stores synonym names	String
BASE_OBJ_CATALOG	Name of the column that stores the required catalog of the synonym’s base object	String
BASE_OBJ_SCHEMA	Name of the column that stores the schema of the base object of the synonym	String
BASE_OBJ_NAME	Name of the column that stores the name of the base object of the synonym	String
BASE_OBJ_LINK	Name of the column that stores the remote link of the base object of the synonym	String

Query Structure

SELECT <CATALOG> AS CATALOG,
  <SYNONYM_NAME> AS SYNONYM_NAME,
  <SYNONYM_OWNER> AS OWNER,
  <BASE_OBJ_OWNER> AS BASE_OBJECT_OWNER,
  <BASE_OBJ_NAME> AS BASE_OBJECT_NAME
FROM <sys_schema.table> WHERE <clause> ORDER BY <clause>;

Example (Oracle)

SELECT '' AS "CATALOG",
  OWNER AS "SCHEMA",
  SYNONYM_NAME AS "SYNONYM_NAME",
  '' AS "BASE_OBJ_CATALOG",
  TABLE_OWNER AS "BASE_OBJ_SCHEMA",
  TABLE_NAME AS "BASE_OBJ_NAME",
  '' AS "BASE_OBJ_LINK"
FROM ALL_SYNONYMS
  WHERE OWNER = 'PUBLIC'

Troubleshooting Query-Based MDE

Error Message	Solution
Mandatory columns missing from result set: <list of columns missing from the query result set>	Add all the mandatory columns as described in the guidelines section. Remember that NULL values may be acceptable but omitting a mandatory column is not.
Unable to show preview. Enter a valid query.	Debug your query in Compose and click Preview again to view the result set.
Query-based extraction is on, but no custom queries saved.	This error occurs when you forget to click the Save Queries button. If you do not save the queries first, the metadata will not be extracted.
Unable to save queries. Enter a valid query to extract.	Ensure that you do not leave the query textbox blank. Enter a valid query.
Enter a valid query. SQL Exception: “<Exception text>”	SQL exceptions may depend on the choice of the JDBC driver you are using. Ensure that you enter an appropriate query.
No result set found for the query entered. Check your filter conditions.	Debug your query in Compose checking the filter conditions and make sure it shows the expected output.