Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Pentaho Data Integration - Kettle - Insert data into Google BigQuery

  1. #1

    Default Pentaho Data Integration - Kettle - Insert data into Google BigQuery

    Hello,

    is there any sample i could follow to load data into Google BigQuery dataset tables using PDI?

    I use PDI 5.4 EE (Kettle).

    Please suggest if any latest versions of PDI (EE or CE) could do above request.

    Thanks.

  2. #2
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Why should there be a sample when Kettle supports anything accessible via JDBC?
    So long, and thanks for all the fish.

  3. #3
    Join Date
    Apr 2012
    Posts
    4

    Default FeatureNotSupportedException

    Quote Originally Posted by marabu View Post
    Why should there be a sample when Kettle supports anything accessible via JDBC?
    Hi, I'm using the jdbc driver provided by starschema (class net.starschema.clouddb.jdbc.BQDriver). Whe I try to insert rows in bigquery, the
    driver returns net.starschema.clouddb.jdbc.BQSQLFeatureNotSupportedException. I believe that this driver not support inserts, is this correct? do you know any free driver that support inserts?
    Thanks in advanced,
    Diego

  4. #4
    Join Date
    Jan 2017
    Posts
    6

    Question

    Quote Originally Posted by marabu View Post
    Why should there be a sample when Kettle supports anything accessible via JDBC?
    Hello Mr. Marabu, I'm using PDI 7.1 and i can connect GBQ with net star schema but, and I was trying for simple query and it's work and can get field but for GBQ advance query i got error and got , parameter are not set, how can I resolve this issue?

  5. #5
    Join Date
    Apr 2012
    Posts
    4

    Unhappy with simba driver, i can´t connect

    Marabu, I have tried to use simba driver that you suggest, but didn´t reach success.

    Someone succesfully used this driver in pentaho?

    When I test the connection, this exception occur:

    Error connecting to database [big query] 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.api.client.http.javanet.NetHttpTransport.<init>(Lcom/google/api/client/http/javanet/ConnectionFactory;Ljavax/net/ssl/SSLSocketFactory;Ljavax/net/ssl/HostnameVerifierV




    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.api.client.http.javanet.NetHttpTransport.<init>(Lcom/google/api/client/http/javanet/ConnectionFactory;Ljavax/net/ssl/SSLSocketFactory;Ljavax/net/ssl/HostnameVerifierV




    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:80)
    at org.pentaho.di.core.database.DatabaseMeta.testConnection(DatabaseMeta.java:2795)
    at org.pentaho.ui.database.event.DataHandler.testDatabaseConnection(DataHandler.java:598)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    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:60)
    at org.pentaho.di.ui.trans.step.BaseStepDialog.showDbDialogUnlessCancelledOrValid(BaseStepDialog.java:779)
    at org.pentaho.di.ui.trans.step.BaseStepDialog$EditConnectionListener.widgetSelected(BaseStepDialog.java:1398)
    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.pentaho.di.ui.trans.steps.tableinput.TableInputDialog.open(TableInputDialog.java:436)
    at org.pentaho.di.ui.spoon.delegates.SpoonStepsDelegate.editStep(SpoonStepsDelegate.java:127)
    at org.pentaho.di.ui.spoon.Spoon.editStep(Spoon.java:8789)
    at org.pentaho.di.ui.spoon.trans.TransGraph.editStep(TransGraph.java:3179)
    at org.pentaho.di.ui.spoon.trans.TransGraph.mouseDoubleClick(TransGraph.java:775)
    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.pentaho.di.ui.spoon.Spoon.readAndDispatch(Spoon.java:1359)
    at org.pentaho.di.ui.spoon.Spoon.waitForDispose(Spoon.java:7990)
    at org.pentaho.di.ui.spoon.Spoon.start(Spoon.java:9290)
    at org.pentaho.di.ui.spoon.Spoon.main(Spoon.java:685)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    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.api.client.http.javanet.NetHttpTransport.<init>(Lcom/google/api/client/http/javanet/ConnectionFactory;Ljavax/net/ssl/SSLSocketFactory;Ljavax/net/ssl/HostnameVerifierV


    at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:587)
    at org.pentaho.di.core.database.Database.normalConnect(Database.java:456)
    ... 55 more
    Caused by: java.lang.NoSuchMethodError: com.google.api.client.http.javanet.NetHttpTransport.<init>(Lcom/google/api/client/http/javanet/ConnectionFactory;Ljavax/net/ssl/SSLSocketFactory;Ljavax/net/ssl/HostnameVerifierV
    at com.google.api.client.http.javanet.NetHttpTransport$Builder.build(NetHttpTransport.java:317)
    at com.google.api.client.googleapis.javanet.GoogleNetHttpTransport.newTrustedTransport(GoogleNetHttpTransport.java:56)
    at com.simba.googlebigquery.client.BQClient.serviceAccountOAuth(Unknown Source)
    at com.simba.googlebigquery.core.BQConnection.connect(Unknown Source)
    at com.simba.jdbc.common.BaseConnectionFactory.doConnect(Unknown Source)
    at com.simba.jdbc.common.AbstractDriver.connect(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:567)
    ... 56 more


    Custom URL :jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId==[delete for security reasons];OAuthType=0;OAuthServiceAcctEmail=[delete for security reasons];OAuthPvtKeyPath=/c:/pentaho/credenciales_bigquery/key.p12;
    Custom Driver Class:com.simba.googlebigquery.jdbc42.Driver

  6. #6
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Sorry, I don't use those drivers, I don't even work with GBQ.
    I would set the driver loglevel to DEBUG in order to learn what's going on.
    So long, and thanks for all the fish.

  7. #7
    Join Date
    Oct 2006
    Posts
    9

    Default

    Quote Originally Posted by marabu View Post
    Sorry, I don't use those drivers, I don't even work with GBQ.
    I would set the driver loglevel to DEBUG in order to learn what's going on.
    Simba drivers works!

    I've downloaded from https://storage.googleapis.com/simba...1.1.2.1002.zip
    and copy the jars in pdi lib dir.

    then create database connection:
    use generic jdbc database connection
    custom connection url: jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=[project id];OAuthType=0;OAuthServiceAcctEmail=[name]@[project label].iam.gserviceaccount.com;OAuthPvtKeyPath=[path to p12 key file]
    custom driver class name: com.simba.googlebigquery.jdbc42.Driver

    So you need a gserviceaccount and p12 key for the bigquery project
    and you need the id and not the label of the project in the connection url

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.