Exemplos de consultas de relatórios para IBM watsonx.data intelligence

Verifique os casos de uso a seguir para relatórios e as consultas SQL de exemplo para criar os relatórios.

Nota:

As consultas são aplicáveis para todos os bancos de dados suportados, a menos que seja especificado de outra forma
Todas as consultas usam globalschema e zoneschema para nomes de esquema.. Você deve substituí-los pelo nome do esquema usado em seu ambiente:
- globalschema é o esquema padrão escolhido na IU.
- zoneschema é o esquema específico para o catálogo, projeto ou categoria.
globalschema e zoneschema podem ser os mesmos se o usuário escolher isso na IU

Consultas de catálogo, projeto e ativos

Número de ativos de catálogo agrupados por tipo

SELECT
  ASSET_TYPE,
  COUNT(*) AS TOTAL
FROM
  zoneschema.CONTAINER_ASSETS
GROUP BY
  ASSET_TYPE

Obter o número de conexões criadas desde o horário específico

SELECT
  CONTAINER_TYPE,
  COUNT(*)
FROM
  zoneschema.CONTAINER_ASSETS
WHERE
  ASSET_TYPE = 'connection'
  AND CREATED_ON >= TO_TIMESTAMP('2022-01-01', 'YYYY-MM-DD')
GROUP BY
  CONTAINER_TYPE

Obter o número de ativos do catálogo que são agrupados por tag e tipo de ativo

SELECT
  TAG_NAME,
  ASSET_TYPE,
  COUNT(*) TOTAL
FROM
  zoneschema.ASSET_TAGS ASSET_TAGS,
  zoneschema.CONTAINER_ASSETS CONTAINER_ASSETS
WHERE
  ASSET_TAGS.ASSET_ID = CONTAINER_ASSETS.ASSET_ID
GROUP BY
  TAG_NAME,
  ASSET_TYPE

Listar os ativos do catálogo que não possuem uma descrição

SELECT
  *
FROM
  zoneschema.CONTAINER_ASSETS
WHERE
  DESCRIPTION IS NULL
  OR DESCRIPTION = ''
  AND CONTAINER_TYPE = 'catalog'

Mostrar a distribuição do termo de negócios em ativos em um catálogo..

SELECT
  GA.NAME,
  AGGR_TABLE.TOTAL
FROM
  zoneschema.GOVERNANCE_ARTIFACTS GA
  LEFT OUTER JOIN (
    SELECT
      ASSOCIATED_ARTIFACT_ID,
      COUNT(*) AS TOTAL
    FROM
      zoneschema.GOVERNANCE_ARTIFACT_CONTAINER_ASSOCIATIONS
    WHERE
      ASSOCIATED_ARTIFACT_TYPE = 'glossary_term'
    GROUP BY
      ASSOCIATED_ARTIFACT_ID
  ) AGGR_TABLE ON GA.ARTIFACT_ID = AGGR_TABLE.ASSOCIATED_ARTIFACT_ID
WHERE
  GA.ARTIFACT_TYPE = 'glossary_term'

Listar os ativos do catálogo sem termos de negócios designados

SELECT
  HEADER,
  COUNT(*) AS TOTAL
FROM
  (
    SELECT
      CASE WHEN GOVERNANCE_ARTIFACT_CONTAINER_ASSOCIATIONS.ASSOCIATED_ARTIFACT_ID IS NULL THEN 'NO TERMS' ELSE 'HAS ATLEAST ONE TERM' END AS HEADER
    FROM
      zoneschema.CONTAINERS CONTAINERS,
      zoneschema.CONTAINER_ASSETS CONTAINER_ASSETS
      LEFT OUTER JOIN zoneschema.GOVERNANCE_ARTIFACT_CONTAINER_ASSOCIATIONS GOVERNANCE_ARTIFACT_CONTAINER_ASSOCIATIONS ON CONTAINER_ASSETS.ASSET_ID = GOVERNANCE_ARTIFACT_CONTAINER_ASSOCIATIONS.ASSET_ID
      AND GOVERNANCE_ARTIFACT_CONTAINER_ASSOCIATIONS.ASSOCIATED_ARTIFACT_TYPE = 'glossary_term'
    WHERE
      CONTAINERS.CONTAINER_ID = CONTAINER_ASSETS.CONTAINER_ID
      AND CONTAINERS.CONTAINER_TYPE = 'catalog'
  ) TEMP_TABLE
GROUP BY
  HEADER

Listar os ativos de catálogo com um usuário específico designado como colaborador

SELECT
  CONTAINER_ASSETS.CONTAINER_ID,
  CONTAINER_ASSETS.ASSET_ID,
  CONTAINER_ASSETS.NAME,
  CONTAINER_ASSETS.ASSET_TYPE
FROM
  zoneschema.ASSET_COLLABORATORS ASSET_COLLABORATORS,
  zoneschema.CONTAINER_ASSETS CONTAINER_ASSETS
WHERE
  USER_ID = 'IBMid-50DW59BCGD'
  AND CONTAINER_ASSETS.CONTAINER_TYPE = 'catalog'
  AND CONTAINER_ASSETS.ASSET_ID = ASSET_COLLABORATORS.ASSET_ID
  AND CONTAINER_ASSETS.CONTAINER_ID = ASSET_COLLABORATORS.CONTAINER_ID

Mostrar a tendência das pontuações de qualidade dos dados de ativos do catálogo

Para Db2:

SELECT
  CA.NAME AS ASSET_NAME,
  TEMP.TECH_START AS UPDATE_TIME,
  TEMP.TECH_START,
  TEMP.ASSET_ID,
  TEMP.TECH_END,
  QUALITY_SCORE
FROM
  zoneschema.CONTAINER_ASSETS AS CA,
  (
    SELECT
      CONTAINER_DATA_ASSETS.ASSET_ID,
      CONTAINER_DATA_ASSETS.QUALITY_SCORE,
      CONTAINER_DATA_ASSETS.TECH_START,
      CONTAINER_DATA_ASSETS.TECH_END
    FROM
      zoneschema.CONTAINER_DATA_ASSETS FOR SYSTEM_TIME
    FROM
      '2021-08-19-00.00.00.000000000000' TO '9999-12-30-00.00.00.000000000000'
    WHERE
      CONTAINER_DATA_ASSETS.ASSET_ID = '85a89a9c-8fd3-4306-8931-cd15c41ae1d4'
      AND QUALITY_SCORE IS NOT NULL
  ) AS TEMP
WHERE
  TEMP.ASSET_ID = CA.ASSET_ID

Para PostgreSQL:

select
  CA.NAME as ASSET_NAME,
  TEMP.TECH_START as UPDATE_TIME,
  TEMP.TECH_START,
  TEMP.ASSET_ID,
  TEMP.TECH_END,
  TEMP.QUALITY_SCORE
from
  zoneschema.CONTAINER_ASSETS CA,
  (
    select
      CONTAINER_DATA_ASSETS.ASSET_ID,
      CONTAINER_DATA_ASSETS.QUALITY_SCORE,
      CONTAINER_DATA_ASSETS.TECH_START,
      CONTAINER_DATA_ASSETS.TECH_END
    from
      zoneschema.CONTAINER_DATA_ASSETS
    where
      CONTAINER_DATA_ASSETS.ASSET_ID = '130ab82a-c3a4-4fbc-880b-51bc49517ebe'
      and CONTAINER_DATA_ASSETS.QUALITY_SCORE is not null
    union
    select
      HIST_CONTAINER_DATA_ASSETS.ASSET_ID,
      HIST_CONTAINER_DATA_ASSETS.QUALITY_SCORE,
      HIST_CONTAINER_DATA_ASSETS.TECH_START,
      HIST_CONTAINER_DATA_ASSETS.TECH_END
    from
      zoneschema.HIST_CONTAINER_DATA_ASSETS
    where
      HIST_CONTAINER_DATA_ASSETS.ASSET_ID = '130ab82a-c3a4-4fbc-880b-51bc49517ebe'
      and HIST_CONTAINER_DATA_ASSETS.QUALITY_SCORE is not null
  ) temp
where
  TEMP.ASSET_ID = CA.ASSET_ID

Consultas de artefatos de controle

Agregar o número de artefatos de controle agrupados por tipo de artefato..

SELECT
  ARTIFACT_TYPE,
  COUNT(*) TOTAL
FROM
  zoneschema.GOVERNANCE_ARTIFACTS
GROUP BY
  ARTIFACT_TYPE

Listar artefatos de controle que não possuem uma descrição

SELECT
  *
FROM
  zoneschema.GOVERNANCE_ARTIFACTS
WHERE
  DESCRIPTION IS NULL

Mostrar a porcentagem de artefatos de controle com organizadores designados

SELECT
  COUNT(*) AS TOTAL_COUNT,
  ARTIFACT_WITH_STEWARD_COUNT
FROM
  zoneschema.GOVERNANCE_ARTIFACTS
  LEFT OUTER JOIN (
    SELECT
      COUNT(*) AS ARTIFACT_WITH_STEWARD_COUNT
    FROM
      zoneschema.GOVERNANCE_ARTIFACTS GOVERNANCE_ARTIFACTS
      LEFT OUTER JOIN zoneschema.GOVERNANCE_ARTIFACT_STEWARDS GOVERNANCE_ARTIFACT_STEWARDS ON GOVERNANCE_ARTIFACTS.ARTIFACT_ID = GOVERNANCE_ARTIFACT_STEWARDS.ARTIFACT_ID
    WHERE
      GOVERNANCE_ARTIFACT_STEWARDS.USER_ID IS NOT NULL
  ) TEMP_TABLE ON 1 = 1
GROUP BY
  ARTIFACT_WITH_STEWARD_COUNT

Listar os termos relacionados de um termo de negócios específico

SELECT
  TEMPTABLE.NAME AS END1_NAME,
  RELATIONSHIP_TYPE,
  SGA.NAME AS END2_NAME
FROM
  (
    SELECT
      GA.NAME,
      BTA.END2_ARTIFACT_ID,
      BTA.RELATIONSHIP_TYPE
    FROM
      zoneschema.BUSINESS_TERM_ASSOCIATIONS BTA
      LEFT OUTER JOIN zoneschema.GOVERNANCE_ARTIFACTS GA ON GA.ARTIFACT_ID = BTA.END1_ARTIFACT_ID
    ORDER BY
      BTA.END1_ARTIFACT_ID
  ) TEMPTABLE
  LEFT OUTER JOIN zoneschema.GOVERNANCE_ARTIFACTS SGA ON TEMPTABLE.END2_ARTIFACT_ID = SGA.ARTIFACT_ID

Mostrar a hierarquia para uma categoria de nível superior

Para Db2:

WITH RPL (
  END1_CATEGORY_ID, END2_CATEGORY_ID,
  DEPTH
) AS (
  SELECT
    ROOT.END1_CATEGORY_ID,
    ROOT.END2_CATEGORY_ID,
    0
  FROM
    zoneschema.CATEGORY_ASSOCIATIONS ROOT
  WHERE
    ROOT.END1_CATEGORY_ID = 'b11e64a2-5466-4c60-bfc5-121c7a80703d'
  UNION ALL
  SELECT
    CHILD.END1_CATEGORY_ID,
    CHILD.END2_CATEGORY_ID,
    PARENT.DEPTH + 1
  FROM
    RPL PARENT,
    zoneschema.CATEGORY_ASSOCIATIONS CHILD
  WHERE
    PARENT.END2_CATEGORY_ID = CHILD.END1_CATEGORY_ID
    AND DEPTH < 1000
)
SELECT
  TEMP.DEPTH,
  TEMP.NAME AS END1_NAME,
  CATEGORIES.NAME AS END2_NAME,
  TEMP.END1_CATEGORY_ID AS END1,
  TEMP.END2_CATEGORY_ID AS END2
FROM
  (
    SELECT
      END1_CATEGORY_ID,
      NAME,
      END2_CATEGORY_ID,
      DEPTH
    FROM
      RPL
      LEFT OUTER JOIN zoneschema.CATEGORIES CATEGORIES ON RPL.END1_CATEGORY_ID = CATEGORIES.CATEGORY_ID
  ) TEMP
  LEFT OUTER JOIN zoneschema.CATEGORIES CATEGORIES ON CATEGORIES.CATEGORY_ID = TEMP.END2_CATEGORY_ID
ORDER BY
  DEPTH

Para PostgreSQL:

WITH RECURSIVE RPL (
  END1_CATEGORY_ID, END2_CATEGORY_ID,
  DEPTH
) AS (
  SELECT
    ROOT.END1_CATEGORY_ID,
    ROOT.END2_CATEGORY_ID,
    0
  FROM
    zoneschema.CATEGORY_ASSOCIATIONS ROOT
  WHERE
    ROOT.END1_CATEGORY_ID = 'b11e64a2-5466-4c60-bfc5-121c7a80703d'
  UNION ALL
  SELECT
    CHILD.END1_CATEGORY_ID,
    CHILD.END2_CATEGORY_ID,
    PARENT.DEPTH + 1
  FROM
    RPL PARENT,
    zoneschema.CATEGORY_ASSOCIATIONS CHILD
  WHERE
    PARENT.END2_CATEGORY_ID = CHILD.END1_CATEGORY_ID
    AND DEPTH < 1000
)
SELECT
  TEMP.DEPTH,
  TEMP.NAME AS END1_NAME,
  CATEGORIES.NAME AS END2_NAME,
  TEMP.END1_CATEGORY_ID AS END1,
  TEMP.END2_CATEGORY_ID AS END2
FROM
  (
    SELECT
      END1_CATEGORY_ID,
      NAME,
      END2_CATEGORY_ID,
      DEPTH
    FROM
      RPL
      LEFT OUTER JOIN zoneschema.CATEGORIES CATEGORIES ON RPL.END1_CATEGORY_ID = CATEGORIES.CATEGORY_ID
  ) TEMP
  LEFT OUTER JOIN zoneschema.CATEGORIES CATEGORIES ON CATEGORIES.CATEGORY_ID = TEMP.END2_CATEGORY_ID
ORDER BY
  DEPTH

Obter o número de políticas que são designadas a uma regra de controle específica

SELECT
  ENFORCEMENT_RULES.NAME,
  COUNT
FROM
  (
    SELECT
      ENFORCEMENT_RULES.RULE_ID AS RULE_ID,
      COUNT(
        ARTIFACT_ENFORCEMENT_RULE_ASSOCIATIONS.ARTIFACT_ID
      ) AS COUNT
    FROM
      globalschema.ENFORCEMENT_RULES ENFORCEMENT_RULES
      LEFT OUTER JOIN zoneschema.ARTIFACT_ENFORCEMENT_RULE_ASSOCIATIONS ARTIFACT_ENFORCEMENT_RULE_ASSOCIATIONS ON ENFORCEMENT_RULES.RULE_ID = ARTIFACT_ENFORCEMENT_RULE_ASSOCIATIONS.RULE_ID
    GROUP BY
      ENFORCEMENT_RULES.RULE_ID
  ) TEMPTABLE
  LEFT OUTER JOIN globalschema.ENFORCEMENT_RULES ENFORCEMENT_RULES ON ENFORCEMENT_RULES.RULE_ID = TEMPTABLE.RULE_ID

Consultas de Metadata import (MDI)

Mostrar o número de ativos descobertos..

SELECT
  MDI.METADATA_IMPORT_NAME MDI_NAME,
  ASSET_DETAIL.NAME DISCOVERED_ASSET
FROM
  zoneschema.METADATA_IMPORTS MDI
  INNER JOIN zoneschema.CONTAINER_DATA_ASSETS MDI_ASSETS ON MDI_ASSETS.METADATA_IMPORT_ID = MDI.METADATA_IMPORT_ID
  INNER JOIN zoneschema.CONTAINER_ASSETS ASSET_DETAIL ON ASSET_DETAIL.ASSET_ID = MDI_ASSETS.ASSET_ID
  AND ASSET_DETAIL.CONTAINER_ID = MDI_ASSETS.CONTAINER_ID

Mostrar o número de ativos que foram alterados desde a última execução (Mostrar informações do usuário relacionadas à criação, atualização, nova execução manual do MDI)

SELECT
    MDI.METADATA_IMPORT_NAME AS MDI_NAME, U2.USER_NAME AS MDI_OWNER,
    MDI_DETAILS.CREATED_ON AS MDI_CREATED_ON,
    U.USER_NAME INVOKED_BY, E.IS_SCHEDULED_RUN, E.START_TIME, E.END_TIME,
    E.COUNT_NEW_ASSETS, E.COUNT_UPDATED_ASSETS, E.COUNT_DELETED_ASSETS
FROM
    METADATA_IMPORTS MDI
    INNER JOIN CONTAINER_ASSETS MDI_DETAILS
       ON MDI_DETAILS.ASSET_ID = MDI.METADATA_IMPORT_ID AND MDI_DETAILS.CONTAINER_ID = MDI.CONTAINER_ID
    LEFT JOIN METADATA_IMPORT_EXECUTIONS E
       ON E.METADATA_IMPORT_ID = MDI.METADATA_IMPORT_ID
    LEFT JOIN USER_PROFILES U ON U.USER_ID = E.INVOKED_BY
    LEFT JOIN USER_PROFILES U2 ON U2.USER_ID = MDI_DETAILS.OWNER
ORDER BY START_TIME DESC

Contar as novas execuções de importação de metadados

SELECT
  MDI.METADATA_IMPORT_NAME AS MDI_NAME,
  COUNT(E.JOB_RUN_ID) AS NUM_JOBS
FROM
  zoneschema.METADATA_IMPORTS MDI
  LEFT OUTER JOIN zoneschema.METADATA_IMPORT_EXECUTIONS E ON E.METADATA_IMPORT_ID = MDI.METADATA_IMPORT_ID
WHERE
  E.START_TIME >= TO_TIMESTAMP('2022-01-01', 'YYYY-MM-DD')
GROUP BY
  MDI.METADATA_IMPORT_NAME

Consultas de enriquecimento de metadados (MDE)

Mostrar qual enriquecimento de metadados um projeto contém

SELECT
  PROJECTS.NAME PROJECT,
  MDE.METADATA_ENRICHMENT_NAME MDE_NAME,
  U.USER_NAME AS MDE_OWNER,
  MDE_DETAILS.CREATED_ON AS MDE_CREATED_ON
FROM
  zoneschema.METADATA_ENRICHMENTS MDE
  INNER JOIN zoneschema.CONTAINER_ASSETS MDE_DETAILS ON MDE_DETAILS.ASSET_ID = MDE.METADATA_ENRICHMENT_ID
  AND MDE_DETAILS.CONTAINER_ID = MDE.CONTAINER_ID
  INNER JOIN zoneschema.CONTAINERS PROJECTS ON PROJECTS.CONTAINER_ID = MDE.CONTAINER_ID
  LEFT JOIN globalschema.USER_PROFILES U ON U.USER_ID = MDE_DETAILS.OWNER

Mostrar a associação entre enriquecimento de metadados e importação de metadados

SELECT
  PROJECTS.NAME PROJECT,
  MDE.METADATA_ENRICHMENT_NAME MDE_NAME,
  MDI.METADATA_IMPORT_NAME AS MDI_NAME
FROM
  zoneschema.METADATA_ENRICHMENTS MDE
  INNER JOIN zoneschema.CONTAINERS PROJECTS ON PROJECTS.CONTAINER_ID = MDE.CONTAINER_ID
  LEFT JOIN zoneschema.METADATA_IMPORTS MDI ON MDI.METADATA_ENRICHMENT_ID = MDE.METADATA_ENRICHMENT_ID
  AND MDI.CONTAINER_ID = MDE.CONTAINER_ID

Detalhe os ativos que foram revisados

SELECT
  PROJECTS.NAME PROJECT,
  MDE.METADATA_ENRICHMENT_NAME MDE_NAME,
  MDE_ASSET_DETAILS.NAME ASSET_NAME,
  MDE_ASSETS.REVIEWED_ON
FROM
  zoneschema.METADATA_ENRICHMENTS MDE
  INNER JOIN zoneschema.CONTAINERS PROJECTS ON PROJECTS.CONTAINER_ID = MDE.CONTAINER_ID
  INNER JOIN zoneschema.CONTAINER_DATA_ASSETS MDE_ASSETS ON MDE_ASSETS.METADATA_ENRICHMENT_ID = MDE.METADATA_ENRICHMENT_ID
  INNER JOIN zoneschema.CONTAINER_ASSETS MDE_ASSET_DETAILS ON MDE_ASSET_DETAILS.ASSET_ID = MDE_ASSETS.ASSET_ID
  AND MDE_ASSET_DETAILS.CONTAINER_ID = MDE_ASSETS.CONTAINER_ID

Detalhar os ativos e colunas revisados

SELECT
  PROJECTS.NAME PROJECT,
  MDE.METADATA_ENRICHMENT_NAME MDE_NAME,
  MDE_ASSET_DETAILS.NAME ASSET_NAME,
  MDE_ASSETS.REVIEWED_ON,
  MDE_ASSET_COLUMNS.NAME COLUMN_NAME,
  MDE_ASSET_COLUMNS.REVIEWED_ON COL_REVIEWED_ON
FROM
  zoneschema.METADATA_ENRICHMENTS MDE
  INNER JOIN zoneschema.CONTAINERS PROJECTS ON PROJECTS.CONTAINER_ID = MDE.CONTAINER_ID
  INNER JOIN zoneschema.CONTAINER_DATA_ASSETS MDE_ASSETS ON MDE_ASSETS.METADATA_ENRICHMENT_ID = MDE.METADATA_ENRICHMENT_ID
  INNER JOIN zoneschema.CONTAINER_ASSETS MDE_ASSET_DETAILS ON MDE_ASSET_DETAILS.ASSET_ID = MDE_ASSETS.ASSET_ID
  AND MDE_ASSET_DETAILS.CONTAINER_ID = MDE_ASSETS.CONTAINER_ID
  INNER JOIN zoneschema.CONTAINER_DATA_ASSET_COLUMNS MDE_ASSET_COLUMNS ON MDE_ASSET_COLUMNS.ASSET_ID = MDE_ASSETS.ASSET_ID
  AND MDE_ASSET_COLUMNS.CONTAINER_ID = MDE_ASSETS.CONTAINER_ID
ORDER BY
  PROJECT,
  MDE_NAME,
  ASSET_NAME

Verifique os termos designados, sugeridos ou rejeitados no nível do ativo juntamente com o autor da mudança e a sincronização

SELECT
  PROJECTS.NAME PROJECT,
  MDE.METADATA_ENRICHMENT_NAME MDE_NAME,
  MDE_ASSET_DETAILS.NAME ASSET_NAME,
  MDE_ASSETS.REVIEWED_ON,
  TERM_DETAILS.NAME TERM_NAME,
  TERMS.ASSIGNMENT_STATE TERM_STATE,
  TERMS.ASSIGNMENT_DATE,
  U.USER_NAME ASSIGNED_BY,
  TERMS.CONFIDENCE
FROM
  zoneschema.METADATA_ENRICHMENTS MDE
  INNER JOIN zoneschema.CONTAINERS PROJECTS ON PROJECTS.CONTAINER_ID = MDE.CONTAINER_ID
  INNER JOIN zoneschema.CONTAINER_DATA_ASSETS MDE_ASSETS ON MDE_ASSETS.METADATA_ENRICHMENT_ID = MDE.METADATA_ENRICHMENT_ID
  INNER JOIN zoneschema.CONTAINER_ASSETS MDE_ASSET_DETAILS ON MDE_ASSET_DETAILS.ASSET_ID = MDE_ASSETS.ASSET_ID
  AND MDE_ASSET_DETAILS.CONTAINER_ID = MDE_ASSETS.CONTAINER_ID
  LEFT OUTER JOIN zoneschema.GOVERNANCE_ARTIFACT_CONTAINER_ASSOCIATIONS TERMS ON TERMS.ASSET_ID = MDE_ASSETS.ASSET_ID
  AND TERMS.CONTAINER_ID = MDE_ASSETS.CONTAINER_ID
  AND UPPER(TERMS.ASSOCIATED_ARTIFACT_TYPE) = 'GLOSSARY_TERM'
  LEFT OUTER JOIN zoneschema.GOVERNANCE_ARTIFACTS TERM_DETAILS ON TERM_DETAILS.ARTIFACT_ID = TERMS.ASSOCIATED_ARTIFACT_ID
  LEFT JOIN globalschema.USER_PROFILES U ON U.USER_ID = TERMS.ASSIGNED_BY
ORDER BY
  PROJECT,
  MDE_NAME,
  ASSET_NAME

Verifique os termos designados, sugeridos ou rejeitados no nível de coluna juntamente com o autor da mudança e a sincronização

SELECT
  PROJECTS.NAME PROJECT,
  MDE.METADATA_ENRICHMENT_NAME MDE_NAME,
  MDE_ASSET_DETAILS.NAME ASSET_NAME,
  MDE_ASSETS.REVIEWED_ON,
  MDE_ASSET_COLUMNS.NAME COLUMN_NAME,
  MDE_ASSET_COLUMNS.REVIEWED_ON COL_REVIEWED_OM,
  COL_TERM_DETAILS.NAME COL_TERM_NAME,
  COL_TERMS.ASSIGNMENT_STATE COL_TERM_STATE,
  COL_TERMS.ASSIGNMENT_DATE COL_TERM_ASSIGNMENT_DATE,
  U.USER_NAME COL_TERM_ASSIGNED_BY,
  COL_TERMS.CONFIDENCE COL_TERM_CONFIDENCE
FROM
  zoneschema.METADATA_ENRICHMENTS MDE
  INNER JOIN zoneschema.CONTAINERS PROJECTS ON PROJECTS.CONTAINER_ID = MDE.CONTAINER_ID
  INNER JOIN zoneschema.CONTAINER_DATA_ASSETS MDE_ASSETS ON MDE_ASSETS.METADATA_ENRICHMENT_ID = MDE.METADATA_ENRICHMENT_ID
  INNER JOIN zoneschema.CONTAINER_ASSETS MDE_ASSET_DETAILS ON MDE_ASSET_DETAILS.ASSET_ID = MDE_ASSETS.ASSET_ID
  AND MDE_ASSET_DETAILS.CONTAINER_ID = MDE_ASSETS.CONTAINER_ID
  INNER JOIN zoneschema.CONTAINER_DATA_ASSET_COLUMNS MDE_ASSET_COLUMNS ON MDE_ASSET_COLUMNS.ASSET_ID = MDE_ASSETS.ASSET_ID
  AND MDE_ASSET_COLUMNS.CONTAINER_ID = MDE_ASSETS.CONTAINER_ID
  LEFT JOIN zoneschema.DATA_ASSET_COLUMN_ARTIFACT_ASSOCIATIONS COL_TERMS ON COL_TERMS.NAME = MDE_ASSET_COLUMNS.NAME
  AND COL_TERMS.ASSET_ID = MDE_ASSET_COLUMNS.ASSET_ID
  AND COL_TERMS.CONTAINER_ID = MDE_ASSET_COLUMNS.CONTAINER_ID
  AND UPPER(
    COL_TERMS.ASSOCIATED_ARTIFACT_TYPE
  ) = 'GLOSSARY_TERM'
  LEFT JOIN zoneschema.GOVERNANCE_ARTIFACTS COL_TERM_DETAILS ON COL_TERM_DETAILS.ARTIFACT_ID = COL_TERMS.ASSOCIATED_ARTIFACT_ID
  LEFT JOIN globalschema.USER_PROFILES U ON U.USER_ID = COL_TERMS.ASSIGNED_BY
ORDER BY
  PROJECT,
  MDE_NAME,
  ASSET_NAME,
  COLUMN_NAME

Verifique as classes de dados designadas no nível da coluna junto com o autor da mudança e a sincronização

SELECT
  PROJECTS.NAME PROJECT,
  MDE.METADATA_ENRICHMENT_NAME MDE_NAME,
  MDE_ASSET_DETAILS.NAME ASSET_NAME,
  MDE_ASSETS.REVIEWED_ON,
  MDE_ASSET_COLUMNS.NAME COLUMN_NAME,
  MDE_ASSET_COLUMNS.REVIEWED_ON COL_REVIEWED_OM,
  COL_DC_DETAILS.NAME COL_DC_NAME,
  COL_DC.ASSIGNMENT_STATE COL_DC_STATE,
  COL_DC.ASSIGNMENT_DATE COL_DC_ASSIGNMENT_DATE,
  U.USER_NAME COL_DC_ASSIGNED_BY,
  COL_DC.CONFIDENCE COL_DC_CONFIDENCE
FROM
  zoneschema.METADATA_ENRICHMENTS MDE
  INNER JOIN zoneschema.CONTAINERS PROJECTS ON PROJECTS.CONTAINER_ID = MDE.CONTAINER_ID
  INNER JOIN zoneschema.CONTAINER_DATA_ASSETS MDE_ASSETS ON MDE_ASSETS.METADATA_ENRICHMENT_ID = MDE.METADATA_ENRICHMENT_ID
  INNER JOIN zoneschema.CONTAINER_ASSETS MDE_ASSET_DETAILS ON MDE_ASSET_DETAILS.ASSET_ID = MDE_ASSETS.ASSET_ID
  AND MDE_ASSET_DETAILS.CONTAINER_ID = MDE_ASSETS.CONTAINER_ID
  INNER JOIN zoneschema.CONTAINER_DATA_ASSET_COLUMNS MDE_ASSET_COLUMNS ON MDE_ASSET_COLUMNS.ASSET_ID = MDE_ASSETS.ASSET_ID
  AND MDE_ASSET_COLUMNS.CONTAINER_ID = MDE_ASSETS.CONTAINER_ID
  LEFT JOIN zoneschema.DATA_ASSET_COLUMN_ARTIFACT_ASSOCIATIONS COL_DC ON COL_DC.NAME = MDE_ASSET_COLUMNS.NAME
  AND COL_DC.ASSET_ID = MDE_ASSET_COLUMNS.ASSET_ID
  AND COL_DC.CONTAINER_ID = MDE_ASSET_COLUMNS.CONTAINER_ID
  AND UPPER(
    COL_DC.ASSOCIATED_ARTIFACT_TYPE
  ) = 'DATA_CLASS'
  LEFT JOIN zoneschema.GOVERNANCE_ARTIFACTS COL_DC_DETAILS ON COL_DC_DETAILS.ARTIFACT_ID = COL_DC.ASSOCIATED_ARTIFACT_ID
  LEFT JOIN globalschema.USER_PROFILES U ON U.USER_ID = COL_DC.ASSIGNED_BY
ORDER BY
  PROJECT,
  MDE_NAME,
  ASSET_NAME,
  COLUMN_NAME

Lista de ativos publicados do projeto para os catálogos relevantes

SELECT
  PROJECTS.NAME PROJECT,
  MDE.METADATA_ENRICHMENT_NAME MDE_NAME,
  MDE_ASSET_DETAILS.NAME ASSET_NAME,
  MDE_ASSET_DETAILS.ASSET_TYPE,
  CATALOG.NAME PUBLISHED_TO_CATALOG
FROM
  zoneschema.METADATA_ENRICHMENTS MDE
  INNER JOIN zoneschema.CONTAINERS PROJECTS ON PROJECTS.CONTAINER_ID = MDE.CONTAINER_ID
  INNER JOIN zoneschema.CONTAINER_DATA_ASSETS MDE_ASSETS ON MDE_ASSETS.METADATA_ENRICHMENT_ID = MDE.METADATA_ENRICHMENT_ID
  INNER JOIN zoneschema.CONTAINER_ASSETS MDE_ASSET_DETAILS ON MDE_ASSET_DETAILS.ASSET_ID = MDE_ASSETS.ASSET_ID
  AND MDE_ASSET_DETAILS.CONTAINER_ID = MDE_ASSETS.CONTAINER_ID
  LEFT JOIN zoneschema.CONTAINERS CATALOG ON CATALOG.CONTAINER_ID = MDE_ASSETS.PUBLISHED_TO_CONTAINER_ID

Recuperar a métrica de perfil de um ativo

SELECT 
  c.name AS container_name, 
  ca.name AS asset_name, 
  cda.quality_score AS table_quality_score, 
  cdac.name AS column_name, 
  cdac.quality_score AS column_quality_score, 
  cdac.native_data_type, 
  cdac.inferred_data_type, 
  cda.number_of_records AS total_records, 
  cda.num_rows_analysed AS analysed_records, 
  cdac.unique_count, 
  cdac.null_count, 
  cdac.empty_count, 
  cdac.distinct_count, 
  cdac.std_deviation, 
  cdac.mean, 
  cdac.is_nullable, 
  cdac.tech_start AS dt 
FROM 
  zoneschema.container_assets ca 
  JOIN zoneschema.containers c ON ca.container_id = c.container_id 
  JOIN zoneschema.container_data_assets cda ON ca.asset_id = cda.asset_id 
  AND ca.container_id = cda.container_id 
  JOIN zoneschema.container_data_asset_columns cdac ON cdac.asset_id = cda.asset_id 
  AND cdac.container_id = cda.container_id 
WHERE 
  cda.asset_id = '41fe47a8-faf4-4eab-a7a7-05567e2c1557'

Recuperar a métrica de perfil histórico de um ativo

SELECT 
  c.name AS container_name, 
  ca.name AS asset_name, 
  cda.quality_score AS table_quality_score, 
  cdac.name AS column_name, 
  cdac.quality_score AS column_quality_score, 
  cdac.native_data_type, 
  cdac.inferred_data_type, 
  cda.number_of_records AS total_records, 
  cda.num_rows_analysed AS analysed_records, 
  cdac.unique_count, 
  cdac.null_count, 
  cdac.empty_count, 
  cdac.distinct_count, 
  cdac.std_deviation, 
  cdac.mean, 
  cdac.is_nullable, 
  cdac.tech_start AS dt 
FROM 
  zoneschema.hist_container_assets ca 
  JOIN zoneschema.containers c ON ca.container_id = c.container_id 
  JOIN zoneschema.hist_container_data_assets cda ON ca.asset_id = cda.asset_id 
  AND ca.container_id = cda.container_id 
  JOIN zoneschema.hist_container_data_asset_columns cdac ON cdac.asset_id = cda.asset_id 
  AND cdac.container_id = cda.container_id 
WHERE 
  cda.asset_id = '41fe47a8-faf4-4eab-a7a7-05567e2c1557'

Obter tabela recém-adicionada ou atualizada com o nome da tabela e a contagem de colunas adicionadas e atualizadas por uma execução de trabalho MDI

select 
  container_name, 
  asset_name, 
  mdi_job_run_id, 
  mdi_job_action, 
  count(column_name) as columns 
FROM 
  (
    SELECT 
      c.name as container_name, 
      b.name as asset_name, 
      a.mdi_job_run_id, 
      a.mdi_job_action, 
      d.name as column_name 
    FROM 
      zoneschema.container_data_assets a 
      JOIN zoneschema.container_assets b ON b.asset_id = a.asset_id 
      AND b.container_id = a.container_id 
      JOIN zoneschema.containers c ON c.container_id = b.container_id 
      JOIN zoneschema.container_data_asset_columns d ON d.container_id = a.container_id 
      AND d.asset_id = a.asset_id 
      and d.mdi_job_run_id = a.mdi_job_run_id 
    UNION 
    SELECT 
      c.name as container_name, 
      b.name as asset_name, 
      a.mdi_job_run_id, 
      a.mdi_job_action, 
      hd.name as column_name 
    FROM 
      zoneschema.container_data_assets a 
      JOIN zoneschema.container_assets b ON b.asset_id = a.asset_id 
      AND b.container_id = a.container_id 
      JOIN zoneschema.containers c ON c.container_id = b.container_id 
      JOIN zoneschema.hist_container_data_asset_columns hd ON hd.container_id = a.container_id 
      AND hd.asset_id = a.asset_id 
      and hd.mdi_job_run_id = a.mdi_job_run_id 
    UNION 
    SELECT 
      c.name as container_name, 
      b.name as asset_name, 
      a.mdi_job_run_id, 
      a.mdi_job_action, 
      d.name as column_name 
    FROM 
      zoneschema.hist_container_data_assets a 
      JOIN zoneschema.container_assets b ON b.asset_id = a.asset_id 
      AND b.container_id = a.container_id 
      JOIN zoneschema.containers c ON c.container_id = b.container_id 
      JOIN zoneschema.container_data_asset_columns d ON d.container_id = a.container_id 
      AND d.asset_id = a.asset_id 
      and d.mdi_job_run_id = a.mdi_job_run_id 
    UNION 
    SELECT 
      c.name as container_name, 
      b.name as asset_name, 
      a.mdi_job_run_id, 
      a.mdi_job_action, 
      hd.name as column_name 
    FROM 
      zoneschema.hist_container_data_assets a 
      JOIN zoneschema.container_assets b ON b.asset_id = a.asset_id 
      AND b.container_id = a.container_id 
      JOIN zoneschema.containers c ON c.container_id = b.container_id 
      JOIN zoneschema.hist_container_data_asset_columns hd ON hd.container_id = a.container_id 
      AND hd.asset_id = a.asset_id 
      and hd.mdi_job_run_id = a.mdi_job_run_id
  ) mdi_assset 
where 
  mdi_job_run_id = 'df48943f-61fe-409a-b78b-72cd1b7875d2' 
group by 
  container_name, 
  asset_name, 
  mdi_job_run_id, 
  mdi_job_action

Consultas sobre a qualidade dos dados

Recuperar regras de qualidade de dados

As regras de qualidade de dados (dq_rule) são ativos, e todos os metadados comuns de todos os ativos estão disponíveis na tabela container_assets . Para obter metadados comuns sobre o ativo dq_rule , vincule a tabela dq_rules à tabela container_assets usando rule_id como asset_id:

SELECT
    dr.rule_id,
    dr.name AS rule_name,
    ca.owner,
    ca.created_on,
    ca.modified_by,
    ca.modified_on,
    dr.output_asset_id,
    dr.output_asset_container_id,
    caout.name AS output_asset_name,
    cdacout.name AS column_name
FROM dq_rules dr
INNER JOIN container_assets ca 
    ON ca.container_id = dr.container_id 
    AND ca.asset_id = dr.rule_id
INNER JOIN container_assets caout 
    ON caout.container_id = dr.output_asset_container_id 
    AND caout.asset_id = dr.output_asset_id
INNER JOIN container_data_assets cdaout
    ON cdaout.container_id = caout.container_id 
    AND cdaout.asset_id = caout.asset_id
INNER JOIN container_data_asset_columns cdacout
    ON cdacout.container_id = cdaout.container_id 
    AND cdacout.asset_id = cdaout.asset_id;

Recuperar detalhes de saída das regras de qualidade de dados

A seguinte opção precisa ser ativada ao criar a regra de qualidade de dados: Importar tabela de saída gerada como ativo de projeto.

Com essa opção ativada:

A tabela de saída é importada como um ativo para o projeto.
A saída correspondente asset_id é sincronizada com a tabela dq_rules .
Os detalhes do ativo de saída correspondente são sincronizados com as tabelas container_assets, container_data_assets e container_data_asset_columns .

Execute a seguinte consulta para recuperar os detalhes de saída da regra:

SELECT
    dr.rule_id,
    dr.name AS rule_name,
    ca.owner,
    ca.created_on,
    ca.modified_by,
    ca.modified_on,
    dr.output_asset_id,
    dr.output_asset_container_id,
    caout.name AS output_asset_name,
    cdacout.name AS column_name
FROM dq_rules dr
INNER JOIN container_assets ca 
    ON ca.container_id = dr.container_id 
    AND ca.asset_id = dr.rule_id
INNER JOIN container_assets caout 
    ON caout.container_id = dr.output_asset_container_id 
    AND caout.asset_id = dr.output_asset_id
INNER JOIN container_data_assets cdaout
    ON cdaout.container_id = caout.container_id 
    AND cdaout.asset_id = caout.asset_id
INNER JOIN container_data

Recuperar a ID do executor da regra

Os detalhes da execução da regra de qualidade de dados são sincronizados com a tabela dq_rule_execution . Ele inclui flow_job_run_id, que pode ser vinculado a container_assets.asset_id para obter o proprietário da execução do trabalho. Um novo job run é criado para cada execução, e o proprietário/criador desse job run é o executor.

Execute a seguinte consulta para recuperar as informações sobre o executor de regras:


SELECT 
    dr.rule_id,
    dr.name AS rule_name,
    dre.start_time,
    dre.execution_id,
    ca.owner,
    up.user_name AS executed_by
FROM dq_rules dr
INNER JOIN dq_rule_execution dre 
    ON dre.dq_rule_id = dr.rule_id
INNER JOIN container_assets ca 
    ON ca.asset_id = dre.flow_job_run_id
INNER JOIN user_profiles up
    ON up.user_id = ca.owner;

Consultas do painel Data Privacy

Recuperar ativos associados a artefatos de controle que são classificados como Dados Pessoais, juntamente com Regras de Proteção de Dados

-- Join from data assets in a container to associated governance artifacts (glossary terms and data classes),
-- then from governance artifacts to classifications to determine items that are classified as Personal Data.
-- This query also retrieves data protection rules that are associated with the governance artifacts.
SELECT
  c.NAME container_name,
  c.CONTAINER_TYPE container_type,
  tab.name table_name,
  col.name col_name,
  gov.name gov_artifact_name,
  gov.artifact_type,
  col_artifact_xref.assignment_state,
  classif.name classification,
  er.NAME rule_name,
  er.ACTION_NAME rule_action
FROM
  zoneschema.CONTAINERS c
  INNER JOIN zoneschema.container_assets tab ON c.CONTAINER_ID = tab.CONTAINER_ID
  INNER JOIN zoneschema.container_data_asset_columns col ON col.asset_id = tab.asset_id
  AND col.container_id = tab.container_id
  INNER JOIN zoneschema.data_asset_column_artifact_associations col_artifact_xref ON col_artifact_xref.asset_id = col.asset_id
  AND col_artifact_xref.container_id = col.container_id
  AND col_artifact_xref.name = col.name
  INNER JOIN zoneschema.governance_artifacts gov ON col_artifact_xref.associated_artifact_id = gov.artifact_id
  LEFT OUTER JOIN zoneschema.governance_artifact_associations gov_gov_xref ON (
    gov.artifact_id = gov_gov_xref.end2_artifact_id
    AND GOV_GOV_XREF.END2_ARTIFACT_TYPE = 'glossary_term'
    AND GOV_GOV_XREF.END1_ARTIFACT_TYPE = 'classification'
  )
  OR (
    gov.artifact_id = gov_gov_xref.end1_artifact_id
    AND GOV_GOV_XREF.END1_ARTIFACT_TYPE = 'data_class'
    AND GOV_GOV_XREF.END2_ARTIFACT_TYPE = 'classification'
  )
  LEFT OUTER JOIN zoneschema.governance_artifacts classif ON (
    classif.artifact_id = gov_gov_xref.end1_artifact_id
    AND classif.artifact_type = 'classification'
    AND classif.name LIKE '%Personal%'
  )
  OR (
    classif.artifact_id = gov_gov_xref.end2_artifact_id
    AND classif.artifact_type = 'classification'
    AND classif.name LIKE '%Personal%'
  )
  LEFT OUTER JOIN zoneschema.ARTIFACT_ENFORCEMENT_RULE_ASSOCIATIONS aera ON aera.ARTIFACT_ID = gov.ARTIFACT_ID
  LEFT OUTER JOIN globalschema.ENFORCEMENT_RULES er ON ER.RULE_ID = aera.RULE_ID