Microsoft Purview Data Governance で Snowflake のオブジェクトやリネージ情報などのメタデータを抽出する

Last updated at 2025-01-06Posted at 2024-09-17

はじめに

Purview Data Governance の Data Map は様々なテクニカルメタデータを収集します。
その対象に Snowflake のメタデータがあるので実際にやってみます。

参考

https://learn.microsoft.com/ja-jp/purview/register-scan-snowflake

準備

試用版の snowflake にサインアップして、以下のようにサンプルクエリに少し手を加えたスクリプトを実行します。

sql

/*--
Tasty Bytes is a fictitious, global food truck network, that is on a mission to serve unique food options with high
quality items in a safe, convenient and cost effective way. In order to drive forward on their mission, Tasty Bytes
is beginning to leverage the Snowflake Data Cloud.

Within this Worksheet, we will walk through the end to end process required to load a CSV file containing Menu specific data
that is currently hosted in Blob Storage.
--*/

-------------------------------------------------------------------------------------------
    -- Step 1: To start, let's set the Role and Warehouse context
        -- USE ROLE: https://docs.snowflake.com/en/sql-reference/sql/use-role
        -- USE WAREHOUSE: https://docs.snowflake.com/en/sql-reference/sql/use-warehouse
-------------------------------------------------------------------------------------------

/*-- 
    - To run a single query, place your cursor in the query editor and select the Run button (⌘-Return).
    - To run the entire worksheet, select 'Run All' from the dropdown next to the Run button (⌘-Shift-Return).
--*/

---> set the Role
USE ROLE accountadmin;

---> set the Warehouse
USE WAREHOUSE compute_wh;

-------------------------------------------------------------------------------------------
    -- Step 2: With context in place, let's now create a Database, Schema, and Table
        -- CREATE DATABASE: https://docs.snowflake.com/en/sql-reference/sql/create-database
        -- CREATE SCHEMA: https://docs.snowflake.com/en/sql-reference/sql/create-schema
        -- CREATE TABLE: https://docs.snowflake.com/en/sql-reference/sql/create-table
-------------------------------------------------------------------------------------------

---> create the Tasty Bytes Database
CREATE OR REPLACE DATABASE tasty_bytes_sample_data;

---> create the Raw POS (Point-of-Sale) Schema
CREATE OR REPLACE SCHEMA tasty_bytes_sample_data.raw_pos;

---> create the Raw Menu Table
CREATE OR REPLACE TABLE tasty_bytes_sample_data.raw_pos.menu
(
    menu_id NUMBER(19,0),
    menu_type_id NUMBER(38,0),
    menu_type VARCHAR(16777216),
    truck_brand_name VARCHAR(16777216),
    menu_item_id NUMBER(38,0),
    menu_item_name VARCHAR(16777216),
    item_category VARCHAR(16777216),
    item_subcategory VARCHAR(16777216),
    cost_of_goods_usd NUMBER(38,4),
    sale_price_usd NUMBER(38,4),
    menu_item_health_metrics_obj VARIANT
);

---> confirm the empty Menu table exists
SELECT * FROM tasty_bytes_sample_data.raw_pos.menu;

---> create stream for cdc 
CREATE OR REPLACE STREAM stream_menu on table tasty_bytes_sample_data.raw_pos.menu

---> create the Stage referencing the Blob location and CSV File Format
CREATE OR REPLACE STAGE tasty_bytes_sample_data.public.blob_stage
url = 's3://sfquickstarts/tastybytes/'
file_format = (type = csv);



---> create view

CREATE OR REPLACE VIEW v_FreezinePoint_menu AS 
    SELECT *,(sale_price_usd - cost_of_goods_usd) AS profit_usd 
    FROM tasty_bytes_sample_data.raw_pos.menu 
    WHERE truck_brand_name = 'Freezing Point'

---> create proc

CREATE OR REPLACE PROCEDURE GET_TOP_SALES()
RETURNS TABLE (MENU_ITEM_NAME VARCHAR, PROFIT_USD NUMBER)
LANGUAGE SQL
EXECUTE AS CALLER
AS
DECLARE
  RES RESULTSET DEFAULT (
  SELECT 
   MENU_ITEM_NAME,
   (SALE_PRICE_USD - COST_OF_GOODS_USD) AS PROFIT_USD
    FROM TASTY_BYTES_SAMPLE_DATA.RAW_POS.MENU
    ORDER BY PROFIT_USD DESC LIMIT 10
    );
BEGIN
  RETURN TABLE(RES);
END;


CREATE OR REPLACE PROCEDURE COPY_INTO_MENU()
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
BEGIN
    COPY INTO TASTY_BYTES_SAMPLE_DATA.RAW_POS.MENU
    FROM @TASTY_BYTES_SAMPLE_DATA.PUBLIC.BLOB_STAGE/raw_pos/menu/;

    RETURN 'Rows inserted: ' || SQLROWCOUNT;
    
END;


---> run

CALL COPY_INTO_MENU();
CALL GET_TOP_SALES();

---> check changes 

SELECT * FROM stream_menu

以下のようにいくつかのオブジェクトが作成できました。

次に、スキャンに必要なアクセス許可を参考に Purview からの参照用ユーザーを作成します。

stream に対しての権限が付与されてなかったので追加

sql


GRANT SELECT ON ALL  STREAMS IN DATABASE TASTY_BYTES_SAMPLE_DATA TO role purview_reader;
GRANT SELECT ON FUTURE STREAMS IN DATABASE TASTY_BYTES_SAMPLE_DATA TO role purview_reader;