Thanks Mich for such a good explanation . I saved your email in my notes .
Thanks, Divya On 30 December 2015 at 20:20, Mich Talebzadeh <[email protected]> wrote: > Hi, > > > > I gave a generic reply to partitioning and bucketing in Hive back in April > for a similar question have a check on this. Here we go. Hope it helps > > > > > > As you may know already in RDBMS partitioning (dividing a very large table > into sub-tables conceptually) is deployed to address three areast. > > > > 1. Availability -- each partition can reside on a different > tablespace/device. Hence a problem with a tablespace/device will take out a > slice of the table's data instead of the whole thing. This does not really > ap[ply to Hive with 3 block replication as standard > > 2. Manageability -- partitioning provides a mechanism for splitting > whole table jobs into clear batches. Partition exchange can make it easier > to bulk load data. Defragging, moving older partitions to lower tier > storage, updating stats etc Most of these benefits apply to Hive as well. > Please check the docs. > > 3. Performance -- partition elimination > > > > In simplest form (excluding composite partitioning), Hive partitioning > will be similar to “range partitioning” in RDBMS. One can partition a table > (say *partitioned_table* as shown below which is batch loaded from > *non_partitioned_table*) -- by country, year, month etc. Each partition > will be stored in Hive under sub-directory *table/year/month* like below > > > > /user/hive/warehouse/scratchpad.db > */partitioned_table/country=Italy/year=2014/month=Feb* > > > > Hive does not have the concept of indexes local or global as yet. So > without partitioning a simple query in Hive will have to read the entire > table even if it is filtering a smaller result set (WHERE CLAUSE). This > becomes a bottleneck for running multiple MapReduce jobs over a large table. > So > partitioning will help localise the query by hitting the relevant > sub-directory or sub-directories only. There is another important aspect > with Hive as well. The locking granularity will be determined by the lowest > slice in the filing system (sub-directory). So entering data into the above > partition/file, will take an exclusive lock on that partition/file but > crucially the rest of partitions will be available (assuming concurrency in > Hive is enabled). > > > > > +----------+-------------+------------------------+------------------------------------+-------------+--------------+-----------------+-----------------+----------------+---------+-----------+--+ > > | lockid | database | table | > partition | lock_state | lock_type | transaction_id | > last_heartbeat | acquired_at | user | hostname | > > > +----------+-------------+------------------------+------------------------------------+-------------+--------------+-----------------+-----------------+----------------+---------+-----------+--+ > > | Lock ID | Database | Table | > Partition | State | Type | > Transaction ID | Last Hearbeat | Acquired At | User | Hostname | > > | 1711 | scratchpad | non_partitioned_table | > NULL | ACQUIRED | *SHARED_READ* | > NULL | 1428862154670 | 1428862151904 | hduser | rhes564 | > > | 1711 | scratchpad | *partitioned_table | > country=Italy/year=2014/month=Feb* | ACQUIRED | *EXCLUSIVE * | > NULL | 1428862154670 | 1428862151905 | hduser | rhes564 | > > > +----------+-------------+------------------------+------------------------------------+-------------+--------------+-----------------+-----------------+----------------+---------+-----------+--+ > > > > Now your point 2, bucketing in Hive refers to hash partitioning where a > hashing function is applied. Likewise an RDBMS, Hive will apply a linear > hashing algorithm to prevent data from clustering within specific > partitions. Hashing is very effective if the column selected for bucketing > has very high selectivity like an ID column where selectivity (*select > count(distinct(column))/count(column)* ) = 1. In this case, the created > partitions/ files will be as evenly sized as possible. In a nutshell > bucketing is a method to get data evenly distributed over many > partitions/files. One should define the number of buckets by a power of > two -- 2^n, like 2, 4, 8, 16 etc to achieve best results. Again bucketing > will help concurrency in Hive. It may even allow a *partition wise join* > i.e. a join between two tables that are bucketed on the same column with > the same number of buckets (anyone has tried this?) > > > > One more things. When one defines the number of buckets at table creation > level in Hive, the number of partitions/files will be fixed. In contrast, > with partitioning you do not have this limitation. > > > > Mich Talebzadeh > > > > *Sybase ASE 15 Gold Medal Award 2008* > > A Winning Strategy: Running the most Critical Financial Data on ASE 15 > > > http://login.sybase.com/files/Product_Overviews/ASE-Winning-Strategy-091908.pdf > > Author of the books* "A Practitioner’s Guide to Upgrading to Sybase ASE > 15", ISBN 978-0-9563693-0-7*. > > co-author *"Sybase Transact SQL Guidelines Best Practices", ISBN > 978-0-9759693-0-4* > > *Publications due shortly:* > > *Complex Event Processing in Heterogeneous Environments*, ISBN: > 978-0-9563693-3-8 > > *Oracle and Sybase, Concepts and Contrasts*, ISBN: 978-0-9563693-1-4, volume > one out shortly > > > > http://talebzadehmich.wordpress.com > > > > NOTE: The information in this email is proprietary and confidential. This > message is for the designated recipient only, if you are not the intended > recipient, you should destroy it immediately. Any information in this > message shall not be understood as given or endorsed by Peridale Technology > Ltd, its subsidiaries or their employees, unless expressly so stated. It is > the responsibility of the recipient to ensure that this email is virus > free, therefore neither Peridale Ltd, its subsidiaries nor their employees > accept any responsibility. > > > > *From:* Divya Gehlot [mailto:[email protected]] > *Sent:* 30 December 2015 10:44 > *To:* [email protected] > *Subject:* how does Hive Partitioning works ? > > > > Hi, > > I am new bee to hive and trying to understand the hive partitioning . > My files are in CSV format > Steps which I followed > CREATE EXTERNAL TABLE IF NOT EXISTS loan_depo_part(COLUMN1 String ,COLUMN2 > String ,COLUMN3 String , > COLUMN4 String,COLUMN5 > String,COLUMN6 String, > COLUMN7 Int ,COLUMN8 Int > ,COLUMN9 String , > COLUMN10 String ,COLUMN11 > String ,COLUMN12 String, > COLUMN13 String ,COLUMN14 > String , > COLUMN15 String ,COLUMN16 > String , > COLUMN17 String ,COLUMN18 > String , > COLUMN19 String ,COLUMN20 > String , > COLUMN21 String ,COLUMN22 > String ) > COMMENT 'testing Partition' > PARTITIONED BY (Year String,Month String ,Day String) > ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' > STORED AS TEXTFILE > TBLPROPERTIES ("skip.header.line.count"="1") ; > > > > ALTER TABLE loan_depo_part ADD IF NOT EXISTS PARTITION( > Year=2015,Month=01,Day=01); > > ALTER TABLE loan_depo_part PARTITION(Year=2015,Month=01,Day=01) > SET LOCATION > 'hdfs://namenode:8020/tmp/TestDivya/HiveInput/year=2015/month=01/day=01/'; > > > > Whereas my HDFS data location is > /TestDivya/HiveInput/year=2015/month=01/day=01/ > > I have few queries regarding the above partioning : > > 1. It creates the table when run the second step and gives the select > command it doesnt diplay any data > > 2. Do I need to create normal external table first and the partitioned one > next > > and then do the insert overwrite. > > Basically I am not able to understand the partioning things mentioned > above > > I followed this link > <http://deanwampler.github.io/polyglotprogramming/papers/Hive-SQLforHadoop.pdf> > > Would really appreciate the help/pointers. > > Thanks, > > Divya > > >
