概要

前提条件

学習する内容

必要なもの

構築するもの

Snowflakeでのデータの設定

オブジェクトの作成、データのロード、データの結合を行う

-- use our accountadmin role
USE ROLE accountadmin;

-- create our database
CREATE OR REPLACE DATABASE frostbyte_tasty_bytes_app;

-- create raw, harmonized, and analytics schemas
-- raw zone for data ingestion
CREATE OR REPLACE SCHEMA frostbyte_tasty_bytes_app.raw;

-- harmonized zone for data processing
CREATE OR REPLACE SCHEMA frostbyte_tasty_bytes_app.harmonized;

-- analytics zone for data driven insights
CREATE OR REPLACE SCHEMA frostbyte_tasty_bytes_app.analytics;

-- create csv file format
CREATE OR REPLACE FILE FORMAT frostbyte_tasty_bytes_app.raw.csv_ff 
type = 'csv';

-- create an external stage pointing to S3
CREATE OR REPLACE STAGE frostbyte_tasty_bytes_app.raw.s3load
COMMENT = 'Quickstarts S3 Stage Connection'
url = 's3://sfquickstarts/frostbyte_tastybytes/app/app_orders/'
file_format = frostbyte_tasty_bytes_app.raw.csv_ff;

-- create our raw.app_order_header table
CREATE OR REPLACE TABLE frostbyte_tasty_bytes_app.raw.app_order_header
(
  order_id NUMBER(19,0),
    month NUMBER(2,0),
    day NUMBER(2,0),
    hour NUMBER(2,0),
    minute NUMBER(2,0),
    second NUMBER(2,0),
    order_total NUMBER(19,3),
    order_tax_amount NUMBER(2,0),
    first_name VARCHAR,
    last_name VARCHAR,
    gender VARCHAR,
    order_status VARCHAR
);

-- create our raw.order_detail table
CREATE OR REPLACE TABLE frostbyte_tasty_bytes_app.raw.app_order_detail
(
  order_id NUMBER(19,0),
  order_detail_id NUMBER(19,0),
  menu_item_name VARCHAR,
  quantity NUMBER(2,0),
  unit_price NUMBER(19,3)
);

-- create our virtual warehouse
CREATE OR REPLACE WAREHOUSE tasty_app_wh AUTO_SUSPEND = 60;

-- use our TASTY_APP_WH virtual warehouse so we can load our data
USE WAREHOUSE tasty_app_wh;

-- ingest from S3 into the app_order_header table
COPY INTO frostbyte_tasty_bytes_app.raw.app_order_header
FROM @frostbyte_tasty_bytes_app.raw.s3load/app_order_header.csv.gz;

-- ingest from S3 into the app_order_detail table
COPY INTO frostbyte_tasty_bytes_app.raw.app_order_detail
FROM @frostbyte_tasty_bytes_app.raw.s3load/app_order_detail.csv.gz;

-- create our harmonized.data_app_orders_v view
CREATE OR REPLACE VIEW frostbyte_tasty_bytes_app.harmonized.data_app_orders_v
  AS
SELECT 
    oh.order_id,
    TIMESTAMP_NTZ_FROM_PARTS(YEAR(CURRENT_DATE()),oh.month,oh.day,oh.hour,oh.second, 0) AS order_ts,
    oh.order_total,
    oh.order_tax_amount,
    oh.first_name,
    oh.last_name,
    oh.gender,
    oh.order_status,
    od.order_detail_id,
    od.menu_item_name,
    od.quantity,
    od.unit_price
FROM frostbyte_tasty_bytes_app.raw.app_order_header oh
JOIN frostbyte_tasty_bytes_app.raw.app_order_detail od
ON oh.order_id = od.order_id;

-- promote our view to analytics
CREATE OR REPLACE VIEW frostbyte_tasty_bytes_app.analytics.data_app_orders_v
  AS
SELECT * FROM frostbyte_tasty_bytes_app.harmonized.data_app_orders_v;

-- view orders data
SELECT * FROM frostbyte_tasty_bytes_app.analytics.data_app_orders_v;

アプリケーションのユーザーを作成する

ステップ1：認証用の公開キーと非公開キーの生成

$ cd ~/.ssh
$ openssl genrsa -out snowflake_app_key 4096
$ openssl rsa -in snowflake_app_key -pubout -out snowflake_app_key.pub

ステップ2：Snowflakeでユーザーとロールを作成し、この新しいロールにデータアクセスを許可する

-- use our securityadmin role
USE ROLE securityadmin;

-- create the tasty_bytes_data_app_demo role
CREATE OR REPLACE ROLE tasty_bytes_data_app_demo;

-- use our securityadmin role
USE ROLE accountadmin;

-- grant privileges to our tasty_bytes_data_app_demo role
GRANT USAGE ON WAREHOUSE tasty_app_wh TO ROLE tasty_bytes_data_app_demo;
GRANT USAGE ON DATABASE frostbyte_tasty_bytes_app TO ROLE tasty_bytes_data_app_demo;
GRANT USAGE ON SCHEMA frostbyte_tasty_bytes_app.analytics TO ROLE tasty_bytes_data_app_demo;
GRANT USAGE ON SCHEMA frostbyte_tasty_bytes_app.harmonized TO ROLE tasty_bytes_data_app_demo;
GRANT USAGE ON SCHEMA frostbyte_tasty_bytes_app.raw TO ROLE tasty_bytes_data_app_demo;
GRANT SELECT ON ALL VIEWS IN SCHEMA frostbyte_tasty_bytes_app.analytics TO ROLE tasty_bytes_data_app_demo;
GRANT SELECT ON ALL VIEWS IN SCHEMA frostbyte_tasty_bytes_app.harmonized TO ROLE tasty_bytes_data_app_demo;
GRANT SELECT ON ALL TABLES IN SCHEMA frostbyte_tasty_bytes_app.analytics TO ROLE tasty_bytes_data_app_demo;
GRANT SELECT ON ALL TABLES IN SCHEMA frostbyte_tasty_bytes_app.harmonized TO ROLE tasty_bytes_data_app_demo;
GRANT SELECT ON ALL TABLES IN SCHEMA frostbyte_tasty_bytes_app.raw TO ROLE tasty_bytes_data_app_demo;
GRANT UPDATE ON TABLE frostbyte_tasty_bytes_app.raw.app_order_header TO ROLE tasty_bytes_data_app_demo;

-- use our useradmin role
USE ROLE useradmin;

-- Open the ~/.ssh/snowflake_app_key.pub file from Step 1 and copy the contents starting just after the PUBLIC KEY header, 
-- and stopping just before the PUBLIC KEY footer for INSERT_RSA_PUBLIC_KEY_HERE. Make sure to remove the additinal spaces. It should be a single line string.
CREATE OR REPLACE USER data_app_demo
RSA_PUBLIC_KEY='<INSERT_RSA_PUBLIC_KEY_HERE>' 
DEFAULT_ROLE=frostbyte_tasty_bytes_app 
DEFAULT_WAREHOUSE=tasty_app_wh 
MUST_CHANGE_PASSWORD=false;

-- use our securityadmin role
USE ROLE securityadmin;
GRANT ROLE tasty_bytes_data_app_demo TO USER data_app_demo;

SQL APIによるデータアプリケーション

ステップ1：ソースコードを取得する

ステップ2：アプリケーションを設定する

ステップ3：ソースコードをレビューする

ステップ4：アプリケーションをテストする

クリーンアップ

USE ROLE accountadmin;
DROP DATABASE frostbyte_tasty_bytes_app;
DROP WAREHOUSE tasty_app_wh;

USE ROLE securityadmin;
DROP USER data_app_demo;
DROP ROLE tasty_bytes_data_app_demo;

まとめ

まとめ

次のステップ