CODEWITHSUNDEEP
hadoophive
Mike Wise
Mike Wise

Reputation: 22807

Format output in Hive Query

I am using a (simplified) query like this to get the status of my hive databases:

show databases; 
show tables

but it is a bit confusing because it runs all the lines together like this:

default
curtime_test
datetime_test
datettime_test
hivesampletable
sensor
sensor_part_subset
sensor_part_subset1

Is there anyway I can easily format the output, maybe add a header or something? I was hoping for something like this:

show databases
   default
show tables
   curtime_test
   datetime_test
   datettime_test
   hivesampletable
   sensor
   sensor_part_subset
   sensor_part_subset1

But this would be fine too:

---  show databases ---
default
---- show tables ---
curtime_test
datetime_test
datettime_test
hivesampletable
sensor
sensor_part_subset
sensor_part_subset1

Upvotes: 0

Views: 12568

Answers (2)

Rudi
Rudi

Reputation: 31

I am 4 years late but this may help someone :)

Option 1 - Use Beeline : It nicely formats the table output and delimits with pipe character '|' . However, it also pads the data so if you are copy pasting into something like Excel, you will have to deal with space paddings.

Option 2 - Redirect the output to a file : If you are running a script using hive -S -f <file_name> or hive -e "select statement here" then you can redirect the output to a file by using standard Unix redirection operator > . This needs some effort as you may have to convert tabs to pipes. E.g. the following command gives you pipe-delimited columns

hive -e "select first_name, last_name from employee_db.employee;" | sed -e 's/\t/|/g' > output_file.csv

Option 3 - If you use Jupyter, then use PyHive and Pandas to show data within the notebook itself. Here is a tutorial about PyHive.

Upvotes: 3

anand
anand

Reputation: 326

The below statement might add readability in your case.

select '------show databases----------';
show databases;
select '------show tables----------';
show tables;

Also use below statement to print header information.

set hive.cli.print.header=true;
show databases;
show tables;

Upvotes: 3

Related Questions