We're facing a blocking issue when trying to connect Admintool to a Hive instance by using any ODBC driver.
For a POC we're trying to setup a Hive subject area to be analyzed in OBIEE. Everything seems to work OK
until we try to load the metadata. In this case, the schema is found but no table under it. We can add the
physical table by hand, and then we can successfully launch the row count. We then try to load the rows, but at
this point we've faces a strange behaviour. But let's summarize the steps we've followed.
- we've downloaded the ODBC drivers to connect to Hive (we've tried with Hortonworks, Cloudera, Microsoft and
MapR, but the results are exactly the same);
- we've configured the connection to Hive on port 10000 and successfully run the ODBC test (BTW: from
SQLDeveloper we can connect to the same server via JDBC driver and query the tables there);
- then we start Admintool; once we define the name and password, we then select the ODBC connection;
actually username and password are not strictly needed, but we provide them anyway;
- next step is the to load metadata; and here is the first issue because we can see the schemas but not
the tables.
- anyway we go on and insert one schema (we've read there could be problems with more than one schema, so
we just select one); in a blog (by Rittman, who's really "THE" OBIEE guru) it is stated that the tables are not
loaded directly, so we've tried by simply adding a Physical table in the Physical layer under the default schema,
making sure the table does exist;
- once we've "physically" added the table we proceed with the row count; and we correctly get the number of
rows once we flyover the table name after this;
- then we try to retrieve the rows and here's the issue; we get the following error message:
We've also digged a little bit and found that the query received by Hive instead of being
SELECT * FROM trucks
it becomes
SELECT FROM trucks
so the '*' is dropped from the SQL statement sent. If we run the same query on Hive we get
exactly the same ParseException error, so it really seems that there's a problem in the
construction of the SQL statement.
Any idea about to fix this ? (if a fix does exists, because after so many attempts we are convinced
it's a bug which can only be fixed by a new release of Admintool)
BTW: we've tried almost all the possible options for the ODBC driver (like "Use native query",
"Use Unicode" and so on) and with regards to Admintool we haven't seen which option
or preference (at physical layer) can influence this anomaly.
Any help is really, really appreciated. Thanks in advance.