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

É possível usar a declaração SELECT INTO OUTFILE S3 para consultar dados de um cluster de banco de dados do Amazon Aurora MySQL e salvá-los em arquivos de texto armazenados em um bucket do Amazon S3. No Aurora MySQL, os arquivos são armazenados primeiramente no disco local e depois exportados para o S3. Depois que as exportações forem concluídas, os arquivos locais serão excluídos.

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

A declaração LOAD DATA FROM S3 pode usar os arquivos criados pela declaração SELECT INTO OUTFILE S3 para carregar dados em um cluster de banco de dados do Aurora. Para ter 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.

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.

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'

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 do Aurora MySQL como a origem da replicação e um banco de dados MySQL como o cliente de replicação, a declaração GRANT do perfil ou do privilégio fará com que essa replicação pare devido a 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:

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.

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.

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:

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;