Snowflake数据工程——使用大语言模型输出增强数据

本章内容：

• 理解外部网络访问
• 配置外部网络访问
• 从 Snowpark 调用 API 端点
• 从 Yelp 等网站获取客户评论
• 推导客户评论的情感
• 使用大语言模型解释电子邮件内容以节省时间

Snowflake 的外部网络访问功能使数据工程师能够访问 Snowflake 之外的网络位置。这为通过从 Snowflake 存储过程或用户定义函数（UDF）调用 API 端点来访问数据开辟了无数机会。例如，数据工程师可以检索外部数据，以增强已存储在 Snowflake 中的数据。随着生成式 AI 能力和大语言模型（LLM）的普及，数据工程师可以通过调用 Snowflake Cortex LLM 函数来集成这些模型的输出。

在本章中，我们将学习如何从外部网站检索客户评论，并将其存储到 Snowflake 表中以供进一步分析。我们将调用一个 LLM 来帮助我们理解评论并对其情感进行分类。我们还将使用 LLM 来帮助我们解读电子邮件正文中的非结构化文本，并从中提取结构化信息。

为了说明本章中的示例，我们将继续使用第 2 章中介绍的虚构面包店。简单回顾一下，面包店制作面包和糕点，并将这些烘焙食品送到附近的小型商家，如杂货店、咖啡馆和餐馆。面包店还与一个全天候提供配送服务的食品配送服务公司签订了合作协议。食品配送服务网站允许客户就服务和配送食品的质量发表评论。面包店员工会查看这些评论，但这需要大量时间。他们希望将评论摄取到 Snowflake 表中，然后使用 LLM 对评论进行正面、负面或中性分类。然后，他们可以选择只阅读特定类型的评论。例如，他们可以只阅读负面评论，寻找改进的地方。

由于面包店没有在线订购系统，客户通过电子邮件订购烘焙食品。面包店的员工阅读电子邮件并将订单信息存储在本地文件系统中的 CSV 文件中。这是一个手动工作，因为员工必须阅读电子邮件，理解内容，提取结构化信息，例如客户订购的每种烘焙食品的数量和日期，并将数据保存到 CSV 文件中。员工可以改为使用 LLM 来解释每封收到的电子邮件正文，并以结构化格式返回订单信息，从而节省时间。

注意： 本章的所有代码可以在随附的 GitHub 仓库中的 Chapter_07 文件夹中找到，网址为 mng.bz/1aZy。SQL 和 Python 代码存储在多个文件中，文件名以 Chapter_07_Part* 开头，其中 * 表示序列号，附加信息表示文件的内容。请按顺序阅读文件以进行练习。

在 Snowflake 中创建外部访问集成对象是构建从外部 API 端点摄取数据的管道的前提条件。此对象提供了一种安全且受管控的方法来从外部 API 摄取数据，遵循下一节中描述的最佳数据管理和安全实践。

注意： 目前 Snowflake 试用账户不支持外部网络访问。您必须有一个付费的 Snowflake 账户才能调用外部 API 端点。

7.1 配置外部网络访问

在数据工程师能够从数据管道中调用外部 API 端点之前，必须由 Snowflake 管理员或具有足够权限的用户启用对外部网络位置的访问。访问权限封装在一个名为外部访问集成的 Snowflake 对象中。然后，数据工程师将被授予对外部访问集成的访问权限，以便在数据管道中使用它。Snowflake 管理员可以监控对外部网络位置的请求。

创建外部访问集成需要以下步骤：

1. 创建一个网络规则，用于表示一个或多个外部网络位置。
2. 创建一个密钥来存储访问外部网络位置的凭证。然后使用密钥而不是提供凭证值（如令牌、密钥或密码）。
3. 使用网络规则和密钥创建外部访问集成。

如图 7.1 所示，描述了外部网络访问架构。

为了展示如何使用外部访问，我们将创建一个外部访问集成，连接到一个托管客户评价的外部网站。由于我们正在与一个虚构的面包店合作，因此该面包店没有任何评价。为了模拟面包店如何在现实世界中完成此任务，我们将从Yelp.com网站读取客户评价。这个网站发布了来自全球的众包商业评价。

我们将随机选择Yelp网站上的一个面包店，并将相关评价导入到Snowflake表中。由于Yelp的免费API端点只能检索三个客户评价，因此我们只能获取三个客户评价。在实际情况下，面包店可能会支付使用食品配送服务的合作伙伴API，从而允许他们检索所有可用的客户评价。

获取公共API端点的信息

许多网站提供通过公共API端点访问的方式。API端点是一个接受请求并返回响应的URL。描述端点的文档通常可用，其中包括调用每个端点时可以指定的输入参数以及端点返回的输出格式。

要调用API端点，您需要一种身份验证方法，例如API密钥或令牌。网站的文档通常还会提供关于如何进行API端点身份验证并生成所需凭证的说明。

作为本示例的开始，我们必须研究如何访问Yelp的网站API。通过简单的互联网搜索“Yelp API”，我们找到了“开始使用Yelp Fusion API”文档，网址是 docs.developer.yelp.com/docs/fusion…。

在这里，我们了解到所有API端点都位于 api.yelp.com/v3。我们还找到了一个可用API端点的列表以及它们的描述。对于本练习，我们将使用“评价（最多三个摘录）”端点，该端点返回一个商业的最多三个评价摘录，其路径为 /businesses/{business_id_or_alias}/reviews。

文档随后引导我们访问身份验证指南，说明如何创建应用程序并获取私有API密钥。身份验证指南可以在 mng.bz/PNBg 找到。如果您正在跟随这个练习，请阅读身份验证指南中的说明，注册Yelp开发者网站，创建您的应用程序，并获取您的私有API密钥。

一旦知道API端点URL并且拥有API密钥，您就可以创建外部访问集成。对于本章中的练习，我们将使用在第二章中创建的BAKERY_DB数据库，并继续使用SYSADMIN角色。我们将通过执行以下命令在BAKERY_DB数据库中创建一个新的模式，命名为REVIEWS：

use role SYSADMIN;
use database BAKERY_DB;
create schema REVIEWS;
use schema REVIEWS;

由于用户需要权限来创建外部访问集成所需的对象，我们将授予SYSADMIN角色CREATE NETWORK RULE、CREATE SECRET和CREATE INTEGRATION权限：

use role ACCOUNTADMIN;
grant create network rule on schema REVIEWS to role SYSADMIN;
grant create secret on schema REVIEWS to role SYSADMIN;
grant create integration on account to role SYSADMIN;

现在，我们可以使用SYSADMIN角色创建一个名为YELP_API_NETWORK_RULE的网络规则。创建网络规则需要一些参数，例如“EGRESS”作为模式（表示“外发”，与“INGRESS”或“内入”相对）、HOST_PORT作为类型，以及外部位置的值列表。由于我们需要通过此网络规则访问Yelp网站，我们将在值列表中提供api.yelp.com外部位置。创建网络规则的命令如下：

use role SYSADMIN;
create network rule YELP_API_NETWORK_RULE
  mode = EGRESS
  type = HOST_PORT
  value_list = ('api.yelp.com');

接下来，我们将创建一个秘密，用于存储之前获得的API密钥。我们将秘密命名为YELP_API_TOKEN；我们将指定GENERIC_STRING作为类型（用于API密钥的类型），并在秘密字符串中提供我们的API密钥值，使用'ab12DE... 89XYZ'作为API密钥（这是一个虚构的API密钥，仅用于示例）。创建秘密的命令如下：

create secret YELP_API_TOKEN
  type = GENERIC_STRING
  secret_string = 'ab12DE...89XYZ';

在我们的示例中，我们使用SYSADMIN角色来创建秘密。由于我们没有自定义角色，因此相同的角色也将使用此秘密。通常，Snowflake管理员使用管理员角色创建秘密，然后将使用权限授予数据工程师使用的自定义角色。我们可以执行以下命令，将对秘密的使用权限授予自定义角色：

grant read on secret YELP_API_TOKEN to role <custom_role>;

提示：最佳实践是使用秘密存储访问凭证，而不是在代码中指定凭证。这有助于保护凭证免于意外暴露，并使Snowflake管理员能够管理凭证的使用，因为他们必须授予使用秘密的权限给个别数据工程师。

最后，我们可以通过提供YELP_API_NETWORK_RULE网络规则和YELP_API_TOKEN秘密作为参数，创建一个名为YELP_API_INTEGRATION的外部访问集成，命令如下：

create external access integration YELP_API_INTEGRATION
  allowed_network_rules = (YELP_API_NETWORK_RULE)
  allowed_authentication_secrets = (YELP_API_TOKEN)
  enabled = TRUE;

数据工程师需要拥有外部访问集成的USAGE权限和秘密的READ权限才能使用集成。在本练习中，我们使用SYSADMIN角色来创建和使用这些对象，因此我们不必显式地授予这些权限，因为SYSADMIN角色拥有这些对象。

注意：有关创建外部网络访问和指定每个命令的参数时各种选项的更多信息，请参阅Snowflake文档：mng.bz/JNWP。

7.2 从Snowpark函数调用API端点

现在我们已经配置了与Yelp API的外部访问集成，我们可以在Snowpark UDF中使用它。我们将创建一个Snowpark Python UDF，调用API并从Yelp网站返回客户评价。我们将把返回的数据存储在Snowflake表中以供进一步处理。

首先，访问Yelp网站，随机选择一个面包店并获取其评价。在 www.yelp.com/ 上的搜索框中，在第一个搜索框输入“bakery”，在第二个搜索框中输入你选择的城市（在此示例中，我们选择了法国巴黎）。你将获得所选城市的面包店列表。向下滚动，找到一个有很多评价的面包店（它应该至少有三个评价，因为我们将使用的API端点最多返回三个评价摘录）。点击面包店名称以打开该面包店的页面（我们选择了Boulangerie Julien）。检查该面包店页面的URL；在我们的示例中，URL如下所示：

https://www.yelp.com/biz/boulangerie-julien-paris-3?osq=bakery

我们需要商家的别名，因为我们将把它作为参数提供给API端点。在我们的示例中，商家别名是 boulangerie-julien-paris-3；这个文本位于URL中的 biz/ 部分之后，在 “?” 部分之前。如果你选择了不同的面包店，你的商家别名应遵循相同的模式。

7.2.1 构造获取客户评价的UDF

我们将使用Python的requests包来从UDF主体中执行API请求。有关此包的更多信息，请参考 requests.readthedocs.io/en/latest/ 。

根据我们目前收集的信息，我们可以创建一个名为 GET_CUSTOMER_REVIEWS 的UDF，定义如下：

• 函数接受一个名为 business_alias 的参数。
• 返回类型为 variant。
• 使用Python语言。
• Python版本为3.10（任何支持的Python版本都可以）。
• 处理程序代码的名称为 get_reviews（这是UDF主体中Python函数的名称）。
• 使用 YELP_API_INTEGRATION 作为外部访问集成。
• 使用 YELP_API_TOKEN 作为秘密。
• 使用 requests Python包。

创建UDF定义的代码（稍后我们将添加更多代码）如下所示：

Listing 7.1 UDF定义

create or replace function GET_CUSTOMER_REVIEWS(business_alias varchar)
returns variant
language python
runtime_version = 3.10
handler = 'get_reviews'
external_access_integrations = (YELP_API_INTEGRATION)
secrets = ('yelp_api_token' = YELP_API_TOKEN)
packages = ('requests')
AS     #1
#1 后续代码见Listing 7.2

在UDF的主体中，我们需要导入所需的包：

_snowflake
requests

注意：尽管我们需要在Python代码中导入两个包（_snowflake和requests），但在定义Snowpark UDF时，在 Listing 7.1 中我们只列出了 requests 包。原因是 _snowflake 是一个模块，暴露给在Snowflake中执行的Python UDF，Snowpark可以直接识别它，而无需显式列出。

接下来，我们创建 get_reviews Python处理程序函数，它有一个 business_alias 参数，执行以下操作：

• 使用 get_generic_secret_string() 方法从 YELP_API_TOKEN 秘密中检索API密钥，并将其存储在名为 api_key 的变量中。
• 通过将 /businesses/{business_id_or_alias}/reviews 模板中的 {business_id_or_alias} 占位符替换为 business_alias 参数的值，构造API端点URL。
• 使用 requests 包的 get() 方法，使用URL和API密钥作为参数从API端点检索结果。
• 返回JSON格式的响应。

UDF主体的代码如下所示：

Listing 7.2 UDF主体

$$     #1
import _snowflake
import requests

def get_reviews(business_alias):
  api_key = _snowflake.get_generic_secret_string('yelp_api_token')
  url = f'''https://api.yelp.com/v3/businesses/{business_alias}/reviews'''
  response = requests.get(
    url=url, 
    headers = {'Authorization': 'Bearer ' + api_key}
  )
  return response.json()
$$;
#1 从Listing 7.1延续

一旦我们在工作表中执行来自 Listing 7.1 和 Listing 7.2 的合并代码，就会创建UDF。我们可以像查询任何其他UDF一样，使用SQL SELECT 命令从这个UDF中选择数据，并提供我们之前选择的参数 boulangerie-julien-paris-3（如果你选择了其他面包店，可以使用不同的参数）：

select GET_CUSTOMER_REVIEWS('boulangerie-julien-paris-3');

此命令的示例输出如下图所示（仅显示了前几行）。

如我们所见，输出是JSON格式的。我们将从JSON结构中提取客户评价、评分以及客户创建评价的时间，并将结构化数据转换为表格格式。您可以参考API端点文档，了解JSON对象的描述，网址为 mng.bz/w5rW。

7.2.2 解释UDF结果

通过检查数据并阅读API端点文档，我们知道我们想要提取的信息（客户评价、评分和客户创建评价的时间）位于一个嵌套JSON对象的“reviews”键的值中。我们可以通过执行以下命令选择该键的值。

Listing 7.3 选择“reviews”键的值

select GET_CUSTOMER_REVIEWS('boulangerie-julien-paris-3'):"reviews";

此命令的示例输出如下所示（某些不相关信息已用“...”缩写）。

Listing 7.4 “reviews”键的示例值

[  {    "id": "yk",    "rating": 5,    "text": "I stayed in Paris for 3 days...",    "time_created": "2023-04-09 03:15:00",    "url": "https://...",    "user": {...}  },  {    "id": "FHjCTg26L1NlN615SyGHDA",    "rating": 4,    "text": "Stopped in on a Tuesday at around 9...",    "time_created": "2023-10-14 23:49:59",    "url": "https://...",    "user": {...}  },  {    "id": "Hw2k9Q6Ktg3-yStDsL_QVg",    "rating": 5,    "text": "After spending hours inside the Louvre...",    "time_created": "2022-12-22 08:43:28",    "url": "https://...",    "user": {...}  }]

“reviews”键的值包含一个数组，我们可以通过它被方括号 [] 包围来识别。为了将“rating”、“text”和“time_created”键的值存储到关系表中，我们必须使用 FLATTEN 语法将它们展平为单独的行，如第四章所述。

提示：在本例中，我们只需要 FLATTEN 关键字，而不需要使用第四章中的 LATERAL 修饰符，因为本查询的目的是展平数据，而不是将数据与主表表达式连接。

以下SQL命令通过提取“rating”、“text”和“time_created”键的值，将 Listing 7.3 查询的输出展平。

Listing 7.5 展平所选键的值

select 
  value:"rating"::number as rating, 
  value:"time_created"::timestamp as time_created, 
  value:"text"::varchar as customer_review
from table(flatten( 
  input => GET_CUSTOMER_REVIEWS(
  'boulangerie-julien-paris-3'):"reviews"     #1
));
#1 来自Listing 7.3的查询

此查询的示例输出如下所示，见Table 7.1（客户评价被缩写为“...”）。如果您选择了不同的面包店或自本章编写以来添加了新的评价，您的输出可能会有所不同。

Table 7.1 展平“reviews”键后的示例输出

Rating	Time created	Customer review
5	2023-04-09 03:15:00	I stayed in Paris for 3 days…
4	2023-10-14 23:49:59	Stopped in on a Tuesday at around 9…
5	2022-12-22 08:43:28	After spending hours inside the Louvre…

展平数据后，我们可以将其存储在表中以供进一步分析。

7.2.3 将客户评价存储到表中

让我们在之前创建的 REVIEWS 模式中创建一个名为 CUSTOMER_REVIEWS 的表，使用以下命令：

use schema REVIEWS;
create table CUSTOMER_REVIEWS (
  rating number,
  time_created timestamp,
  customer_review varchar
);

在将数据插入到 CUSTOMER_REVIEWS 表之前，我们将使用正则表达式去除客户评价中的所有不可打印字符。我们将仅保留将用于进一步文本分析的字符，例如小写字母a到z、大写字母A到Z、数字0到9，以及一些标点符号，包括空格、句点、逗号、感叹号、问号和破折号。我们不会在表中存储任何其他不可打印字符或外语字符。

正则表达式

用于在字符串中搜索模式并执行匹配模式、用不同模式替换、提取模式等操作的函数，使用正则表达式。有关Snowflake中支持的正则表达式函数的更多信息，请参阅文档：mng.bz/q0EE。

我们将使用 REGEXP_REPLACE 正则表达式命令，仅保留匹配模式中指定的字符：

regexp_replace(value:"text"::varchar, '[^a-zA-Z0-9 .,!?-]+')

现在，我们可以使用以下命令将 Listing 7.5 查询的结果插入到 CUSTOMER_REVIEWS 表中：

insert into CUSTOMER_REVIEWS
select 
  value:"rating"::number as rating, 
  value:"time_created"::timestamp as time_created, 
  regexp_replace(value:"text"::varchar, 
    '[^a-zA-Z0-9 .,!?-]+')::varchar 
    as customer_review     #1
from table(flatten(
  input => GET_CUSTOMER_REVIEWS('boulangerie-julien-paris-3'):"reviews"
));
#1 正则表达式，仅保留匹配模式中指定的字符

为了验证数据是否成功插入，我们可以使用以下命令查询 CUSTOMER_REVIEWS 表：

select * from CUSTOMER_REVIEWS;

此查询的输出应该类似于 Table 7.1。由于我们使用的是Yelp的免费API，因此最多返回三个评价。

警告：在本示例中，我们将数据插入到目标表中，而没有检查数据是否已经存在。在实际情况下，数据工程师应该检查文档，以确定API端点是否接受限制每次API调用返回的客户评价数量的参数。为了避免重复，另一种预防措施是将从API端点接收到的数据合并到目标表中，而不是直接插入。

一旦从外部网站检索到客户评价数据并存储在Snowflake表中，我们可以继续探索如何使用LLMs分析这些数据。

7.3 推导客户评价的情感

LLM（大规模语言模型）是一种人工智能算法，旨在生成文本内容。它使用深度学习技术，并基于大量数据集进行训练，以理解、总结、翻译或生成输出。

通过API调用LLM

最著名的LLM之一是OpenAI的GPT-3。你可以通过API使用它，但请注意，它不是免费的。在使用之前，你必须创建一个OpenAI账户并购买积分。有关如何通过API调用GPT-3的更多信息，请参阅 platform.openai.com/docs/api-re…。

在本章中，我们将使用Snowflake的Cortex LLM函数。Snowflake托管并管理这些函数，允许你访问由Mistral、Reka、Meta和Google等公司的研究人员训练的LLM。你还可以使用Snowflake开发的模型——Snowflake Arctic。

一些可用的Snowflake Cortex LLM函数包括：

• COMPLETE：返回完成的提示响应
• EMBED_TEXT_768：返回表示给定文本的向量嵌入
• EXTRACT_ANSWER：基于非结构化数据返回问题的答案
• SENTIMENT：返回给定文本的情感分数
• SUMMARIZE：返回给定文本的摘要
• TRANSLATE：将给定文本从一种支持的语言翻译为另一种语言

注意：有关Snowflake Cortex LLM函数的更多信息，请参阅文档 mng.bz/75ng。

我们将使用Snowflake Cortex的 SENTIMENT 函数来返回客户评价的情感结果，分为正面、负面或未知。 SENTIMENT 函数接受一个字符串并返回一个浮动的数字，范围从 -1 到 1，表示文本中的负面或正面情感程度。

要使用Snowflake Cortex LLM函数，用户需要具备所需的权限，我们将通过执行以下命令将这些权限授予 SYSADMIN 角色：

use role ACCOUNTADMIN;
grant database role SNOWFLAKE.CORTEX_USER to role SYSADMIN;

然后我们继续在 REVIEWS 模式下使用 SYSADMIN 角色：

use role SYSADMIN;
use database BAKERY_DB;
use schema REVIEWS;

接下来，我们通过提供字符串 'The service was excellent!' 作为参数来调用 SENTIMENT 函数：

select SNOWFLAKE.CORTEX.SENTIMENT('The service was excellent!');

该命令的输出是情感分数 0.7691993，接近 1，表示正面情感。

让我们尝试另一个例子：

select SNOWFLAKE.CORTEX.SENTIMENT('The bagel was stale.');

该命令的输出是情感分数 -0.42285544，表示负面情感。

再来看一个例子：

select SNOWFLAKE.CORTEX.SENTIMENT('I went to the bakery for lunch.');

这次，情感分数是 0.15711609，表示中性情感。

由于我们希望将客户的评价分类为正面、负面或中性，我们可以为情感分数（从 -1 到 1）定义界限，并确定各情感类别的边界。我们可以根据判断选择这些值，并在查看结果后进行调整。在本例中，我们将情感分数从 -1 到 -0.7 定义为负面，从 -0.7 到 0.4 定义为中性，分数大于 0.4 定义为正面。

要从情感分数推导情感类别，我们可以使用 SQL CASE 语句：

case
    when sentiment_score < -0.7 then 'Negative'
    when sentiment_score < 0.4 then 'Neutral'
    else 'Positive'
  end

要从 CUSTOMER_REVIEWS 表中推导客户的情感分数，我们可以在从表中选择时使用 SENTIMENT 函数，将 CUSTOMER_REVIEW 列作为函数参数，并使用 CASE 语句计算情感类别。返回情感分数和类别的查询是：

select 
  rating,
  time_created,
  customer_review,
  SNOWFLAKE.CORTEX.SENTIMENT(customer_review) as sentiment_score,
  case
    when sentiment_score < -0.7 then 'Negative'
    when sentiment_score < 0.4 then 'Neutral'
    else 'Positive'
  end as sentiment
from CUSTOMER_REVIEWS;

此命令的输出如 Table 7.2 所示（客户评价部分已缩写）。如果您的 CUSTOMER_REVIEWS 表中的数据不同，输出可能会有所不同。

Table 7.2 从 CUSTOMER_REVIEWS 表推导情感分数后的示例输出

Rating	Time created	Customer review	Sentiment score	Sentiment
5	2023-04-09 03:15:00	I stayed in Paris for 3 days…	0.8525086	Positive
4	2023-10-14 23:49:59	Stopped in on a Tuesday at around 9…	0.8410726	Positive
5	2022-12-22 08:43:28	After spending hours inside the Louvre…	0.64034367	Positive

情感分类可以帮助面包店员工减少他们需要阅读的客户评价数量，仅需阅读符合特定分类的评价。员工还可以跟踪情感分布，查看正面和负面评价的比例，从而帮助他们改善服务。

7.4 使用LLM解读订单邮件以节省时间

面包店员工通过使用LLM（大规模语言模型）解读客户邮件中的订单信息，可以节省大量时间。我们的虚构面包店没有在线订购系统。它通过电子邮件接收订单信息，面包店员工必须阅读这些邮件并将订单信息提取到结构化格式中，例如CSV格式。

我们可以构建一个提示，要求LLM读取并理解邮件内容，提取订单信息并以结构化格式返回数据，然后将数据写入Snowflake表中。让我们一起走过这个示例。

我们将创建一个存储过程，该过程构建一个提示，指示Snowflake Cortex COMPLETE 函数读取邮件内容并以CSV格式返回数据。然后，我们将添加代码，将CSV数据转换为数据框，并将数据框保存到Snowflake表中。

7.4.1 创建一个存储过程解读客户邮件

存储过程的定义与我们之前创建的 GET_CUSTOMER_REVIEWS 和 REVIEW_SENTIMENT UDF的定义类似，唯一不同的是：

• 我们将创建一个存储过程，而不是UDF，因为我们需要程序逻辑来从模型中检索结果，并将结果转换并保存到表中。
• 存储过程的名称为 READ_EMAIL_PROC，该过程接受一个名为 email_content 的参数，该参数传递邮件内容。
• 返回类型是 table() ，因为存储过程返回结构化信息，格式为表。
• 处理函数是 get_order_info_from_email。
• 存储过程使用 snowflake-ml-python 包，这提供了Snowflake的机器学习和LLM功能。
• 存储过程还使用 snowflake-snowpark-python 包，这是我们需要用来将CSV数据转换为数据框并保存到Snowflake表中的。

创建存储过程定义的代码如下：

create or replace procedure READ_EMAIL_PROC(email_content varchar)
returns table()
language python
runtime_version = 3.10
handler = 'get_order_info_from_email'
packages = ('snowflake-snowpark-python', 'snowflake-ml-python')

在存储过程的主体中，我们将像之前一样导入 _snowflake 包。我们还将导入 snowflake.snowpark 包，并使用该包导入Snowpark库和所需的数据类型，以便在将数据保存到表中时定义结构。最后，我们将从 snowflake.cortex 包中导入 Complete 函数：

import _snowflake
import snowflake.snowpark as snowpark
from snowflake.snowpark.types import StructType, StructField, DateType, StringType, IntegerType
from snowflake.cortex import Complete

在导入所需的包和库之后，我们将创建处理函数 GET_ORDER_INFO_FROM_EMAIL。该函数接受两个参数。第一个参数是存储过程所需的会话变量，以便存储过程能够使用Snowpark API库对象（如第6章所述）。第二个参数 email_content 来自存储过程的定义。以下代码表示处理函数的定义及其参数：

def get_order_info_from_email(session: snowpark.Session, email_content):

7.4.2 构建提示

接下来的步骤是构建提示。这里，我们可以根据需要尽可能创意地解释我们从LLM中需要什么。如果我们对LLM的输出不满意，我们可以在后续交互中完善提示，以获得更好的结果。提示的初始值为：

prompt = f"""You are a bakery employee, reading customer emails asking
   for deliveries. Please read the email at the end of this text and
   extract information about the ordered items. Format the information in
   CSV using the following columns: customer, order_date, delivery_date,
   item, and quantity. Format the date as YYYY-MM-DD. If no year is given,
   assume the current year. Use the current date in the format YYYY-MM-DD
   for the order date. Items should be in this list: [white loaf, rye loaf,
   baguette, bagel, croissant, chocolate muffin, blueberry muffin]. The
   content of the email follows this line. \n {email_content}"""

提示工程学

提示工程学是设计可以被生成AI和LLM理解并解读的提问或指令的实践。问题或指令的意图必须明确陈述，并提供足够的细节和背景信息，以便模型能够理解。使用AI模型时，通常需要多次迭代来完善提示，直到达到所需的结果。

存储过程主体中的下一行代码调用了 COMPLETE 函数，提供 snowflake-arctic 作为模型，并使用我们之前构建的提示：

csv_output = Complete('snowflake-arctic', prompt)

注意：在本示例中，我们使用 snowflake-arctic 作为LLM模型。你也可以使用其他模型，如 mistral-large、reka-flash、llama2-70b-chat 或 gemma-7b。有关当前支持的模型列表，请参阅文档：mng.bz/mR9M。如果需要，你可以尝试不同的模型并比较结果。

如果LLM按照我们的提示返回结果，它应该返回一个CSV输出，包含五个列：CUSTOMER、ORDER_DATE、DELIVERY_DATE、ITEM 和 QUANTITY。虽然我们没有指定这些列的数据类型，但我们要求模型以 YYYY-MM-DD 格式返回日期，这样我们可以将其存储为Snowflake表中的日期数据类型。

7.4.3 将CSV结果保存到表中

在将CSV数据存储到Snowflake表之前，我们必须先将其转换为数据框并提供结构。如第6章所述，我们可以使用Snowpark中的 StructType 构造来提供数据框的结构。以下代码创建一个名为 schema 的变量，保存CSV文件的架构定义：

schema = StructType([ 
    StructField("CUSTOMER", StringType(), False),  
    StructField("ORDER_DATE", DateType(), False),  
    StructField("DELIVERY_DATE", DateType(), False), 
    StructField("ITEM", StringType(), False),  
    StructField("QUANTITY", IntegerType(), False)
])

在将返回的CSV数据传递给 session.create_dataframe 方法之前，我们必须对其进行处理。由于 create_dataframe 方法接受一个列表的列表作为参数，因此我们需要通过换行符（\n）分割CSV数据，然后再通过逗号分割。我们还使用切片 [1:] 来去掉CSV数据中的表头行。

以下代码使用 schema 中的架构从存储在 csv_output 变量中的CSV数据创建一个名为 ORDERS_DF 的数据框：

orders_df = session.create_dataframe(
  [x.split(',') for x in csv_output.split("\n")][1:], schema)

最后，我们可以将数据框的内容保存到一个名为 COLLECTED_ORDERS_FROM_EMAIL 的Snowflake表中。我们使用 append 参数，表示数据将被附加到表中，而不是覆盖表。按照Snowpark的惯例，如果表不存在，则会创建表。

将数据从数据框保存到Snowflake表中的代码如下：

orders_df.write.mode("append").save_as_table('COLLECTED_ORDERS_FROM_EMAIL')

虽然不是必需的，但我们可以将数据框从存储过程中返回，以便在执行存储过程后，可以立即看到 COLLECTED_ORDERS_FROM_EMAIL 表中的结果，代码如下：

return orders_df

存储过程的完整代码可以在随附的GitHub存储库中找到，在 Chapter_07 文件夹下，名为 Chapter_07_Part3_read_emails.sql 的文件中。

执行完整代码后，它应该创建一个名为 READ_EMAIL_PROC 的存储过程。

7.4.4 评估输出

我们可以通过使用 CALL 命令调用存储过程并提供一个代表电子邮件正文的示例参数来测试存储过程并评估输出。以下是一个使用虚构电子邮件内容调用存储过程的示例：

call READ_EMAIL_PROC(
  'Hello, please deliver 6 loaves of white bread on Tuesday, September 5.
  On Wednesday, September 6, we need 16 bagels. Thanks, Lilys Coffee');

在使用上述命令调用存储过程后，输出应如下所示，见 Table 7.3。

Table 7.3 调用 READ_EMAIL_PROC 存储过程后返回来自示例客户邮件的结构化数据的输出

Customer	Order date	Delivery date	Item	Quantity
Lilys Coffee	2023-09-05	2023-09-05	White loaf	6
Lilys Coffee	2023-09-06	2023-09-06	Bagel	16

此外，我们可以通过以下命令从 COLLECTED_ORDERS_FROM_EMAIL 表中选择数据，以检查数据是否已正确填充到表中：

select * from COLLECTED_ORDERS_FROM_EMAIL;

此命令的输出应与 Table 7.3 中相同。LLM大部分信息处理正确，唯一的错误是它不知道当前的年份和日期。它似乎是在2023年进行训练的，因此认为2023年是当前年份。

以下是一些可以用来测试存储过程的更多示例电子邮件：

call READ_EMAIL_PROC (
  'Hi again. At Metro Fine Foods, we are renewing our order for 
  Thursday, September 7. We need 20 baguettes, 16 croissants, and 
  a dozen blueberry muffins. Have a nice day!');

call READ_EMAIL_PROC(
  'Greetings! We loved your French bread last week. Please deliver
  10 more tomorrow. Cheers from your friends at Page One Fast Food');

call READ_EMAIL_PROC (
  'Do you deliver pizza? If so, send two this afternoon. If not, 
  then some bagels should do. Best, Jimmys Diner');

执行这些命令后，COLLECTED_ORDERS_FROM_EMAIL 表应包含如下所示的数据，见 Table 7.4。

Table 7.4 COLLECTED_ORDERS_FROM_EMAIL 表中的示例数据

Customer	Order date	Delivery date	Item	Quantity
Lilys Coffee	2023-09-05	2023-09-05	White loaf	6
Lilys Coffee	2023-09-06	2023-09-06	Bagel	16
Metro Fine Foods	2023-09-07	2023-09-07	Baguette	20
Metro Fine Foods	2023-09-07	2023-09-07	Croissant	16
Metro Fine Foods	2023-09-07	2023-09-07	Blueberry muffin	12
Page One Fast Food	2022-07-19	2022-07-20	White loaf	10
Jimmys Diner	2022-01-01	2022-01-01	Bagel	2

如表7.4所示，并与示例邮件比较，可以看出LLM并不总是处理得完全正确。除了在前两个示例中，错误地使用了2023年作为当前年份外，在最后两个示例中，它将2022年视为当前年份。模型还不得不猜测“French bread”意味着什么，因为它不在面包店提供的烘焙产品列表中。在之后使用相同电子邮件内容和不同模型执行存储过程时，LLM有时猜测“French bread”是法棍面包，有时猜测是白面包。

正如之前提到的，并从表7.4的数据中可以明显看出，目前的LLM在处理这些任务时非常有帮助，例如从非结构化文本中提取结构化信息。然而，我们应该谨慎对待所有LLM的结果，并检查其准确性。随着生成性AI和LLM领域的快速发展，我们可以预期这些模型将更加易于访问，并且结果的准确性将得到提高。

Snowflake正在通过多种方式增加LLM功能。除了本章中描述的外部网络访问功能，Snowflake还宣布了许多新功能，例如容器服务、Cortex函数、SQL自然语言助手等。

总结

Snowflake的外部网络访问功能使数据工程师能够访问Snowflake之外的网络位置。他们可以通过Snowflake的存储过程或UDF调用API端点。

外部网络访问架构由一个指向外部网络位置的网络规则和一个包含令牌、密钥或密码的秘密组成。创建外部访问集成对象时，网络规则和秘密作为参数包含在内。这个对象会作为参数传递给Python函数或存储过程，以便函数或过程可以调用外部API。

在创建外部访问集成之前，必须知道网站的API端点URL和访问该网站所需的API密钥。具有足够权限的用户可以创建外部访问集成。

为了使用外部访问集成，数据工程师必须被授予 USAGE 权限和对秘密的 READ 权限。如果需要，Snowflake管理员可以监控并限制对外部位置的访问。

我们可以使用requests Python包从UDF的主体中调用API端点。我们必须在UDF定义的参数中指定此包。

API调用的响应通常以JSON对象的格式返回。如果有必要，我们必须解析并展平JSON结构，以便在SQL查询中使用响应。

我们可以使用正则表达式，如 REGEXP_REPLACE，从API调用结果中删除任何不可打印字符。

LLM（大规模语言模型）是一种人工智能算法，旨在生成文本内容。它使用深度学习技术，并通过庞大的数据集进行训练，以理解、总结、翻译或生成输出。

在调用LLM时，我们必须提供一个明确说明问题或指令意图的提示，并提供足够的细节和背景信息，以便模型理解。精炼提示可能需要多次迭代，直到达到预期结果。

当我们需要程序逻辑来从模型中检索结果并执行额外的操作（如转换结果并将其保存到Snowflake表中）时，可以创建一个存储过程来调用Snowflake Cortex LLM函数。

我们可以使用 snowflake-snowpark-python 包将API调用的结果转换为数据框。我们通过Snowpark中的 StructType 构造提供数据框的结构。然后，我们可以将数据框作为表保存到Snowflake中。

LLM并不总是完全按照我们的要求执行，也不总是能够正确处理所有内容。我们应谨慎对待所有LLM的结果，并仔细检查其准确性。