Extraction Queries for SAP HANA

Alation Cloud Service Applies to Alation Cloud Service instances of Alation

Customer Managed Applies to customer-managed instances of Alation

The default extraction queries for the SAP HANA data source are listed below. You can customize them to better suit your extraction needs. Custom queries should be provided on the Metadata Extraction tab of the data source Settings page under Metadata Extraction Queries.

You can customize all or some of the queries.

Schema

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

SELECT
  SCHEMA_OWNER AS USER_NAME,
  SCHEMA_NAME AS SCHEMA
FROM SCHEMAS
WHERE
  SCHEMA_NAME NOT IN ('''')
  AND SCHEMA_NAME NOT LIKE '%XSSQLCC_AUTO_USER%'
  AND SCHEMA_NAME NOT IN  ('''');

Table

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

(SELECT
  SCHEMA,
  TABLE_OWNER,
  Q.TABLE_NAME AS TABLE_NAME,
  Q.TABLE_TYPE AS TABLE_TYPE,
  IFNULL(T.COMMENTS,'') AS REMARKS,
  'TRUE' AS IS_TABLE
FROM
    (SELECT
      RES.SCHEMA_NAME AS SCHEMA,
      SCHEMA_OWNER AS TABLE_OWNER,
      TABLE_NAME,
      TABLE_TYPE,
      'TRUE' AS IS_TABLE
    FROM M_TABLES RES
    JOIN SCHEMAS
      ON SCHEMAS.SCHEMA_NAME = RES.SCHEMA_NAME
    WHERE RES.SCHEMA_NAME NOT IN ('''')
      AND RES.SCHEMA_NAME NOT IN ('''')) Q
JOIN TABLES T
  ON Q.SCHEMA = T.SCHEMA_NAME
    AND Q.TABLE_NAME = T.TABLE_NAME)
ORDER BY
  SCHEMA,
  TABLE_NAME,
  IS_TABLE;

If you want specific table information, such as table description or information related to versions, you can modify the table query to include fields that contain the corresponding information.

For example,

(SELECT
  SCHEMA,
  TABLE_OWNER,
  Q.TABLE_NAME AS TABLE_NAME,
  Q.TABLE_TYPE AS TABLE_TYPE,
  IFNULL(T.COMMENTS,'') AS REMARKS,
  'TRUE' AS IS_TABLE
FROM
    (SELECT
      RES.SCHEMA_NAME AS SCHEMA,
      SCHEMA_OWNER AS TABLE_OWNER,
      TABLE_NAME,
      TABLE_TYPE,
      'TRUE' AS IS_TABLE
    FROM M_TABLES RES
    JOIN SCHEMAS
      ON SCHEMAS.SCHEMA_NAME = RES.SCHEMA_NAME
    WHERE RES.SCHEMA_NAME NOT IN (''''')
      AND RES.SCHEMA_NAME NOT IN (''''')) Q
JOIN TABLES T
  ON Q.SCHEMA = T.SCHEMA_NAME
    AND Q.TABLE_NAME = T.TABLE_NAME
  JOIN SAPHANADB.DD02T D on D.tabname = t.table_name
    AND D.DDLANGUAGE = 'E'
    AND AS4LOCAL = 'A'
    AND AS4VERS = '0000' )
ORDER BY
  SCHEMA,
  TABLE_NAME,
  IS_TABLE;

In this example, SAPHANADB.DD02T is a data dictionary table that contains descriptions of tables. The JOIN statement in this query filters the data based on language and the activation status of tables. Similarly, the AS4VERS field represents the version of a table. When you save a table, it is assigned a version that you can use to track changes made to the table.

View

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

SELECT
  VW.SCHEMA_NAME AS SCHEMA,
  SCHEMA_OWNER AS TABLE_OWNER,
  VIEW_NAME,
  CASE
    WHEN lower(definition) LIKE 'create materialized view%'
      THEN definition
    WHEN lower(definition) LIKE 'create materialized view%'
      THEN definition
    WHEN lower(definition) LIKE 'create view%'
      THEN definition
    WHEN lower(definition) LIKE 'create view%'
      THEN definition
    ELSE 'CREATE VIEW ' || VW.SCHEMA_NAME || '.' || VIEW_NAME || ' AS ' || definition
  END AS VIEW_CREATE_STATEMENT,
  VIEW_TYPE,
  COMMENTS AS REMARKS,
  'FALSE' AS IS_TABLE
FROM VIEWS VW
JOIN SCHEMAS
  ON VW.SCHEMA_NAME = SCHEMAS.SCHEMA_NAME
WHERE VIEW_TYPE <> 'HIERARCHY'
  AND VW.SCHEMA_NAME  NOT IN ('''')
  AND VW.SCHEMA_NAME NOT IN  ('''')
ORDER BY
  VW.SCHEMA_NAME,
  VIEW_NAME;

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 * FROM
  (SELECT
    SCHEMA_NAME AS "SCHEMA",
    TABLE_NAME,
    COMMENTS AS "REMARKS",
    COLUMN_NAME,
    POSITION AS "ORDINAL_POSITION",
    DATA_TYPE_NAME AS "TYPE_NAME",
    DATA_TYPE_NAME AS "DATA_TYPE",
    SCALE AS "NUMERIC_SCALE",
    LENGTH AS "MAX_LENGTH",
    IS_NULLABLE,
    DEFAULT_VALUE AS "COLUMN_DEFAULT"
  FROM
    TABLE_COLUMNS
  WHERE
    SCHEMA_NAME  NOT IN ('''')
    AND SCHEMA_NAME NOT IN ('''')
  UNION ALL
  SELECT
    SCHEMA_NAME AS "SCHEMA",
    VIEW_NAME AS "TABLE_NAME",
    COMMENTS AS "REMARKS",
    COLUMN_NAME,
    POSITION AS "ORDINAL_POSITION",
    DATA_TYPE_NAME AS "TYPE_NAME",
    DATA_TYPE_NAME AS "DATA_TYPE",
    SCALE AS "NUMERIC_SCALE",
    LENGTH AS "MAX_LENGTH",
    IS_NULLABLE,
    DEFAULT_VALUE AS "COLUMN_DEFAULT"
  FROM
    VIEW_COLUMNS
  WHERE
    SCHEMA_NAME NOT IN ('''')
    AND SCHEMA_NAME NOT IN ('''')
  UNION ALL
  SELECT
    S.SCHEMA_NAME AS "SCHEMA",
    S.SYNONYM_NAME AS "TABLE_NAME",
    P.COMMENTS AS "REMARKS",
    P.COLUMN_NAME,
    P.POSITION AS "ORDINAL_POSITION",
    P.DATA_TYPE_NAME AS "TYPE_NAME",
    P.DATA_TYPE_NAME AS "DATA_TYPE",
    P.SCALE AS "NUMERIC_SCALE",
    P.LENGTH AS "MAX_LENGTH",
    P.IS_NULLABLE,
    P.DEFAULT_VALUE AS "COLUMN_DEFAULT"
  FROM
    TABLE_COLUMNS P
    INNER JOIN SYNONYMS S
      ON S.OBJECT_SCHEMA = P.SCHEMA_NAME
        AND S.OBJECT_NAME=P.table_name
  WHERE
    S.OBJECT_SCHEMA NOT IN ('''')
    AND S.OBJECT_SCHEMA  NOT IN ('''')
  UNION ALL
  SELECT
    S.SCHEMA_NAME AS "SCHEMA",
    S.SYNONYM_NAME AS "TABLE_NAME",
    P.COMMENTS AS "REMARKS",
    P.COLUMN_NAME,
    P.POSITION AS "ORDINAL_POSITION",
    P.DATA_TYPE_NAME AS "TYPE_NAME",
    P.DATA_TYPE_NAME AS "DATA_TYPE",
    P.SCALE AS "NUMERIC_SCALE",
    P.LENGTH AS "MAX_LENGTH",
    P.IS_NULLABLE,
    P.DEFAULT_VALUE AS "COLUMN_DEFAULT"
  FROM
    VIEW_COLUMNS P
    INNER JOIN SYNONYMS S
      ON S.OBJECT_SCHEMA = P.SCHEMA_NAME
        AND S.OBJECT_NAME=P.view_name
  WHERE
    S.OBJECT_SCHEMA NOT IN ('''')
    AND S.OBJECT_SCHEMA NOT IN (''''))
ORDER BY
  "SCHEMA",
  TABLE_NAME,
  COLUMN_NAME;

Primary Key

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

SELECT
  INDEX_COLUMNS.SCHEMA_NAME AS "SCHEMA",
  SCHEMAS.SCHEMA_OWNER AS "OWNER",
  TABLE_NAME,
  INDEX_NAME,
  COLUMN_NAME,
  POSITION
FROM
  INDEX_COLUMNS
JOIN SCHEMAS
  ON INDEX_COLUMNS.SCHEMA_NAME = SCHEMAS.SCHEMA_NAME
WHERE
  INDEX_COLUMNS.SCHEMA_NAME NOT IN ('''')
  AND CONSTRAINT = 'PRIMARY KEY'
  AND INDEX_COLUMNS.SCHEMA_NAME NOT IN ('''')
ORDER BY
  INDEX_COLUMNS.SCHEMA_NAME,
  INDEX_COLUMNS.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
  '' AS "PK_CATALOG",
  SCHEMA_NAME AS "PK_SCHEMA",
  TABLE_NAME AS "PK_TABLE",
  COLUMN_NAME AS "PK_COLUMN",
  '' AS "FK_CATALOG",
  REFERENCED_SCHEMA_NAME AS "FK_SCHEMA",
  REFERENCED_TABLE_NAME AS "FK_TABLE",
  REFERENCED_COLUMN_NAME AS "FK_COLUMN"
FROM
  REFERENTIAL_CONSTRAINTS
  WHERE SCHEMA_NAME NOT IN ('''')
    AND SCHEMA_NAME NOT IN ('''')
ORDER BY
  SCHEMA_NAME,
  TABLE_NAME;

Function

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

(SELECT
  TRIM(SCHEMA_NAME) AS "SCHEMA",
  TRIM(FUNCTION_NAME) AS "FUNCTION_NAME",
  '' AS "FUNCTION_DEFINITION",
  '' AS "REMARKS"
FROM FUNCTIONS
WHERE SCHEMA_NAME NOT IN ('''')
  AND SCHEMA_NAME NOT IN (''''))
UNION
  (SELECT
    TRIM(SCHEMA_NAME) AS "SCHEMA",
    TRIM(FUNCTION_NAME) AS "FUNCTION_NAME",
    '' AS "FUNCTION_DEFINITION",
    '' AS "REMARKS"
  FROM AFL_FUNCTIONS
  WHERE SCHEMA_NAME NOT IN ('''')
    AND SCHEMA_NAME NOT IN (''''))
UNION
  (SELECT
    TRIM(SCHEMA_NAME) AS "SCHEMA",
    TRIM(PROCEDURE_NAME) AS "FUNCTION_NAME",
    '' AS "FUNCTION_DEFINITION",
    '' AS "REMARKS"
  FROM PROCEDURES
  WHERE SCHEMA_NAME NOT IN ('''')
    AND SCHEMA_NAME NOT IN (''''))
ORDER BY
  "SCHEMA",
  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
  T1.FUNCTION_NAME,
  T1.PARAMETER_NAME AS "ARG_NAME",
  T1.PARAMETER_TYPE AS "ARG_TYPE",
  T1.DATA_TYPE_NAME AS "TYPE_NAME",
  T2.DEFINITION AS "ARG_DEF",
  T2.SCHEMA_NAME AS "SCHEMA"
FROM
  (SELECT
    TRIM(FUNCTION_NAME) AS FUNCTION_NAME,
    TRIM(DATA_TYPE_NAME) AS DATA_TYPE_NAME,
    IFNULL(TRIM(PARAMETER_NAME),'') AS PARAMETER_NAME,
    IFNULL(TRIM(PARAMETER_TYPE),'') AS PARAMETER_TYPE
  FROM
    FUNCTION_PARAMETERS) T1
    INNER JOIN
      (SELECT
        TRIM(SCHEMA_NAME) AS SCHEMA_NAME,
        TRIM(FUNCTION_NAME) AS FUNCTION_NAME,
        CAST(DEFINITION AS VARCHAR) AS DEFINITION,
        TRIM(FUNCTION_TYPE) AS FUNCTION_TYPE
      FROM FUNCTIONS) T2
        ON T1.FUNCTION_NAME = T2.FUNCTION_NAME
  WHERE
    T2.SCHEMA_NAME  NOT IN ('''')
    AND T2.SCHEMA_NAME NOT IN (''''))
UNION
  (SELECT
    T1.FUNCTION_NAME,
    T1.PARAMETER_NAME,
    T1.PARAMETER_TYPE,
    T1.DATA_TYPE_NAME,
    T2.DEFINITION,
    T2.SCHEMA_NAME
  FROM
    (SELECT
      TRIM(FUNCTION_NAME) AS FUNCTION_NAME,
      TRIM(DATA_TYPE) AS DATA_TYPE_NAME,
      TRIM(PARAMETER_NAME),
      '' AS PARAMETER_TYPE,
      PARAMETER_NAME
      FROM AFL_FUNCTION_PARAMETERS) T1
      INNER JOIN
        (SELECT
          TRIM(SCHEMA_NAME) AS SCHEMA_NAME,
          TRIM(FUNCTION_NAME) AS FUNCTION_NAME,
          NULL AS DEFINITION,
          TRIM(FUNCTION_TYPE) AS FUNCTION_TYPE
        FROM AFL_FUNCTIONS) T2
          ON T1.FUNCTION_NAME = T2.FUNCTION_NAME
      WHERE
        T2.SCHEMA_NAME NOT IN ('''')
        AND T2.SCHEMA_NAME NOT IN (''''))
UNION
  (SELECT
    T1.FUNCTION_NAME,
    T1.PARAMETER_NAME,
    T1.PARAMETER_TYPE,
    T1.DATA_TYPE_NAME,
    T2.DEFINITION,
    T2.SCHEMA_NAME
  FROM
    (SELECT
      TRIM(PROCEDURE_NAME) AS FUNCTION_NAME,
      TRIM(DATA_TYPE_NAME) AS DATA_TYPE_NAME,
      TRIM(PARAMETER_NAME) AS PARAMETER_NAME,
      IFNULL(TRIM(PARAMETER_TYPE),'') AS PARAMETER_TYPE
    FROM
      PROCEDURE_PARAMETERS) T1
      INNER JOIN
        (SELECT
          TRIM(SCHEMA_NAME) AS SCHEMA_NAME,
          TRIM(PROCEDURE_NAME) AS FUNCTION_NAME ,
          CAST(DEFINITION AS VARCHAR) AS DEFINITION,
          TRIM(PROCEDURE_TYPE) AS FUNCTION_TYPE
        FROM PROCEDURES) T2
          ON T1.FUNCTION_NAME = T2.FUNCTION_NAME
    WHERE
      T2.SCHEMA_NAME  NOT IN ('''')
      AND T2.SCHEMA_NAME NOT IN (''''))
ORDER BY
  "SCHEMA",
  FUNCTION_NAME,
  ARG_NAME;