INSERT INTO - Amazon Athena
Considerações e limitaçõesINSERT INTO...SELECTINSERT INTO...VALUES

INSERT INTO

Insere novas linhas em uma tabela de destino com base em uma instrução de consulta SELECT executada em uma tabela de origem ou com base em um conjunto de VALUES fornecidos como parte da instrução. Quando a tabela de origem é baseada em dados subjacentes em um formato, como CSV ou JSON, e a tabela de destino é baseada em outro formato, como Parquet ou ORC, você pode usar as consultas INSERT INTO para transformar os dados selecionados no formato da tabela de destino.

Considerações e limitações

Considere o seguinte ao usar as consultas INSERT com o Athena.

  • Ao executar uma consulta INSERT em uma tabela com dados subjacentes criptografados no Amazon S3, os arquivos de saída que a consulta INSERT grava não são criptografados por padrão. Recomendamos que você criptografe os resultados da consulta INSERT se estiver inserindo em tabelas com dados criptografados.

    Para obter mais informações sobre como criptografar resultados da consulta usando o console, consulte Criptografar resultados das consultas do Athena armazenados no Amazon S3. Para habilitar a criptografia usando a AWS CLI ou a API do Athena, use as propriedades EncryptionConfiguration da ação StartQueryExecution para especificar as opções de criptografia do Amazon S3 de acordo com os seus requisitos.

  • Para instruções INSERT INTO, a configuração do proprietário do bucket esperado não se aplica ao local da tabela de destino no Amazon S3. A configuração esperada do proprietário do bucket se aplica somente ao local de saída do Amazon S3 que você especificar para os resultados da consulta do Athena. Para obter mais informações, consulte Especificar um local para resultados de consultas usando o console do Athena.

  • Para obter instruções INSERT INTO em conformidade com ACID, consulte a seção INSERT INTO em Atualizar dados nas tabelas Iceberg.

Formatos compatíveis e SerDes

É possível executar uma consulta INSERT em tabelas criadas de dados com os seguintes formatos e SerDes.

Formato de dados SerDe

Avro

org.apache.hadoop.hive.serde2.avro.AvroSerDe
Ion com.amazon.ionhiveserde.IonHiveSerDe

JSON

org.apache.hive.hcatalog.data.JsonSerDe

ORC

org.apache.hadoop.hive.ql.io.orc.OrcSerde

Parquet

org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe

Arquivo de texto

org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

nota

Arquivos CSV, TSV e delimitados personalizados são compatíveis.

Tabelas em bucket sem suporte

INSERT INTO não é compatível com tabelas em bucket. Para obter mais informações, consulte Particionamento e bucketing no Athena.

Consultas federadas sem suporte

INSERT INTO não é suportado para consultas federadas. Tentar fazer isso pode gerar a mensagem de erro: This operation is currently not supported for external catalogs (Atualmente, esta operação não é suportada para catálogos externos). Para obter informações sobre consultas federadas, consulte Usar a consulta federada do Amazon Athena.

Particionamento

Considere os pontos desta seção ao usar o particionamento com as consultas INSERT INTO ou CREATE TABLE AS SELECT.

Limites

A instrução INSERT INTO comporta a gravação de no máximo 100 combinações de partições na tabela de destino. Se você executar a cláusula SELECT em uma tabela com mais de 100 partições, a consulta falhará a menos que a consulta SELECT seja limitada a 100 partições ou menos.

Para obter informações sobre como contornar essa limitação, consulte Usar CTAS e INSERT INTO para resolver o limite de 100 partições.

Ordem das colunas

As instruções INSERT INTO ou CREATE TABLE AS SELECT esperam que a coluna particionada seja a última na lista de colunas projetadas em uma instrução SELECT.

Se a tabela de origem não for particionada ou for particionada em colunas diferentes em comparação com a tabela de destino, as consultas como INSERT INTO destination_table SELECT * FROM source_table vão considerar os valores na última coluna da tabela de origem como os valores de uma coluna de partição na tabela de destino. Tenha isso em mente ao tentar criar uma tabela particionada com base em uma tabela não particionada.

Recursos

Para obter mais informações sobre como usar INSERT INTO com particionamento, consulte os recursos abaixo.

Arquivos gravados no Amazon S3

O Athena grava arquivos nos locais dos dados de origem no Amazon S3 como resultado do comando INSERT. Cada operação INSERT cria um novo arquivo, em vez de anexar a um arquivo existente. Os locais de arquivos dependem da estrutura da tabela e da consulta SELECT, se houver. O Athena gera um arquivo manifesto de dados para cada consulta INSERT. O manifesto rastreia os arquivos que a consulta gravou. Ele é salvo no local dos resultados das consultas do Athena no Amazon S3. Para obter mais informações, consulte Identificar os arquivos de saída das consultas.

Evitar atualizações altamente transacionais

Quando você usa INSERT INTO para adicionar linhas a uma tabela no Amazon S3, o Athena não reescreve nem modifica arquivos existentes. Em vez disso, ele grava as linhas como um ou mais novos arquivos. Como tabelas com muitos arquivos pequenos resultam em desempenho inferior de consultas, e operações de gravação e leitura, como PutObject e GetObject, resultam em custos mais altos no Amazon S3, considere as seguintes opções ao usar INSERT INTO:

  • Execute operações INSERT INTO com menor frequência em lotes maiores de linhas.

  • Para grandes volumes de ingestão de dados, considere usar um serviço como o Amazon Data Firehose.

  • Evite completamente o uso de INSERT INTO. Em vez disso, acumule linhas em arquivos maiores e faça o upload deles diretamente para o Amazon S3, onde poderão ser consultados pelo Athena.

Localizar arquivos órfãos

Se uma instrução INSERT INTO ou CTAS falhar, os dados órfãos poderão ser deixados no local de dados e ser lidos em consultas subsequentes. Para localizar arquivos órfãos para inspeção ou exclusão, é possível usar o arquivo do manifesto de dados que o Athena oferece para rastrear a lista de arquivos a serem gravados. Para obter mais informações, consulte Identificar os arquivos de saída das consultas e DataManifestLocation.

INSERT INTO...SELECT

Especifica a consulta a ser executada em uma tabela, source_table, que determina as linhas a serem inseridas em uma segunda tabela, destination_table. Se a consulta SELECT especificar colunas na source_table, as colunas deverão corresponder precisamente àquelas na destination_table.

Para obter mais informações sobre consultas SELECT, consulte SELECT.

Resumo

INSERT INTO destination_table 
SELECT select_query 
FROM source_table_or_view

Exemplos

Selecione todas as linhas na tabela vancouver_pageviews e insira-as na tabela canada_pageviews:

INSERT INTO canada_pageviews 
SELECT * 
FROM vancouver_pageviews;

Selecione apenas as linhas na tabela vancouver_pageviews em que a coluna date tenha um valor entre 2019-07-01 e 2019-07-31, e insira-as em canada_july_pageviews:

INSERT INTO canada_july_pageviews
SELECT *
FROM vancouver_pageviews
WHERE date
    BETWEEN date '2019-07-01'
        AND '2019-07-31';

Selecione os valores nas colunas city e state na tabela cities_world somente dessas linhas com um valor usa na coluna country e insira-os nas colunas city e state na tabela cities_usa:

INSERT INTO cities_usa (city,state)
SELECT city,state
FROM cities_world
    WHERE country='usa'

INSERT INTO...VALUES

Insere linhas em uma tabela existente especificando colunas e valores. As colunas especificadas e os tipos de dados associados devem corresponder precisamente às colunas e aos tipos de dados na tabela de destino.

Importante

Não recomendamos inserir linhas com VALUES porque o Athena gera arquivos para cada operação INSERT. Isso pode fazer com que muitos arquivos pequenos sejam criados e degradem a performance de consulta da tabela. Para identificar arquivos que uma consulta INSERT cria, examine o arquivo manifesto de dados. Para obter mais informações, consulte Trabalhar com resultados de consultas, consultas recentes e arquivos de saída.

Resumo

INSERT INTO destination_table [(col1,col2,...)] 
VALUES (col1value,col2value,...)[,
       (col1value,col2value,...)][,
       ...]

Exemplos

Nos exemplos a seguir, a tabela de cidades tem três colunas: id, city, state, state_motto. A coluna id é do tipo INT, e todas as outras colunas são do tipo VARCHAR.

Insira uma única linha na tabela cities, com todos os valores da coluna especificados:

INSERT INTO cities 
VALUES (1,'Lansing','MI','Si quaeris peninsulam amoenam circumspice')

Insira duas linhas na tabela cities:

INSERT INTO cities 
VALUES (1,'Lansing','MI','Si quaeris peninsulam amoenam circumspice'),
       (3,'Boise','ID','Esto perpetua')