Hive Variations

Run Hive query from commmand line :

$ hive -e “selcect * from student.subjects”;

How to run a file containing hive query :

We can run a file containing hive query with any extensions like .sql,.hql,.txt or even a plain file

hive -f “/home/administrator/hive/demo.txt”

Writing Hive output 

Writing to HDFS :

INSERT OVERWRITE DIRECTORY ‘/PATH/To/HDFS’
select * from tabelename

We can write data to local directories as well like this just by putting Keyword LOCAL:

INSERT OVERWRITE LOCAL DIRECTORY ‘/home/username/hive_outpus/tablename’
select * from tabelename

Data written to the filesystem is serialized as text with columns separated by ^A and rows separated by newlines. If any of the columns are not of primitive type, then those columns are serialized to JSON format.

But hive now supports to write data to file in any format like how we import data into hive.

eg:

INSERT OVERWRITE LOCAL DIRECTORY ‘/home/administrator/Data/POC/DataScience/HiveOutput/cohort’
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘,’
select * from tabelename

We can write hive output to another tables also.

insert OVERWRITE table2
select * from tablename;

We need to first create output table before executing the query and writing output to it. We can have internal or external tables to write this output.