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

[#1]

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?

[#2]

@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/

[#3]

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

do i need to have all these .jar files?

[#4]

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

[#5]

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

[#6]

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?

[#7]

yes. I did. 

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

[#8 · Accepted solution]

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.

[#9]

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