As software-defined vehicles continue to advance and the quantity of digital services grows to meet consumer demand, the data required to provide these services continue to grow as well. This makes automotive manufacturers and suppliers look for capabilities to log and analyze data, update applications, and extend commands to in-vehicle software.

The challenges the automotive sector faces can be quantified. A modern vehicle contains upwards of 70 electronic control units (ECUs), most of which are connected to one or more sensors. Not only is it now possible to exactly measure many aspects of vehicle performance, but new options become available. Using LIDAR (light detection and ranging), for example, vehicles are achieving higher levels of autonomy; this leads to a data stream from such demanding applications that may reach 25 GB per hour. For the in-vehicle processing of data, 100 million lines of software code may be present — more than a fighter jet. This in-vehicle code will have to be maintained with updates and new functionalities.

Access to the data will allow manufacturers to gain valuable insights into operational details of their vehicles. The use of this data can help to reduce costs and risks, increase ROI, support ESG initiatives, and provide valuable insights to develop innovative solutions and shorten the time to value for Electric Vehicle innovations.

Sibros’ Deep Connected Platform (DCP) makes it possible for these manufacturers to build and launch new connected vehicle use cases from production to post-sale at scale by connecting and managing all software and data throughout every life cycle stage. A key component of this platform is the Sibros Deep Logger that provides capabilities like the following:

• Full configurability of what to record, when to record it, and how fast to record it.
• High resolution timestamps of all Controller Area Network (CAN) messages.
• Dynamic application of live log configurations to receive new data points without deploying new software.

For example, properly analyzed engine data enables true predictive maintenance for the first time, which creates the option to repair or replace components before failure happens. Another example would be the evaluation of data regarding the use of certain in-car features with the goal to redesign its interior.

Two other components of the DCP are software updates and remote commands to ECUs. The DCP on Google Cloud enables seamless integration with any vehicle architecture and provides OEMs and suppliers with the platform to manage connected vehicle data at rest and in transit using a proven and secure way on a global scale.

OEMs can pull data through APIs provided by Sibros into Google Data Cloud (including BigQuery) to gain access to the rich information data sets provided by the DCP within their environment and blend this data with their first party data sets to provide value insights for their business. Some of the Connected Vehicle insights that DCP information enables are:

• Damage prevention, improved operation, or development of the next generation of engines with insights from complex analyses that could consider parameters like model, engine type, mileage, overall speed, temperature, air pressure, load, services, and more.
• The combination of electric vehicle battery usage data like charging cycles, engine performance, and battery age with contributing factors as the use of the air conditioning to determine if such factors contribute to hazardous battery conditions and for improved battery development.
• Cross-organization collaboration in R&D by the provision of information on all these metrics and more from real-world driving, like engine knock data and even tire pressure.
• Google Cloud’s unified data cloud offering provides a complete platform for building data-driven applications like those from Sibros — from simplified data ingestion, processing, and storage to powerful analytics, AI, ML, and data sharing capabilities — integrated with Google Cloud. With a diverse partner ecosystem and support for multi-cloud, open-source tools and APIs, Google Cloud provides Sibros the portability and the extensibility they need to avoid data lock-in.

“Software has an ever increasing importance in the automotive world, even more so with electric vehicles and new mobility services. Google Cloud is partnering with Sibros to bring their award winning Deep Connected Platform to deliver high frequency, low latency over-the-air software updates, data logging & diagnostics capabilities to our automotive customers, leveraging the security and scale of Google Cloud. This is revolutionizing everything from development cycles to business models and customer relationships.” — Matthias Breunig, Director, Global Automotive Solutions, Google Cloud

Through Built with BigQuery, Google Cloud is helping tech companies like Sibros build innovative applications on Google’s Data Cloud with simplified access to technology, helpful and dedicated engineering support, and joint go-to-market programs.

“Sibros is looking forward to partnering with Google Cloud, which will enable vehicle manufacturers and suppliers to reach the next level in their use of data. Sibros solutions for Deep Data Logging and Updating on the Google Data Cloud, combined with Google BigQuery, will help them to mitigate risks, reduce costs, add innovative products, and introduce value-added use cases.” — Xiaojian Huang, Chief Digital Officer, Software, Sibros

Sibros and Google Cloud are driving Connected Mobility transformation to help our customers accelerate R&D innovation, power efficient operations, and unlock software-defined vehicle use cases with a full stack connected vehicle platform. Click here to learn more about Sibros on Google Cloud.

Like virtually every business across the globe, The New York Times had to quickly adapt to the challenges of the coronavirus pandemic last year. Fortunately, our data system with Google Cloud positioned us to perform quickly and efficiently in the new normal. 

How we use data

We have an end-to-end type of data platform; on one side we work very closely with our product teams to collect the right level of data that they’re interested in, such as which articles people are reading, and how long they’re staying onsite. We frequently measure our audience to understand our user segments, and how they come onsite or use our apps. We then provide that data to analysts for end-to-end analytics. 

On the other side, the newsroom is also focused on audience, and we build tools to help them understand how Google Search or different social promotions play a role in a person’s decision to read The New York Times, and also to get a better sense of their behavior on our pages. With this data, the newsroom can make decisions about information that should be displayed on our homepage or in push notifications. 

Ultimately, we’re interested in behavioral analytics—how people engage with our site and our apps. We want to understand different behavioral patterns, and which factors or features will encourage users to register and subscribe with us. 

We also use data to create or curate preferences around personalization, to ensure we’re delivering to our users fresh content, or content that they may not have normally read. Likewise, our data also gets used in our targeting system, so that we can send out the right messaging about our various subscription packages to the right users.

Choosing to migrate to Google Cloud

When I came to The New York Times over five years ago, our data architecture was not working for us. Our infrastructure was gathering data that proved harder for analysts to crunch on a daily basis. We were also hitting hang ups with how that data was streaming into our system and environment. Back then we’d run a query and then go grab some coffee, hoping that the query would finish or give us the right data by the time we came back to our desks. Sometimes it would, sometimes it wouldn’t.

We realized that Hadoop was definitely not going to be the on-premises solution for us, and that’s when we started talking with the Google Cloud team. We began our digital transformation with a migration to BigQuery, their fully managed, serverless database warehouse. We were under a pretty aggressive migration timeline, focusing first on moving over analytics. We made sure our analysts got a top-of-the-line system that treated them the way that they themselves would want to treat the data. 

One significant prominent requirement in our data architecture choice was to enable analysts to be able to work as quickly as they needed to provide high-quality deliverables for their business partners. For our analysts, the transition to BigQuery was night and day. I still remember when my manager ran his very first query on BigQuery and was ready to go grab his coffee, but the query finished by the time he got up from his chair. Our analysts talk about that to this day.

While we were doing the BigQuery transition, we did have concerns about our other systems not scaling correctly. Two years ago, we weren’t sure we’d be able to scale up to the audience we expected on that election day. We were able to band-aid a solution back then, but we knew we only had two more years to figure out a real, dependable solution. 

During that time, we moved our streaming pipeline over to Google Cloud, primarily using App Engine, which has been a flexible environment that enabled quick scaling changes and requirements as needed. Dataflow and Pub/Sub also played significant roles in managing the data. In Q4 of 2020 we had our most significant traffic ever recorded, at 273 million global readers, and four straight days of the highest traffic we’ve had compared to other election weeks. We were proud to see that there was no data loss.

A couple of years ago, on our legacy system, I was up until three in the morning one night trying to keep data running for their needs. This year, for election night, I relaxed and ate a pint of ice cream because I was able to more easily manage our data environment, allowing us to set and meet higher expectations for data ingestion, analysis and insight among our partners in the newsroom.

How COVID-19 changed our 2020 roadmap 

The coronavirus pandemic definitely wasn’t on my team’s roadmap for 2020, and it’s important to mention here that The New York Times is not fundamentally a data company. Our job is to get the news out to our users every single day in paper, on apps, and onsite. Our newsroom didn’t expect the need to build out a giant coronavirus database that would enrich the news they share every day. 

Our newsroom moves quickly, and our engineers have built one of the most comprehensive datasets on COVID-19 in the U.S. With Google, The New York Times decided to make our data publicly available on BigQuery Google’s COVID-19 public dataset. Check out this webinar for more details on our evolution architecture:https://www.youtube.com/embed/mtNlrFpschU?enablejsapi=1&

Flexible approach

We have many different teams that work within Google Cloud, and they’ve been able to pick from the range of available services and tailor project requirements keeping those tools available in mind.

One challenge we think about with the data platform at The New York Times is determining the priorities of what we build. Our ability to engage with product teams at Google though the Data Analytics Customer Council allows us to see into the BigQuery roadmap, or the data analytics roadmap, and plays a significant role in determining where we focus our own development. For example, we’ve built tools like our Data Reporting API, which reads data directly from BigQuery, in order to take advantage of tools like BigQuery BI Engine. This approach encourages our analysts to be better managers of their domains around dimensions and metrics, but not have to focus on building caching mechanisms of their data. Getting that kind of clarity helps us plan how to build The New York Times in the new normal and beyond.

If you are interested to learn more about the data teams at the New York Times, take a look at our open tech roles here and you’ll find many interesting articles at NYT data blog.

The popularity of meal kit delivery services has surged in recent years as consumer attitudes toward home cooking and grocery shopping have shifted. As a pioneer in the category, Blue Apron helps its customers create incredible home cooking experiences by sending culinary-driven recipes with high-quality ingredients and step-by-step instructions straight to customers’ doors. Blue Apron also offers a monthly wine subscription service and a la carte culinary tools and products through its marketplace.

If that sounds simple, it isn’t.

Ingredients for the meal kits must be sourced at the right time, quality, and price. Orders must be packed efficiently and in exactly the right proportions. Most importantly, meal kits must be delivered to the customer fresh and on time.

To meet these criteria and make data meaningful and intuitive to its managers, one of the tools Blue Apron relies on is Looker, an analytics platform that lets business users explore data and ask sophisticated questions using familiar terms. Looker integrates its solution with Google Cloud Platform to help customers modernize their analytics.

“The combination of Looker and Google BigQuery is powerful, allowing us to get data-hungry analysts essential information much faster. Because we choose to pay by the query, it’s also flexible and cost effective—plus storage is cheap, so we can just put data in and query what we need.”

—Sam Chase, Tech Lead, Data Operations, Blue Apron

Blue Apron previously used Looker with a single database instance hosted on another cloud provider. As data volumes grew and queries became more complex, it became difficult to scale. Blue Apron’s only options were choosing ever-larger server classes and increasing storage throughput by purchasing a higher number of provisioned IOPS. To improve speed, scalability, and cost efficiency, Blue Apron moved its data warehouse to Google BigQuery.

“The combination of Looker and Google BigQuery is powerful, allowing us to get data-hungry analysts essential information much faster,” says Sam Chase, Tech Lead, Data Operations at Blue Apron. “Because we choose to pay by the query, it’s also flexible and cost effective—plus storage is cheap, so we can just put data in and query what we need.”

“After we moved to Google BigQuery, query time was reduced exponentially. It’s an astonishing difference, allowing us to run 300 queries per day.”

—Sam Chase, Tech Lead, Data Operations, Blue Apron

The analytics platform of the future

When you’re making business decisions about a customer’s dinner, speed matters. Looker takes full advantage of the power of Google BigQuery, making it easy to build a data exploration platform.

Blue Apron’s applications publish event data to Kafka—approximately 140 million events per day—and data is then streamed into Google BigQuery, which performs lightning-fast queries on both streamed and static data. Now, business users and analytics teams can make decisions based on near real-time information in Looker, instead of waiting until the next business day for results.

“After we moved to Google BigQuery, query time was reduced exponentially. It’s an astonishing difference, allowing us to run 300 queries per day,” says Sam.

Previously, Blue Apron spent up to a week out of every month optimizing its data warehouse to attempt to improve query performance. With Google BigQuery, all maintenance is handled by Google, reclaiming 25% of up to two engineers’ time. Even when multiple people are using Looker concurrently, query performance never degrades and storage never runs out.

“Because Google BigQuery is architected as a giant, shared cluster, growth is smooth,” says Lloyd Tabb, Founder and CTO of Looker. “Like a race car going from 0 to 120 mph, there are no shift points, just smooth acceleration. To us, it looks like the future.”

An empowering, integrated toolset

Looker takes advantage of aggressive caching and support for date-based table partitioning in Google BigQuery to increase performance, simplify the load process, and improve data manageability. By partitioning data by time, Blue Apron can also take advantage of better long-term storage pricing without sacrificing query performance. When using Google BigQuery with Looker, analysts can easily see how much data is going to be scanned before each query is run.

Blue Apron is also using Looker for Google BigQuery Data Transfer Service to provide actionable analytics for all of the company’s Google marketing data from Google AdWords and DoubleClick by Google in one place to understand campaign performance across channels, saving its data operations team months of work. Using Looker Blocks, marketers can quickly make sense of the data with reports and dashboards, and set alerts when campaign performance hits certain thresholds.

“Everyone at Blue Apron is excited about using Google BigQuery with Looker. Business users and marketers are more empowered to look for answers, instead of waiting for analytics teams. Because users know they can get results rapidly, our business processes are evolving and improving.”

—Sam Chase, Tech Lead, Data Operations, Blue Apron

Looker Blocks for Google AdWords and DoubleClick by Google provide all the analysis you’d get straight from the Google console, plus additional value-add analysis that’s impossible to replicate without SQL. Complex metrics such as ROI on ad spend, flexible multi-touch attribution, and predictive lifetime value empower marketers with a better understanding of their customers and where to spend their next dollar.

In addition to these turnkey dashboards and pieces of analysis, marketers can customize views to meet their unique needs and workflows. These capabilities help the Blue Apron marketing team make decisions regarding the allocation of spend to maximize customer acquisition and retention.

“Everyone at Blue Apron is excited about using Google BigQuery with Looker,” says Sam. “Business users and marketers are more empowered to look for answers, instead of waiting for analytics teams. Because users know they can get results rapidly, our business processes are evolving and improving.”

For data cleansing and transformation, Blue Apron uses Google Cloud Dataproc to run fully managed Apache Spark clusters on Google Cloud Platform. It’s also leveraging Google BigQuery integration with G Suite to bring data into Google Sheets for further distribution and analysis.

“Transferring data between Google tools is fast because it all happens on the Google network,” says Sam. “We can pull data from Google BigQuery, run transformations with Spark, and then write it back to Google BigQuery. That’s very helpful in providing our business users and data analysts with the richest, most current data.”

A perfect match for better insights

As Blue Apron seeks to expand its reach and deepen its engagement with customers, it is making Google BigQuery and Looker available to more users, providing a high-quality interactive analytics experience. “Our ability to pull a lot of data in and compute fast results affects everyone in our company,” says Sam. “Using Google BigQuery and Looker to iterate quickly and build new models to make our operations more efficient will directly impact our customers.”

For Looker, Google BigQuery represents the next step in data warehouse evolution. “Google BigQuery is a perfect match for Looker, combining easy setup with near infinite scale-out and elasticity,” says Lloyd. “People can make smarter decisions faster that directly benefit their business and customers.”

Earlier this month, SIGOPS announced that it had selected the paper, “Spanner: Google’s Globally-Distributed Database” for the 2022 SIGOPS Hall of Fame Award, an honor bestowed on the most influential Operating Systems papers published by the organization.

In giving this award, the award committee stated:

“Spanner showed how to balance the requirements of consistency, availability, and partition tolerance for a globally replicated transactional database system. In doing so, Spanner simplified the implementation of a range of higher level services which could then scale across the planet through a familiar and powerful transactional API. The key insights of leveraging clock synchronization using TrueTime and a dedicated, redundant, high-capacity datacenter WAN distinct from the public Internet as the basis for scaling remain fundamental design patterns today.”

As one of the original authors of the paper, to say that I am humbled by this award is an understatement. It’s also taken me on a trip down memory lane, and gotten me thinking about all the ways — expected and unexpected — that Spanner has influenced not only how Google builds its applications, but also how it’s simplified application design and operations for organizations that aren’t running at hyperscale.

From very early on — think early 2000s — one of Google’s biggest challenges was scaling its software to keep up with the rapid growth of the internet. We needed a database that scaled, that could be built with a small team. Those needs led to Bigtable and the NoSQL movement: a feature-lean database that performed and scaled.

But as Google grew, and entered other lines of business, our priorities evolved. We needed to simplify the jobs of our ever-larger engineering staff and support business-critical services like the AdWords and Payments platforms, as well as then-new consumer products like Gmail. To do that, we knew we had to give our product teams database features like ACID transactions, multi-region consistency, schema-declared invariants, SQL, and more — without compromising on cost or scale. At the time it was an outrageous goal, but with our experience building Bigtable and Megastore, as well as new innovations like TrueTime, we knew it could be done – and it was clear talking to our internal customers that it needed to be done.

Like most good software, our plans for Spanner really came together when we found our first serious customer: AdWords. At the time, Google’s entire advertiser-facing interface was sharded across MySQL instances on specially acquired hardware. Ads had just completed a very challenging resharding project to accommodate Google’s growing business, and was resolute in its desire to never do it again. So the Spanner team and Ads partnered on a project, called F1, to transparently move the entire AdWords stack from MySQL to Spanner.

“Before Spanner, Google’s entire advertiser-facing interface was sharded across MySQL instances on specially acquired hardware.”

By 2012, when we published the Spanner paper, we had just completed the migration of AdWords frontend traffic to the new Spanner backend, and we knew we had the seed of something really interesting. Since then, we have been rapidly improving Spanner in every dimension. Google, like most businesses, needs databases that are fully managed, easy to deploy, highly available, and robust in the face of any workload. Google services set a high bar: Spanner stores a copy of the Internet (actually several, at different stages of the indexing pipeline) and is the bedrock of availability and durability for billions of users that depend on Google in their daily lives.

Then, in 2017, Google Cloud launched Cloud Spanner, a fully managed database service that brought the unique capabilities of Spanner to every organization, allowing them to deliver always-on experiences at any scale from thousands to millions of active users across the world without sacrificing consistency.

That took us down another road: innovating to make it easy for all enterprises to build data-driven applications using Cloud Spanner. This meant adding enterprise features such as CMEK and access approval, fine-grained access control, backup and restore, and point-in-time recovery (PITR). We also added VPC Service Controls support and compliance certifications and necessary approvals so that Spanner can be used for workloads requiring ISO 27001, 27017, 27018, PCI DSS, SOC1|2|3, HIPAA and FedRamp. More recently, we added granular instance sizing, a PostgreSQL interface, and free trial instances to lower the barrier to entry for Spanner, and make it accessible for any developer and for any application, big or small.

Speaking of small, one of the interesting things we discovered along the way is that Spanner isn’t only a great fit for huge mission-critical applications – we also use it internally for smaller applications and internal tools, secure in the knowledge that the data is safe, available, and nowhere near any limits. That’s the thing about infrastructure innovations: at their best, they aren’t only about scale, they’re about extracting complexity from applications and presenting the solution in an easy-to-use package.

As we have incorporated solutions to those problems into Spanner, customers have dramatically simplified their applications. For example, application teams often created a dedicated “storage” tier to provide higher-level features like indexes, transactions, or data synchronization needed to support their business logic. Spanner enables application teams to hollow out or even completely eliminate these tiers for significant cost and maintenance savings. We have also seen big reliability dividends: rather than each application discovering the corner cases of transactions, consistency or high availability architectures one at a time, Spanner’s customers are all able to benefit from a rigorously designed and battle-tested implementation out of the gate.

“At their best, infrastructure innovations aren’t only about scale, they’re about extracting complexity from applications and presenting the solution in an easy-to-use package.”

The tradeoff between the scale limitations of relational databases and the functionality limitations of NoSQL systems is really familiar, and the software world has learned to build great applications within those confines. But we never tire of seeing great applications transform as Spanner melts the distinction away.

Get started with Spanner

It has never been easier to try out Spanner. With a free trial instance, you can try out Spanner at no cost for 90 days. You can even prototype an entire application for free on Google Cloud by using the Spanner free trial along with the free tier offered by other Google Cloud products such as Compute Engine and BigQuery.

Create a 90-day Spanner free trial instance. Try Spanner free.

Take a deep dive into the new trial experience and learn more about Spanner.

Mercury, the Roman god of commerce, is often depicted carrying a purse, symbolic of business transactions, wearing winged sandals, illustrating his abilities to move at great speeds. Transactions power the world’s business systems today, ranging from millions of packages moving worldwide tracked in real time by logistics companies to global payments from personal loans to securities trading to intergovernmental transactions, keeping goods and services flowing worldwide. Today, we are very pleased to announce the public preview of multi-statement transactions in BigQuery.

BigQuery has long supported single-statement transactions through DML statements such as INSERT, UPDATE, DELETE, MERGE and TRUNCATE, applied to one table per transaction. Multi-statement transactions expand on this scope by supporting multiple SQL statements, including DML, spanning multiple tables in a single transaction. This means that the changes to data across multiple tables associated with all statements in a given transaction are committed atomically (all at once) if successful or all rolled back atomically in the event of a failure. These new multi-statement transaction capabilities now bridge the gap between online transaction processing (OLTP) systems and BigQuery through tighter and faster integration, just as Mercury’s speed enabled him to travel rapidly between the mortal and divine worlds.

Overview

A transaction is a set of tasks carried out against a database or a data warehouse representing a business unit of work. For example, an order expedite action may result in a series of changes to multiple tables, e.g., updating delivery dates on the order, adding new shipment lines on a shipment to create expedited delivery shipments and canceling (updating) prior normal delivery shipment lines. Correspondingly, when a batch job execute an ETL process in a data warehouse, it may perform data wrangling and data cleansing operations by transforming and saving the data in multiple tables. In both of these cases, if a failure occurs, then the entire transaction must be rolled back so that the system of record does not have partial data saved putting the dataset in an inconsistent state.

Transactions are known for their atomicity, consistency, isolation, and durability (ACID) properties. Atomicity indicates that all data changes (including metadata changes) are applied or reverted in a single operation, e.g. when a set of rows are added to a table with enrichment and the corresponding set of rows deleted, both operations happen together. Consistency refers to the consistency of data before and after the transaction has completed. When data is added to or updated in a table, the changes to data in tables are consistent with the constraints specified on the table or its columns, e.g. ensuring that the NOT NULL constraint is complied with when data gets added or modified in a table. Isolation indicates that multiple transactions can make changes to the table concurrently as long as the changes don’t conflict with each other, e.g. two transactions loading data into tables from different business units can proceed concurrently. Durability is the last and possibly the most important property of transactions which guarantees that in the event of a failure (after completing the transaction), all the changes committed by the transaction are saved for future transactions or queries.

Isolation level

All transactions in BigQuery including multi-statement transactions support snapshot isolation. At this isolation level, all statements in a transaction see a consistent snapshot of the database, as of the start of the transaction. BigQuery achieves this due to its multi-version property where it maintains the history of mutations to the database for a certain period. Transactions do not see any changes, committed or uncommitted, from other concurrent transactions while the transaction is in progress. BigQuery supports read-your-own-writes, where statements in a transaction can see all the changes applied by prior statements in the same transaction.

Example

Let’s run through an example to demonstrate how BigQuery supports the concept of multi-statement transactions with multiple sets of changes across multiple tables in one operation. In this example, we will set up this demo by creating 10 tables each with 100 partitions 

Language: SQL

  DECLARE num_tables DEFAULT 10;
DECLARE x INT64 DEFAULT 0;
# replace "demo" with your preferred dataset name
DECLARE ds STRING default "`00jathreya`";
DECLARE prefix STRING DEFAULT "mst_demo";
DECLARE table_name STRING;
DECLARE pre_table_name STRING;
/*
Sets up 10 tables, with schema (partition_id:integer,value:timestamp).
Each table has 100 partitions, with partition key 0, 10, 20, ..., 990.
*/
SET x = 0;
WHILE x < num_tables DO
 SET table_name = format("%s.%s_%d", ds, prefix, x); 
 EXECUTE IMMEDIATE format("""DROP TABLE IF EXISTS %s""", table_name); 
 EXECUTE IMMEDIATE format("""
 CREATE OR REPLACE TABLE %s (partition_id INT64, value TIMESTAMP)
 PARTITION BY RANGE_BUCKET(partition_id, GENERATE_ARRAY(0, 1000, 10))
 AS SELECT partition_id, CURRENT_TIMESTAMP() AS value FROM UNNEST(GENERATE_ARRAY(0, 990, 10)) AS partition_id
 """, table_name);
 SET x = x + 1;
END WHILE;
# Verify the partitioned tables created and the timestamp
execute immediate format("""select table_schema, table_name, count(partition_id), sum(total_rows), max(last_modified_time) from %s.INFORMATION_SCHEMA.PARTITIONS
where starts_with(table_name,'%s')
group by table_schema, table_name""", ds, prefix);
# Verify data in table 9 based on the timestamp
EXECUTE IMMEDIATE format("""
SELECT * FROM %s ORDER BY partition_id
""", table_name);

Next, we will show how the new COMMIT and ROLLBACK commands in BigQuery allow you to save or reverse the changes in a transaction spanning multiple tables.

Language: SQL

  DECLARE num_tables DEFAULT 10;
DECLARE x INT64 DEFAULT 0;
# Replace `mydataset` with your preferred dataset name
DECLARE ds STRING DEFAULT "`mydataset`";
DECLARE prefix STRING DEFAULT "mst_demo";
DECLARE table_name STRING;
DECLARE pre_table_name STRING;
/*
Steps of the transaction:
1. For table 0, updates the timestamp to CURRENT_TIMESTAMP.
2. FOR table 1 to 9, merge table x using data from table x-1. All MERGE should
see the updated data in table x-1, so as a result the change in table 0 should be
propagated all the way to table 9.
3. A total of 1000 partition changes on 10 tables.
*/
BEGIN TRANSACTION;
SET x = 0;
WHILE x < num_tables DO
 SET table_name = format("%s.%s_%d", ds, prefix, x);
 IF x = 0 THEN
   EXECUTE IMMEDIATE format("""
   UPDATE %s SET value = CURRENT_TIMESTAMP WHERE true
   """, table_name);
 ELSE
   SET pre_table_name = format("%s.%s_%d", ds, prefix, x-1);
   EXECUTE IMMEDIATE format("""
   MERGE %s t2 USING %s t1 ON t2.partition_id = t1.partition_id
   WHEN MATCHED THEN UPDATE SET value = t1.value
   """, table_name, pre_table_name);
 END IF;
 SET x = x + 1;
END WHILE;
# Verify data in table 9 inside transaction shows recently updated data
SET table_name = format("%s.%s_%d", ds, prefix, num_tables-1);
EXECUTE IMMEDIATE format("""
SELECT * FROM %s ORDER BY partition_id
""", table_name);
IF x = 9 THEN
 COMMIT TRANSACTION;
ELSE
 ROLLBACK TRANSACTION;
END IF;
# Verify data in table 9 outside transaction shows timestamp of original data
EXECUTE IMMEDIATE format("""
SELECT * FROM %s ORDER BY partition_id
""", table_name);

Monitoring and Management

Unlike single-statement transactions which map to a single DML command, multi-statement transactions are complex spanning multiple DML operations across multiple tables. To monitor these complex jobs, the BigQuery JOBS INFORMATION_SCHEMA views have been expanded to add a new column, TRANSACTION_ID, which contains a unique identifier for each transaction in BigQuery. You can query transactions that are in progress or the ones that have completed by querying these views.

Language: SQL

  #Returns the list of transactions running under a parent job ID .;
SELECT
DISTINCT transaction_id
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE parent_job_id = "job_id" AND state = "RUNNING";
# Returns the active transactions and parent job id that are running and affect a named table.;
WITH 
  running_transactions AS (
  SELECT DISTINCT transaction_id 
  FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT 
  WHERE state = "RUNNING")
SELECT 
  transaction_id, parent_job_id, query 
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT, running_transactions
WHERE destination_table = "mytable" 
  AND transaction_id = running_transactions.transaction_id;
# Returns all successfully committed transactions
SELECT 
  transaction_id, parent_job_id, query 
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT 
WHERE statement_type = "COMMIT_TRANSACTION" AND error_result IS NULL;
# Returns all rolled back transactions
SELECT 
  transaction_id, parent_job_id, query 
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE statement_type = "ROLLBACK_TRANSACTION" AND error_result IS NULL;

To terminate a transaction, you will need to terminate the parent job under which the transaction is running by passing the parent job ID of the script containing the transaction to the BQ.JOBS.CANCEL system procedure.

Conclusion

As enterprises accelerate towards more real time insight-driven decisions, multi-statement transactions are a key enabler towards bringing transaction data into analytical data warehouses, such as BigQuery. With strong ACID properties, full transaction support for commit and rollback, and rich monitoring and management APIs built on proven transaction processing backbone, we are very pleased to help our BigQuery customers unlock the value of transactions through these new capabilities.