Hi Avdeev, in response to question 2. Bucketing is a second level of data division on the table. You can tell Hive that your data should be distributed over several files, inside your table of partition, using some fields as distribution key. So you can have partitions, what means your files are inside a directory structure, and buckets that split your data in distinct files.
See an example. You have the following data: |id |country | 1 | US | 2 | ES | 3 | ES | 4 | ES | 5 | US | 6 | US You create the table as: CREATE TABLE data (id int) PARTITIONED BY (country string) CLUSTERED BY (id) INTO 2 BUCKETS; Then the structure of data will be (bucket filenames don't have any meaning just a sample): data/country=ES/bucket.0 data/country=ES/bucket.1 data/country=US/bucket.0 data/country=US/bucket.1 Then bucket.0 should contain registers with id 1,3,5... while bucket.1 should contain 2,4,6... This can be helpful for sampling (you don't what to read all registers but just 50% of them in this sample). In that scenario you only need to read bucket.0 on each partition. Also can be useful for joins. If both tables have the same number of buckets (or an exact multiple) then the join can be done in a bucket by bucket fashion instead of a full tables join. Looking for a given id also can be optimized in this situation because you know which buckets to discard. There is another optional clause that tells a bucket should by ordered by cluster key: CLUSTER BY (id) SORTED BY (id ASC) INTO 2 BUCKETS. This is an extra optimization for joins because now not only can be done in a bucket by bucket fashion. It also can be implemented as a merge sort. Hope this was useful, Ramón Pin -----Original Message----- From: Avdeev V. M. [mailto:ls...@list.ru] Sent: sábado, 02 de junio de 2012 18:43 To: ruben.devr...@hyves.nl Cc: user@hive.apache.org Subject: Re[2]: table design and performance questions Thank for the information Ruben. 1. I found the issue https://issues.apache.org/jira/browse/HIVE-1642 does it mean that MAPJOIN hint is obsolete since 2010 and I can avoid this hint absolutely? 2. sorry for stupid questions, but I can't understand bucketing still. partitioning is ok, it is hdfs folders and I able to understand how it improve query execution. but what is bucketing in terms of storing data? 3. I embarrassed to ask such stupid questions, but is there 'how hive works' manual or something like? And again - sorry for bad English. Vyacheslav Tue, 29 May 2012 10:02:14 +0200 от Ruben de Vries <ruben.devr...@hyves.nl>: > Partitioning can greatly increase performance for WHERE clauses since hive > can omit parsing the data in the partitions which do no meet the requirement. > > For example if you partition by date (I do it by INT dateint, in which case I > set dateint to be YYYYMMDD) and you do WHERE dateint >= 20120101 then it > won't even have to touch any of the data from before 2012-01-01 and in my > case that means I don't parse the last 2 years of data, reducing the time the > query takes by about 70% :-) > > > > Buckets are the second awesome way of getting a big optimization in, > specifically for joins! If you have 2 tables you're joining onto each other > then if they're both bucketed on their join column it will also greatly > increase speed. > > Another good join optimization is MAPJOIN, if one of the tables you're > joining is rather small (below 30mb) then you can force it to MAPJOIN or you > can enable automatic mapjoin, I personally prefere explicit behavory instead > of automagic so use a hint: > > SELECT /* +MAPJOIN(the_small_table) */ fields FROM table JOIN > the_small_table, etc. > > Sorted by is for sorting within buckets, only relevant if you're doing a lot > of ordering I think. > > > > I'm assuming sequencefiles are faster, but I wouldn't really know :( need > someone else to tell us more about that ;) > > > > > > -----Original Message----- > > From: Avdeev V. M. [mailto:ls...@list.ru] > > Sent: Monday, May 28, 2012 7:17 AM > > To: user@hive.apache.org > > Subject: table design and performance questions > > > > Question from novice. > > > > Where I can read table design best practices? I have a measure table with > millions of rows and many dimension tables with less than 1000 rows each. I > can't find out the way to get optimal design of both kind of tables. Is there > performance tuning guides or performance FAQ? > > > > Specifically > > 1) PARTITIONED BY, CLUSTERED BY, SORTED BY statements. In which cases using > these statements make sense? > > 2) DDL language manual says 'This can improve performance on certain kinds of > queries.' about CLUSTERED BY statement. What kind of queries can be improved? > > 3) What is preferable - SEQUENCEFILE, RCFILE or TEXTFILE - in terms of > performance? What aspects should be taken into account when choosing a file > format? > > 4) Compressed storage article says 'Keeping data compressed in Hive tables > has, in some cases, known to give better performance that uncompressed > storage;' and again - What is these cases? > > > > Thanks! > > Vyacheslav > > ________________________________ Subject to local law, communications with Accenture and its affiliates including telephone calls and emails (including content), may be monitored by our systems for the purposes of security and the assessment of internal compliance with Accenture policy. ______________________________________________________________________________________ www.accenture.com
