Salvar dados a partir de um cluster de banco de dados do Amazon Aurora MySQL em arquivos de texto de um bucket do Amazon S3 - Amazon Aurora

Salvar dados a partir de um cluster de banco de dados do Amazon Aurora MySQL em arquivos de texto de um bucket do Amazon S3

Você pode usar a instrução SELECT INTO OUTFILE S3 para consultar dados de um cluster de banco de dados do Amazon Aurora MySQL e salvá-los diretamente em arquivos de texto armazenados em um bucket do Amazon S3. É possível usar essa funcionalidade para dispensar a necessidade de trazer os dados para o cliente primeiro e depois copiá-los do cliente para o Amazon S3. A instrução LOAD DATA FROM S3 pode usar os arquivos criados por essa instrução para carregar dados em um cluster de banco de dados Aurora. Para obter mais informações, consulte Carregar dados em um cluster de banco de dados do Amazon Aurora MySQL a partir de arquivos de texto em um bucket do Amazon S3.

É possível criptografar o bucket do Amazon S3 usando uma chave gerenciada pelo Amazon S3 (SSE-S3) ou AWS KMS key (SSE-KMS: Chave gerenciada pela AWS ou chave gerenciada pelo cliente).

Esse recurso não é compatível com clusters de banco de dados do Aurora Serverless v1. É compatível com clusters de banco de dados do Aurora Serverless v2.

nota

É possível salvar os dados de snapshot do cluster de banco de dados no Amazon S3 usando o AWS Management Console, a AWS CLI ou a API do Amazon RDS. Para ter mais informações, consulte Exportar dados de snapshot de cluster de banco de dados para o Amazon S3.

Concessão de acesso ao Amazon S3 para o Aurora MySQL

Antes de poder salvar dados em um bucket do Amazon S3, você deve primeiro dar permissão para o seu cluster de banco de dados Aurora MySQL acessar o Amazon S3.

Para conceder acesso ao Amazon S3 para o Aurora MySQL
  1. Crie uma política do AWS Identity and Access Management (IAM) que forneça as permissões de bucket e de objeto que permitem que o cluster de banco de dados Aurora MySQL acesse o Amazon S3. Para obter instruções, consulte Criar uma política do IAM para acessar recursos do Amazon S3.

    nota

    No Aurora MySQL versão 3.05 e superior, você pode criptografar objetos usando chaves AWS KMSgerenciadas pelo cliente. Para isso, inclua a permissão kms:GenerateDataKey na sua política do IAM. Para obter mais informações, consulte Criar uma política do IAM para acessar recursos do AWS KMS.

    Não é necessária essa permissão para criptografar objetos usando Chaves gerenciadas pela AWS ou chaves gerenciadas pelo Amazon S3 (SSE-S3).

  2. Crie um perfil do IAM e anexe a política do IAM criada em Criar uma política do IAM para acessar recursos do Amazon S3 ao novo perfil do IAM. Para obter instruções, consulte Criar uma função do IAM para permitir que o Amazon Aurora acesse produtos da AWS.

  3. No caso do Aurora MySQL versão 2, defina o parâmetro de cluster de banco de dados aurora_select_into_s3_role ou aws_default_s3_role como o nome do recurso da Amazon (ARN) do novo perfil do IAM. Se um perfil do IAM não for especificado para aurora_select_into_s3_role, o Aurora usará o perfil do IAM especificado em aws_default_s3_role.

    No caso do Aurora MySQL versão 3, use aws_default_s3_role.

    Se o cluster fizer parte de um banco de dados global Aurora, defina esse parâmetro para cada cluster do Aurora no banco de dados global.

    Para ter mais informações sobre parâmetros de cluster de banco de dados, consulte Parâmetros do cluster de banco de dados e da instância de bancos de dados Amazon Aurora.

  4. Para permitir que os usuários do banco de dados em um cluster de banco de dados Aurora MySQL acessem o Amazon S3, associe a função criada em Criar uma função do IAM para permitir que o Amazon Aurora acesse produtos da AWS ao cluster de banco de dados.

    Para um banco de dados global Aurora, associe a função a cada cluster do Aurora no banco de dados global.

    Para obter informações sobre como associar um perfil do IAM a um cluster de banco de dados, consulte Associar uma função do IAM a um cluster de banco de dados do Amazon Aurora MySQL.

  5. Configure o cluster de banco de dados Aurora MySQL para permitir conexões de saída com o Amazon S3. Para obter instruções, consulte Permitir a comunicação de rede do Amazon Aurora MySQL com outros produtos da AWS.

    Para um banco de dados global Aurora, habilite conexões de saída para cada cluster do Aurora no banco de dados global.

Conceder privilégios para salvar dados no Aurora MySQL

O usuário do banco de dados que emite a instrução SELECT INTO OUTFILE S3 deve ter uma função ou privilégio específico. No Aurora MySQL versão 3, é concedida a função AWS_SELECT_S3_ACCESS. No Aurora MySQL versão 2, é concedido o privilégio SELECT INTO S3. O usuário administrativo de um cluster de banco de dados recebe a devida função ou privilégio por padrão. É possível conceder o privilégio a outro usuário, utilizando uma das instruções a seguir.

Use a instrução a seguir para o Aurora MySQL versão 3:

GRANT AWS_SELECT_S3_ACCESS TO 'user'@'domain-or-ip-address'
dica

Ao utilizar a técnica de perfil no Aurora MySQL versão 3, você também pode ativar o perfil usando a instrução SET ROLE role_name ou SET ROLE ALL. Se não estiver familiarizado com o sistema de funções do MySQL 8.0, é possível saber mais em Modelo de privilégios baseados em funções. Para obter mais detalhes, consulte Usar perfis no Manual de referência do MySQL.

Isso se aplica somente à sessão ativa atual. Ao se reconectar, execute a instrução SET ROLE novamente para conceder privilégios. Para ter mais informações, consulte a instrução SET ROLE no Guia de referência do MySQL.

Você pode usar o parâmetro activate_all_roles_on_login de cluster de banco de dados para ativar automaticamente todos os perfis quando um usuário se conecta a uma instância de banco de dados. Quando esse parâmetro está definido, não é necessário chamar a instrução SET ROLE explicitamente para ativar um perfil. Para ter mais informações, consulte activate_all_roles_on_login no Guia de referência do MySQL.

Use a seguinte instrução para o Aurora MySQL versão 2:

GRANT SELECT INTO S3 ON *.* TO 'user'@'domain-or-ip-address'

A função AWS_SELECT_S3_ACCESS e o privilégio SELECT INTO S3 são específicos do Amazon Aurora MySQL e não estão disponíveis para bancos de dados do MySQL ou instâncias de banco de dados RDS para MySQL. Se você tiver configurado a replicação entre um cluster de banco de dados Aurora MySQL como o elemento primário de replicação e um banco de dados MySQL como o cliente de replicação, a instrução GRANT da função ou do privilégio fará com que essa replicação pare com um erro. Você pode ignorar o erro com segurança para retomar a replicação. Para ignorar o erro em uma instância de banco de dados RDS para MySQL, use o procedimento mysql_rds_skip_repl_error. Para ignorar o erro em um banco de dados MySQL externo, use a variável de sistema slave_skip_errors (Aurora MySQL versão 2) ou a variável de sistema replica_skip_errors (Aurora MySQL versão 3).

Especificação de um caminho para um bucket do Amazon S3

A sintaxe para especificar um caminho para armazenar os dados e os arquivos de manifesto em um bucket do Amazon S3 é semelhante à usada na instrução LOAD DATA FROM S3 PREFIX, conforme mostrado a seguir.

s3-region://bucket-name/file-prefix

O caminho inclui os seguintes valores:

  • region (opcional): a região da AWS que contém o bucket do Amazon S3 no qual salvar os dados. Este valor é opcional. Se você não especificar um valor para region, o Aurora salvará seus arquivos no Amazon S3, na mesma região que o seu cluster de banco de dados.

  • bucket-name: o nome do bucket do Amazon S3 no qual salvar os dados. Há suporte para prefixos do objeto que identificam um caminho de pasta virtual.

  • file-prefix: o prefixo de objeto do Amazon S3 que identifica os arquivos a serem salvos no Amazon S3.

Os arquivos de dados criados pela instrução SELECT INTO OUTFILE S3 usam o seguinte caminho, no qual 00000 representa um número inteiro de 5 dígitos com base em zeros.

s3-region://bucket-name/file-prefix.part_00000

Por exemplo, suponha que uma instrução SELECT INTO OUTFILE S3 especifique s3-us-west-2://bucket/prefix como o caminho no qual armazenar arquivos de dados e crie três arquivos de dados. O bucket do Amazon S3 especificado contém os seguintes arquivos de dados.

  • s3-us-west-2://bucket/prefix.part_00000

  • s3-us-west-2://bucket/prefix.part_00001

  • s3-us-west-2://bucket/prefix.part_00002

Criar um manifesto para listar arquivos de dados

Você pode usar a instrução SELECT INTO OUTFILE S3 com a opção MANIFEST ON para criar um arquivo manifesto no formato JSON que lista os arquivos de texto criados pela instrução. A instrução LOAD DATA FROM S3 pode usar o arquivo manifesto para carregar os arquivos de dados de volta para um cluster de banco de dados Aurora MySQL. Para ter mais informações sobre como usar um manifesto para carregar arquivos de dados do Amazon S3 em um cluster de banco de dados Aurora MySQL, consulte Usar um manifesto para especificar arquivos de dados para carregamento.

Os arquivos de dados incluídos no manifesto criado pela instrução SELECT INTO OUTFILE S3 são listados na ordem em que são criados pela instrução. Por exemplo, suponha que uma instrução SELECT INTO OUTFILE S3 tenha especificado s3-us-west-2://bucket/prefix como o caminho no qual armazenar arquivos de dados e criado três arquivos de dados e um arquivo manifesto. O bucket do Amazon S3 especificado contém um arquivo manifesto chamado s3-us-west-2://bucket/prefix.manifest, que contém as seguintes informações.

{ "entries": [ { "url":"s3-us-west-2://bucket/prefix.part_00000" }, { "url":"s3-us-west-2://bucket/prefix.part_00001" }, { "url":"s3-us-west-2://bucket/prefix.part_00002" } ] }

SELECT INTO OUTFILE S3

Você pode usar a instrução SELECT INTO OUTFILE S3 para consultar dados de um cluster de banco de dados e salvá-los diretamente em arquivos de texto delimitados armazenados em um bucket do Amazon S3.

Não há suporte para arquivos compactados. Os arquivos criptografados têm suporte a partir do Aurora MySQL 2.09.0.

Sintaxe

SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr [, select_expr ...] [FROM table_references [PARTITION partition_list] [WHERE where_condition] [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC], ...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] INTO OUTFILE S3 's3_uri' [CHARACTER SET charset_name] [export_options] [MANIFEST {ON | OFF}] [OVERWRITE {ON | OFF}] [ENCRYPTION {ON | OFF | SSE_S3 | SSE_KMS ['cmk_id']}] export_options: [FORMAT {CSV|TEXT} [HEADER]] [{FIELDS | COLUMNS} [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char'] ] [LINES [STARTING BY 'string'] [TERMINATED BY 'string'] ]

Parâmetros

A instrução SELECT INTO OUTFILE S3 usa os seguintes parâmetros obrigatórios e opcionais que são específicos do Aurora.

s3-uri

Especifica o URI de um prefixo do Amazon S3 a ser usado. Use a sintaxe descrita em Especificação de um caminho para um bucket do Amazon S3.

FORMAT {CSV|TEXT} [HEADER]

Opcionalmente, salva os dados em formato CSV.

A opção TEXT é o padrão e produz o formato de exportação MySQL existente.

A opção CSV produz valores de dados separados por vírgulas. O formato CSV segue a especificação em RFC-4180. Se você especificar a palavra-chave opcional HEADER, o arquivo de saída conterá uma linha de cabeçalho. Os rótulos na linha de cabeçalho correspondem aos nomes de coluna da instrução SELECT. É possível usar os arquivos CSV como modelos de dados de treinamento para uso com os serviços do AWS ML. Para ter mais informações sobre como usar dados exportados do Aurora com os produtos de ML da AWS, consulte Exportar dados ao Amazon S3 para treinamento de modelos do SageMaker (avançado).

MANIFEST {ON | OFF}

Indica se um arquivo de manifesto é criado no Amazon S3. O arquivo manifesto é um arquivo JavaScript Object Notation (JSON) que pode ser usado para carregar dados em um cluster de banco de dados Aurora com a instrução LOAD DATA FROM S3 MANIFEST. Para ter mais informações sobre o LOAD DATA FROM S3 MANIFEST, consulte Carregar dados em um cluster de banco de dados do Amazon Aurora MySQL a partir de arquivos de texto em um bucket do Amazon S3.

Se MANIFEST ON for especificado na consulta, o arquivo manifesto será criado no Amazon S3 depois que todos os arquivos de dados tiverem sido criados e carregados. O arquivo manifesto é criado usando o seguinte caminho:

s3-region://bucket-name/file-prefix.manifest

Para ter mais informações sobre o formato do conteúdo do arquivo manifesto, consulte Criar um manifesto para listar arquivos de dados.

OVERWRITE {ON | OFF}

Indica se os arquivos existentes no bucket especificado do Amazon S3 são substituídos. Se OVERWRITE ON for especificado, os arquivos existentes que corresponderem ao prefixo de arquivo no URI especificado em s3-uri são substituídos. Caso contrário, ocorrerá um erro.

CRIPTOGRAFIA {LIGADO | DESLIGADO | SSE_S3 | SSE_KMS ['cmk_id']}

Indica se deve usar criptografia do lado do servidor com chaves gerenciadas pelo Amazon S3 (SSE-S3) ou (SSE-S3) ou AWS KMS keys (SSE-KMS, incluindo Chaves gerenciadas pela AWS e chaves gerenciadas pelo cliente). As configurações SSE_S3 e SSE_KMS estão disponíveis no Aurora MySQL versão 3.05 e posteriores.

Você também pode usar a variável de sessão aurora_select_into_s3_encryption_default em vez da cláusula ENCRYPTION, conforme mostrado no exemplo a seguir. Use a cláusula SQL ou a variável de sessão, mas não ambas.

set session set session aurora_select_into_s3_encryption_default={ON | OFF | SSE_S3 | SSE_KMS};

As configurações SSE_S3 e SSE_KMS estão disponíveis no Aurora MySQL versão 3.05 e posteriores.

Quando você define aurora_select_into_s3_encryption_default com o seguinte valor:

  • OFF: a política de criptografia padrão do bucket do S3 é seguida. O valor padrão de aurora_select_into_s3_encryption_default é OFF.

  • ON ou SSE_S3: o objeto do S3 é criptografado usando chaves gerenciadas pelo Amazon S3 (SSE-S3).

  • SSE_KMS: o objeto do S3 é criptografado usando uma chave AWS KMS key.

    Nesse caso, você também inclui a variável de sessão aurora_s3_default_cmk_id, por exemplo:

    set session aurora_select_into_s3_encryption_default={SSE_KMS}; set session aurora_s3_default_cmk_id={NULL | 'cmk_id'};
    • Quando aurora_s3_default_cmk_id é NULL, o objeto S3 é criptografado usando uma chave Chave gerenciada pela AWS.

    • Quando aurora_s3_default_cmk_id é uma string cmk_id não vazia, o objeto S3 é criptografado usando uma chave gerenciada pelo cliente.

      O valor de cmk_id pode ser uma string vazia.

Quando você usa o comando SELECT INTO OUTFILE S3, o Aurora determina a criptografia da seguinte forma:

  • Se a cláusula ENCRYPTION estiver presente no comando SQL, o Aurora vai se basear somente no valor de ENCRYPTION e não usará uma variável de sessão.

  • Se a cláusula ENCRYPTION não estiver presente, o Aurora vai se basear no valor da variável de sessão.

Para obter mais informações, consulte Usar a criptografia no lado do servidor com chaves gerenciadas pelo Amazon S3 (SSE-S3) e Usar a criptografia no lado do servidor com as chaves do AWS KMS (SSE-KMS) no Guia do usuário do Amazon Simple Storage Service.

Você pode encontrar mais detalhes sobre outros parâmetros em Instrução SELECT e Instrução LOAD DATA, na documentação do MySQL.

Considerações

O número de arquivos gravados no bucket do Amazon S3 depende da quantidade de dados selecionados pela instrução SELECT INTO OUTFILE S3 e do limite de tamanho do arquivo do Aurora MySQL. O limite de tamanho de arquivo padrão é de 6 gigabytes (GB). Se os dados selecionados pela instrução forem inferiores ao limite do tamanho do arquivo, um único arquivo será criado; caso contrário, vários arquivos serão criados. Outras considerações para os arquivos criados por esta instrução incluem:

  • O Aurora MySQL garante que as linhas em arquivos de dados não sejam divididas em limites de arquivos. Para vários arquivos, o tamanho de cada arquivo de dados, exceto o último, geralmente está próximo ao limite do tamanho do arquivo. No entanto, ficar ocasionalmente abaixo do limite de tamanho do arquivo faz com que uma linha seja dividida em dois arquivos de dados. Nesse caso, o Aurora MySQL cria um arquivo de dados que mantém a linha intacta, mas pode ser maior que o limite do tamanho do arquivo.

  • Como cada instrução SELECT no Aurora MySQL é executada como uma transação atômica, uma instrução SELECT INTO OUTFILE S3 que seleciona um conjunto de dados grande pode ser executada por um certo tempo. Se a instrução falhar por qualquer motivo, talvez seja necessário reiniciar e emiti-la novamente. Entretanto, se a instrução falhar, os arquivos já carregados no Amazon S3 permanecerão no bucket do Amazon S3 especificado. Você pode usar outra instrução para fazer upload dos dados restantes em vez de começar de novo.

  • Se a quantidade de dados a serem selecionados for grande (mais de 25 GB), recomendamos que você use várias instruções SELECT INTO OUTFILE S3 para salvar os dados no Amazon S3. Cada instrução deve selecionar uma porção diferente dos dados a serem salvos e também especificar um file_prefix diferente no parâmetro s3-uri a ser usado ao salvar os arquivos de dados. Particionar os dados a serem selecionados com várias instruções facilita a recuperação de um erro em uma instrução. Se ocorrer um erro para uma instrução, apenas uma parte dos dados precisa ser selecionada novamente e carregada no Amazon S3. O uso de várias instruções também ajuda a evitar uma única transação de longa execução, o que pode melhorar a performance.

  • Se várias instruções SELECT INTO OUTFILE S3 que usam o mesmo file_prefix no parâmetro s3-uri forem executadas em paralelo para selecionar dados no Amazon S3, o comportamento será indefinido.

  • Metadados, como o esquema de tabela ou metadados de arquivos, não são carregados pelo Aurora MySQL no Amazon S3.

  • Em alguns casos, você pode executar novamente uma consulta SELECT INTO OUTFILE S3, de modo a se recuperar de uma falha. Nesses casos, você deve remover todos os arquivos de dados existentes no bucket do Amazon S3 com o mesmo prefixo de arquivo especificado em s3-uri ou incluir OVERWRITE ON na consulta SELECT INTO OUTFILE S3.

A instrução SELECT INTO OUTFILE S3 retorna um número de erro típico e uma resposta MySQL em caso de êxito ou falha. Se você não tiver acesso ao número de erro e à resposta MySQL, a maneira mais fácil de determinar a conclusão é especificando MANIFEST ON na instrução. O arquivo manifesto é o último arquivo escrito pela instrução. Em outras palavras, se você tiver um arquivo manifesto, a instrução foi concluída.

Atualmente, não há como monitorar diretamente o progresso da instrução SELECT INTO OUTFILE S3 durante a execução. No entanto, suponha que você esteja gravando uma grande quantidade de dados do Aurora MySQL no Amazon S3 usando essa instrução e conheça a quantidade de dados selecionados pela instrução. Nesse caso, você pode estimar o progresso monitorando a criação de arquivos de dados no Amazon S3.

Para fazer isso, você pode usar o fato de que um arquivo de dados é criado no bucket do Amazon S3 especificado para cerca de cada 6 GB de dados selecionados pela instrução. Divida o tamanho dos dados selecionados por 6 GB para obter o número estimado de arquivos de dados a serem criados. Depois disso, é possível estimar o progresso da instrução monitorando o número de arquivos carregados no Amazon S3 durante a execução da instrução.

Exemplos

A seguinte instrução seleciona todos os dados na tabela employees e os salva em um bucket do Amazon S3 que está em uma região diferente do cluster de banco de dados do Aurora MySQL. A instrução cria arquivos de dados nos quais cada campo é encerrado por um caractere de vírgula (,) e cada linha é encerrada por um caractere de nova linha (\n). A instrução retornará um erro se os arquivos que corresponderem ao prefixo do arquivo sample_employee_data existirem no bucket do Amazon S3 especificado.

SELECT * FROM employees INTO OUTFILE S3 's3-us-west-2://aurora-select-into-s3-pdx/sample_employee_data' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

A seguinte instrução seleciona todos os dados na tabela employees e os salva em um bucket do Amazon S3 que está na mesma região que o cluster de banco de dados do Aurora MySQL. A instrução cria arquivos de dados nos quais cada campo é encerrado por um caractere de vírgula (,) e cada linha é encerrada por um caractere de nova linha (\n), bem como um arquivo manifesto. A instrução retornará um erro se os arquivos que corresponderem ao prefixo do arquivo sample_employee_data existirem no bucket do Amazon S3 especificado.

SELECT * FROM employees INTO OUTFILE S3 's3://aurora-select-into-s3-pdx/sample_employee_data' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' MANIFEST ON;

A seguinte instrução seleciona todos os dados na tabela employees e os salva em um bucket do Amazon S3 que está em uma região diferente do cluster de banco de dados do Aurora. A instrução cria arquivos de dados nos quais cada campo é encerrado por um caractere de vírgula (,) e cada linha é encerrada por um caractere de nova linha (\n). A instrução substitui todos os arquivos existentes que correspondem ao prefixo do arquivo sample_employee_data no bucket do Amazon S3 especificado.

SELECT * FROM employees INTO OUTFILE S3 's3-us-west-2://aurora-select-into-s3-pdx/sample_employee_data' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' OVERWRITE ON;

A seguinte instrução seleciona todos os dados na tabela employees e os salva em um bucket do Amazon S3 que está na mesma região que o cluster de banco de dados do Aurora MySQL. A instrução cria arquivos de dados nos quais cada campo é encerrado por um caractere de vírgula (,) e cada linha é encerrada por um caractere de nova linha (\n), bem como um arquivo manifesto. A instrução substitui todos os arquivos existentes que correspondem ao prefixo do arquivo sample_employee_data no bucket do Amazon S3 especificado.

SELECT * FROM employees INTO OUTFILE S3 's3://aurora-select-into-s3-pdx/sample_employee_data' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' MANIFEST ON OVERWRITE ON;