Hive - Table Partitions
Goal: We want to create a partitioned table based on year and month.
Create a hive table based on raw logs from apache web server. You can follow this blog to find out the steps.
http://blog.einext.com/hadoop/hive-table-using-regex-serde
hive> select host, request, size from weblogs limit 10;
+----------------+-------------------------------+--------+--+
| host | time | size |
+----------------+-------------------------------+--------+--+
| 66.249.67.3 | [20/Jul/2009:20:12:22 -0700] | 5 |
| 66.249.67.3 | [20/Jul/2009:20:12:25 -0700] | 8068 |
| 64.233.172.17 | [20/Jul/2009:20:12:26 -0700] | 2969 |
| 74.125.74.193 | [20/Jul/2009:20:13:01 -0700] | 2969 |
| 192.168.1.198 | [20/Jul/2009:20:13:18 -0700] | 17935 |
| 192.168.1.198 | [20/Jul/2009:20:13:18 -0700] | 1504 |
| 192.168.1.198 | [20/Jul/2009:20:13:19 -0700] | 146 |
| 66.249.67.3 | [20/Jul/2009:20:13:21 -0700] | 5 |
| 66.249.67.3 | [20/Jul/2009:20:13:24 -0700] | 8196 |
| 66.249.67.3 | [20/Jul/2009:20:13:29 -0700] | 6360 |
+----------------+-------------------------------+--------+--+
Create a statement to convert convert the date time stamp to a UTC datetime string.
hive> SELECT t1.*, from_unixtime(UNIX_TIMESTAMP(t1.time, '[dd/MMM/yyyy:HH:mm:ss Z]')) AS `ds` FROM weblogs t1;
Use the following command to view table creation statement for the view accesslogs_view, but this time, we want add partition information to it.
hive> show create table weblogs;
Add the partitioning details to the table creation statement like below. Then run the command to create the table in hive.
hive> CREATE TABLE `weblogs_partitioned`(
`host` string,
`identity` string,
`user` string,
`time` string,
`request` string,
`status` string,
`size` string,
`referrer` string,
`useragent` string)
PARTITIONED BY(year int, month int, day int)
STORED AS PARQUET;
Enable dynamic partitioning.
hive> SET hive.exec.dynamic.partition=true;
SET hive.exec.max.dynamic.partitions=10024;
SET hive.exec.max.dynamic.partitions.pernode=10024;
SET hive.exec.dynamic.partition.mode=non-strict;
Run the following statement to load the data into partitioned table.
hive> INSERT INTO TABLE weblogs_partitioned
PARTITION (year,month, day)
SELECT host
,identity
,`user`
,TIME
,request
,STATUS
,size
,referrer
,useragent
,year(ds)
,month(ds)
,day(ds)
FROM (SELECT *, from_unixtime(UNIX_TIMESTAMP(TIME, '[dd/MMM/yyyy:HH:mm:ss Z]')) AS ds
FROM weblogs) t;
Verify the dir structure in the HDFS. Drill down to a few files to check.
$ hadoop fs -ls -R /user/hive/warehouse/weblogs_partitioned
drwxrwxrwx - cloudera supergroup 0 2016-08-30 10:20 /user/hive/warehouse/weblogs_partitioned/year=2009
drwxrwxrwx - cloudera supergroup 0 2016-08-30 10:20 /user/hive/warehouse/weblogs_partitioned/year=2009/month=10
drwxrwxrwx - cloudera supergroup 0 2016-08-30 10:20 /user/hive/warehouse/weblogs_partitioned/year=2009/month=10/day=1
-rwxrwxrwx 1 cloudera supergroup 447901 2016-08-30 10:20 /user/hive/warehouse/weblogs_partitioned/year=2009/month=10/day=1/000001_0
drwxrwxrwx - cloudera supergroup 0 2016-08-30 10:20 /user/hive/warehouse/weblogs_partitioned/year=2009/month=7
drwxrwxrwx - cloudera supergroup 0 2016-08-30 10:20 /user/hive/warehouse/weblogs_partitioned/year=2009/month=7/day=20
-rwxrwxrwx 1 cloudera supergroup 337404 2016-08-30 10:20 /user/hive/warehouse/weblogs_partitioned/year=2009/month=7/day=20/000005_0
drwxrwxrwx - cloudera supergroup 0 2016-08-30 10:20 /user/hive/warehouse/weblogs_partitioned/year=2009/month=7/day=21
-rwxrwxrwx 1 cloudera supergroup 2202305 2016-08-30 10:20 /user/hive/warehouse/weblogs_partitioned/year=2009/month=7/day=21/000005_0
drwxrwxrwx - cloudera supergroup 0 2016-08-30 10:20 /user/hive/warehouse/weblogs_partitioned/year=2009/month=7/day=22
-rwxrwxrwx 1 cloudera supergroup 2221714 2016-08-30 10:20 /user/hive/warehouse/weblogs_partitioned/year=2009/month=7/day=22/000005_0
drwxrwxrwx - cloudera supergroup 0 2016-08-30 10:20 /user/hive/warehouse/weblogs_partitioned/year=2009/month=7/day=23
-rwxrwxrwx 1 cloudera supergroup 2123367 2016-08-30 10:20 /user/hive/warehouse/weblogs_partitioned/year=2009/month=7/day=23/000005_0
drwxrwxrwx - cloudera supergroup 0 2016-08-30 10:20 /user/hive/warehouse/weblogs_partitioned/year=2009/month=7/day=24
-rwxrwxrwx 1 cloudera supergroup 1631330 2016-08-30 10:20 /user/hive/warehouse/weblogs_partitioned/year=2009/month=7/day=24/000005_0
.... (truncated output)
To see the performance difference due to table partition, compare the execution time of the following two queries
hive> select count(*) from weblogs_partitioned where year = 2009 and month = 10 and day = 1;
hive> select count(*) from (SELECT t1.*, from_unixtime(UNIX_TIMESTAMP(t1.time, '[dd/MMM/yyyy:HH:mm:ss Z]')) AS `ds` FROM weblogs t1) t2 where year(t2.ds) = 2009 and month(t2.ds) = 10 and day(t2.ds) = 1;
Compare the number of bytes read from HDFS to run a sample query - find distinct ip addresses for year = 2009 and month = 7
Query on the non_partitioned table
hive> SELECT count(DISTINCT host) FROM weblogs
where year(from_unixtime(UNIX_TIMESTAMP(time, '[dd/MMM/yyyy:HH:mm:ss Z]'))) = 2009 and
month(from_unixtime(UNIX_TIMESTAMP(time, '[dd/MMM/yyyy:HH:mm:ss Z]'))) = 7
HDFS Number of bytes read 5,303,409
Query on the partitioned table
hive> SELECT count(DISTINCT host) FROM weblogs_partitioned where year = 2009 and month = 7
HDFS Number of bytes read 196,398
The number of bytes reads for the same query on the partitioned tabled is much less.
You can find the "Number of bytes read" counter from job history server.