BIGQUERY - How to create a connection with Pentaho Data Integration (Spoon)?

Question

I'm trying to have access to BigQuery through Pentaho Data Integration, but I'm not succeeding.

• System: OSX El Capitan
• Google BigQuery Authentication Method: Service Account with .p12 key

I have followed this tutorial and I'm using OSX http://wiki.pentaho.com/display/EAI/Google+BigQuery

This is what I've done:

1. I downloaded and extracted "dependencies for kettle.zip" to PDI_FOLDER/libswt/osx64
2. I downloaded and copied "bqjdbc-1.4-standalone.jar" to PDI_FOLDER/lib
3. After that I tried to create a new connection in Data Integration, using New>Database Connection>Generic Database>Native (JDBC)

I configured the connection with this parameters, following this tutorial https://code.google.com/p/starschema-bigquery-jdbc/wiki/JDBCURL. So the parameters are:

• Custom Connection URL: jdbc:BQDriver:projectid(secretproject)?withServiceAccount=true
• Custom Driver Class Name: net.starschema.clouddb.jdbc.BQDrive
• Username: pentaho-data-integration@secretproject.iam.gserviceaccount.com
• Password: /Users/luisfsns/Dropbox/Lendico/etl/marketing/lendico-pentaho-data-integration-googlebigquery.p12

Things I don't know:

• Is my custom connection URL name correct? What should I supply as projectid argument? The name of the project or a URL to the path? Can someone give me an example?
• Should I use any other method of authentication (despite "Service Account) or any other type of private key like .json?
• Is my Custom Driver Class Name correct?

Can somebody help me?

This is the log of when i tried to test the connection created:

Error connecting to database [Teste] : org.pentaho.di.core.exception.KettleDatabaseException: Error occurred while trying to connect to the database

Driver class 'net.starschema.clouddb.jdbc.BQDrive' could not be found, make sure the 'Generic database' driver (jar file) is installed. net.starschema.clouddb.jdbc.BQDrive

org.pentaho.di.core.exception.KettleDatabaseException: Error occurred while trying to connect to the database

Driver class 'net.starschema.clouddb.jdbc.BQDrive' could not be found, make sure the 'Generic database' driver (jar file) is installed. net.starschema.clouddb.jdbc.BQDrive

at org.pentaho.di.core.database.Database.normalConnect(Database.java:428) at org.pentaho.di.core.database.Database.connect(Database.java:358) at org.pentaho.di.core.database.Database.connect(Database.java:311) at org.pentaho.di.core.database.Database.connect(Database.java:301) at org.pentaho.di.core.database.DatabaseFactory.getConnectionTestReport(DatabaseFactory.java:80) at org.pentaho.di.core.database.DatabaseMeta.testConnection(DatabaseMeta.java:2686) at org.pentaho.ui.database.event.DataHandler.testDatabaseConnection(DataHandler.java:546) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) 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:138) 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.Widget.sendEvent(Unknown Source) at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source) at org.eclipse.swt.widgets.Widget.notifyListeners(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.ui.xul.swt.tags.SwtDialog.show(SwtDialog.java:389) at org.pentaho.ui.xul.swt.tags.SwtDialog.show(SwtDialog.java:318) 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:59) at org.pentaho.di.ui.spoon.delegates.SpoonDBDelegate.newConnection(SpoonDBDelegate.java:464) at org.pentaho.di.ui.spoon.delegates.SpoonDBDelegate.newConnection(SpoonDBDelegate.java:451) at org.pentaho.di.ui.spoon.Spoon.newConnection(Spoon.java:8728) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) 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.Widget.sendEvent(Unknown Source) at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source) at org.eclipse.swt.widgets.Widget.notifyListeners(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:1319) at org.pentaho.di.ui.spoon.Spoon.waitForDispose(Spoon.java:7939) at org.pentaho.di.ui.spoon.Spoon.start(Spoon.java:9190) at org.pentaho.di.ui.spoon.Spoon.main(Spoon.java:654) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.pentaho.commons.launcher.Launcher.main(Launcher.java:92) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at apple.launcher.LaunchRunner.run(LaunchRunner.java:116) at apple.launcher.LaunchRunner.callMain(LaunchRunner.java:51) at apple.launcher.JavaApplicationLauncher.launch(JavaApplicationLauncher.java:52) Caused by: org.pentaho.di.core.exception.KettleDatabaseException: Driver class 'net.starschema.clouddb.jdbc.BQDrive' could not be found, make sure the 'Generic database' driver (jar file) is installed. net.starschema.clouddb.jdbc.BQDrive

at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:522) at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:4697) at org.pentaho.di.core.database.Database.normalConnect(Database.java:414) ... 70 more Caused by: java.lang.ClassNotFoundException: net.starschema.clouddb.jdbc.BQDrive at java.net.URLClassLoader$1.run(URLClassLoader.java:202) at java.security.AccessController.doPrivileged(Native Method) at java.net.URLClassLoader.findClass(URLClassLoader.java:190) at java.lang.ClassLoader.loadClass(ClassLoader.java:306) at java.lang.ClassLoader.loadClass(ClassLoader.java:247) at org.pentaho.di.core.database.Database.connectUsingClass(Database.java:497) ... 72 more

Custom URL :
jdbc:BQDriver:projectid(secretproject)?withServiceAccount=true Custom Driver Class:net.starschema.clouddb.jdbc.BQDrive

Answer 1

The answer to this may not please you, but here we go. It is possible to create this connection but the extraction is buggy and with a really slow flow of rows (Bigquery is fast to process anything, but this JDBC makes fetching the data really slow.

What I did here is a Python 2.7 script to extract the query to a table and extract the table to a csv file on Google Cloud Storage, then download the file.

It's really fast and you won't have lot's of bugs.

Here goes the python code that you can use. (you will need google storage utils installed to copy files from cloud to you machine easily)

The SH code: (use in a shell script entry on your JOB)

#!/bin/bash
export PATH=${PATH}

# BOTO is the login manager for GsUtil
export BOTO_DISPLAYENV="/home/mromano/.boto"
export BOTO_CONFIG="/home/mromano/.boto"

rm /tmp/bigquery_extraction_*

#Run Big Query extraction script on python
python "$caminho/google_bigquery_extract_foo_bar.py"

#Give it some seconds to sync data to Google Cloud Storage
sleep 10

#Copy from Google Cloud Storage to local file
/usr/local/bin/gsutil -q cp gs://pentaho_exports/google_bigquery_extract_foo_bar.csv.gz /tmp/google_bigquery_extract_foo_bar.csv.gz

The python script: (create a table with query results, export table to CSV and delete the table)

import httplib2
import logging
logging.basicConfig()

from apiclient.discovery import build
from oauth2client.client import SignedJwtAssertionCredentials
from bigquery import get_client

# BigQuery project id as listed in the Google Developers Console.
project_id = 'ce______?_____8'

# Service account email address as listed in the Google Developers Console.
service_account = '5399951_____?_______73k@developer.gserviceaccount.com'

f = file('../../../../keys/bigquery_key.p12', 'rb')
key = f.read()
f.close()

credentials = SignedJwtAssertionCredentials(
    service_account,
    key,
    scope='https://www.googleapis.com/auth/bigquery')

http = httplib2.Http()
http = credentials.authorize(http)


client = get_client(project_id, credentials=credentials, service_account=service_account)

# Write to table
job = client.write_to_table("""SELECT * FROM 001234.TEST""",
                    'pentaho_export',
                  table='table_foo_bar',
                create_disposition='CREATE_IF_NEEDED',
                write_disposition='WRITE_TRUNCATE')
try:
    job_resource = client.wait_for_job(job, timeout=6000)
    #print job_resource
except BigQueryTimeoutException:
    print "Timeout"

# Exporting
job_export = client.export_data_to_uris( ['gs://pentaho_exports/foo_bar.csv.gz'],
                                   'pentaho_export',
                                   'table_foo_bar',
                   compression='GZIP',
                   field_delimiter='    ')
try:
    job_resource = client.wait_for_job(job_export, timeout=6000)
    #print job_resource
except BigQueryTimeoutException:
    print "Timeout"

# Delete an existing table.
deleted = client.delete_table('pentaho_export', 'table_foo_bar')

I hope it helped. =)

Answer 2

Reassure that you have done the following.

• You stated,

  2) I downloaded and copied "bqjdbc-1.4-standalone.jar" to PDI_FOLDER/lib

  It should be copied to data-integration\libext\JDBC as well. If such a folder is not there you need to create it and copy the .jar file.

• Then Custom Connection URL should be like this:

  jdbc:BQDriver:themetic-scope-114043?withServiceAccount=true where themetic-scope-114043 is your project id. (use your correct id)

• Custom Driver Class Name is wrong (missed letter at at the end)

  net.starschema.clouddb.jdbc.BQDriver

• Password should correctly point to the .p12 file. In my windows machine I used C:\Digin-f537871c3b66.p12 as the password and the physical file should be there in the mentioned path.