增强元数据以提高 Amazon Athena 的文本到 SQL 生成准确性

作者 Naidu Rongali发表日期 2024年10月14日主题 Amazon Athena Amazon Bedrock AWS Glue 生成式 AI永久链接留言区

主要重点

抽取大数据集中的有价值见解对企业至关重要。Amazon Athena 为数据分析提供了互动式服务。在生成 SQL 查询时，元数据的准确性与完整性至关重要。生成式 AI 模型可以将自然语言问题转换为有效的 SQL 查询，但仍需依赖元数据。本文探讨如何通过 AWS Glue 和 Amazon Bedrock 解决元数据维护及增强的挑战。

提取大量数据集中的有价值见解对于那些寻求竞争优势的企业至关重要。企业数据被引入数据湖和数据仓库，以进行分析、报告和数据科学用例，这些都依赖于 AWS 的分析服务，如 Amazon Athena、Amazon Redshift、Amazon EMR，等等。Amazon Athena 提供一种交互式分析服务，以分析存储在 Amazon S3 中的数据。而 Amazon Redshift 则用于分析结构化和半结构化数据。 Amazon EMR 提供一个大数据环境，以使用 Apache Spark、Apache Hive 和 Presto 等开源框架进行数据处理、交互式分析和机器学习。

撰写 SQL 查询不仅需要记住 SQL 语法规则，还需要了解表的元数据，这是关于表架构、表之间关系和可能列值的数据。基于大型语言模型LLM的生成式 AI 是一项新技术趋势，旨在理解大规模信息并帮助处理复杂任务。这项技术能否帮助生成 SQL 查询呢？答案是肯定的。

生成式 AI 模型能够将自然语言问题转换为有效的 SQL 查询，这一能力称为文本到 SQL 生成。尽管 LLM 能够生成语法正确的 SQL 查询，但在撰写准确的 SQL 查询时仍需要表的元数据。在本文中，我们将通过一个对 Amazon Athena 使用 Amazon Bedrock 实施的示例，演示元数据在文本到 SQL 生成中的关键作用。我们将讨论维护元数据的挑战以及克服这些挑战以增强元数据的方法。

解决方案概述

此篇文章展示了用于 Amazon Athena 的文本到 SQL 生成示例，该示例是使用 Amazon Bedrock 实现的。本文使用 Anthropic 的 Claude 21 基础模型作为 LLM。Amazon Bedrock 模型通过 Amazon SageMaker 调用。工作示例旨在展示元数据中包含的各种详细信息如何影响模型生成的 SQL。这些示例使用在 AWS Glue 和 Amazon S3 中创建的合成数据集。当我们回顾这些元数据细节的重要性后，将深入探讨收集所需元数据的挑战，然后探讨克服这些挑战的策略。

以下图示显示了工作流程中实现的示例。

图 1 解决方案架构及工作流程

工作流程遵循以下步骤：

用户提出一个文本问题，该问题可以通过对相关的 AWS Glue 表进行查询来回答。从 AWS Glue 获取表的元数据。将表的元数据和 SQL 生成指令添加到提示模板中。通过传递提示和模型参数调用 Claude AI 模型。Claude AI 模型根据指令和表的元数据将用户意图问题转换为 SQL。执行生成的 Athena SQL 查询。将生成的 Athena SQL 查询及其结果返回给用户。

前提条件

如果您希望自己尝试此示例，请查阅以下前提条件。如果您希望了解该示例但不想实施它，可以跳过此前提条件部分。此示例主要涉及使用 SageMaker 调用 Amazon Bedrock 模型，因此需要在 AWS 帐户中设置一些资源。此部分涵盖了相关的 CloudFormation 模板、Jupyter Notebook 和启动必要 AWS 服务的详细信息。CloudFormation 模板创建了 SageMaker 实例，并赋予运行 AWS Glue 命令、Athena SQL 和调用 Amazon Bedrock AI 模型所需的 S3 桶和 IAM 角色权限。两个 Jupyter Notebook (0createtableswithmetadataipynb 和 1texttosqlforathenaipynb) 提供了生成必要表格及使用 Claude AI 模型在 Amazon Bedrock 上生成 SQL 的工作代码片段。

为 Anthropic 的 Claude 授权 Amazon Bedrock 权限

拥有一个 AWS 帐户，并使用 AWS 管理控制台登录。将 AWS 区域更改为 美国西部俄勒冈。导航至 AWS 服务目录 控制台，选择 Amazon Bedrock。在 Amazon Bedrock 控制台中，选择导航窗格中的 模型访问。选择 管理模型访问。选择 Claude。如果您是第一次请求模型访问，请选择 请求模型访问，否则选择 保存更改。

部署 CloudFormation 堆栈

在启动 CloudFormation 堆栈后：

在 创建堆栈 页面，选择 下一步。在 指定堆栈详情 页面，选择 下一步。在 配置堆栈选项 页面，选择 下一步。在 审查和创建页面，选中 我确认 AWS CloudFormation 可能创建 IAM 资源。选择 提交。

将 Jupyter Notebook 下载到 SageMaker

在 AWS 管理控制台中，选择当前显示的区域名称，并将其更改为 美国西部俄勒冈。导航至 AWS 服务目录控制台，选择 Amazon SageMaker。在 Amazon SageMaker 控制台中，选择导航窗格中的 Notebook。选择 Notebook 实例。选择由 texttosqlmetadata CloudFormation 堆栈创建的 SageMaker Notebook 实例。在 操作 下，选择 打开 Jupyter。在 Jupyter 控制台中，选择 新建，然后选择 控制台。

在控制台中运行以下 shell 脚本命令，以复制 Jupyter Notebook。

bashcd /home/ec2user/SageMakerBASES3PATH=s3//awsblogsartifactspublic/artifacts/BDB4265aws s3 cp {BASES3PATH}/0createtableswithmetadataipynb /aws s3 cp {BASES3PATH}/1texttosqlforathenaipynb /

打开每个下载的 Notebook 并根据 texttosqlmetadata CloudFormation 的输出更新 athenaresultsbucket、awsregion 和 athenaworkgroup 变量的值。

解决方案实施

如果您想自己试试这个示例，可以使用上面提供的 CloudFormation 模板。在随后的部分中，我们将展示元数据中每个元素如何影响模型生成的 SQL 查询。

0createtableswithmetadataipynb Jupyter Notebook 中的步骤为员工和部门数据集创建了带有合成数据的 Amazon S3 文件，创建了指向这些 S3 桶的 employeedtls 和 departmentdtls Glue 表，并提取了这两个表的以下元数据：

sqlCREATE EXTERNAL TABLE employeedtls ( id int COMMENT Employee id name string COMMENT Employee name age int COMMENT Employee age deptid int COMMENT Employee Departments ID empcategory string COMMENT Employee category Contains TEMP For temporary PERM for permanent CONTR for contractors locationid int COMMENT Location identifier of the Employee joiningdate date COMMENT Joining date of the Employee CONSTRAINT pk1 PRIMARY KEY (id) CONSTRAINT FK1 FOREIGN KEY (deptid) REFERENCES departmentdtls(id)) PARTITIONED BY ( regionid string COMMENT Region identifier Contains AMER for Americas EMEA for Europe the Middle East and Africa APAC for Asia Pacific countries)

加速器试用七天

CREATE EXTERNAL TABLE departmentdtls ( id int COMMENT Department id name string COMMENT Department name locationid int COMMENT Location identifier of the Department)

在上一步提取的元数据中提供了列描述。对于 regionid 分区列和 empcategory 列，描述提供了可用值及其含义。元数据还包含外键约束详情。AWS Glue 没有提供指定主键和外键约束的方法，因此在创建 AWS Glue 表时，使用自定义键作为替代方案来收集主键和外键。

python

定义表架构

employeetableinput = { Name employeetablename PartitionKeys [ {Name regionid Type string Comment Region identifier Contains AMER for Americas EMEA for Europe the Middle East and Africa APAC for Asia Pacific countries} ] StorageDescriptor { Columns [ {Name id Type int Comment Employee id} # 其他列 ] Location employees3path # 其他属性 } TableType EXTERNALTABLE Parameters { classification csv primarykey CONSTRAINT pk1 PRIMARY KEY (id) foreignkey1 CONSTRAINT FK1 FOREIGN KEY (deptid) REFERENCES departmentdtls(id) }}

创建表

response = glueclientcreatetable(DatabaseName=databasename TableInput=employeetableinput)

1texttosqlforathenaipynb Jupyter Notebook 中的步骤创建了以下包装函数，以与 Claude FM 在 Amazon Bedrock 上互动，以根据用户提供的文本生成 SQL。此函数硬编码了模型的参数和 ID，以演示基本功能。

pythondef interactWithClaude(prompt) body = jsondumps( { prompt prompt maxtokenstosample 2048 temperature 1 topk 250 topp 0999 stopsequences [] } ) modelId = anthropicclaudev2 accept = application/json contentType = application/json response = bedrockclientinvokemodel( body=body modelId=modelId accept=accept contentType=contentType ) responsebody = jsonloads(responseget(body)read()) responsetextclaude = responsebodyget(completion) return responsetextclaude

定义以下一组指令，用于生成 Athena SQL 查询。这些 SQL 生成指令指定 SQL 查询应该在哪个计算引擎上运行及其他指导模型生成 SQL 查询的指令。这些指令包含在发送给 Bedrock 模型的提示中。

pythonathenasqlgeneratinginstructions = Read database schema inside the ltdatabaseschemagtlt/databaseschemagt tags which contains a list of table names and their schemas to do the following 1 Create a syntactically correct AWS Athena query to answer the question 2 For tables with partitions include the filters on the relevant partition columns 3 Include only relevant columns for the given question 4 Use only the column names that are listed in the schema description 5 Qualify column names with the table name 6 Avoid joins to a table if there is no column required from the table 7 Convert Strings to Date type while filtering on Date type columns 8 Return the sql query inside the ltSQLgtlt/SQLgt tab

定义不同的提示模板，以展示元数据在文本到 SQL 生成中的重要性。这些模板具有 SQL 查询生成指令和表元数据的占位符。

pythonathenaprompt1 = Human You are an AWS Athena query expert whose output is a valid sql query You are given the following Instructions for building the AWS Athena query{instructiondtls}

Only use the following tables defined within the databaseschema and tableschema XMLstyle tags

CREATE EXTERNAL TABLE employeedtls ( id int name string age int deptid int empcategory string locationid int joiningdate date) PARTITIONED BY ( regionid string )

CREATE EXTERNAL TABLE departmentdtls ( id int name string locationid int )

Question {question}

Assistant

通过将问题和指令详情作为参数传递给提示模板来生成最终提示。然后，请调用模型。

pythonquestionasked = List of permanent employees who work in North America and joined after Jan 1 2024prompttemplateforquerygenerate = PromptTemplatefromtemplate(athenaprompt1)promptdataforquerygenerate = prompttemplateforquerygenerateformat(question=questionaskedinstructiondtls=athenasqlgeneratinginstructions)llmgeneratedresponse = interactWithClaude(promptdataforquerygenerate)print(llmgeneratedresponsereplace(ltsqlgt )replace(lt/sqlgt ) )

模型根据提供的提示中的指令和表详情生成用户问题的 SQL 查询。

sqlSELECT employeedtlsid employeedtlsname employeedtlsage employeedtlsdeptid employeedtlsempcategoryFROM employeedtls WHERE employeedtlsregionid = NA AND employeedtlsempcategory = permanent AND employeedtlsjoiningdate gt CAST(20240101 AS DATE)

文本到 SQL 生成中提示和元数据的重要性

理解表的细节及其包含的数据对于人类 SQL 专家和基于