Snowflake数据库使用指南：功能、操作与最佳实践

# Snowflake 数据库使用指南：功能、操作与最佳实践 ## 1. 数据类型与存储 ### 1.1 半结构化数据类型 Snowflake 支持三种半结构化数据类型： - VARIANT：通用数据类型。 - OBJECT - ARRAY ### 1.2 时间戳数据类型 Snowflake 的 TIMESTAMP 数据类型支持本地时区。其中，TIMESTAMP_LTZ 是具有指定精度的内部 UTC 时间，是带本地时区的 TIMESTAMP，不支持夏令时。TIMESTAMP_TZ 值基于其 UTC 时间进行比较，不考虑夏令时，创建时存储给定时区的偏移量而非实际时区。 ### 1.3 派生列 派生列（有时称为计算列值或虚拟列值）不物理存储在表中，而是在每次查询引用时重新计算。派生列可用于计算其他派生列、被外部 SELECT 查询使用或作为 WITH 子句的一部分。 ### 1.4 非结构化数据访问 可通过以下三种方式访问 Snowflake 中的非结构化数据文件： - 阶段文件 URL - 作用域 URL - 预签名 URL 非结构化数据类型包括视频、音频或图像文件、日志文件、传感器数据和社交媒体帖子等。它有内部结构，但无法以结构化数据库格式存储。 ### 1.5 目录表 Snowflake 目录表是内置的只读表。 ## 2. 角色与权限 ### 2.1 固有权限 固有权限是分配给每个具有特定角色的用户的权限。某些系统定义的角色带有固有权限，例如资源监控器的权限是 ACCOUNTADMIN 角色固有的。权限除了固有之外，还可以分配和继承。 ### 2.2 系统定义的账户角色 Snowflake 系统定义的账户角色包括： - ORGADMIN - ACCOUNTADMIN - SYSADMIN - SECURITYADMIN - USERADMIN - PUBLIC 大多数自定义角色应分配给 SYSADMIN 角色。 ### 2.3 权限相关操作 - 为用户添加默认值可使操作更简便，但不会验证用户是否实际获得了该权限。 - 只有 ACCOUNTADMIN 可以创建新的资源监控器。 - 要查看表中的数据，角色需要具有使用表所在数据库和模式的权限，以及在表上使用 SELECT 命令的权限。 - 默认情况下，只有 ACCOUNTADMIN 角色可以访问 SNOWFLAKE 数据库。 - 默认情况下，Snowflake 组织中的最大账户数不能超过 25 个，但可联系 Snowflake 支持提高此限制。 - 具有授予选项的权限可以授予其他系统定义或自定义角色。 - SHOW 命令的结果基于执行该命令的用户角色，不同角色的用户可能因访问不同的虚拟仓库或数据库而得到不同结果。 - Snowflake 没有超级用户或超级角色的概念，对可安全对象的所有访问，即使是账户管理员，也需要通过显式授予或通过处于更高层次角色隐式授予访问权限。 ## 3. 数据加载与存储操作 ### 3.1 阶段类型 Snowflake 的阶段类型包括： - 内部阶段：用户阶段、表阶段和内部命名阶段。 - 外部阶段 内部命名阶段和外部阶段是数据库对象，而用户阶段和表阶段不能更改或删除，因此不是单独的数据库对象。 ### 3.2 插入命令 INSERT ALL 命令可用于无条件或有条件的多表插入。当键值对包含空值时，该键值对不会被插入。 ### 3.3 数据加载错误处理 使用加载数据向导时，错误处理选项如下： - 不加载文件中的任何数据。 - 停止加载、回滚并返回错误（默认）。 - 如果错误计数超过 <阈值>，则不加载文件中的任何数据。 - 继续从文件中加载有效数据。 ### 3.4 数据类型支持 - 加载数据类型：JSON、Parquet、XML、Avro、ORC（半结构化）和 CSV/TSV（结构化）。 - 卸载数据类型：JSON 和 Parquet（半结构化）以及 CSV/TSV（结构化）。 ### 3.5 Snowpipe #### 3.5.1 Snowpipe REST - 可用于内部和外部阶段。 - 手动调用 Snowpipe REST API 端点，提供管道名称和文件名列表。 - 传递阶段位置中的文件列表。 - 适用于数据随机到达、需要使用 ETL 或 ELT 工具进行预处理或外部阶段不可用的场景。 #### 3.5.2 Snowpipe AUTO_INGEST（更具扩展性的方法） - 仅适用于外部阶段。 - 新文件到达时从云提供商接收通知。 - 唤醒时处理新文件。 - 适用于文件持续到达的场景。 ### 3.6 资源管理 为防止资源争用，应将数据加载作业与查询隔离，为每个任务分配单独的虚拟仓库。不要假设大的虚拟仓库一定能比小的虚拟仓库更快地加载大量数据文件，可尝试将大文件拆分为 100 到 250 MB 左右的小文件。因为加载的文件数量和每个文件的大小对性能的影响比虚拟仓库的大小更大。 ### 3.7 数据传输命令 - PUT 命令将数据加载到阶段，然后 COPY INTO 命令将数据从阶段加载到表。 - GET 命令从阶段卸载数据，COPY INTO 命令先将数据从表卸载到阶段。 ## 4. 安全与存储保留 ### 4.1 多因素认证 多因素认证（MFA）应与强密码结合使用。Snowflake 为所有账户级别提供自助式 MFA，用户可自行注册。 ### 4.2 网络策略 账户网络策略和用户网络策略的数量没有限制，但一次只能激活一个账户级网络策略和每个用户一个用户级网络策略。 ### 4.3 密钥管理 Snowflake 的根密钥存储在硬件安全模块中，是唯一以明文存储的密钥。 ### 4.4 数据掩码与行访问策略 数据掩码策略包括动态掩码、条件掩码和静态掩码。数据掩码基于列，而行访问策略提供动态行级安全。注意，列不能同时具有掩码策略和行访问策略。 ### 4.5 时间旅行保留期 所有 Snowflake 账户自动启用时间旅行保留期，默认值为 24 小时，可在账户和对象级别设置为零。对于 Snowflake 企业版及更高版本的组织，永久数据库、模式和表的保留期可设置为最长 90 天。设置数据库的保留时间为 90 天，该数据库的所有对象也将具有 90 天的保留期。在模式级别更改数据保留时间，模式中的所有表将继承该模式的保留期，除非表被明确赋予不同的保留期。删除对象的顺序会影响保留期，如果要确保子对象的数据保留期得到遵守，需要在删除父对象之前删除子对象。 ### 4.6 数据标签 标签不需要用引号括起来。如果用双引号括起标签标识符，则区分大小写且可以包含空格；如果未加引号，则不区分大小写、不能包含空格且必须以字母或下划线开头。Snowflake 的自动数据标签有两种类型： - 语义标签 - 隐私标签 ### 4.7 数据扁平化 当存在嵌套数据层对象时，需要扁平化查询输出，可使用 FLATTEN 函数将嵌套数