选择表是否在Apache Hive中存在 [英] 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)

根据环境， 表2 可能不存在.因此，如果只有table2存在，我想加入，否则就忽略子查询.

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.

以下查询返回table_name(如果存在)

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.

感谢任何帮助

注意:如果表不存在，我不想创建它.

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

推荐答案

注释中已经提到Hive不支持 if-else 构造，因此，如果要使用它，您可以则必须从bash或.

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. 将查询的两个版本作为视图定义放置在单独的文件中:

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. 添加用于检测实际视图定义并将其复制到预定义位置的shell脚本:

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. 在脚本/初始化脚本中添加以下内容:

!bash place_correct_view_source.sh;
source actual_view_ddl.hql;
...

Voila！您已经在视图 target_view 中找到了正确的查询，并且可以在脚本中使用它.

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

这篇关于选择表是否在Apache Hive中存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！