BigQuery and Spanner work better together by extending operational insights with external datasets.
Analyzing data from several databases has always been difficult for data analysts. They must employ ETL procedures to transfer data from transactional databases into analytical data storage due to data silos. If you have data in both Spanner and BigQuery, BigQuery has made the issue somewhat simpler to tackle.
You might use federated queries to wrap your Spanner query and integrate the results set with BigQuery using a TVF by using the EXTERNAL_QUERY table-valued function (TVF). Although effective, this method had drawbacks, including restricted query monitoring and query optimization insights, and added complexity by having the analyst to create intricate SQL when integrating data from two sources.
Google Cloud to provides today public preview of BigQuery external datasets for Spanner, which represents a significant advancement. Data analysts can browse, analyze, and query Spanner tables just as they would native BigQuery tables with to this productivity-boosting innovation that connects Spanner schema to BigQuery datasets. BigQuery and Spanner tables may be used with well-known GoogleSQL to create analytics pipelines and dashboards without the need for additional data migration or complicated ETL procedures.
Using Spanner external datasets to get operational insights
Gathering operational insights that were previously impossible without transferring data is made simple by spanner external databases.
Operational dashboards: A service provider uses BigQuery for historical analytics and Spanner for real-time transaction data. This enables them to develop thorough real-time dashboards that assist frontline employees in carrying out daily service duties while providing them with direct access to the vital business indicators that gauge the effectiveness of the company.
Customer 360: By combining extensive analytical insights on customer loyalty from purchase history in their data lake with in-store transaction data, a retail company gives contact center employees a comprehensive picture of its top consumers.
Threat intelligence: Information security businesses’ Security Operations (SecOps) personnel must use AI models based on long-term data stored in their analytical data store to assess real-time streaming data entering their operations data store. To compare incoming threats with pre-established threat patterns, SecOps staff must be able to query historical and real-time data using familiar SQL via a single interface.
Leading commerce data SaaS firm Attain was among the first to integrate BigQuery external datasets and claims that it has increased data analysts’ productivity.
Advantages of Spanner external datasets
The following advantages are offered by Spanner and BigQuery working together for data analysts seeking operational insights on their transactions and analytical data:
Simplified query writing: Eliminate the need for laborious federated queries by working directly with data in Spanner as if it were already in BigQuery.
Unified transaction analytics: Combine data from BigQuery and Spanner to create integrated dashboards and reports.
Real-time insights: BigQuery continuously asks Spanner for the most recent data, giving reliable, current insights without affecting production Spanner workloads or requiring intricate synchronization procedures.
Low-latency performance: BigQuery speeds up queries against Spanner by using parallelism and Spanner Data Boost features, which produces results more quickly.
How it operates
Suppose you want to include new e-commerce transactions from a Spanner database into your BigQuery searches.
All of your previous transactions are stored in BigQuery, and your analytical dashboards are constructed using this data. But sometimes, you may need to examine the combined view of recent and previous transactions. At that point, you may use BigQuery to generate an external datasets that replicates your Spanner database.
Assume that you have a project called “myproject” in Spanner, along with an instance called “myinstance” and a database called “ecommerce,” where you keep track of the transactions that are currently occurring on your e-commerce website. With the inclusion of the “Link to an external database” option, you may Create an external datasets in BigQuery exactly like any other dataset:
Browse a Spanner external dataset
A chosen Spanner database may also be seen as an external datasets via the Google Cloud console’s BigQuery Studio. You may see all of your Spanner tables by selecting this dataset and expanding it:
Sample queries
You can now run any query you choose on the tables in your external datasets actually, your Spanner database.
Let’s look at today’s transactions using customer segments that BigQuery calculates and stores, for instance:
SELECT o.id, o.customer_id, o.total_value, s.segment_name
FROM
current_transactions.ecommerce_order o
left join crm_dataset.customer_segments s on o.customer_id=s.customer_id
WHERE
o.order_date = ‘2024-09-01’
Observe that current_transactions is an external datasets that refers to a Spanner database, whereas crm_dataset is a standard BigQuery dataset.
An additional example would be a single view of every transaction a client has ever made, both past and present:
SELECT id, customer_id, total_value
FROM
current_transactions.ecommerce_order o
union transactions_history th
Once again, transactions_history is stored in BigQuery, but current_transactions is an external datasets.
Note that you don’t need to manually transfer the data using any ETL procedures since it is retrieved live from Spanner!
You may see the query plan when the query is finished. You can see how the ecommerce_order table was utilized in a query and how many entries were read from a particular database by selecting the EXECUTION GRAPH tab.
