Watson Knowledge Catalog için örnek raporlama sorguları

Translation not up to date

The translation of this page does not represent the latest version. For the latest updates, see the English version of the documentation.

Go back to the English version of the documentation

Watson Knowledge Catalog için örnek raporlama sorguları

Last updated: 29 Eyl 2023

Watson Knowledge Catalog için örnek raporlama sorguları

Raporlama için aşağıdaki kullanım senaryolarını ve raporları oluşturmak için örnek SQL sorgularını kontrol edin.

Not:

Aksi belirtilmedikçe, sorgular desteklenen tüm veritabanları için geçerlidir.
Tüm sorgular şema adları için globalschema ve zoneschema kullanır. Bunları, ortamınızda kullanılan şema adıyla değiştirmelisiniz:
- globalschema , kullanıcı arabiriminde seçilen varsayılan şemaktır.
- zoneschema , şemaya özgü katalog, proje ya da kategoridir.
Kullanıcı, kullanıcı arabiriminde bunu seçerse globalschema ve zoneschema aynı olabilir.

Katalog, proje ve varlık sorguları

Tipe göre gruplanmış katalog varlıklarının sayısı

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

Belirli bir zamandan bu yana oluşturulan bağlantı sayısını al

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

Etikete ve varlık tipine göre gruplanan katalog varlıklarının sayısını al

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

Açıklaması olmayan katalog varlıklarını listele

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

Bir katalogdaki varlıklar arasında iş terimi dağıtımını göster

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'

İş koşulları atanmamış katalog varlıklarını listele

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

Belirli bir kullanıcının işbirliği yapan olarak atandığı katalog varlıklarını listele

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

Katalog varlıkları veri kalitesi puanlarının eğilimini göster

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

PostgreSQLiçin:

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

Yönetişim yapıtları sorguları

Yapay nesne tipine göre gruplanmış yönetişim yapay nesnelerinin sayısını topla

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

Açıklaması olmayan yönetişim yapıtlarını listele

SELECT 
  * 
FROM 
  zoneschema.GOVERNANCE_ARTIFACTS 
WHERE 
  DESCRIPTION IS NULL

Atanmış görevlilerle yönetişim yapay nesnelerinin yüzdesini göster

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

Belirli bir iş koşulunun ilgili koşullarını listele

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

Üst düzey kategori için sıradüzeni göster

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

PostgreSQLiçin:

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

Belirli bir yönetişim kuralına atanan ilkelerin sayısını al

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) sorguları

Keşfedilen varlıkların sayısını göster

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

Son çalıştırmanın ardından değiştirilen varlıkların sayısını göster (MDI ile ilgili kullanıcı bilgilerini göster-yaratma, güncelleme, el ile yeniden çalıştırma)

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

Yeni meta veri içe aktarma yürütmelerini say

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

Meta veri zenginleştirme (MDE) sorguları

Bir projenin hangi meta veri zenginleştirmeyi içerdiğini göster

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

Meta veri zenginleştirmesi ve meta veri içe aktarma arasındaki ilişkilendirmeyi göster

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

Gözden geçirilen varlıkların ayrıntılarına bakın

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

Gözden geçirilen varlıkların ve sütunların ayrıntısı

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

Değişiklik yazarı ve zamanlamasıyla birlikte varlık düzeyinde atanan, önerilen ya da reddedilen koşulları denetleyin

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

Değişiklik yazarı ve zamanlamasıyla birlikte sütun düzeyinde atanan, önerilen ya da reddedilen terimleri denetleyin

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

Sütun düzeyinde atanan veri sınıflarını, değişiklik yazarı ve zamanlamasıyla birlikte denetleyin

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

Projeden ilgili kataloglara yayınlanan varlıkların listesi

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

Data Privacy gösterge panosu sorguları

Veri Koruma Kurallarıyla birlikte Kişisel Veri olarak sınıflandırılan yönetişim yapıtlarıyla ilişkili varlıkları al

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

Üst konu: Watson Knowledge Catalog