Not able to pull Hive Metadata & run queries

Hi

I am connected to a Kerberized CDH cluster through DbVis from Mac OSX Siera.

I am able to successfully see the schemas, tables and table data from the data tab and pull row counts from the tabs as well.

But, I am not able to

1. run any query - it always says "Database connection has been lost. Re-establish database connection for <name>. Even if I do a - Reconnect & Execute, the query is not running.

2. I am not able to pull column names of any table. 

Any idea how to resolve these issues? Thanks.

Hi,

For 1), please see this page:

https://support.dbvis.com/solution/articles/1000196590-any-statement-i-execute-in-the-sql-commander-results-in-database-connection-is-not-open-

For 2), please open Tools->Debug, enable debugging, and then run the operation that fails. Open Help->Contact Support to report the issue, making sure Attach Logs is checked.

Best Regards,

Hans

Thanks for the reply. 

For 1 )

I can execute "select 1;" from Hue just fine. So, that is the validation statement to keep the connection alive. But, seeing the debug window, it is not able to execute that sql.  

If this is not the common statement for Hive, what should be used.

For 2 )

have attached logs thru Help-Contact Support. Thanks.

Hi Prasanna,

I answered the ticket you submitted through Help->Contact Support, but basically it looks like a JDBC driver configuration/version issue. Please use the ticket for any follow-up instead of this forum thread.

Best Regards,

Hans

Hans

Have replied back to the ticket. 

Couple of questions -

1. Does the app support pulling metadata for External Tables?

2. When pulling data through data tab for any table - the query being executed is 

select * from table_name 

 - which is not smart - especially when we are dealing with huge hive tables. 

Can you enhance the feature to execute query with Limit statement if the database is Hive?

select * from table_name limit nn - where nn can be set by user?

Otherwise, even though we limit the number of rows to be displayed in the preferences - it keeps pulling large amount of data. It bogs down the resources and slows down everything if the table is huge. 

Thanks.

I answered your ticket, but just in case others read this:

1) For databases that are not explicitly supported, like Hive, DbVisualizer relies 100% on the JDBC driver; if the driver can provide metadata for External Tables, then DbVisualizer displays that metadata.

2) The Max Rows field in DbVisualizer is used to limit the number of results from a query. DbVisualizer calls the corresponding setMaxRows() method in the JDBC driver with this value, and it is up to the JDBC driver to honor it, e.g. by adding a LIMIT clause to the SQL it sends to the database. See

  http://confluence.dbvis.com/display/UG95/Using+Max+Rows+and+Max+Chars+for+a+Table. 

If the driver does not honor the setMaxRows() value, you need to use WHERE clause filters to limit the number of rows.

Best Regards,

Hans