Lab 1 Overview
In this hands-on lab, you'll learn how to create Hive Data warehouse infrastructure
using basic hive commands .
1.1. What you'll learn
After completing all exercises in this lab guide, you'll know about
Hive Databases.
Hive Managed Tables
Hive External Tables
Hive Partition Tables
Hive Bucket Tables
Hive Skewed Tables
Hive Temporary Tables
HIVE DATABASE
Create Database
1) CREATE DATABASE employee;
2) CREATE DATABASE IF NOT EXISTS customers;
3) SHOW DATABASES;
4) SHOW DATABASES LIKE 'cu.*';
5) DESCRIBE DATABASE customers;
6) CREATE DATABASE stocks
LOCATION '/sekhar/hivelabs/lab-01';
7) USE stocks;
8) set [Link]=true;
Alter Database
ALTER DATABASE stocks SET DBPROPERTIES ('edited-by' = 'active steps');
Drop Database
DROP DATABASE IF EXISTS stocks;
DROP DATABASE IF EXISTS stocks CASCADE;
HIVE TABLES
Unzip the following data file and copy to HDFS location or keep it in local directory.
1. Managed Tables.
DROP TABLE IF EXISTS user;
CREATE TABLE IF NOT EXISTS user (
first_name VARCHAR(64),
last_name VARCHAR(64),
company_name VARCHAR(64),
address STRUCT<zip:INT, street:STRING>,
country VARCHAR(64),
city VARCHAR(32),
state VARCHAR(32),
post INT,
phone_nos ARRAY<STRING>,
mail MAP<STRING, STRING>,
web_address VARCHAR(64)
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '\t'
MAP KEYS TERMINATED BY ':'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;
LOAD DATA LOCAL INPATH '/sekhar/user/User_Records.txt' OVERWRITE INTO TABLE user;
SELECT * FROM user;
1B CREATE TABLE IF NOT EXISTS user_copy
LIKE user
LOCATION '/user/hive/usertable'
;
INSERT OVERWRITE TABLE user_copy SELECT * FROM user;
External Table :
CREATE EXTERNAL TABLE employees (
name STRING,
salary FLOAT,
subordinates ARRAY<STRING>,
deductions MAP<STRING, FLOAT>,
address STRUCT<street:STRING, city:STRING, state:STRING, zip:INT>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\001'
COLLECTION ITEMS TERMINATED BY '\002'
MAP KEYS TERMINATED BY '\003'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE
LOCATION 'sekhar/hive-lab01/data/employees';
Load Data
LOAD DATA LOCAL INPATH '/Hive-Demo/data/employees/input/plain-
text/[Link]' INTO TABLE employees
Note : you can load data into table either from HDFS location or
from local.
External Table ORC File Format & Snappy Compressd :
CREATE EXTERNAL TABLE User_ORC(
first_name VARCHAR(64),
last_name VARCHAR(64),
company_name VARCHAR(64),
address STRUCT<zip:INT, street:STRING>,
country VARCHAR(64),
city VARCHAR(32),
state VARCHAR(32),
post INT,
phone_nos ARRAY<STRING>,
mail MAP<STRING, STRING>,
web_address VARCHAR(64)
)
COMMENT 'Temporary ORC table for testing purpose'
STORED AS ORC
LOCATION '/user/hive/orc/user'
TBLPROPERTIES ("[Link]"="SNAPPY");
INSERT OVERWRITE TABLE user_ORC SELECT * FROM user;
SELECT * FROM User_ORC;
DESCRIBE FORMATTED User_ORC;
Partition Tables :
Tables used in this LAB.
1. Dividends
2. Stocks
Create External table stocks ( Partition on Exchange and Symbol)
CREATE EXTERNAL TABLE IF NOT EXISTS stocks (
ymd STRING,
price_open FLOAT,
price_high FLOAT,
price_low FLOAT,
price_close FLOAT,
volume INT,
price_adj_close FLOAT)
PARTITIONED BY (exchange STRING, symbol STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/data/stocks/';
Load the stocks for AAPL
LOAD DATA LOCAL
INPATH '/Users/sekhar/Hive-Demo/data/stocks/input/plain-text/NASDAQ/AAPL/[Link]'
INTO TABLE stocks
PARTITION (exchange = 'NASDAQ', symbol = 'AAPL');
Load the stocks for INTC
LOAD DATA LOCAL
INPATH '/Users/sekhar/Hive-Demo/data/stocks/input/plain-text/NASDAQ/INTC/[Link]'
INTO TABLE stocks
PARTITION (exchange = 'NASDAQ', symbol = 'INTC');
Load the stocks for GE
LOAD DATA LOCAL INPATH '/Users/sekhar/Hive-Demo/data/stocks/input/plain-text/NYSE/
GE/[Link]'
INTO TABLE stocks
PARTITION (exchange = 'NYSE', symbol = 'GE');
Load the stocks for IBM
LOAD DATA LOCAL INPATH '/Users/sekhar/Hive-Demo/data/stocks/input/plain-text/NYSE/
IBM/[Link]'
INTO TABLE stocks
PARTITION (exchange = 'NYSE', symbol = 'IBM');
Create Dividends
CREATE TABLE IF NOT EXISTS dividends (
ymd STRING,
dividend FLOAT
)
PARTITIONED BY (exchange STRING, symbol STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/data/dividends';
Load Divedends Data
LOAD DATA LOCAL
INPATH '/path/to/data/dividends/input/plain-text/NASDAQ/AAPL/[Link]'
INTO TABLE DIVIDENDS
PARTITION (exchange = 'NASDAQ', symbol = 'AAPL');
LOAD DATA LOCAL
INPATH '/path/to/data/dividends/input/plain-text/NASDAQ/INTC/[Link]'
INTO TABLE DIVIDENDS
PARTITION (exchange = 'NASDAQ', symbol = 'INTC');
LOAD DATA LOCAL
INPATH '/path/to/data/dividends/input/plain-text/NYSE/GE/[Link]'
INTO TABLE DIVIDENDS
PARTITION (exchange = 'NYSE', symbol = 'GE');
LOAD DATA LOCAL
INPATH '/path/to/data/dividends/input/plain-text/NYSE/IBM/[Link]'
INTO TABLE DIVIDENDS
PARTITION (exchange = 'NYSE', symbol = 'IBM');
Bucketed Table :
CREATE TABLE stocks
(market string,
stock string,
open double,
high double,
low double,
close double,
volume bigint,
adj_close double) PARTITIONED BY(sdate STRING) CLUSTERED BY(stock) INTO 5 BUCKETS row
format delimited fields terminated by '\t';
LOAD DATA LOCAL INPATH '/sekhar/stocks/[Link]' OVERWRITE INTO TABLE stocks;
CREATE TABLE stocks_buck(
market string,
stock string,
sdate STRING ,
open double,
high double,
low double,
close double, volume bigint, adj_close double) CLUSTERED BY(stock) INTO 10 BUCKETS row
format delimited fields terminated by '\t';
insert into table stocks_buck select market, stock, sdate,
open,high,low,close,volume,adj_close from stocks;
Bucket & Partition Example :
CREATE TABLE stocks_bucketed(
market string,
stock string,
open double,
high double,
low double,
close double,
volume bigint,
adj_close double)
PARTITIONED BY(sdate STRING) CLUSTERED BY(stock) INTO 5 BUCKETS row format delimited
fields terminated by '\t';
insert into table stocks_bucketed partition(sdate) select market, stock,
open,high,low,close,volume,adj_close,sdate from stocks;
Skewed Table :
DROP TABLE IF EXISTS User_Skewed;
CREATE EXTERNAL TABLE User_Skewed(
first_name VARCHAR(64),
last_name VARCHAR(64),
company_name VARCHAR(64),
address STRUCT<zip:INT, street:STRING>,
country VARCHAR(64),
city VARCHAR(32),
state VARCHAR(32),
post INT,
phone_nos ARRAY<STRING>,
mail MAP<STRING, STRING>,
web_address VARCHAR(64)
)
COMMENT 'Skewed table for testing purpose'
SKEWED BY (country) ON ('AU')
STORED AS SEQUENCEFILE;
INSERT OVERWRITE TABLE user_Skewed SELECT * FROM user;
SELECT * FROM User_Skewed;
DESCRIBE FORMATTED User_Skewed;
Twitter POC :
In this POC, we will explore the following:
1. Load a twitter data into a Hive table
2. Create a table using RCFormat
3. Query tables
4. Managed tables vs external tables
5. ORC format
6. PARTITIONED a Table
7. Bucketing a Table
Step 1 : Creating a table TwitterExampletextexample
CREATE TABLE TwitterExampletextexample(
tweetId BIGINT, username STRING,
txt STRING, CreatedAt STRING,
profileLocation STRING,
favc BIGINT,retweet STRING,retcount BIGINT,followerscount BIGINT)
COMMENT 'This is the Twitter streaming data'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;
Step 2 : Load Data into table TwitterExampletextexample
LOAD DATA INPATH '/user/[Link]' OVERWRITE INTO TABLE
TwitterExampletextexample;
Step 3 :
Select * from TwitterExampletextexample;
Create a table using RCfile format and load the data using TwitterExampletextexample
CREATE TABLE TwitterExampleRCtable(
tweetId INT, username BIGINT,
txt STRING, CreatedAt STRING,
profileLocation STRING COMMENT 'Location of user',
favc INT,retweet STRING,retcount INT,followerscount INT)
COMMENT 'This is the Twitter streaming data'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS RCFILE;
INSERT OVERWRITE TABLE TwitterExampleRCtable select * from
TwitterExampletextexample;
Select profileLocation, COUNT(txt) as count1 FROM TwitterExampleRCtable GROUP BY
profileLocation ORDER BY count1 desc limit 10;
Managed tables vs External tables
Managed:
CREATE TABLE ManagedExample(
tweetId BIGINT, username STRING,
txt STRING, CreatedAt STRING,
profileLocation STRING,
favc BIGINT,retweet STRING,retcount BIGINT,followerscount BIGINT)
COMMENT 'This is the Twitter streaming data'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;
External:
CREATE EXTERNAL TABLE IF NOT EXISTS ExternalExample(
tweetId BIGINT, username STRING,
txt STRING, CreatedAt STRING,
profileLocation STRING,
favc BIGINT,retweet STRING,retcount BIGINT,followerscount BIGINT)
COMMENT 'This is the Twitter streaming data'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE
location '/user/[Link]';
describe formatted ManagedExample;
describe formatted ExternalExample;
Optimized Row Columnar (ORC) File format is used as it further compresses data files. It could
result in a small performance loss in writing, but there will be huge performance gain in
reading.
CREATE TABLE ORCFileFormatExample(
tweetId INT, username BIGINT,
txt STRING, CreatedAt STRING,
profileLocation STRING COMMENT 'Location of user',
favc INT,retweet STRING,retcount INT,followerscount INT)
COMMENT 'This is the Twitter streaming data'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS ORC tblproperties ("[Link]"="GLIB");
Create a PARTITIONED Table and load data into.
CREATE TABLE PARTITIONEDExample(
tweetId INT, username BIGINT, txt STRING,
favc INT,retweet STRING,retcount INT,
followerscount INT)
COMMENT 'This is the Twitter streaming data'
PARTITIONED BY(CreatedAt STRING, profileLocation STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;
FROM twitterexampletextexample
INSERT OVERWRITE TABLE PARTITIONEDExample
PARTITION (CreatedAt="26 04:50:56 UTC 2014",profileLocation="Chicago")
SELECT tweetId,username,txt,favc,retweet,retcount,followerscount where
profileLocation='Chicago' limit 100;
Creating a table with buckets and load data into it.
CREATE TABLE BucketingExample(
tweetId INT, username BIGINT,
txt STRING,CreatedAt STRING,favc INT,retweet STRING,retcount INT,
followerscount INT)
COMMENT 'This is the Twitter streaming data'
PARTITIONED BY( profileLocation STRING)
CLUSTERED BY(tweetId) INTO 2 BUCKETS
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;
set [Link] = true;
FROM twitterexampletextexample
INSERT OVERWRITE TABLE BucketingExample PARTITION (profileLocation="Chicago")
SELECT tweetId,username,txt,CreatedAt,favc,retweet,retcount,followerscount
where profileLocation='Chicago' limit 100;