Friday, January 26, 2018

Hive Cheat Sheet



Start the hive shell
hive

Create schema in hive
hive> create schema hiveschema location '/hivedatabase/';

Create table with location
use hiveschema;
create external table employee(emp_id int,emp_name string,emp_phone string)
row format delimited
fields terminated by '\t'
location '/hivedatabase/employee';


Create External table in hive

Note: The difference between external and internal table is that.Drop table for internal table drops the table and metadata. Drop table for external table only drops the metadata and data is not touched.

External table
When there is data already in HDFS, an external Hive table can be created to describe the data.It is called External (Data is in the external table and is specified in the Location properties instead of the default warehouse directory.
  • Table name would be created as directory
  • Schema would be created as meta store
Create external table
create table product_ratings(UserId string, ISBN string) 
partitioned by (ProductRating int)
row  format delimited  
fields terminated by '\t';

Load data from HDFS

load data inpath '/Locations/Products_Ratings.cvs' 
into table 
product_ratings partition (ProductRating=0)

Query data
Now we can use select * from table or specify the partition by condition. When we use partition condition internally the fetch data would be faster.

select * from product_ratings where ProductRating=0
Note:
After dropping the table data was retained.
To access the data we need to create the table. In this example after creating the table we can find that data is retained.

Partitioning of  table
  • Hive creates tables in partitions.
  • They are used to divide the table into related parts.
  • Makes data querying more efficient
Import data to hive from mysql contacts and account tables

sqoop import --connect jdbc:mysql://localhost:3306/securemask \
--username root --password cloudera \
--table contacts \
--fields-terminated-by "," \
--target-dir /user/cloudera/workspace/contactsmask \
--hive-import 

In this import we are specifying the schema and not the directory path
sqoop import --connect jdbc:mysql://quickstart.cloudera/original \
--username root --password cloudera \
--table employee \
--hive-import \
--hive-table hiveschema.employee -m1 \
--fields-terminated-by '\t' \
--direct; 

Add UDF function
 hive> ADD JAR /home/cloudera/mask.jar; 
 hive> CREATE TEMPORARY FUNCTION MASK AS 'hiveudf.PImask';
 

Note: Before running this command we need to make sure that we have added the Jar file for UDF and create temporary function as shown above
Create similar tables in Hive
 hive> create table securedaccounts like accounts; 
 DATA MASKING

Insert masked phone numer into accounts
 insert overwrite table securedaccounts select id,name,MASK(phone) from accounts; 

Iinsert masked phone and email into contacts

insert overwrite table securedcontacts select id,accountid,firstname,
lastname,MASK(phone),
MASK(email) from contacts;

 Move tables (securedcontacts,securedaccounts) from hive to my SQL
sqoop export --connect jdbc:mysql://localhost:3306/securemask 
--username root --password cloudera 
--table contacts  
--export-dir /user/hive/warehouse/securedcontacts

RETRIEVING INFORMATION

FunctionMySQLHive
Retrieving Information (General)SELECT from_columns FROM table WHERE conditions;SELECT from_columns FROM table WHERE conditions;
Retrieving All ValuesSELECT * FROM table;SELECT * FROM table;
Retrieving Some ValuesSELECT * FROM table WHERE rec_name = "value";SELECT * FROM table WHERE rec_name = "value";
Retrieving With Multiple CriteriaSELECT * FROM TABLE WHERE rec1 = "value1" AND rec2 ="value2";SELECT * FROM TABLE WHERE rec1= "value1" AND rec2 ="value2";
Retrieving Specific ColumnsSELECT column_name FROM table;SELECT column_name FROM table;
Retrieving Unique OutputSELECT DISTINCT column_name FROM table;SELECT DISTINCT column_name FROM table;
SortingSELECT col1, col2 FROM table ORDER BY col2;SELECT col1, col2 FROM table ORDER BY col2;
Sorting ReverseSELECT col1, col2 FROM table ORDER BY col2 DESC;SELECT col1, col2 FROM table ORDER BY col2 DESC;
Counting RowsSELECT COUNT(*) FROM table;SELECT COUNT(*) FROM table;
Grouping With CountingSELECT owner, COUNT(*) FROM table GROUP BY owner;SELECT owner, COUNT(*) FROM table GROUP BY owner;
Maximum ValueSELECT MAX(col_name) AS label FROM table;SELECT MAX(col_name) AS label FROM table;
Selecting from multiple tables (Join same table using alias w/”AS”)SELECT pet.name, comment FROM pet, event WHERE pet.name =event.name;SELECT pet.name, comment FROM pet JOIN event ON (pet.name =event.name)

METADATA

FunctionMySQLHive
Selecting a databaseUSE database;USE database;
Listing databasesSHOW DATABASES;SHOW DATABASES;
Listing tables in a databaseSHOW TABLES;SHOW TABLES;
Describing the format of a tableDESCRIBE table;DESCRIBE (FORMATTED|EXTENDED)table;
Creating a databaseCREATE DATABASE db_name;CREATE DATABASE db_name;
Dropping a databaseDROP DATABASE db_name;DROP DATABASE db_name (CASCADE);

CURRENT SQL COMPATIBILITY

COMMAND LINE

FunctionHive
Run Queryhive -'select a.col from tab1 a'
Run Query Silent Modehive --'select a.col from tab1 a'
Set Hive Config Variableshive -'select a.col from tab1 a' -hiveconf hive.root.logger=DEBUG,console
Use Initialization Scripthive -i initialize.sql
Run Non-Interactive Scripthive -f script.sql

No comments:

Post a Comment