10 Easy Steps to Integrate Real-time SAP Data with BigQuery Using Qlik Talend Cloud

Qlik offers a comprehensive suite of solutions designed to enhance data integration, analytics, and data quality, seamlessly integrating with Google BigQuery. Qlik's data integration tools enable efficient and secure data ingestion from various sources into BigQuery, ensuring real-time data availability for analytics. With Qlik's advanced analytics capabilities, users can perform sophisticated data analysis and gain actionable insights from their BigQuery data. Together, Qlik and Google BigQuery provide a powerful platform for unlocking the full potential of your data.

Introducing BigQuery continuous queries

BigQuery continuous queries allows users to execute continuously processing SQL statements that can process, analyze, and transform data as new events arrive in BigQuery, ensuring your insights are always up to date. The feature's native integration with the Google Cloud ecosystem unlocks even more potential. You can harness the power of Vertex AI and Gemini to perform machine learning inference on incoming data in real time. As well as seamlessly replicate the results of a continuous query to Pub/Sub topics, Bigtable instances, or even other BigQuery tables for further processing or analysis.

With BigQuery continuous queries, Google is expanding BigQuery's abilities, empowering users with new dynamic and event-driven data processing capabilities, alongside its existing unified data platform strengths. This feature allows you to build applications that respond instantly to changes in your data, opening doors to a new realm of possibilities. Craft personalized customer experiences on the fly, detect anomalies before they escalate, and automate decision-making processes with unprecedented agility.

How BigQuery continuous queries works with Qlik Talend Cloud

Below is a very popular SAP use case where Talend Cloud helps ingest data into BigQuery for analytics purposes, where the data is enriched, processed and transformed. Based on the transformations data would be added to tables which are continuously being queried using BigQuery continuous queries and streamed back into Qlik Talend Cloud via Pub/Sub to further processed and ingested downstream (Reverse ETL’d) into applications like Salesforce, Hubspot, Marketo, ServiceNow etc. This new integration opens up a lot of possibilities and use cases for our joint customers.

Qlik Talend Cloud covers all the steps and solutions needed by organizations to modernize their SAP data using Google BigQuery, a single platform provides the CDC Data ingestion, Data Transformation and Quality, Analytics and Application integration to maximize the usage of SAP data.

Qlik offers SAP Accelerators—pre-built templates designed to streamline the extraction, transformation, and analysis of SAP data. These accelerators target specific business areas such as Order-to-Cash, Financial Analytics, and Inventory Management. Qlik's model-driven approach facilitates the rapid addition of new data warehouses and the creation of purpose-built data sets to address evolving business needs. Utilizing change data capture (CDC), Qlik efficiently identifies and transfers only data changes and metadata, minimizing impact on SAP environments.

Once the data is loaded into Google BigQuery, automations can capture messages from continuous queries and integrate with platforms like Salesforce, ServiceNow, Marketo and Hubspot.

10 Steps to Get Started with BigQuery Continuous Queries and Qlik Talend Cloud

Setting up and running BigQuery continuous queries

At the time this blog was written, the Continuous queries feature is in preview and subjected to the "Pre-GA Offerings Terms". To enroll in the continuous queries preview, fill out the request form.

1. Talend Cloud pub/sub connector would be using a service account’s short lived bearer token to connect and consume data from pub/sub, you can configure a single service account for running continuous queries and consume from Pub/Sub by assigning relevant permissions to the user. You can configure the service account with permissions listed here. Make sure you create a JSON key and have a bearer token handy to configure the connection in Talend Cloud in the later step.

2. To run continuous queries, BigQuery requires a slot reservation with a “CONTINUOUS” assignment type. Follow steps here if you are not sure how to create a reservation.

3. Navigate to the Pub/Sub topic page and click on “Create Topic” button on the top center of the page, and provide a name (say ‘continuous_query_topic’, also create a default subscription if needed).

4. Navigate to BigQuery service page and design the query as an export to pub/sub.
   
   EXPORT DATA
   OPTIONS (
   format = 'CLOUD_PUBSUB',
   uri = 'https://pubsub.googleapis.com/projects/<your project_id>/topics/continuous_query_topic'
   ) AS
   (
   <Your Query>
   );
   
   
   In the More Settings as shown below, select the query mode as continuous query and in the query settings select the service account created above to run the query. You can also choose the timeout required if any.
   
   
   
   
   
   Before we execute the query, make sure the below steps are done to ensure data continuously generated can be captured by the Talend Cloud Pub/Sub connector.

5. Now let’s login into your Qlik Talend Cloud and select Data Integration from the top left menu.
   
   
   
   

6. Click on the Create Automation button using a blank template.

7. Click on the Start block and configure how often this automation will check for new messages on Pub/Sub.
   
   
   
   

8. From the left blocks menu, drag and drop the “Call URL” block to the canvas and configure the block using
   
   - URL : copy the subscription URL from the step 3
   - Method : POST
   - Params: add one param named maxMessages and set a proper value according to the expected throughput, you can start with 50 and adjust later
   - Add three Headers:
   1. Authorization: Bearer <token generated at step 1>
   2. x-goog-user-project: your projectID
   3. Content-Type: application/json
   
   
   
   

9. The Call URL block will return a list of messages, so the best way to go through all them is to add a Loop block dragging it from the left menu and configuring to process the Body / Received Messages list.
   
   
   
   

10. Message data is JSON string base64 encoded, to decode it, let’s use a variable with the expression below to convert the data into a string. You can use the raw input mode to copy and paste the expression {object: '{ base64decode: {$.loop.item.message.data} }'}
    
    
    
    

11. Now you can use the Qlik Talend Cloud application automation connectors to create the workflow based on the messages coming from your BigQuery through Pub/Sub. Just an example, the workflow below will check if a contact exists on Salesforce searching for the information coming from the flow and create/update the contact accordingly.
    
    
    
    
    
    If you want to see more and learn about Application Automation, you can start from this Youtube Playlist.
    
    The Qlik SAP Accelerators deployment cannot be covered in this blog post, requiring a more deep understanding of the security and data requirements from your organization. You can refer to this page to get more information or be contacted by a Qlik professional.

Conclusion

Now you can leverage BigQuery continuous queries to stream real time data directly into Qlik Talend Cloud and enable you to create solutions which were not possible before. This opens up a lot of opportunities for our mutual customers. By harnessing the power of Qlik and BigQuery continuous queries, organizations can unlock the full potential of their data and gain a competitive edge. This powerful combination enables real-time data-driven decision-making, streamlined data pipelines, and improved application performance.

To learn more about BigQuery continuous query click here. Begin experimenting with Qlik on Google Cloud Marketplace today!