How to Enhance Incremental Pipeline Performance while Ingesting Data into BigQuery - Build What's Next

Build What's Next

Big Query
Bigtable
Cloud Memorystore
Cloud Spanner
Cloud SQL
Firestore/Firebase
Oracle Workloads
How-to

How to Enhance Incremental Pipeline Performance while Ingesting Data into BigQuery

6336

Of your peers have already read this article.

3:00 Minutes

The most insightful time you'll spend today!

When ingesting data into BigQuery, you can enhance data ingestion pipelines from large and frequently updated table in the source system. Refer the blog with examples and tips listed to streamline your journey with BigQuery.

When you build a data warehouse, the important question is how to ingest data from the source system to the data warehouse. If the table is small you can fully reload a table on a regular basis, however, if the table is large a common technique is to perform incremental table updates. This post demonstrates how you can enhance incremental pipeline performance when you ingest data into BigQuery.

Setting up a standard incremental data ingestion pipeline

We will use the below example to illustrate a common ingestion pipeline that incrementally updates a data warehouse table. Let’s say that you ingest data into BigQuery from a large and frequently updated table in the source system, and you have Staging and Reporting areas (datasets) in BigQuery.

Optimizing Big Query 1.jpg

The Reporting area in BigQuery stores the most recent, full data that has been ingested from the source system tables. Usually you create the base table as a full snapshot of the source system table. In our running example, we use BigQuery public data as the source system and create reporting.base_table as shown below. In our example each row is identified by a unique key which consists of two columns: block_hash and log_index.

  CREATE TABLE reporting.base_table  --156 GB processed
PARTITION BY TIMESTAMP_TRUNC(block_timestamp, DAY) AS
SELECT log_index, data, topics, block_timestamp, block_hash
FROM bigquery-public-data.crypto_ethereum.logs
WHERE block_timestamp BETWEEN TIMESTAMP '2020-01-01' AND TIMESTAMP '2020-11-30';

In data warehouses it is common to partition a large base table by a datetime column that has a business meaning. For example, it may be a transaction timestamp, or datetime when some business event happened, etc. The idea is that data analysts who use the data warehouse usually need to analyze only some range of dates and rarely need the full data. In our example, we partition the base table by block_timestamp which comes from the source system.

After ingesting the initial snapshot you need to capture changes that happen in the source system table and update the reporting base table accordingly. This is when the Staging area comes into the picture. The staging table will contain captured data changes that you will merge into the base table. Let’s say that in our source system on a regular basis we have a set of new rows and also some updated records. In our example we mock the staging data as follows: first, we create new data, than we mock the updated records:

  CREATE TABLE staging.load_delta AS --5 GB processed
SELECT log_index, data, topics, block_timestamp, block_hash
FROM bigquery-public-data.crypto_ethereum.logs
WHERE block_timestamp BETWEEN TIMESTAMP '2020-12-01' AND TIMESTAMP '2020-12-07';
 
INSERT INTO staging.load_delta --2 GB processed
SELECT log_index, CONCAT(data, RAND()), topics, block_timestamp, block_hash
FROM bigquery-public-data.crypto_ethereum.logs TABLESAMPLE SYSTEM (5 PERCENT)
WHERE block_timestamp BETWEEN TIMESTAMP '2020-10-01' AND TIMESTAMP '2020-11-30';

Next, the pipeline merges the staging data into the base table. It joins two tables by unique key and than updates the changed value or inserts a new row

  MERGE INTO reporting.base_table T --161 GB processed
USING staging.load_delta S
ON T.block_hash = S.block_hash
 AND T.log_index = S.log_index
WHEN MATCHED THEN UPDATE SET 
  T.data = S.data
WHEN NOT MATCHED THEN INSERT (log_index, data, topics, block_timestamp, block_hash)
VALUES (log_index, data, topics, block_timestamp, block_hash);

It is often the case that the staging table contains keys from various partitions but the number of those partitions are relatively small. It holds, for instance, because in the source system the recently added data may get changed due to some initial errors or ongoing processes but older records are rarely updated. However, when the above MERGE gets executed, BigQuery scans all partitions in the base table and processes 161 GB of data. You might add additional join condition on block_timestamp:

  MERGE INTO reporting.base_table T --161 GB processed
USING staging.load_delta S
ON T.block_hash = S.block_hash
 AND T.log_index = S.log_index
 AND T.block_timestamp = S.block_timestamp
WHEN MATCHED THEN UPDATE SET 
  T.data = S.data
WHEN NOT MATCHED THEN INSERT (log_index, data, topics, block_timestamp, block_hash)
VALUES (log_index, data, topics, block_timestamp, block_hash);

But BigQuery would still scan all partitions in the base table because condition T.block_timestamp = S.block_timestamp is a dynamic predicate and BigQuery doesn’t automatically push such predicates down from one table to another in MERGE.

Can you improve the MERGE efficiency by making it scan less data? The answer is Yes. 

As described in the MERGE documentation, pruning conditions may be located in a subquery filter, a merge_condition filter, or a search_condition filter. In this post we show how you can leverage the first two. The main idea is to turn a dynamic predicate into a static predicate.

Steps to enhance your ingestion pipeline

The initial step is to compute the range of partitions that will be updated during the MERGE and store it in a variable. As was mentioned above, in data ingestion pipelines, staging tables are usually small so the cost of the computation is relatively low.

  DECLARE src_range STRUCT<date_min TIMESTAMP, date_max TIMESTAMP> --115 MB processed
DEFAULT(SELECT STRUCT(
  MIN(block_timestamp) AS date_min,  
  MAX(block_timestamp) AS date_max) FROM staging.load_delta);

Based on your existing ETL/ELT pipeline, you can add the above code as-is to your pipeline or you can compute date_min, data_max as part of some already existing transformation step. Alternatively, date_min, data_max can be computed on the Source System side while capturing the next ingestion data batch.

After computing date_min, date_max you pass those values to the MERGE statement as static predicates. There are several ways to enhance the MERGE and prune partitions in the base table based on precomputed date_min, data_max. 

If your initial MERGE statement uses a subquery, you can incorporate a new filter into it:

  BEGIN 
DECLARE src_range STRUCT<date_min TIMESTAMP, date_max TIMESTAMP> --115 MB processed
DEFAULT(SELECT STRUCT(
  MIN(block_timestamp) AS date_min,  
  MAX(block_timestamp) AS date_max) FROM staging.load_delta);

MERGE INTO reporting.base_table T --41 GB processed
USING (
  SELECT *
  FROM staging.load_delta
  WHERE block_timestamp BETWEEN src_range.date_min AND src_range.date_max) S 
ON T.block_hash = S.block_hash
 AND T.log_index = S.log_index
 AND T.block_timestamp = S.block_timestamp
WHEN MATCHED THEN UPDATE SET 
  T.data = S.data
WHEN NOT MATCHED THEN INSERT (log_index, data, topics, block_timestamp, block_hash)
VALUES (log_index, data, topics, block_timestamp, block_hash);
END;

Note that you add the static filter to the staging table and keep T.block_timestamp = S.block_timestamp to convey to BigQuery that it can push that filter to the base table. This MERGE processes 41 GB of data in contrast to the initial 161 GB. You can see in the query plan that BigQuery pushes the partition filter from the staging table to the base table:

Optimizing Big Query 3.jpg

This type of optimization, when a pruning condition is pushed from a subquery to a large partitioned or clustered table, is not unique for MERGE. It also works for other types of queries. For instance:

  SELECT * -- 41 GB processed
FROM reporting.base_table T
INNER JOIN staging.load_delta S
ON T.block_hash = S.block_hash
 AND T.log_index = S.log_index
 AND T.block_timestamp = S.block_timestamp
WHERE S.block_timestamp BETWEEN TIMESTAMP '2020-10-05' AND TIMESTAMP '2020-12-07'

And you can check the query plan to verify that BigQuery pushed down the partition filter from one table to another.

Moreover, for SELECT statements, BigQuery can automatically infer a filter predicate on a join column and push it down from one table to another if your query meets the following criteria:

  • The target table must be clustered or partitioned. 
  • The result size of the other table, i.e. after applying all filters, must qualify for broadcast join. Namly, the result set must be relatively small, less than ~100MB.

In our running example, reporting.base_table is partitioned by block_timestamp. If you define a selective filter on staging.load_delta and join two tables, you can see an inferred filter on the join key pushed to the target table

  SELECT * 
FROM reporting.base_table T
INNER JOIN staging.load_delta S
ON T.block_timestamp = S.block_timestamp
WHERE S.block_hash = '0x0c1caa16b34d94843aabfebc0d5a961db358135988f7498a6fdc450ad55f0870'
Optimizing Big Query 2.jpg

There is no requirement to join tables by partitioning or clustering key to kick off this type of optimization. However, in this case the pruning effect on the target table would be less significant.

But let us get back to the pipeline optimizations. Another way to enhance MERGE is to modify the merge_condition filter by adding static predicate on the base table:

  BEGIN 
DECLARE src_range STRUCT<date_min TIMESTAMP, date_max TIMESTAMP> --115 MB processed
DEFAULT(SELECT STRUCT(
  MIN(block_timestamp) AS date_min,  
  MAX(block_timestamp) AS date_max) FROM staging.load_delta);

MERGE INTO reporting.base_table T --41 GB processed
USING staging.load_delta S
ON T.block_hash = S.block_hash
 AND T.log_index = S.log_index
 AND T.block_timestamp BETWEEN src_range.date_min AND src_range.date_max
WHEN MATCHED THEN UPDATE SET 
  T.data = S.data
WHEN NOT MATCHED THEN INSERT (log_index, data, topics, block_timestamp, block_hash)
VALUES (log_index, data, topics, block_timestamp, block_hash);
END;

To summarize, here are the steps that you can perform to enhance incremental ingestion pipelines in BigQuery. First you compute the range of updated partitions based on the small staging table. Next, you tweak the MERGE statement a bit to let BigQuery know to prune data in the base table.

All the enhanced MERGE statements scanned 41 GB of data, and setting up the src_range variable took 115 MB.  Compare it with the initial 161 GB scan. Moreover, given that computing src_range may be incorporated into some existing transformation in your ETL/ELT, it results in a good performance improvement which you can leverage in your pipelines. 

In this post we described how to enhance data ingestion pipelines by turning dynamic filter predicates into static predicates and letting BiQuery prune data for us. You can find more tips on BigQuery DML tuning here.

Special thanks to Daniel De Leo, who helped with examples and provided valuable feedback on this content.

Case Study

Wunderkind Leverages Google Cloud to Address the Growing Needs of its Customer Base

5346

Of your peers have already read this article.

1:30 Minutes

The most insightful time you'll spend today!

Performance marketing channel, Wunderkind after having issues with legacy database deployed Google Cloud solutions and Cloud Bigtable for flexibility and scalability to meet the growing needs of their data use cases.

Editor’s note: We’re hearing here how martech provider Wunderkind easily met the scaling demands of their growing customer base on multiple use cases with Cloud Bigtable and other Google Cloud data solutions.

Wunderkind is a performance marketing channel and we mostly have two kinds of customers: online retailers, and publishers like Gizmodo Media Group, Reader’s Digest, The New York Post and more. We help  retailers boost their e-commerce revenue through real-time messaging solutions designed for email, SMS, onsite, and advertising. Brands want to provide a one-to-one experience to more of their customers, and we use our extensive history with best practices in email marketing and technology to help brands reach more customers through targeted messaging and personalized shopping experiences. With  publishers, it’s a different value proposition, we use the same platform to provide a non disruptive and personalized ad experience for their website. For example, if you are on their site and then you left, we might show an ad tailored to you when you come back later – depending on the campaign. 

After running into limitations with our legacy database system, we turned to Cloud Bigtable and Google Cloud, which helped us be more flexible and easily scale for high traffic demand – which can be a stable 40,000 requests per second, and meet the needs of our growing number of data use cases. 

Three different databases power our core product

In our core offering, companies send us user events from their websites. We store these events and later decide (using our secret sauce) if and how to reach out to those users on behalf of our customers. Because many of our customers are retailers, Black Friday and Cyber Monday are big traffic days for us as. On such days, we can get 31 billion events, sometimes as many as  200K events per second. We show 1.6 billion impressions that have seen close to 1 billion pageviews. And at the end of all this, we securely send about 100 million emails. We noticed the same thing for election time; traffic reached the same high volume. We need scalable solutions to support this level of traffic as well as the elasticity to let us pay only for what we use, and that’s where Google Cloud comes in.

So how does this work? Our externally facing APIs, which are running on Google Kubernetes Engine, receive those user events—up to hundreds of thousand per second. All the components in our architecture need to be able to handle this demand. So from our APIs, those events go to Pub/Sub, Dataflow and from there they are written to Bigtable and BigQuery, Google Cloud’s serverless, and highly scalable data warehouse. This business user activity data underpins almost all our products. Events can be things like product views or additions to shopping carts. When we store this data in Bigtable, we use a combination of email address and the customer ID as the Bigtable key and we record the event details in that record. 

What do we do with this information next? It’s important to mention that we also mark the last time we received an event about a user in Memorystore for Redis, Google Cloud’s fully managed Redis service. This is important because we have another service that is periodically checking Memorystore for users that have not been active for a campaign-specific period of time (it can be 30 minutes, for example), then deciding whether to reach out to them.

How we decide when we reach out is an intelligent part of our product offering, based on the channel, message, product, etc. When we do reach out, we use Memorystore for Redis as a rate limiter or token bucket. In order not to overwhelm the email or texting providers we send API requests to, we throttle those requests using Memorystore. (We prefer to preemptively throttle the outgoing API requests as opposed to handling errors later.)

When we do reach out, often we will need details for a specific product—let’s say if the website belongs to a retailer. We usually get that information from the retailer through various channels and we store product information in Cloud SQL for MySQL. We pull that information when we need to send an email with product information, and we use Memorystore for Redis to cache that information, since many of the products are repeatedly called. Our Cloud SQL instance has 16 vCPUs, 60GBs of memory and 0.5TB of disk space and when we perform those product information updates, we have about a thousand write transactions per second. We are also in the process of migrating some tables from a self managed MySQL instance, and we keep those tables synchronized with Cloud SQL using Datastream. 

Our user history database was originally stored in AWS DynamoDB, but we were running into problems with how they structured the data, and we’d often get hot shards but with no way to determine how or why. That led to our decision to migrate to Bigtable. We set up the migration first by writing the data to two locations from Pub/Sub, performed some backfill of data until that was up and running, and then started working on the reading. We performed this over a few short months, then switched everything to Bigtable. 

So, as mentioned, we are using Bigtable for multiple databases. The instance that stores our user events has about 30 TB with about 50 nodes.

Profile management

A second use case for Bigtable is for user profile management, where we track, for example, user attributes based on subscription activity, whether they’ve opted in or out of various lists, and where we apply list-specific rules that determine which targeted emails we send out to users. 

Our very own URL shortener

Our third use case for Bigtable is our URL shortener. When our customers build out campaigns and choose a URL, we append tracking information to the query string of the URLs and they become long. Many times, we are sending them via SMS texts, so the URLs need to be short. We originally used an external solution, but made the determination that they couldn’t support our future demands. Our calls tend to be very bursty in nature, and we needed to plan for a future state of supporting higher throughput. We use a separate table in Bigtable for this shortened URL. We generate the short slug that is 62 bit-encoded and use it as the rowkey. We use the long slug as a Protobuf-encoded data structure in one of the row cells and we also have a cell for counting how many times it was used. We use Bigtable’s atomic increment to increase that counter to track how many times the short slug was used. 

When the user receives a text message on their phone, they click the short URL, which goes through to us, and we expand it to the long slug (from Bigtable) and redirect them to the appropriate site location. Obviously, for the URL shortener use case, we need to make the conversion very quickly. Bigtable’s low latency helps us meet that demand and we can scale it up to meet higher throughput demands.

Meeting the future with Google Cloud

Our business has grown considerably, and as we keep signing up new clients, we need to scale up accordingly, and Bigtable has met our scaling demands easily. With Bigtable and other Google Cloud products powering our data architecture, we’ve met the demand of incredibly high traffic days in the last year, including Black Friday and Cyber Monday. Traffic for these events went much higher than expected, and Bigtable was there, helping us easily scale on demand. 

We are working on leveraging a more cloud native approach and using Google Cloud managed services like GKE, Dataflow, pub/sub, Cloud SQL , Memorystore, BigQuery and more. Google has those 1st party products and we don’t see the value in rolling out or self managing such solutions ourselves..

Thanks to Google Cloud, we now have reliable and flexible data solutions that will help us meet the needs of our growing customer base, and delight their users with fast, responsive, personalized shopping messaging and experiences. 

Learn more about Wunderkind and Cloud Bigtable. Or check out our recent blog exploring the differences between Bigtable and BigQuery.

Blog

Two Ways to Deploy SAP HANA System on Google Cloud

7899

Of your peers have already read this article.

4:00 Minutes

The most insightful time you'll spend today!

You can expand the benefits of SAP by migrating SAP S/4 HANA deployments to Google Cloud. But, did you know there are two different ways that includes a set of pros and cons for rehosting SAP HANA database on Google Cloud? Read more!

Many of the world’s leading companies run on SAP—and deploying it on Google Cloud extends the benefits of SAP even further. Migrating your current SAP S/4HANA deployment to Google Cloud—whether it resides on your company’s on-premises servers or another cloud service—provides your organization with a flexible virtualized architecture that lets you scale your environment to match your workloads, so you pay only for the compute and storage capacity you need at any given moment. Google Cloud includes built-in features, such as Compute Engine live migration and automatic restart, that minimize downtime for infrastructure maintenance. And it allows you to integrate your SAP data with multiple data sources and process it using Google Cloud technology such as BigQuery to drive data analytics.

SAP server-side architecture consists of two layers: the SAP HANA database, and the Netweaver application layer. In this blog post, we’ll look at the options and steps for moving the database layer to Google Cloud as a lift and shift or rehost, a straightforward approach that entails moving your current SAP environment unchanged onto Google Cloud.

Deploying an SAP HANA system on Google Cloud

Google Cloud offers SAP-certified virtual machines (VMs) optimized for SAP products, including SAP HANA and SAP HANA Enterprise Cloud, as well as dedicated servers for SAP HANA for environments greater than 12TB. (For a complete list of VM and hardware options, visit the Certified and Supported SAP HANA Hardware Directory.)

Before proceeding with a rehost migration to Google Cloud, your current (source) environment and Google Cloud (target) environments should meet these specifications:

Prerequisites:  

  • The configuration of the Google Cloud environment (i.e., VM  resources, SSD storage capacity) should be identical to that of the source environment. If the underlying hardware is different, however, you must use Option 2 for your migration, detailed below.
  • Both environments should be running the same operating system (SUSE or RHEL Linux).
  • The HANA version, instance number, and system ID (SID) should be identical.
  • Schema names must remain the same.
  • Establishing the network connection between the on-premises environment and Google Cloud will be required in this phase to support rehost of the SAP application.you can use Cloud VPN or Dedicated Interconnect. Learn more about Dedicated Interconnect and Cloud VPN.

Note: Depending on your internet connection and bandwidth requirements, we recommend using a Dedicated Interconnect over Cloud VPN for production environments. 

We offer a number of automated processes to accelerate your cloud journey. To deploy the SAP HANA system on Google Cloud, you can use the Google Cloud Deployment manager or Terraform and Ansible scripts available on GitHub with configuration file templates to define your installation. For more details, see the Google Cloud SAP HANA Planning Guide.

Note: To deploy SAP HANA on Google Cloud machine types that are certified by SAP for production, please review the Certification for SAP HANA on Google Cloud page. 

Moving an SAP HANA Database to Google Cloud

There are two different options you can use to rehost your SAP HANA database to Google Cloud, and each has pros and cons that you should consider when deciding on your approach.

Option 1: Asynchronous replication uses SAP’s built-in replication tool to provide continuous data replication from the source system (also known as the primary system) to the destination or secondary system—in this case residing on Google Cloud. It’s best for mission-critical applications for which minimum downtime is a high priority, and for large databases. In addition, the high level of automation means that the process requires less manual intervention. Here’s where you can learn more on HANA Asynchronous Replication.

Option 2: Backup and restore relies on SAP’s backup utility to create an image of the database that is then transferred to Google Cloud, where it is restored in the new environment. Downtime for this method varies by database size, so large databases may require more downtime via this method vs. asynchronous replication. It also involves more manual tasks. However, it requires fewer resources to perform, making it an attractive option for less urgent use cases. Here’s where you can learn more on SAP HANA database Backup and restore.

Migration options Pros and cons.jpg
Click to enlarge

How to migrate the SAP HANA database to Google Cloud using Asynchronous Replication

1 Asynchronous Replication.jpg
Click to enlarge
  1. Create and configure Dedicated Interconnect or Cloud VPN between the current environment and Google Cloud.
  2. Set up SAP HANA asynchronous replication. You can configure system replication using SAP HANA Cockpit, SAP HANA Studio, or hdbnsutil. See Setting Up SAP HANA System Replication in the SAP HANA Administration Guide.
  3. Be sure to use the same instance number and HANA SID in the template as the primary instance.
  4. Configure the Google Cloud instance as the secondary node for using HANA Asynchronous replication.
  5. Perform data validation once full data replication is completed to the SAP HANA database in Google Cloud. To learn more: HANA System Replication overview.  
  6. Perform an SAP HANA takeover on your standby database. This switches your active system from the current primary system onto the secondary system on Google Cloud. Once the takeover command runs, the system on Google Cloud becomes the new primary system.To learn more: HANA Takeover. 

How to migrate the SAP HANA database to Google Cloud using Backup and Restore

2 Backup and Restore.jpg
Click to enlarge
  1. Create a full backup of your SAP HANA database in your current environment.
  2. Create a new storage bucket in your Google Cloud environment. Visit Creating Storage Buckets in the Google Cloud Storage documentation. 
  3. Download and install gsutil onto the source environment and run it to upload the HANA backup to the Google Cloud storage bucket. To install gsutil utility on any computer or server, visit Install gsutil in the Google Cloud Storage documentation.
    Note: You can run parallel multi thread/multi processing in gsutil to copy large files more quickly.
  4. Recover the HANA database on Google Cloud using SAP’s RECOVER DATABASE statement. See RECOVER DATABASE Statement (Backup and Recovery) in the SAP HANA SQL Reference Guide for SAP HANA Platform.

Note: BackInt agent is an integrated SAP interface tool used for HANA database on Google Cloud.Backint agent for SAP HANA can be used to store and retrieve backups directly from Google Cloud Storage. It is supported and certified by SAP on Google Cloud. To learn more:  SAP HANA Backint Agent on Google Cloud. 

In summary, we recommend using Asynchronous Replication (Option 1) for mission-critical applications that require the lowest downtime window. For all other applications, we recommend Backup and Restore (Option 2), as this approach requires fewer resources. It’s also a great way to implement the backup and restore functionality on Google Cloud.

A rehost migration is the most straightforward path to getting your SAP on HANA system up and running on Google Cloud. And the sooner you migrate, the sooner you can take advantage of the many benefits Google Cloud brings to your SAP solution. For more information on the different migration options please review: SAP on Google Cloud: Migration strategies. 

Learn more about deploying SAP on Google Cloud. Technical resources can be found here.

Blog

Bigtable’s High Performance and Low Opex is Great for Building Personalization

3751

Of your peers have already read this article.

3:00 Minutes

The most insightful time you'll spend today!

With Google Cloud's Bigtable, businesses can build solutions with strong personalization features cost-effectively and enhance customer experience.

Customer expectations have shifted as a result of evolving needs. Across industries, customers expect that you treat them as individuals, and demonstrate how well you understand and serve their unique needs. This concept—personalization—is the idea that you’re delivering a tailored experience to each customer corresponding to their needs and preferences; you’re setting up a process to create individualized interactions that improves the customer’s experience. According to Salesforce, 84% of consumers say being treated like a person, not a number, is very important to winning their business. 

Entire industries are undergoing digital transformation to better serve their customers through personalized experiences. For example, retailers are improving engagement and conversion with personalized content, offers, and product recommendations. Advertising technology organizations are increasing the relevance and effectiveness of their ads using customer insights like their specific interests, purchasing intent, and buying behavior. Digital music services are helping their customers discover and enjoy new music, playlists, and podcasts based on their listening behavior and interests. 

As customers want more and more personalization, modern technology is making it possible for many more businesses to achieve this. In this post, we’ll look at some common challenges to implementing personalization capabilities and how to solve them with transformative database technologies like Google Cloud’s Bigtable. Bigtable powers core Google services such as Google Maps that supports more than a billion users, and its petabyte scale, high availability, high throughput, and price-performance advantages help you deliver personalization at scale.  

Challenges with personalization 

Data is at the heart of personalization. To deliver personalization at scale, an application needs to store, manage, and access large volumes of data (a combination of customer-specific data and anonymized aggregate data across customers) to develop a deep understanding of the behavior, needs, and preferences of each customer. Your database needs to very quickly write large volumes of data concurrently for all active customers. You need to continuously capture data on customer behavior because each step potentially informs the next, e.g., adding an item to a shopping cart can be used to trigger new recommendations for related or complementary products. Much of this data needed for personalization is semi-structured and sparse, and therefore requires a database with a flexible data model. 

Personalization at scale requires large volumes of data to be read in near real-time so that it can be in the critical serving path to deliver a seamless user experience, often with a total application latency of less than 100ms. This means your requests to the database need to return results with latencies of single-digit milliseconds. You need to ensure that application latencies do not degrade as you onboard more customers. Data needs to be organized efficiently and integrated with other tools so that you can run deep analytical queries and use machine learning (ML) models to develop personalized recommendations, and store the aggregates in your operational database for serving your customers. You also need the ability to run large batch reads for analytics without affecting the serving performance of your application. 

In addition, you need to ensure that your database costs do not explode with the popularity of your application. Your database needs to consistently deliver low total cost of ownership (TCO), and high price-performance as your data volumes and throughput needs grow. Your database needs to scale seamlessly and linearly to deliver consistent, predictable performance to all users around the world. Additionally, your database needs to be easy to manage, so that you can focus on your application instead of managing the complexity of your database.

Why a NoSQL database is the right fit for personalization 

Every database reflects a set of engineering tradeoffs. When relational databases were designed 40 years ago, storage, compute, and memory were thousands of times more expensive than they are today. Databases were deployed on a single server to a relatively small number of concurrent users, whose access to the systems tended to be during normal business hours when users had network access. Relational databases were designed with these resources, costs, and use in mind. They work very hard to be storage and memory efficient, and assume a single server for deployments. 

As the costs of storage, memory, and compute decreased, and as data and workloads grew to exceed the capacity of commodity hardware, engineers began to reconsider these tradeoffs with different goals in mind. New types of databases later emerged that assumed distributed architectures so they could be easier to scale, especially with cloud infrastructure. With this approach the tradeoff in turn was to forego the sophistication of SQL and much of the data integrity and transactional capabilities developed in relational systems. These systems are commonly called NoSQL databases.

Traditional relational databases assume a fixed schema that will change infrequently over time. While this predictability of data structure allows for many optimizations, it also makes it difficult and cumbersome to add new and varying data elements in your application. NoSQL databases, such as key value stores and document databases, relax the rigidity of the schema and allow for data structures to evolve much more easily over time. Flexible data models speed the pace of innovation in your application, and increase your ability to iterate on your ML models, which is essential for personalization. In addition, the scalability of systems like Cloud Bigtable allow you to deliver personalization to millions of concurrent users while you continue to evolve how you personalize experiences for your customers.

How Cloud Bigtable enables personalization at scale

Cloud Bigtable supports personalization at scale with its ability to handle millions of requests per second, cost-effectively store petabytes of data1, and deliver consistent single-digit millisecond latencies for reads and writes. Bigtable delivers a unique mix of high performance and low operating cost to reduce your TCO. 

We’ve heard from Spotify, Segment, and Algolia about how they’ve built personalized experiences for their customers with Bigtable. Check out this presentation to hear Peter Sobot of Spotify describe how they use Bigtable for personalization. 

Let’s imagine a scenario where your application takes off like a rocketship, and grows to 250 million users. Let’s assume a peak 1.75 million concurrent users of your application2, with each user sending two requests per minute to your database. This will drive 3.5 million requests per minute to your database, or approximately 58.3K requests per second. Pricing for Bigtable to run this workload will start at under $400 per day3.

Bigtable scales throughput linearly with additional nodes. With separation of compute and storage, Bigtable automatically configures throughput by adjusting the association of nodes and data to provide consistent performance. When a node is experiencing heavy load, Bigtable automatically moves some of the traffic to a node with lower load to improve the overall performance. Bigtable also supports cross-region replication, with local writes in each region. This allows you to manage your data near your customers’ geographic locations, reducing network latency and bringing predictable, low-latency reads and writes to your customers in different regions around the world. 

Bigtable is a NoSQL database developed and operated by Google Cloud. Bigtable provides a column family data model that allows you to flexibly store varying data elements for customers associated with their behavior and preferences, store a very large number of such data elements across your customers, and quickly iterate on your application. Bigtable supports trillions of rows with millions of columns. Each row in Bigtable supports up to 256 MB of data, so that you can easily store all personalized data for a customer in a single row. Bigtable tables are sparse, and there is no storage penalty for a column that is not used in a row; you only pay for the columns that store values.

BigQuery ML allows you to create and run ML models directly in BigQuery to develop personalization recommendations that you can bring back to Bigtable. You can easily pipe Bigtable data into BigQuery to run deep analytical queries and develop recommendations. These aggregates, like computed recommendations, are brought back to Bigtable so your application can serve those recommendations to users with low latency and massive scale. 

Bigtable integrates with the Apache Beam ecosystem and Dataflow to make it easier for you to process and analyze your data. With application profiles and replication in Bigtable, you can isolate your workloads so that batch reads do not slow down your serving workload that has a mix of reads and writes. This enables your application to perform near real-time reads at scale to develop and train machine learning models in TensorFlow for personalization. Bigtable gives you the right operational data platform to develop personalization recommendations offline or in real-time, and serve them to your customers.

regional setups.jpg
Click to enlarge

Here’s a look at conceptual schema examples for personalization in ecommerce:

conceptual schema examples.jpg
Click to enlarge

And here’s a quick overview of what personalization use cases require, and how Bigtable addresses them.

what personalization use cases require.jpg
Click to enlarge

Bigtable is fully managed to free you from the complexity of managing your database, so that you can focus on delivering a deeply personalized experience to your customers. Learn more about Bigtable.

1. Storage pricing (HDD) starts at $0.026 per GB/mo (us-central1)
2. Assumes application is used 24 hours a day, average user session is 5 minutes (Android app average), and daily peak is 2x the average. (250 million / (24 hours / 5 minutes) *2 = 1,736,111 peak concurrent users (us-central1 region)
3. Cloud Bigtable pricing for us-central1 region. Assumes 25 TB SSD storage (100 KB per user, for 250 million users) per month, 10 compute nodes per month (with no replication), includes data backup. Bigtable pricing details.

Blog

Using Google Cloud’s Backup and DR Service with Logging and Monitoring Tools

1108

Of your peers have already read this article.

2:30 Minutes

The most insightful time you'll spend today!

Learn how Google Cloud's Backup and DR service integrates with logging and monitoring tools, allowing for proactive issue identification, alerts, and efficient troubleshooting to ensure data protection and business continuity.

Backup and DR data is a valuable business asset, and ensuring that it’s safe and accessible is essential. In particular, you want to be able to monitor your backups to ensure that the data is indeed protected and that you can quickly recover it in the event of a disaster, user error, or failed upgrade.

Users of Google Cloud’s Backup and DR service often ask:

  1. What are the best practices for monitoring my backup and recovery jobs?
  2. How can I proactively identify issues and troubleshoot? 
  3. How can I create alerts and be notified about important events? 

The good news is that Google Cloud Backup and DR now integrates with Cloud Logging and Cloud Monitoring tools. Now, you can monitor backup events, jobs, appliance health, resource consumption and user actions in the same way you monitor other Google Cloud workloads and services. 

With this integration, you can now:

  • Monitor events related to backup and restore – With detailed Google Cloud Backup and DR service event logs in Cloud Logging, you can now use log based alerts to create a customisable notification, in near-real time. This lets you monitor important events such as  backup job failures, jobs that did not run, local backup storage saturation, and network failures.
  • Configure fine grained alerting – You can write custom event queries on a wide variety of dimensions such as event severity, application type, application name, job type, job name, error message and many more. This gives a lot of flexibility to define alerts for specific events or conditions within a system, thus reducing noise and helping you identify and fix issues easily. 
  • Get notified on your preferred notification channel – Google Cloud offers seven pre-built notification channels that let you receive customisable notifications directly over email, SMS, Slack or the Google Cloud mobile app. Alternatively, you can integrate with your own monitoring and event management tools using webhooks or Pub/Sub. 
  • Derive useful insights to troubleshoot issues – With Log Analytics, you can use BigQuery to query your data using SQL queries and generate operational insights, which can help you reduce time spent troubleshooting. For example, you can analyze the key reasons that backup and restore jobs fail for a given application or application type. 

The integration also lets you proactively discover deeper issues related to backup and recovery, such as capacity-related issues or certain job failures that happen periodically, by monitoring recurring events in the logs over time. With log-based metrics, you can: 

  • Create trend charts to track important metrics such as the number of backup or restore jobs that failed during a day/week/month 
  • Receive a notification when the number of occurrences crosses a threshold, for example receive a notification if a snapshot pool saturates more than five times a week 
  • Monitor trends in data, such as latency values in logs, and receive a notification if the values change in an unacceptable way.

Getting started

The time to learn that your backup failed should never be when you go to restore. By integrating our Backup and DR service with Cloud Monitoring and Cloud Logging, you can get valuable assurances about the health of your backup and business continuity processes with the same tools that you use to manage other Google Cloud workloads. To get started, check out the Backup and DR event logs page. You can also watch a video that shows you how to set up and use the service and configure some custom alerts.

Blog

Qlik and Google Cloud Combo: Extending Integration of SAP Data on BigQuery

5477

Of your peers have already read this article.

2:00 Minutes

The most insightful time you'll spend today!

Data integration challenges plague almost 52% of companies with SAP workloads and systems. Qlik Data integration for Google Cloud helps customers accelerate the delivery of SAP data on BigQuery.

If your organization is one of the 52% of SAP customers whose top analytics pain point is data integration1, Google Cloud has got you covered. By working with partners like Qlik, we are expanding our integration options and bringing real-time replication capability for SAP to BigQuery.

Integrated data for accelerated insights

BigQuery—our fully managed, enterprise data warehouse that can scale up to petabytes on demand and execute queries in seconds—allows SAP customers to consolidate enterprise data silos and confidently derive more use and value from their data.  Customers can accelerate and simplify the delivery of SAP data on BigQuery with the latest Qlik Data Integration platform offering for Google Cloud which allows data integration using an automated, near real-time data pipeline through Qlik Replicate, whether data originates from legacy SAP environments, SAP HANA, or SAP application servers. Additionally, Qlik Compose for Data Warehouses can be used to easily generate and automate logical data models from SAP directly in BigQuery freeing up more time for data analysts to leverage advanced built-in capabilities such as BigQuery ML to derive greater value and insights using standard SQL without the need for advanced programming expertise.

Delivering faster results with real-time 

Traditional extract-transform-load (ETL) solutions operate on a batch basis, pulling data sets from SAP daily, hourly, or every minute. These tools often require manual mapping of multiple data fields so that data flows accurately. Given SAP’s highly complex table relationships, in which a single transaction can result in multiple changes, this can be a time consuming and tedious process. ETL can also increase the burden on your SAP systems.

Qlik Replicate simplifies this with its intuitive user interface where you can set up real-time data replication between SAP and BigQuery, eliminating the need for manual coding. And, to prevent system overhead, as soon as a new transaction is entered into SAP, the resulting data is replicated into BigQuery in a process known as change data capture (CDC) from SAP’s log layer. This means that data transfer can benefit from high performance with minimal impact on the source system’s resources. Read our latest white paper to learn how to extract SAP data into BigQuery leveraging Qlik Replicate. 

Solution expertise for all core SAP workloads

It doesn’t matter what database your SAP system runs on, Qlik Replicate supports all core SAP systems. It automates real-time data replication and decodes SAP’s complex, application-specific data structures into formats that flow smoothly into BigQuery. This ensures that anyone who depends on data analytics has the most current and relevant SAP data they need. For its robust solution capabilities Qlik has received a new “SAP on Google Cloud Expertise” designation for supporting:

  • Fast onboarding and accelerated replication of SAP data into Google Cloud
  • Real-time and continuous data replication from SAP applications to BigQuery
  • Support for all core SAP modules and a broad set of data sources
  • Automated data integration, which cuts resource requirements for initial delivery and ongoing maintenance

Proven customer results

Many SAP customers have experienced the benefits of leveraging Qlik alongside BigQuery for data analytics and AI at scale, including German luxury department store chain Breuninger. In order to meet its customers’ growing and changing expectations,Breuninger needed to accelerate its time to insight from data sources across a highly dispersed landscape of on-premises databases and systems, including SAP. The company uses Qlik Replicate to feed corporate data from modules in its SAP system into BigQuery and integrate its varying on-premises databases with the Google Cloud environment. This has yielded game-changing, real-time customer insights for the retailer.

What could your business do with faster, more integrated insights? Learn more about BigQuery for SAP customers and also how Qlik and Google Cloud can help you modernize and automate data integration and analytics.

More Relevant Stories for Your Company

Blog

Incorporating Custom Holidays into Your Time-Series Models with BigQuery ML

About three years ago, JCB, one of the biggest Japanese payment companies, launched a project to develop new high-value services with agility. We set up a policy of starting small from scratch without using the existing system, which we call the concept of “Dejima”, where we focused on improving various aspects

“In just a few clicks and in a matter of minutes, users can easily spin up a database instance and manage routine administrative tasks.” Aastha Bhardwaj, Software Engineer, Google Cloud

How-to

How BigQuery’s Unique Features Support Your Data at Petabyte-scale

Organizations rely on data warehouses to aggregate data from disparate sources, process it, and make it available for data analysis in support of strategic decision-making. BigQuery is the Google Cloud enterprise data warehouse designed to help organizations to run large scale analytics with ease and quickly unlock actionable insights. You

With BigQuery, we’ve been able to reduce our total cost per query by 5x.

Blog

Neo4J & Google Cloud: Graph Data in Cloud to Address Challenges in FinServ Industry

Over the last decade, financial service organizations have been adopting a cloud-first mindset. According to InformationWeek, lower costs and enhanced scalability were the biggest drivers for cloud adoption in financial services, and cloud-native applications allow access to the latest technology and talent, enabling adopters to rebuild transaction processing systems capable of

Case Study

Groww’s Google Cloud-Powered Platform: The Key to Secure and Successful Investing

Groww makes investments simple and accessible, using Google Kubernetes Engine to ensure a reliable platform for customers, and makes data-backed decisions to grow its business with BigQuery. Google Cloud results Reduces hardware costs with Preemptable Virtual Machines Enables a lean DevOps team with Google Cloud Analyzes data effectively and quickly

SHOW MORE STORIES

We use cookies to improve your experience. You accept to receive communications from GetIT Comms.   |   Cookie Policy  

Got It