INSERT INTO
基于在源表上运行的 SELECT
查询语句或作为语句一部分提供的一组 VALUES
,将新行插入到目标表中。如果源表基于一种格式(例如 CSV 或 JSON)的基础数据,而目标表基于另一种格式(例如 Parquet 或 ORC),则可以使用 INSERT INTO
查询将所选数据转换为目标表的格式。
注意事项和限制
在 Athena 中使用 INSERT
查询时注意以下事项。
-
在具有以 Amazon S3 加密的基础数据的表上运行
INSERT
查询时,INSERT
查询写入的输出文件预设情况下不加密。如果要插入具有加密数据的表中,建议您对INSERT
查询结果进行加密。有关使用控制台加密查询结果的更多信息,请参阅 加密在 Amazon S3 中存储的 Athena 查询结果。要启用加密,请使用 AWS CLI 或 Athena API,使用 StartQueryExecution 操作的
EncryptionConfiguration
属性来根据您的要求指定 Amazon S3 加密选项。 -
对于
INSERT INTO
语句,预期存储桶拥有者设置不适用于 Amazon S3 中的目标表位置。预期存储桶拥有者设置仅适用于您为 Athena 查询结果指定的 Amazon S3 输出位置。有关更多信息,请参阅 使用 Athena 控制台指定查询结果位置。 -
有关符合 ACID 的
INSERT INTO
语句,请参阅 更新 Iceberg 表数据 的INSERT INTO
一节。
支持的格式和 SerDes
针对使用以下格式和 SerDes 从数据创建的表,您可以运行 INSERT
查询。
Data format(数据格式) | 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 |
文本文件 |
org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe 注意支持 CSV、TSV 和自定义分隔文件。 |
不支持分桶表
分桶表上不支持 INSERT INTO
。有关更多信息,请参阅 使用分区和分桶。
不支持联合查询
INSERT INTO
不支持联合查询。尝试这样做可能会导致出现错误消息 This operation is currently not supported for external catalogs
(外部目录目前不支持此操作)。有关联合查询的信息,请参阅 使用 Amazon Athena 联合查询。
分区
使用 INSERT
INTO
或者 CREATE TABLE AS SELECT
查询进行分区时,请考虑本节中的要点。
限制
INSERT INTO
语句支持向目标表写入最多 100 个分区。如果您在包含超过 100 个分区的表上运行 SELECT
子句,则查询将失败,除非 SELECT
查询限制为 100 个或更少的分区。
有关绕过此限制的信息,请参阅使用 CTAS 和 INSERT INTO 绕过 100 分区限制。
列排序
INSERT INTO
或者 CREATE TABLE AS SELECT
语句期望分区列是 SELECT
语句中投影列列表的最后一列。
如果源表未分区,或者与目标表相比在不同的列上进行分区,则类似 INSERT INTO
的查询会将源表最后一列中的值视为目标表中某个分区列的值。在尝试从未分区的表创建已分区的表时,请记住这一点。destination_table
SELECT * FROM
source_table
资源
有关使用带有分区功能的 INSERT INTO
的更多信息,请参阅以下资源。
-
有关将已分区数据插入已分区表中的信息,请参阅 使用 CTAS 和 INSERT INTO 绕过 100 分区限制。
-
有关将未分区数据插入已分区表中的信息,请参阅 将 CTAS 和 INSERT INTO 用于 ETL 和数据分析。
写入 Amazon S3 的文件
作为 INSERT
命令的结果,Athena 将文件写入 Amazon S3 中的源数据位置。每个 INSERT
操作都会创建一个新文件,而不是附加到现有文件。文件位置取决于表和 SELECT
查询的结构(如果存在)。Athena 为每个 INSERT
查询生成一个数据清单文件。清单跟踪查询写入的文件。它在 Amazon S3 中保存到 Athena 查询结果位置。有关更多信息,请参阅 识别查询输出文件。
避免高度事务性的更新
在使用 INSERT INTO
向 Amazon S3 中的表添加行时,Athena 不会重写或修改现有文件,而是会将这些行作为一个或多个新文件写入。由于这些表会因小文件过多而导致查询性能降低,而且 PutObject
和 GetObject
等写入和读取操作会导致 Amazon S3 的成本增加,所以请在使用 INSERT INTO
时考虑以下选项:
-
降低对较大批量行运行
INSERT INTO
操作的频率。 -
对于大量的数据摄取,可以考虑使用 Amazon Data Firehose 之类的服务。
-
完全避免使用
INSERT INTO
,而是将行累积到更大的文件中,然后将这些文件直接上传到 Amazon S3,以便 Athena 在其中进行查询。
查找孤立文件
如果 CTAS
或 INSERT INTO
语句失败,孤立数据可以保留在数据位置,可能会在后续查询中进行读取。若要查找孤立文件以执行检查或删除操作,您可以使用 Athena 提供的数据清单文件跟踪要写入的文件列表。有关更多信息,请参阅识别查询输出文件和 DataManifestLocation。
INSERT INTO...SELECT
指定要在表 source_table
上运行的查询,该表确定要插入第二个表 destination_table
中的行。如果 SELECT
查询指定 source_table
中的列,则这些列必须与 destination_table
中的那些列精确匹配。
有关 SELECT
查询的更多信息,请参阅 SELECT。
摘要
INSERT INTO destination_table
SELECT select_query
FROM source_table_or_view
示例
选择 vancouver_pageviews
表中的所有行并将其插入 canada_pageviews
表中:
INSERT INTO canada_pageviews SELECT * FROM vancouver_pageviews;
只选择 vancouver_pageviews
表中 date
列的值介于 2019-07-01
和 2019-07-31
之间的那些行,然后将它们插入 canada_july_pageviews
:
INSERT INTO canada_july_pageviews SELECT * FROM vancouver_pageviews WHERE date BETWEEN date '2019-07-01' AND '2019-07-31';
仅从 country
列中值为 usa
的那些行中选择 cities_world
表中 city
和 state
列中的值,然后将它们插入 cities_usa
表中的 city
和 state
列中:
INSERT INTO cities_usa (city,state) SELECT city,state FROM cities_world WHERE country='usa'
INSERT INTO...VALUES
通过指定列和值将行插入到现有表中。指定的列和关联的数据类型必须与目标表中的列和数据类型精确匹配。
重要
我们不建议使用 VALUES
插入行,因为 Athena 会为每个 INSERT
操作生成文件。这会导致创建许多小文件并降低表的查询性能。要标识 INSERT
查询创建的文件,请检查数据清单文件。有关更多信息,请参阅 使用查询结果和最近查询。
摘要
INSERT INTO destination_table [(col1,col2,...)]
VALUES (col1value,col2value,...)[,
(col1value,col2value,...)][,
...]
示例
在以下示例中,城市表包含三个列:id
、city
、state
、state_motto
。id
列为类型 INT
,所有其他列均为类型 VARCHAR
。
在 cities
表中插入一行,并指定所有列值:
INSERT INTO cities VALUES (1,'Lansing','MI','Si quaeris peninsulam amoenam circumspice')
在 cities
表中插入两行:
INSERT INTO cities VALUES (1,'Lansing','MI','Si quaeris peninsulam amoenam circumspice'), (3,'Boise','ID','Esto perpetua')