Hive Bucketing Example

Prerequisite: create stocks table as describe in the first portion of this article.

Create table by with partition by year and bucket on symbol column.

hive> CREATE EXTERNAL TABLE `stocks_bucket`(

`tr_date` string,

`open` double,

`high` double,

`low` double,

`close` double,

`volume` double,

`adjclose` double,

`symbol` string)

PARTITIONED BY(year int)

CLUSTERED BY (`symbol`) INTO 4 BUCKETS;

Before loading data into bucketed table, set the right number of reducers or set the hive configuration that will determine the number reducers by its own.

hive> set map.reduce.tasks = 4; -- This number should be equal to the number of buckets

set hive.exec.dynamic.partition=true; -- Required if you want to create dynamic partitioning using insert into statement

set hive.enforce.bucketing = true; -- This is recommended

Populate the stocks_bucket table with the data from stocks table

INSERT INTO `stocks_bucket`

PARTITION (year)

SELECT

`tr_date`,

`open`,

`high`,

`low`,

`close`,

`volume`,

`adjclose`,

`symbol`,

year(tr_date) FROM stocks

View the directory of stocks_bucket table.

$ hadoop fs -ls /user/hive/warehouse/stocks_bucket

Found 19 items

drwxrwxrwt - cloudera cloudera 1 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/.hive-staging_hive_2018-08-28_03-27-52_161_558394225296515089-1

drwx------ - cloudera cloudera 1 2018-08-28 03:27 /user/hive/warehouse/stocks_bucket/_scratchdir_hive_2018-08-28_03-27-52_161_558394225296515089-14

drwxrwxrwt - cloudera cloudera 4 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2000

drwxrwxrwt - cloudera cloudera 4 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2001

drwxrwxrwt - cloudera cloudera 4 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2002

drwxrwxrwt - cloudera cloudera 4 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2003

drwxrwxrwt - cloudera cloudera 4 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2004

drwxrwxrwt - cloudera cloudera 4 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2005

drwxrwxrwt - cloudera cloudera 4 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2006

drwxrwxrwt - cloudera cloudera 4 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2007

drwxrwxrwt - cloudera cloudera 4 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2008

drwxrwxrwt - cloudera cloudera 4 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2009

drwxrwxrwt - cloudera cloudera 4 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2010

drwxrwxrwt - cloudera cloudera 4 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2011

drwxrwxrwt - cloudera cloudera 4 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2012

drwxrwxrwt - cloudera cloudera 4 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2013

drwxrwxrwt - cloudera cloudera 4 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2014

drwxrwxrwt - cloudera cloudera 4 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2015

drwxrwxrwt - cloudera cloudera 4 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2016

View the directory for a given partition. Since we created 4 buckets based on the stocks symbol, stocks symbols will be exclusively distributed among 4 files.

$ hadoop fs -ls /user/hive/warehouse/stocks_bucket/year=2000

Found 4 items

-rwxrwxrwt 1 cloudera cloudera 812167 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2000/000000_0

-rwxrwxrwt 1 cloudera cloudera 652720 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2000/000001_0

-rwxrwxrwt 1 cloudera cloudera 851500 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2000/000002_0

-rwxrwxrwt 1 cloudera cloudera 735537 2018-08-28 03:28 /user/hive/warehouse/stocks_bucket/year=2000/000003_0

$ hadoop fs -cat /user/hive/warehouse/stocks_bucket/year=2000/000000_0 | head

2000-08-2213.37513.87513.37513.5222700.013.006862PXD

2000-08-2225.75001926.62525.43748126.6252544000.03.278705CSX

2000-11-2941.06248141.06248140.2540.9375191.23904E78.546621LOW

2000-08-2221.25000121.31250420.25000620.6874946013900.09.815813USB

2000-11-2966.812567.12563.2565.06259.82804E722.376616MSFT

2000-11-2966.12496267.43752366.12496267.1249627696800.07.741655AET

2000-11-2950.551.049.87550.0795400.019.656488TXT

2000-11-29134.5139.375133.25138.25775200.034.5625LH

2000-11-2914.37514.514.2514.4375963800.013.910117PXD

2000-11-2926.31251926.87501926.31251926.5624813726600.03.347279CSX

Since we have not specified our own field delimiter, hive used its default.

What is right number of bucket?

Each bucket should have approximately 512 MB of data. Try to choose the number of bucket ax 2 ^ n that 4, 8, 16 etc.

Benefits of bucketing

By using buckets, HQL query can easily and efficiently do data aggregation, sampling), bucket-side joins, and map-side joins (also known as hash join) data correlation and scope.

hive> select tr_date, symbol, adjclose from stocks_bucket TABLESAMPLE(BUCKET 1 OUT OF 4 ON symbol)

where year = 2016 limit 3;

+-------------+---------+-------------+--+

| tr_date | symbol | adjclose |

+-------------+---------+-------------+--+

| 2016-04-11 | NFX | 34.450001 |

| 2016-03-23 | ADS | 209.800003 |

| 2016-07-15 | HUM | 158.889999 |

+-------------+---------+-------------+--+

Which column you should bucket by?

Choose the column that is often used for joining with other tables, for example customer id, employee id, product id etc.

Reference: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL+BucketedTables