Pentaho

 View Only

 Big Query Connection from Pentaho

  • Pentaho
  • Pentaho
Mandar Deshpande's profile image
Mandar Deshpande posted 05-22-2019 10:28

Hi,

I have installed Pentaho 8.2. We have database on Google BigQuery which we are trying to connect.

I am trying to create new connection to Big query.

I have copied simba Jar files (4.2) under

I am using below inforomation \server\pentaho-server\tomcat\webapps\pentaho\WEB-INF\lib

GoogleBigQueryJDBC42.jar

google-http-client-1.22.0.jargoogle-http-client-jackson2-1.22.0.jargoogle-oauth-client-1.22.0.jargoogle-api-client-1.22.0.jargoogle-api-services-bigquery-v2-rev355-1.22.0.jar

In Pentaho, I am creating new connection using below info,

General:

Host: https://www.googleapis.com/bigquery/v2Database: Project ID in Google CloudPort: 443User Name: BlankPassword: Blank

In options (Tab), below options are used

OAuthType: 0

OAuthServiceAcctEmail: pentaho-new@<Project_ID>.iam.gserviceaccount.comOAuthPvtKeyPath: D:\Downloads\<Key>.p12Timeout: 120

And I am getting error 'ConnectionServiceImpl.Error_00009 - Connection to database [Project ID] Failed.

Below are details in log file.

2019-05-21 18:45:32,377 ERROR [org.pentaho.platform.dataaccess.datasource.wizard.service.impl.ConnectionServiceImpl] Error end:

2019-05-21 18:45:39,543 ERROR [org.pentaho.platform.dataaccess.datasource.wizard.service.impl.ConnectionServiceImpl] Error Start: Pentaho pentaho-platform-core 8.2.0.0-3422019-05-21 18:45:39,544 ERROR [org.pentaho.platform.dataaccess.datasource.wizard.service.impl.ConnectionServiceImpl] ::: ConnectFactory.ERROR_0001 - Database connection could not be established to: com.simba.googlebigquery.jdbc42.Driver jdbc:bigquery:// https://www.googleapis.com/bigquery/v2:443;ProjectId=<Project_ID>;;OAuthServiceAcctEmail=pentaho-new@<Project_ID>.iam.gserviceaccount.com;OAuthPvtKeyPath=D:\Downloads\<File>.p12;Timeout=120;OAuthType=0java.lang.NoClassDefFoundError: com/google/auth/http/HttpTransportFactory

I have verified using client tool that service account and P12 works fine.

Can someone help what I am missing here?


#Pentaho
Paulo Pires's profile image
Paulo Pires posted 05-22-2019 15:10

Hi Mandar,

Copy the driver to \server\pentaho-server\tomcat\lib instead and test it again.

Restart the server after copying the driver.

Best regards

Mandar Deshpande's profile image
Mandar Deshpande posted 05-24-2019 08:36

Hi Paulo,

Thank you for response. I tried this option. But still same error.

I copied all 6 files. 

GoogleBigQueryJDBC42.jargoogle-http-client-1.22.0.jargoogle-http-client-jackson2-1.22.0.jargoogle-oauth-client-1.22.0.jargoogle-api-client-1.22.0.jargoogle-api-services-bigquery-v2-rev355-1.22.0.jar

Paulo Pires's profile image
Paulo Pires posted 05-24-2019 09:10

Hi Mandar,

You where right about the folder to deploy the drivers.

Follow the steps in Google BigQuery - Pentaho Documentation 

If you still have an error then it's best to contact support.

Best regards

Ana Ilharco's profile image
Ana Ilharco posted 01-31-2020 17:39

@Paulo Pires​ me and other colleagues have done exactly as stated on the Google Biq Query Documentation for PDI, in versions 8.2 or 8.3 one EE, other CE. It doesn't matter, the problem is always something like this for the rest of us:

 

Error connecting to database: (using class com.simba.googlebigquery.jdbc42.Driver)com/google/auth/http/HttpTransportFactory  org.pentaho.di.core.exception.KettleDatabaseException: Error occurred while trying to connect to the database Error connecting to database: (using class com.simba.googlebigquery.jdbc42.Driver)com/google/auth/http/HttpTransportFactory  at org.pentaho.di.core.database.Database.normalConnect(Database.java:472)at org.pentaho.di.core.database.Database.connect(Database.java:370)at org.pentaho.di.core.database.Database.connect(Database.java:341)at org.pentaho.di.core.database.Database.connect(Database.java:331)at org.pentaho.di.core.database.DatabaseFactory.getConnectionTestReport(DatabaseFactory.java:83)at org.pentaho.di.core.database.DatabaseFactory.getConnectionTestResults(DatabaseFactory.java:112)at org.pentaho.di.core.database.DatabaseMeta.testConnectionSuccess(DatabaseMeta.java:2811)at org.pentaho.ui.database.event.DataHandler.testDatabaseConnection(DataHandler.java:621)at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)at java.lang.reflect.Method.invoke(Method.java:498)at org.pentaho.ui.xul.impl.AbstractXulDomContainer.invoke(AbstractXulDomContainer.java:313)at org.pentaho.ui.xul.impl.AbstractXulComponent.invoke(AbstractXulComponent.java:157)at org.pentaho.ui.xul.impl.AbstractXulComponent.invoke(AbstractXulComponent.java:141)at org.pentaho.ui.xul.swt.tags.SwtButton.access$500(SwtButton.java:43)at org.pentaho.ui.xul.swt.tags.SwtButton$4.widgetSelected(SwtButton.java:137)at org.eclipse.swt.widgets.TypedListener.handleEvent(Unknown Source)at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source)at org.eclipse.swt.widgets.Display.sendEvent(Unknown Source)at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source)at org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source)at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source)at org.eclipse.jface.window.Window.runEventLoop(Window.java:820)at org.eclipse.jface.window.Window.open(Window.java:796)at org.pentaho.di.ui.xul.KettleDialog.show(KettleDialog.java:80)at org.pentaho.di.ui.xul.KettleDialog.show(KettleDialog.java:47)at org.pentaho.di.ui.core.database.dialog.XulDatabaseDialog.open(XulDatabaseDialog.java:116)at org.pentaho.di.ui.core.database.dialog.DatabaseDialog.open(DatabaseDialog.java:61)at org.pentaho.di.ui.spoon.delegates.SpoonDBDelegate.editConnection(SpoonDBDelegate.java:96)at org.pentaho.di.ui.spoon.Spoon.editConnection(Spoon.java:2714)at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)at java.lang.reflect.Method.invoke(Method.java:498)at org.pentaho.ui.xul.impl.AbstractXulDomContainer.invoke(AbstractXulDomContainer.java:313)at org.pentaho.ui.xul.impl.AbstractXulComponent.invoke(AbstractXulComponent.java:157)at org.pentaho.ui.xul.impl.AbstractXulComponent.invoke(AbstractXulComponent.java:141)at org.pentaho.ui.xul.jface.tags.JfaceMenuitem.access$100(JfaceMenuitem.java:43)at org.pentaho.ui.xul.jface.tags.JfaceMenuitem$1.run(JfaceMenuitem.java:106)at org.eclipse.jface.action.Action.runWithEvent(Action.java:498)at org.eclipse.jface.action.ActionContributionItem.handleWidgetSelection(ActionContributionItem.java:545)at org.eclipse.jface.action.ActionContributionItem.access$2(ActionContributionItem.java:490)at org.eclipse.jface.action.ActionContributionItem$5.handleEvent(ActionContributionItem.java:402)at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source)at org.eclipse.swt.widgets.Display.sendEvent(Unknown Source)at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source)at org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source)at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source)at org.pentaho.di.ui.spoon.Spoon.readAndDispatch(Spoon.java:1381)at org.pentaho.di.ui.spoon.Spoon.waitForDispose(Spoon.java:7817)at org.pentaho.di.ui.spoon.Spoon.start(Spoon.java:9179)at org.pentaho.di.ui.spoon.Spoon.main(Spoon.java:707)at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)at java.lang.reflect.Method.invoke(Method.java:498)at org.pentaho.commons.launcher.Launcher.main(Launcher.java:92)Caused by: org.pentaho.di.core.exception.KettleDatabaseException: Error connecting to database: (using class com.simba.googlebigquery.jdbc42.Driver)com/google/auth/http/HttpTransportFactory at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:585)at org.pentaho.di.core.database.Database.normalConnect(Database.java:456)... 57 moreCaused by: java.lang.NoClassDefFoundError: com/google/auth/http/HttpTransportFactoryat com.simba.googlebigquery.googlebigquery.core.BQConnection.connect(Unknown Source)at com.simba.googlebigquery.jdbc.common.BaseConnectionFactory.doConnect(Unknown Source)at com.simba.googlebigquery.jdbc.common.AbstractDriver.connect(Unknown Source)at java.sql.DriverManager.getConnection(DriverManager.java:664)at java.sql.DriverManager.getConnection(DriverManager.java:208)at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:570)... 58 moreCaused by: java.lang.ClassNotFoundException: com.google.auth.http.HttpTransportFactoryat java.net.URLClassLoader.findClass(URLClassLoader.java:381)at java.lang.ClassLoader.loadClass(ClassLoader.java:424)at java.lang.ClassLoader.loadClass(ClassLoader.java:357)... 64 more

We've already googled it and I personally succeed setting up this in DBeaver, for example. So, it seems more like a driver problem?

 

Related Content