Concessão de acesso ao Amazon S3 para o Aurora Conceder privilégios para carregar dados Especificar o caminho (URI) para um bucket do Amazon S3 LOAD DATA FROM S3 LOAD XML FROM S3

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.

nota

O carregamento de dados de arquivos de texto em uma tabela não é compatível com o Aurora Serverless v1. É compatível com o Aurora Serverless v2.

Sumário

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.

Para conceder acesso ao Amazon S3 para o Aurora MySQL

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 carregar objetos criptografados usando a chave AWS KMS keys gerenciada pelo cliente. Para isso, inclua a permissão kms:Decrypt na sua política do IAM. Para ter mais informações, consulte Criar uma política do IAM para acessar recursos do AWS KMS.
Você não precisa dessa permissão para carregar objetos que são criptografados com a chave Chaves gerenciadas pela AWS ou chaves gerenciadas pelo Amazon S3 (SSE-S3).
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.
Certifique-se de que o cluster de banco de dados esteja usando um grupo de parâmetros de cluster de banco de dados personalizado.

Para ter mais informações sobre como criar um grupo de parâmetros de cluster de banco de dados personalizado, consulte Criar um grupo de parâmetros do cluster de banco de dados no Amazon Aurora.
No caso do Aurora MySQL versão 2, defina o parâmetro de cluster de banco de dados aurora_load_from_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_load_from_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. Embora somente o cluster primário em um banco de dados global Aurora possa carregar dados, outro cluster pode ser promovido pelo mecanismo de failover e se tornar o cluster primário.

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.
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.
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 com outros serviços da AWS.

Se o cluster de banco de dados do não for publicamente acessível e não estiver em uma sub-rede pública de VPC, então ele é privado. Você pode criar um endpoint de gateway do S3 para acessar o bucket do S3. Para obter mais informações, consulte Endpoints de gateway para o Amazon S3.

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

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 ver mais detalhes, consulte Using roles no MySQL Reference Manual.

Isso se aplica somente à sessão ativa atual. Ao se reconectar, execute a declaraçã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 declaraçã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.

No entanto, é necessário chamar SET ROLE ALL explicitamente no início de um procedimento armazenado para ativar o perfil, quando o procedimento armazenado é chamado por um usuário diferente.

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

nota

O usuário do banco de dados deve ter privilégios INSERT para o banco de dados no qual está carregando dados.

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:

region (opcional): a região da AWS que contém o bucket do Amazon S3 do qual carregar. Este valor é opcional. Se você não especificar um valor para region, o Aurora carregará o arquivo do Amazon S3 na mesma região que o seu cluster de banco de dados.
bucket-name: o nome do bucket do Amazon S3 que contém os dados a serem carregados. Há suporte para prefixos do objeto que identificam um caminho de pasta virtual.
file-name-or-prefix: o nome do arquivo de texto ou arquivo XML do Amazon S3, ou um prefixo que identifica um ou mais arquivos de texto ou XML para carregar. Você também pode especificar um arquivo manifesto que identifique um ou mais arquivos de texto para carregar. Para ter mais informações sobre como usar um arquivo manifesto para carregar arquivos de texto a partir do Amazon S3, consulte Usar um manifesto para especificar arquivos de dados para carregamento.

Como copiar o URI para arquivos em um bucket do S3

Faça login no AWS Management Console e abra o console do Amazon S3 em https://console.aws.amazon.com/s3/.
No painel de navegação, escolha Buckets e selecione o bucket cujo URI você deseja copiar.
Selecione o prefixo ou arquivo que deseja carregar do S3.
Escolha Copiar URI do S3.

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.

nota

Verifique se o cluster de banco de dados do Aurora MySQL permite conexões de saída com o S3. Para ter mais informações, consulte Permitir a comunicação de rede do Amazon Aurora com outros serviços da AWS.

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,...]

nota

No Aurora MySQL versão 3.05 e superior, a palavra-chave FROM é opcional.

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.

FILE | PREFIX | MANIFEST

Identifica se os dados devem ser carregados de um único arquivo, de todos os arquivos que correspondem a determinado prefixo ou de todos os arquivos em um manifesto especificado. FILE é o padrão.

S3-URI

Especifica o URI de um arquivo de texto ou manifesto a ser carregado, ou um prefixo do Amazon S3 a ser usado. Especifique o URI usando a sintaxe descrita em Especificar o caminho (URI) para um bucket do Amazon S3.

REPLACE | IGNORE

Determina a ação a ser tomada se uma linha de entrada tiver os mesmos valores de chave exclusivos que os de uma linha existente na tabela de banco de dados.

Especifique REPLACE se quiser que a linha de entrada substitua a linha existente na tabela.
Especifique IGNORE se quiser descartar a linha de entrada.

INTO TABLE

Identifica o nome da tabela do banco de dados na qual carregar as linhas de entrada.

PARTITION

Requer que todas as linhas de entrada sejam inseridas nas partições identificadas pela lista especificada de nomes de partição separados por vírgulas. Se uma linha de entrada não puder ser inserida em uma das partições especificadas, a instrução falhará e um erro será retornado.

CHARACTER SET

Identifica o conjunto de caracteres dos dados no arquivo de entrada.

FIELDS | COLUMNS

Identifica como os campos ou colunas no arquivo de entrada são delimitados. Os campos são delimitados por tabulação por padrão.

LINES

Identifica como as linhas no arquivo de entrada são delimitadas. As linhas são delimitadas por um caractere de nova linha ('\n') por padrão.

IGNORE número LINES | ROWS

Especifica que determinado número de linhas no início do arquivo de entrada devem ser ignoradas. Por exemplo, você pode usar IGNORE 1 LINES para ignorar uma linha de cabeçalho inicial contendo nomes de coluna ou IGNORE 2 ROWS para ignorar as duas primeiras linhas de dados no arquivo de entrada. Se você também usar PREFIX, IGNORE ignorará determinado número de linhas no início do primeiro arquivo de entrada.

col_name_or_user_var, ...

Especifica uma lista separada por vírgulas de um ou mais nomes de colunas ou variáveis de usuário que identificam quais colunas devem ser carregadas, por nome. O nome de uma variável de usuário usada para esse fim deve corresponder ao nome de um elemento do arquivo de texto, com o prefixo @. Você pode aplicar variáveis de usuário para armazenar valores de campos correspondentes para reutilização.

Por exemplo, a seguinte instrução carrega a primeira coluna do arquivo de entrada na primeira coluna de table1 e define o valor da coluna table_column2 em table1 como o valor da entrada da segunda coluna dividida por 100.


LOAD DATA FROM S3 's3://amzn-s3-demo-bucket/data.txt'
    INTO TABLE table1
    (column1, @var1)
    SET table_column2 = @var1/100;

SET

Especifica uma lista de operações de atribuição separadas por vírgulas que define os valores de colunas na tabela como valores não incluídos no arquivo de entrada.

Por exemplo, a seguinte instrução define as duas primeiras colunas de table1 como os valores nas duas primeiras colunas do arquivo de entrada e, em seguida, define o valor do column3 em table1 como o carimbo de data/hora atual.


LOAD DATA FROM S3  's3://amzn-s3-demo-bucket/data.txt'
    INTO TABLE table1
    (column1, column2)
    SET column3 = CURRENT_TIMESTAMP;

Você pode usar subconsultas no lado direito das atribuições de SET. Para uma subconsulta que retorna um valor a ser atribuído a uma coluna, você pode usar apenas uma subconsulta escalar. Além disso, você não pode usar uma subconsulta para selecionar a tabela que está sendo carregada.

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.

Campo	Descrição
`load_prefix`	O URI que foi especificado na instrução de carregamento. Este URI pode ser mapeado para: Um arquivo de dados único para uma instrução `LOAD DATA FROM S3 FILE` Um prefixo do Amazon S3 que mapeia para vários arquivos de dados para uma instrução `LOAD DATA FROM S3 PREFIX` Um arquivo manifesto único que contém os nomes de arquivos a serem carregados para uma instrução `LOAD DATA FROM S3 MANIFEST`
`file_name`	O nome de um arquivo que foi carregado no Aurora a partir do Amazon S3 usando a URI identificada no campo `load_prefix`.
`version_number`	O número da versão do arquivo identificado pelo campo `file_name` que foi carregado, se o bucket do Amazon S3 tiver um número de versão.
`bytes_loaded`	O tamanho do arquivo carregado, em bytes.
`load_timestamp`	O carimbo de data/hora de quando a instrução `LOAD DATA FROM S3` foi concluída.

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:

Nomes de coluna como atributos de um elemento <row>. O valor do atributo identifica o conteúdo do campo da tabela.
```
<row column1="value1" column2="value2" .../>
```
Nomes de colunas como elementos filho de um elemento <row>. O valor do elemento filho identifica o conteúdo do campo da tabela.
```
<row>
  <column1>value1</column1>
  <column2>value2</column2>
</row>
```
Nomes de colunas no atributo name de elementos <field> em um elemento <row>. O valor do elemento <field> identifica o conteúdo do campo da tabela.
```
<row>
  <field name='column1'>value1</field>
  <field name='column2'>value2</field>
</row>
```

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.

FILE | PREFIX

Identifica se os dados devem ser carregados de um único arquivo ou de todos os arquivos que correspondam ao prefixo especificado. FILE é o padrão.

REPLACE | IGNORE

Determina a ação a ser tomada se uma linha de entrada tiver os mesmos valores de chave exclusivos que os de uma linha existente na tabela de banco de dados.

Especifique REPLACE se quiser que a linha de entrada substitua a linha existente na tabela.
Especifique IGNORE se quiser descartar a linha de entrada. IGNORE é o padrão.

INTO TABLE

Identifica o nome da tabela do banco de dados na qual carregar as linhas de entrada.

PARTITION

CHARACTER SET

Identifica o conjunto de caracteres dos dados no arquivo de entrada.

ROWS IDENTIFIED BY

Indica o nome do elemento que identifica uma linha no arquivo de entrada. O padrão é <row>.

IGNORE número LINES | ROWS

Especifica que determinado número de linhas no início do arquivo de entrada devem ser ignoradas. Por exemplo, você pode usar IGNORE 1 LINES para ignorar a primeira linha no arquivo de texto ou IGNORE 2 ROWS para ignorar as duas primeiras linhas de dados no XML de entrada.

field_name_or_user_var, ...

Especifica uma lista separada por vírgulas de um ou mais nomes de elementos XML ou variáveis de usuário que identificam quais elementos devem ser carregados, por nome. O nome de uma variável de usuário usada para esse fim deve corresponder ao nome de um elemento do arquivo XML, com o prefixo @. Você pode aplicar variáveis de usuário para armazenar valores de campos correspondentes para reutilização.


LOAD XML FROM S3 's3://amzn-s3-demo-bucket/data.xml'
   INTO TABLE table1
   (column1, @var1)
   SET table_column2 = @var1/100;

SET

Especifica uma lista de operações de atribuição separadas por vírgulas que define os valores de colunas na tabela como valores não incluídos no arquivo de entrada.


LOAD XML FROM S3 's3://amzn-s3-demo-bucket/data.xml'
   INTO TABLE table1
   (column1, column2)
   SET column3 = CURRENT_TIMESTAMP;

Atenção O Javascript está desativado ou não está disponível no seu navegador.

Para usar a documentação da AWS, o Javascript deve estar ativado. Consulte as páginas de Ajuda do navegador para obter instruções.

Convenções do documento

Permitir a comunicação de rede com serviços da AWS

Salvar dados em arquivos de texto no Amazon S3