Extraction Queries for Vertica

Alation Cloud Service Applies to Alation Cloud Service instances of Alation

Customer Managed Applies to customer-managed instances of Alation

Core Connector Core connectors are included with all Alation platform tiers (subject to each tier’s connector limits) and are fully supported by Alation.

Schema

Ensure your query has a column labeled as USERNAME in the SELECT list.

SELECT
    current_database() AS CATALOG,
    schema_name AS SCHEMA
FROM
    v_catalog.schemata
WHERE schema_name NOT IN ('''')
    AND schema_name NOT IN ('public', 'v_idol', 'pg_catalog', 'v_internal',
                            'v_catalog', 'v_monitor', 'pg_internal',
                            'v_txtindex')
ORDER BY
  CATALOG,
  schema_name;

Table

Ensure your query has columns labeled as SCHEMA, TABLE_NAME, TABLE_TYPE, REMARKS in the SELECT list.

SELECT
    current_database AS CATALOG,
    a.schema_name AS SCHEMA,
    a.table_name AS TABLE_NAME,
    a.table_type AS TABLE_TYPE,
    a.remarks AS REMARKS,
    b.owner_name AS OWNER,
    b.create_time AS CREATE_TIME
FROM
    v_catalog.all_tables a
    LEFT JOIN v_catalog.tables b
      ON a.table_id = b.table_id
WHERE schema_name NOT IN ('''')
    AND schema_name NOT IN ('public', 'v_idol', 'pg_catalog',
                            'v_internal', 'v_catalog', 'v_monitor',
                            'pg_internal', 'v_txtindex')
    AND table_type ILIKE 'TABLE'
ORDER BY
  schema_name,
  table_name;

View

Ensure your query has columns labeled as SCHEMA, VIEW_NAME, VIEW_CREATE_STATEMENT, 'VIEW' AS VIEW_TYPE, REMARKS in the SELECT list.

SELECT
    current_database AS CATALOG,
    a.schema_name AS SCHEMA,
    a.table_name AS TABLE_NAME,
    a.table_type AS VIEW_TYPE,
    a.remarks AS REMARKS,
    c.owner_name AS OWNER,
    c.create_time AS CREATE_TIME,
    c.view_definition as VIEW_CREATE_STATEMENT
FROM
    v_catalog.all_tables a
    LEFT JOIN v_catalog.views c
        ON a.table_id = c.table_id
WHERE a.schema_name NOT IN ('''')
    AND a.schema_name NOT IN ('public', 'v_idol', 'pg_catalog',
                              'v_internal', 'v_catalog', 'v_monitor',
                              'pg_internal', 'v_txtindex')
    AND table_type ILIKE 'VIEW';

Column

Ensure your query has columns labeled as SCHEMA, TABLE_NAME, TYPE_NAME, DATA_TYPE, COLUMN_NAME, ORDINAL_POSITION, IS_NULLABLE, REMARKS, COLUMN_DEFAULT in the SELECT list.

SELECT
    current_database AS CATALOG,
    schema_name as SCHEMA,
    TABLE_NAME,
    COLUMN_NAME,
    CASE WHEN data_type_name = 'Char'
          THEN 'Char(' || column_size || ')'
        WHEN data_type_name = 'Varchar'
          THEN 'Varchar(' || column_size || ')'
        WHEN data_type_name = 'Numeric'
          THEN 'Numeric(' || column_size || ',' || decimal_digits || ')'
        WHEN data_type_name = 'Date'
          THEN 'Date(' || column_size || ')'
        WHEN data_type_name = 'Integer'
          THEN 'Integer(' || column_size || ')'
        WHEN data_type_name = 'Float'
          THEN 'Float(' || column_size || ')'
        WHEN data_type_name = 'Time'
          THEN 'Time(' || column_size || ')'
        WHEN data_type_name = 'Timestamp'
          THEN 'Timestamp(' || column_size || ')'
        WHEN data_type_name = 'Interval Second'
          THEN 'Interval Second(' || column_size || ')'
        WHEN data_type_name = 'Interval Month'
          THEN 'Interval Month(' || column_size || ')'
        WHEN data_type_name = 'Interval Day'
          THEN 'Interval Day(' || column_size || ')'
        WHEN data_type_name = 'Interval Minute to Second'
          THEN 'Interval Minute to Second(' || column_size || ')'
        WHEN data_type_name = 'TimestampTz'
          THEN 'TimestampTz(' || column_size || ')'
        WHEN data_type_name = 'Interval Hour'
          THEN 'Interval Hour(' || column_size || ')'
        WHEN data_type_name = 'Interval Day to Hour'
          THEN 'Interval Day to Hour(' || column_size || ')'
        WHEN data_type_name = 'Binary'
          THEN 'Binary(' || column_size || ')'
        WHEN data_type_name = 'Varbinary'
          THEN 'Varbinary(' || column_size || ')'
        WHEN data_type_name = 'TimeTz'
          THEN 'TimeTz(' || column_size || ')'
        WHEN data_type_name = 'Interval Minute'
          THEN 'Interval Minute(' || column_size || ')'
        WHEN data_type_name = 'Interval Day to Minute'
          THEN 'Interval Day to Minute(' || column_size || ')'
        WHEN data_type_name = 'Interval Day to Second'
          THEN 'Interval Day to Second(' || column_size || ')'
        WHEN data_type_name = 'Interval Hour to Second'
          THEN 'Interval Hour to Second(' || column_size || ')'
        WHEN data_type_name = 'Interval Year'
          THEN 'Interval Year(' || column_size || ')'
        WHEN data_type_name = 'Interval Year to Month'
          THEN 'Interval Year to Month(' || column_size || ')'
        ELSE data_type_name
    END AS TYPE_NAME,
    column_default AS COLUMN_DEF,
    remarks,
    column_size AS MAX_LENGTH,
    decimal_digits AS NUMERIC_SCALE,
    ordinal_position,
    COLUMN_DEFAULT,
    data_type_name AS DATA_TYPE,
    is_nullable
FROM
  v_catalog.odbc_columns
WHERE schema_name NOT IN ('''')
  AND schema_name NOT IN ('public', 'v_idol', 'pg_catalog',
                          'v_internal', 'v_catalog', 'v_monitor',
                          'pg_internal' ,'v_txtindex')
ORDER BY
  CATALOG,
  schema_name,
  table_name;

Primary Key

Ensure your query has columns labeled as TABLE_NAME, COLUMN_NAME, OWNER in the SELECT list.

SELECT
    current_database AS CATALOG,
    table_schema AS SCHEMA,
    table_name,
    column_name,
    ordinal_position AS KEY_SEQ,
    constraint_name AS PK_NAME
FROM
    v_catalog.primary_keys
WHERE
    table_schema NOT IN ('''')
      AND table_schema NOT IN ('public', 'v_idol', 'pg_catalog',
                              'v_internal', 'v_catalog', 'v_monitor',
                              'pg_internal', 'v_txtindex')
ORDER BY
    CATALOG,
    table_schema,
    table_name;

Foreign Key

Ensure your query has columns labeled as PK_CATALOG, PK_SCHEMA, PK_TABLE, PK_COLUMN, FK_CATALOG, FK_SCHEMA, FK_TABLE, FK_COLUMN in the SELECT list.

SELECT
    current_database AS CATALOG,
    null AS PK_CATALOG,
    primary_keys.table_schema AS PK_SCHEMA,
    primary_keys.table_name AS PK_TABLE,
    primary_keys.column_name AS PK_COLUMN,
    null AS FK_CATALOG,
    foreign_keys.table_schema AS FK_SCHEMA,
    foreign_keys.table_name AS FK_TABLE,
    foreign_keys.column_name AS FK_COLUMN,
    primary_keys.ordinal_position AS KEY_SEQ,
    3 AS FOREIGN_KEY_UPDATE_RULE,
    3 AS FOREIGN_KEY_DELETE_RULE,
    foreign_keys.constraint_name AS FK_NAME,
    primary_keys.constraint_name AS PK_NAME,
    1 AS DEFERRABILITY
FROM
    v_catalog.primary_keys
    JOIN v_catalog.foreign_keys
        ON primary_keys.table_name = foreign_keys.reference_table_name
        AND primary_keys.table_schema = foreign_keys.reference_table_schema
        AND primary_keys.column_name = foreign_keys.reference_column_name
WHERE primary_keys.table_schema NOT IN ('''')
    AND primary_keys.table_schema NOT IN ('public', 'v_idol', 'pg_catalog',
                                          'v_internal', 'v_catalog',
                                          'v_monitor', 'pg_internal',
                                          'v_txtindex')
ORDER BY
  pk_catalog,
  primary_keys.table_schema,
  pk_table;

Function

Ensure your query has columns labeled as SCHEMA, FUNCTION_NAME, REMARKS in the SELECT list.

SELECT
    catalog,
    schema_name as SCHEMA,
    function_name,
    remarks
FROM
    (
      SELECT
        current_database AS CATALOG,
        schema_name as SCHEMA_NAME,
        function_name,
        function_definition AS REMARKS
      FROM
        v_catalog.user_functions
      UNION ALL
      SELECT
        current_database AS CATALOG,
        schema_name as SCHEMA_NAME,
        function_name,
        function_definition AS REMARKS
      FROM
        v_catalog.user_transforms)
      AS vmd
WHERE schema_name NOT IN ('''')
    AND schema_name NOT IN ('public', 'v_idol', 'pg_catalog',
                            'v_internal', 'v_catalog', 'v_monitor',
                            'pg_internal', 'v_txtindex')
ORDER BY
  catalog,
  schema_name,
  function_name;

Function Definition

Ensure your query has columns labeled as SCHEMA, FUNCTION_NAME, ARG_NAME, TYPE_NAME, ARG_TYPE, ARG_DEF in the SELECT list.

SELECT
    current_database AS FUNCTION_CAT,
    proc_schema AS SCHEMA,
    proc_name AS FUNCTION_NAME,
    specific_proc_name AS SPECIFIC_NAME,
    column_name,
    type_name,
    remarks AS DEFINITION,
    '' AS FUNCTION_TYPE,
    column_name AS ARG_NAME,
    type_name AS ARG_TYPE,
    column_def AS COLUMN_DEFAULT
FROM
    v_internal.odbc_procedure_columns
WHERE proc_schema NOT IN ('''')
    AND proc_schema NOT IN ('public', 'v_idol', 'pg_catalog',
                            'v_internal', 'v_catalog', 'v_monitor',
                            'pg_internal', 'v_txtindex')
ORDER BY
  proc_schema,
  function_name;