13 Replies Latest reply on Feb 17, 2016 9:36 AM by rmoff

    Connecting OBIEE Admintool to Hive : issues with ODBC. Any option ?

    923551
    923551 Feb 9, 2016 10:04 PM

      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.

      1) 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);

      2) 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);

      3) 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;

      4) next step is the to load metadata; and here is the first issue because we can see the schemas but not

      the tables.

      Connection_OBIEE_BigData.PNG

      5) 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;

      6) 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;

      7) then we try to retrieve the rows and here's the issue; we get the following error message:

      ErroreSQL.PNG

      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.

      • 310 Views
      • Tags: