如何检查Hive中是否存在表？

I am connecting to Hive via an ODBC driver from a .NET application. Is there a query to determine if a table already exists?

我通过.NET应用程序中的ODBC驱动程序连接到Hive。是否有查询来确定表是否已存在？

For example, in MSSQL you can query the INFORMATION_SCHEMA table and in Netezza you can query the _v_table table.

例如，在MSSQL中，您可以查询INFORMATION_SCHEMA表，在Netezza中，您可以查询_v_table表。

Any assistance would be appreciated.

任何援助将不胜感激。

6 个解决方案

#1

There are two approaches by which you can check that:

有两种方法可以检查：

1.) As @dimamah suggested, just to add one point here, for this approach you need to

1.）正如@dimamah建议的那样，只需要在这里添加一点，就需要这种方法

 1.1) start the **hiveserver** before running the query
 1.2) you have to run two queries
      1.2.1) USE <database_name>
      1.2.2) SHOW TABLES LIKE 'table_name'
      1.2.3) Then you check your result using Result set.

2.) Second approach is to use HiveMetastoreClient APIs, where you can directly use the APIs to check whether the table_name exist in a particular database or not.

2.）第二种方法是使用HiveMetastoreClient API，您可以直接使用API来检查table_name是否存在于特定数据库中。

For further help please go through this Hive 11

如需进一步帮助，请浏览此Hive 11

#2

Execute the following command : show tables in DB like 'TABLENAME'
If the table exists, its name will be returned, otherwise nothing will be returned.
This is done directly from hive. for more options see this.

执行以下命令：在DB中显示表格'TABLENAME'如果表存在，将返回其名称，否则将不返回任何内容。这是直接从蜂巢完成的。有更多选项，请看这个。

DB is the database in which you want to see if the table exists.
TABLENAME is the table name you seek,

DB是您希望查看表是否存在的数据库。 TABLENAME是您寻找的表名，

What actually happens is that Hive queries its metastore (depends on your configuration but it can be in a standard RDBMS like MySQL) so you can optionally connect directly to the same metastore and write your own query to see if the table exists.

实际发生的是Hive查询其Metastore（取决于您的配置，但它可以在MySQL之类的标准RDBMS中），因此您可以选择直接连接到同一Metastore并编写您自己的查询以查看该表是否存在。

#3

When programming on Hive by Spark SQL, you can use following method to check whether Hive table exists.

通过Spark SQL在Hive上编程时，可以使用以下方法检查Hive表是否存在。

if (hiveContext.hql("SHOW TABLES LIKE '" + tableName + "'").collect().length == 1) {

println(tableName + " exists")

}

#4

If someone is using shell script like me then my answer could be useful. Assume that your table is in the default namespace.

如果有人像我一样使用shell脚本，那么我的答案可能会有用。假设您的表位于默认命名空间中。

table=your_hive_table
validateTable=$(hive --database default -e "SHOW TABLES LIKE '$table'")
if [[ -z $validateTable ]]; then
  echo "Error:: $table cannot be found"
  exit 1
fi

#5

Code similar to below one can find in many of my Spark notebooks:

类似于下面的代码可以在我的许多Spark笔记本中找到：

stg_table_exists = sqlCtx.sql("SHOW TABLES IN "+ stg_db) 
                  .filter("tableName='%s'" % stg_tab_name) .collect()

(made two-liner for readability)

（为了便于阅读，制作双线）

I wish Spark would have an API call to check the same.

我希望Spark有一个API调用来检查相同的。

#6

If you're using SparkSQL you can do the following.

如果您使用的是SparkSQL，则可以执行以下操作。

if "table_name" in sqlContext.tableNames("db_name"):
    ...do something

http://spark.apache.org/docs/2.1.0/api/python/pyspark.sql.html#pyspark.sql.SQLContext.tableNames

#1