Amazon Athena
User Guide

INSERT INTO

Inserts new rows into a destination table based on a SELECT query statement that runs on a source table, or based on a set of VALUES provided as part of the statement. When the source table is based on underlying data in one format, such as CSV or JSON, and the destination table is based on another format, such as Parquet or ORC, you can use INSERT INTO queries to transform selected data into the destination table's format.

Considerations and Limitations

Consider the following when using INSERT queries with Athena.

Important

When running an INSERT query on a table with underlying data that is encrypted in Amazon S3, the output files that the INSERT query writes are not encrypted by default. We recommend that you encrypt INSERT query results if you are inserting into tables with encrypted data. For more information about encrypting query results using the console, see Encrypting Query Results Stored in Amazon S3. To enable encryption using the AWS CLI or Athena API, use the EncryptionConfiguration properties of the StartQueryExecution action to specify Amazon S3 encryption options according to your requirements.

Supported Formats and SerDes

You can run an INSERT query on tables created from data with the following formats and SerDes.

Data format SerDe

Avro

org.apache.hadoop.hive.serde2.avro.AvroSerDe

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

Text file

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

Note

CSV, TSV, and custom-delimited files are supported.

Bucketed tables not supported

INSERT INTO is not supported on bucketed tables. For more information, see Bucketing vs Partitioning.

Partition Limits

The INSERT INTO statement supports writing a maximum of 100 partitions to the destination table. If you run the SELECT clause on a table with more than 100 partitions, the query fails unless the SELECT query is limited to 100 partitions or fewer.

Files Written to Amazon S3

Athena writes files to source data locations in Amazon S3 as a result of the INSERT command. Each INSERT operation creates a new file, rather than appending to an existing file. The file locations depend on the structure of the table and the SELECT query, if present. Athena generates a data manifest file for each INSERT query. The manifest tracks the files that the query wrote. It is saved to the Athena query result location in Amazon S3. If a query fails, the manifest also tracks files that the query intended to write. The manifest is useful for identifying orphaned files resulting from a failed query. For more information, see Working with Query Results, Output Files, and Query History.

INSERT INTO...SELECT

Specifies the query to run on one table, source_table, which determines rows to insert into a second table, destination_table. If the SELECT query specifies columns in the source_table, the columns must precisely match those in the destination_table.

For more information about SELECT queries, see SELECT.

Synopsis

INSERT INTO destination_table SELECT select_query FROM source_table_or_view

Examples

Select all rows in the vancouver_pageviews table and insert them into the canada_pageviews table:

INSERT INTO canada_pageviews SELECT * FROM vancouver_pageviews;

Select only those rows in the vancouver_pageviews table where the date column has a value between 2019-07-01 and 2019-07-31, and then insert them into canada_july_pageviews:

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

Select the values in the city and state columns in the cities_usa table only from those rows with a value of usa in the country column and insert them into the city and state columns in the cities_world table:

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

INSERT INTO...VALUES

Inserts rows into an existing table by specifying columns and values. Specified columns and associated data types must precisely match the columns and data types in the destination table.

Important

We do not recommend inserting rows using VALUES because Athena generates files for each INSERT operation. This can cause many small files to be created and degrade the table's query performance. To identify files that an INSERT query creates, examine the data manifest file. For more information, see Working with Query Results, Output Files, and Query History.

Synopsis

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

Examples

In the following examples, the cities table has three columns: id, city, state, state_motto. The id column is type INT and all other columns are type VARCHAR.

Insert a single row into the cities table, with all column values specified:

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

Insert two rows into the cities table:

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