HIVE table/column description does not display in Power BI

[#1]

Hi Community,

I'd like to know if it's possible to display a table or column description from Power BI that is connected to Hive using ODBC driver?

Issue is following: I set a comment in a Hive table and on its columns and I would expect that these are loaded and visible as "Description" fields from external tools (like Power BI).

For example:

create table mytable1(mycol1 string comment 'column description') stored as ORC TBLPROPERTIES('comment' = 'table description');

I would expect that when Power BI accesses this table, it would retrieve these comments as descriptions of the table/column, but the "Description" fields are all 'null'.

Note that these table/column descriptions are visible from HUE.

Thanks in advance for your anwsers.

[#2]

@anjel could you share the query you are running from Hue or powerbi that you expect to return column/table comments?

Does describe <table> not return the column comments in powerBI? What's the JDBC/ODBC driver version you are using and is it shared by Cloudera?

[#3]

Hi @smruti,

And thanks for your reply. Sorry for my late answer but I was out of office.

I don't know precisely how Power BI (i.e. the driver) is supposed to extract the column or table description, either from a "describe" or from sys.columns_v2 table (for example); this is an internal mechanism not a request that I'm executing.

I tried the ClouderaHive_ODBC_2.6.13.1013 driver version as well as the "Hive LLAP" driver included within Power BI.

On Hue, I'm using the "Table Browser" that displays the table/column descriptions correctly and here is a screenshot:

And here is a Power BI Query editor screenshot, where the table Description field is "null":

[#4]

@anjel Power BI does not have a native Hive connector. I guess you could only connect using an ODBC connector. I just tried listing a table using DbVisualizer, and and it shows the column comments as REMARKS. 

So, I would say it is specific to the Hive client(Power BI)

[#5]

@smruti, PowerBI desktop has a native (in the sense that it's usable out of the box after installing PowerBI) Hive connector called "LLAP Hive"; it works well and I used it for the screenshot.

You may be right that there is maybe an issue how PowerBI gathers these descriptions, but I more likely suspect an issue in the ODBC implementation of the driver; let me explain.

I've read that in the JDBC/Java specification you can collect table and column metadata (through java.sql.DatabaseMetaData class, getColumns() method call [https://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getColumns(java.lang.String...)]) and the specification shows all columns that you see in DbVizualizer, and REMARKS (12th) columns in particular...
Now in the ODBC specifications [https://learn.microsoft.com/en-us/sql/odbc/reference/syntax/sqlcolumns-function?view=sql-server-2017] for SQLColumns, it also defines "REMARKS" as the 12th column returned by the function.

I installed the Cloudera Hive ODBC Linux driver and configured a odbc.ini file (Cloudera provides installation instructions) to connect to my HiveServer2 instance.
Then I used a ODBC sql client called isql (from unixODBC).

[bash]$ isql -v "Cloudera Hive 64-bit" "userXXX" "passwordXXXX" -m20

SQL> help null test_anj firsttable null
+---------------------+---------------------+---------------------+---------------------+---------------------+
| TABLE_QUALIFIER     | TABLE_OWNER         | TABLE_NAME          | TABLE_TYPE          | REMARKS             |
+---------------------+---------------------+---------------------+---------------------+---------------------+
| HIVE                | test_anj            | firsttable          | TABLE               |                     |
+---------------------+---------------------+---------------------+---------------------+---------------------+
SQLRowCount returns 1
1 rows fetched
SQL> describe test_anj.firsttable
+---------------------+---------------------+---------------------+
| col_name            | data_type           | comment             |
+---------------------+---------------------+---------------------+
| a                   | bigint              | la description      |
| b                   | string              | colonne b           |
| c                   | int                 |                     |
+---------------------+---------------------+---------------------+
SQLRowCount returns -1
3 rows fetched

Here I see that this "generic" client can display column comments, but it cannot display the 'REMARKS' for the table.

When using a JDBC client (like you, DbVisualizer on Linux):

Therefore, I suspect (without certitude) that there could be an issue in the Hive ODBC driver that does not return the "REMARKS" at table level, whereas it can display column level comments.

On the other hand, I agree that the Hive JDBC driver works fine for both table/column remarks.

As a comparison, I added a comment on a MariaDB table and used the appropriate ODBC driver: the description is displayed correctly in isql.

[bash ~]$ isql -v "MariaDB-server" -m20
SQL> help null null test_table null
+---------------------+------------+---------------------+---------------------+---------------------+
| TABLE_CAT           | TABLE_SCHEM| TABLE_NAME          | TABLE_TYPE          | REMARKS             |
+---------------------+------------+---------------------+---------------------+---------------------+
| testdb              |            | test_table          | TABLE               | a table description |
+---------------------+------------+---------------------+---------------------+---------------------+
SQLRowCount returns 1
1 rows fetched

[#6]

@anjel Are we saying we are able to see the column comments but table comment missing?

Could you please try adding table comment as follows, instead of setting it in table properties?

CREATE TABLE name(column1 string comment 'column comment') COMMENT "table comment")

[#7]

@smruti, thanks for your suggestion.

I tried but the result is the same.

create table test_anj.comment_testtable(col1 string comment 'col1 comment') comment 'testtable comment';

Under isql:

SQL> help null null comment_testtable null
+---------------------+---------------------+---------------------+---------------------+---------------------+
| TABLE_QUALIFIER     | TABLE_OWNER         | TABLE_NAME          | TABLE_TYPE          | REMARKS             |
+---------------------+---------------------+---------------------+---------------------+---------------------+
| HIVE                | test_anj            | comment_testtable   | TABLE               |                     |
+---------------------+---------------------+---------------------+---------------------+---------------------+
SQLRowCount returns 1
1 rows fetched

Same behavior on PowerBI as for previous table.

In the meantime, I opened a Cloudera support case for this matter and after discussion with support team, I should soon be in contact with the Cloudera Hive ODBC Connector team to discuss this matter. I'll keep you informed.

[#8]

@anjel Thank you! I can see that Support has reached out to the ODBC driver team. We'll wait for their response.

[#9]

@anjel, Has the reply helped resolve your issue? If so, please mark the appropriate reply as the solution, as it will make it easier for others to find the answer in the future.  

[#10]

Hi Vidya, no solution yet.