第四章：探索Snowflake SQL命令、数据类型和函数

正如我们在前几章中所学到的，Snowflake是一个在关系数据库内以优化、压缩、列式格式存储数据的系统。Snowflake的数据终端用户需要访问存储的数据，并能够给出执行任务、调用函数和执行查询等指令。完成这一任务的方式是使用关系数据库的标准编程语言——结构化查询语言（SQL）。Snowflake支持SQL:ANSI，这是最常见的SQL标准化版本。除了对结构化数据提供SQL支持之外，Snowflake还原生支持半结构化数据格式，如JSON和XML。Snowflake还支持非结构化数据。

本章的主要重点是学习使用Snowflake工作表执行各种SQL命令、使用不同的数据类型和函数。除了在Snowflake Web界面中使用工作表之外，还可以使用Snowflake原生的命令行客户端SnowSQL来创建和执行SQL命令。有关SnowSQL的更多详细信息将在第6章中提供。

除了通过Web界面或SnowSQL连接到Snowflake之外，还可以使用ODBC和JDBC驱动程序通过外部应用程序（如Tableau和Looker）访问Snowflake数据。我们将在第12章中探索与Tableau和Looker的连接。还可以使用Python和Spark等原生连接器开发与Snowflake连接的应用程序。

为了帮助您为即将到来的高级主题做好准备，我们首先将专注于学习Snowflake SQL命令、数据类型和Snowflake函数的基础知识。

准备工作

创建一个名为“Chapter4 Syntax Examples, Data Types, and Functions”的新工作表。如果需要创建新工作表，请参考“导航Snowsight工作表”以获取帮助。要设置工作表上下文，请确保您处于“Syntax Examples”工作表中，并使用SYSADMIN角色和COMPUTE_WH虚拟仓库。

在Snowflake中使用SQL命令的操作

SQL可以分为五种不同的语言命令类型。要创建Snowflake对象，您需要使用数据定义语言（DDL）命令。为了给予对这些对象的访问权限，需要使用数据控制语言（DCL）。接下来，您将使用数据操作语言（DML）命令将数据输入和输出到Snowflake中。事务控制语言（TCL）命令使您能够管理事务块。然后，使用数据查询语言（DQL）语句来实际查询数据。以下是按类型组织的常见SQL命令列表：

DDL 命令:

• CREATE
• ALTER
• TRUNCATE
• RENAME
• DROP
• DESCRIBE
• SHOW
• USE
• SET/UNSET
• COMMENT

DCL 命令:

• GRANT
• REVOKE

DML 命令:

• INSERT
• MERGE
• UPDATE
• DELETE
• COPY INTO
• PUT
• GET
• LIST
• VALIDATE
• REMOVE

TCL 命令:

• BEGIN
• COMMIT
• ROLLBACK
• CREATE

DQL 命令:

• SELECT

下面将简要讨论这五种不同的命令语言类型及其相关命令。您可以在Snowflake的在线文档中找到所有Snowflake SQL命令的全面列表。

DDL 命令

DDL（数据定义语言）命令是用于定义数据库模式的SQL命令。这些命令用于创建、修改和删除数据库结构。此外，DDL命令还可以用于执行帐户级会话操作，例如设置参数，我们将在本章稍后讨论SET和UNSET命令时看到。DDL命令包括CREATE、ALTER、TRUNCATE、RENAME、DROP、DESCRIBE、SHOW、USE和COMMENT。除COMMENT命令外，每个DDL命令都需要指定对象类型和标识符。

Snowflake的DDL命令用于操作数据库、虚拟仓库、模式、表和视图等对象；但它们不操作数据。请参考专门介绍Snowflake DDL命令的第3章，其中详细解释了这些命令，并提供了许多实际示例。

DCL 命令

DCL（数据控制语言）命令是用于实现访问控制的SQL命令。DCL命令的示例包括GRANT和REVOKE。第5章将通过一系列完整且详细的示例，使用DCL命令向您展示如何保护Snowflake对象。

DML 命令

DML（数据操作语言）命令是用于操作数据的SQL命令。传统的DML命令，如INSERT、MERGE、UPDATE和DELETE，用于常规数据操作。对于数据的加载和卸载，Snowflake提供了COPY INTO

和COPY INTO 命令。此外，Snowflake的DML命令包括一些不执行实际数据操作的命令，而是用于暂存和管理存储在Snowflake位置的文件。一些示例包括VALIDATE、PUT、GET、LIST和REMOVE。第6章将探索Snowflake的许多DML命令。

TCL 命令

TCL（事务控制语言）命令用于管理Snowflake中的事务块。命令，如BEGIN、COMMIT和ROLLBACK，可用于会话中的多语句事务。Snowflake事务是一组读写SQL语句，它们作为一个单元一起处理。默认情况下，在查询成功时，单独运行的DML语句将单独提交，或者如果查询失败，则在语句结束时回滚。

DQL 命令

DQL（数据查询语言）命令是作为语句或子句使用的SQL命令，用于检索满足SELECT命令中指定条件的数据。请注意，SELECT命令是唯一的DQL命令；它用于检索数据，并通过指定位置并使用WHERE语句来包含进行数据选择所必需的属性。

Snowflake的SELECT命令适用于外部表，并可用于查询历史数据。在某些情况下，使用SELECT语句不需要运行虚拟仓库即可返回结果；这是由于Snowflake的缓存机制，正如第2章所述。SELECT语句是最常见的SQL语句，在本书的大多数章节中都可以找到示例。下一节将详细介绍如何充分利用SELECT命令。

在Snowflake中的SQL查询开发、语法和运算符

在Snowflake中，可以使用Snowflake UI工作表或SnowSQL以及许多第三方SQL工具原生地进行SQL开发。

查询语法是Snowflake SQL查询的结构或构建方式。通常有很多不同的方法可以编写一个能够产生所需结果的SQL查询。重要的是要考虑如何优化查询，以获得最佳的数据库性能和最低的成本。第9章包括一个专门讨论分析查询性能和优化技术的部分。

查询运算符是指在SQL查询语句中用于指定条件的保留术语，最常用于WHERE子句中。它们还可以用作语句中多个条件的连接词。我们将在本节的后面探索查询运算符。

SQL 开发和管理

在Snowflake中，有两种原生选项用于开发和查询数据。使用Snowflake界面内的基于浏览器的SQL编辑器Worksheets，可以轻松开始Snowflake SQL开发。使用Snowflake Worksheets不需要安装或配置。到目前为止，我们只使用了Snowflake Worksheets来创建对象和查询数据。

Worksheets的替代选择是SnowSQL，这是一个基于Python的客户端，可以从Snowflake客户端存储库下载并用于执行Snowflake任务，如查询或执行DDL和DML命令。SnowSQL经常用于数据的加载和卸载。在第6章中，我们将亲身体验SnowSQL。

Snowflake为Linux、macOS和Microsoft Windows提供SnowSQL版本。可执行的SnowSQL可以作为交互式shell或批处理模式运行。Snowflake提供了完整的下载和安装SnowSQL的说明，适用于所有支持的平台。

您可以通过查询Snowflake的查询历史记录，在Snowflake帐户中查看最近使用的客户端版本，包括SnowSQL版本。要查看该信息，如果您使用新的Snowsight Web界面，请单击"Activity" → "Query History"。如果您不能立即看到客户端驱动程序信息，请单击"Columns"按钮，然后选择"Client Driver"（如图4-1所示）。

或者，在经典控制台Web界面中，点击"History"选项卡。从那里，您可以通过在右上角点击"Column"按钮并选择"Client Driver"来查看"Client Info"列。有趣的是，在经典控制台Web界面中，"Client Info"列包含一个图标，用于指示客户端版本是否受支持、不受支持或即将结束支持。我们一直在使用Snowsight Web界面，因此我们将看到Go客户端驱动程序已被使用，并且它是受支持的，如复选标记所示（如图4-2所示）。

除了原生的Snowflake工具外，还有各种第三方SQL工具可用于在Snowflake应用程序中建模、开发和部署SQL代码。其中一些第三方工具，如DataOps和SqlDBM，可通过使用Snowflake Partner Connect进行免费试用。您可以访问Snowflake在线文档，了解更全面的可与Snowflake一起使用的第三方SQL工具列表。

查询语法

Snowflake SQL查询可以以WITH子句或SELECT命令开始。WITH子句是可选的，位于SELECT语句之前，用于定义在FROM子句中引用的公共表达式（CTEs）。然而，大多数查询以SELECT命令开始，后面是其他语法。其他语法按照以下顺序进行评估：

• FROM
• WHERE
• GROUP BY
• HAVING
• WINDOW
• QUALIFY
• ORDER BY
• LIMIT

请注意，QUALIFY在窗口函数之后进行评估；QUALIFY与窗口函数的工作方式类似于HAVING与聚合函数和GROUP BY子句的工作方式。有关窗口函数的更多信息将在本章后面介绍。

子查询、派生列和CTE

子查询是一个嵌套在另一个查询中的查询，可以用于计算在SELECT列表中返回的值，在GROUP BY子句中进行分组，或与WHERE或HAVING子句中的其他表达式进行比较。

在Snowflake中，子查询是作为一个块支持的嵌套SELECT语句，可以在以下一个或多个Snowflake SQL语句中使用：

• CREATE
• TABLE
• AS
• SELECT
• INSERT
• INSERT INTO
• UPDATE
• DELETE

为了准备我们的子查询和派生列的实践练习，我们需要创建一些简单的表格并将一些值插入到这些表格中。我们将为本章创建一个数据库。我们还将为我们的子查询和派生列示例创建一个模式和表格。在Snowsight中导航到Chapter4工作表以执行以下语句：

USE ROLE SYSADMIN;
USE WAREHOUSE COMPUTE_WH;
CREATE OR REPLACE DATABASE DEMO4_DB;
CREATE OR REPLACE SCHEMA SUBQUERIES;
CREATE OR REPLACE TABLE DEMO4_DB.SUBQUERIES.DERIVED
    (ID integer, AMT integer, Total integer);
INSERT INTO DERIVED (ID, AMT, Total)
VALUES (1,1000,4000),(2,2000,3500),(3,3000, 9900),(4,4000,3000),
    (5,5000,3700),(6,6000,2222);
SELECT * FROM DEMO4_DB.SUBQUERIES.DERIVED;

您的结果应该与图4-3中显示的结果相匹配。

我们还需要在SUBQUERIES模式中添加第二个表格；在添加表格后，我们将看到图4-4中显示的结果：

CREATE OR REPLACE TABLE DEMO4_DB.SUBQUERIES.TABLE2
    (ID integer, AMT integer, Total integer);
INSERT INTO TABLE2 (ID, AMT, Total)
VALUES (1,1000,8300),(2,1001,1900),(3,3000,4400),(4,1010,3535),
    (5,1200,3232),(6,1000,2222);
SELECT * FROM DEMO4_DB.SUBQUERIES.TABLE2;

现在我们已经创建了两个表，我们可以编写一个非相关子查询：

SELECT ID, AMT
FROM DEMO4_DB.SUBQUERIES.DERIVED
WHERE AMT = (SELECT MAX(AMT)
    FROM DEMO4_DB.SUBQUERIES.TABLE2);

您会注意到，非相关子查询是一个独立的查询，它的返回值不依赖于外部查询的任何列。非相关子查询返回一个仅被外部查询使用一次的单个结果。另一方面，相关子查询引用一个或多个外部列。相关子查询在外部查询表的每一行上进行计算，并返回每一行的一个结果。

现在让我们尝试执行一个相关子查询：

SELECT ID, AMT
FROM DEMO4_DB.SUBQUERIES.DERIVED
WHERE AMT = (SELECT AMT
    FROM DEMO4_DB.SUBQUERIES.TABLE2
    WHERE ID = ID);

我们收到了一个错误消息，告诉我们一个单行子查询返回了多行（如图4-5所示）。这可能不是您预期的结果。

从逻辑上讲，我们知道每个ID只有一行数据；因此，子查询在结果集中不会返回多行。然而，服务器无法知道这一点。我们必须使用MIN、MAX或AVG函数，这样服务器就能确保每次执行子查询时只返回一行结果。

让我们继续在语句中添加MAX，以了解这是如何工作的：

SELECT ID, AMT
FROM DEMO4_DB.SUBQUERIES.DERIVED
WHERE AMT = (SELECT MAX(AMT)
    FROM DEMO4_DB.SUBQUERIES.TABLE2
    WHERE ID = ID);

成功了！我们得到了一个包含一个行的结果集，其中ID的值等于3。让我们看看如果我们将等号改为大于号会发生什么：

SELECT ID, AMT
FROM DEMO4_DB.SUBQUERIES.DERIVED
WHERE AMT > (SELECT MAX(AMT)
    FROM DEMO4_DB.SUBQUERIES.TABLE2
    WHERE ID = ID);

现在我们得到了一个包含三个值的结果集（如图4-6所示）。

让我们看看如果我们将MAX更改为AVG会发生什么：

SELECT ID, AMT
FROM DEMO4_DB.SUBQUERIES.DERIVED
WHERE AMT > (SELECT AVG(AMT)
    FROM DEMO4_DB.SUBQUERIES.TABLE2
    WHERE ID = ID);

有五条记录在结果集中。您可以尝试在WHERE子句中使用不同的运算符和在SELECT子句中使用不同的聚合函数，以亲自了解关联子查询的工作原理。 由于每行结果都需要执行一个查询，关联子查询的使用频率较低，对于大多数用例来说，这可能不是最佳的可扩展方法。

子查询可以用于多种目的，其中之一是计算或推导出值，然后以各种不同的方式使用这些值。在Snowflake中，派生列也可以用于计算另一个派生列，可以被外部SELECT查询使用，或者可以作为WITH子句的一部分使用。这些派生列的值，有时称为计算列值或虚拟列值，并没有在表中实际存储，而是在每次查询引用时重新计算。

下一个示例演示了如何在Snowflake中使用派生列来计算另一个派生列。我们还将了解如何在一个查询中使用派生列，子查询和CTE。 让我们从AMT列创建一个派生列AMT1，然后直接使用第一个派生列来创建第二个派生列AMT2：

SELECT ID, AMT, AMT * 10 as AMT1, AMT1 + 20 as AMT2
FROM DEMO4_DB.SUBQUERIES.DERIVED;

运行该查询的结果可在图4-7中看到。

我们可以通过创建一个派生列AMT1来实现相同的结果，然后可以在外部SELECT查询中使用它。我们的示例中的子查询是一个Snowflake非相关标量子查询。提醒一下，这个子查询被认为是一个非相关子查询，因为返回的值不依赖于任何外部查询的列：

SELECT sub.ID, sub.AMT, sub.AMT1 + 20 as AMT2
FROM (SELECT ID, AMT, AMT * 10 as AMT1
    FROM DEMO4_DB.SUBQUERIES.DERIVED) AS sub;

最后，我们通过在WITH子句中使用一个派生列得到相同的结果。您会注意到，我们包含了一个CTE子查询，它可以增加模块化并简化维护。CTE定义了一个临时视图名称，在我们的示例中是CTE1。CTE中包含列名和一个查询表达式，其结果基本上是一个表格：

WITH CTE1 AS (SELECT ID, AMT, AMT * 10 as AMT2
    FROM DEMO4_DB.SUBQUERIES.DERIVED)
SELECT a.ID, b.AMT, b.AMT2 + 20 as AMT2
FROM DEMO4_DB.SUBQUERIES.DERIVED a
    JOIN CTE1 b ON(a.ID = b.ID);

使用CTE的一个主要好处是它可以使您的代码更可读。使用CTE，您可以定义一个临时表，并在需要时引用它，而不是在每个需要它的地方声明相同的子查询。虽然在这里没有展示，但CTE也可以是递归的。递归CTE可以将表与自身多次连接，以处理层次化的数据。

关于多行插入的注意事项

现在是学习一些关于多行插入的基本知识的好时机。可以使用SELECT查询插入一个或多个数据行，也可以将数据行作为显式声明的值插入，这些值以逗号分隔的列表形式呈现。为了简单起见，在本章中我们一直使用逗号分隔的列表插入值。

关于多行插入，有一件重要的事情需要注意。当向VARCHAR数据类型插入多行数据时，插入到VARCHAR列中的每个数据类型必须相同，否则插入将失败。VARCHAR数据类型可以接受诸如单词one或数字1之类的数据值，但不能在同一INSERT语句中同时插入这两种类型的值。我们可以通过一些示例来更好地理解这一点。

首先，我们将创建一个新的模式和表来进行一些多行插入测试。在第一个示例中，我们将向VARCHAR的DEPT列中插入值one：

USE ROLE SYSADMIN;
CREATE OR REPLACE SCHEMA DEMO4_DB.TEST;
CREATE OR REPLACE TABLE DEMO4_DB.TEST.TEST1 (ID integer, DEPT Varchar);
INSERT INTO TEST1 (ID, DEPT)
VALUES (1,'one');
SELECT * FROM DEMO4_DB.TEST.TEST1;

正如预期的那样，该值成功插入。现在让我们看看如果我们将一个数值插入到VARCHAR列中会发生什么：

USE ROLE SYSADMIN;
CREATE OR REPLACE SCHEMA DEMO4_DB.TEST;
CREATE OR REPLACE TABLE DEMO4_DB.TEST.TEST1 (ID integer, DEPT Varchar);
INSERT INTO TEST1 (ID, DEPT)
VALUES (1,1);
SELECT * FROM DEMO4_DB.TEST.TEST1;

同样，该值成功插入。现在让我们尝试在同一INSERT语句中将这两种类型都插入到列中：

USE ROLE SYSADMIN;
CREATE OR REPLACE SCHEMA DEMO4_DB.TEST;
CREATE OR REPLACE TABLE DEMO4_DB.TEST.TEST1 (ID integer, DEPT Varchar);
INSERT INTO TEST1 (ID, DEPT)
VALUES (1,'one'), (2,2);
SELECT * FROM DEMO4_DB.TEST.TEST1;

当我们尝试同时将两种不同的数据类型插入到VARCHAR列中时，我们会遇到错误，如图4-8所示。

让我们再试一次，但这次我们将插入两个相同数据类型的值：

USE ROLE SYSADMIN;
CREATE OR REPLACE SCHEMA DEMO4_DB.TEST;
CREATE OR REPLACE TABLE DEMO4_DB.TEST.TEST1 (ID integer, DEPT Varchar);
INSERT INTO TEST1 (ID, DEPT)
VALUES (1,'one'), (2,'two');
SELECT * FROM DEMO4_DB.TEST.TEST1;

如果我们将两个数值类型的值插入到VARCHAR列中，我们也会成功：

USE ROLE SYSADMIN;
CREATE OR REPLACE SCHEMA DEMO4_DB.TEST;
CREATE OR REPLACE TABLE DEMO4_DB.TEST.TEST1 (ID integer, DEPT Varchar);
INSERT INTO TEST1 (ID, DEPT)
VALUES (1,1), (2,2);
SELECT * FROM DEMO4_DB.TEST.TEST1;

您会注意到我们可以成功地将两种不同的数据类型加载到VARCHAR列中，但不能同时进行。一旦在VARCHAR列中有了两种不同的数据类型，我们仍然可以添加其他值：

INSERT INTO TEST1 (ID, DEPT)
VALUES (5, 'five');
SELECT * FROM DEMO4_DB.TEST.TEST1;

多行插入是将数据导入到Snowflake的一种方法。第6章专门讨论数据加载和卸载，并包括对批量数据加载选项和连续数据加载选项的深入讨论。

查询操作符

查询操作符有多种不同类型，包括算术、比较、逻辑、子查询和集合操作符。

算术操作符（Arithmetic operators）包括 +、–、*、/ 和 %，用于从一个或多个输入产生数值输出。输出的小数位数和精度取决于输入的小数位数和精度。请注意，减法是DATE表达式中唯一允许的算术操作。

比较操作符（Comparison operators）通常出现在WHERE子句中，用于测试两个输入的相等性。比较操作符包括以下内容：

• 等于（=）：测试两个输入是否相等。
• 不等于（<>或!=）：测试两个输入是否不相等。
• 大于（>）：测试第一个输入是否大于第二个输入。
• 小于（<）：测试第一个输入是否小于第二个输入。
• 大于等于（>=）：测试第一个输入是否大于或等于第二个输入。
• 小于等于（<=）：测试第一个输入是否小于或等于第二个输入。

逻辑操作符（Logical operators）只能在WHERE子句中使用。这些操作符的优先级顺序为NOT，然后AND，最后是OR。子查询操作符包括[NOT] EXISTS，ANY或ALL，以及[NOT] IN。当使用集合操作符时，可以组合查询，例如INTERSECT，MINUS或EXCEPT，UNION和UNION ALL。

集合操作符的默认优先顺序是INTERSECT具有最高优先级，其次是EXCEPT，MINUS和UNION，最后是UNION ALL具有最低优先级。当然，您可以始终使用括号来覆盖默认设置。请注意，UNION集合操作非常耗费资源，因为它需要对记录进行排序以消除重复行。

这些操作符提供了灵活的查询能力，可以根据特定的逻辑和条件来筛选和组合查询结果。

长时间运行的查询以及查询性能和优化

Snowflake系统会取消长时间运行的查询。长时间运行的查询的默认持续时间为两天，但可以始终在账户、会话、对象或虚拟仓库级别设置STATEMENT_TIMEOUT_IN_SECONDS持续时间值。

在Snowflake SQL查询过程中，优化引擎会找到特定查询的最高效执行计划。在第9章中，我们将学习更多关于分析查询性能和优化技术的内容，以及如何使用Snowflake的查询分析器（query profiler）。

Snowflake查询限制

通过Snowflake客户端提交的SQL语句具有1 MB的查询文本大小限制。这个限制包括文本中的字面量，包括字符串字面量和二进制字面量。查询文本大小限制适用于查询的压缩大小。然而，由于数据的压缩比例差异很大，建议将未压缩的查询文本大小保持在1 MB以下。

此外，Snowflake还限制了查询中允许的表达式数量为16,384个。根据您对SQL查询语句的操作目的，有多种方法可以解决这种类型的错误。如果您在插入数据时遇到错误，可以尝试将语句拆分为较小的查询。然而，更好的选择可能是使用COPY INTO命令而不是INSERT命令。

另一种类型的查询限制错误发生在使用SELECT语句并且IN子句中包含超过16,384个值的情况下。以下是可能出现此类错误的示例代码：

SELECT   <column_1>  FROM   <table_1> 
WHERE  <column_2>  IN  (1, 2, 3, 4, 5,...);

一个解决方案是在将这些值放入第二个表之后使用JOIN或UNION命令。SQL代码可能如下所示：

SELECT  <column_1> 
  FROM   <table_1>  a
    JOIN   <table_2>  b ON a. <column_2>   = b. <column_2> ;

Snowflake支持的数据类型简介

Snowflake支持基本的SQL数据类型，包括地理空间数据类型和布尔逻辑数据类型，提供了三值逻辑。Snowflake的BOOLEAN数据类型可以具有未知值、TRUE或FALSE值。如果布尔值用于表达式，比如SELECT语句中，未知值将返回NULL。如果布尔值用作谓词，比如在WHERE子句中，未知结果将评估为FALSE。Snowflake不支持一些数据类型，如大对象（LOB），包括BLOB和CLOB，以及ENUM和用户自定义数据类型。

Snowflake原生支持地理空间特性，如地球表面上的点、线和多边形。Snowflake的GEOGRAPHY数据类型遵循WGS标准。地球上的点用经度和纬度表示。目前不支持海拔高度。

在本节中，我们将深入探讨几种Snowflake数据类型，包括数字、字符串和二进制、日期和时间、半结构化以及非结构化数据类型。

数值数据类型

Snowflake的数值数据类型包括定点数和浮点数，详见表4-2。表中包含了每种数值数据类型的精度（precision）和标度（scale）信息。精度是指数字的总位数，影响存储空间；而标度是指小数点后的位数，不影响存储空间。然而，处理具有更大标度的数值数据可能会导致处理速度较慢。

定点数是精确的数值，因此通常用于自然数和精确的十进制值，如货币金额。相比之下，浮点数数据类型最常用于数学和科学领域。

您可以看到定点数根据数据类型的不同而有所变化。请确保导航到第4章的工作表，然后尝试以下示例：

USE ROLE SYSADMIN;
CREATE OR REPLACE SCHEMA DEMO4_DB.DATATYPES;
CREATE OR REPLACE TABLE NUMFIXED (
    NUM NUMBER,
    NUM12 NUMBER(12, 0),
    DECIMAL DECIMAL (10, 2),
    INT INT,
    INTEGER INTEGER
);

要查看创建的内容，可以运行DESC TABLE NUMFIXED语句以获取所示结果。

现在，您可以使用下面的示例来比较定点数和浮点数：

USE ROLE SYSADMIN; USE SCHEMA DEMO4_DB.DATATYPES;
CREATE OR REPLACE TABLE NUMFLOAT (
    FLOAT FLOAT,
    DOUBLE DOUBLE,
    DP DOUBLE PRECISION,
    REAL REAL
);

再次使用DESC命令来查看结果，如图4-10所示：

DESC TABLE NUMFLOAT;

在传统计算中，浮点数据类型被认为在计算中更快。但在现代数据平台（如Snowflake）中，浮点数据类型是否仍然具备这样的优势？并不一定。重要的是要考虑到在Snowflake中，整数值可以以压缩格式存储，而浮点数据类型则不行。这导致整数需要更少的存储空间和成本。对整数表类型进行查询的时间也明显较短。

Snowflake的数值数据类型支持数值常量。常量，也称为字面量，表示固定的数据值。数值常量可以以正负符号开头，数字范围从0到9。Snowflake还支持指数表示法，使用e或E来表示指数部分的数值常量。

字符串和二进制数据类型

Snowflake支持文本和二进制字符串数据类型，具体细节可以参考表4-3。

您可以通过尝试以下示例来了解文本字符串数据类型的差异，该示例将创建文本字符串字段，然后描述该表：

USE ROLE SYSADMIN; USE SCHEMA DEMO4_DB.DATATYPES;
CREATE OR REPLACE TABLE TEXTSTRING(
    VARCHAR VARCHAR,
    V100 VARCHAR(100),
    CHAR CHAR,
    C100 CHAR(100),
    STRING STRING,
    S100 STRING(100),
    TEXT TEXT,
    T100 TEXT(100)
);

DESC TABLE TEXTSTRING;

如果您按照示例进行操作，应该会看到图4-11中显示的输出结果。

Snowflake的字符串数据类型支持字符串常量，始终被定界符（单引号或美元符号）包围。在字符串包含多个引号字符时，使用美元符号作为定界符特别有用。

日期和时间的输入/输出数据类型

Snowflake使用公历（Gregorian calendar），而不是儒略历（Julian calendar），来处理所有日期和时间戳。Snowflake的日期和时间数据类型总结如表4-4所示。

Snowflake的日期和时间数据类型支持间隔常量（interval constants）以及日期和时间常量。间隔常量可以用于将特定的时间段添加到日期、时间或时间戳中，或者从中减去。间隔不是一种数据类型；它只能用于日期、时间或时间戳的计算，并且如果未指定日期或时间部分，它将表示为秒。

半结构化数据类型

结构化数据，也称为定量数据，可以以行和列的形式轻松存储在数据库表中，而半结构化数据（如XML数据）不依赖于模式，这使得它更难以存储在数据库中。然而，在某些情况下，半结构化数据可以存储在关系数据库中。

Snowflake支持导入和操作半结构化数据（如JSON、Avro、ORC、Parquet和XML数据）的数据类型。Snowflake通过其通用数据类型VARIANT来实现这一功能，VARIANT是一种特殊的列类型，允许您存储半结构化数据。表4-5提供了有关Snowflake半结构化数据类型的更多信息。请注意，VARIANT值可能是缺失的，这与真正的空值不同。

半结构化数据的实践练习将使用存储在原生JSON格式中的Snowflake示例天气数据。我们将花一些时间来了解现有的数据，然后学习如何使用FLATTEN函数生成半结构化数据的横向视图。

让我们首先快速查看一些数据行：

USE ROLE SYSADMIN; 
USE SCHEMA SNOWFLAKE_SAMPLE_DATA.WEATHER;
SELECT * FROM DAILY_16_TOTAL
LIMIT 5;

您会看到有两列数据：一个VARIANT列（V）和一个TIMESTAMP列（T），如图4-12所示。

让我们将重点放在VARIANT列中的数据上：

SELECT v:city
FROM SNOWFLAKE_SAMPLE_DATA.WEATHER.DAILY_16_TOTAL
LIMIT 10;

当结果返回后，点击列顶部的V:CITY。这将突出显示该列并提供您需要查看的详细信息，以查看此列中存在四个不同的对象键（如图4-13所示）。按顺序，与V:CITY相关的对象键是coordinates、country、ID和name。

现在让我们手动分解一些CITY数据，并按更合理的顺序列出它们（如图4-14所示）：

SELECT v:city:id, v:city:name, v:city:country, v:city:coord
FROM SNOWFLAKE_SAMPLE_DATA.WEATHER.DAILY_16_TOTAL
LIMIT 10;

纬度和经度的详细信息嵌套在坐标信息中。让我们将它们分离出来，并为列赋予一些适当的名称：

SELECT v:city:id AS ID, v:city:name AS CITY,
    v:city:country AS COUNTRY, v:city:coord:lat AS LATITUDE,
    v:city:coord:lon AS LONGITUDE
FROM SNOWFLAKE_SAMPLE_DATA.WEATHER.DAILY_16_TOTAL
LIMIT 10;

我们可以将变体数据类型转换为其他数据类型。在下一个示例中，我们将把城市和国家的VARIANT数据转换为VARCHAR数据类型，并为列分配有意义的标签：

SELECT v:city:id AS ID, v:city:name::varchar AS city,
    v:city.country::varchar AS country, v:city:coord:lon
    AS longitude, v:city:coord:lat AS latitude
FROM SNOWFLAKE_SAMPLE_DATA.WEATHER.DAILY_16_TOTAL
LIMIT 10;

结果如图4-15所示。

我们可以通过请求Snowflake描述我们最后一个查询的结果来确认我们成功地转换了这两列：

DESC RESULT LAST_QUERY_ID();

接下来，让我们查看VARIANT列中的更多数据：

SELECT v:data
FROM SNOWFLAKE_SAMPLE_DATA.WEATHER.DAILY_16_TOTAL
LIMIT 10;

结果返回后，点击列顶部的V:DATA。这将突出显示该列并在右侧提供列的详细信息（如图4-16所示）。您会注意到在该列中有一个与DATA信息相关的数组。

因为数据信息以数组形式存储，我们可以查看数组中的特定元素。请确保点击每个结果行，以查看每行只选择了一个元素。

SELECT v:data[5]
FROM SNOWFLAKE_SAMPLE_DATA.WEATHER.DAILY_16_TOTAL
LIMIT 10;

我们可以通过查看特定城市和国家某一天的湿度值来进一步限制返回的信息。

SELECT v:city:name AS city, v:city:country AS country,
    v:data[0]:humidity AS HUMIDITY
FROM SNOWFLAKE_SAMPLE_DATA.WEATHER.DAILY_16_TOTAL
LIMIT 10;

现在让我们快速回顾一下。当我们查看DATA数组时，在以下语句中的v:data AS DATA中，我们注意到每一行都包含一个完整的数据数组。在每个数据数组中，每个不同的数据部分有16个元素（如图4-17所示）。在我们的SQL查询中，我们将包括湿度和日温度的前两个数据元素：

SELECT v:data[0]:dt::timestamp AS TIME,
v:data[0]:humidity AS HUMIDITY0, v:data[0]:temp:day AS DAY_TEMP0,
v:data[1]:humidity AS HUMIDITY1, v:data[1]:temp:day AS DAY_TEMP1,  
v:data AS DATA
FROM SNOWFLAKE_SAMPLE_DATA.WEATHER.DAILY_16_TOTAL
LIMIT 100;

让我们看看如何利用FLATTEN表函数。FLATTEN函数可以产生VARIANT、OBJECT或ARRAY列的横向视图。我们将演示如何在示例天气数据表中对DATA数组使用FLATTEN函数：

SELECT d.value:dt::timestamp AS TIME,
    v:city:name AS CITY, v:city:country AS COUNTRY,
    d.path AS PATH, d.value:humidity AS HUMIDITY,
	d.value:temp:day AS DAY_TEMP,v:data AS DATA
FROM SNOWFLAKE_SAMPLE_DATA.WEATHER.DAILY_16_TOTAL,
LATERAL FLATTEN(input => daily_16_total.v:data) d
LIMIT 100;

您会注意到，对于每个展开的行，都会出现相同的DATA数组，但是每行报告的湿度和日温度与数组的特定路径相关联（如图4-18所示）。

DATA数组中的温度信息有六个嵌套值：day、eve、max、min、morn和night。我们可以使用嵌套的FLATTEN来进一步展开DATA数组。当我们这样做时，每个DATA行会出现96次，即每个16个路径值都会出现六次（如图4-19所示）。

SELECT d.value:dt::timestamp AS TIME,
t.key,
v:city:name AS CITY, v:city:country AS COUNTRY,
d.path AS PATH,
d.value:humidity AS HUMIDITY,
d.value:temp:day AS DAY_TEMP,
d.value:temp:night AS NIGHT_TEMP,
v:data AS data
FROM SNOWFLAKE_SAMPLE_DATA.WEATHER.DAILY_16_TOTAL,
LATERAL FLATTEN(input => daily_16_total.v:data) d,
LATERAL FLATTEN(input => d.value:temp) t
WHERE v:city:id = 1274693 
LIMIT 100;

正如我们刚刚看到的，Snowflake的FLATTEN函数用于将半结构化数据转换为关系表示形式。

非结构化数据类型

使用非结构化数据获得洞察力有许多优势。非结构化数据通常是定性数据，但也可以是缺乏行、列或分隔符的定量数据，例如包含定量数据的PDF文件。媒体日志、医学图像、呼叫中心录音的音频文件、文档图像以及许多其他类型的非结构化数据可用于分析目的和情感分析。存储和管理非结构化数据并不容易。非结构化数据没有按预定义的方式组织，这意味着它不适合关系型数据库。通常，非结构化数据被存储在Blob存储位置中，这带有一些固有的缺点，使得搜索文件变得困难而耗时。

为了提高非结构化数据的可搜索性，Snowflake最近推出了内置的目录表。现在，使用表格文件目录对非结构化数据进行搜索就像在目录表上使用SELECT *命令一样简单。用户还可以在目录表之上构建一个表流，从而可以创建用于处理非结构化数据的流水线。此外，Snowflake用户可以在目录表上创建安全视图，并能与他人共享这些安全视图。

Snowflake怎样支持非结构化数据的使用？

如今，非结构化数据所占据的比例越来越大。非结构化数据类型的示例包括视频、音频或图像文件、日志文件、传感器数据和社交媒体帖子。非结构化数据可以是人工生成的或机器生成的，它具有内部结构，但无法以结构化数据库格式进行存储。

有许多原因会让您希望利用所有这些非结构化数据。这些用例可能包括从呼叫中心录音中提取情感分析等洞察力；通过对保险卡或处方药片进行光学字符识别处理，提取文本进行分析；在DICOM医学图像上使用机器学习；或从存储的PDF文档中提取键值对。

非结构化数据的复杂性是众所周知的，因此在存储、搜索和分析非结构化数据时存在许多挑战。传统的数据仓库和数据湖无法充分支持当今数据格式，尤其是非结构化数据的工作负载需求。然而，Snowflake不是传统的数据仓库或数据湖。它是一个从云端开始构建的数据平台，因此它消除了存储、搜索、分析或处理非结构化数据所带来的许多困难。

在使用非结构化数据时的首要考虑是如何存储非结构化文件以及存储在何处。在使用Snowflake时，有两种方法可以做到这一点：内部存储区和外部存储区。如果我们希望在Snowflake内部存储数据，尤其是如果我们正在寻找一种简单、易于管理的解决方案，我们将使用内部存储区。这是因为Snowflake会自动管理可扩展性、加密、数据压缩和存储的其他方面。如果我们在云端的其他地方存储了遗留数据，我们可以选择使用外部存储区，也就是所谓的自定义存储，因为无需将所有数据移到Snowflake中。

虽然可以使用VARIANT列类型在Snowflake表中内部存储非结构化数据，但通常不建议这样做，因为存在16 MB的文件存储限制。相反，如果使用存储区，则没有大小限制，除非受到构建Snowflake实例的主要云提供商施加的限制：AWS和GCP的数据限制为5 TB，Azure的数据限制为256 GB。

无论您使用内部还是外部的Snowflake存储区，通过基于角色的访问控制可以轻松控制对数据的访问。通过使用GRANT和REVOKE语句，可以将权限授予Snowflake资源（如存储区），通过授予角色的权限再授予个人。可以轻松理解和学习如何对内部或外部存储区中的数据或在存储区上创建的Snowflake对象的子集进行细粒度的数据访问控制。有关Snowflake访问控制的复习，请参考第5章。

使用Snowflake，存储和授予对非结构化数据的访问可以通过三种不同的方式实现：存储区文件URL、作用域URL或预签名URL。

存储区文件URL访问

存储区文件URL用于创建指向Snowflake存储区上文件的永久URL，通常用于自定义应用程序。通过对REST API端点进行GET请求并附带授权令牌，可以访问文件URL。请注意，用户必须具有对存储区的读取权限。存储区文件URL具有独特的功能，可以在Snowflake目录表中列出。

创建目录表的能力就像创建文件目录一样，您可以轻松搜索目录表以检索文件URL以访问存储的文件以及其他元数据，这是Snowflake为非结构化数据提供的独特功能。已被授予权限的Snowflake角色可以查询目录表以检索访问存储文件的URL。

无论您想按文件大小或上次修改日期排序，还是仅获取前100个文件或最大的文件，使用Snowflake目录表都可以实现。您还可以将Snowflake流和任务与目录表结合使用，获得强大的组合。例如，使用表格流，您可以轻松找到最近添加的所有新文件。由于目录表是一个表格，因此可以执行细粒度的选择和搜索操作。在常规的Blob存储中进行搜索操作非常困难，因为它们没有以表格格式的目录信息。

Snowflake目录表是一个内置的只读表格。因此，您无法向目录表中添加更多列或修改列。您可以使用Snowflake流和任务计算值，并将其放入具有包含计算结果的列的新表格中。然后，您可以通过创建视图将该表格与目录表进行关联。如果需要，还可以添加标签。

作用域URL访问

作用域URL通常用于自定义应用程序，特别是在将数据共享功能提供给其他账户或在内部使用Snowsight进行即席分析的情况下。使用Snowflake在云端安全地共享非结构化数据非常简单。在存储区上不需要权限。相反，您可以创建一个安全视图，并使用作用域URL共享安全视图的内容。作用域URL经过编码，因此无法从URL中确定账户、数据库、模式或其他存储细节。

使用作用域URL访问存储区中的文件有两种方式。一种方式是Snowflake用户在Snowsight的结果表中点击作用域URL。另一种方式是将作用域URL作为请求发送，Snowflake将对用户进行身份验证，验证作用域URL是否未过期，然后将用户重定向到云端存储服务中的存储文件。请记住，存储文件在云存储中的位置是经过编码的，因此用户无法确定其位置。API调用输出中的作用域URL有效期为24小时，即结果缓存存在的当前时长。

预签名URL访问

预签名URL通常用于需要显示打开文件的非结构化文件内容的商业智能应用程序或报告工具。由于预签名URL已经经过身份验证，用户或应用程序可以直接访问或下载文件，无需传递授权令牌。

GET_PRESIGNED_URL函数使用存储区名称和相对文件路径作为输入生成预签名URL到存储区文件。使用预签名URL访问存储区中的文件可以通过以下三种方式完成：在Web浏览器中使用预签名URL直接导航到文件，单击Snowsight中结果表中的预签名URL，或将预签名URL包含在REST API调用请求中发送。

使用Java函数和外部函数处理非结构化数据

在Snowflake中运行非结构化数据内部文件的处理过程是其提供的最令人兴奋的功能之一。目前，使用Snowflake处理非结构化数据有两种方法：Java函数和外部函数。未来，Snowflake计划添加使用Python函数处理非结构化数据的功能。

如果您已经编写了用于处理非结构化数据的Java代码，那么使用Java用户定义函数（UDF）是很合理的。请注意，Java UDF在Snowflake中直接执行，使用Snowflake虚拟仓库。因此，Java UDF不会在Snowflake边界之外进行任何API调用。所有的操作都在Snowflake环境中进行严密的安全和管理。

如果存在外部API服务，如机器学习模型、地理编码器或其他自定义代码，您想要利用它们，可以使用外部函数。外部函数使得可以使用现有的机器学习服务从图像中提取文本，或者处理PDF文件以提取键值对等。在外部函数中，您可以使用AWS、Azure或GCP的任何功能，包括AWS Rekognition或Azure认知服务。在处理非结构化数据时，无论数据存储在内部还是外部阶段，外部函数都可以用于消除导出和重新导入数据的需要。

Snowflake SQL函数和Session变量

Snowflake为用户提供了创建用户定义函数（UDFs）和使用外部函数的能力，同时还可以访问许多不同的内置函数。会话变量也扩展了Snowflake SQL的功能。

使用系统定义的（内置）函数

内置函数的示例包括标量函数、聚合函数、窗口函数、表函数和系统函数。

标量函数接受单行或单个值作为输入，然后返回一个值作为结果，而聚合函数也返回一个值，但接受多行或多个值作为输入。

标量函数

一些标量函数可以对字符串或二进制输入值进行操作。例如，CONCAT、LEN、SPLIT、TRIM、UPPER和LOWER大小写转换以及REPLACE等。其他标量文件函数（如GET_STAGE_LOCATION）可以让您访问在Snowflake云存储中暂存的文件。 此外，您可以使用Snowflake的日期和时间数据类型执行许多操作。以下是一些标量日期和时间函数和数据生成函数的示例：

• 构建/解构（提取）使用月份、日期和年份组件。
• 将日期截断或“舍入”到更高的级别。
• 使用字符串解析和格式化日期。
• 添加/减去以查找和使用日期差异。
• 生成系统日期或日期表。

聚合函数

Snowflake聚合函数在输入不包含任何行时，始终会返回一行结果。当输入包含零行时，聚合函数返回的行可以是零、空字符串或其他值。聚合函数可以具有一般性质，如MIN、MAX、MEDIAN、MODE和SUM。聚合函数还包括线性回归、统计与概率、频率估计、百分位数估计等等。

Snowflake窗口函数是一种特殊类型的聚合函数，可以在一组行的子集上操作。这个相关行的子集被称为窗口。与对一组行返回单个值的聚合函数不同，窗口函数将为每个输入行返回一个输出行。输出不仅取决于传递给函数的个别行，还取决于传递给函数的窗口中其他行的值。

窗口函数通常用于找到年度百分比变化、移动平均值和累积总数，以及按分组或自定义条件对行进行排名。

让我们比较一下聚合函数和窗口函数。在第一个示例中，我们将使用字母表中的元音字母及其对应的位置创建一个聚合函数：

SELECT LETTER, SUM(LOCATION) as AGGREGATE
FROM (SELECT 'A' as LETTER, 1 as LOCATION
        UNION ALL (SELECT 'A' as LETTER,1 as LOCATION)
        UNION ALL (SELECT 'E' as LETTER,5 as LOCATION)
     ) as AGG_TABLE
GROUP BY LETTER;

这个查询的结果如图4-20所示。

接下来，我们将使用相同的逻辑创建一个窗口函数：

SELECT LETTER, SUM(LOCATION) OVER (PARTITION BY LETTER) as WINDOW_FUNCTION
FROM (SELECT 'A' as LETTER, 1 as LOCATION
        UNION ALL (SELECT 'A' as LETTER, 1 as LOCATION)
        UNION ALL (SELECT 'E' as LETTER, 5 as LOCATION)
     ) as WINDOW_TABLE;

请注意，在图4-21中，字母A在窗口函数中具有与聚合函数中相同的总和值，但由于输入中有两个单独的A列表，所以结果中重复出现了两次A。

表函数

表函数，通常称为表格函数，以表格格式返回结果，具有一个或多个列以及零个、一个或多个行。大多数Snowflake表函数都是1对N函数，其中每个输入行生成N个输出行，但也存在一些M对N表函数，其中M个输入行产生N个输出行。表函数可以是系统定义的或用户定义的。一些系统定义的表函数的示例包括VALIDATE、GENERATOR、FLATTEN、RESULT_SCAN、LOGIN_HISTORY和TASK_HISTORY。

系统函数

内置的系统函数返回系统级信息或查询信息，或执行控制操作。 一个经常使用的系统信息函数是SYSTEM$CLUSTERING_INFORMATION，它返回有关表中一个或多个列的聚类信息，包括平均聚类深度。 系统控制函数允许您在系统中执行操作。一个控制函数的示例是SYSTEM$CANCEL_ALL_QUERIES，需要会话ID。

您可以通过以ACCOUNTADMIN身份登录来获取会话ID。在Snowsight的主菜单中，转到Activity → Query History，然后使用Column按钮选择会话ID，以便它显示出来。或者，您可以在经典控制台界面中转到Account → Sessions：

    SELECT SYSTEM$CANCEL_ALL_QUERIES(<session_id>);

如果您需要取消特定虚拟仓库或用户的查询，而不是会话，您将需要使用ALTER命令以及ABORT ALL QUERIES，而不是系统控制函数。

创建SQL和JavaScript UDFs和使用会话变量

SQL功能可以通过SQL UDFs、Java UDFs、Python UDFs和会话变量进行扩展。在第三章中，我们深入探讨了SQL和JavaScript UDFs，所以在本节中我们将着重学习更多关于会话变量的内容。

Snowflake支持用户声明的SQL变量，使用SET命令进行定义。这些会话变量在Snowflake会话处于活动状态时存在。Snowflake SQL语句中的变量以$前缀来区分，并且在与对象一起使用时也可以包含标识符名称。要将变量用作标识符，您必须将变量包裹在标识符内，例如IDENTIFIER($Variable)。或者，在FROM子句的上下文中，您可以将变量包裹在对象内部。

要查看当前会话中定义的所有变量，可以使用SHOW VARIABLES命令。

一些会话变量函数的示例包括：

• SYS_CONTEXT和SET_SYS_CONTEXT
• SESSION_CONTEXT和SET_SESSION_CONTEXT
• GETVARIABLE和SETVARIABLE

在会话结束时，所有在会话期间创建的变量都将被删除。如果您想在会话期间销毁一个变量，可以使用UNSET命令。

外部函数

外部函数是一种调用存储在Snowflake之外的代码的UDF类型。Snowflake支持标量外部函数，这意味着远程服务必须为每个接收到的行返回精确的一行。在Snowflake中，外部函数被存储为数据库对象，Snowflake使用它来调用远程服务。

需要注意的是，Snowflake通常不直接调用远程服务，而是通过代理服务将数据中继到远程服务。Amazon API Gateway和Microsoft Azure API管理服务是可以使用的代理服务的示例。远程服务可以实现为AWS Lambda函数、Microsoft Azure函数，或在EC2实例上运行的HTTPS服务器（例如Node.js）。

远程服务提供商的任何费用将单独计费。在使用外部函数时，Snowflake会收取与数据传输和虚拟数据仓库使用相关的正常费用。

使用外部函数有许多优势。除了在Snowflake内部调用外，还可以从其他软件程序中调用外部函数。此外，远程服务的代码可以用Go或C#等语言编写，这些语言不能在其他Snowflake UDF中使用。其中最大的优势之一是Snowflake外部函数的远程服务可以与商业可用的第三方库进行接口交互，例如机器学习评分库。