Error fetching columns (in DB Table Selector)

Hello,

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.
workflow with problem in DB Table Selector

Hi @bekop_veo ,
Welcome to the KNIME Community!

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?

Thanks,
Sanket

Dear Sanket,

Thanks for your reply.

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

Hello @bekop_veo ,

Would it be possible for you to provide the log file?

Thanks,
Sanket

Dear Sanket,

Please find attached the relevant logfile.
knime_DB_problem.log (24.3 KB)

@bekop_veo from the log it seems that your Google project ID does contain underscores while only letters, digits and hyphens are allowed.

@sanket_2012 can there be a problem with the authentication method?

Also from my recollection I think knime does not currently support JSON based Google connections only P12. Has anything changed in that regard.

com.google.api.client.googleapis.json.GoogleJsonResponseException: 400 Bad Request
POST

@bekop_veo maybe you can try and set up the connection again from the documentation to see if this could be the problem.

https://docs.knime.com/2022-12/google_cloud_integration_user_guide/index.html#google_authentication_api

Dear mlauber71,

Thank you very much for helping as well.

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

Could you please try this and let us know?

Thanks,
Sanket

1 Like

Dear Sanket,

that was indeed the solution!!! Now it works.

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.

2 Likes

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.