You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Federico D'Ambrosio <fe...@gmail.com> on 2018/07/13 16:05:04 UTC
Cannot INSERT OVERWRITE on clustered table with > 8 buckets
Hello everyone,
I'm using Hive 1.2.1 with LLAP on HDP 2.6.5. Tez AM is 3GB, there are 3
daemons for a total of 34816 MB.
I'm trying to insert data from an external table (csv, 20 GB) into a table
defined as such:
CREATE TABLE IF NOT EXISTS passaggi1718
(
PASSAGGIO tinyint
,DISTINCTTESSERA string
,DISTINCTTESSERAXGG string
,CODICETESSERA string
,PRIMOINGRESSOAX decimal(38,10)
,PRIMOINGRESSOCALC decimal(38,10)
,PRODLORDO decimal(20,6)
,PUNTI decimal(38,10)
,NPOOLNR decimal(4,0)
,NZUTRNR decimal(3,0)
,NKASSANR decimal(3,0)
,ID_TICKETTYPE decimal(5,0)
,ID_PERSONTYPE decimal(6,0)
,ID_TICKETPERSONTYPEDEF decimal(6,0)
,NZUTRNRPRINGR decimal(38,10)
,NZUTRNRBEF decimal(38,10)
,NZUTRNRAFT decimal(38,10)
,NSERIENNR decimal(38,10)
,NSERIENNR_GROUP string
,NGES_VALLEY_COMPANY string
)
PARTITIONED BY (DATAPASSAGGIO string, ORAPASSAGGIO string)
CLUSTERED BY (ID_TICKETTYPE, ID_PERSONTYPE, NPOOLNR, NKASSANR) INTO 8
BUCKETS
STORED AS ORC
TBLPROPERTIES("orc.compress"="ZLIB");
Total number of partitions is 137k.
The insert query is the following:
INSERT OVERWRITE TABLE passaggi1718
PARTITION(DATAPASSAGGIO, ORAPASSAGGIO)
SELECT
PASSAGGIO
,DISTINCTTESSERA
,DISTINCTTESSERAXGG
,CODICETESSERA
,PRIMOINGRESSOAX
,PRIMOINGRESSOCALC
,PRODLORDO
,PUNTI
,NPOOLNR
,NZUTRNR
,NKASSANR
,ID_TICKETTYPE
,ID_PERSONTYPE
,ID_TICKETPERSONTYPEDEF
,NZUTRNRPRINGR
,NZUTRNRBEF
,NZUTRNRAFT
,NSERIENNR
,NSERIENNR_GROUP
,NGES_VALLEY_COMPANY
,DATAPASSAGGIO
,ORAPASSAGGIO
FROM passaggi_csv1718;
This is during execution, with a weirdly high number of reducers (337):
[image: immagine.png]
It then remains stuck like this, with the FAILED tasks number increasing
and increasing, while the RUNNING tasks number remains fixed at 336 (not
337 because apparently one task succeeded, as you can see):
[image: immagine.png]
Without the CLUSTERED clause, or with 4 buckets, the query gets executed
correctly (337 reducers, even in this case, but they succeed).
Has anyone ever had this kind of issue? Where should I look into?
Kind regards,
--
Federico D'Ambrosio
Re: Cannot INSERT OVERWRITE on clustered table with > 8 buckets
Posted by Gopal Vijayaraghavan <go...@apache.org>.
> Or a simple insert will be automatically sorted as the table DDL mention ?
Simple insert should do the sorting, older versions of Hive had ability to disable that (which is a bad thing & therefore these settings are now just hard-configed to =true in Hive3.x)
-- set hive.enforce.bucketing=true;
-- set hive.enforce.sorting=true;
It will pick 8 reducers are the default count, which might not work for the # of partitions you have.
set hive.optimize.sort.dynamic.partition=true;
is what was used to fix these sort of reducer count issues when you are using bucketing + partitioning on a table (using bucketing without partitioning doesn't need that).
With every test run I end up inserting 3Tb or so into 2500 partitions using these settings.
https://github.com/hortonworks/hive-testbench/blob/hdp3/settings/load-partitioned.sql
Cheers,
Gopal
Re: Cannot INSERT OVERWRITE on clustered table with > 8 buckets
Posted by Nicolas Paris <ni...@gmail.com>.
Hi Gopal
Can you try running with (& see what your query read-perf looks like)
> https://gist.github.com/t3rmin4t0r/087b61f79514673c307bb9a88327a4db
>
> CREATE TABLE IF NOT EXISTS passaggi1718
> (
> ...
> )
> PARTITIONED BY (DATAPASSAGGIO string)
> CLUSTERED BY (ORAPASSAGGIO) INTO 8 BUCKETS
> SORTED BY (ORAPASSAGGIO, ID_TICKETTYPE, ID_PERSONTYPE, NPOOLNR, NKASSANR)
> STORED AS ORC
> TBLPROPERTIES("orc.compress"="ZLIB");
>
>
>
With such a table, does one need to sort within the INSERT statement too ?
Or a simple insert will be automatically sorted as the table DDL mention ?
Thanks for clarifications
Re: Cannot INSERT OVERWRITE on clustered table with > 8 buckets
Posted by Federico D'Ambrosio <fe...@gmail.com>.
Hi Gopal,
Thank you very much for your response and your very helpful insight on my
problem.
As you correctly guessed, I'm quite new to Hive.
I tried the query you suggested, in your gist, for the creation of the
table without using LLAP and it worked fine, as in it didn't hang like the
one I tried before. I'll be trying the performances of the read queries and
check back in case of need.
In the slides you linked, you mention that the average partition should be
>=1GB, now I have 181 partitions with an uneven distribution data
(partitions sizes go from 64MB to 1.7 kB, in orc format). Could that be a
problem? Should I be aware of any specific issue with this?
In any case, the dataset I'm using would be a single partition of a bigger
dataset (there are passaggi1617, passaggi1516 and so on, of the same raw
size of 20GB each), and for this reason I was thinking that in the possible
final table, there would be 2 levels of partitions (SEASONPASSAGE,
DATEPASSAGE). Is there any best practice when it comes to partitions
hierarchy? Other than what you already mentioned, making sure partitioning
is not too fine grained.
Thank you very much Gopal,
really appreciate your help.
Kind regards,
Federico
Il giorno ven 13 lug 2018 alle ore 20:18 Gopal Vijayaraghavan <
gopalv@apache.org> ha scritto:
>
> > I'm using Hive 1.2.1 with LLAP on HDP 2.6.5. Tez AM is 3GB, there are 3
> daemons for a total of 34816 MB.
>
> Assuming you're using Hive2 here (with LLAP) and LLAP kinda sucks for ETL
> workloads, but this is a different problem.
>
> > PARTITIONED BY (DATAPASSAGGIO string, ORAPASSAGGIO string)
> > CLUSTERED BY (ID_TICKETTYPE, ID_PERSONTYPE, NPOOLNR, NKASSANR) INTO 8
> BUCKETS
> > STORED AS ORC
> ...
> > Total number of partitions is 137k.
>
> 20Gb divided by 137k makes for very poorly written ORC files, because I'd
> guess that it has too few rows in a file (will be much smaller than 1 HDFS
> block) - partitioning this fine is actually a performance issue on compile
> time.
>
> You can make this insert work by changing the insert shuffle mechanism
> (run an explain with/without to see the difference).
>
> set hive.optimize.sort.dynamic.partition=true; --
> https://issues.apache.org/jira/browse/HIVE-6455
>
> But I suspect you will be very disappointed by the performance of the read
> queries after this insert.
>
> > ,NPOOLNR decimal(4,0)
> > ,NZUTRNR decimal(3,0)
> > ,NKASSANR decimal(3,0)
> > ,ID_TICKETTYPE decimal(5,0)
> > ,ID_PERSONTYPE decimal(6,0)
> > ,ID_TICKETPERSONTYPEDEF decimal(6,0)
>
> That's also going to hurt - your schema raises a lot of red-flags that I
> find people do when they first migrated to hive.
>
> https://www.slideshare.net/t3rmin4t0r/data-organization-hive-meetup/
>
> In general, you need to fix the partition count, bucketing structure (how
> clustered by does not "cluster", you need another "sorted by"), zero scale
> decimals.
>
> Can you try running with (& see what your query read-perf looks like)
>
> https://gist.github.com/t3rmin4t0r/087b61f79514673c307bb9a88327a4db
>
> Cheers,
> Gopal
>
>
>
--
Federico D'Ambrosio
Re: Cannot INSERT OVERWRITE on clustered table with > 8 buckets
Posted by Gopal Vijayaraghavan <go...@apache.org>.
> I'm using Hive 1.2.1 with LLAP on HDP 2.6.5. Tez AM is 3GB, there are 3 daemons for a total of 34816 MB.
Assuming you're using Hive2 here (with LLAP) and LLAP kinda sucks for ETL workloads, but this is a different problem.
> PARTITIONED BY (DATAPASSAGGIO string, ORAPASSAGGIO string)
> CLUSTERED BY (ID_TICKETTYPE, ID_PERSONTYPE, NPOOLNR, NKASSANR) INTO 8 BUCKETS
> STORED AS ORC
...
> Total number of partitions is 137k.
20Gb divided by 137k makes for very poorly written ORC files, because I'd guess that it has too few rows in a file (will be much smaller than 1 HDFS block) - partitioning this fine is actually a performance issue on compile time.
You can make this insert work by changing the insert shuffle mechanism (run an explain with/without to see the difference).
set hive.optimize.sort.dynamic.partition=true; -- https://issues.apache.org/jira/browse/HIVE-6455
But I suspect you will be very disappointed by the performance of the read queries after this insert.
> ,NPOOLNR decimal(4,0)
> ,NZUTRNR decimal(3,0)
> ,NKASSANR decimal(3,0)
> ,ID_TICKETTYPE decimal(5,0)
> ,ID_PERSONTYPE decimal(6,0)
> ,ID_TICKETPERSONTYPEDEF decimal(6,0)
That's also going to hurt - your schema raises a lot of red-flags that I find people do when they first migrated to hive.
https://www.slideshare.net/t3rmin4t0r/data-organization-hive-meetup/
In general, you need to fix the partition count, bucketing structure (how clustered by does not "cluster", you need another "sorted by"), zero scale decimals.
Can you try running with (& see what your query read-perf looks like)
https://gist.github.com/t3rmin4t0r/087b61f79514673c307bb9a88327a4db
Cheers,
Gopal