HIVE LAB

 HIVE LAB

---------------------

1. create data base

hive > CREATE DATABASE db1;

2. show data bases

hive> show databases;

3. drop data base

hive> drop database db1;

4. show data bases

hive> show databases;

5.create database and alter database owner

hive > create database db1;

hive>ALTER DATABASE db1 SET DBPROPERTIES ('owner'='John');

6. create emp table in db1 as below

HIVE> create database db1;

hive > use db1;

hive> CREATE TABLE employees (

  emp_id INT,

  emp_name STRING,

  emp_age INT,

  emp_city STRING,

  emp_dept STRING,

  emp_sal  INT

)

row format delimited

fields terminated by ','

lines terminated by '\n'

stored as textfile;

$cat > employees.txt

101,scott,34,chennai,hr,45000

102,lara,32,bangalore,fin,55000

103,john,41,hyd,mark,56000

104,michel,35,chennai,hr,67000

105,clark,28,bangalore,fin,86000

ctrl+z

hive>load data local inpath 'employees.txt' overwrite into employees;

 $ cat > hivescript1.ql

$hive -S -f hivescript1.ql

7. emp salary greater than 50000

hive> select * from employees where emp_sal > 50000;

8. create view for your hive table

CREATE VIEW AGE_gt_30 AS

SELECT emp_id, emp_name, emp_age, emp_city

FROM employees

WHERE emp_age > 30;

9.execute view

SELECT * FROM AGE_gt_30;

10. drop the view

DROP VIEW IF EXISTS AGE_gt_30;

11. create index

CREATE INDEX emp_city_index

ON TABLE employees (emp_city)

AS 'COMPACT'

WITH DEFERRED REBUILD;

12. drop index

DROP INDEX IF EXISTS emp_city_index ON employees;