question Re: How to select the last table from a list of hive tables? in Archives of Support Questions (Read Only)

How to select the last table from a list of hive tables?

Alexraj84 — Thu, 30 Jun 2016 13:15:28 GMT

I have a list of hive tables and want to select the last table for performing some query. Here is what I use to get the list of similar hive tables.

show tables 'test_temp_table*';

It displays the below result

test_temp_table_1
test_temp_table_2
test_temp_table_3
test_temp_table_4
test_temp_table_5
test_temp_table_6

I need to run some query on test_temp_table_6. I can do this using shell script by writing the output to a temp file and reading the last value from it but is there a simple way using hive query to get the last table that has the maximum number at the end?

Re: How to select the last table from a list of hive tables?

Alexraj84 — Thu, 30 Jun 2016 19:13:25 GMT

Finally, I got the answer from Stackoverflow and wanted to propagate here. There isn't seems to be a straight way to get the last table name however the answer works just using a single line of shell script including the hive query. Here is it.

last_table=$(hive -e "show tables 'test_temp_table*';" | sort -r | head -n1)

Re: How to select the last table from a list of hive tables?

pankaj_singh — Thu, 30 Jun 2016 19:52:11 GMT

@Alex Raj You can also create a script using 2 commands

Execute "show tables; " to get all tables
For each table execute "desc formatted < database >.< table_name >" . The output contains a field CreateTime.
use CreateTime field to find the latest table.