Hive Notes - Part 1

Hive Commands:
----------------
start HiveServer2 :
beeline -u jdbc:hive2://

show databases;

use sara;

create table customers (id bigint, name string, address string);

describe customers;

insert into customers values(101,"Johny","WA");

select * from customers;

insert into customers values (102,"Bramma","IN"), (103,"Ravi","IN"), (104,"Janani","UK");

select * from customers where address='IN';

select * from customers where address like ('IN');

select name,address from customers where address like ('IN');

select distinct address from customers;

select name,address from customers order by address desc;

select count(*) from customers;

select address,count(*) from customers group by address;


if it is simple select operation without any where condition, group by and all - no MapReduce


create table if not exists orders (id bigint, product_id string, customer_id bigint, quantity int, amount double);  // no location specified
insert into orders values (1,'camera',101,1,5200),(2,'t-shirt',102,2,300),(3,'painbalm',103,3,50);

select customers.id,name,product_id,quantity,amount from customers join orders where customers.id = orders.customer_id;

Hive only supports Equvi Join

Boolean - true/false : yes/no - exists or not
Numeric
integral
tinyint (1byte), smallint(2byte), int (4byte), bigint (8byte)
decimal
float (4byte), double(8byte), decimal (arbitrary precision)
dec(10,2) => 1234567890.12
String
string: unbounded - variable length character string
char : fixed length character string
varchar : bounded, variable length
Timestamp:



make a copy of existing table:
create table if not exists fresh_products like products;


table data files will be stored in warehouse directory implicitly.