Usar CTAS e INSERT INTO para resolver o limite de 100 partições - Amazon Athena

Usar CTAS e INSERT INTO para resolver o limite de 100 partições

Athena tem um limite de 100 partições por consulta CREATE TABLE AS SELECT (CTAS). Da mesma forma, é possível adicionar, no máximo, 100 partições a uma tabela de destino com uma instrução INSERT INTO.

Se exceder essa limitação, você poderá receber a mensagem de erro HIVE_TOO_MANY_OPEN_PARTITIONS: Exceeded limit of 100 open writers for partitions/buckets (Limite excedido de 100 gravadores abertos para partições/buckets). Para contornar essa limitação, é possível usar uma instrução CTAS e uma série de instruções INSERT INTO que criam ou inserem até 100 partições cada.

O exemplo neste tópico usa um banco de dados chamado tpch100 cujos dados residem no local do bucket do Amazon S3 s3://amzn-s3-demo-bucket/.

Como usar CTAS e INSERT INTO para criar uma tabela com mais de 100 partições

  1. Use uma instrução CREATE EXTERNAL TABLE para criar uma tabela particionada no campo desejado.

    A instrução de exemplo a seguir particiona os dados de acordo com a coluna l_shipdate. A tabela tem 2.525 partições.

    CREATE EXTERNAL TABLE `tpch100.lineitem_parq_partitioned`(
  `l_orderkey` int, 
  `l_partkey` int, 
  `l_suppkey` int, 
  `l_linenumber` int, 
  `l_quantity` double, 
  `l_extendedprice` double, 
  `l_discount` double, 
  `l_tax` double, 
  `l_returnflag` string, 
  `l_linestatus` string, 
  `l_commitdate` string, 
  `l_receiptdate` string, 
  `l_shipinstruct` string, 
  `l_comment` string)
PARTITIONED BY ( 
  `l_shipdate` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat' LOCATION   's3://amzn-s3-demo-bucket/lineitem/'

  2. Execute um comando SHOW PARTITIONS <table_name> conforme o seguinte para listar as partições.

    SHOW PARTITIONS lineitem_parq_partitioned

    Veja a seguir os resultados parciais de exemplo.

    /*
l_shipdate=1992-01-02
l_shipdate=1992-01-03
l_shipdate=1992-01-04
l_shipdate=1992-01-05
l_shipdate=1992-01-06

...

l_shipdate=1998-11-24
l_shipdate=1998-11-25
l_shipdate=1998-11-26
l_shipdate=1998-11-27
l_shipdate=1998-11-28
l_shipdate=1998-11-29
l_shipdate=1998-11-30
l_shipdate=1998-12-01
*/

  3. Execute uma consulta CTAS para criar uma tabela particionada.

    O exemplo a seguir cria uma tabela chamada my_lineitem_parq_partitioned e usa a cláusula WHERE para restringir DATE a um valor anterior a 1992-02-01. Como o conjunto de dados de exemplo começa com janeiro de 1992, somente partições para janeiro de 1992 são criadas.

    CREATE table my_lineitem_parq_partitioned
WITH (partitioned_by = ARRAY['l_shipdate']) AS
SELECT l_orderkey,
         l_partkey,
         l_suppkey,
         l_linenumber,
         l_quantity,
         l_extendedprice,
         l_discount,
         l_tax,
         l_returnflag,
         l_linestatus,
         l_commitdate,
         l_receiptdate,
         l_shipinstruct,
         l_comment,
         l_shipdate
FROM tpch100.lineitem_parq_partitioned
WHERE cast(l_shipdate as timestamp) < DATE ('1992-02-01');

  4. Execute o comando SHOW PARTITIONS para verificar se a tabela contém as partições desejadas.

    SHOW PARTITIONS my_lineitem_parq_partitioned;

    As partições no exemplo são de janeiro de 1992.

    /*
l_shipdate=1992-01-02
l_shipdate=1992-01-03
l_shipdate=1992-01-04
l_shipdate=1992-01-05
l_shipdate=1992-01-06
l_shipdate=1992-01-07
l_shipdate=1992-01-08
l_shipdate=1992-01-09
l_shipdate=1992-01-10
l_shipdate=1992-01-11
l_shipdate=1992-01-12
l_shipdate=1992-01-13
l_shipdate=1992-01-14
l_shipdate=1992-01-15
l_shipdate=1992-01-16
l_shipdate=1992-01-17
l_shipdate=1992-01-18
l_shipdate=1992-01-19
l_shipdate=1992-01-20
l_shipdate=1992-01-21
l_shipdate=1992-01-22
l_shipdate=1992-01-23
l_shipdate=1992-01-24
l_shipdate=1992-01-25
l_shipdate=1992-01-26
l_shipdate=1992-01-27
l_shipdate=1992-01-28
l_shipdate=1992-01-29
l_shipdate=1992-01-30
l_shipdate=1992-01-31
*/

  5. Use uma instrução INSERT INTO para adicionar partições à tabela.

    O exemplo a seguir adiciona partições para as datas do mês de fevereiro de 1992.

    INSERT INTO my_lineitem_parq_partitioned
SELECT l_orderkey,
         l_partkey,
         l_suppkey,
         l_linenumber,
         l_quantity,
         l_extendedprice,
         l_discount,
         l_tax,
         l_returnflag,
         l_linestatus,
         l_commitdate,
         l_receiptdate,
         l_shipinstruct,
         l_comment,
         l_shipdate
FROM tpch100.lineitem_parq_partitioned
WHERE cast(l_shipdate as timestamp) >= DATE ('1992-02-01')
AND cast(l_shipdate as timestamp) < DATE ('1992-03-01');

  6. Execute SHOW PARTITIONS novamente.

    SHOW PARTITIONS my_lineitem_parq_partitioned;

    A tabela de exemplo agora tem partições de janeiro e fevereiro de 1992.

    /*
l_shipdate=1992-01-02
l_shipdate=1992-01-03
l_shipdate=1992-01-04
l_shipdate=1992-01-05
l_shipdate=1992-01-06

...

l_shipdate=1992-02-20
l_shipdate=1992-02-21
l_shipdate=1992-02-22
l_shipdate=1992-02-23
l_shipdate=1992-02-24
l_shipdate=1992-02-25
l_shipdate=1992-02-26
l_shipdate=1992-02-27
l_shipdate=1992-02-28
l_shipdate=1992-02-29
*/

  7. Continue a usar instruções INSERT INTO que leem e adicionam até 100 partições cada. Continue até atingir o número de partições necessárias.

    Importante

    Ao definir a condição WHERE, certifique-se de que as consultas não se sobreponham. Caso contrário, algumas partições podem ter dados duplicados.