Exercise 03 - Create hive table and load data

hive
#1

Sample code:

Here is the data model - Retail DB Data Model

create table orders (
order_id int,
order_date string,
order_customer_id int,
order_status string)
row format delimited fields terminated by ‘,’;

describe formatted orders;

LOAD DATA LOCAL INPATH ‘/tmp/retail_db/orders’ OVERWRITE INTO TABLE orders;

Problem:

  • Make sure you have database with your username and it is selected
  • Create 6 tables, at least one as external table
  • For all managed tables, load data from your HDFS location to tables under your database

Please provide the following information

  • Run show tables and paste the output
  • Output of metadata for 1 table (describe formatted)
  • Script to load data
  • Run query with limit 10 on all the tables - and paste for one of the tables
0 Likes

#2

show tables;
OK
categories
customers
departments
order_items
orders
products
test_table

Time taken: 0.219 seconds, Fetched: 7 row(s)


describe formatted orders;
OK

col_name data_type comment

order_id int
order_date string
order_customer_id int
order_status string

Detailed Table Information

Database: chethan121212
Owner: chethan121212
CreateTime: Wed Dec 14 02:00:49 EST 2016
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://nn01.itversity.com:8020/apps/hive/warehouse/chethan121212.db/orders
Table Type: MANAGED_TABLE
Table Parameters:
COLUMN_STATS_ACCURATE {“BASIC_STATS”:“true”}
numFiles 0
numRows 0
rawDataSize 0
totalSize 0
transient_lastDdlTime 1481698849

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.353 seconds, Fetched: 35 row(s)
hive> load data inpath ‘data/retail_db/orders’ into table orders;
Loading data to table chethan121212.orders
Table chethan121212.orders stats: [numFiles=1, numRows=0, totalSize=2999944, rawDataSize=0]
OK
Time taken: 0.86 seconds

0 Likes

#3

show tables;
OK
categories
customers
departments
order_items
orders
products

select * from saswat232.customers limit 10;
OK
1 Richard Hernandez XXXXXXXXX XXXXXXXXX 6303 Heather Plaza Brownsville TX 78521
2 Mary Barrett XXXXXXXXX XXXXXXXXX 9526 Noble Embers Ridge Littleton CO 80126
3 Ann Smith XXXXXXXXX XXXXXXXXX 3422 Blue Pioneer Bend Caguas PR 00725
4 Mary Jones XXXXXXXXX XXXXXXXXX 8324 Little Common San Marcos CA 92069
5 Robert Hudson XXXXXXXXX XXXXXXXXX "10 Crystal River Mall " Caguas PR 00725
6 Mary Smith XXXXXXXXX XXXXXXXXX 3151 Sleepy Quail Promenade Passaic NJ 07055
7 Melissa Wilcox XXXXXXXXX XXXXXXXXX 9453 High Concession Caguas PR 00725
8 Megan Smith XXXXXXXXX XXXXXXXXX 3047 Foggy Forest Plaza Lawrence MA 01841
9 Mary Perez XXXXXXXXX XXXXXXXXX 3616 Quaking Street Caguas PR 00725
10 Melissa Smith XXXXXXXXX XXXXXXXXX 8598 Harvest Beacon Plaza Stafford VA 22554

describe formatted categories;
OK

col_name data_type comment

catagory_id int
catagory_department_id int
catagory_name string

Detailed Table Information

Database: saswat232
Owner: saswat232
CreateTime: Wed Dec 14 02:17:16 EST 2016
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://nn01.itversity.com:8020/user/saswat232/retail_db/categories
Table Type: EXTERNAL_TABLE
Table Parameters:
EXTERNAL TRUE
numFiles 1
totalSize 1029
transient_lastDdlTime 1481699836

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.354 seconds, Fetched: 32 row(s)

0 Likes

#4

hive> show tables;
OK
categories
customers
departments
order_items
orders
products
test_table
Time taken: 0.217 seconds, Fetched: 7 row(s)

OK

col_name data_type comment

department_id int
department_name string

Detailed Table Information

Database: default
Owner: mail2vasant
CreateTime: Tue Dec 06 22:31:35 EST 2016
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://nn01.itversity.com:8020/apps/hive/warehouse/departments
Table Type: MANAGED_TABLE
Table Parameters:
numFiles 4
numRows 0
rawDataSize 0
totalSize 482
transient_lastDdlTime 1481473136

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: 1.148 seconds, Fetched: 32 row(s)

0 Likes

#5

output of show tables

tab_name
categories
customers
departments
employee
order_items
orders
products

output of metadata of 1 table

col_name data_type comment

“” null null
customer_id int ""
customer_fname string ""
customer_lname string ""
customer_email string ""
customer_password string ""
customer_street string ""
customer_city string ""
customer_state string ““
customer_zipcode string “”
”” null null

Detailed Table Information

Database: farhan null
Owner: farhanmisarwala null
CreateTime: Wed Dec 14 02:20:39 EST 2016 null
LastAccessTime: UNKNOWN null
Protect Mode: None null
Retention: 0 null
Location: hdfs://nn01.itversity.com:8020/apps/hive/warehouse/farhan.db/customers null
Table Type: MANAGED_TABLE null
Table Parameters: null null
"" numFiles 1
"" numRows 0
"" rawDataSize 0
"" totalSize 953719
"" transient_lastDdlTime 1481700366
"" null null

Storage Information null null

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

To load data from hdfs

LOAD DATA INPATH ‘/user/farhanmisarwala/data/retail_db/order_items’ OVERWRITE INTO TABLE order_items;
LOAD DATA INPATH ‘/user/farhanmisarwala/data/retail_db/categories’ OVERWRITE INTO TABLE categories;
LOAD DATA INPATH ‘/user/farhanmisarwala/data/retail_db/customers’ OVERWRITE INTO TABLE customers;
LOAD DATA INPATH ‘/user/farhanmisarwala/data/retail_db/products’ OVERWRITE INTO TABLE products;
LOAD DATA INPATH ‘/user/farhanmisarwala/data/retail_db/departments’ OVERWRITE INTO TABLE departments;

selecting 10 records from customers;

customers.customer_id customers.customer_fname customers.customer_lname customers.customer_email customers.customer_password customers.customer_street customers.customer_city customers.customer_state customers.customer_zipcode
1 Richard Hernandez XXXXXXXXX XXXXXXXXX 6303 Heather Plaza Brownsville TX 78521
2 Mary Barrett XXXXXXXXX XXXXXXXXX 9526 Noble Embers Ridge Littleton CO 80126
3 Ann Smith XXXXXXXXX XXXXXXXXX 3422 Blue Pioneer Bend Caguas PR 00725
4 Mary Jones XXXXXXXXX XXXXXXXXX 8324 Little Common San Marcos CA 92069
5 Robert Hudson XXXXXXXXX XXXXXXXXX "10 Crystal River Mall " Caguas PR 00725
6 Mary Smith XXXXXXXXX XXXXXXXXX 3151 Sleepy Quail Promenade Passaic NJ 07055
7 Melissa Wilcox XXXXXXXXX XXXXXXXXX 9453 High Concession Caguas PR 00725
8 Megan Smith XXXXXXXXX XXXXXXXXX 3047 Foggy Forest Plaza Lawrence MA 01841
9 Mary Perez XXXXXXXXX XXXXXXXXX 3616 Quaking Street Caguas PR 00725
10 Melissa Smith XXXXXXXXX XXXXXXXXX 8598 Harvest Beacon Plaza Stafford VA 22554

0 Likes

#6

show tables;
OK
categories
customers
departments
employee
order_item
orders
products
values__tmp__table__1
Time taken: 0.215 seconds, Fetched: 8 row(s)
hive (parulshine92)>

describe formatted orders;
OK

col_name data_type comment

order_id int
order_date string
order_customer_id int
order_status string

Detailed Table Information

Database: parulshine92
Owner: parulshine92
CreateTime: Wed Dec 14 02:15:09 EST 2016
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://nn01.itversity.com:8020/user/parulshine92/orders
Table Type: EXTERNAL_TABLE
Table Parameters:
EXTERNAL TRUE
numFiles 1
totalSize 2999944
transient_lastDdlTime 1481699719

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.347 seconds, Fetched: 33 row(s)


load data local inpath ‘/tmp/retail_db/orders’ overwrite into table orders;
Loading data to table parulshine92.orders
Table parulshine92.orders stats: [numFiles=1, totalSize=2999944]
OK
Time taken: 0.967 seconds

select * from orders limit 10;
OK
1 2013-07-25 00:00:00.0 11599 CLOSED
2 2013-07-25 00:00:00.0 256 PENDING_PAYMENT
3 2013-07-25 00:00:00.0 12111 COMPLETE
4 2013-07-25 00:00:00.0 8827 CLOSED
5 2013-07-25 00:00:00.0 11318 COMPLETE
6 2013-07-25 00:00:00.0 7130 COMPLETE
7 2013-07-25 00:00:00.0 4530 COMPLETE
8 2013-07-25 00:00:00.0 2911 PROCESSING
9 2013-07-25 00:00:00.0 5657 PENDING_PAYMENT
10 2013-07-25 00:00:00.0 5648 PENDING_PAYMENT
Time taken: 0.243 seconds, Fetched: 10 row(s)

0 Likes

#7

hive> show tables;
OK
categories
customers
departments
employee
order_items
orders
products

Output of metadata:

describe formatted categories;
OK
col_name data_type comment
category_id int
category_department_id int
category_name string

Detailed Table Information
Database: nagellarajashyam
Owner: nagellarajashyam
CreateTime: Wed Dec 14 02:50:22 EST 2016
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://nn01.itversity.com:8020/user/nagellarajashyam/retail_db/categories
Table Type: EXTERNAL_TABLE
Table Parameters:
EXTERNAL TRUE
numFiles 1
totalSize 1029
transient_lastDdlTime 1481701822

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.376 seconds, Fetched: 32 row(s)

Script to load data:

LOAD DATA INPATH ‘/user/nagellarajashyam/retail_db/customers’ OVERWRITE INTO TABLE customers;

hive> > select * from categories limit 10;
OK
1 2 Football
2 2 Soccer
3 2 Baseball & Softball
4 2 Basketball
5 2 Lacrosse
6 2 Tennis & Racquet
7 2 Hockey
8 2 More Sports
9 3 Cardio Equipment
10 3 Strength Training
Time taken: 0.231 seconds, Fetched: 10 row(s)

0 Likes

#8

show tables;
tab_name
categories
customers
departments
order_items
orders
products

load data inpath ‘/user/sumanthsharma21/retail_db/orders’ into table orders;
describe formatted orders;

col_name data_type comment

“” null null
order_id int ""
order_date timestamp ""
order_customer_id int ““
order_status string “”
”” null null

Detailed Table Information null null

Database: sumanthsharma21 null
Owner: sumanthsharma21 null
CreateTime: Wed Dec 14 03:03:39 EST 2016 null
LastAccessTime: UNKNOWN null
Protect Mode: None null
Retention: 0 null
Location: hdfs://nn01.itversity.com:8020/apps/hive/warehouse/sumanthsharma21.db/orders null
Table Type: MANAGED_TABLE null
Table Parameters: null null
"" COLUMN_STATS_ACCURATE {“BASIC_STATS”:“true”}
"" numFiles 1
"" numRows 68883
"" rawDataSize 2793295
"" totalSize 2862178
"" transient_lastDdlTime 1481702619
"" null null

Storage Information null null

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

select * from orders limit 10;
1 2013-07-25 00:00:00.0 11599 CLOSED
2 2013-07-25 00:00:00.0 256 PENDING_PAYMENT
3 2013-07-25 00:00:00.0 12111 COMPLETE
4 2013-07-25 00:00:00.0 8827 CLOSED
5 2013-07-25 00:00:00.0 11318 COMPLETE
6 2013-07-25 00:00:00.0 7130 COMPLETE
7 2013-07-25 00:00:00.0 4530 COMPLETE
8 2013-07-25 00:00:00.0 2911 PROCESSING
9 2013-07-25 00:00:00.0 5657 PENDING_PAYMENT
10 2013-07-25 00:00:00.0 5648 PENDING_PAYMENT

0 Likes

#9

hive> describe formatted bineysnehi.orders;
OK

col_name data_type comment

order_od int
order_date string
order_customer_id int
order_status string

Detailed Table Information

Database: bineysnehi
Owner: bineysnehi
CreateTime: Wed Dec 14 02:13:04 EST 2016
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://nn01.itversity.com:8020/apps/hive/warehouse/bineysnehi.db/orders
Table Type: MANAGED_TABLE
Table Parameters:
numFiles 1
numRows 0
rawDataSize 0
totalSize 2999944
transient_lastDdlTime 1481699737

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.354 seconds, Fetched: 34 row(s)
hive>

0 Likes

#10

use paramesh;
create table orders_items (
orders_items_id int,
order_item_order_id string,
orders_items_product_id string,
order_item_quantity string,
orders_items_subtotal float,
orders_items_product_price float)
row format delimited fields terminated by ‘,’;

load data local inpath ‘/home/paramesh/retail_db/order_items’ into table orders_items;

use paramesh;
create table products (
product_id int,
product_category_id int,
product_name string,
product_description string,
product_price int,
product_image string
)
row format delimited fields terminated by ‘,’;

load data local inpath ‘/home/paramesh/retail_db/products’ into table products;

use paramesh;
create table categories (
category_id int,
category_deprtment int,
category_name string
)
row format delimited fields terminated by ‘,’;

load data local inpath ‘/home/paramesh/retail_db/categories’ into table categories;

use paramesh;
create table deprtments (
deprtment_id int,
deprtment_name string

)
row format delimited fields terminated by ‘,’;

load data local inpath ‘/home/paramesh/retail_db/deprtments’ into table deprtments;

describe formatted customers;
OK

col_name data_type comment

customer_id int
customer_fname string
customer_lname string
customer_emil string
customer_password string
customer_street string
customer_city string
customer_state string
customer_zipcode string

Detailed Table Information

Database: paramesh
Owner: paramesh
CreateTime: Wed Dec 14 02:21:22 EST 2016
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://nn01.itversity.com:8020/apps/hive/warehouse/paramesh.db/customers
Table Type: MANAGED_TABLE
Table Parameters:
numFiles 1
numRows 0
rawDataSize 0
totalSize 953719
transient_lastDdlTime 1481700324

Storage Information

0 Likes

#11

create table customers(
customer_id int,
customer_fname varchar(45),
customer_lname varchar(45),
customer_email varchar(45),
customer_password varchar(45),
customer_street varchar(45),
customer_city varchar(45),
customer_state varchar(45),
customer_zipcode varchar(45)
)
row format delimited
fields terminated by ‘,’;

load data local inpath ‘/tmp/retail_db/customers’ overwrite into table customers;


create table order_items(
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

)
row format delimited
fields terminated by ‘,’;

load data local inpath ‘/tmp/retail_db/items’ overwrite into table items;


create table products(
product_id int,
product_category_id int,
product_name varchar(45),
product_description varchar(255),
product_price float,
product_image varchar(255))
row format delimited fields terminated by ‘,’ ;

load data local inpath ‘/tmp/retail_db/products’ overwrite into table products;


create table categories(
category_id int,
category_dept_id int,
category_name varchar(45))
row format delimited fields terminated by ‘,’ ;

load data local inpath ‘/tmp/retail_db/categories’ overwrite into table categories;


create external table departments(
department_id int,
department_name varchar(45))
row format delimited fields terminated by ','
location ‘/user/infosnehasish/retail_db/departments’;

load data local inpath ‘/tmp/retail_db/departments’ overwrite into table departments;


hive (infosnehasish)> show tables;
OK
categories
customers
departments
order_items
orders
products
Time taken: 0.213 seconds, Fetched: 6 row(s)


hive (infosnehasish)> describe formatted departments;
OK

col_name data_type comment

department_id int
department_name varchar(45)

Detailed Table Information

Database: infosnehasish
Owner: infosnehasish
CreateTime: Wed Dec 14 02:42:40 EST 2016
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://nn01.itversity.com:8020/user/infosnehasish/retail_db/departments
Table Type: EXTERNAL_TABLE
Table Parameters:
EXTERNAL TRUE
numFiles 1
totalSize 60
transient_lastDdlTime 1481701360

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.392 seconds, Fetched: 31 row(s)

0 Likes

#12

SHOW TABLES:

hive> use mahesh007;
OK
Time taken: 0.93 seconds
hive> show tables;
OK
categories
customers
departments
order_items
orders
products
test1
Time taken: 0.293 seconds, Fetched: 7 row(s)

METADATA OF order_items TABLE:

hive> describe formatted order_items;
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: mahesh007
Owner: mahesh007
CreateTime: Wed Dec 14 02:27:23 EST 2016
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://nn01.itversity.com:8020/user/mahesh007/hive_tables
Table Type: EXTERNAL_TABLE
Table Parameters:
EXTERNAL TRUE
transient_lastDdlTime 1481700443

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.462 seconds, Fetched: 33 row(s)

LOAD DATA SCRIPTS:

hive> LOAD DATA LOCAL INPATH ‘/tmp/retail_db/orders’ OVERWRITE INTO TABLE orders;
Loading data to table mahesh007.orders
Table mahesh007.orders stats: [numFiles=1, numRows=0, totalSize=2999944, rawDataSize=0]
OK
Time taken: 1.157 seconds
hive> LOAD DATA LOCAL INPATH ‘/tmp/retail_db/categories’ OVERWRITE INTO TABLE categories;
Loading data to table mahesh007.categories
Table mahesh007.categories stats: [numFiles=1, numRows=0, totalSize=1029, rawDataSize=0]
OK
Time taken: 0.971 seconds
hive> LOAD DATA LOCAL INPATH ‘/tmp/retail_db/customers’ OVERWRITE INTO TABLE customers;
Loading data to table mahesh007.customers
Table mahesh007.customers stats: [numFiles=1, numRows=0, totalSize=953719, rawDataSize=0]
OK
Time taken: 0.983 seconds
hive> LOAD DATA LOCAL INPATH ‘/tmp/retail_db/departments’ OVERWRITE INTO TABLE departments;
Loading data to table mahesh007.departments
Table mahesh007.departments stats: [numFiles=1, numRows=0, totalSize=60, rawDataSize=0]
OK
Time taken: 0.942 seconds
hive> LOAD DATA LOCAL INPATH ‘/tmp/retail_db/order_items’ OVERWRITE INTO TABLE order_items;
Loading data to table mahesh007.order_items
Table mahesh007.order_items stats: [numFiles=1, totalSize=5408880]
OK
Time taken: 1.12 seconds
hive> LOAD DATA LOCAL INPATH ‘/tmp/retail_db/products’ OVERWRITE INTO TABLE products;
Loading data to table mahesh007.products
Table mahesh007.products stats: [numFiles=1, numRows=0, totalSize=174155, rawDataSize=0]
OK
Time taken: 1.009 seconds

QUERY OUTPUT WITH LIMIT 10:

hive> select * from products limit 10;
OK
products.product_id products.product_category_id products.product_name products.product_description products.product_price products.product_image
1 2 Quest Q64 10 FT. x 10 FT. Slant Leg Instant U 59.98 http://images.acmesports.sports/Quest+Q64+10+FT.+x+10+FT.+Slant+Leg+Instant+Up+Canopy
2 2 Under Armour Men’s Highlight MC Football Clea 129.99 http://images.acmesports.sports/Under+Armour+Men’s+Highlight+MC+Football+Cleat
3 2 Under Armour Men’s Renegade D Mid Football Cl 89.99 http://images.acmesports.sports/Under+Armour+Men’s+Renegade+D+Mid+Football+Cleat
4 2 Under Armour Men’s Renegade D Mid Football Cl 89.99 http://images.acmesports.sports/Under+Armour+Men’s+Renegade+D+Mid+Football+Cleat
5 2 Riddell Youth Revolution Speed Custom Footbal 199.99 http://images.acmesports.sports/Riddell+Youth+Revolution+Speed+Custom+Football+Helmet
6 2 Jordan Men’s VI Retro TD Football Cleat 134.99 http://images.acmesports.sports/Jordan+Men’s+VI+Retro+TD+Football+Cleat
7 2 Schutt Youth Recruit Hybrid Custom Football H 99.99 http://images.acmesports.sports/Schutt+Youth+Recruit+Hybrid+Custom+Football+Helmet+2014
8 2 Nike Men’s Vapor Carbon Elite TD Football Cle 129.99 http://images.acmesports.sports/Nike+Men’s+Vapor+Carbon+Elite+TD+Football+Cleat
9 2 Nike Adult Vapor Jet 3.0 Receiver Gloves 50.0 http://images.acmesports.sports/Nike+Adult+Vapor+Jet+3.0+Receiver+Gloves
10 2 Under Armour Men’s Highlight MC Football Clea 129.99 http://images.acmesports.sports/Under+Armour+Men’s+Highlight+MC+Football+Cleat
Time taken: 0.194 seconds, Fetched: 10 row(s)

0 Likes

#13

hive> show tables;
OK
categories
customers
department
order_items
orders
products
test

hive> select * from categories limit 3;
OK
1 2 Football
2 2 Soccer
3 2 Baseball & Softball
Time taken: 0.369 seconds, Fetched: 3 row(s)
hive> select * from customers limit 3;
OK
1 Richard Hernandez XXXXXXXXX XXXXXXXXX 6303 Heather Plaza Brownsville TX 78521
2 Mary Barrett XXXXXXXXX XXXXXXXXX 9526 Noble Embers Ridge Littleton CO 80126
3 Ann Smith XXXXXXXXX XXXXXXXXX 3422 Blue Pioneer Bend Caguas PR 00725
Time taken: 0.19 seconds, Fetched: 3 row(s)
hive> select * from department limit 3;
OK
2 Fitness
3 Footwear
4 Apparel
Time taken: 0.194 seconds, Fetched: 3 row(s)
hive> select * from order_items limit 3;
OK
1 1 957 1 299.98 299.98
2 2 1073 1 199.99 199.99
3 2 502 5 250.0 50.0
Time taken: 0.29 seconds, Fetched: 3 row(s)
hive> select * from orders limit 3;
OK
1 2013-07-25 00:00:00.0 11599 CLOSED
2 2013-07-25 00:00:00.0 256 PENDING_PAYMENT
3 2013-07-25 00:00:00.0 12111 COMPLETE
Time taken: 0.188 seconds, Fetched: 3 row(s)
hive> select * from products limit 3;
OK
1 2 Quest Q64 10 FT. x 10 FT. Slant Leg Instant U 59.98
2 2 Under Armour Men’s Highlight MC Football Clea 129.99
3 2 Under Armour Men’s Renegade D Mid Football Cl 89.99
Time taken: 0.185 seconds, Fetched: 3 row(s)

0 Likes

#14

hive> show tables;
OK
categories
customers
departments
order_items
orders
products
Time taken: 0.224 seconds, Fetched: 6 row(s)
hive>

0 Likes

#15

database: jasonbourne

**

List of tables in database jasonboune

**

show tables;
OK
categories
customers
departments
order_items
orders
products

External table:

describe formatted customers;
OK

col_name data_type comment

customer_id int
customer_fname string
customer_lname string
customer_email string
customer_password string
customer_street string
customer_city string
customer_state string
customer_zipcode string

Detailed Table Information

Database: jasonbourne
Owner: jasonbourne
CreateTime: Wed Dec 14 03:26:59 EST 2016
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://nn01.itversity.com:8020/user/jasonbourne/retail_db/customers
Table Type: EXTERNAL_TABLE
Table Parameters:
EXTERNAL TRUE
numFiles 1
totalSize 953719
transient_lastDdlTime 1481704019

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.36 seconds, Fetched: 38 row(s)

Load script:

LOAD DATA LOCAL INPATH ‘/tmp/retail_db/order_items’ OVERWRITE INTO TABLE order_items;

hive> select * from departments;
OK
2 Fitness
3 Footwear
4 Apparel
5 Golf
6 Outdoors
7 Fan Shop

0 Likes

#16

show tables;
OK
categories
customers
departments
order_items
orders
products
tutorial

describe formatted orders_items;
OK

col_name data_type comment

order_id int
order_date string
order_customer_id int
order_status string
order_item_id int
order_item_order_id int
order_item_product_id int
order_item_quantity tinyint
order_item_subtotal double
order_item_product_price double

Detailed Table Information

Database: default
Owner: subhash29
CreateTime: Tue Nov 15 23:41:19 EST 2016
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://nn01.itversity.com:8020/apps/hive/warehouse/orders_items
Table Type: MANAGED_TABLE
Table Parameters:
COLUMN_STATS_ACCURATE {“BASIC_STATS”:“true”}
comment Imported by sqoop on 2016/11/15 23:41:10
numFiles 0
numRows 0
rawDataSize 0
totalSize 0
transient_lastDdlTime 1479271279

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 \u0001
line.delim \n
serialization.format \u0001
Time taken: 0.358 seconds, Fetched: 43 row(s)

select * from orders LIMIT 10

OK
1 2013-07-25 00:00:00.0 11599 CLOSED
2 2013-07-25 00:00:00.0 256 PENDING_PAYMENT
3 2013-07-25 00:00:00.0 12111 COMPLETE
4 2013-07-25 00:00:00.0 8827 CLOSED
5 2013-07-25 00:00:00.0 11318 COMPLETE
6 2013-07-25 00:00:00.0 7130 COMPLETE
7 2013-07-25 00:00:00.0 4530 COMPLETE
8 2013-07-25 00:00:00.0 2911 PROCESSING
9 2013-07-25 00:00:00.0 5657 PENDING_PAYMENT
10 2013-07-25 00:00:00.0 5648 PENDING_PAYMENT

LOAD DATA LOCAL INPATH ‘/tmp/retail_db/categories’ OVERWRITE INTO TABLE categories;

0 Likes