Snowflake数据加载与处理全解析

# Snowflake数据加载与处理全解析 ## 1. 数据文件压缩 可以将压缩和未压缩的数据加载到Snowflake中。默认情况下，Snowflake中存储的所有数据都使用gzip进行压缩，无需手动选择列或压缩算法。如果数据文件较大，建议在加载到Snowflake之前进行压缩。Snowflake支持多种压缩方法，如GZIP、BZIP2、DEFLATE、RAW_DEFLATE、BROTLI和ZSTANDARD (ZSTD)，除BROTLI和ZSTANDARD (ZSTD)外，其余压缩方法均可自动检测，对于这两种方法，需要在加载数据时指定。此外，对于Parquet文件格式，Snowflake还支持Lempei - Ziv - Oberhumer (LZO)和SNAPPY压缩方法。 | 压缩方法 | 是否可自动检测 | | ---- | ---- | | GZIP | 是 | | BZIP2 | 是 | | DEFLATE | 是 | | RAW_DEFLATE | 是 | | BROTLI | 否 | | ZSTANDARD (ZSTD) | 否 | | Lempei - Ziv - Oberhumer (LZO)（Parquet格式） | - | | SNAPPY（Parquet格式） | - | ## 2. 数据处理频率 数据处理从原始数据开始，经过准备、处理后存储。可以将实时数据流传输到存储中以实现即时可用，但更常见的是近实时处理，有时也会进行批量处理。下面来看看不同数据处理类型的区别。 ### 2.1 批量处理 批量处理是指自动处理在指定时间段内积累的大量数据。例如工资系统，数据可能只需每两周处理一次。当数据只能批量访问、数据新鲜度不是关键要求，或者处理大型数据集上的复杂算法时，批量处理是不错的选择。其主要优点是通常比其他数据处理类型成本更低，因为计算资源仅在处理执行时使用，且批量处理执行频率低于近实时处理，所以总体成本较低，但代价是数据新鲜度较差。 ### 2.2 流式、连续加载和微批量处理 流式、流处理、连续加载、近实时处理和微批量处理这些术语常可互换使用，因为它们在实践中能实现相似的结果。连续加载、近实时处理和微批量处理含义相近，但与流式和流处理有所不同。 微批量处理对少量数据执行批量处理，处理时间通常不超过60秒。如果数据加载时间经常超过60秒，可考虑减小每个批次的大小。每次加载微批量之间的时间可能超过60秒，但通常不超过几分钟，以此实现近实时结果。 而纯流处理解决方案在需要实际实时数据的情况下，会使用Kafka Streams API或Confluent的KSQL等工具。例如在安全场景中，数据应用需提供即时欺诈检测功能；在运营场景中，需要实时查看电子商务数据或物联网（IoT）数据，如安全摄像头或患者医疗设备的数据。 ```mermaid graph LR A[原始数据] --> B{处理方式} B --> C[批量处理] B --> D[微批量处理] B --> E[流式处理] C --> F[积累大量数据] C --> G[定期处理] D --> H[少量数据] D --> I[短时间处理] E --> J[实时数据] E --> K[实时处理] ``` ## 3. Snowflake阶段引用 Snowflake阶段是临时存储空间，用于将文件导入Snowflake表或从Snowflake表导出数据到文件。主要有内部和外部两种类型。 ### 3.1 外部阶段 文件存储在外部位置，如S3存储桶，通过外部阶段引用。过去通过云身份和访问管理（IAM）角色和访问控制列表（ACL）管理对这些外部位置的访问，现在最佳实践是创建存储集成。存储集成是一个Snowflake对象，用于存储为外部云存储生成的IAM实体，还可选择包含Amazon S3、Google Cloud Storage或Microsoft Azure的允许或阻止存储位置。 ### 3.2 内部阶段 包括内部命名阶段、用户阶段和表阶段，所有阶段类型在Snowflake中都使用@符号引用。 - **命名阶段**：是数据库对象，任何被授予相应权限角色的用户都可使用。引用命名阶段的SQL语句需要@符号和阶段名称，可使用`LIST @<阶段名称>`语句列出命名阶段。 - **用户阶段**：每个Snowflake用户都有一个用于存储文件的阶段，只有该用户可以访问。它不是单独的数据库对象，不能被更改或删除，数据仅对执行SQL命令的特定用户可用。引用用户阶段的SQL语句需要@~符号，使用`LIST @~` SQL语句可列出用户阶段。 - **表阶段**：不是单独的数据库对象，而是与表本身关联的隐式阶段。与用户阶段一样，不能被更改或删除，表阶段中的数据仅对被授予从表中读取权限的Snowflake角色可用。引用表阶段的SQL语句需要@%符号和表名，使用`LIST @% <表名>`语句可列出表阶段。 ## 4. 数据源 数据可以从本地文件系统、Amazon S3存储桶、Azure容器或GCP存储桶加载到Snowflake中。从不同源加载数据不受Snowflake账户所在云平台的限制，除某些自动摄取Snowpipe用例外，无论Snowflake账户托管在Amazon、Azure还是GCP，都可以从上述任何数据源加载数据。使用Snowflake或第三方工具，几乎可以从任何数据源（包括文件、API、企业应用程序和数据库）将数据加载到Snowflake中。 ## 5. 数据加载工具 ### 5.1 Snowflake工作表SQL使用INSERT INTO和INSERT ALL命令 #### 5.1.1 单记录插入 - **结构化数据**： ```sql -- 创建表 CREATE OR REPLACE TABLE TABLE1 (id integer, f_name string, l_name string, city string) COMMENT = "Single - Row Insert for Structured Data using Explicitly Specified Values"; -- 插入一行数据 INSERT INTO TABLE1 (id, f_name, l_name, city) VALUES (1, 'Anthony', 'Robinson', 'Atlanta'); -- 查询表数据 SELECT * FROM TABLE1; -- 插入另一行数据 INSERT INTO TABLE1 (id, f_name, l_name, city) VALUES (2, 'Peggy', 'Mathison', 'Birmingham'); SELECT * FROM TABLE1; ``` - **半结构化数据（JSON）**： ```sql -- 创建表 CREATE OR REPLACE TABLE TABLE2 (id integer, variant1 variant) COMMENT = "Single - Row Insert for Semi - Structured JSON Data"; -- 插入一行半结构化数据 INSERT INTO TABLE2 (id, variant1) SELECT 1, parse_json(' {"f_name": "Anthony", "l_name": "Robinson", "city": "Atlanta" } '); SELECT * FROM TABLE2; -- 插入另一行半结构化数据 INSERT INTO TABLE2 (id, variant1) SELECT 2, parse_json(' {"f_name": "Peggy", "l_name": "Mathison", "city": "Birmingham" } '); SELECT * FROM TABLE2; ``` #### 5.1.2 多记录插入 - **结构化数据**： ```sql -- 创建表 CREATE OR REPLACE TABLE TABLE3 (id integer, f_name string, l_name string, city string) COMMENT = "Multi - row Insert for Structured Data using Explicitly Stated Values"; -- 插入两行数据 INSERT INTO TABLE3 (id, f_name, l_name, city) VALUES (1, 'Anthony', 'Robin ```