Execução de consultas SQL no Amazon Redshift usando o Terraform

Sylvia Qi e Aditya Ambati, Amazon Web Services

Resumo

Usar a infraestrutura como código (IaC) para a implantação e o gerenciamento do Amazon Redshift é uma prática predominante na empresa. DevOps A IaC facilita a implantação e a configuração de diversos recursos do Amazon Redshift, como clusters, snapshots e grupos de parâmetros. No entanto, a IaC não abrange o gerenciamento de recursos de banco de dados, como tabelas, esquemas, visualizações e procedimentos armazenados. Esses elementos de banco de dados são gerenciados por meio de consultas SQL e não são diretamente compatíveis com as ferramentas de IaC. Embora existam soluções e ferramentas para gerenciar esses recursos, você pode preferir não adicionar ferramentas extras à sua pilha tecnológica.

Este padrão descreve uma metodologia que usa o Terraform para implantar recursos de banco de dados do Amazon Redshift, incluindo tabelas, esquemas, visualizações e procedimentos armazenados. O padrão faz a distinção entre dois tipos de consultas SQL:

Para obter mais detalhes, consulte Explicação passo a passo da solução em Informações adicionais.

Pré-requisitos e limitações

Pré-requisitos

Você deve ter um ativo Conta da AWS e instalar o seguinte em sua máquina de implantação:

Limitações

Versões do produto

Esta solução foi desenvolvida e testada no patch 179 do Amazon Redshift.

Repositório de código

O código desse padrão está disponível no repositório GitHub amazon-redshift-sql-deploy-terraform.

Arquitetura

O diagrama apresentado a seguir ilustra como o Terraform gerencia os recursos do banco de dados do Amazon Redshift ao lidar tanto com consultas SQL não repetíveis quanto com consultas SQL repetíveis.

O diagrama mostra as seguintes etapas:

A solução fornecida por este padrão é desenvolvida com base no módulo do Terraform para o Amazon Redshift. O módulo do Terraform provisiona um cluster e um banco de dados do Amazon Redshift. Para aprimorar o módulo, usamos terraform_data recursos, que invocam um script Python personalizado para executar consultas SQL usando a operação de API do Amazon Redshift. ExecuteStatement Como resultado, o módulo é capaz de realizar o seguinte:

Para obter mais detalhes, consulte Explicação passo a passo da solução em Informações adicionais.

Ferramentas

Serviços da AWS

Outras ferramentas

Práticas recomendadas

Épicos

git clone https://github.com/aws-samples/amazon-redshift-sql-deploy-terraform.git

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>"

terraform destroy

Recursos relacionados

AWS documentação

Outros recursos

Mais informações

Explicação passo a passo da solução

Para usar a solução, você deve organizar suas consultas SQL do Amazon Redshift de uma forma específica. Todas as consultas SQL devem ser armazenadas em arquivos com a extensão .sql.

No exemplo de código fornecido com este padrão, as consultas SQL estão organizadas na estrutura de pastas apresentada a seguir. É possível modificar o código (sql-queries.tf e sql-queries.py) para funcionar com qualquer estrutura que atenda ao seu caso de uso específico.

/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

Dada a estrutura de pastas apresentada anteriormente, durante a implantação do cluster do Amazon Redshift, o Terraform executa as consultas na seguinte ordem:

A pasta /repeatable contém quatro subpastas: /udf, /table, /view e /stored-procedure. Essas subpastas indicam a ordem em que o Terraform executa as consultas SQL.

O script em Python que executa as consultas SQL é sql-queries.py. Primeiro, o script realiza a leitura de todos os arquivos e subpastas de um diretório de origem específico, por exemplo, o parâmetro sql_path_bootstrap. Em seguida, o script executa as consultas chamando a operação da API do Amazon ExecuteStatementRedshift. É possível ter uma ou mais consultas SQL em um arquivo. O trecho de código apresentado a seguir mostra a função em Python que executa as instruções SQL armazenadas em um arquivo em um cluster do Amazon Redshift.

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
    )
    ...

O script do Terraform sql-queries.tf cria os recursos terraform_data que invocam o script sql-queries.py. Há um recurso terraform_data para cada uma das quatro pastas: /bootstrap, /nonrepeatable, /repeatable e /finalize. O trecho de código apresentado a seguir mostra o recurso terraform_data que executa as consultas SQL na pasta /bootstrap.

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}"
  }
}

É possível controlar se as consultas serão executadas usando as variáveis apresentadas a seguir. Caso não deseje executar as consultas em sql_path_bootstrap, sql_path_nonrepeatable, sql_path_repeatable ou sql_path_finalize, defina seus valores como "".

  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"

Quando você executa terraform apply, o Terraform considera o recurso terraform_data adicionado após a conclusão do script, independentemente dos resultados do script. Se algumas consultas SQL falharem e você desejar executá-las novamente, é possível remover manualmente o recurso do estado do Terraform e executar terraform apply novamente. Por exemplo, o seguinte comando remove o recurso run_bootstrap_queries do estado do Terraform.

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

O exemplo de código apresentado a seguir mostra como o recurso run_repeatable_queries monitora alterações na pasta repeatable usando sha256 hash. Se qualquer arquivo presente na pasta for atualizado, o Terraform marca todo o diretório para atualização. Em seguida, o Terraform executa novamente as consultas do diretório na próxima execução de 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}"
  }
}

Para aprimorar o código, você pode implementar um mecanismo para detectar e aplicar alterações apenas nos arquivos que foram atualizados na pasta repeatable, em vez de aplicá-las a todos os arquivos indiscriminadamente.

Instruções SQL de exemplo

A saída a seguir apresenta uma execução SQL com falha, juntamente com a mensagem de erro correspondente.

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]

A saída apresentada a seguir mostra uma execução SQL com êxito.

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]