Harnessing continuous queries for real-time BigQuery data streaming with Nexla

In today’s digital world, most organizations deal with large volumes of data from disparate systems, and yet have the need to make real time, informed decisions to stay competitive. For instance, an organization may need to consolidate data from multiple systems like Shopify, Marketo and Salesforce and process lead data real-time, generate scores, and distribute this enriched information. This can be consumed by various marketing tools for targeted personalized campaigns, or in Salesforce to prioritize leads for sales reps, based on their likelihood to convert or even in Gainsight so Account Managers can use the intelligence to plan their customer touches. This is where BigQuery’s continuous ingestion capabilities combined with Nexla’s powerful data integration tools, can streamline and enhance this process and help organizations make faster, more intelligent business decisions.

Introduction to BigQuery continuous queries

BigQuery continuous queries provide customers with the ability to run continuously processing SQL statements that can analyze, transform, and externally replicate data as new events arrive into BigQuery, real-time. This approach extends BigQuery’s real-time capabilities to handle continuous streams of input, analysis, and output; which can be leveraged to construct event-driven actionable insights, real-time machine learning inference, operational reverse ETL pipelines, and more. This essentially transforms BigQuery from the analytical data sink at the end of data’s architectural journey into an operational and event-driven data processing engine in the middle of an application’s decision logic, all using the user-friendly language of SQL.

How BigQuery continuous queries and Nexla work together

The illustration below shows how businesses can tap into the continuous flow of data through continuous queries from BigQuery using Nexla. Users can create and execute continuous queries either through Google Cloud Console, bq-command-line-interface, or the BigQuery API after choosing Quote mode as “Continuous Query”. This integration opens up a range of real-time use cases, ensuring that the latest information is always available for action. For this integration continuous queries will be using Pub/Sub Export as the option and events will be streamed in real time to a topic as it is added to BigQuery tables.

Once data is available in Pub/Sub Nexla consumes data from Pub/Sub via a source connector, processes data as needed, and pushes it to downstream applications such as Salesforce, Marketo, Salesforce, Gainsight, Zendesk, Google Analytics etc., which open up a lot of use cases and possibilities for our joint customers.

How organizations can benefit from using BigQuery continuous queries and Nexla

With BigQuery and Nexla integration in place, organizations can avail real-time messaging and data sharing between systems, ensuring seamless data communication. They can also benefit from monitoring data changes and automatic trigger updates whenever new data arrives, without needing manual intervention. With this powerful integration, organizations now can ensure that real-time data is readily available to other systems that require the data and when they require the data, powering critical business transactions or decisions.

Consider this example, customer transaction and profile information from Shopify and other ecommerce sources can easily be combined with lead scores and other campaign attributes from other systems and all of this sent to Marketo. Marketers can use this to help create campaigns that can use the data to execute real time personalization experiences that could increase customer engagement and customer conversion. Another example – lead scoring based on multiple touches from multiple activities, can get updated real-time and that data sent to Salesforce and/ or Gainsight. Sales reps can prioritize leads based on real-time activity and touches, the prospect engaged with or use that information to craft very personalized outreach campaigns or Account Managers can leverage the real time intelligence in Gainsight to craft their customer communication. In these examples real time information from multiple systems is combined, processed and automatically sent to sales and marketing teams to drive timely decisions and drive more demand and revenue.

This integration allows for the development of personalized marketing campaigns, boosts sales efforts, and enhances customer success management. Nexla’s Reverse ETL functionality enables organizations to seamlessly connect their data with essential operational tools, facilitating improved decision-making and more tailored interactions across different business areas.

Getting started with BigQuery continuous queries and Nexla

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.

It is easy to get started with this powerful integration by following the simple steps outlined below.

1)  Nexla Pub/Sub connector uses a service account 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 to configure the connection in Nexla in a 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//topics/continuous_query_topic’
) AS
(
);
———————————-
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 Nexla Pub/Sub connector.

5) Now let’s login to the Nexla and navigate to Integrate → Sources, and click Create New Flow on the top right corner.

6) Choose ‘FlexFlow’ and click ‘Create’ button, on the next page search for Pub/Sub and click the tile. Click “Add Credentials”.

7) Provide a Name, Description and select “System User Authentication” and upload the JSON file captured in the first step.

8) On the next page, select the Pub/Sub Topic where the BigQuery data will be streamed into, provide a name and Description. Then Select appropriate offset mode, and data format. You can also fetch sample data to verify.

9) Next click create source and Nexla will now automatically scan the source for data and organize that data into a Nexset, which is a logical data product that is immediately ready to be transformed or sent to any destination.

10) You can click the icon on a Nexset to open it in the Nexset Designer to create and apply Nexset Rules to make any necessary transformations to the data in the Nexset.

11) Click the icon on a Nexset to begin sending the data that it contains to a destination.This data can also be pushed to multiple destinations as well.

Summary

To stay ahead of the competition, organizations have an ever increasing need to be agile and leverage data real time to make real time decisions that will drive productivity, user experiences or other mission critical decisions. BigQuery’s continuous ingestion and query capabilities combined with Nexla’s powerful data integration tools, can streamline and enhance this process and enable teams with relevant data that they need, when they need and in the format they need, to help them make more intelligent business decisions and help organizations stay ahead of the competition. Getting started is easy and our experts can help you on your journey using continuous queries for real-time BigQuery data streaming with Nexla.

Learn more about BigQuery Continuous Query.
Learn more about Nexla data integration platform.