Executar instruções SQL usando a API Data do Cloud SQL

MySQL   |  PostgreSQL   |  SQL Server

Nesta página, descrevemos como executar instruções SQL em bancos de dados em instâncias do Cloud SQL usando a API Data. Com a API Data, você usa a API Cloud SQL Admin e a CLI gcloud para executar instruções SQL em qualquer instância em que você tenha ativado o acesso à API Data.

É possível usar a API de dados com instâncias que usam endereços IP públicos, acesso a serviços particulares ou Private Service Connect. A API Data oferece suporte a todos os tipos de instruções SQL, incluindo linguagem de manipulação de dados (DML), linguagem de definição de dados (DDL) e linguagem de consulta de dados (DQL). A API Data é adequada para executar instruções administrativas pequenas e rápidas, como criar papéis ou usuários de banco de dados e fazer pequenas atualizações de esquema.

Antes de começar

Antes de executar instruções SQL em uma instância, siga estas etapas.

Configurar o usuário do banco de dados

A API Data precisa ser autenticada como um usuário do banco de dados para executar instruções SQL. É possível fazer a autenticação como um usuário integrado, um usuário do IAM, uma conta de serviço do IAM ou um grupo do IAM.

Para fazer a autenticação usando o IAM, siga estas etapas:

1. Configure a instância para IAM autenticação do banco de dados.
2. Adicione um IAM usuário, uma conta de serviço ou um grupo à instância.
3. Conceda à conta os papéis ou privilégios necessários para executar instruções SQL É possível atribuir papéis de banco de dados ao criar a conta ou atualizar a conta. Se você criou papéis de banco de dados personalizados com privilégios mínimos, atribua-os à conta. Caso contrário, atribua o papel predefinido cloudsqlsuperuser à conta, use a API Data para criar novos papéis de banco de dados personalizados com menos privilégios e conceda os novos papéis à conta em vez de cloudsqlsuperuser.

Para fazer a autenticação como um usuário integrado usando a senha, siga estas etapas:

1. Crie um usuário. Observação: a API Data não pode ser autenticada como o usuário padrão root.
2. Use o Secret Manager para criar um secret regional para armazenar a senha. Por segurança, a API de dados pede o nome do recurso do Secret em vez da senha na solicitação de API. O secret regional precisa ser armazenado na mesma região da instância do Cloud SQL. Um secret criado usando o endpoint global do Secret Manager não é compatível, mesmo que seja armazenado na mesma região.
Como prática recomendada,
3. defina condições opcionais do IAM para permitir que um usuário acesse um secret específico, mas não outros secrets no projeto.

Papéis ou permissões necessárias

Por padrão, contas de usuário ou de serviço com um dos seguintes papéis têm permissão para executar instruções SQL em uma instância do Cloud SQL (cloudsql.instances.executesql):

• Cloud SQL Admin (roles/cloudsql.admin)
• Cloud SQL Instance User (roles/cloudsql.instanceUser)
• Cloud SQL Studio User (roles/cloudsql.studioUser)

Também é possível definir um papel personalizado do IAM para a conta de usuário ou serviço que inclui a cloudsql.instances.executesql permissão. Essa permissão é suportada em papéis personalizados do IAM.

Ativar ou desativar a API Data

Para usar a API Data, é necessário ativá-la para cada instância. É possível desativar a API Data a qualquer momento.

Console

1. No Google Cloud console, acesse a página Instâncias do Cloud SQL.

   Acesse "Instâncias do Cloud SQL"

2. Para abrir a página Visão geral de uma instância, clique no nome da instância.
3. No menu de navegação SQL, selecione Conexões.
4. Clique na guia Rede.
5. Marque a caixa de seleção Permitir API Data.
6. Clique em Salvar.

gcloud

Para ativar o acesso à API Data em uma instância, use o gcloud sql instances patch comando com a --data-api-access=ALLOW_DATA_API flag:

gcloud sql instances patch INSTANCE_NAME --data-api-access=ALLOW_DATA_API

Para desativar o acesso à API Data, use a flag --data-api-access=DISALLOW_DATA_API:

gcloud sql instances patch INSTANCE_NAME --data-api-access=DISALLOW_DATA_API

Substitua INSTANCE_NAME pelo nome da instância em que a API Data será ativada ou desativada.

Executar uma instrução SQL

É possível executar instruções SQL em bancos de dados na instância do Cloud SQL usando a CLI gcloud ou a API REST.

gcloud

Para executar uma instrução SQL em um banco de dados em uma instância usando a CLI gcloud, use o comando gcloud sql instances execute-sql.

Para se conectar usando o IAM:

gcloud sql instances execute-sql INSTANCE_NAME \
--database=DATABASE_NAME \
--sql=SQL_STATEMENT \
--partial-result-mode=PARTIAL_RESULT_MODE

Faça as seguintes substituições:

• INSTANCE_NAME: o nome da instância.
• DATABASE_NAME: o nome do banco de dados na instância.
• SQL_STATEMENT: a instrução SQL a ser executada. Se a instrução contiver espaços ou caracteres especiais do shell, ela precisará estar entre aspas.
• PARTIAL_RESULT_MODE: opcional. Controla como responder quando o resultado está incompleto. Pode ser ALLOW_PARTIAL_RESULT, FAIL_PARTIAL_RESULT ou PARTIAL_RESULT_MODE_UNSPECIFIED. Consulte Modificar o comportamento de truncamento.

Também é possível incluir a flag --project=PROJECT_ID, se necessário.

Para se conectar como um usuário integrado usando a senha:

gcloud sql instances execute-sql INSTANCE_NAME \
--database=DATABASE_NAME \
--sql=SQL_STATEMENT \
--user=USER \
--password-secret-version=PASSWORD_SECRET_VERSION \
--partial-result-mode=PARTIAL_RESULT_MODE

Faça as seguintes substituições:

• USER: o usuário do banco de dados a ser autenticado. Omita o @ e o nome do host.
• PASSWORD_SECRET_VERSION: o nome do recurso do secret do Secret Manager que contém a senha do usuário do banco de dados. O secret precisa ser a regional e armazenado na mesma região da instância do Cloud SQL. O formato esperado do nome do recurso é projects/{project}/locations/{location}/secrets/{secret}/versions/{secret_version}.

Terraform

É possível usar a API Data no Terraform para provisionar recursos no banco de dados, como bancos de dados, tabelas, extensões, usuários e concessões de privilégios, sem se conectar manualmente à instância. Para executar um script SQL no Terraform, use o google_sql_provision_script recurso do Terraform.

resource "google_sql_database_instance" "instance" {
  name             = "my-instance"
  database_version = "MYSQL_8_4"

  settings {
    tier            = "db-perf-optimized-N-2"
    data_api_access = "ALLOW_DATA_API"  # This allows the use of Data API.
    database_flags {
      name  = "cloudsql_iam_authentication"
      value = "on"
    }
  }
}

/*
 * Create a database user for your account and grant roles so it has privilege
 * to access the database. Set the type to CLOUD_IAM_USER for huamn
 * account or CLOUD_IAM_SERVICE_ACCOUNT for service account.
 */
resource "google_sql_user" "iam_user" {
  name     = "[e-mail]"
  instance = google_sql_database_instance.instance.name
  type     = "CLOUD_IAM_USER"

  # Roles granted to the user. To follow the principle of least privilege, you
  can first use `google_sql_provision_script` to create custom database role(s)
  with lesser privileges and then assign them to this user in place of
  `cloudsqlsuperuser`.
  # This field doesn't support MySQL 5.6 and 5.7.
  database_roles = ["cloudsqlsuperuser"]
}

resource "google_sql_provision_script" "script" {
  # You can inline the script or import from a file like script  = file("${path.module}/script.sql")
  # When modified, the whole script will be executed again. It's recommended to
  # make the script idempotent with patterns like create if not exists ... or
  # if not exists (select ...) then ... end if.
  script = "CREATE DATABASE pets;"
  instance = google_sql_database_instance.instance.name

  # Some of your queries may require a database. You can create and use a
  # database in the script or explicitly create and reference a database
  # like database = google_sql_database.database.name.

  description = "sql script to create DBs"

  # The identity account used to apply your Terraform config must exist as an
  # IAM user or IAM service account in the instance. Terraform connects to the
  # instance via IAM database authentication to execute the script.
  depends_on = [google_sql_user.iam_user]
}

Aplique as alterações

Para aplicar a configuração do Terraform em um Google Cloud projeto, siga as etapas nas seções a seguir.

Preparar o Cloud Shell

1. Inicie o Cloud Shell.

2. Defina o Google Cloud projeto em que você quer aplicar as configurações do Terraform.

   Você só precisa executar esse comando uma vez por projeto, e ele pode ser executado em qualquer diretório.

   export GOOGLE_CLOUD_PROJECT=PROJECT_ID

   As variáveis de ambiente serão substituídas se você definir valores explícitos no arquivo de configuração do Terraform.

Preparar o diretório

Cada arquivo de configuração do Terraform precisa ter o próprio diretório, também chamado de módulo raiz.

1. No Cloud Shell, crie um diretório e um novo arquivo dentro dele. O nome do arquivo precisa ter a extensão .tf, por exemplo, main.tf. Neste tutorial, o arquivo é chamado de main.tf.

   mkdir DIRECTORY && cd DIRECTORY && touch main.tf

2. Se você estiver seguindo um tutorial, poderá copiar o exemplo de código em cada seção ou etapa.

   Copie o exemplo de código no main.tf recém-criado.

   Se preferir, copie o código do GitHub. Isso é recomendado quando o snippet do Terraform faz parte de uma solução de ponta a ponta.

3. Revise e modifique os parâmetros de amostra para aplicar ao seu ambiente.
4. Salve as alterações.
5. Inicialize o Terraform. Você só precisa fazer isso uma vez por diretório.

   terraform init

   Opcionalmente, para usar a versão mais recente do provedor do Google, inclua a opção -upgrade:

   terraform init -upgrade

Aplique as alterações

1. Revise a configuração e verifique se os recursos que o Terraform vai criar ou atualizar correspondem às suas expectativas:

   terraform plan

   Faça as correções necessárias na configuração.

2. Para aplicar a configuração do Terraform, execute o comando a seguir e digite yes no prompt:

   terraform apply

   Aguarde até que o Terraform exiba a mensagem "Apply complete!".

3. Abra seu Google Cloud projeto para ver os resultados. No Google Cloud console, navegue até seus recursos na UI para verificar se foram criados ou atualizados pelo Terraform.

Observação: as amostras do Terraform geralmente presumem que as APIs necessárias estejam ativadas no seu Google Cloud projeto.

Excluir as alterações

A exclusão de um recurso google_sql_provision_script não exclui os recursos no banco de dados que ele criou. Para excluí-los, adicione instruções explicitamente no script, como drop ... if exists, e aplique as alterações.

REST

Para executar uma instrução SQL em um banco de dados em uma instância usando a API REST, envie uma solicitação POST para o endpoint executeSql:

POST https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_NAME/executeSql

O corpo da solicitação precisa conter o nome do banco de dados e a instrução SQL:

{
  "database": "DATABASE_NAME",
  "sqlStatement": "SQL_STATEMENT",
  "partialResultMode": "PARTIAL_RESULT_MODE"
  "autoIamAuthn": true
}

Faça as seguintes substituições:

• PROJECT_ID: o ID do projeto.
• INSTANCE_NAME: o nome da instância.
• DATABASE_NAME: o nome do banco de dados na instância.
• SQL_STATEMENT: a instrução SQL a ser executada.
• PARTIAL_RESULT_MODE: opcional. Controla como a API responde quando o resultado excede 10 MB. Pode ser FAIL_PARTIAL_RESULT, ALLOW_PARTIAL_RESULT ou PARTIAL_RESULT_MODE_UNSPECIFIED. Consulte Modificar o comportamento de truncamento.

Modificar o comportamento de truncamento

É possível controlar como os resultados grandes são processados ao executar o SQL, incluindo o "partialResultMode" campo na solicitação. Esse campo aceita os seguintes valores:

• FAIL_PARTIAL_RESULT: padrão. Gera um erro se o resultado exceder 10 MB ou se apenas um resultado parcial puder ser recuperado. Não retorna o resultado.
• ALLOW_PARTIAL_RESULT: retorna um resultado truncado e define partial_result como verdadeiro se o resultado exceder 10 MB ou se apenas um resultado parcial puder ser recuperado devido a um erro. Não gera um erro.
• PARTIAL_RESULT_MODE_UNSPECIFIED: modo não especificado, efetivamente o mesmo que FAIL_PARTIAL_RESULT.

Limitações

• O limite de tamanho para uma resposta é de 10 MB. Os resultados que excedem esse tamanho são truncados se partialResultMode estiver definido como ALLOW_PARTIAL_RESULT. Caso contrário, um erro será gerado.
• As solicitações são limitadas a 0,5 MB.
• Só é possível executar instruções SQL para instâncias do Cloud SQL para MySQL em execução.
• O Cloud SQL não oferece suporte ao uso da API Data com instâncias configuradas para replicação de servidor externo.
• As solicitações que levam mais de 30 segundos são canceladas. Não é possível definir um tempo limite de instrução maior usando SET SESSION MAX_EXECUTION_TIME isn't supported. No Cloud SQL para MySQL 5.6 e 5.7, o tempo limite de instruções DDL de longa execução pode causar tabelas ou arquivos órfãos que não podem ser revertidos com segurança. Tenha cuidado com instruções como ALTER TABLE em tabelas grandes.
• O Cloud SQL limita o número de solicitações executeSql simultâneas a 10 por instância para cada usuário. Se esse limite for atingido, as solicitações subsequentes vão falhar com "No máximo 10 consultas simultâneas podem ser executadas nesta instância. Tente novamente mais tarde." ou "O número máximo de leituras simultâneas (10) foi atingido".
• Cada resposta pode conter no máximo 10 mensagens ou avisos de banco de dados.
• Se houver um erro de sintaxe ou execução da instrução, nenhum resultado será retornado.
• Para o Cloud SQL para MySQL, os avisos e alertas estão disponíveis apenas para a última instrução de uma execução de várias instruções.
• Instruções que consomem uma grande quantidade de memória podem causar erros de falta de memória. Para mais informações sobre como evitar esses erros, consulte Práticas recomendadas para gerenciar o uso da memória. Uma instância de banco de dados em execução com alta utilização de memória geralmente causa problemas de desempenho, interrupções ou até mesmo inatividade no banco de dados.
• A API Data pode ser bloqueada temporariamente para fins de integridade de dados quando determinadas operações de manutenção estão em andamento na instância. Tente novamente mais tarde se isso acontecer.
• O script SQL e a resposta de execução podem transitar por locais intermediários entre o cliente e o local da instância de destino. Por esse motivo, as solicitações vão falhar com o erro "indisponível para instâncias em determinadas pastas de pacotes de controle do Assured Workloads" para determinados projetos do Assured Workloads e para projetos com constraints/sql.restrictNoncompliantResourceCreation aplicados manualmente.