Exercise 04 - Partition order_items with order_month

itversity · December 14, 2016, 9:59am

Sample code

CREATE TABLE orders_dg (
order_id int,
order_date timestamp,
order_customer_id int,
order_status string)
PARTITIONED BY (order_month string)
STORED AS avro
;

alter table orders_dg add partition (order_month = ‘2014-01’);

insert into table orders_dg
partition (order_month = ‘2014-01’)
select * from orders where substr(order_date, 1, 7) = ‘2014-01’;

set hive.exec.dynamic.partition.mode=nonstrict;

insert into table orders_dg partition (order_month)
select order_id, order_date, order_customer_id, order_status,
substr(order_date, 1, 7) order_month
from orders where substr(order_date, 1, 7) != ‘2014-01’;

Problem:

Create partitioned order_items table by order_month
You need to join orders and order_items on order_id and order_item_order_id

You have to provide

Output of describe formatted
Output of dfs -ls command
Output of select * from your_table_name limit 10;

tpulimoottil · December 14, 2016, 9:51am

Output of describe formatted

col_name data_type comment

order_items_id int
order_item_order_id int
order_item_product_id int
order_item_quantity int
order_item_subtotal float
order_item_product_price float

Partition Information

col_name data_type comment

order_month string

Detailed Table Information

Database: tpulimoottil
Owner: tpulimoottil
CreateTime: Wed Dec 14 04:29:15 EST 2016
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://nn01.itversity.com:8020/apps/hive/warehouse/tpulimoottil.db/order_items_part
Table Type: MANAGED_TABLE
Table Parameters:
transient_lastDdlTime 1481707755

Storage Information

SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
InputFormat: org.apache.hadoop.mapred.TextInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
Compressed: No
Num Buckets: -1
Bucket Columns: []
Sort Columns: []
Storage Desc Params:
field.delim ,
serialization.format ,
Time taken: 0.364 seconds, Fetched: 37 row(s)

Output of dfs -ls command

Found 13 items
drwxrwxrwx - tpulimoottil hdfs 0 2016-12-14 04:48 /apps/hive/warehouse/tpulimoottil.db/order_items_part/order_month=2013-07
drwxrwxrwx - tpulimoottil hdfs 0 2016-12-14 04:48 /apps/hive/warehouse/tpulimoottil.db/order_items_part/order_month=2013-08
drwxrwxrwx - tpulimoottil hdfs 0 2016-12-14 04:48 /apps/hive/warehouse/tpulimoottil.db/order_items_part/order_month=2013-09
drwxrwxrwx - tpulimoottil hdfs 0 2016-12-14 04:48 /apps/hive/warehouse/tpulimoottil.db/order_items_part/order_month=2013-10
drwxrwxrwx - tpulimoottil hdfs 0 2016-12-14 04:48 /apps/hive/warehouse/tpulimoottil.db/order_items_part/order_month=2013-11
drwxrwxrwx - tpulimoottil hdfs 0 2016-12-14 04:48 /apps/hive/warehouse/tpulimoottil.db/order_items_part/order_month=2013-12
drwxrwxrwx - tpulimoottil hdfs 0 2016-12-14 04:48 /apps/hive/warehouse/tpulimoottil.db/order_items_part/order_month=2014-01
drwxrwxrwx - tpulimoottil hdfs 0 2016-12-14 04:48 /apps/hive/warehouse/tpulimoottil.db/order_items_part/order_month=2014-02
drwxrwxrwx - tpulimoottil hdfs 0 2016-12-14 04:48 /apps/hive/warehouse/tpulimoottil.db/order_items_part/order_month=2014-03
drwxrwxrwx - tpulimoottil hdfs 0 2016-12-14 04:48 /apps/hive/warehouse/tpulimoottil.db/order_items_part/order_month=2014-04
drwxrwxrwx - tpulimoottil hdfs 0 2016-12-14 04:48 /apps/hive/warehouse/tpulimoottil.db/order_items_part/order_month=2014-05
drwxrwxrwx - tpulimoottil hdfs 0 2016-12-14 04:48 /apps/hive/warehouse/tpulimoottil.db/order_items_part/order_month=2014-06
drwxrwxrwx - tpulimoottil hdfs 0 2016-12-14 04:48 /apps/hive/warehouse/tpulimoottil.db/order_items_part/order_month=2014-07

Output of select * from your_table_name limit 10;

OK
1 1 957 1 299.98 299.98 2013-07
2 2 1073 1 199.99 199.99 2013-07
3 2 502 5 250.0 50.0 2013-07
4 2 403 1 129.99 129.99 2013-07
5 4 897 2 49.98 24.99 2013-07
6 4 365 5 299.95 59.99 2013-07
7 4 502 3 150.0 50.0 2013-07
8 4 1014 4 199.92 49.98 2013-07
9 5 957 1 299.98 299.98 2013-07
10 5 365 5 299.95 59.99 2013-07
Time taken: 0.227 seconds, Fetched: 10 row(s)

chethan121212 · December 14, 2016, 9:57am

hive> DESCRIBE FORMATTED order_items_pt;
OK

col_name data_type comment

order_item_id int
order_item_order_id int
order_item_product_id int
order_item_quantity int
order_item_subtotal float
order_item_product_price float

Partition Information

col_name data_type comment

order_month string

Detailed Table Information

Database: chethan121212
Owner: chethan121212
CreateTime: Wed Dec 14 04:30:00 EST 2016
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://nn01.itversity.com:8020/apps/hive/warehouse/chethan121212.db/order_items_pt
Table Type: MANAGED_TABLE
Table Parameters:
transient_lastDdlTime 1481707800

Storage Information

SerDe Library: org.apache.hadoop.hive.serde2.avro.AvroSerDe
InputFormat: org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat
Compressed: No
Num Buckets: -1
Bucket Columns: []
Sort Columns: []
Storage Desc Params:
serialization.format 1
Time taken: 0.357 seconds, Fetched: 36 row(s)

strong text
Found 13 items
drwxrwxrwx - chethan121212 hdfs 0 2016-12-14 04:55 /apps/hive/warehouse/chethan121212.db/order_items_pt/order_month=2013-07
drwxrwxrwx - chethan121212 hdfs 0 2016-12-14 04:55 /apps/hive/warehouse/chethan121212.db/order_items_pt/order_month=2013-08
drwxrwxrwx - chethan121212 hdfs 0 2016-12-14 04:55 /apps/hive/warehouse/chethan121212.db/order_items_pt/order_month=2013-09
drwxrwxrwx - chethan121212 hdfs 0 2016-12-14 04:55 /apps/hive/warehouse/chethan121212.db/order_items_pt/order_month=2013-10
drwxrwxrwx - chethan121212 hdfs 0 2016-12-14 04:55 /apps/hive/warehouse/chethan121212.db/order_items_pt/order_month=2013-11
drwxrwxrwx - chethan121212 hdfs 0 2016-12-14 04:55 /apps/hive/warehouse/chethan121212.db/order_items_pt/order_month=2013-12
drwxrwxrwx - chethan121212 hdfs 0 2016-12-14 04:55 /apps/hive/warehouse/chethan121212.db/order_items_pt/order_month=2014-01
drwxrwxrwx - chethan121212 hdfs 0 2016-12-14 04:55 /apps/hive/warehouse/chethan121212.db/order_items_pt/order_month=2014-02
drwxrwxrwx - chethan121212 hdfs 0 2016-12-14 04:55 /apps/hive/warehouse/chethan121212.db/order_items_pt/order_month=2014-03
drwxrwxrwx - chethan121212 hdfs 0 2016-12-14 04:55 /apps/hive/warehouse/chethan121212.db/order_items_pt/order_month=2014-04
drwxrwxrwx - chethan121212 hdfs 0 2016-12-14 04:55 /apps/hive/warehouse/chethan121212.db/order_items_pt/order_month=2014-05
drwxrwxrwx - chethan121212 hdfs 0 2016-12-14 04:55 /apps/hive/warehouse/chethan121212.db/order_items_pt/order_month=2014-06
drwxrwxrwx - chethan121212 hdfs 0 2016-12-14 04:55 /apps/hive/warehouse/chethan121212.db/order_items_pt/order_month=2014-07

select * from order_items_pt limit 10;
OK
1 1 957 1 299.98 299.98 2013-07
2 2 1073 1 199.99 199.99 2013-07
3 2 502 5 250.0 50.0 2013-07
4 2 403 1 129.99 129.99 2013-07
5 4 897 2 49.98 24.99 2013-07
6 4 365 5 299.95 59.99 2013-07
7 4 502 3 150.0 50.0 2013-07
8 4 1014 4 199.92 49.98 2013-07
9 5 957 1 299.98 299.98 2013-07
10 5 365 5 299.95 59.99 2013-07

arun_g · December 14, 2016, 9:56am

hive> describe formatted order_items_partition;
OK

col_name data_type comment

order_item_id int
order_item_order_id int
order_item_product_id int
order_item_quantity int
order_item_subtotal float
order_item_product_price float

Detailed Table Information

Database: arun
Owner: aruncse11
CreateTime: Wed Dec 14 04:52:08 EST 2016
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://nn01.itversity.com:8020/apps/hive/warehouse/arun.db/order_items_partition
Table Type: MANAGED_TABLE
Table Parameters:
COLUMN_STATS_ACCURATE {“BASIC_STATS”:“true”}
numFiles 1
numRows 14666
rawDataSize 0
totalSize 338236
transient_lastDdlTime 1481709129

Storage Information

SerDe Library: org.apache.hadoop.hive.serde2.avro.AvroSerDe
InputFormat: org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat
Compressed: No
Num Buckets: -1
Bucket Columns: []
Sort Columns: []
Storage Desc Params:
field.delim ,
serialization.format ,
Time taken: 0.474 seconds, Fetched: 37 row(s)

dfs -ls hdfs://nn01.itversity.com:8020/apps/hive/warehouse/arun.db/order_items_partition;
Found 1 items
-rwxrwxrwx 3 aruncse11 hdfs 338236 2016-12-14 04:52 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/arun.db/order_items_partition/000000_0
hive>

hive> select * from order_items_partition limit 5;
OK
64768 25876 365 2 119.98 59.99
64769 25877 191 1 99.99 99.99
64770 25877 191 4 399.96 99.99
64771 25878 191 1 99.99 99.99
64772 25878 823 2 103.98 51.99
Time taken: 0.245 seconds, Fetched: 5 row(s)

saswata_ranjan_Nayak · December 14, 2016, 10:41am

hive> describe formatted orders_part;
OK

col_name data_type comment

order_item_id int
order_item_order_id int
order_item_product_id int
order_item_quantity int
order_item_subtotal float
order_item_product_price float

Partition Information

col_name data_type comment

order_month string

Detailed Table Information

Database: saswat232
Owner: saswat232
CreateTime: Wed Dec 14 04:43:56 EST 2016
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://nn01.itversity.com:8020/apps/hive/warehouse/saswat232.db/orders_part
Table Type: MANAGED_TABLE
Table Parameters:
transient_lastDdlTime 1481708636

Storage Information

SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
InputFormat: org.apache.hadoop.mapred.TextInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
Compressed: No
Num Buckets: -1
Bucket Columns: []
Sort Columns: []
Storage Desc Params:
serialization.format 1
Time taken: 0.446 seconds, Fetched: 36 row(s)

dfs -ls hdfs://nn01.itversity.com:8020/apps/hive/warehouse/saswat232.db/orders_part
bash: dfs: command not found…
[saswat232@gw01 ~]$ hadoop dfs -ls hdfs://nn01.itversity.com:8020/apps/hive/warehouse/saswat232.db/orders_part
DEPRECATED: Use of this script to execute hdfs command is deprecated.
Instead use the hdfs command for it.

Found 14 items
drwxrwxrwx - saswat232 hdfs 0 2016-12-14 05:08 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/saswat232.db/orders_part/.hive-staging_hive_2016-12-14_04-43-56_139_322255800219801106-433
drwxrwxrwx - saswat232 hdfs 0 2016-12-14 05:08 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/saswat232.db/orders_part/order_month=2013-07
drwxrwxrwx - saswat232 hdfs 0 2016-12-14 05:08 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/saswat232.db/orders_part/order_month=2013-08
drwxrwxrwx - saswat232 hdfs 0 2016-12-14 05:08 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/saswat232.db/orders_part/order_month=2013-09
drwxrwxrwx - saswat232 hdfs 0 2016-12-14 05:08 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/saswat232.db/orders_part/order_month=2013-10
drwxrwxrwx - saswat232 hdfs 0 2016-12-14 05:08 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/saswat232.db/orders_part/order_month=2013-11
drwxrwxrwx - saswat232 hdfs 0 2016-12-14 05:08 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/saswat232.db/orders_part/order_month=2013-12
drwxrwxrwx - saswat232 hdfs 0 2016-12-14 05:08 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/saswat232.db/orders_part/order_month=2014-01
drwxrwxrwx - saswat232 hdfs 0 2016-12-14 05:08 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/saswat232.db/orders_part/order_month=2014-02
drwxrwxrwx - saswat232 hdfs 0 2016-12-14 05:08 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/saswat232.db/orders_part/order_month=2014-03
drwxrwxrwx - saswat232 hdfs 0 2016-12-14 05:08 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/saswat232.db/orders_part/order_month=2014-04
drwxrwxrwx - saswat232 hdfs 0 2016-12-14 05:08 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/saswat232.db/orders_part/order_month=2014-05
drwxrwxrwx - saswat232 hdfs 0 2016-12-14 05:08 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/saswat232.db/orders_part/order_month=2014-06
drwxrwxrwx - saswat232 hdfs 0 2016-12-14 05:08 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/saswat232.db/orders_part/order_month=2014-07

select * from orders_part limit 10;
OK
1 1 957 1 299.98 299.98 2013-07
2 2 1073 1 199.99 199.99 2013-07
3 2 502 5 250.0 50.0 2013-07
4 2 403 1 129.99 129.99 2013-07
5 4 897 2 49.98 24.99 2013-07
6 4 365 5 299.95 59.99 2013-07
7 4 502 3 150.0 50.0 2013-07
8 4 1014 4 199.92 49.98 2013-07
9 5 957 1 299.98 299.98 2013-07
10 5 365 5 299.95 59.99 2013-07
Time taken: 0.231 seconds, Fetched: 10 row(s)

infosnehasish · December 14, 2016, 10:29am

show create table order_items;

CREATE TABLE order_items_part(
order_item_id int,
order_item_order_id int,
order_item_product_id int,
order_item_quantity tinyint,
order_item_subtotal float,
order_item_product_price float
)
partitioned by (order_month string);

insert into table order_items_part
partition (order_month)
select order_item_id,
order_item_order_id ,
order_item_product_id,
order_item_quantity ,
order_item_subtotal ,
order_item_product_price,
substr(order_date,1,7) order_month
from order_items join orders
on order_item_order_id = order_id;

Sumanth_Sharma · December 14, 2016, 10:31am

insert into order_items_partion PARTITION(order_month)
select odi.order_item_id,odi.order_item_order_id,
odi.order_item_product_id,odi.order_item_quantity,
odi.order_item_subtotal,odi.order_item_product_price,
ord.order_date,ord.order_customer_id,ord.order_status,substr(ord.order_date,1,7) as order_month
from order_items odi
join orders ord on odi.order_item_order_id=ord.order_id;

hive> DESCRIBE FORMATTED order_items_partion;
OK

col_name data_type comment

order_item_id int
order_item_order_id int
order_item_product_id int
order_item_quantity int
order_item_subtotal float
order_item_product_price float
order_date timestamp
order_customer_id int
order_status string

Partition Information

col_name data_type comment

order_month string

Detailed Table Information

Database: sumanthsharma21
Owner: sumanthsharma21
CreateTime: Wed Dec 14 04:57:43 EST 2016
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://nn01.itversity.com:8020/apps/hive/warehouse/sumanthsharma21.db/order_items_partion
Table Type: MANAGED_TABLE
Table Parameters:
transient_lastDdlTime 1481709463

Storage Information

SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
InputFormat: org.apache.hadoop.mapred.TextInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
Compressed: No
Num Buckets: -1
Bucket Columns: []
Sort Columns: []
Storage Desc Params:
serialization.format 1
Time taken: 0.388 seconds, Fetched: 39 row(s)

hive> select * from order_items_partion limit 10;
OK
1 1 957 1 299.98 299.98 2013-07-25 00:00:00 11599 CLOSED 2013-07
2 2 1073 1 199.99 199.99 2013-07-25 00:00:00 256 PENDING_PAYMENT 2013-07
3 2 502 5 250.0 50.0 2013-07-25 00:00:00 256 PENDING_PAYMENT 2013-07
4 2 403 1 129.99 129.99 2013-07-25 00:00:00 256 PENDING_PAYMENT 2013-07
5 4 897 2 49.98 24.99 2013-07-25 00:00:00 8827 CLOSED 2013-07
6 4 365 5 299.95 59.99 2013-07-25 00:00:00 8827 CLOSED 2013-07
7 4 502 3 150.0 50.0 2013-07-25 00:00:00 8827 CLOSED 2013-07
8 4 1014 4 199.92 49.98 2013-07-25 00:00:00 8827 CLOSED 2013-07
9 5 957 1 299.98 299.98 2013-07-25 00:00:00 11318 COMPLETE 2013-07
10 5 365 5 299.95 59.99 2013-07-25 00:00:00 11318 COMPLETE 2013-07
Time taken: 0.281 seconds, Fetched: 10 row(s)

[sumanthsharma21@gw01 ~]$ hadoop fs -ls /apps/hive/warehouse/sumanthsharma21.db/order_items_partion
Found 14 items
drwxrwxrwx - sumanthsharma21 hdfs 0 2016-12-14 05:26 /apps/hive/warehouse/sumanthsharma21.db/order_items_partion/.hive-staging_hive_2016-12-14_05-17-18_250_8079560736418741001-10
drwxrwxrwx - sumanthsharma21 hdfs 0 2016-12-14 05:27 /apps/hive/warehouse/sumanthsharma21.db/order_items_partion/order_month=2013-07
drwxrwxrwx - sumanthsharma21 hdfs 0 2016-12-14 05:27 /apps/hive/warehouse/sumanthsharma21.db/order_items_partion/order_month=2013-08
drwxrwxrwx - sumanthsharma21 hdfs 0 2016-12-14 05:26 /apps/hive/warehouse/sumanthsharma21.db/order_items_partion/order_month=2013-09
drwxrwxrwx - sumanthsharma21 hdfs 0 2016-12-14 05:26 /apps/hive/warehouse/sumanthsharma21.db/order_items_partion/order_month=2013-10
drwxrwxrwx - sumanthsharma21 hdfs 0 2016-12-14 05:27 /apps/hive/warehouse/sumanthsharma21.db/order_items_partion/order_month=2013-11
drwxrwxrwx - sumanthsharma21 hdfs 0 2016-12-14 05:26 /apps/hive/warehouse/sumanthsharma21.db/order_items_partion/order_month=2013-12
drwxrwxrwx - sumanthsharma21 hdfs 0 2016-12-14 05:27 /apps/hive/warehouse/sumanthsharma21.db/order_items_partion/order_month=2014-01
drwxrwxrwx - sumanthsharma21 hdfs 0 2016-12-14 05:26 /apps/hive/warehouse/sumanthsharma21.db/order_items_partion/order_month=2014-02
drwxrwxrwx - sumanthsharma21 hdfs 0 2016-12-14 05:26 /apps/hive/warehouse/sumanthsharma21.db/order_items_partion/order_month=2014-03
drwxrwxrwx - sumanthsharma21 hdfs 0 2016-12-14 05:27 /apps/hive/warehouse/sumanthsharma21.db/order_items_partion/order_month=2014-04
drwxrwxrwx - sumanthsharma21 hdfs 0 2016-12-14 05:27 /apps/hive/warehouse/sumanthsharma21.db/order_items_partion/order_month=2014-05
drwxrwxrwx - sumanthsharma21 hdfs 0 2016-12-14 05:26 /apps/hive/warehouse/sumanthsharma21.db/order_items_partion/order_month=2014-06
drwxrwxrwx - sumanthsharma21 hdfs 0 2016-12-14 05:27 /apps/hive/warehouse/sumanthsharma21.db/order_items_partion/order_month=2014-07

Farhan_Misarwala · December 14, 2016, 10:34am

#Output of describe formatted

hive> describe formatted order_items_partitioned;
OK
col_name data_type comment

order_item_id int
order_item_order_id int
order_item_product_id int
order_item_quantity int
order_item_subtotal float
order_item_product_price float

Partition Information
col_name data_type comment

order_month string

Detailed Table Information
Database: farhan
Owner: farhanmisarwala
CreateTime: Wed Dec 14 04:47:56 EST 2016
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://nn01.itversity.com:8020/apps/hive/warehouse/farhan.db/order_items_partitioned
Table Type: MANAGED_TABLE
Table Parameters:
transient_lastDdlTime 1481708876

Storage Information
SerDe Library: org.apache.hadoop.hive.serde2.avro.AvroSerDe
InputFormat: org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat
Compressed: No
Num Buckets: -1
Bucket Columns: []
Sort Columns: []
Storage Desc Params:
serialization.format 1
Time taken: 0.361 seconds, Fetched: 36 row(s)

#Output of dfs -ls command

[farhanmisarwala@gw01 ~]$ hadoop fs -ls hdfs://nn01.itversity.com:8020/apps/hive/warehouse/farhan.db/order_items_partitioned
Found 14 items
drwxrwxrwx - farhanmisarwala hdfs 0 2016-12-14 04:59 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/farhan.db/order_items_partitioned/.hive-staging_hive_2016-12-14_04-59-48_534_1046843676098482550-1
drwxrwxrwx - farhanmisarwala hdfs 0 2016-12-14 05:26 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/farhan.db/order_items_partitioned/order_month=2013-07
drwxrwxrwx - farhanmisarwala hdfs 0 2016-12-14 05:26 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/farhan.db/order_items_partitioned/order_month=2013-08
drwxrwxrwx - farhanmisarwala hdfs 0 2016-12-14 05:26 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/farhan.db/order_items_partitioned/order_month=2013-09
drwxrwxrwx - farhanmisarwala hdfs 0 2016-12-14 05:26 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/farhan.db/order_items_partitioned/order_month=2013-10
drwxrwxrwx - farhanmisarwala hdfs 0 2016-12-14 05:26 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/farhan.db/order_items_partitioned/order_month=2013-11
drwxrwxrwx - farhanmisarwala hdfs 0 2016-12-14 05:26 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/farhan.db/order_items_partitioned/order_month=2013-12
drwxrwxrwx - farhanmisarwala hdfs 0 2016-12-14 05:26 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/farhan.db/order_items_partitioned/order_month=2014-01
drwxrwxrwx - farhanmisarwala hdfs 0 2016-12-14 05:26 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/farhan.db/order_items_partitioned/order_month=2014-02
drwxrwxrwx - farhanmisarwala hdfs 0 2016-12-14 05:26 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/farhan.db/order_items_partitioned/order_month=2014-03
drwxrwxrwx - farhanmisarwala hdfs 0 2016-12-14 05:26 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/farhan.db/order_items_partitioned/order_month=2014-04
drwxrwxrwx - farhanmisarwala hdfs 0 2016-12-14 05:26 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/farhan.db/order_items_partitioned/order_month=2014-05
drwxrwxrwx - farhanmisarwala hdfs 0 2016-12-14 05:26 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/farhan.db/order_items_partitioned/order_month=2014-06
drwxrwxrwx - farhanmisarwala hdfs 0 2016-12-14 05:26 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/farhan.db/order_items_partitioned/order_month=2014-07

#Output of select * from your_table_name limit 10
hive> select * from order_items_partitioned limit 10;
OK
1 1 957 1 299.98 299.98 2013-07
2 2 1073 1 199.99 199.99 2013-07
3 2 502 5 250.0 50.0 2013-07
4 2 403 1 129.99 129.99 2013-07
5 4 897 2 49.98 24.99 2013-07
6 4 365 5 299.95 59.99 2013-07
7 4 502 3 150.0 50.0 2013-07
8 4 1014 4 199.92 49.98 2013-07
9 5 957 1 299.98 299.98 2013-07
10 5 365 5 299.95 59.99 2013-07

Bourne · December 14, 2016, 10:44am

Describe Formatted:

hive> describe formatted order_items_part;
OK
col_name data_type comment

order_item_id int
order_item_order_id int
order_item_product_id int
order_item_quantity_id int
order_item_subtotal float
order_item_product_price float

Partition Information
col_name data_type comment

order_month string

Detailed Table Information
Database: jasonbourne
Owner: jasonbourne
CreateTime: Wed Dec 14 04:32:58 EST 2016
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://nn01.itversity.com:8020/apps/hive/warehouse/jasonbourne.db/order_items_part
Table Type: MANAGED_TABLE
Table Parameters:
transient_lastDdlTime 1481707978

Storage Information
SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
InputFormat: org.apache.hadoop.mapred.TextInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
Compressed: No
Num Buckets: -1
Bucket Columns: []
Sort Columns: []
Storage Desc Params:
serialization.format 1

**

dfs -ls command

**

hive> dfs -ls /apps/hive/warehouse/jasonbourne.db/order_items_part;
Found 13 items
drwxrwxrwx - jasonbourne hdfs 0 2016-12-14 05:26 /apps/hive/warehouse/jasonbourne.db/order_items_part/order_month=2013-07
drwxrwxrwx - jasonbourne hdfs 0 2016-12-14 05:26 /apps/hive/warehouse/jasonbourne.db/order_items_part/order_month=2013-08
drwxrwxrwx - jasonbourne hdfs 0 2016-12-14 05:26 /apps/hive/warehouse/jasonbourne.db/order_items_part/order_month=2013-09
drwxrwxrwx - jasonbourne hdfs 0 2016-12-14 05:26 /apps/hive/warehouse/jasonbourne.db/order_items_part/order_month=2013-10
drwxrwxrwx - jasonbourne hdfs 0 2016-12-14 05:26 /apps/hive/warehouse/jasonbourne.db/order_items_part/order_month=2013-11
drwxrwxrwx - jasonbourne hdfs 0 2016-12-14 05:26 /apps/hive/warehouse/jasonbourne.db/order_items_part/order_month=2013-12
drwxrwxrwx - jasonbourne hdfs 0 2016-12-14 05:26 /apps/hive/warehouse/jasonbourne.db/order_items_part/order_month=2014-01
drwxrwxrwx - jasonbourne hdfs 0 2016-12-14 05:26 /apps/hive/warehouse/jasonbourne.db/order_items_part/order_month=2014-02
drwxrwxrwx - jasonbourne hdfs 0 2016-12-14 05:26 /apps/hive/warehouse/jasonbourne.db/order_items_part/order_month=2014-03
drwxrwxrwx - jasonbourne hdfs 0 2016-12-14 05:26 /apps/hive/warehouse/jasonbourne.db/order_items_part/order_month=2014-04
drwxrwxrwx - jasonbourne hdfs 0 2016-12-14 05:26 /apps/hive/warehouse/jasonbourne.db/order_items_part/order_month=2014-05
drwxrwxrwx - jasonbourne hdfs 0 2016-12-14 05:26 /apps/hive/warehouse/jasonbourne.db/order_items_part/order_month=2014-06
drwxrwxrwx - jasonbourne hdfs 0 2016-12-14 05:26 /apps/hive/warehouse/jasonbourne.db/order_items_part/order_month=2014-07

**

select * from order_items_part limit 10;

**
OK
1 1 957 1 299.98 299.98 2013-07
2 2 1073 1 199.99 199.99 2013-07
3 2 502 5 250.0 50.0 2013-07
4 2 403 1 129.99 129.99 2013-07
5 4 897 2 49.98 24.99 2013-07
6 4 365 5 299.95 59.99 2013-07
7 4 502 3 150.0 50.0 2013-07
8 4 1014 4 199.92 49.98 2013-07
9 5 957 1 299.98 299.98 2013-07
10 5 365 5 299.95 59.99 2013-07

infosnehasish · December 14, 2016, 10:44am

#Output of describe formatted

hive> describe formatted order_items_part;
OK

col_name data_type comment

order_item_id int
order_item_order_id int
order_item_product_id int
order_item_quantity tinyint
order_item_subtotal float
order_item_product_price float

Partition Information

col_name data_type comment

order_month string

Detailed Table Information

Database: infosnehasish
Owner: infosnehasish
CreateTime: Wed Dec 14 04:26:09 EST 2016
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://nn01.itversity.com:8020/apps/hive/warehouse/infosnehasish.db/order_items_part
Table Type: MANAGED_TABLE
Table Parameters:
transient_lastDdlTime 1481707569

Storage Information

SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
InputFormat: org.apache.hadoop.mapred.TextInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
Compressed: No
Num Buckets: -1
Bucket Columns: []
Sort Columns: []
Storage Desc Params:
serialization.format 1
Time taken: 0.384 seconds, Fetched: 36 row(s)

#Output of dfs -ls command

[infosnehasish@gw01 tmp]$ drwxrwxrwx - infosnehasish hdfs -rwxrwxrwx 3 infosnehasish hdfs drwxrwxrwx - infosnehasish hdfs -rwxrwxrwx 3 infosnehasish hdfs drwxrwxrwx - infosnehasish hdfs -rwxrwxrwx 3 infosnehasish hdfs drwxrwxrwx - infosnehasish hdfs -rwxrwxrwx 3 infosnehasish hdfs drwxrwxrwx - infosnehasish hdfs -rwxrwxrwx 3 infosnehasish hdfs drwxrwxrwx - infosnehasish hdfs -rwxrwxrwx 3 infosnehasish hdfs drwxrwxrwx - infosnehasish hdfs -rwxrwxrwx 3 infosnehasish hdfs drwxrwxrwx - infosnehasish hdfs -rwxrwxrwx 3 infosnehasish hdfs drwxrwxrwx - infosnehasish hdfs -rwxrwxrwx 3 infosnehasish hdfs drwxrwxrwx - infosnehasish hdfs -rwxrwxrwx 3 infosnehasish hdfs drwxrwxrwx - infosnehasish hdfs -rwxrwxrwx 3 infosnehasish hdfs drwxrwxrwx - infosnehasish hdfs -rwxrwxrwx 3 infosnehasish hdfs drwxrwxrwx - infosnehasish hdfs -rwxrwxrwx 3 infosnehasish hdfs hdfs dfs -ls -R /apps/hive/warehouse/infosnehasish.db/order_items_part
0 2016-12-14 05:26 /apps/hive/warehouse/infosnehasish.db/order_items_part/order_month=2013-07
109570 2016-12-14 05:26 /apps/hive/warehouse/infosnehasish.db/order_items_part/order_month=2013-07/000000_0
0 2016-12-14 05:26 /apps/hive/warehouse/infosnehasish.db/order_items_part/order_month=2013-08
429040 2016-12-14 05:26 /apps/hive/warehouse/infosnehasish.db/order_items_part/order_month=2013-08/000000_0
0 2016-12-14 05:26 /apps/hive/warehouse/infosnehasish.db/order_items_part/order_month=2013-09
450994 2016-12-14 05:26 /apps/hive/warehouse/infosnehasish.db/order_items_part/order_month=2013-09/000000_0
0 2016-12-14 05:26 /apps/hive/warehouse/infosnehasish.db/order_items_part/order_month=2013-10
414571 2016-12-14 05:26 /apps/hive/warehouse/infosnehasish.db/order_items_part/order_month=2013-10/000000_0
0 2016-12-14 05:26 /apps/hive/warehouse/infosnehasish.db/order_items_part/order_month=2013-11
498819 2016-12-14 05:26 /apps/hive/warehouse/infosnehasish.db/order_items_part/order_month=2013-11/000000_0
0 2016-12-14 05:26 /apps/hive/warehouse/infosnehasish.db/order_items_part/order_month=2013-12
462217 2016-12-14 05:26 /apps/hive/warehouse/infosnehasish.db/order_items_part/order_month=2013-12/000000_0
0 2016-12-14 05:26 /apps/hive/warehouse/infosnehasish.db/order_items_part/order_month=2014-01
460421 2016-12-14 05:26 /apps/hive/warehouse/infosnehasish.db/order_items_part/order_month=2014-01/000000_0
0 2016-12-14 05:26 /apps/hive/warehouse/infosnehasish.db/order_items_part/order_month=2014-02
440304 2016-12-14 05:26 /apps/hive/warehouse/infosnehasish.db/order_items_part/order_month=2014-02/000000_0
0 2016-12-14 05:26 /apps/hive/warehouse/infosnehasish.db/order_items_part/order_month=2014-03
450826 2016-12-14 05:26 /apps/hive/warehouse/infosnehasish.db/order_items_part/order_month=2014-03/000000_0
0 2016-12-14 05:26 /apps/hive/warehouse/infosnehasish.db/order_items_part/order_month=2014-04
457220 2016-12-14 05:26 /apps/hive/warehouse/infosnehasish.db/order_items_part/order_month=2014-04/000000_0
0 2016-12-14 05:26 /apps/hive/warehouse/infosnehasish.db/order_items_part/order_month=2014-05
443164 2016-12-14 05:26 /apps/hive/warehouse/infosnehasish.db/order_items_part/order_month=2014-05/000000_0
0 2016-12-14 05:26 /apps/hive/warehouse/infosnehasish.db/order_items_part/order_month=2014-06
428751 2016-12-14 05:26 /apps/hive/warehouse/infosnehasish.db/order_items_part/order_month=2014-06/000000_0
0 2016-12-14 05:26 /apps/hive/warehouse/infosnehasish.db/order_items_part/order_month=2014-07
362983 2016-12-14 05:26 /apps/hive/warehouse/infosnehasish.db/order_items_part/order_month=2014-07/000000_0

#Output of select * from your_table_name limit 10;

hive> select * from order_items_part limit 10;
OK
1 1 957 1 299.98 299.98 2013-07
2 2 1073 1 199.99 199.99 2013-07
3 2 502 5 250.0 50.0 2013-07
4 2 403 1 129.99 129.99 2013-07
5 4 897 2 49.98 24.99 2013-07
6 4 365 5 299.95 59.99 2013-07
7 4 502 3 150.0 50.0 2013-07
8 4 1014 4 199.92 49.98 2013-07
9 5 957 1 299.98 299.98 2013-07
10 5 365 5 299.95 59.99 2013-07
Time taken: 0.388 seconds, Fetched: 10 row(s)

Parul · December 14, 2016, 10:45am

describe formatted order_items_part;
OK

col_name data_type comment

order_item_id int
order_item_order_id int
order_item_product_id int
order_item_quantity tinyint
order_item_subtotal float
order_item_product_price float

Partition Information

col_name data_type comment

order_month string

Detailed Table Information

Database: parulshine92
Owner: parulshine92
CreateTime: Wed Dec 14 04:26:46 EST 2016
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://nn01.itversity.com:8020/apps/hive/warehouse/parulshine92.db/order_items_part
Table Type: MANAGED_TABLE
Table Parameters:
transient_lastDdlTime 1481707606

Storage Information

SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
InputFormat: org.apache.hadoop.mapred.TextInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
Compressed: No
Num Buckets: -1
Bucket Columns: []
Sort Columns: []
Storage Desc Params:
serialization.format 1
Time taken: 0.403 seconds, Fetched: 36 row(s)

dfs -ls /apps/hive/warehouse/parulshine92.db/
> ;
Found 8 items
drwxrwxrwx - parulshine92 hdfs 0 2016-12-14 02:56 /apps/hive/warehouse/parulshine92.db/categories
drwxrwxrwx - parulshine92 hdfs 0 2016-12-14 02:51 /apps/hive/warehouse/parulshine92.db/customers
drwxrwxrwx - parulshine92 hdfs 0 2016-12-14 02:50 /apps/hive/warehouse/parulshine92.db/departments
drwxrwxrwx - parulshine92 hdfs 0 2016-12-14 01:05 /apps/hive/warehouse/parulshine92.db/employee
drwxrwxrwx - parulshine92 hdfs 0 2016-12-14 02:54 /apps/hive/warehouse/parulshine92.db/order_items
drwxrwxrwx - parulshine92 hdfs 0 2016-12-14 04:26 /apps/hive/warehouse/parulshine92.db/order_items_part
drwxrwxrwx - parulshine92 hdfs 0 2016-12-14 04:06 /apps/hive/warehouse/parulshine92.db/orders_part
drwxrwxrwx - parulshine92 hdfs 0 2016-12-14 02:42 /apps/hive/warehouse/parulshine92.db/products

select * from order_items_part limit 10;
OK
1 1 957 1 299.98 299.98 2013-07
2 2 1073 1 199.99 199.99 2013-07
3 2 502 5 250.0 50.0 2013-07
4 2 403 1 129.99 129.99 2013-07
5 4 897 2 49.98 24.99 2013-07
6 4 365 5 299.95 59.99 2013-07
7 4 502 3 150.0 50.0 2013-07
8 4 1014 4 199.92 49.98 2013-07
9 5 957 1 299.98 299.98 2013-07
10 5 365 5 299.95 59.99 2013-07

Raja_Shyam · December 14, 2016, 10:46am

hive> describe formatted orders_items_dg;
OK
col_name data_type comment

order_item_id int
order_item_order_id int
order_item_product_id int
order_item_quantity int
order_item_subtotal double
order_item_product_price double

Partition Information
col_name data_type comment

order_month string

Detailed Table Information

Database: nagellarajashyam
Owner: nagellarajashyam
CreateTime: Wed Dec 14 04:25:43 EST 2016
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://nn01.itversity.com:8020/apps/hive/warehouse/nagellarajashyam.db/orders_items_dg
Table Type: MANAGED_TABLE
Table Parameters:
transient_lastDdlTime 1481707543

Storage Information
SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
InputFormat: org.apache.hadoop.mapred.TextInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
Compressed: No
Num Buckets: -1
Bucket Columns: []
Sort Columns: []
Storage Desc Params:
serialization.format 1
Time taken: 0.363 seconds, Fetched: 36 row(s)
=========================================================

dfs -ls hdfs://nn01.itversity.com:8020/apps/hive/warehouse/nagellarajashyam.db/orders_items_dg
;
Found 12 items
drwxrwxrwx - nagellarajashyam hdfs 0 2016-12-14 05:40 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/nagellarajashyam.db/orders_items_dg/order_month=2013-07
drwxrwxrwx - nagellarajashyam hdfs 0 2016-12-14 05:40 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/nagellarajashyam.db/orders_items_dg/order_month=2013-08
drwxrwxrwx - nagellarajashyam hdfs 0 2016-12-14 05:40 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/nagellarajashyam.db/orders_items_dg/order_month=2013-09
drwxrwxrwx - nagellarajashyam hdfs 0 2016-12-14 05:40 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/nagellarajashyam.db/orders_items_dg/order_month=2013-10
drwxrwxrwx - nagellarajashyam hdfs 0 2016-12-14 05:40 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/nagellarajashyam.db/orders_items_dg/order_month=2013-11
drwxrwxrwx - nagellarajashyam hdfs 0 2016-12-14 05:40 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/nagellarajashyam.db/orders_items_dg/order_month=2013-12
drwxrwxrwx - nagellarajashyam hdfs 0 2016-12-14 05:40 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/nagellarajashyam.db/orders_items_dg/order_month=2014-02
drwxrwxrwx - nagellarajashyam hdfs 0 2016-12-14 05:40 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/nagellarajashyam.db/orders_items_dg/order_month=2014-03
drwxrwxrwx - nagellarajashyam hdfs 0 2016-12-14 05:40 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/nagellarajashyam.db/orders_items_dg/order_month=2014-04
drwxrwxrwx - nagellarajashyam hdfs 0 2016-12-14 05:40 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/nagellarajashyam.db/orders_items_dg/order_month=2014-05
drwxrwxrwx - nagellarajashyam hdfs 0 2016-12-14 05:40 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/nagellarajashyam.db/orders_items_dg/order_month=2014-06
drwxrwxrwx - nagellarajashyam hdfs 0 2016-12-14 05:40 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/nagellarajashyam.db/orders_items_dg/order_month=2014-07

=========================================================

select * from orders_items_dg limit 10;
OK
1 1 957 1 299.98 299.98 2013-07
2 2 1073 1 199.99 199.99 2013-07
3 2 502 5 250.0 50.0 2013-07
4 2 403 1 129.99 129.99 2013-07
5 4 897 2 49.98 24.99 2013-07
6 4 365 5 299.95 59.99 2013-07
7 4 502 3 150.0 50.0 2013-07
8 4 1014 4 199.92 49.98 2013-07
9 5 957 1 299.98 299.98 2013-07
10 5 365 5 299.95 59.99 2013-07
Time taken: 0.225 seconds, Fetched: 10 row(s)

catchask · December 14, 2016, 11:05am

create table order_items_part(
order_item_id int,
order_item_order_id int,
order_item_product_id int,
order_item_quantity tinyint,
order_item_subtotal float,
order_item_product_price float
)

PARTITIONED BY (order_month string)
STORED AS avro
;

insert into order_items_part partition(order_month)
> select
> oi.order_item_id,
> oi.order_item_order_id,
> oi.order_item_product_id,
> oi.order_item_quantity,
> oi.order_item_subtotal,
> oi.order_item_product_price, substr(o.order_date,1,7)
> from
> orders o
> inner join
> order_items oi
> ON o.order_id = oi.order_item_order_id ;
Query ID = catchask_20161214055450_14430669-f3ab-42f0-a1e8-9a1ced8c9ddf
Total jobs = 1
Execution log at: /tmp/catchask/catchask_20161214055450_14430669-f3ab-42f0-a1e8-9a1ced8c9ddf.log
2016-12-14 05:54:54 Starting to launch local task to process map join; maximum memory = 1046478848
2016-12-14 05:54:56 Dump the side-table for tag: 0 with group count: 68883 into file: file:/tmp/catchask/fd5e75b3-da65-4316-b3ef-2611412b6669/hive_2016-12-14_05-54-50_239_1854064839581894744-1/-local-10002/HashTable-Stage-4/MapJoin-mapfile00–.hashtable
2016-12-14 05:54:56 Uploaded 1 File to: file:/tmp/catchask/fd5e75b3-da65-4316-b3ef-2611412b6669/hive_2016-12-14_05-54-50_239_1854064839581894744-1/-local-10002/HashTable-Stage-4/MapJoin-mapfile00–.hashtable (2979673 bytes)
2016-12-14 05:54:56 End of local task; Time Taken: 1.895 sec.
Execution completed successfully
MapredLocal task succeeded
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there’s no reduce operator
Starting Job = job_1480307771710_2763, Tracking URL = http://rm01.itversity.com:8088/proxy/application_1480307771710_2763/
Kill Command = /usr/hdp/2.5.0.0-1245/hadoop/bin/hadoop job -kill job_1480307771710_2763
Hadoop job information for Stage-4: number of mappers: 1; number of reducers: 0
2016-12-14 05:55:08,037 Stage-4 map = 0%, reduce = 0%
2016-12-14 05:55:17,384 Stage-4 map = 100%, reduce = 0%, Cumulative CPU 9.94 sec
MapReduce Total cumulative CPU time: 9 seconds 940 msec
Ended Job = job_1480307771710_2763
Loading data to table catchask.order_items_part partition (order_month=null)
Time taken for load dynamic partitions : 5372
Loading partition {order_month=2014-01}
Loading partition {order_month=2014-04}
Loading partition {order_month=2013-08}
Loading partition {order_month=2013-11}
Loading partition {order_month=2014-05}
Loading partition {order_month=2013-12}
Loading partition {order_month=2014-07}
Loading partition {order_month=2014-06}
Loading partition {order_month=2013-10}
Loading partition {order_month=2013-07}
Loading partition {order_month=2014-02}
Loading partition {order_month=2014-03}
Loading partition {order_month=2013-09}
Time taken for adding to write entity : 4
Partition catchask.order_items_part{order_month=2013-07} stats: [numFiles=1, numRows=3821, totalSize=81865, rawDataSize=0]
Partition catchask.order_items_part{order_month=2013-08} stats: [numFiles=1, numRows=14268, totalSize=312176, rawDataSize=0]
Partition catchask.order_items_part{order_month=2013-09} stats: [numFiles=1, numRows=14694, totalSize=333504, rawDataSize=0]
Partition catchask.order_items_part{order_month=2013-10} stats: [numFiles=1, numRows=13216, totalSize=304824, rawDataSize=0]
Partition catchask.order_items_part{order_month=2013-11} stats: [numFiles=1, numRows=15905, totalSize=366756, rawDataSize=0]
Partition catchask.order_items_part{order_month=2013-12} stats: [numFiles=1, numRows=14729, totalSize=339683, rawDataSize=0]
Partition catchask.order_items_part{order_month=2014-01} stats: [numFiles=1, numRows=14666, totalSize=338235, rawDataSize=0]
Partition catchask.order_items_part{order_month=2014-02} stats: [numFiles=1, numRows=14034, totalSize=323675, rawDataSize=0]
Partition catchask.order_items_part{order_month=2014-03} stats: [numFiles=1, numRows=14350, totalSize=330944, rawDataSize=0]
Partition catchask.order_items_part{order_month=2014-04} stats: [numFiles=1, numRows=14192, totalSize=327317, rawDataSize=0]
Partition catchask.order_items_part{order_month=2014-05} stats: [numFiles=1, numRows=13754, totalSize=317237, rawDataSize=0]
Partition catchask.order_items_part{order_month=2014-06} stats: [numFiles=1, numRows=13305, totalSize=306881, rawDataSize=0]
Partition catchask.order_items_part{order_month=2014-07} stats: [numFiles=1, numRows=11264, totalSize=259894, rawDataSize=0]
MapReduce Jobs Launched:
Stage-Stage-4: Map: 1 Cumulative CPU: 9.94 sec HDFS Read: 5419008 HDFS Write: 3943809 SUCCESS
Total MapReduce CPU Time Spent: 9 seconds 940 msec
OK
Time taken: 36.398 seconds

hive>
> desc formatted order_items_part;
OK

col_name data_type comment

order_item_id int
order_item_order_id int
order_item_product_id int
order_item_quantity int
order_item_subtotal float
order_item_product_price float

Partition Information

col_name data_type comment

order_month string

Detailed Table Information

Database: catchask
Owner: catchask
CreateTime: Wed Dec 14 04:30:34 EST 2016
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://nn01.itversity.com:8020/apps/hive/warehouse/catchask.db/order_items_part
Table Type: MANAGED_TABLE
Table Parameters:
transient_lastDdlTime 1481707834

Storage Information

SerDe Library: org.apache.hadoop.hive.serde2.avro.AvroSerDe
InputFormat: org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat
Compressed: No
Num Buckets: -1
Bucket Columns: []
Sort Columns: []
Storage Desc Params:
serialization.format 1
Time taken: 0.361 seconds, Fetched: 36 row(s)

hdfs dfs -ls /apps/hive/warehouse/catchask.db/order_items_part
;Found 13 items
drwxrwxrwx - catchask hdfs 0 2016-12-14 05:55 /apps/hive/warehouse/catchask.db/order_items_part/order_month=2013-07
drwxrwxrwx - catchask hdfs 0 2016-12-14 05:55 /apps/hive/warehouse/catchask.db/order_items_part/order_month=2013-08
drwxrwxrwx - catchask hdfs 0 2016-12-14 05:55 /apps/hive/warehouse/catchask.db/order_items_part/order_month=2013-09
drwxrwxrwx - catchask hdfs 0 2016-12-14 05:55 /apps/hive/warehouse/catchask.db/order_items_part/order_month=2013-10
drwxrwxrwx - catchask hdfs 0 2016-12-14 05:55 /apps/hive/warehouse/catchask.db/order_items_part/order_month=2013-11
drwxrwxrwx - catchask hdfs 0 2016-12-14 05:55 /apps/hive/warehouse/catchask.db/order_items_part/order_month=2013-12
drwxrwxrwx - catchask hdfs 0 2016-12-14 05:55 /apps/hive/warehouse/catchask.db/order_items_part/order_month=2014-01
drwxrwxrwx - catchask hdfs 0 2016-12-14 05:55 /apps/hive/warehouse/catchask.db/order_items_part/order_month=2014-02
drwxrwxrwx - catchask hdfs 0 2016-12-14 05:55 /apps/hive/warehouse/catchask.db/order_items_part/order_month=2014-03
drwxrwxrwx - catchask hdfs 0 2016-12-14 05:55 /apps/hive/warehouse/catchask.db/order_items_part/order_month=2014-04
drwxrwxrwx - catchask hdfs 0 2016-12-14 05:55 /apps/hive/warehouse/catchask.db/order_items_part/order_month=2014-05
drwxrwxrwx - catchask hdfs 0 2016-12-14 05:55 /apps/hive/warehouse/catchask.db/order_items_part/order_month=2014-06
drwxrwxrwx - catchask hdfs 0 2016-12-14 05:55 /apps/hive/warehouse/catchask.db/order_items_part/order_month=2014-07

select * from order_items_part limit 10;
OK
1 1 957 1 299.98 299.98 2013-07
2 2 1073 1 199.99 199.99 2013-07
3 2 502 5 250.0 50.0 2013-07
4 2 403 1 129.99 129.99 2013-07
5 4 897 2 49.98 24.99 2013-07
6 4 365 5 299.95 59.99 2013-07
7 4 502 3 150.0 50.0 2013-07
8 4 1014 4 199.92 49.98 2013-07
9 5 957 1 299.98 299.98 2013-07
10 5 365 5 299.95 59.99 2013-07

mahesh007 · December 14, 2016, 3:13pm

Output of Describe formatted command:

hive> describe formatted order_items_part;
OK

col_name data_type comment

order_item_id int
order_item_order_id int
order_item_product_id int
order_item_quantity int
order_item_subtotal float
order_item_product_price float
order_status string

Partition Information

col_name data_type comment

order_month string

Detailed Table Information

Database: mahesh007
Owner: mahesh007
CreateTime: Wed Dec 14 06:55:00 EST 2016
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://nn01.itversity.com:8020/apps/hive/warehouse/mahes h007.db/order_items_part
Table Type: MANAGED_TABLE
Table Parameters:
transient_lastDdlTime 1481716500

Storage Information

SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
InputFormat: org.apache.hadoop.mapred.TextInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputForm at
Compressed: No
Num Buckets: -1
Bucket Columns: []
Sort Columns: []
Storage Desc Params:
field.delim ,
serialization.format ,
Time taken: 0.382 seconds, Fetched: 38 row(s)

Output of dfs -ls command:

[mahesh007@gw01 ~]$ hdfs dfs -ls hdfs://nn01.itversity.com:8020/apps/hive/warehouse/mahesh007.db/order_items_part
Found 13 items
drwxrwxrwx - mahesh007 hdfs 0 2016-12-14 06:55 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/mahesh007.db/order_items_part/order_month=2013-07
drwxrwxrwx - mahesh007 hdfs 0 2016-12-14 06:55 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/mahesh007.db/order_items_part/order_month=2013-08
drwxrwxrwx - mahesh007 hdfs 0 2016-12-14 06:55 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/mahesh007.db/order_items_part/order_month=2013-09
drwxrwxrwx - mahesh007 hdfs 0 2016-12-14 06:55 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/mahesh007.db/order_items_part/order_month=2013-10
drwxrwxrwx - mahesh007 hdfs 0 2016-12-14 06:55 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/mahesh007.db/order_items_part/order_month=2013-11
drwxrwxrwx - mahesh007 hdfs 0 2016-12-14 06:55 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/mahesh007.db/order_items_part/order_month=2013-12
drwxrwxrwx - mahesh007 hdfs 0 2016-12-14 06:55 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/mahesh007.db/order_items_part/order_month=2014-01
drwxrwxrwx - mahesh007 hdfs 0 2016-12-14 06:55 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/mahesh007.db/order_items_part/order_month=2014-02
drwxrwxrwx - mahesh007 hdfs 0 2016-12-14 06:55 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/mahesh007.db/order_items_part/order_month=2014-03
drwxrwxrwx - mahesh007 hdfs 0 2016-12-14 06:55 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/mahesh007.db/order_items_part/order_month=2014-04
drwxrwxrwx - mahesh007 hdfs 0 2016-12-14 06:55 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/mahesh007.db/order_items_part/order_month=2014-05
drwxrwxrwx - mahesh007 hdfs 0 2016-12-14 06:55 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/mahesh007.db/order_items_part/order_month=2014-06
drwxrwxrwx - mahesh007 hdfs 0 2016-12-14 06:55 hdfs://nn01.itversity.com:8020/apps/hive/warehouse/mahesh007.db/order_items_part/order_month=2014-07

Output of select 10 records from table:

hive> select * from order_items_part limit 10;
OK
1 1 957 1 299.98 299.98 CLOSED 2013-07
2 2 1073 1 199.99 199.99 PENDING_PAYMENT 2013-07
3 2 502 5 250.0 50.0 PENDING_PAYMENT 2013-07
4 2 403 1 129.99 129.99 PENDING_PAYMENT 2013-07
5 4 897 2 49.98 24.99 CLOSED 2013-07
6 4 365 5 299.95 59.99 CLOSED 2013-07
7 4 502 3 150.0 50.0 CLOSED 2013-07
8 4 1014 4 199.92 49.98 CLOSED 2013-07
9 5 957 1 299.98 299.98 COMPLETE 2013-07
10 5 365 5 299.95 59.99 COMPLETE 2013-07
Time taken: 0.571 seconds, Fetched: 10 row(s)