IBM 에 대한 샘플 보고 쿼리 watsonx.data intelligence

보고에 대한 다음 사용 사례와 보고서를 작성하기 위한 예제 SQL 조회를 확인하십시오.

참고:

조회는 별도로 지정하지 않는 한 지원되는 모든 데이터베이스에 적용 가능합니다.
모든 조회는 스키마 이름으로 globalschema 및 zoneschema 를 사용합니다. 사용자 환경에서 사용되는 스키마 이름으로 대체해야 합니다.
- globalschema 는 UI에서 선택된 기본 스키마입니다.
- zoneschema 는 카탈로그, 프로젝트 또는 카테고리에 특정한 스키마입니다.
사용자가 UI에서 선택하는 경우 globalschema 및 zoneschema 는 동일할 수 있습니다.

카탈로그, 프로젝트 및 자산 조회

유형별로 그룹화된 카탈로그 자산 수

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

특정 시간 이후 작성된 연결 수 가져오기

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

자산의 태그 및 유형별로 그룹화된 카탈로그 자산 수 가져오기

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

설명이 없는 카탈로그 자원 나열

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

카탈로그의 자산 간 비즈니스 용어 분배 표시

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'

비즈니스 용어가 지정되지 않은 카탈로그 자원 나열

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

협업자로 지정된 특정 사용자가 있는 카탈로그 자산 나열

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

카탈로그 자산 데이터 품질 점수의 추세 표시

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

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

통제 아티팩트 조회

아티팩트 유형별로 그룹화된 통제 아티팩트 수 집계

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

설명이 없는 통제 아티팩트 나열

SELECT
  *
FROM
  zoneschema.GOVERNANCE_ARTIFACTS
WHERE
  DESCRIPTION IS NULL

지정된 스튜워드가 있는 통제 아티팩트의 백분율 표시

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

특정 비즈니스 용어의 관련 용어 나열

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

최상위 레벨 카테고리의 계층 구조 표시

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

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

특정 통제 규칙에 지정된 정책 수 가져오기

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

Metadata import (MDI) 조회

감지된 자산 수 표시

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

마지막 실행 이후 변경된 자산 수 표시 (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

새 메타데이터 가져오기 실행 계수

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

메타데이터 강화 (MDE) 조회

프로젝트에 포함된 메타데이터 강화 표시

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

메타데이터 강화와 메타데이터 가져오기 간의 연관 표시

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

검토된 자산 세부사항하 데이터

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

검토된 자산 및 열을 자세히 설명합니다.

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

변경 작성자 및 타이밍과 함께 자산 레벨에서 지정, 제안 또는 거부된 용어를 확인하십시오.

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

변경 작성자 및 시간과 함께 열 레벨에서 지정, 제안 또는 거부된 용어를 확인하십시오.

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

변경 작성자 및 타이밍과 함께 열 레벨에서 지정된 데이터 클래스를 확인하십시오.

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

프로젝트에서 관련 카탈로그에 공개된 자산 목록

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

자산에 대한 프로필 메트릭 검색

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'

자산의 과거 프로필 메트릭 검색

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'

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

데이터 품질 쿼리

데이터 품질 규칙 검색

데이터 품질 규칙(dq_rule)은 자산이며, 모든 자산에 대한 모든 공통 메타데이터는 container_assets 표에서 확인할 수 있습니다. dq_rule 에셋에 대한 공통 메타데이터를 가져오려면 dq_rules 테이블을 container_assets 테이블과 rule_id 을 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;

데이터 품질 규칙 출력 세부 정보 검색

데이터 품질 규칙을 만들 때 다음 옵션을 활성화해야 합니다: 생성된 출력 테이블을 프로젝트 자산으로 가져오기.

이 옵션을 활성화한 상태입니다:

출력 테이블은 프로젝트에 에셋으로 가져옵니다.
해당 출력 asset_id 이 dq_rules 테이블에 동기화됩니다.
해당 출력 자산 세부 정보는 container_assets, container_data_assets, container_data_asset_columns 테이블에 동기화됩니다.

다음 쿼리를 실행하여 규칙의 출력 세부 정보를 검색합니다:

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

규칙 실행자 ID 검색

데이터 품질 규칙 실행 세부 정보는 dq_rule_execution 테이블에 동기화됩니다. 여기에는 container_assets.asset_id 에 연결하여 작업 실행의 소유자를 가져올 수 있는 flow_job_run_id 이 포함됩니다. 실행할 때마다 새 작업 실행이 생성되며, 이 작업 실행의 소유자/작성자가 실행자입니다.

다음 쿼리를 실행하여 규칙 실행자에 대한 정보를 검색합니다:


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;

Data Privacy 대시보드 조회

데이터 보호 규칙과 함께 개인 데이터로 분류된 통제 아티팩트와 연관된 자산 검색

-- 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