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.
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.
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.
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.