CODEWITHSUNDEEP
hadoophivehiveql
Maverick
Maverick

Reputation: 748

Select if table exists in Apache Hive

I have a hive query which is of the format,

select . . . from table1 left join (select . . . from table2) on (some_condition)

The table2 might not be present depending on the environment. So I would like to join if only table2 is present otherwise just ignore the subquery.

The below query returns the table_name if it exists,

show tables in {DB_NAME} like '{table_name}'

But I dont know how I can integrate this into my query to select only if it exists.

Is there a way in hive query to check if a table exists before selecting.

Appreciate any help

Note: I do not want to create the table if it doesn't exist.

Upvotes: 5

Views: 2726

Answers (1)

GoodDok
GoodDok

Reputation: 1850

It was already mentioned in the comments that Hive does not support if-else construction, so if you want to have it, you'll have to borrow it from the languages like bash or HPL/SQL.

What I suggest here is the following construction:

  1. Place the two versions of the query into a separate files as view definitions:

view_ddl_if_exists.hql:

create view if not exists target_view
as
select . . . from table1 left join (select . . . from table2) on (some_condition)

view_ddl_if_not_exists.hql:

create view if not exists target_view
as
select . . . from table1
  1. Add shell script for detecting an actual view definition and copying to a predefined place:

place_correct_view_source.sh

if hive -S -e 'explain select 1 from table2' &>/dev/null; then
  cp view_ddl_if_exists.hql actual_view_ddl.hql
else 
  cp view_ddl_if_not_exists.hql actual_view_ddl.hql
fi
  1. Add the following into your script/init script:
!bash place_correct_view_source.sh;
source actual_view_ddl.hql;
...

Voila! You've got the correct query in the view target_view and can use it in your scripts.

Upvotes: 0

Related Questions