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

上次更新时间：2020 年 1 月 3 日

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

解决方法

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

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

1.    修改 Amazon Simple Storage Service (Amazon S3) 数据集中的文件数量。

2.    设置每个文件的近似大小。

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

检查数据集

使用 AWS 命令行界面 (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 个文件，请运行如下语句。替换以下字段中的值：

external_location：供 Athena 保存 CTAS 查询的 Amazon S3 位置格式：您希望采用的输出格式（如 ORC、PARQUET、AVRO、JSON 或 TEXTFILE）bucket_count：您需要的文件数量（例如，20 个）bucketed_by：该字段用于散列数据并将数据保存在存储桶（例如，yearmonthday）中

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

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.    运行如下语句。替换以下字段中的值：

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

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

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.    运行如下语句，将数据转换为不同的格式。替换以下字段中的值：

external_location：供 Athena 保存 CTAS 查询格式的 Amazon S3 位置：您希望转换为的格式（ORC、PARQUET、AVRO、JSON 或 TEXTFILE）

CREATE TABLE "historic_climate_parquet"
WITH (
      external_location = 's3://awsexamplebucket/historic_climate_parquet/',
      format = 'PARQUET') as
select * from historic_climate_gz

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 大小的文件，请运行如下语句。替换以下字段中的值：

external_location：供 Athena 保存 CTAS 查询的 Amazon S3 位置bucket_count：您需要的文件数量（例如，20 个）bucketed_by：该字段用于散列数据并将数据保存在存储桶中。选择基数高的字段。

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

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

CTAS 查询的示例

CTAS 查询的注意事项和限制

这篇文章对您有帮助吗？

有
没有

我们可以改进什么？

请告诉我们

需要更多帮助？

联系 AWS Support