Snowflake的重点知识

系列文章目录

提示：这里可以添加系列文章的所有文章的目录，目录需要自己手动添加
例如：第一章 Python 机器学习入门之pandas的使用

---

提示：写完文章后，目录可以自动生成，如何生成可参考右边的帮助文档

---

一、将各个数据源load到snowflake里

1.1 本地的数据使用internal stage

1.2 各种cloud的数据使用external stage

1.2.1 直接创建external stage加载azure blob里的数据到snowflake里

1. 创建针对azure blob里的csv文件的专门文件格式

CREATE OR REPLACE FILE FORMAT my_azure_blob_csv
  TYPE = 'CSV'
  FIELD_OPTIONALLY_ENCLOSED_BY = '"'
  SKIP_HEADER = 1
  FIELD_DELIMITER = ',';

2. 创建external stage，这里直接复制了azure container的sas token

CREATE OR REPLACE STAGE dbt_demo.raw.my_azure_stage
  URL = 'azure://dl201lg.blob.core.windows.net/snowflakecsv'
  CREDENTIALS = (AZURE_SAS_TOKEN = 'sp=r&st=2024-06-18T23:19:41Z&se=2024-06-26T07:19:41Z&spr=https&sv=2022-11-02&sr=c&sig')
  FILE_FORMAT = my_azure_blob_csv;

3. 查看external stage是否成功

LIST @dbt_demo.raw.my_azure_stage

4. 查看表的内容，注意如果不添加pattern 会将所有表都匹配到

select 
$1,
$2,
$3,
$4,metadata$filename 
from @dbt_demo.raw.my_azure_stage(PATTERN => 'employee.csv');

5. snowflake里创建employee表

CREATE OR REPLACE TABLE dbt_demo.raw.employee
(
   customerid NUMBER,
   custname STRING,
   email STRING
); 

6. 复制blob里，所有含有employee的所有文件到该表里

COPY INTO  dbt_demo.raw.employee
    FROM @dbt_demo.raw.my_azure_stage
    PATTERN = '.*employee.*';    

1.3 validate data before loading data into snowflake(验证数据)

加载大量数据时，最好进行data validation

1.3.1 创建csv表/定义csv的文件结构

• snowflake里，创建表 customer_validation

create or replace transient table customer_validation (
	customer_pk number(38,0),
	salutation varchar(10),
	first_name varchar(50),
	last_name varchar(50),
	gender varchar(1),
	marital_status varchar(1),
	day_of_birth date,
	birth_country varchar(60),
	email_address varchar(50),
	city_name varchar(60),
	zip_code varchar(10),
	country_name varchar(20),
	gmt_timezone_offset number(10),
	preferred_cust_flag boolean,
	registration_time timestamp_ltz(9)
);

• 创建csv文件的结构

create or replace file format csv_ff 
    type = 'csv' 
    compression = 'none' 
    field_delimiter = ',' 
    record_delimiter = '\n' 
    skip_header = 1 
    field_optionally_enclosed_by = '\047';

1.3.2 copy数据时使用validation_mode进行数据验证

1. 无视错误直接加载：我们使用copy语句，复制我们的表进入到snowflake里，不使用验证直接copy，此时不管数据格式是否错误，都将加载数据，遇到错误数据报错

copy into customer_validation
    from @~/ch08/small-csv/customer_01_one_error.csv
    file_format = csv_ff;

2. 跳过错误数据，加载正确数据：该模式下，只会加载正确的数据，错误的数据跳过

copy into customer_validation
    from @~/ch08/small-csv/customer_01_one_error.csv
    file_format = csv_ff
    on_error = 'continue';

3. 检查是否有错，只能进行验证，无Copy功能： 该模式下，会返回不符合表的规则的所有错误，注意只返回错误，如果验证没问题，需要重新进行一次copy数据

 copy into customer_validation
    from @~/ch08/small-csv/customer_01_one_error.csv
    file_format = csv_ff
    force = true
    validation_mode = return_errors;

4. 对大型数据Copy的validation：我们可以直接从文件夹里一次性将所有的文件进行validation

copy into customer_validation
	from @~/ch08/csv/partition 
	file_format = csv_gz_ff
	force = true
	pattern='.*[.]csv[.]gz'
	validation_mode = return_all_errors;