Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ChiranjeeviVeesam
Contributor II
Contributor II

Export data from qlik to BigQuery

I successfully loaded Qlik data to Google Cloud Storage using store command. Now, I'm trying to load data from Qlik Sense to Google BigQuery. I'm able to load data from BigQuery to Qlik Sense but can't able to load data from Qlik to Google BigQuery. Can you please let me know how to do it because store command is not working here. Thanks in advance.

1 Solution

Accepted Solutions
KeaneGleeson
Partner - Contributor III
Partner - Contributor III

Did you have SQL typed in a line above calling that stored procedure? I had to use that to get my script to work. I noticed when removing that the '!EXECUTE_NON_SELECT_QUERY' part is unrecognized.

 

 

View solution in original post

7 Replies
KeaneGleeson
Partner - Contributor III
Partner - Contributor III

I'm trying to do the same thing. I wonder is there a REST API for BigQuery where you could use the POST method to get the data in there.

KeaneGleeson
Partner - Contributor III
Partner - Contributor III

I've been able to successfully do this with the help of this forum thread.  

 
There was one specific tweak I had to make for calling the stored procedure because it seemed like BigQuery does not use the same syntax as Postgres or whatever they used in that example:
SQL
CALL bqtest.writeback($(vYear)$(vPercent_bottom)$(v_Percent_top)) !EXECUTE_NON_SELECT_QUERY;

 

ChiranjeeviVeesam
Contributor II
Contributor II
Author

Hi, 

I tried but it's not working. Please let me know where I am wrong.

BIGQUERY:

a) created dataset "qlik"

b) created table "alerts"

CREATE OR REPLACE TABLE qlik.alerts

(

alertname STRING,

desk STRING)

c) Created a procedure "qlik.writeback"

CREATE OR REPLACE PROCEDURE qlik.writeback( alertname STRING, desk STRING) BEGIN INSERT INTO qlik.alerts VALUES (alertname,desk); END;

QLIK Sense:

a) Successfully tested the connection using service account credentials with project editor access

b) Did the same as specified in thisforum

except calling where I specified as call qlik.writeback('$(vAlert)','$(vDesk)');
!EXECUTE_NON_SELECT_QUERY;

In editor, everything is good but !EXECUTE_NON_SELECT_QUERY; is in red color(error).

While executing, I'm getting error at call qlik.writeback('alert1','desk1') 

NOTE:

a) Function in BigQuery is not supporting "begin and end" so I used procedure over function

b) I read that this QvOdbcConnectorPackage.exe.config file should be configured from false to true to allow non_select_query but I can't able to as I don't have installed the application rather accessing QlikSense Cloud directly in the browser.

KeaneGleeson
Partner - Contributor III
Partner - Contributor III

Did you have SQL typed in a line above calling that stored procedure? I had to use that to get my script to work. I noticed when removing that the '!EXECUTE_NON_SELECT_QUERY' part is unrecognized.

 

 

ChiranjeeviVeesam
Contributor II
Contributor II
Author

Hey, It's working! I put SQL before the calling and it worked with no errors. Thank you so much.

I have a query on this. My goal is to move data from QlikSense to BigQuery and I'm left with two options either direct migration from QlikSense to BigQuery or take help of Google Cloud Storage(QlikSense -> Google Cloud Storage -> BigQuery). 

Which option is more preferable?

My understanding is to take help of Google Cloud Storage because 'inserts' (insert data from qlikSense to BigQuery one by one) degrade the performance of BigQuery as BigQuery is Column oriented(analytics) rather row oriented database(transactions/operations). 

 

KeaneGleeson
Partner - Contributor III
Partner - Contributor III

Glad it works for you too!

 

Hmm, that's a good question. I don't know if I can speak to that with much experience with it. Our org. doesn't have the Google Cloud Connector set up. I'm not sure if you would this method above to send the data through the Cloud connector. Might be worth starting another thread weighing the two options, or maybe inquiring on the Qlik Branch Slack channel.

ChiranjeeviVeesam
Contributor II
Contributor II
Author

sure and thanks again for the help.