¿Cómo ajusto el número o el tamaño de los archivos cuando ejecuto una consulta de CTAS en Amazon Athena?

Solución

Nota: Si se muestran errores al ejecutar comandos de la Interfaz de la línea de comandos de AWS (AWS CLI), consulta Solución de problemas de AWS CLI. Además, asegúrate de utilizar la versión más reciente de la AWS CLI.

Puedes utilizar la asignación de buckets en una consulta de CTAS para controlar la cantidad de archivos de salida. Sin embargo, es posible que la cantidad de archivos que se crean no siempre coincida con la cantidad especificada de buckets. La característica de asignación de buckets puede agrupar datos similares, pero no puede controlar con precisión el recuento de archivos.

La siguiente resolución utiliza el conjunto de datos públicos diarios de la Red mundial de climatología histórica, s3://noaa-ghcn-pds/csv.gz/. Para obtener más información, consulta Visualización de más de 200 años de datos climáticos globales con Amazon Athena y Amazon QuickSight.

Nota: En los siguientes comandos de ejemplo, sustituye los valores siguientes por tus valores:

external_location: Ubicación de Amazon Simple Storage Service (Amazon S3) donde guardaste la consulta de CTAS.

formato: El formato que deseas para la salida, como ORC, PARQUET, AVRO, JSON o TEXTFILE.

bucket_count: La cantidad de buckets que deseas.

bucketed_by: El campo para crear un hash y guardar los datos en el bucket, por ejemplo, yearmonthday.

Examinar el conjunto de datos

Para verificar la cantidad de archivos y el tamaño del conjunto de datos, ejecuta el siguiente comando ls:

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

Nota: Sustituye s3://noaa-ghcn-pds/csv.gz/ por la ruta de tu bucket de S3.

Resultado de ejemplo:

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

Creación del entorno

Sigue estos pasos:

1. Para crear una tabla, ejecuta el siguiente comando:

   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. Para probar la tabla, ejecuta el siguiente comando:

   SELECT * FROM historic_climate_gz LIMIT 10

   El resultado muestra 10 líneas del conjunto de datos.

Después de crear el entorno, utiliza los siguientes métodos para modificar el conjunto de datos cuando ejecutes consultas de CTAS.

Modificación de la cantidad de archivos del conjunto de datos

Se recomienda agrupar los datos según una columna que tenga valores de cardinalidad alta y distribuidos de manera uniforme. Para obtener más información, consulta Beneficios de la asignación de buckets.

Sigue estos pasos:

1. Para convertir el conjunto de datos en 20 archivos, ejecuta el siguiente comando:

   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

   Nota: En el ejemplo anterior se utiliza el campo yearmonthday.

2. Para confirmar que el bucket contiene la cantidad deseada de archivos, ejecuta el siguiente comando ls:

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

   Resultado de ejemplo:

   Total Objects: 20
   Total Size: 15.6 Gib

Definición del tamaño aproximado de cada archivo

Sigue estos pasos:

1. Determina el número de buckets que debes usar para alcanzar el número de archivos deseado. Por ejemplo, para dividir el conjunto de datos de 15,4 GB en archivos de 2 GB, debes tener 8 archivos (15,4/2 = 7,7, redondeado a 8).

2. Para crear una nueva tabla agrupada en buckets, ejecuta el siguiente comando:

   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. Para confirmar que el conjunto de datos contiene la cantidad deseada de archivos, ejecuta el siguiente comando ls:

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

   Resultado de ejemplo:

   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

Conversión del formato de datos y definición del tamaño aproximado del archivo

Sigue estos pasos:

1. Para convertir los datos a un formato diferente, ejecuta el siguiente comando:

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

2. Para confirmar el tamaño del conjunto de datos, ejecuta el siguiente comando ls:

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

   Resultado de ejemplo:

   Total Objects: 30
   Total Size: 9.8 GiB

3. Determina el número de buckets que debes usar para alcanzar el número de archivos deseado. Por ejemplo, para archivos de 500 MB y un conjunto de datos de 9,8 GB, debes tener 20 archivos.

4. Para convertir el conjunto de datos en archivos de 500 MB, ejecuta el siguiente comando:

   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. Para confirmar que el conjunto de datos contiene la cantidad deseada de archivos, ejecuta el siguiente comando ls:

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

   Resultado de ejemplo:

   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

Nota: Las tablas agrupadas en buckets no admiten la instrucción INSERT INTO.

Información relacionada

Ejemplos de consultas CTAS

Consideraciones y limitaciones de las consultas CTAS