在 Athena 中运行 CTAS 查询时，该如何设置文件数量或大小？

上次更新时间：2021 年 9 月 8 日

在 Amazon Athena 中运行 CREATE TABLE AS SELECT (CTAS) 查询时，我需要定义文件的数量或每个文件的数据量。

解决方法

在 CTAS 查询中使用分桶设置文件大小或数量。

注意：以下步骤使用全球历史气候网络日报的公共数据集 (s3://noaa-ghcn-pds/csv.gz/) 来说明解决方案。有关此数据集的更多信息，请参阅使用 Amazon Athena 和 Amazon QuickSight 对 200 多年的全球气候数据进行可视化。以下步骤演示了如何检查数据集、创建环境，以及如何修改数据集：

修改 Amazon Simple Storage Service (Amazon S3) 数据集中的文件数量。
设置每个文件的近似大小。
转换数据格式并设置近似的文件大小。

检查数据集

运行以下 AWS Command Line Interface (AWS CLI) 来验证文件数量和数据集大小：

注意：如果在运行 AWS CLI 命令时遇到错误，请确保您使用的是最新版本的 AWS CLI。

aws s3 ls s3://noaa-ghcn-pds/csv.gz/ --summarize --recursive --human-readable

输出与以下内容类似：

2019-11-30 01:58:05    3.3 KiB csv.gz/1763.csv.gz
2019-11-30 01:58:06    3.2 KiB csv.gz/1764.csv.gz
2019-11-30 01:58:06    3.3 KiB csv.gz/1765.csv.gz
2019-11-30 01:58:07    3.3 KiB csv.gz/1766.csv.gz
...
2019-11-30 02:05:43  199.7 MiB csv.gz/2016.csv.gz
2019-11-30 02:05:50  197.7 MiB csv.gz/2017.csv.gz
2019-11-30 02:05:54  197.0 MiB csv.gz/2018.csv.gz
2019-11-30 02:05:57  168.8 MiB csv.gz/2019.csv.gz

Total Objects: 257
Total Size: 15.4 GiB

创建环境

1. 运行如下语句来创建表：

CREATE EXTERNAL TABLE historic_climate_gz(
  id string,
  yearmonthday int,
  element string,
  temperature int,
  m_flag string,
  q_flag string,
  s_flag string,
  obs_time int)
ROW FORMAT DELIMITED
  FIELDS TERMINATED BY ','
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://noaa-ghcn-pds/csv.gz/'

2. 运行以下命令来测试表：

SELECT * FROM historic_climate_gz LIMIT 10

输出将显示数据集中的 10 行。创建环境后，在运行 CTAS 查询时使用以下一种或多种方法来修改数据集。

修改数据集中的文件数量

最好按基数高且值分布均匀的列来分桶存储数据。有关更多信息，请参阅分桶与分区。在以下示例中，我们使用 yearmonthday 字段。

1. 要将数据集转换为 20 个文件，请运行如下语句。

CREATE TABLE "historic_climate_gz_20_files"
WITH (
      external_location = 's3://awsexamplebucket/historic_climate_gz_20_files/',
      format = 'TEXTFILE',
      bucket_count=20,
      bucketed_by = ARRAY['yearmonthday']
     ) AS
SELECT * FROM historic_climate_gz

替换查询中的以下值：
external_location：Athena 保存 CTAS 查询的 Amazon S3 位置
格式：输出所需的格式（例如 ORC、PARQUET、AVRO、JSON 或 TEXTFILE）
bucket_count：您想要的文件数量（例如，20）
bucketed_by：用于哈希并将数据保存在存储桶中的字段（例如，yearmonthday）

2. 运行以下命令以确认存储桶中包含所需的文件数量：

aws s3 ls s3://awsexamplebucket/historic_climate_gz_20_files/ --summarize --recursive --human-readable
Total Objects: 20
Total Size: 15.6 Gib

设置每个文件的近似大小

1. 确定需要多少文件才能达到所需的文件大小。例如，要将 15.4GB 的数据集拆分为 2GB 的文件，您需要 8 个文件（15.4 / 2 = 7.7，四舍五入为 8）。

2. 运行如下语句：

CREATE TABLE "historic_climate_gz_2GB_files"
WITH (
      external_location = 's3://awsexamplebucket/historic_climate_gz_2GB_file/',
      format = 'TEXTFILE',
      bucket_count=8,
      bucketed_by = ARRAY['yearmonthday']) AS
SELECT * FROM historic_climate_gz

替换查询中的以下值：
external_location：Athena 保存 CTAS 查询的 Amazon S3 位置
格式：必须与源数据的格式相同（例如 ORC、PARQUET、AVRO、JSON 或 TEXTFILE）
bucket_count：您想要的文件数量（例如，20）
bucketed_by：用于哈希并将数据保存在存储桶中的字段。选择基数高的字段。

3. 运行以下命令以确认数据集中包含所需的文件数量：

aws s3 ls s3://awsexamplebucket/historic_climate_gz_2GB_file/ --summarize --recursive --human-readable

输出与以下内容类似：

2019-09-03 10:59:20    1.7 GiB historic_climate_gz_2GB_file/20190903_085819_00005_bzbtg_bucket-00000.gz
2019-09-03 10:59:20    2.0 GiB historic_climate_gz_2GB_file/20190903_085819_00005_bzbtg_bucket-00001.gz
2019-09-03 10:59:20    2.0 GiB historic_climate_gz_2GB_file/20190903_085819_00005_bzbtg_bucket-00002.gz
2019-09-03 10:59:19    1.9 GiB historic_climate_gz_2GB_file/20190903_085819_00005_bzbtg_bucket-00003.gz
2019-09-03 10:59:17    1.7 GiB historic_climate_gz_2GB_file/20190903_085819_00005_bzbtg_bucket-00004.gz
2019-09-03 10:59:21    1.9 GiB historic_climate_gz_2GB_file/20190903_085819_00005_bzbtg_bucket-00005.gz
2019-09-03 10:59:18    1.9 GiB historic_climate_gz_2GB_file/20190903_085819_00005_bzbtg_bucket-00006.gz
2019-09-03 10:59:17    1.9 GiB historic_climate_gz_2GB_file/20190903_085819_00005_bzbtg_bucket-00007.gz
Total Objects: 8
Total Size: 15.0 GiB

转换数据格式并设置近似的文件大小

1. 运行如下语句，将数据转换为不同的格式：

CREATE TABLE "historic_climate_parquet"
WITH (
      external_location = 's3://awsexamplebucket/historic_climate_parquet/',
      format = 'PARQUET') AS
SELECT * FROM historic_climate_gz

替换查询中的以下值：
external_location：Athena 保存 CTAS 查询的 Amazon S3 位置
格式：您想要输出的格式（例如 ORC、PARQUET、AVRO、JSON 或 TEXTFILE）

2. 运行以下命令以确认数据集的大小：

aws s3 ls s3://awsexamplebucket/historic_climate_parquet/ --summarize --recursive --human-readable

输出与以下内容类似：

Total Objects: 30
Total Size: 9.8 GiB

3. 确定需要多少文件才能达到所需的文件大小。例如，如果您需要 500MB 大小的文件，而数据集大小是 9.8GB，则需要 20 个文件（9800 / 500 = 19.6，四舍五入为 20）。

4. 要将数据集转换为 500MB 大小的文件，请运行如下语句：

CREATE TABLE "historic_climate_parquet_500mb"
WITH (
      external_location = 's3://awsexamplebucket/historic_climate_parquet_500mb/',
      format = 'PARQUET',
      bucket_count=20,
      bucketed_by = ARRAY['yearmonthday']
       ) AS
SELECT * FROM historic_climate_parquet

替换查询中的以下值：
external_location：Athena 保存 CTAS 查询的 Amazon S3 位置
bucket_count：您想要的文件数量（例如，20）
bucketed_by：用于哈希并将数据保存在存储桶中的字段。选择基数高的字段。

5. 运行以下命令以确认数据集中包含所需的文件数量：

aws s3 ls s3://awsexamplebucket/historic_climate_parquet_500mb/ --summarize --recursive --human-readable

输出与以下内容类似：

2019-09-03 12:01:45  333.9 MiB historic_climate_parquet_500mb/20190903_095742_00001_uipqt_bucket-00000
2019-09-03 12:01:01  666.7 MiB historic_climate_parquet_500mb/20190903_095742_00001_uipqt_bucket-00001
2019-09-03 12:01:00  665.6 MiB historic_climate_parquet_500mb/20190903_095742_00001_uipqt_bucket-00002
2019-09-03 12:01:06  666.0 MiB historic_climate_parquet_500mb/20190903_095742_00001_uipqt_bucket-00003
2019-09-03 12:00:59  667.3 MiB historic_climate_parquet_500mb/20190903_095742_00001_uipqt_bucket-00004
2019-09-03 12:01:27  666.0 MiB historic_climate_parquet_500mb/20190903_095742_00001_uipqt_bucket-00005
2019-09-03 12:01:10  666.5 MiB historic_climate_parquet_500mb/20190903_095742_00001_uipqt_bucket-00006
2019-09-03 12:01:12  668.3 MiB historic_climate_parquet_500mb/20190903_095742_00001_uipqt_bucket-00007
2019-09-03 12:01:03  666.8 MiB historic_climate_parquet_500mb/20190903_095742_00001_uipqt_bucket-00008
2019-09-03 12:01:10  646.4 MiB historic_climate_parquet_500mb/20190903_095742_00001_uipqt_bucket-00009
2019-09-03 12:01:35  639.0 MiB historic_climate_parquet_500mb/20190903_095742_00001_uipqt_bucket-00010
2019-09-03 12:00:52  529.5 MiB historic_climate_parquet_500mb/20190903_095742_00001_uipqt_bucket-00011
2019-09-03 12:01:29  334.2 MiB historic_climate_parquet_500mb/20190903_095742_00001_uipqt_bucket-00012
2019-09-03 12:01:32  333.0 MiB historic_climate_parquet_500mb/20190903_095742_00001_uipqt_bucket-00013
2019-09-03 12:01:34  332.2 MiB historic_climate_parquet_500mb/20190903_095742_00001_uipqt_bucket-00014
2019-09-03 12:01:44  333.3 MiB historic_climate_parquet_500mb/20190903_095742_00001_uipqt_bucket-00015
2019-09-03 12:01:51  333.0 MiB historic_climate_parquet_500mb/20190903_095742_00001_uipqt_bucket-00016
2019-09-03 12:01:39  333.0 MiB historic_climate_parquet_500mb/20190903_095742_00001_uipqt_bucket-00017
2019-09-03 12:01:47  333.0 MiB historic_climate_parquet_500mb/20190903_095742_00001_uipqt_bucket-00018
2019-09-03 12:01:49  332.3 MiB historic_climate_parquet_500mb/20190903_095742_00001_uipqt_bucket-00019
Total Objects: 20
Total Size: 9.9 GiB

注意：分桶存储的表不支持 INSERT INTO 语句。有关详细信息，请参阅不支持分桶存储的表。

在 Athena 中运行 CTAS 查询时，该如何设置文件数量或大小？

解决方法

检查数据集

创建环境

修改数据集中的文件数量

设置每个文件的近似大小

转换数据格式并设置近似的文件大小

相关信息

终止对 Internet Explorer 的支持