イベントテーブルのデータベースへの関連付けを試してみた #SnowflakeDB

--sysadminで作成
USE ROLE sysadmin;

--変数を定義
----環境名
SET env_name = 'test1';
--SET env_name = 'test2'; 

----データベース名
SET db_name = concat($env_name,'_db');
----イベントテーブル名
SET event_table_name = concat($env_name,'_events');

--イベントテーブルとストアドプロシージャの作成先となるデータベースを作成
CREATE OR REPLACE DATABASE identifier($db_name);

--スキーマを作成
USE DATABASE identifier($db_name);
----イベントテーブルの作成
CREATE OR REPLACE SCHEMA event_tables;
----ストアドプロシージャの作成
CREATE OR REPLACE SCHEMA procedures;

--イベントテーブルを作成
USE DATABASE identifier($db_name);
USE SCHEMA event_tables;
CREATE OR REPLACE EVENT TABLE identifier($event_table_name);

--ストアドプロシージャの作成
USE DATABASE identifier($db_name);
USE SCHEMA procedures;

----サンプルテーブルを作成
CREATE OR REPLACE TABLE parent (ID INTEGER);
CREATE OR REPLACE TABLE child (ID INTEGER);

----Snowflakeスクリプトでストアドプロシージャを定義
CREATE OR REPLACE PROCEDURE insert_data()
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
$$
BEGIN
    BEGIN TRANSACTION;
    INSERT INTO procedures.parent VALUES (1);
    SELECT 1/0; --エラーを発生させる
    INSERT INTO procedures.child VALUES (1);
    COMMIT;
    RETURN 'complete';
EXCEPTION
  WHEN OTHER THEN
  ROLLBACK;
  RAISE;
END;
$$
;

--データベースにイベントテーブルを関連付け
USE ROLE ACCOUNTADMIN;
----環境1
ALTER DATABASE test1_db SET EVENT_TABLE = test1_db.event_tables.test1_events;
----環境2
ALTER DATABASE test2_db SET EVENT_TABLE = test2_db.event_tables.test2_events;

--設定値を確認:環境1
>SHOW PARAMETERS LIKE 'event_table' IN DATABASE test1_db;
+-------------+------------------------------------+----------------------------+----------+-----------------------------------------+--------+
| key         | value                              | default                    | level    | description                             | type   |
|-------------+------------------------------------+----------------------------+----------+-----------------------------------------+--------|
| EVENT_TABLE | test1_db.event_tables.test1_events | snowflake.telemetry.events | DATABASE | Event destination for the given target. | STRING |
+-------------+------------------------------------+----------------------------+----------+-----------------------------------------+--------+

--データベースレベルのログレベル設定変更
----データベース所有者のSYSADMINを使用
USE ROLE SYSADMIN;
--環境1：ログレベルを「WARN」に設定
ALTER DATABASE test1_db SET LOG_LEVEL = WARN;
--環境2：ログレベルを「ERROR」に設定
ALTER DATABASE test2_db SET LOG_LEVEL = ERROR;

--確認:環境1
>SHOW PARAMETERS LIKE '%LOG_LEVEL%' IN DATABASE test1_db;
+-----------+-------+---------+----------+----------------------------------------+--------+
| key       | value | default | level    | description                            | type   |
|-----------+-------+---------+----------+----------------------------------------+--------|
| LOG_LEVEL | WARN  | OFF     | DATABASE | LOG_LEVEL to use when filtering events | STRING |
+-----------+-------+---------+----------+----------------------------------------+--------+

--各環境でストアドプロシージャを作成
USE ROLE SYSADMIN;
--環境1
USE SCHEMA test1_db.procedures;
CALL insert_data();
--環境2
USE SCHEMA test2_db.procedures;
CALL insert_data();

{
  "db.user": "<ユーザー名>",
  "snow.database.id": 11,
  "snow.database.name": "TEST1_DB",
  "snow.executable.id": 513,
  "snow.executable.name": "INSERT_DATA():VARCHAR",
  "snow.executable.type": "PROCEDURE",
  "snow.owner.id": 2,
  "snow.owner.name": "SYSADMIN",
  "snow.query.id": "01bacb2a-0002-274f-0000-000223724785",
  "snow.schema.id": 33,
  "snow.schema.name": "PROCEDURES",
  "snow.session.id": 601922486771722,
  "snow.session.role.primary.id": 2,
  "snow.session.role.primary.name": "SYSADMIN",
  "snow.user.id": 1,
  "snow.warehouse.id": 4,
  "snow.warehouse.name": "COMPUTE_WH",
  "telemetry.sdk.language": "sql"
}

{
  "db.user": "<ユーザー名>",
  "snow.database.id": 12,
  "snow.database.name": "TEST2_DB",
  "snow.executable.id": 258,
  "snow.executable.name": "INSERT_DATA():VARCHAR",
  "snow.executable.type": "PROCEDURE",
  "snow.owner.id": 2,
  "snow.owner.name": "SYSADMIN",
  "snow.query.id": "01bacb2a-0002-27fe-0002-23720001702e",
  "snow.schema.id": 36,
  "snow.schema.name": "PROCEDURES",
  "snow.session.id": 601922486771722,
  "snow.session.role.primary.id": 2,
  "snow.session.role.primary.name": "SYSADMIN",
  "snow.user.id": 1,
  "snow.warehouse.id": 4,
  "snow.warehouse.name": "COMPUTE_WH",
  "telemetry.sdk.language": "sql"
}

--デフォルトのイベントテーブルを確認
USE ROLE ACCOUNTADMIN;
SELECT * FROM SNOWFLAKE.TELEMETRY.EVENTS;

----環境1
ALTER DATABASE test1_db UNSET EVENT_TABLE;
----環境2
ALTER DATABASE test2_db UNSET EVENT_TABLE;

--確認:環境1
>SHOW PARAMETERS LIKE 'event_table' IN DATABASE test1_db;
+-------------+----------------------------+----------------------------+-------+-----------------------------------------+--------+
| key         | value                      | default                    | level | description                             | type   |
|-------------+----------------------------+----------------------------+-------+-----------------------------------------+--------|
| EVENT_TABLE | snowflake.telemetry.events | snowflake.telemetry.events |       | Event destination for the given target. | STRING |
+-------------+----------------------------+----------------------------+-------+-----------------------------------------+--------+

USE SCHEMA test1_db.procedures;
CALL insert_data();

USE SCHEMA test2_db.procedures;
CALL insert_data();

--デフォルトのイベントテーブルを確認
USE ROLE ACCOUNTADMIN;
SELECT * FROM SNOWFLAKE.TELEMETRY.EVENTS;