How to Move From Redshift to BigQuery Easily - Build What's Next

3695

Of your peers have already watched this video.

61:30 Minutes

The most insightful time you'll spend today!

How-to

How to Move From Redshift to BigQuery Easily

Enterprise data warehouses are getting more expensive to maintain. Traditional data warehouses are hard to scale and often involve lots of data silos. Business teams need data insights quickly, but technology teams have to grapple with managing and providing that data using old tools that aren’t keeping up with demand. Increasingly, enterprises are migrating their data warehouses to the cloud to take advantage of the speed, scalability, and access to advanced analytics it offers. 

With this in mind, we introduced the BigQuery Data Transfer Service to automate data movement to BigQuery, so you can lay the foundation for a cloud data warehouse without writing a single line of code. Earlier this year, we added the capability to move data and schema from Teradata and S3 to BigQuery via the BigQuery Data Transfer Service. To help you take advantage of the scalability of BigQuery, we’ve now added a service to transfer data from Amazon Redshift, in beta, to that list. 

Data and schema migration from Redshift to BigQuery is provided by a combination of the BigQuery Data Transfer Service and a special migration agent running on Google Kubernetes Engine (GKE), and can be performed via UI, CLI or API. In the UI, Redshift to BigQuery migration can be initiated from BigQuery Data Transfer Service by choosing Redshift as a source. 

The migration process has three steps: 

  1. UNLOAD from Redshift to S3—The GKE agent initiates an UNLOAD operation from Redshift to S3. The agent extracts Redshift data as a compressed file, which helps customers minimize the egress costs. 
  2. Transfer from S3 to Cloud Storage—The agent then moves data from Amazon S3 to a Cloud Storage bucket using Cloud Storage Transfer Service. 
  3. Load from Cloud Storage to BigQuery—Cloud Storage data is loaded into BigQuery (up to 10 million files).
GCP bigquery.png
The BigQuery Data Transfer Service, showing Redshift as a source.

Watch the video to learn more!

4572

Of your peers have already watched this video.

32:30 Minutes

The most insightful time you'll spend today!

How-to

Technical deep dive on Looker: The enterprise BI solution for Google Cloud

Thousands of users accessing petabytes of data on a daily basis: This is a challenging proposition for enterprises, without a doubt.

But Looker makes it possible. Beyond just accessing data though, Looker’s platform transforms your company’s relationship with data.

Go under the hood of Looker, with Olivia Morgan, Enterprise CE, Looker, to see how LookML empowers developers to take advantage of powerful data warehouses like BigQuery and ultimately enhance the workflows of end users.

She also takes a deep dive into LookML’s ability to use Google Cloud Functions and Search API to enhance dashboards, leverage BQML within Looker’s modeling layer to give users access to forecasts, tie in BigQuery’s public datasets to add richness to analysis, and show off LookML’s ability to handle nested tables for faster performance on transaction analysis all through a complete end to end demo.

Case Study

What Swiggy and You Can Learn From This Company’s Use of ML to Engage Customers

9662

Of your peers have already read this article.

1:45 Minutes

The most insightful time you'll spend today!

Just Eat, which is similar to Swiggy, uses Google Cloud's machine learning to power sophisticated consumer recommendations on both its app and website. It enables them to create an “Adventurous Index”, for instance, something we haven't seen in Indian ordering apps.

The app economy has enabled a huge range of unique business models to flourish. One such model is online food ordering and delivery services, in which apps leverage geo-location data to aggregate local food choices and offer personalized options to consumers.

A leading company in this space is Just Eat. Launched in the UK in 2001 with a vision of ‘serving the world’s greatest menu. Brilliantly.’ The company has capitalized on the popularity of online food delivery and grown its presence across 12 markets. 

Just Eat acts as an intermediary between take-out food outlets and hungry customers, giving local restaurants access to a broader base of potential diners, while providing consumers with an easy and secure way to order and pay for food from their favourite restaurants.

Today the company helps 27 million customers find food from more than 112,000 restaurants—everything from homemade Italian pasta, to Chinese noodle bowls, to fish-and-chips. 

Data is the fuel of Just Eat’s rapid growth, but it wasn’t always looked at that way. In its early days, Just Eat struggled with the deluge of information and faced fragmentation across its systems. In fact, the company realized its legacy data vendor wasn’t capable of ingesting 90 percent of the data produced by its food platform. This was incredibly frustrating for Just Eat’s analysts and data scientists, who had to waste time cleaning up sources instead of leveraging the data to create a better user experience. 

Just Eat turned to Google Cloud, and now uses machine learning (ML) to power sophisticated consumer recommendations on both its app and website. It also makes heavy use of features offered by Google Cloud Platform, including BigQuery for running analytics on its customer data set and Cloud Pub/Sub for messaging app users with relevant offers in real-time. 

Having all of Just Eat’s data in one platform has translated into real value for its customers. With Google Cloud tools, Just Eat has created its own proprietary Customer Ontology framework, which today contains 5.5 billion features that better understand consumers’ behavior and food habits, and provides insights into previous visits.

Just Eat recently created an “Adventurous Index” to map its customers according to their ordering habits, enabling them to tailor their marketing and user experiences. For example, mid-adventurous customers are shown a choice of restaurants that serve their most ordered cuisine, while adventurous customers can choose from restaurants that serve a wider variety. This not only has prompted consumers to be more adventurous with their choices, but also has led to more business at a more diverse set of restaurants.

Matt Cresswell, Director of Customer Platforms at Just Eat said that Google Cloud has become integral to its product delivery: “Consumer food choice is a hugely nuanced topic. We know that individuals have their own unique journeys when they use Just Eat. We’ve sought to create a truly one-to-one relationship with every customer. The changes we’ve made to the platform mean they can access the dishes they enjoy at the touch of a fingertip, and find inspiration to discover new dishes they’ll love. We’re grateful to Google Cloud for helping us support our customers on their culinary explorations.”

Case Study

Google Cloud Helped Digitec Galaxus Personalize Over 2 Million Newsletters in a Week

8307

Of your peers have already read this article.

3:00 Minutes

The most insightful time you'll spend today!

Swiss consumer electronics and media products brand Digitec Galaxus and Google Cloud built many recommendation systems to offer personalised experience and content. Read to learn how the brand personalised over 2 million newsletters/week.

Digitec Galaxus AG is the biggest online retailer in Switzerland, operating two online stores: Digitec, Switzerland’s online market leader for consumer electronics and media products, and Galaxus, the largest Swiss online shop with a steadily growing range of consistently low-priced products for almost all daily needs. 

Known for its efficient, personalized shopping experiences, it’s clear that Digitec Galaxus understands what it takes to deliver a platform that is interesting and relevant to customers every time they shop. 

The problem: Personalizing decisions for every situation

Digitec Galaxus already had established an engine to help them personalize experiences for shoppers when they reached out to Google Cloud. They had multiple recommendation systems in place and were also extensive early adopters of Recommendations AI, which already enabled them to offer personalized content in places like their homepages, product detail pages, and their newsletter. 

But those same systems sometimes made it difficult to understand how best to combine and optimize to create the most personalized experiences for their shoppers. Their requirements were threefold:

  1. Personalization: They have over 12 recommenders they can display on the app, however they would like to contextualize this and choose different recommenders (which in turn select the items) for different users. Furthermore they would like to exploit existing trends as well as experiment with new ones.
  2. Latency: They would like to ensure that the solution is architected so that the ranked list of recommenders can be retrieved with sub 50 ms latency.
  3. End-to-end easy to maintain & generalizable/modular architecture: Digitec wanted the solution to be architected using an easy to maintain, open source stack, complete with all MLops capabilities required to train and use contextual bandits models. It was also important to them that it is built in a modular fashion such that it can be adapted easily to other use cases which have in mind such as recommendations on the homepage, Smartags and more . 

To improve, they asked us to help them implement a machine learning (ML) contextual bandit based recommender system on Google Cloud taking all the above factors into consideration to take their personalization to the next level. 

Contextual bandits algorithms are a simplified form of reinforcement learning and help aid real-world decision making by factoring in additional information about the visitor (context) to help learn what is most engaging for each individual. They also excel at exploiting trends which work well, as well as exploring new untested trends which can yield potentially even better results. For instance, imagine that you are personalizing a homepage image where you could show a comfy living room couch or pet supplies. 

Without a contextual bandit algorithm, one of these images would be shown to someone at random without considering information you may have observed about them during previous visits. Contextual bandits enable businesses to consider outside context, such as previously visited pages or other purchases, and then observe the final outcome (a click on the image) to help determine what works best. 

Creating a personalization system with contextual bandits

While Digitec Galaxus heavily personalizes their website homepages, they are very very sensitive and also require more cross-team collaboration to update and make changes. 

Together with the Digitec Galaxus team, we decided to narrow the scope and focus on building a contextual bandit personalization system for the newsletter first. The digitec Galaxus team has complete control over newsletter decisions and testing various ML experiments on a newsletter would have less chance of adverse revenue impact than a website homepage. 

The main goal was to architect a system that could be easily ported over to the homepage and other services offered by Digitec with minimal adaptations. It would also need to satisfy the functional and non-functional requirements of the homepage as well as other internal use cases.

Below is a diagram of how the newsletter’s personalization recommendation system works:

Digitec-01.jpg
Click to enlarge
  • The system is given some context features about the newsletter subscriber such as their purchase history and demographics. Features are sometimes referred to as variables or attributes, and can vary widely depending on what data is being analyzed. 
  • The contextual bandit model trains recommendations using those context features and 12 available recommenders (potential actions). 
  • The model then calculates which action is most likely to enhance the chance of reward (a user clicking in the newsletter) and also minimize the problem (an unsubscribe). 

Calculating whether a click was a newsletter or an unsubscribe enabled the system to optimize for increasing clicks and avoid showing non-relevant content to the user (click-bait). This enabled Digitec Galaxus to exploit popular trends while also exploring potentially better-performing trends. 

How Google Cloud helps

The newsletter context-driven personalization system was built on Google Cloud architecture using the ML recommendation training and prediction solutions available within our ecosystem. 

Below is a diagram of the high-level architecture used:

The architecture covers three phases of generating context-driven ML predictions, including: 

ML Development: Designing and building the ML models and pipeline 
Vertex Notebooks are used as data science environments for experimentation and prototyping. Notebooks are also used to implement model training, scoring components, and pipelines. The source code is version controlled in Github. A continuous integration (CI) pipeline is set up to automatically run unit tests, build pipeline components, and store the container images to Cloud Container Registry. 

ML Training: Large-scale training and storing of ML models 
The training pipeline is executed on Vertex Pipelines. In essence, the pipeline trains the model using new training data extracted from BigQuery and produces a trained, validated contextual bandit model stored in the model registry. In our system, the model registry is a curated Cloud Storage

The training pipeline uses Dataflow for large scale data extraction, validation, processing, and model evaluation, and Vertex Training for large-scale distributed training of the model. AI Platform Pipelines also stores artifacts, the output of training models, produced by the various pipeline steps to Cloud Storage. Information about these artifacts are then stored in an ML metadata database in Cloud SQL. To learn more about how to build a Continuous Training Pipeline, read the documentation guide.

ML Serving: Deploying new algorithms and experiments in production 
The training pipeline uses batch prediction to generate many predictions at once using AI Platform Pipelines, allowing Digitec Galaxus to score large data sets. Once the predictions are produced, they are stored in Cloud Datastore for consumption. The pipeline uses the most recent contextual bandit model in the model registry to evaluate the inference dataset in BigQuery and give a ranked list of the best newsletters for each user, and persist it in Datastore. A Cloud Function is provided as a REST/HTTP endpoint to retrieve the precomputed predictions from Datastore.

All components of the code and architecture are modular and easy to use, which means they can be adapted and tweaked to several other use cases within the company as well.

Better newsletter predictions for millions

The newsletter prediction system was first deployed in production in February, and Digitec Galaxus has been using it to personalize over 2 million newsletters a week for subscribers. The results have been impressive, 50% higher than our baseline. However, the collaboration is still ongoing to improve the results even more. 

“Working at this level in direct exchange with Google’s machine learning experts is a unique opportunity for us. The use of contextual bandits in the targeting of our recommendations enables us to pursue completely new approaches in personalization by also personalizing the delivery of the respective recommender to the user. We have already achieved good results in our newsletter in initial experiments and are now working on extending the approach to the entire newsletter by including more contextual data about the bandits arms. Furthermore, as a next step, we intend to apply the system to our online store as well, in order to provide our users with an even more personalized experience. To build this scalable solution, we are using Google’s open source tools such as TFX and TF Agents, as well as Google Cloud Services such as Compute Engine, Cloud Machine Learning Engine, Kubernetes Engine and Cloud Dataflow.”—Christian Sager, Product Owner, Personalization ( Digitec Galaxus)

Since the existing architecture and system is also dynamic, it will automatically adapt to new behaviours, trends, and users. As a result, Digitec Galaxus plans to re-use the same components and extend the existing system to help them improve the personalization of their homepage and other current use cases they have within the company. Beyond clicks and user engagement, the system’s flexibility also allows for future optimization of other criteria. It’s a very exciting time and we can’t wait to see what they build next!

3052

Of your peers have already listened to this podcast

25:30 Minutes

The most insightful time you'll spend today!

Podcast

Setting up Data Pipelines Easily for Streaming and Non-Streaming Data

Enterprise data warehouses are getting more expensive to maintain. Traditional data warehouses are hard to scale and often involve lots of data silos. Business teams need data insights quickly, but technology teams have to grapple with managing and providing that data using old tools that aren’t keeping up with demand. Increasingly, enterprises are migrating their data warehouses to the cloud to take advantage of the speed, scalability, and access to advanced analytics it offers. 

With this in mind, we introduced the BigQuery Data Transfer Service to automate data movement to BigQuery, so you can lay the foundation for a cloud data warehouse without writing a single line of code. Earlier this year, we added the capability to move data and schema from Teradata and S3 to BigQuery via the BigQuery Data Transfer Service. To help you take advantage of the scalability of BigQuery, we’ve now added a service to transfer data from Amazon Redshift, in beta, to that list. 

Data and schema migration from Redshift to BigQuery is provided by a combination of the BigQuery Data Transfer Service and a special migration agent running on Google Kubernetes Engine (GKE), and can be performed via UI, CLI or API. In the UI, Redshift to BigQuery migration can be initiated from BigQuery Data Transfer Service by choosing Redshift as a source. 

The migration process has three steps: 

  1. UNLOAD from Redshift to S3—The GKE agent initiates an UNLOAD operation from Redshift to S3. The agent extracts Redshift data as a compressed file, which helps customers minimize the egress costs. 
  2. Transfer from S3 to Cloud Storage—The agent then moves data from Amazon S3 to a Cloud Storage bucket using Cloud Storage Transfer Service. 
  3. Load from Cloud Storage to BigQuery—Cloud Storage data is loaded into BigQuery (up to 10 million files).
GCP bigquery.png
The BigQuery Data Transfer Service, showing Redshift as a source.

Watch the video to learn more!

7477

Of your peers have already watched this video.

1:15 Minutes

The most insightful time you'll spend today!

Case Study

An Indian Example of How to Really Up Your Customer Experience Game and Increase Conversion Rates With AI

How about selfie analysis of users to recommend them the right lipstick color?

That’s just one of the many ideas folks at Purplle.com came up with to improve the buying experience of Indian consumers.

And without the power of Google Cloud, it would probably have remained just that…an idea.

But today, thanks to Google Cloud, “Nothing seems impossible,” says Suyash Katyayani, CTO, Purplle.

Purplle.com is an online e-commerce company in India and one of the pioneers in creating a digitally-native beauty brands in India.

“The beauty industry is so data intensive that we needed to have a strong data strategy and we were looking out for solutions which would enable us to have a strong data pipeline and a strong data warehousing solution,” says Katyayani.

That’s when it turned to Google Cloud.

Additionally, Purplle.com, says Katyayani, does not have to worry about at what scale the company operates at because they have access to state-of-the-art infrastructure from Google Cloud available to them so that their developers can run experiments.

“The biggest plus point for us has been the agility that Google Cloud has added,” says Katyayani.

More Relevant Stories for Your Company

E-book

A CIO’s Guide to Data Analytics and Machine Learning

Breakthroughs in artificial intelligence (AI) have captured the imaginations of business and technical leaders alike. The AI techniques underlying these breakthroughs are finding diverse application across every industry. Early adopters are seeing results, particularly encouraging is that AI is starting to transform processes in established industries, from retail to financial

Case Study

AirAsia Leverages Google Cloud to Enhance Pricing, Revenue, and Customer Experience

With Google Cloud, AirAsia is now a “data first” business that can capture, analyze, and report on rising volumes of data to address complex problems and increase revenue streams. The airline is also using AI-powered chatbots to streamline internal operations and provide a faster, more efficient service. Become a digital

Explainer

The New and Upcoming Infrastructure for Google Cloud’s AI and ML Solutions

How does Google manage to provide its customers a differentiated compute platform experience and define ways to fully leverage its infrastructure supporting its cutting-edge AI and ML offerings? Easy-to-use, scalable and ability to create innovative products and services to end-users at low cost of ownership is the narrative behind Google

Blog

How AI and Analytics in EdTech are Living Upto the Hype

Over the last year, COVID-19 presented unforeseen challenges for practically every type of business and organization—including schools, colleges, and universities. For educational institutions, the pandemic was an unapologetic agent of acceleration, shifting one billion learners from in-person to online learning within two months.  The rapid transition to online learning exposed

SHOW MORE STORIES