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;

MongoDB Practice on TEACHER DB

Create a new MONGODB database called “TEACHER”

---

---

INSERT

No	Documents / Queries
0	db.TEACHER_MASTER.insert({    _id : Uniqe_Id,    Name : "Name",    Subject : ["Array_1","Array_2","Array_N"],    DOB : ISODate("YYYY-MM-DD"),    Gender : "Male/Female",    City : "City_Name" })
1	db.TEACHER_MASTER.insert({    _id : 1001,    Name : "Nimit",    Subject : ["PHP","Laravel","Android"],    DOB : ISODate("1997-06-19"),    Gender : "Male",    City : "Rajkot" })
2	db.TEACHER_MASTER.insert({    _id : 1002,    Name : "Nishit",    Subject : ["ASP.Net","React"],    DOB : ISODate("1998-01-23"),    Gender : "Male",    City : "Rajkot" })
3	db.TEACHER_MASTER.insert({    _id : 1003,    Name : "Nirajbhai",    Subject : ["ASP.Net","Java","Android"],    DOB : ISODate("1995-01-07"),    Gender : "Male",    City : "Katch" })
4	db.TEACHER_MASTER.insert({    _id : 1004,    Name : "Dipen",    Subject : ["PHP","MongoDB","Laravel"],    DOB : ISODate("1997-11-13"),    Gender : "Male",    City : "Anjar" })
5	db.TEACHER_MASTER.insert({    _id : 1005,    Name : "Ankit",    Subject : ["PHP","Network"],    DOB : ISODate("1995-01-07"),    Gender : "Male",    City : "Jam Kandola" })
6	db.TEACHER_MASTER.insert({    _id : 1006,    Name : "Roshni",    Subject : ["ASP.net","Android","Fox-Pro"],    DOB : ISODate("1998-12-25"),    Gender : "Female",    City : "Surat" })
7	db.TEACHER_MASTER.insert({    _id : 1007,    Name : "Surbhi",    Subject : ["ASP.net","Html"],    DOB : ISODate("1998-02-26"),    Gender : "Female",    City : "Rajkot" })
8	db.TEACHER_MASTER.insert({    _id : 1008,    Name : "Milan",    Subject : ["Html","ASP.net","Android"],    DOB : ISODate("1998-12-09"),    Gender : "Male",    City : "Rajkot" })
9	db.TEACHER_MASTER.insert({    _id : 1009,    Name : "Radhika",    Subject : ["Html","ASP.Net","Fox-Pro"],    DOB : ISODate("1998-02-26"),    Gender : "Female",    City : "Rajkot" })
10	db.TEACHER_MASTER.insert({    _id : 1010,    Name : "Priayal",    Subject : ["Html","ASP.net","Laravel"],    DOB : ISODate("1998-02-26"),    Gender : "Female",    City : "Rajkot" })

Implement the following SQL Queries.
Implements :-

No	Implement Documents	Queries
1	Display all the teachers on screen.	db.TEACHER_MASTER.find()
2	Display all the teachers with name, subject and gender on screen.	db.TEACHER_MASTER.find({},    {           "_id":0,        "Name":1,        "Subject":1,        "Gender":1,    }).pretty()
3	Display only those documents where the gender of the teacher is Male.	db.TEACHER_MASTER.find( { "Gender":"Male" },{}).pretty()
4	Update all those documents where Name of teacher is “Ankit" with the new value of Subject as "Java".	db.TEACHER_MASTER.update( {    Name:"Ankit" },{    $addToSet:{        Subject:"Java"    } })
5	Sort the documents in the given collection by Name in Descending Order.	db.TEACHER_MASTER.find().sort({    Name:1 })
6	Delete data of all those Teachers who were born after 1st January 1980.	db.TEACHER_MASTER.deleteMany({    DOB : {        $gte : new Date("1996-01-01")    }},{})
7	Count all teachers who are taking Big Data.	db.TEACHER_MASTER.find({    Subject:'BigData'},{}).count()
8	Display 2nd last record of teacher.	db.TEACHER_MASTER.find().skip(    db.TEACHER_MASTER.count()-2    ).limit(1)
9	Delete all teachers who are taking “Fox-Pro".	db.TEACHER_MASTER.deleteMany({ Subject:"Fox-Pro"},{})
10	Update city of teacher with “Ahmedabad” who are living in “Baroda”.	db.TEACHER_MASTER.updateMany( {City:"Baroda"}, {$set: {City:"“Ahmedabad"} } )