[新機能]Snowflake CoCo for Excelがプレビューとなったので、自然言語クエリ・CORTEX セル関数・Intelligent refresh をExcelで試してみた

USE ROLE ACCOUNTADMIN;

CREATE WAREHOUSE IF NOT EXISTS COCO_EXCEL_WH
  WAREHOUSE_SIZE = XSMALL AUTO_SUSPEND = 60 AUTO_RESUME = TRUE INITIALLY_SUSPENDED = TRUE;
CREATE DATABASE IF NOT EXISTS COCO_EXCEL_DB;
CREATE SCHEMA IF NOT EXISTS COCO_EXCEL_DB.SALES;

USE WAREHOUSE COCO_EXCEL_WH;
USE SCHEMA COCO_EXCEL_DB.SALES;

CREATE OR REPLACE TABLE REGION AS
SELECT r_regionkey AS region_key, r_name AS region_name
FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.REGION;

CREATE OR REPLACE TABLE NATION AS
SELECT n_nationkey AS nation_key, n_name AS nation_name, n_regionkey AS region_key
FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.NATION;

CREATE OR REPLACE TABLE CUSTOMER AS
SELECT c_custkey AS customer_key, c_name AS customer_name, c_address AS address, c_nationkey AS nation_key,
       c_phone AS phone, c_acctbal AS account_balance, c_mktsegment AS market_segment, c_comment AS customer_comment
FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER
WHERE c_custkey BETWEEN 1 AND 1000;

CREATE OR REPLACE TABLE ORDERS AS
SELECT o_orderkey AS order_key, o_custkey AS customer_key, o_orderstatus AS order_status, o_totalprice AS total_price,
       o_orderdate AS order_date, o_orderpriority AS order_priority, o_clerk AS clerk, o_comment AS order_comment
FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS
WHERE o_custkey BETWEEN 1 AND 1000;

CREATE OR REPLACE TABLE CUSTOMER_FEEDBACK (
  feedback_id NUMBER, customer_key NUMBER, feedback_date DATE, channel VARCHAR(20), feedback_text VARCHAR(1000)
);

INSERT INTO CUSTOMER_FEEDBACK VALUES
  (1,  12, '2026-10-01', 'web',   '注文から到着まで 2 日で届きました。梱包も丁寧で満足しています。'),
  (2,  25, '2026-10-02', 'phone', '請求書の金額が見積と違っていました。確認に時間がかかり不満です。'),
  (3,  37, '2026-10-03', 'email', 'サポートの返信は早かったものの、回答内容が質問とずれていました。'),
  (4,  41, '2026-10-05', 'web',   '新しい管理画面は見やすくなりました。検索が速くなったのが助かります。'),
  (5,  58, '2026-10-07', 'phone', '配送が 1 週間遅れ、連絡もありませんでした。改善を求めます。'),
  (6,  63, '2026-10-09', 'email', '特に問題はありません。価格が少し高いと感じます。'),
  (7,  77, '2026-10-10', 'web',   'The delivery was fast and the product quality exceeded my expectations.'),
  (8,  84, '2026-10-12', 'email', 'I was charged twice for the same order and still waiting for the refund.'),
  (9,  91, '2026-10-14', 'phone', 'The support agent was polite but could not solve my login issue.'),
  (10, 99, '2026-10-15', 'web',   'Average experience. Nothing special, nothing wrong.');

CREATE OR REPLACE MASKING POLICY COCO_EXCEL_DB.SALES.MASK_CUSTOMER_PII AS (val STRING) RETURNS STRING ->
  CASE
    WHEN IS_ROLE_IN_SESSION('COCO_EXCEL_ANALYST') OR IS_ROLE_IN_SESSION('ACCOUNTADMIN') THEN val
    ELSE '*** MASKED ***'
  END;

ALTER TABLE COCO_EXCEL_DB.SALES.CUSTOMER MODIFY COLUMN customer_name
  SET MASKING POLICY COCO_EXCEL_DB.SALES.MASK_CUSTOMER_PII;
ALTER TABLE COCO_EXCEL_DB.SALES.CUSTOMER MODIFY COLUMN phone
  SET MASKING POLICY COCO_EXCEL_DB.SALES.MASK_CUSTOMER_PII;

CREATE OR REPLACE SEMANTIC VIEW COCO_EXCEL_DB.SALES.SV_SALES
  TABLES (
    orders    AS COCO_EXCEL_DB.SALES.ORDERS   PRIMARY KEY (order_key)    WITH SYNONYMS ('注文', 'sales orders'),
    customers AS COCO_EXCEL_DB.SALES.CUSTOMER PRIMARY KEY (customer_key) WITH SYNONYMS ('顧客', 'clients'),
    nations   AS COCO_EXCEL_DB.SALES.NATION   PRIMARY KEY (nation_key),
    regions   AS COCO_EXCEL_DB.SALES.REGION   PRIMARY KEY (region_key)
  )
  RELATIONSHIPS (
    orders_to_customers  AS orders (customer_key)  REFERENCES customers (customer_key),
    customers_to_nations AS customers (nation_key) REFERENCES nations (nation_key),
    nations_to_regions   AS nations (region_key)   REFERENCES regions (region_key)
  )
  FACTS (
    orders.order_total AS total_price
  )
  DIMENSIONS (
    orders.order_month       AS DATE_TRUNC('MONTH', order_date),
    customers.customer_name  AS customer_name,
    customers.market_segment AS market_segment,
    regions.region_name      AS region_name
  )
  METRICS (
    orders.total_revenue       AS SUM(order_total)   WITH SYNONYMS ('売上', 'revenue'),
    orders.order_count         AS COUNT(order_key)   WITH SYNONYMS ('注文件数'),
    orders.average_order_value AS AVG(order_total)   WITH SYNONYMS ('平均注文金額'),
    customers.customer_count   AS COUNT(customer_key) WITH SYNONYMS ('顧客数')
  );

USE ROLE ACCOUNTADMIN;
SET coco_user = 'ユーザー名';   -- Excel にサインインする Snowflake ユーザー

CREATE ROLE IF NOT EXISTS COCO_EXCEL_ANALYST;
CREATE ROLE IF NOT EXISTS COCO_EXCEL_VIEWER;
GRANT ROLE COCO_EXCEL_ANALYST TO ROLE SYSADMIN;
GRANT ROLE COCO_EXCEL_VIEWER  TO ROLE SYSADMIN;

-- CoCo Admin の "All Cortex AI features" 相当。CORTEX セル関数は Cortex REST API を呼ぶため CORTEX_USER を付ける
GRANT DATABASE ROLE SNOWFLAKE.CORTEX_USER TO ROLE COCO_EXCEL_ANALYST;
GRANT DATABASE ROLE SNOWFLAKE.CORTEX_USER TO ROLE COCO_EXCEL_VIEWER;

GRANT USAGE ON WAREHOUSE COCO_EXCEL_WH TO ROLE COCO_EXCEL_ANALYST;
GRANT USAGE ON WAREHOUSE COCO_EXCEL_WH TO ROLE COCO_EXCEL_VIEWER;
GRANT USAGE ON DATABASE COCO_EXCEL_DB TO ROLE COCO_EXCEL_ANALYST;
GRANT USAGE ON DATABASE COCO_EXCEL_DB TO ROLE COCO_EXCEL_VIEWER;
GRANT USAGE ON SCHEMA COCO_EXCEL_DB.SALES TO ROLE COCO_EXCEL_ANALYST;
GRANT USAGE ON SCHEMA COCO_EXCEL_DB.SALES TO ROLE COCO_EXCEL_VIEWER;
GRANT SELECT ON ALL TABLES IN SCHEMA COCO_EXCEL_DB.SALES TO ROLE COCO_EXCEL_ANALYST;
GRANT SELECT ON ALL TABLES IN SCHEMA COCO_EXCEL_DB.SALES TO ROLE COCO_EXCEL_VIEWER;
GRANT SELECT ON SEMANTIC VIEW COCO_EXCEL_DB.SALES.SV_SALES TO ROLE COCO_EXCEL_ANALYST;
GRANT SELECT ON SEMANTIC VIEW COCO_EXCEL_DB.SALES.SV_SALES TO ROLE COCO_EXCEL_VIEWER;

GRANT ROLE COCO_EXCEL_ANALYST TO USER IDENTIFIER($coco_user);
GRANT ROLE COCO_EXCEL_VIEWER  TO USER IDENTIFIER($coco_user);

-- ペインにはウェアハウスを選ぶ UI がないため、既定ウェアハウスを検証用にする
ALTER USER IDENTIFIER($coco_user) SET DEFAULT_WAREHOUSE = COCO_EXCEL_WH;