question Re: How to load data from Google Big query table using Apache Nifi? in Support Questions

How to load data from Google Big query table using Apache Nifi?

Sivaluxan — Tue, 11 Jul 2023 06:40:46 GMT

I need to load the data from big query table using nifi.

I used executesql nifi processor to load data from big query table.

when i was executing processor, i got following error.

java.lang.NoSuchMethodError: 'void com.google.api.gax.rpc.RequestUrlParamsEncoder.(com.google.api.gax.rpc.RequestParamsExtractor)'

configure DBCPConnectionPooling Service in following class name - com.simba.googlebigquery.jdbc42.Driver

I am using nifi1.21.0 version. download latest version of big query jdbc driver(version 4.2) and other all dependencies and put into nifi/lib folder

I also used correct connection url when configuring pooling service.

How to solve this error?

Re: How to load data from Google Big query table using Apache Nifi?

cotopaul — Tue, 11 Jul 2023 06:59:59 GMT

@Sivaluxan,

I am not quite sure you have the correct Database Driver Class Name. I am extracting data our of BigQuery using the combination of GenerateTableFetch and ExecuteSQLRecord and I receive no error message at all.

In terms of configurations I have the following:
Database Connection URL: jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=your-project-if-here;OAuthType=0;OAuthServiceAcctEmail=your-email-for-service-account-here;OAuthPvtKeyPath=path_on_nifi_server_where_the_service_account_json_is_located;

Database Driver Class Name: com.simba.googlebigquery.jdbc.Driver
Database Driver Location: full_path_to_jars_location/

Re: How to load data from Google Big query table using Apache Nifi?

Sivaluxan — Tue, 11 Jul 2023 10:22:52 GMT

@cotopaul what is the version of your big query JDBC driver?

do i need to have all these .jar files?

Re: How to load data from Google Big query table using Apache Nifi?

Sivaluxan — Tue, 11 Jul 2023 10:49:00 GMT

@cotopaul can you share me the big query jdbc .jar file?

Re: How to load data from Google Big query table using Apache Nifi?

cotopaul — Tue, 11 Jul 2023 11:13:04 GMT

yes you do 🙂 and you will point the JAR location to that entire folder and let NiFi do it's job.
The JAR File (all of them) can be download directly from google: https://cloud.google.com/bigquery/docs/reference/odbc-jdbc-drivers

There is only a single version for JDBC: SimbaJDBCDriverforGoogleBigQuery42_1.3.3.1004

Re: How to load data from Google Big query table using Apache Nifi?

Sivaluxan — Wed, 12 Jul 2023 02:51:25 GMT

@cotopaul ERROR [Timer-Driven Process Thread-1] o.a.n.p.standard.ExecuteSQLRecord ExecuteSQLRecord[id=47eb2248-0189-1000-5848-add6083f3a2f] Processing halted: yielding [1 sec] java.lang.NoClassDefFoundError: com/google/api/core/NanoClock

i am getting this error now.

Re: How to load data from Google Big query table using Apache Nifi?

cotopaul — Wed, 12 Jul 2023 06:58:04 GMT

Have you extracted the JDBC zip (not the ODBC) correctly? Have you pointed NiFi to the full path of the folder? Have you defined the Database Connection URL, Database Driver Class Name, Database Driver Location correctly?

Re: How to load data from Google Big query table using Apache Nifi?

Sivaluxan — Wed, 12 Jul 2023 10:05:39 GMT

yes. I did.

I configured correct Database Connection URL, Database Driver Class Name, Database Driver Location.

Re: How to load data from Google Big query table using Apache Nifi?

cotopaul — Wed, 12 Jul 2023 10:46:53 GMT

the question is how? 😀

I just tested again and I was able to extract data from (and into) BigQuery without any errors. It seems that something you have defined is not 100% correct.

Circling back to my initial post, you would need those defined as:

Database Connection URL: jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=your-project-if-here;OAuthType=0;OAuthServiceAcctEmail=your-email-for-service-account-here;OAuthPvtKeyPath=path_on_nifi_server_where_the_service_account_json_is_located;

Database Driver Class Name: com.simba.googlebigquery.jdbc.Driver
Database Driver Location: full_path_to_jars_location/

Where the Database driver location has to be the full path to the folder where you have extracted the JDBC (not ODBC) ZIP File... and you only have to point to the folder, not to a specific JAR file.

In addition, pay attention to how the connection URL is configured and make sure that you follow the same structure. Plus, make sure that your SA has all the necessary rights for your actions.