I am a Knime beginner and work with version 4.7.2. I would like to establish a data connection between Knime and a database in Google BigQuery.
For the first steps I used the Google Authentication (API Key) and the Google BigQuery Connector. Up to this point there is no error message.
The problem then occurs with the DB Table Selector. After the Execute command, I get the following error message in the Knime Console:
“ERROR DB Table Selector 5:3 Execute failed: [Simba]BigQueryJDBCDriver Error executing query job. Message: Invalid project ID ‘nifty_stage_144415’. Project IDs must contain 6-63 lowercase letters, digits, or dashes. Some project IDs also include domain name separated by a colon. IDs must start with a letter and may not end with a dash.”
A vague assumption is that the database name (which contains dashes) may be a problem. In contrast, the above mentioned project ID in the error message contains underscores.
In the Google BigQuery Connector, I have entered nifty-stage-144415 in the Database name field under Connection Settings.
In the DB Table Selector (in the Settings), I also entered nifty-stage-144415 in the database field. I have also filled in the Schema field. In “Select a Table” I can also see the existing table names of the database, but when I want to select a table, I am told “Error fetching columns (for details see tooltip).”
Does anyone have any idea what the problem is and how I can fix it.
Could you please let us know which version of the DB nodes you are working with?
Can you access the table by other means? specifically by the DB SQL Executor node?
I am using the standard Knime node “DB Table Selector” (this is where the problem occured) of the Knime version 4.7.5. When I posted the problem some days ago, I used version 4.7.2.
Unfortunately, I can also not access the table via the mentioned “DB SQL Executor” node. The message there is still “Error fetching columns (for details see tooltip)”.
a) Authentification method
I use a P12 file and this works. I don’t think this can be a problem, I can even see the correct names of the tables in the database in the “DB Table Selector” node.
b) Underscores versus hyphens
You are right, in the log file the project ID is shown with underscores (“nifty_stage_144415”).
In the “Google BigQuery Connector” node, on the other hand, “nifty-stage-144415” is entered in the database name field (with hyphens). And in the “DB Table Selector” node, “nifty-stage-144415” is entered in the database field (also with hyphens). According to our IT, “nifty-stage-144415” is also the correct name.
Accordingly, underscores are only used in the Knime log (and in the error message). Is it possible that Knime not only displays the project ID incorrectly in the log file, but perhaps also converted it incorrectly during the processing itself?
Hi @bekop_veo ,
I asked about this issue internally and one of our developers said that there is a setting inside the Advanced setting tab in the big query connector node which might be the spaces with underscores which you need to disable.
Big query connector node ->Advanced->Dialect Syntax->replace non-word characters in identifiers
Here is the solution to the problem described above (for Knime version 4.7.5):
a) In the Google BigQuery Connector you have to go to the Advanced tab.
b) There, in the “Dialect syntax” section, you will find the point “Replace non-word characters in identifiers”, which has a tick in the default setting.
c) If you remove the tick from the above-mentioned point, then access to the tables is successful, at least in my case.
Sanket, many thanks to you and your developer colleague. It’s great that you took the time to solve the problem.
