hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Patrick Duin <patd...@gmail.com>
Subject Re: Control large file output in dynamic partitioned insert
Date Tue, 25 Sep 2018 13:05:33 GMT
ok found my own answer via:
https://www.ericlin.me/2016/03/hive-dynamic-insert-query-only-uses-1-reducer-out-of-thousands-of-reducers/

This setting gets rid of the last reduce phase in my insert:
set hive.optimize.sort.dynamic.partition=false;

Now I get as many files in my partition as I have reducers.
I can now control the file size and the number of reducers with:
hive.exec.reducers.bytes.per.reducer
I'm using Parquet with Snappy compression for my data. This reduces the
file size with about 0.5. So I set the bytes per reducer to 2x what I want
to get my preferred file size.

Hope this benefits anyone else.



Op ma 24 sep. 2018 om 18:22 schreef Patrick Duin <patduin@gmail.com>:

> Hi all,
>
> I got a query doing an insert overwrite like this:
>
> WITH tbl1 AS (
>   SELECT
>    col0, col1, local_date, local_hour
>   FROM tbl1
>   WHERE ....
>   ),
> tbl2 AS (
>   SELECT col0, col1, local_date, local_hour
>   FROM tbl2
>   WHERE ....
> )
> INSERT OVERWRITE TABLE
>   tbl3
> PARTITION (local_date, local_hour)
>   SELECT * FROM tbl1
>   UNION DISTINCT
>   SELECT * FROM tbl2
>
> Each partition contains ~15GB of compressed Parquet data.. The input
> tables are AVRO.
> I'm running on hive-2.3.2 using Tez on EMR in AWS.
> My problem is that the final reduce phase spins up with x reducers where x
> is let's say 60. If my query has only 3 partitions to write, all reducers
> are done very quickly except 3 of them that write the file.
> This makes the whole process very slow and also creates one very large
> part file in each output partition.
> I've been trying to control the size of the files and force multiple
> reducers writing to the same output partition.
>
> Settings I've been using:
> set hive.exec.dynamic.partition=false;
> set hive.exec.dynamic.partition.mode=nonstrict;
> set hive.exec.max.dynamic.partitions.pernode=2000;
> set hive.exec.max.dynamic.partitions=20000;
> set mapred.reduce.task=9;
>
> any ideas?
>
> Thanks,
>  Patrick
>

Mime
View raw message