Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

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

avatar
Expert Contributor

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?

1 ACCEPTED SOLUTION

avatar
Expert Contributor

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)

View solution in original post

2 REPLIES 2

avatar
Expert Contributor

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)

avatar
Super Collaborator

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

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