Working with Hive Tables

HIVE is a data warehousing infrastructure on hadoop which provides capabilities for data storage in hadoop filesystem. It is designed for easy data summarization,ad-hoc analysis of larger volume of data from hadoop . It has own SQL-style language to work on this hive database and we call it HQL.

Here are some HQL commands to work with hive database.

CREATE HIVE Table

CREATE TABLE employee ( empid int, emp_name string,salary string, hire_dt timestamp)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ‘\t’

LINES TERMINATED BY ‘\n’

STORED AS TEXTFILE;

Browse HIVE Tables 

SHOW TABLES; --To show all tables

SHOW TABLES ‘emp*’; --To show tables start with letters emp 

DESCRIBE employee; --To show columns and those types

ALTER HIVE Tables

RENAME table name:

ALTER TABLE employee RENAME TO new_empl;

REPLACE all columns with new  list of columns in a table:

ALTER TABLE new_empl REPLACE COLUMNS (empid int, emp_name string,salary string);

ADD new columns:

ALTER TABLE new_empl ADD COLUMNS (hire_dt timestamp);

INSERT Data into HIVE Table

In HIVE, we have LOAD DATA command to insert data into tables.

Load data from local file-system file

LOAD DATA LOCAL INPATH '/home/cloudera/sample_emp_data.txt' OVERWRITE INTO TABLE

employee;

Load data from hadoop file-system file

LOAD DATA INPATH “/user/Hadoop_dir/sample_emp_data.txt” OVERWRITE INTO TABLE 

employee;

Note: OVERWRITE is optional to overwrite the table data .

DROP HIVE Tables

DROP TABLE new_empl;

.

HDFS basic commands

Here we will see some basic commands to work on Hadoop Distributed File System (HDFS).

Basic hdfs command syntax is

 $ hdfs dfs -unix_command

1. View hdfs directory

$ hdfs dfs –ls

2. Create a directory

$ hdfs dfs –mkdir /user/hadoop_dir

3. Change permission of directory or files

$ hdfs dfs –chmod –R 777 /user/hadoop_dir

4. Change ownership of file or directory

$ hdfs dfs –chown –R [owner_name] /user/hadoop_dir

5. Copy local file to hdfs directory

$ hdfs dfs –put “/home/local_dir/n1.txt” “/user/hadoop_dir”

Or

$ hdfs dfs –copyFromLocal “/home/local_dir/n1.txt” “/user/hadoop_dir”

6. Create a txt file under a Hadoop folder

$ hdfs dfs –touchz /user/hadoop_dir/n2.txt

7. Determine size of hdfs file (not local file)

$ hdfs dfs –du –h /user/hadoop_dir/n2.txt

8. Print first 5 lines from hdfs file

$ hdfs dfs –cat “/user/hadoop_dir/n2.txt” || head –n 5

9. Copy a file to another or same hdfs directory with different name

$ hdfs dfs –cp “/user/hadoop_dir/n1.txt” “/user/hadoop_dir/n1_copy.txt”

10. Copy hdfs file to local filesystem

$ hdfs dfs –get “/user/hadoop_dir/n1_copy.txt” “/home/local_dir/”

Or

$ hdfs dfs –copyToLocal “/user/hadoop_dir/n1_copy.txt” “/home/local_dir/”

11.  Delete file from hdfs directory

$ hdfs dfs –rm /user/hadoop_dir/n1_copy.txt

12. Delete hdfs directory

$ hdfs dfs –rm –r /user/hadoop_dir