Today, we’re announcing Datastream, a serverless change data capture (CDC) and replication service, available now in preview. Datastream allows enterprises to synchronize data across heterogeneous databases, storage systems, and applications reliably and with minimal latency to support real-time analytics, database replication, and event-driven architectures. You can now easily and seamlessly deliver change streams from Oracle and MySQL databases into Google Cloud services such as BigQuery, Cloud SQL, Google Cloud Storage, and Cloud Spanner, saving time and resources and ensuring your data is accurate and up-to-date.

“Global companies are demanding change data capture to provide replication capabilities across disparate data sources, and provide a real-time source of streaming data for real-time analytics and business operations,” says Stewart Bond, Director, Data Integration and Intelligence Software Research at IDC.

However, companies are finding it difficult to realize these capabilities because commonly used data replication offerings are costly, cumbersome to set up, and require significant management and monitoring overhead to run flexibly or at scale. This leaves customers with a difficult-to-maintain and fragmented architecture. 

Datastream’s differentiated approach 

Datastream is taking on these challenges with a differentiated approach. Its serverless architecture seamlessly and transparently scales up or down as data volumes shift in real time, freeing teams to focus on delivering up-to-date insights instead of managing infrastructure. It also provides the streamlined customer experience, ease of use, and security that our customers have come to expect from Google Cloud, with private connectivity options built into the guided setup experience. 

Datastream integrates with purpose-built and extensible Dataflow templates to pull the change streams written to Cloud Storage, and create up-to-date replicated tables in BigQuery for analytics. It also leverages Dataflow templates to replicate and synchronize databases into Cloud SQL or Cloud Spanner for database migrations and hybrid cloud configurations. 

Datastream also powers a Google-native Oracle connector in Cloud Data Fusion’s new replication feature for easy ETL/ELT pipelining. And by delivering change streams directly into Cloud Storage, customers can leverage Datastream to implement modern, event-driven architectures.

Customers tell us about the benefits they’ve found using Datastream. That includes Schnuck Markets, Inc., “Leveraging Datastream, we’ve been able to replicate data from our on-premises databases to BigQuery reliably and with little impact to our production workloads. This new method replaced our batch processing and allowed for insights to be leveraged from BigQuery quicker,” says Caleb Carr, principal technologist from Schnuck Markets. “Furthermore, implementing Datastream removed the need for our analytics group to reference on-premises databases to do their work and support our business users.”

Cogeco Communications, Inc. used Datastream to also realize the value of low-latency data access. “Datastream unlocked new customer interaction opportunities not previously possible by enabling low-latency access in BigQuery to our operational Oracle data.” says Jean-Lou Dupont, Senior Director, Enterprise Architecture, Cogeco Communications, Inc. “This streamlined integration process brings data from hundreds of disparate Oracle tables into a unified data hub. Datastream enabled us to achieve this with 10X time and effort efficiency.”

In addition, Major League Baseball (MLB) used Datastream’s replication capabilities to migrate their data from Oracle to Cloud SQL for PostgreSQL. “As we’re modernizing our applications, replicating the database data reliably out of Oracle and into Cloud SQL for PostgreSQL is a critical component of that process,” says Shawn O’Rourke, manager of technology at MLB. “Using Datastream’s CDC capabilities, we were able to replicate our database securely and with low latency, resulting in minimal downtime to our application. We can now standardize on this process and repeat it for our next databases, regardless of scale.”

Our partner HCL has worked with many organizations looking to get more out of their data and plan for the future. “HCL customers across every industry are looking for ways to extract more value out of their vast amounts of data,” says Siva G. Subramanian, Global Head for Data & Analytics at HCL Google Business Unit. “CDC plays a big part in the solutions we offer to our customers using Google Cloud. Datastream enables us to deliver a secure and reliable solution to our customers that’s easy to set up and maintain. CDC is a key and integrated part of Google Cloud Data Solutions.”

“Google Cloud’s new CDC offering, Datastream, is a differentiator for Google among hyperscale cloud service providers, by supporting replication of data from Oracle and MySQL databases into the Google Cloud environment using a serverless cloud-native architecture, which removes the burden of infrastructure management for organizations, and provides elastic scalability to handle real-time workloads,” says Stewart Bond, Director, Data Integration and Intelligence Software Research at IDC.

Datastream under the hood

Datastream reads CDC events (inserts, updates, and deletes) from source databases, and writes those events with minimal latency to a data destination. It leverages the fact that each database source has its own CDC log—for MySQL it’s the binlog, for Oracle it’s LogMiner—which it uses for its own internal replication and consistency purposes. Using Google-native, agentless, high-scale log reader technology, Datastream can quickly and efficiently generate change streams populated by events based on the database’s CDC log while minimizing performance impact on the source database.

Each generated event includes the entire row of data from the database, with the data type and value of each column. The original source data types, whether it’s, for example, an Oracle NUMBER type or a MySQL NUMERIC type, are normalized into Datastream unified types. The unified types represent a lossless superset of all possible source types, and the normalization means data from different sources can easily be processed and queried downstream in a source-agnostic way. Should a downstream system need to know the original source data type, it can perform a quick API call to Datastream’s Schema Registry, which stores up-to-date, versioned schemas for every data source. This also allows for in-flight downstream schema drift resolution as source database schemas change. 

The generated streams of events, referred to as “change streams,” are then written as files, either in JSON or Avro format during preview or in other formats like Parquet in the future, into a Cloud Storage bucket organized by source table and event times. Files are rotated as table schemas change, so events in a single file always have the same schema, as well as on a configurable file size or rotation frequency setting. This way customers can find the best balance between the speed of data availability and the file size that makes the most sense for their business use case.

Through its integration with Dataflow, Datastream powers up-to-date, replicated tables for analytics over BigQuery, and for data replication and synchronization to Cloud SQL and Spanner. Datastream refers to these constantly updated tables as “materialized views.” They are kept up-to-date via Dataflow template-based upserts into Cloud SQL or Spanner, or through consolidations into BigQuery. The consolidations, performed as part of the Dataflow template, take the change streams that are written into a log table in BigQuery, and push those changes into a final table, which mirrors the table from the source.

Datastream offers a variety of secure connectivity methods to sources, so your data is always safe in transit. And with its serverless architecture, Datastream can scale up and down readers and processing power to seamlessly keep up with the speed of data and ensure minimal latency end to end. As data volumes decrease, Datastream automatically scales back down—the result is a “pay for what you use” pricing model, where you never have to pay for idle machines or worry about bottlenecks and delays during data peaks.

Get started with Datastream 

Datastream, now available in preview, supports streaming change data from Oracle and MySQL sources, hosted either on-premises or in the cloud, into Cloud Storage. You can start streaming your data today for $2 per GB of data processed by Datastream. 

To get started, head over to the Datastream area of your Google Cloud console, under Big Data, and click Create Stream. There you can:

1. Initiate stream creation, and see what actions you need to take to set up your source and destination for successful streaming.
2. Define your source and destination, whose connectivity information is saved as connection profiles you can re-use for other streams. Sources support multiple connectivity options, with both private and public connectivity options to suit your business needs.
3. Select the source data you’d like to stream, and which you’d like to exclude.
4. Test your stream to ensure it will be successful when you’re ready to go.

Start your stream and your database’s CDC data will start to flow to your Cloud Storage bucket! From there you can integrate with Dataflow templates to load data into BigQuery, Spanner, or Cloud SQL. Datastream’s preview is supported in us-central1, europe-west1, and asia-east1, with additional regions coming soon.https://www.youtube.com/embed/FZG4w4Vbj38?enablejsapi=1&

Datastream will become generally available later this year, and will soon expand its support to also include PostgreSQL and SQL Server as sources, as well as out-of-the-box integration with BigQuery for easy delivery of up-to-date replicated tables for analytics, and message queues like Pub/Sub for real-time change stream access. 

For more resources to help get you started with change streaming, check out the Datastream documentation.

Cloud Bigtable is a popular and widely used key-value database available on Google Cloud. The service provides scale elasticity, cost efficiency, excellent performance characteristics, and 99.999% availability SLA. This has led to massive adoption with thousands of customers trusting Bigtable to run a variety of their mission-critical workloads.

Bigtable has been in continuous production usage at Google for more than 15 years now. It processes more than 5 billion requests per second at peak and has more than 10 exabytes of data under management. It’s one of the largest semi-structured data storage services at Google.

One of the key use cases for Bigtable at Google is ad personalization. This post describes the central role that Bigtable plays within ad personalization.

Ad personalization

Ad personalization aims to improve user experience by presenting topical and relevant ad content. For example, I often watch bread-making videos on YouTube. If ads personalization is enabled in my ad settings, my viewing history could indicate to YouTube that I’m interested in baking as a topic and would potentially be interested in ad content related to baking products.

Ad personalization requires large-scale data processing in near real-time for timely personalization with strict controls for user data handling and retention. System availability needs to be high, and serving latencies need to be low due to the narrow window within which decisions need to be made on what ad content to retrieve and serve. Sub-optimal serving decisions (e.g. falling back to generic ad content) could potentially impact user experience. Ad economics requires infrastructure costs to be kept as low as possible.

Google’s ad personalization platform provides frameworks to develop and deploy machine learning models for relevance and ranking of ad content. The platform supports both real-time and batch personalization. The platform is built using Bigtable, allowing Google products to access data sources for ads personalization in a secure manner that is both privacy and policy compliant, all while honoring users’ decisions about what data they want to provide to Google

The output from personalization pipelines, such as advertising profiles are stored back in Bigtable for further consumption. The ad serving stack retrieves these advertising profiles to drive the next set of ad serving decisions.

Some of the storage requirements of the personalization platform include:

• Very high throughput access for batch and near real-time personalization
• Low latency (<20 ms at p99) lookup for reads on the critical path for ad serving
• Fast (i.e. in the order of seconds) incremental update of advertising models in order to reduce personalization delay

Bigtable

Bigtable’s versatility in supporting both low-cost, high-throughput access to data for offline personalization as well as consistent low-latency access for online data serving makes it an excellent fit for the ads workloads.

Personalization at Google-scale requires a very large storage footprint. Bigtable’s scalability, performance consistency and low cost required to meet a given performance curve are key differentiators for these workloads.

Data model

The personalization platform stores objects in Bigtable as serialized protobufs keyed by Object ids. Typical data sizes are less than 1 MB and serving latency is less than 20 ms at p99.

Data is organized as corpora, which correspond to distinct categories of data. A corpus maps to a replicated Bigtable.

Within a corpus, data is organized as DataTypes, logical groupings of data. Features, embeddings, and different flavors of advertising profiles are stored as DataTypes, which map to Bigtable column families. DataTypes are defined in schemas which describe the proto structure of the data and additional metadata indicating ownership and provenance. SubTypes map to Bigtable columns and are free-form.

Each row of data is uniquely identified by a RowID, which is based on the Object ID. The personalization API identifies individual values by RowID (row key), DataType (column family), SubType (column part), and Timestamp.

Consistency

The default consistency mode for operations is eventual. In this mode, data from the Bigtable replica nearest to the user is retrieved, providing the lowest median and tail latency.

Reads and writes to a single Bigtable replica are consistent. If there are multiple replicas of Bigtable in a region, traffic spillover across regions is more likely. To improve the likelihood of read-after-write consistency, the personalization platform uses a notion of row affinity. If there are multiple replicas in a region, one replica is preferentially selected for any given row, based on a hash of the Row ID.

For lookups with stricter consistency requirements, the platform first attempts to read from the nearest replica and requests that Bigtable return the current low watermark (LWM) for each replica. If the nearest replica happens to be the replica where the writes originated, or if the LWMs indicate that replication has caught up to the necessary timestamp, then the service returns a consistent response. If replication has not caught up, then the service issues a second lookup—this one targeted at the Bigtable replica where writes originated. That replica could be distant and the request could be slow. While waiting for a response, the platform may issue failover lookups to other replicas in case replication has caught up at those replicas.

Bigtable replication

The Ads personalization workloads use a Bigtable replication topology with more than 20 replicas, spread across four continents.

Replication helps address the high availability needs for ad serving. Bigtable’s zonal monthly uptime percentage is in excess of 99.9%, and replication coupled with a multi-cluster routing policy allows for availability in excess of 99.999%.

A globe-spanning topology allows for data placement that is close to users, minimizing serving latencies. However, it also comes with challenges such as variability in network link costs and throughputs. Bigtable uses Minimum Spanning Tree-based routing algorithms and bandwidth-conserving proxy replicas to help reduce network costs.

For ads personalization, reducing Bigtable replication delay is key to lowering the personalization delay (the time between a user’ action and when that action has been incorporated into advertising models to show more relevant ads to the user). Faster replication is preferred but we also need to balance serving traffic against replication traffic and make sure low-latency user-data serving is not disrupted due to incoming or outgoing replication traffic flows. Under the hood, Bigtable implements complex flow control and priority boost mechanisms to manage global traffic flows and to balance serving and replication traffic priorities.

Workload Isolation

Ad personalization batch workloads are isolated from serving workloads by pinning a given set of workloads onto certain replicas; some Bigtable replicas exclusively drive personalization pipelines while others drive user-data serving. This model allows for a continuous and near real-time feedback loop between serving systems and offline personalization pipelines, while protecting the two workloads from contending with each other.

For Cloud Bigtable users, AppProfiles and cluster-routing policies provide a way to confine and pin workloads to specific replicas to achieve coarse-grained isolation.

Data residency

By default, data is replicated to every replica—often spread out globally—which is wasteful for data that is only accessed regionally. Regionalization saves on storage and replication costs by confining data to the region where it is most likely to be accessed. Compliance with regulations mandating that data pertaining to certain subjects are physically stored within a given geographical area is also vital.

The location of data can be either implicitly determined by the access location of requests or through location metadata and other product signals. Once the location for a user is determined, it is stored in a location metadata table which points to the Bigtable replicas that read requests should be routed to. Migration of data based on row-placement policies happens in the background, without downtime or serving performance regressions.

Conclusion

In this blog post, we looked at how Bigtable is used within Google to support an important use case—modeling user intent for ad personalization.

Over the past decade, Bigtable has scaled as Google’s personalization needs have scaled by orders of magnitude. For large-scale personalization workloads, Bigtable offers low cost storage with excellent performance characteristics. It seamlessly handles global traffic flows with simple user configurations. Its ease at handling both low-latency serving and high-throughput batch computations make it an excellent option for lambda-style data processing pipelines.

We continue to drive high levels of investment to further lower costs, improve performance, and bring new features to make Bigtable an even better choice for personalization workloads.

Learn more

To get started with Bigtable, try it out with a Qwiklab and learn more about the product here.

Acknowledgements
We’d like to thank Ashish Awasthi, Ashish Chopra, Jay Wylie, Phaneendhar Vemuru, Bora Beran, Elijah Lawal, Sean Rhee and other Googlers for their valuable feedback and suggestions.

When you’re serving customers online, speed is essential for a good experience. As the amount of data in a database grows, queries that used to be fast can slow down.

For example, if a query has to scan every row because a table is missing an index, response times that were acceptable with a thousand rows can turn into multiple seconds of waiting once you have a million rows.

If this query is executed every time a user loads your web page, their browsing experience will slow to a crawl, causing user frustration. Slow queries can also impact automated jobs, causing them to time out before completion.

If there are too many of these slow queries executing at once, the database can even run out of connections, causing all new queries, slow or fast, to fail. 

The popular open-source databases MySQL and Google Cloud Platform‘s fully managed version, Cloud SQL for MySQL, include a feature to log slow queries, letting you find the cause, then optimize for better performance.

However, developers and database administrators typically only access this slow query log reactively, after users have seen the effects and escalated the performance degradation.

With Stackdriver Logging and Monitoring, you can stay ahead of the curve for database performance with automatic alerts when query latency goes over the threshold, and a monitoring dashboard that lets you quickly pinpoint the specific queries causing the slowdown.

To get started, import MySQL’s slow query log into Stackdriver Logging.

Once the logs are in Stackdriver, it’s straightforward to set up logs-based metrics that can both count the number of slow queries over time, which is useful for setting up appropriate alerts, and also provide breakdowns by slow SQL statement, allowing speedy troubleshooting.

What’s more, this approach works equally well for managed databases in Cloud SQL for MySQL and for self-managed MySQL databases hosted on Compute Engine. 

For a step-by-step tutorial to set up slow query monitoring, check out Monitoring slow queries in MySQL with Stackdriver. For more ideas about what else you can accomplish with Stackdriver Logging, check out Design patterns for exporting Stackdriver Logging.

Editor’s note: In honor of Mother’s Day, we look at how 1-800-FLOWERS.COM, Inc. migrated to Google Cloud as part of its digital transformation to quickly deploy seamless and convenient customer experiences across multiple brands on Mother’s Day and every day. 

As a leading provider of gifts designed to help customers express, connect, and celebrate,  1-800-FLOWERS.COM, Inc. has embraced cloud technologies to grow and transform its business through constant innovation. As part of our digital transformation, we recently completed the migration of our ecommerce platform and other services to Google Cloud. We’ve transitioned from a monolithic to a microservices platform, moved many workloads from our on-premises data centers to our Google Cloud environment, and scaled both horizontally and vertically. 

Since our migration, we’ve developed efficient processes to launch new brands, improved the customer experience across all brands, and seen significantly increased site traffic. 

Nurturing a more delightful customer journey

Customer delight is at the core of everything we do. Whether it be with a flower bouquet, a sweet treat, or a personalized keepsake, our mission is to deliver smiles. With the rise of the COVID-19 pandemic, we’ve all been challenged to find unique and safe ways to continue honoring the special connections in our lives and celebrating occasions with loved ones. Our customers have adapted by doing things such as sending gifts to isolated loved ones, sharing the same meal together virtually, or using video to engage with others through group activities like flower arranging and building charcuterie boards. 

The customer experience is a top priority for us, and we constantly look for innovative ways to enhance the customer journey across our ecommerce platform of more than a dozen brands. As a result, we’ve continued to see a rise in demand as customers enjoy the ease and convenience of our site and discover our full family of brands. 

Migrating to a cloud-first mindset

As we’ve continued to innovate and iterate on the customer experience, we knew we wanted to evolve our platform. We wanted to shift to a microservices platform, which would allow our team the opportunity to release updates to our site more often and set up the right continuous integration/continuous deployment (CI/CD) practices. 

Working with Google Cloud, we were able to move our ecommerce platform to the cloud and standardize our site and brand deployment by building one release that could then be repeated across all of our brands. We built everything in a modular fashion, including microservices and      code libraries, so that sites could be easily constructed and replicated for each brand. And because of this, we were able to launch Shari’s Berries extremely quickly after we acquired the brand in 2019.   

Moving our platform to a completely homegrown solution of microservices was a daunting task. But our team handled it beautifully through load-testing, stress-testing, and building new monitoring tools. And with Google Cloud supporting us all along the way, managing the migration process was simple and easy from start to finish. 

Arranging a better bouquet of services

Currently, we’ve migrated every customer-facing touchpoint for all of our brands to Google Cloud—whether it’s on the web or mobile, our AI bots, or our chat interfaces. 

• We run on Google Kubernetes Engine and Istio.
• We have nearly 200 microservices built to help power our entire ecommerce stack across several cloud services running on Google Cloud. 
• We’re utilizing BigQuery for our offline intelligence.

Results are coming up roses

Our new stack on Google Cloud has benefits for both us and our customers. We moved from a session-based to a token-based system, which provides enhanced security as well as a consistent, convenient experience across all our brands. Using service workers and a single-page app, we are able to download all the relevant site content to the browser in under two seconds to create an instant-click experience for each and every customer. We also use Google Analytics to measure our user interactions and provide personalized results to each customer. Our hope is that with this new system, we can learn from customer behavior to offer gift givers a more personalized shopping experience during each visit. 

The benefits of our new tech stack have not only helped us enhance the solutions we offer to customers today, they’ve also enabled us to offer new ones at lightning speed. With our legacy system, we used to release new code once a week or once a month. Now, even during our peak periods, we’re able to release 10 to 15 times a day and can deploy and pivot quickly to create new microservices and microsites on the fly—often without having to touch any code. 

Efficiencies abound     

The benefits of moving our platform to Google Cloud have extended to our internal teams as well. Before the migration, we had only two environments for developing and testing, which made it time-consuming to test updates before they went into production. Now with Google Cloud, we have several different journey teams—which are made up of developers, product owners, and technical owners—all working in several different environments, solving problems, and creating new solutions together. 

Everyone is now empowered to be self-sufficient, developing and releasing microservices on their own when they’re ready. This has given our developers more time to take part in continued development and learning opportunities. For example, we offer lunch-and-learn sessions as well as other resources for everyone to take advantage of so they can continue to learn and refine their skills.

Planting the seeds for future growth

As we look to the future and think about how we help our customers express, connect, and celebrate, we’ll continue to collaborate across teams to deliver solutions that spread smiles. Specifically, we’re exploring additional use of AI to help us better serve our customers across all our brands. 

We’ve enjoyed the ongoing support we’ve received from the Google Cloud team as they help us build new solutions and design a road map for the future. Their support has helped the 1-800-FLOWERS.COM, Inc. team to realize the power of the cloud and bring the very best experience to our customers.

Learn more about 1-800-FLOWERS.COM, Inc., or check out our recent blog about cloud migration for the real world.

Enterprises expect extreme reliability of the database infrastructure that’s accessed by their applications. Despite your best intentions and careful engineering, database errors happen, whether that’s machine crashes or network partitioning. Good planning can help you stay ahead of problems and recover more quickly when issues do occur.

This blog shows one approach of deploying a database architecture that implements high availability and disaster recovery for MySQL on Compute Engine, using regional disks as well as load balancers.

Any database architecture must provide approaches to tolerate errors and recover from those errors quickly without losing data. These approaches are expressed in RTO (recovery time objective) and RPO (recovery point objective), which offer ways to set and then measure how long a service can be unavailable, and how far back data should be saved.

After a database error, a database must recover as fast as possible with an RTO as small as possible, ideally in seconds. There must be as little data loss as possible—ideally, none at all. The desired RPO is the last consistent database state.

From a database architecture and deployment viewpoint, this can be accomplished with two distinct concepts: high availability and disaster recovery. Use both at the same time in order to achieve an architecture that’s prepared for the widest range of errors or incidents.

Creating a resilient database architecture

A high-availability database architecture has database instances in two or more zones. If a server on a zone fails, or the zone becomes inaccessible, the instances in other zones are available to continue the processing. The figure below shows two instances, one in zone zn1, and one in zone zn2. The load balancer in front supports directing traffic to a healthy database instance available for read and write queries.

A disaster recovery architecture adds a second high-availability database setup in a second region. If one of the regions becomes inaccessible or fails, the other region takes over. The figure below shows two regions, primary and DR. Data is replicated from the primary to the DR region so that the DR region can take over from the latest consistent database state. The load balancer in front of the regions directs traffic to the region in charge of the read and write traffic. Here’s how this architecture looks:

In addition to the database instance setup, a regional disk is deployed so that data is written simultaneously in two zones, proving fail-safe in the event of zone failure. This is a huge advantage of Google Cloud, allowing you to skip MySQL-level replication within a region. Each write operation to disk is done in two zones synchronously. When the primary instance fails, a standby instance is mounted with regional persistent disk(s), and the database service (MySQL) is then started using the same. This brings the peace of mind of not worrying about replication lag or database state for high availability.

From a disaster recovery process view, the following happens over time during a failure situation:

• Normal steady state database operation
• A failure happens and a region becomes unavailable or the database instance inaccessible
• A decision must be made to fail over or not (in case there is the expectation that the region becomes available soon enough or the instance becomes responsive again)
• DNS is updated manually, therefore it redirects application traffic to a second region
• Fallback to the primary region after it becomes available again is optional, as the second region is a fully built-out deployment

From a high-availability process view, the following happens over time during a failure situation:

• Normal steady state database operation
• Database instance fails or becomes unavailable
• Launch the standby instance
• Mount regional SSD and start database
• Automatic redirection of application traffic to the standby via load balancer
• After the failed or unavailable instance becomes available again, a fallback can take place or not

The database architecture shown demonstrates a highly available architecture supporting disaster recovery. With regional disks and load balancers, it is straightforward to provide a resilient database deployment.

Find out more about load balancers and regional disks. Check out general HA and DR processes and detailed steps in the initial part of the reference guide. Try it out to become familiar with the architecture as well as the two major failover processes.