Panoramica

Prerequisiti

Cosa imparerai

Cosa ti serve

Cosa realizzerai

Configurare i dati in Snowflake

Creare oggetti, caricare i dati e unire i dati

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

Creare un utente per l’applicazione

Passaggio 1 - Generare la chiave pubblica e privata per l’autenticazione

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

Passaggio 2 - Creare l’utente e il ruolo in Snowflake e concedere l’accesso ai dati a questo nuovo ruolo

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

Data application con la SQL API

Passaggio 1 - Ottenere il codice sorgente

Passaggio 2 - Configurare l’applicazione

Passaggio 3 - Esaminare il codice sorgente

Passaggio 4 - Testare l’applicazione

Pulizia

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; 

Conclusione

Conclusione

Fasi successive