How to check if a table exists in Hive?

sql odbc hive
Michael Robinson picture Michael Robinson · Jan 17, 2014 · Viewed 48.9k times · Source

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

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

Any assistance would be appreciated.

Answer

dimamah picture dimamah · Jan 18, 2014

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 is the database in which you want to see if the table exists.
TABLENAME is the table name you seek,

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.

Related questions

Need a row count after SELECT statement: what's the optimal SQL approach?

I'm trying to select a column from a single table (no joins) and I need the count of the number of rows, ideally before I begin retrieving the rows. I have come to two approaches that provide the information I …

Read More
Does MS Access support "CASE WHEN" clause if connect with ODBC?

Does ODBC support CASE WHEN clause for MS Access? Is there any other database which does not support the CASE WHEN clause? I tried the following query while connecting to MS Access with ODBC but get an exception. SELECT (CASE …

Read More
Check if a SQL table exists

What's the best way to check if a table exists in a Sql database in a database independant way? I came up with: bool exists; const string sqlStatement = @"SELECT COUNT(*) FROM my_table"; try { using (OdbcCommand cmd = new OdbcCommand(sqlStatement, …

Read More