Connect Hive with Cognos 10.2

[satish217_1990]

Hey all.. I have few tables in Hive. Want to use those tables in Cognos Report Studio. Can someone guide me how to connect the Hive DB with Cognos 10.2? I don't see anything related to Hive in New Data Source Creation wizard. Please help.

[MFGF]

Hey all.. I have few tables in Hive. Want to use those tables in Cognos Report Studio. Can someone guide me how to connect the Hive DB with Cognos 10.2? I don't see anything related to Hive in New Data Source Creation wizard. Please help.

Hi,

Not sure if you have this option back in 10.2, but in case, here goes:

1. Create a new data source. Give it a name and press Next
2. Choose "JDBC" from the Type dropdown and press Next

3. Choose "Hive" from the JDBC Type dropdown, then fill in the connection details


I can't remember when this option was added, but hopefully it's there in your 10.2 build.

Cheers!

MF.

[Cognos8]

Hi,

ODBC hive connection works fine  but JDBC Hive is not working. i have raised PMR on this will keep you posted. if you found any solution let me know.

[MFGF]

Hi,

ODBC hive connection works fine  but JDBC Hive is not working. i have raised PMR on this will keep you posted. if you found any solution let me know.

Have you copied the Hive JDBC jar files into your Cognos instance?

MF.

[satish217_1990]

MFGF.. Thanks for your reply. I will try this and let you know.

[satish217_1990]

I have pasted the .JAR files in the respective path and chose "Hive" from the JDBC Type dropdown in Data Source Creation Wizard.. But when I test the connection, I get the below error.. Please help..

XQE-JDB-0004 A problem occurred finding the driver class "org.apache.hadoop.hive.jdbc.HiveDriver".

[MFGF]

I have pasted the .JAR files in the respective path and chose "Hive" from the JDBC Type dropdown in Data Source Creation Wizard.. But when I test the connection, I get the below error.. Please help..

XQE-JDB-0004 A problem occurred finding the driver class "org.apache.hadoop.hive.jdbc.HiveDriver".

Did you restart your Cognos service after copying in the files? Where in the instance did you copy them to?

MF.

[Cognos8]

Hi,

I faced the same error after placing Hive and impala .jar files. IBM confirmed i have placed right .jar files. Still the PMR is pending with IBM.

[satish217_1990]

Did you restart your Cognos service after copying in the files? Where in the instance did you copy them to?

MF.

I copied the below .jar files to <c10 install folder>\webapps\p2pd\web-inf\lib. My Hadoop version is 1.2.1 and Hive version is 0.14.0

• hive-metastore-0.14.0.jar
  libthrift-0.9.1.jar
  hive-exec-0.14.0.jar
  hive-jdbc-0.14.0.jar
  libfb303-0.9.1.jar
  hadoop-core-1.2.1.jar
  hive-service-0.14.0.jar

I have restarted the Cognos service and now I'm getting the below (different) error. Please help.

XQE-DS-0006 Unable to logon to the data source.
Could not establish connection to <ip-address>:10000/default:java.net.ConnectException: Connection timed out: connect

[Cognos8]

Hi,

Yes i faced this issue as well, eventhough uid and pwd are correct. Place your .jar files in c10 v5dataserver as well and restart cognos service

[pk.mahadevan]

RE: Satish

Any update on connecting successfully using the JDBC connectivity? Thank you

Regards,
PK

[satish217_1990]

Hi PKM..

No. I'm still stuck up and getting the below error.
Unable to connect to data source
could not establish connection to java.net.connectexception: connection timed out

If anyone have ideas please post here please.

[pk.mahadevan]

Thanks Satish!

Any link or document that you guys followed for the JDBC Driver installation in the Cognos BI Servers?

Regards,
PK

[MFGF]

Thanks Satish!

Any link or document that you guys followed for the JDBC Driver installation in the Cognos BI Servers?

Regards,
PK

Hi,

Take a look at the Dynamic Query Cookbook:

http://www.ibm.com/developerworks/data/library/cognos/infrastructure/cognos_specific/page529-pdf.pdf

There is a chapter on configuring data source connectivity.

Cheers!

MF.

[satish217_1990]

I have installed Cognos 10.2.1 FP3 since it supports till Hive 0.12.0

But now I get the below error.
XQE-JDB-0012 Could not load the driver "class org.apache.hadoop.hive.jdbc.HiveDriver" because it is not certified for use with this product.

[MFGF]

I have installed Cognos 10.2.1 FP3 since it supports till Hive 0.12.0

But now I get the below error.
XQE-JDB-0012 Could not load the driver "class org.apache.hadoop.hive.jdbc.HiveDriver" because it is not certified for use with this product.

Did you manually enter that string (org.apache.hadoop.hive.jdbc.HiveDriver) as the class name? It doesn't look correct to me - it should be org.apache.hive.jdbc.HiveDriver

MF.

[the6campbells]

Each release of Apache Hive will require that you have the equivalent Hive JDBC driver.

The set of JAR files that Apache distributes will vary across those releases.

Hence, you need to look at the Apache Hive WIKI site or the equivalent online documentation that Hortonworks, Cloudera, Mapr, Amazon or whomever supplies for the distro of Hive you are planning to use.

Do not assume that you can mix different versions of Apache drivers with servers.

Ensure you copied the set of JAR files under <c10x>\webapps\p2pd\WEB-INF\lib

Connections as of Hive 0.10 would normally point to HiveServer2 versus HiveServer - for more details reference the Apache documentation.

https://cwiki.apache.org/confluence/display/Hive/Home#Home-HiveDocumentation