extract table schema with jdbc driver

[Anonymous]

Hi all.

I am looking for help on using 'jdbc connection' to bigquery, the purpose is, I would like to browse through the schema of bigquery in Talend Open Studio for Big Data 7x. anyhelp is appreciated.

I get following error :

Starting job BigQueryWIthoutSchemaTest1 at 17:57 26/03/2019.

[statistics] connecting to socket on port XXXXXX
[statistics] connected
Exception in component tDBConnection_1 (BigQueryWIthoutSchemaTest1)
java.lang.RuntimeException: No suitable driver found for jdbc:bigquery:........................
[statistics] disconnected

Using the component : tDBConnection(JDBC) and Drivers

[Anonymous]

Hello,

This  above error due to the Simba JDBC driver (GoogleBigQueryJDBC42.jar) for the BigQuery does not support the auto commit.

Please try to use tdbconnection and tdbinput component with "auto commit" option deactivated .

Let us know if it is OK with you.

Best regards

Sabrina

[Anonymous · Accepted solution]

Hello @ssomanchi 

In the Advanced settings of tDBConnection component and then uncheck the "Use Auto-Commit" option.

Best regards

Sabrina

[Anonymous]

thank you Sabrina.

It was switched off already. 

I anyways managed to get the connection working using Simba JDBC connection and extract the table schema.

so basiclally here are full steps if some one gets into my situation:

- use component tDBConnection.

download simba drivers and extract it in local folder.

you must then add the JARS into Talend. as following

see below screenshot:

after you wrote the sql: select * from dataset.tablename; then click on "Guess schema". Then you will be able to see your schema when you click "Edit schema".

You will see column names, data types, scale, precission.

I dont know if this is the optimal way, but I got this way working.

JDBC URL for Simba driver is : 

JDBC URL : "jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=<project-Name>;OAuthType=0;OAuthServiceAcctEmai... to connect to gcp>;OAuthPvtKeyPath=<path to json file \\ to escpae>.json;"

good luck!!!

[Anonymous]

Hello @ssomanchi ,

Thanks for your feedback and sharing your solution with us kindly.

We have one concern that there is no connector OnComponentOK or OnsubjobOK between your t<DB>Connection and t<DB>Input. Which will cause "java.lang.NullPointerException" when you create a DB connection and extract data from it.

Best regards

Sabrina