Hive Basic Command

Basic Commands

---

---

Downloads

No.	Commands Description	Commands
1	To create the new database Customer DB and show	CREATE DATABASE CUSTOMER; SHOW DATABASES;
2.	To Use Database	use customer;
3.	To Show Tables	show tables;
4.	Create a partition table for Customer schema to reward the customers based on their city. Customer (CustomerID, Name, City).	CREATE TABLE customer_master (CID int, NAME String, CITY String) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
5.	Show the Table Structure	DESCRIBE customer_master;
6.	Load the csv file on customer-master	LOAD DATA INPATH 'customer.csv' INTO TABLE customer_master;
7.	Select Data From customer_master	SELECT * FROM customer_master;
8.	Create a static partition for city Rajkot
8.1 CREATE STATIC_CUSTOMER_PARTITION CREATE TABLE STATIC_CUSTOMER_PARTITION (CID int,NAME String) PARTITIONED BY (CITY String) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','; 8.2 INSERT DATA ON STATIC PARTICTION INSERT OVERWRITE TABLE STATIC_CUSTOMER_PARTITION PARTITION(CITY="Rajkot") SELECT CID,NAME FROM customer_master WHERE CITY="Rajkot"; 8.3 SESLECT DATA FROM STATIC_CUSTOMER_PARTITION SELECT * FROM STATIC_CUSTOMER_PARTITION;
9.	Create a dynamic partition for all cities.
9.1 CREATE DYNAMIC_CUSTOMER_PARTITION CREATE TABLE DYNAMIC_CUSTOMER_PARTITION (CID int,NAME String) PARTITIONED BY (CITY String) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','; 9.2 Change Settings for Dynamic Partition SET hive.exec.dynamic.partition = true; SET hive.exec.dynamic.partition.mode = nonstrict; 9.3 INSERT DATA ON DYNAMIC PARTITION INSERT OVERWRITE TABLE DYNAMIC_CUSTOMER_PARTITION PARTITION(CITY="Rajkot") SELECT CID,NAME FROM customer_master WHERE CITY="Rajkot"; 9.4 SELECT DATA FROM DYNAMIC_CUSTOMER_PARTITION
2.0
2.1	Order and Customer csv file copyFromLocal
	-> hadoop fs -copyFromLocal MSC2/customer.csv MSC2HDFS -> hadoop fs -copyFromLocal MSC2/order.csv MSC2HDFS -> hadoop fs -ls MSC2HDFS/
2.2.	Create Order & Customer Table
	CREATE TABLE order (CID int, ItemId String, ItemName String, OrderDate String, DeliveryDate String) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
	CREATE TABLE customer_order (CustomerID int, CustomerName String, City String, State String, Country String) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
2.3	LOAD DATA INPATH 'MSC2HDFS/order.csv' OVERWRITE INTO TABLE order;