There is a new KNIME forum. You can still browse and read content from our old forum but if you want to create new posts or join ongoing discussions, please visit our new KNIME forum: https://forum.knime.com

Connection with Google Big query

Member for

2 years 6 months rapozzatir

Hello everybody,

I can not access to Google Big Query database.

Which database connector do I have to use with version 3.2.2? Which driver?

I have installed the Knime versions 2.12.2 and 3.2.2. The first one allow me to use database connector and choose the driver sun.jdbc.odbc that is not available anymore in the second one.

Unfortunately the first one gives me the following error: 

ERROR Database Reader      0:357      SQL Exception reading Object of type "12": [Microsoft][ODBC Driver Manager] Invalid string or buffer length - all further errors are suppressed and reported on debug level only
ERROR Database Reader      0:357      Execute failed: Cell at index 0 is null! 

The test of ODBC in successful and the result of the query on Google platform is OK.

Thank you in advance.

Best regards.

Roberto

Comments
Mon, 03/27/2017 - 09:26

Member for

10 years 11 months

tobias.koetter

Hello Roberto,

Oracle remoced the JDBC-ODBC driver in Java 8. For details see this post.

To connect to Google Bigquery from KNIME 3.x you can use the JDBC driver provided by Google which you can download here.

Bye

Tobias

Tue, 04/11/2017 - 03:35

Member for

2 years 6 months

hectoribalop

Hi guys,

 

I am trying to connect using the simba driver, but I have different doubts.

What should I include in the Database URL, username and password?

I have linked BigQuery to Google Analytics and I have been able to query through the BigQuery console, but at the time of wanting to do it from BigQuery, I can not connect.

 

Thanks.

Wed, 05/24/2017 - 10:54

Member for

3 years 8 months

jnewcombe

Hi,

I managed to get BigQuery and Knime working together for a project last year.

I was never able to get the Simba JDBC driver to work correctly, the StarSchema one sort of worked, but the CData one worked perfectly. Sadly it's not free, but you can get a 30-day trial to prove it works before you buy. The trick was in the method of access, and it may work for the newest Simba driver.

I had to use a Google Cloud Platform Service Account to connect to BigQuery, and I had to create a Client ID connected to the Service Account in order to get the JDBC connector to work.

Once I imported the CData driver into Knime, the settings in the database connector were as follows:

Database driver: cdata.jdbc.googlebigquery.GoogleBigQueryDriver

Database URL: jdbc:googlebigquery:InitiateOAuth=GETANDREFRESH;OAuthClientId=<client ID>;OAuthClientSecret=<OAuth key generated by Google for the client ID>;ProjectId=<your BigQuery project ID>;DatasetId=<BigQuery dataset name>

Tick "Use username and password"

Username : <BigQuery Service Account ID>

Password: <Service Account password>

Note that the URL structure above only works for the CData JDBC connector. If you use the StarSchema one, the URL is structured differently, but I can't find my notes on that.

I only used BigQuery as a data source. I think I did manage to get Knime to write new tables, but remember that an RDBMS-style UPDATE has no meaning in a BigQuery context. You can't update a row, you can only read it and write a new table with the altered data. I recall that BigQuery worked well and quickly as a data source, but trying to write back to it was painful.

I'd expect that the drivers have improved in the last 12 months, and Knime has had a number of updates, so it may be easier now, but I'm now using Azure rather than Google, and so far it's a much happier experience. BigQuery isn't really suited to the usage scenario I'm working with.

Good luck.

Tue, 05/30/2017 - 04:33

Member for

2 years 6 months

hectoribalop

Hi @jnewcombe,

Thanks for the help, i download the trial version from the driver (CData) but I've some problems...

When I put the credentials at the "Database Reader" node this don't run and appears this error: 

ERROR Database Reader      2:941      Execute failed: 401:Invalid Credentials

 

What credentials I need to put? Can you help me indicating the origin from the necessary information at the URL and credentials?.

 

Thank you so much.