Export data from qlik to BigQuery

[ChiranjeeviVeesam]

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.

[kpgleeso]

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.

[kpgleeso]

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]

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.

[kpgleeso · Accepted solution]

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]

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

[kpgleeso]

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]

sure and thanks again for the help.