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

Você pode usar a instrução LOAD DATA FROM S3 ou a LOAD XML FROM S3 para carregar dados de arquivos armazenados em um bucket do Amazon S3. No Aurora MySQL, os arquivos são armazenados primeiramente no disco local e depois importados para o banco de dados. Depois que as importações para o banco de dados forem concluídas, os arquivos locais serão excluídos.

Concessão de acesso ao Amazon S3 para o Aurora

Para poder carregar dados de um bucket do Amazon S3, você deve primeiro fornecer permissão a seu cluster de banco de dados Aurora MySQL para acessar o Amazon S3.

Conceder privilégios para carregar dados no Amazon Aurora MySQL

O usuário do banco de dados que emite a instrução LOAD DATA FROM S3 ou LOAD XML FROM S3 deve ter uma função ou um privilégio específico para emitir qualquer uma dessas instruções. No Aurora MySQL versão 3, é concedida a função AWS_LOAD_S3_ACCESS. No Aurora MySQL versão 2, é concedido o privilégio LOAD FROM 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_LOAD_S3_ACCESS TO 'user'@'domain-or-ip-address'

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

GRANT LOAD FROM S3 ON *.* TO 'user'@'domain-or-ip-address'

O perfil AWS_LOAD_S3_ACCESS e o privilégio LOAD FROM S3 são específicos do Amazon Aurora e não estão disponíveis para bancos de dados MySQL externos ou instâncias de banco de dados do RDS para MySQL. Se você tiver configurado a replicação entre um cluster de banco de dados do Aurora como a origem de 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 a 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 do 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).

Especificar o caminho (URI) para um bucket do Amazon S3

A sintaxe para especificar o caminho (URI) para arquivos armazenados em um bucket do Amazon S3 é mostrada a seguir.

s3-region://amzn-s3-demo-bucket/file-name-or-prefix

O caminho inclui os seguintes valores:

LOAD DATA FROM S3

Você pode usar a instrução LOAD DATA FROM S3 para carregar dados de qualquer formato de arquivo de texto com suporte pela instrução LOAD DATA INFILE, como dados de texto que são delimitados por vírgulas. Não há suporte para arquivos compactados.

Sintaxe

LOAD DATA [FROM] S3 [FILE | PREFIX | MANIFEST] 'S3-URI'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name,...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]

Parâmetros

A instrução LOAD DATA FROM S3 usa os seguintes parâmetros obrigatórios e opcionais. Você pode encontrar mais detalhes sobre alguns desses parâmetros em Instrução LOAD DATA, na documentação do MySQL.

Você não poderá usar a palavra-chave LOCAL da instrução LOAD DATA FROM S3 se estiver carregando dados de um bucket do Amazon S3.

Usar um manifesto para especificar arquivos de dados para carregamento

Você pode usar a instrução LOAD DATA FROM S3 com a palavra-chave MANIFEST para especificar um arquivo manifesto no formato JSON que lista os arquivos de texto a serem carregados em uma tabela no seu cluster de banco de dados.

O seguinte esquema JSON descreve o formato e o conteúdo de um arquivo manifesto.

{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "additionalProperties": false,
    "definitions": {},
    "id": "Aurora_LoadFromS3_Manifest",
    "properties": {
        "entries": {
            "additionalItems": false,
            "id": "/properties/entries",
            "items": {
                "additionalProperties": false,
                "id": "/properties/entries/items",
                "properties": {
                    "mandatory": {
                        "default": "false",
                        "id": "/properties/entries/items/properties/mandatory",
                        "type": "boolean"
                    },
                    "url": {
                        "id": "/properties/entries/items/properties/url",
                        "maxLength": 1024,
                        "minLength": 1,
                        "type": "string"
                    }
                },
                "required": [
                    "url"
                ],
                "type": "object"
            },
            "type": "array",
            "uniqueItems": true
        }
    },
    "required": [
        "entries"
    ],
    "type": "object"
}

Cada url no manifesto deve especificar um URL com o nome do bucket e o caminho de objeto completo para o arquivo, e não apenas um prefixo. Você pode usar um manifesto para carregar vários arquivos de buckets diferentes, diferentes regiões ou arquivos que não compartilham o mesmo prefixo. Se uma região não for especificada no URL, a região do cluster de banco de dados Aurora de destino será usada. O exemplo a seguir mostra um arquivo manifesto que carrega quatro arquivos de diferentes buckets.

{
  "entries": [
    {
      "url":"s3://aurora-bucket/2013-10-04-customerdata", 
      "mandatory":true
    },
    {
      "url":"s3-us-west-2://aurora-bucket-usw2/2013-10-05-customerdata",
      "mandatory":true
    },
    {
      "url":"s3://aurora-bucket/2013-10-04-customerdata", 
      "mandatory":false
    },
    {
      "url":"s3://aurora-bucket/2013-10-05-customerdata"
    }
  ]
}

O sinalizador opcional mandatory especifica se LOAD DATA FROM S3 deve retornar um erro se o arquivo não for localizado. O sinalizador mandatory assume como padrão false. Independentemente de como mandatory está definido, LOAD DATA FROM S3 será encerrado se nenhum arquivo for encontrado.

Arquivos manifestos podem ter qualquer extensão. O exemplo a seguir executa a instrução LOAD DATA FROM S3 com o manifesto do exemplo anterior, que se chama customer.manifest.

LOAD DATA FROM S3 MANIFEST 's3-us-west-2://aurora-bucket/customer.manifest'
    INTO TABLE CUSTOMER
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
    (ID, FIRSTNAME, LASTNAME, EMAIL);

Após a conclusão da instrução, uma entrada para cada arquivo carregado com êxito é gravada na tabela aurora_s3_load_history.

Verificar arquivos carregados usando a tabela aurora_s3_load_history

Cada instrução LOAD DATA FROM S3 bem-sucedida atualiza a tabela aurora_s3_load_history no esquema mysql com uma entrada para cada arquivo que foi carregado.

Depois de executar a instrução LOAD DATA FROM S3, você pode verificar quais arquivos foram carregados consultando a tabela aurora_s3_load_history. Para ver os arquivos que foram carregados de uma iteração da instrução, use a cláusula WHERE para filtrar os registros na URI do Amazon S3 para o arquivo manifesto usado na instrução. Se você usou o mesmo arquivo manifesto antes, filtre os resultados usando o campo timestamp.

select * from mysql.aurora_s3_load_history where load_prefix = 'S3_URI';

A tabela a seguir descreve os campos na tabela aurora_s3_load_history.

Exemplos

A seguinte instrução carrega dados de um bucket do Amazon S3 que está na mesma região que o cluster de banco de dados Aurora. A declaração lê os dados delimitados por vírgula no arquivo customerdata.txt que está no bucket amzn-s3-demo-bucket do Amazon S3 e, depois, carrega os dados na tabela store-schema.customer-table.

LOAD DATA FROM S3 's3://amzn-s3-demo-bucket/customerdata.csv' 
    INTO TABLE store-schema.customer-table
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
    (ID, FIRSTNAME, LASTNAME, ADDRESS, EMAIL, PHONE);

A seguinte instrução carrega dados de um bucket do Amazon S3 que está em uma região diferente do cluster de banco de dados Aurora. A declaração lê os dados delimitados por vírgula de todos os arquivos que correspondem ao prefixo do objeto employee-data no bucket amzn-s3-demo-bucket do Amazon S3 na região us-west-2 e, depois, carrega os dados na tabela employees.

LOAD DATA FROM S3 PREFIX 's3-us-west-2://amzn-s3-demo-bucket/employee_data'
    INTO TABLE employees
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
    (ID, FIRSTNAME, LASTNAME, EMAIL, SALARY);              

A seguinte instrução carrega dados dos arquivos especificados em um arquivo de manifesto JSON chamado q1_sales.json na tabela sales.

LOAD DATA FROM S3 MANIFEST 's3-us-west-2://amzn-s3-demo-bucket1/q1_sales.json'
    INTO TABLE sales
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
    (MONTH, STORE, GROSS, NET);

LOAD XML FROM S3

Você pode usar a instrução LOAD XML FROM S3 para carregar dados de arquivos XML armazenados em um bucket do Amazon S3 em um dos três formatos XML diferentes:

Sintaxe

LOAD XML FROM S3 'S3-URI'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name,...)]
    [CHARACTER SET charset_name]
    [ROWS IDENTIFIED BY '<element-name>']
    [IGNORE number {LINES | ROWS}]
    [(field_name_or_user_var,...)]
    [SET col_name = expr,...]

Parâmetros

A instrução LOAD XML FROM S3 usa os seguintes parâmetros obrigatórios e opcionais. Você pode encontrar mais detalhes sobre alguns desses parâmetros em Instrução LOAD XML, na documentação do MySQL.