Hive Notes - Part 2

Hive:
------
use <db name>
show databases;
show tables;
describe formatted | extended table;
create database <db name>
drop database <db name> (cascade)


hive -e 'select * from hive_demo.salary_orc limit 5'

hive -S -e 'select * from hive_demo.salary_orc limit 5'

hive -S -e 'select * from hive_demo.salary_orc limit 5' -hiveconf hive.root.logger=DEBUG,console

hive -i initialize.sql

hive -f script.sql


source file_name

dfs -ls /user

!ls

set mapred.reduce.tasks = 32

set hive.TAB

set

reset

add jar jar_path

list jars

delete jar jar_name


/user/hue/hdfs-course/awe.csv

Hive:

show databases;
use sara;
show tables;
drop table txnrecords;

!ls -l

dfs -ls /user/hue/hdfs-course/awe.csv

dfs -cat /user/hue/hdfs-course/awe.csv

Build the table
Write HiveQL query


CREATE DATABASE stock_values;
CREATE DATABASE stock_values;


CREATE TABLE apple (date STRING, open INT, high INT, low INT, close INT, adj_close DECIMAL, volume INT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' lines terminated by '\n' tblproperties('skip.header.line.count'='1');

DESCRIBE FORMATTED apple;

LOAD DATA INPATH '/user/hue/hdfs-course/awe.csv' OVERWRITE INTO TABLE apple;

select date,high from apple where high < "20";



create external table employee
(
id string,
fname string,
lname string,
department_id string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/user/sample_data/employee'

 LOAD DATA INPATH '/user/sample_data/employee.csv' OVERWRITE INTO TABLE employee;

  select * from employee;

  select * from employee limit 2;

  create external table department ( id string,dept_name string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/user/sample_data/department';

  CREATE EXTERNAL TABLE salary (salary_id string, employee_id string, payment double, date string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/user/sample_data/salary';


  displayt the current db in prompt:
set hive.cli.print.current.db = true;