2.Intall-Hive

# Requirements
* Install MySQL

sudo apt-get install mysql-client
sudo apt-get install mysql-server


# Hive install

cd
cd app
cp ../hadoop-workshop/app-deliverable/hive.tar.gz .
tar -xzvf hive.tar.gz

1) hive-default.xml

hive.exec.scratchdir - /home/ubuntu/hive/logs/hive-${user.name}

<!-- Added for MySQL -->
MySQL connection details

2) hive-site.xml

fs.default.name - hdfs://localhost:10011/hive
mapred.job.tracker - localhost:10012
hive.querylog.location - /home/ubuntu/hive/logs
hive.metastore.warehouse.dir - /user/hive/warehouse

3) hive-log4j.properties
hive.log.dir=/home/ubuntu/hive/logs/

4) hive-exec-log4j.properties
hive.log.dir=/home/ubuntu/hive/logs/${user.name}


### datastore ###

Login MySQL : $ mysql -u root -p

CREATE DATABASE metastore;

USE metastore;

SOURCE <hive_home_path>/scripts/metastore/upgrade/mysql/hive-schema-0.7.0.mysql.sql;

SOURCE /home/ubuntu/app/hive/scripts/metastore/upgrade/mysql/hive-schema-0.7.0.mysql.sql;

SHOW TABLES;

CREATE USER 'hiveuser'@'%' IDENTIFIED BY 'hiveuser';

CREATE USER 'hiveuser'@'localhost' IDENTIFIED BY 'hiveuser';

GRANT SELECT,INSERT,UPDATE,DELETE,ALTER,CREATE ON metastore.* TO 'hiveuser'@'%';

GRANT SELECT,INSERT,UPDATE,DELETE,ALTER,CREATE ON metastore.* TO 'hiveuser'@'localhost';


### start hive ###

- HADOOP must be running (jps)
- create directory for file storage which can be accessed through hive
$HADOOP_HOME/bin/hadoop dfs -mkdir /user/hive/warehouse
$HADOOP_HOME/bin/hadoop dfs -mkdir /tmp

- Environmet variable
echo "export HIVE_HOME=/home/ubuntu/app/hive" >> ~/.bashrc
source ~/.bashrc

- STARTING HIVE
$HIVE_HOME/bin/hive --service hiveserver &
$HIVE_HOME/bin/hive


# Hive table with data

CREATE TABLE student(
id int comment 'student id',
name string comment 'name of student',
age int comment 'age of student'
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

show tables;

describe student;

LOAD DATA LOCAL INPATH '/home/ubuntu/hadoop-workshop/handson/hive/student.tsv' OVERWRITE INTO TABLE student;

select * from student;

select name from student;

# Hive table with partition

CREATE TABLE alumni(
id int comment 'student id',
name string comment 'name of student',
age int comment 'age of student'
) 
PARTITIONED BY(year int)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

show tables;

describe alumni;

LOAD DATA LOCAL INPATH '/home/ubuntu/hadoop-workshop/handson/hive/alumni2013.tsv' OVERWRITE INTO TABLE alumni PARTITION(year=2013);

show partitions alumni;

LOAD DATA LOCAL INPATH '/home/ubuntu/hadoop-workshop/handson/hive/alumni2014.tsv' OVERWRITE INTO TABLE alumni PARTITION(year=2014);

show partitions alumni;

- advantages of partitions 

select * from alumni where year=2013;
select * from alumni where id=1;