使用 Terraform 执行亚马逊 Redshift SQL 查询

由 Sylvia Qi (AWS) 和 Aditya Ambati () 创作 AWS

代码存储库：terraform-execute-redshift-sql	环境：PoC 或试点	技术： DevOps；分析；大数据；数据湖
工作负载：开源	AWS服务：亚马逊 Redshift；AWSCLI

Summary

使用基础设施即代码 (IaC) 来部署和管理 Amazon Redshift 是一种普遍的做法。 DevOpsIaC 有助于部署和配置各种 Amazon Redshift 资源，例如集群、快照和参数组。但是，IaC 并未扩展到数据库资源的管理，例如表、架构、视图和存储过程。这些数据库元素通过SQL查询进行管理，不受 IaC 工具的直接支持。尽管有管理这些资源的解决方案和工具，但您可能不希望在技术堆栈中引入其他工具。

此模式概述了一种使用 Terraform 部署 Amazon Redshift 数据库资源（包括表、架构、视图和存储过程）的方法。该模式区分了两种类型的SQL查询：

不可重复查询 — 这些查询在最初的 Amazon Redshift 部署期间执行一次，以建立基本的数据库组件。
可重复查询-这些查询是不可变的，可以在不影响数据库的情况下重新运行。该解决方案使用 Terraform 来监视可重复查询中的变化并相应地应用它们。

有关更多详细信息，请参阅 “其他信息” 中的 “解决方案演练”。

先决条件和限制

先决条件

您必须激活 AWS 账户并在部署计算机上安装以下内容：

AWS Command Line Interface (AWS CLI)
AWS CLI 配置有 Amazon Redshift 读/写权限的个人资料
Terraform 版本 1.6.2 或更高版本
Python3
Boto3

限制

此解决方案支持单个 Amazon Redshift 数据库，因为 Terraform 只允许在创建集群期间创建一个数据库。
此模式不包括在应用可重复查询之前对其进行验证的测试。我们建议您采用此类测试以增强可靠性。
为了说明解决方案，此模式提供了一个使用本地 Terraform 状态redshift.tf文件的示例文件。但是，对于生产环境，我们强烈建议您使用带有锁定机制的远程状态文件，以增强稳定性和协作性。
有些 AWS 服务并非全部可用 AWS 区域。有关区域可用性，请参阅AWS 服务按地区划分。有关特定终端节点，请参阅服务终端节点和配额，然后选择服务的链接。

产品版本

该解决方案是在亚马逊 Redshift 补丁 179 上开发和测试的。

代码存储库

此模式的代码可在 GitHub amazon-redshift-sql-deploy-terraform 存储库中找到。

架构

下图说明了 Terraform 如何通过处理不可重复和可重复的查询来管理 Amazon Redshift 数据库资源。SQL

Terraform 使用查询管理 Amazon Redshift 数据库资源的流程。SQL

图中显示以下步骤：

Terraform 在最初的 Amazon Redshift SQL 集群部署期间会应用不可重复的查询。
开发者提交对可重复SQL查询的更改。
Terraform 监视可重复SQL查询中的变化。
Terraform 将可重复的SQL查询应用于亚马逊 Redshift 数据库。

这种模式提供的解决方案是基于适用于 Amazon Redshift 的 Terraform 模块构建的。Terraform 模块预配置 Amazon Redshift 集群和数据库。为了增强该模块，我们使用了terraform_data资源，这些资源调用自定义 Python 脚本来使用 Amaz ExecuteStatementAPIon Redshift 操作执行SQL查询。因此，该模块可以执行以下操作：

置备数据库后，使用SQL查询部署任意数量的数据库资源。
持续监控可重复SQL查询中的更改，并使用 Terraform 应用这些更改。

有关更多详细信息，请参阅 “其他信息” 中的 “解决方案演练”。

工具

AWS 服务

Amazon Redshift 是一项完全托管的 PB 级数据仓库服务。 AWS Cloud

其他工具

Terraform 是一款基础设施即代码 (IaC) 工具 HashiCorp ，可帮助您创建和管理云和本地资源。
Python 是一种通用编程语言，在这种模式中用于执行SQL查询。

最佳实践

操作说明

任务	描述	所需技能
克隆存储库。	要克隆包含用于配置 Amazon Redshift 集群的 Terraform 代码的 Git 存储库，请使用以下命令。 `git clone https://github.com/aws-samples/amazon-redshift-sql-deploy-terraform.git`	DevOps 工程师
更新 Terraform 变量。	要根据您的特定要求自定义 Amazon Redshift 集群部署，请更新文件中的以下`terraform.tfvars`参数。 region = "<AWS_REGION>" cluster_identifier = "<REDSHIFT_CLUSTER_IDENTIFIER>" node_type = "<REDSHIFT_NODE_TYPE>" number_of_nodes = "<REDSHIFT_NODE_COUNT>" database_name = "<REDSHIFT_DB_NAME>" subnet_ids = "<REDSHIFT_SUBNET_IDS>" vpc_security_group_ids = "<REDSHIFT_SECURITY_GROUP_IDS>" run_nonrepeatable_queries = true run_repeatable_queries = true sql_path_bootstrap = "<BOOTSTRAP_SQLS_PATH>" sql_path_nonrepeatable = "<NON-REPEATABLE_SQLS_PATH>" sql_path_repeatable = "<REPEATABLE_SQLS_PATH>" sql_path_finalize = "<FINALIZE_SQLS_PATH>" create_random_password = false master_username = "<REDSHIFT_MASTER_USERNAME>"	DevOps 工程师
使用 Terraform 部署资源。	要为部署过程做准备，请使用以下命令在克隆的存储库中初始化 Terraform。 `terraform init` 要预览 Terraform 将应用于基础架构的更改，请使用以下命令创建执行计划。 `terraform plan -var-file terraform.tfvars` 要配置 Amazon Redshift 集群和相关资源，请使用以下命令应用 Terraform 执行计划。 `terraform apply -var-file terraform.tfvars`	DevOps 工程师
（可选）执行其他SQL查询。	示例存储库提供了几个用于演示目的的SQL查询。要执行您自己的SQL查询，请将其添加到以下文件夹： `/bootstrap` `/nonrepeatable` `/repeatable` `/finalize`

任务描述所需技能

任务	描述	所需技能
监控SQL语句的部署。	您可以监控 Amazon Redshift 集群的SQL执行结果。有关显示执行失败和成功SQL执行的输出示例，请参阅其他信息中的示例SQL语句。	DBA， DevOps 工程师
清理资源。	要删除 Terraform 部署的所有资源，请运行以下命令。 `terraform destroy`	DevOps 工程师

监控SQL语句的部署。

您可以监控 Amazon Redshift 集群的SQL执行结果。有关显示执行失败和成功SQL执行的输出示例，请参阅其他信息中的示例SQL语句。

DBA， DevOps 工程师

清理资源。

要删除 Terraform 部署的所有资源，请运行以下命令。


terraform destroy

DevOps 工程师

任务	描述	所需技能
验证 Amazon Redshift 集群中的数据。	登录并打开亚马逊 Redshift 控制台。 AWS Management Console 在导航菜单上，选择集群。在列表中选择相关的集群名称。按照亚马逊 Redshift 文档中使用亚马逊 Redshift 查询编辑器 v2 查询数据库中的说明进行操作。	DBA, AWS DevOps

其他信息

解决方案演练

要使用该解决方案，您必须以特定的方式组织您的 Amazon Redshift SQL 查询。所有SQL查询都必须存储在.sql扩展名为的文件中。

在此模式提供的代码示例中，SQL查询按以下文件夹结构进行组织。您可以修改代码（sql-queries.tf和sql-queries.py），使其适用于适合您独特用例的任何结构。


/bootstrap
     |- Any # of files
     |- Any # of sub-folders
/nonrepeatable
     |- Any # of files
     |- Any # of sub-folders
/repeatable
     /udf
          |- Any # of files
          |- Any # of sub-folders
     /table
          |- Any # of files
          |- Any # of sub-folders
     /view
          |- Any # of files
          |- Any # of sub-folders
     /stored-procedure
          |- Any # of files
          |- Any # of sub-folders
/finalize
     |- Any # of files
     |- Any # of sub-folders

鉴于上述文件夹结构，在 Amazon Redshift 集群部署期间，Terraform 按以下顺序执行查询：

/bootstrap
/nonrepeatable
/repeatable
/finalize

该/repeatable文件夹包含四个子文件夹：/udf、/table/view、和。/stored-procedure这些子文件夹指示 Terraform 执行查询的顺序。SQL

执行SQL查询的 Python 脚本是sql-queries.py。首先，该脚本读取特定源目录的所有文件和子文件夹，例如sql_path_bootstrap参数。然后，该脚本通过调用 Amazon ExecuteStatementAPIRedshift 操作来执行查询。一个文件中可能有一个或多个SQL查询。以下代码片段显示了 Python 函数，该函数针对亚马逊 Redshift 集群执行存储在文件中的SQL语句。


def execute_sql_statement(filename, cluster_id, db_name, secret_arn, aws_region):
    """Execute SQL statements in a file"""
    redshift_client = boto3.client(
        'redshift-data', region_name=aws_region)
    contents = get_contents_from_file(filename),
    response = redshift_client.execute_statement(
        Sql=contents[0],
        ClusterIdentifier=cluster_id,
        Database=db_name,
        WithEvent=True,
        StatementName=filename,
        SecretArn=secret_arn
    )
    ...

Terraform 脚本sql-queries.tf创建用于调用该脚本的 terraform_data 资源。sql-queries.py四个文件夹中的每一个都有一个terraform_data资源：/bootstrap、/nonrepeatable/repeatable、和/finalize。以下代码片段显示了在/bootstrap文件夹中执行SQL查询的terraform_data资源。


locals {
  program               = "${path.module}/sql-queries.py"
  redshift_cluster_name = try(aws_redshift_cluster.this[0].id, null)
}

resource "terraform_data" "run_bootstrap_queries" {
  count      = var.create && var.run_nonrepeatable_queries && (var.sql_path_bootstrap != "") && (var.snapshot_identifier == null) ? 1 : 0
  depends_on = [aws_redshift_cluster.this[0]]

  provisioner "local-exec" {
    command = "python3 ${local.program} ${var.sql_path_bootstrap} ${local.redshift_cluster_name} ${var.database_name} ${var.redshift_secret_arn} ${local.aws_region}"
  }
}

您可以使用以下变量控制是否运行这些查询。如果您不想在sql_path_bootstrap、、或sql_path_finalize中运行查询 sql_path_nonrepeatablesql_path_repeatable，请将其值设置为""。


  run_nonrepeatable_queries = true
  run_repeatable_queries    = true
  sql_path_bootstrap        = "src/redshift/bootstrap"
  sql_path_nonrepeatable    = "src/redshift/nonrepeatable"
  sql_path_repeatable       = "src/redshift/repeatable"
  sql_path_finalize         = "src/redshift/finalize"

运行时terraform apply，无论脚本的结果如何，Terraform 都会考虑脚本完成后添加的terraform_data资源。如果某些SQL查询失败，而您想重新运行它们，则可以手动将该资源从 Terraform 状态中移除，然后重新运行。terraform apply例如，以下命令将该run_bootstrap_queries资源从 Terraform 状态中移除。

terraform state rm module.redshift.terraform_data.run_bootstrap_queries[0]

以下代码示例显示了run_repeatable_queries资源如何使用 s ha256 哈希值监视repeatable文件夹中的变化。如果更新了文件夹中的任何文件，Terraform 会将整个目录标记为更新。然后，Terraform 在下一次运行目录中的查询。terraform apply


resource "terraform_data" "run_repeatable_queries" {
  count      = var.create_redshift && var.run_repeatable_queries && (var.sql_path_repeatable != "") ? 1 : 0
  depends_on = [terraform_data.run_nonrepeatable_queries]

  # Continuously monitor and apply changes in the repeatable folder
  triggers_replace = {
    dir_sha256 = sha256(join("", [for f in fileset("${var.sql_path_repeatable}", "**") : filesha256("${var.sql_path_repeatable}/${f}")]))
  }

  provisioner "local-exec" {
    command = "python3 ${local.sql_queries} ${var.sql_path_repeatable} ${local.redshift_cluster_name} ${var.database_name} ${var.redshift_secret_arn}"
  }
}

要完善代码，您可以实现一种机制，以检测并仅对repeatable文件夹中已更新的文件应用更改，而不是不分青红皂白地将更改应用于所有文件。

SQL语句示例

以下输出显示了SQL执行失败以及错误消息。


module.redshift.terraform_data.run_nonrepeatable_queries[0] (local-exec): Executing: ["/bin/sh" "-c" "python3 modules/redshift/sql-queries.py src/redshift/nonrepeatable testcluster-1 db1 arn:aws:secretsmanager:us-east-1:XXXXXXXXXXXX:secret:/redshift/master_user/password-8RapGH us-east-1"]
module.redshift.terraform_data.run_nonrepeatable_queries[0] (local-exec): -------------------------------------------------------------------
module.redshift.terraform_data.run_nonrepeatable_queries[0] (local-exec): src/redshift/nonrepeatable/table/admin/admin.application_family.sql
module.redshift.terraform_data.run_nonrepeatable_queries[0] (local-exec): -------------------------------------------------------------------
module.redshift.terraform_data.run_nonrepeatable_queries[0] (local-exec): Status: FAILED
module.redshift.terraform_data.run_nonrepeatable_queries[0] (local-exec): SQL execution failed.
module.redshift.terraform_data.run_nonrepeatable_queries[0] (local-exec): Error message: ERROR: syntax error at or near ")"
module.redshift.terraform_data.run_nonrepeatable_queries[0] (local-exec):   Position: 244
module.redshift.terraform_data.run_nonrepeatable_queries[0]: Creation complete after 3s [id=ee50ba6c-11ae-5b64-7e2f-86fd8caa8b76]

以下输出显示成功SQL执行。


module.redshift.terraform_data.run_bootstrap_queries[0]: Provisioning with 'local-exec'...
module.redshift.terraform_data.run_bootstrap_queries[0] (local-exec): Executing: ["/bin/sh" "-c" "python3 modules/redshift/sql-queries.py src/redshift/bootstrap testcluster-1 db1 arn:aws:secretsmanager:us-east-1:XXXXXXXXXXXX:secret:/redshift/master_user/password-8RapGH us-east-1"]
module.redshift.terraform_data.run_bootstrap_queries[0] (local-exec): -------------------------------------------------------------------
module.redshift.terraform_data.run_bootstrap_queries[0] (local-exec): src/redshift/bootstrap/db.sql
module.redshift.terraform_data.run_bootstrap_queries[0] (local-exec): -------------------------------------------------------------------
module.redshift.terraform_data.run_bootstrap_queries[0] (local-exec): Status: FINISHED
module.redshift.terraform_data.run_bootstrap_queries[0] (local-exec): SQL execution successful.
module.redshift.terraform_data.run_bootstrap_queries[0]: Creation complete after 2s [id=d565ef6d-be86-8afd-8e90-111e5ea4a1be]

Javascript 在您的浏览器中被禁用或不可用。

要使用 Amazon Web Services 文档，必须启用 Javascript。请参阅浏览器的帮助页面以了解相关说明。

文档惯例

在多个AWS区域部署代码

将 AWS Backup 报告导出为CSV文件