Connection with Google Big query

KNIME Analytics Platform
1

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

2

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

3

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.

4

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.

5

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.

6

A bit late to the party, but SIMBA JDBC driver works with BigQuery, here is a nice post with the walkthrough: https://ericksondata.wordpress.com/2017/11/19/google-bigquery-connector-in-knime/

Note: I couldn’t get it to work with the KNIME Labs new DB nodes, but it works OK with the standard Database nodes.

1 Like
7

Topic to follow for BigQuery and new DB nodes issue: KNIME Labs DB nodes - BigQuery - DB & Query Readers don't work
Br,
Ivan

8

Hi All,

I used the instructions above and it worked like charm to read data from Big Query to Knime.

Using the generic database writer I was also able to send data from Knime to Big Query, however this process was terribly slow.

I tried altering the batch-size but that gave me an error “Execute failed: [Simba]BigQueryJDBCDriver Batched and streamed parameters not supported.”

Does anybody here know of an alternative to send data back from Knime to BQ?

Regards,

Maarten

9

Hi there Maarten,

Here is more on options to load data to BigQuery: https://cloud.google.com/bigquery/docs/loading-data

Not sure but this restriction is not on driver but rather from BigQuery. Check here: https://cloud.google.com/bigquery/streaming-data-into-bigquery

A possible workaround could be to use KNIME External Tool node either to transfer your data to Google Cloud Storage using gsutil cp command and from there create a job to transfer data to BigQuery or use bq command-line tool directly to load data. Disclaimer: Haven’t tried it out so feel free to share your experience with us :smiley:

Anyways, if not already, you will find out Google has quotas on everything :wink:

Br,
Ivan

10

Thanks for the link! It works like a charm. I use legacy connectors. It would be so cool if KNIME could introduce more straightforward (KNIME STYLE) nodes to connect. :smile: It also helps to save money doing stuff offline (instead of doing analysis on google).

1 Like
11

Hi,

I was using Credentials Input, Database Connector(legacy) and Database Reader(leagcy) following @Dimitri_B’s link in knime v4.2. It was working fine for past few weeks until today i try to connect the same database with different SQL, and it shows this error:

ERROR Database Connector (legacy) 0:316 Execute failed: Could not create connection to database: 400 Bad Request
{
“error” : “invalid_grant”,
“error_description” : “Malformed auth code.”
}

Anyone has a solution to it? Thanks in advanced!

12

Hello @gracelim,

welcome to KNIME Community!

For connection to Google BigQuery I suggest to use dedicated node - Google BigQuery Connector.

Also in DB Guide, Examples section, there are steps on how to connect to various databases and one example covers BigQuery so make sure to take a look: https://docs.knime.com/latest/db_extension_guide/index.html#connecting_examples

Hope this helps!

Br,
Ivan