Built with BigQuery: Retailers Unlock the Value of Data with SoundCommerce
3238
Of your peers have already read this article.
3:30 Minutes
The most insightful time you'll spend today!
As economic conditions change, retail brands’ reliance on ever-growing customer demand puts these companies at financial and even existential risk. Top-line revenue and active customer growth do not equal profitable growth.
Despite multi-billion dollar valuations for some brands, especially those operating the direct-to-consumer model, the rising costs of meeting shoppers’ high expectations (i.e. free shipping, free returns) along with the escalating cost of goods, fulfillment operations, and delivery costs, create pressure for brands to turn a profit. The ONLY way brands drive profitable growth is by managing variable costs in real-time. This in turn mandates adopting modern data cloud infrastructure including Google Cloud services such as BigQuery.
To unlock the value of data, Google Cloud has partnered with SoundCommerce, a retail data and analytics platform that offers a unique way of connecting marketing, merchandising, and operations data and modeling it within a retail context – all so brands can optimize profitability across the business.
Profitability can be measured per order through short-term metrics like contribution profit or long-term metrics like Customer Lifetime Value (CLV). Often, retailers calculate CLV as a measure of revenue with no consideration for the variable costs of serving that customer, for instance: the costs of marketing, discounting, delivering orders to the doorstep, or post-conversion operational exceptions (e.g. cancellations, returns).
What may first appear to be a high lifetime value customer through revenue-based CLV models, may not be profitable at all. By connecting marketing, merchandising, and operations data together, brands can understand their most profitable programs, channels, and products through the lens of actual customer value – and optimize accordingly.
The journey for brands starts with the awareness and data enablement of a more complex data set containing all variable revenue, cost, and margin inputs. What does a retailer need to do to achieve this?
- All data together in one place
- Matched and deduplicated disparate data
- Data is organized into entities and concepts that business decision makers can understand
- A common definition of key business metrics (this is especially important yet challenging for retailers because systems are siloed by the department and common KPIs like contribution profit per order may be defined differently across a company)
- Branched outputs for actionability: BI dashboards vs. data activation to improve marginal profit.
Once brands understand these requirements, up next is execution. This responsibility may fall within a ‘build’ strategy on the shoulders of technical IT/data leadership and their team(s) within a brand. This offers maximum control but at maximum cost and time-to-value. Retail data sources are complicated and change often. Technical teams within brands can spend too much time building and maintaining the tactical data ingestion process, which means they are spending less time deriving business value from the data.
But it doesn’t have to be this hard. There are other options in the market that brands can consider, such as a tool like SoundCommerce which provides a library of data connectors, pre-built and customizable date mappings, and outbound data orchestrations all tailor-made and ready-to-go for retail brands.
SoundCommerce empowers retail technology leaders to:
- Maintain data ownership to allow users to send modeled data to external data warehouses or layer-on business analytics tools for greater flexibility
- Provide universal access to data across the organization so every employee can have access to and participate in a low-code or no-code experience
- Expand and democratize data exploration and activation among both technical and non-technical users
For retail business and marketing decision-makers, SoundCommerce makes it easy to:
- Calculate individual customer lifetime value through the lens of profitability – not just revenue.
- Evaluate and predict lifetime shopper performance – identify which programs drive the highest CLV impact
- Set CAC and retention cost thresholds – determine optimal Customer Acquisition Costs (CAC) and retention costs that ensure marketing efforts are profitable through the lens of total lifetime transactions
Below is a sample data flow that illustrates how SoundCommerce connects to all the tools a Retailer is using and ingests the first-party data, agnostic of the platform. SoundCommerce then models and transforms the data within the retail context for brands to take immediate action on the insights they gain from the modeled data.
SoundCommerce built on Google Cloud Platform Services
SoundCommerce selected the Google Cloud Platform and its services to achieve what they set out to do – drive profitability for retailers and brands. SoundCommerce perfected this very need of retailers to centralize and harmonize data, map to business users’ needs to infer key metrics and insights by visualizing the data, or reuse the produced datasets to build upon other use cases specific to retailers. SoundCommerce built a cloud-native solution on Google Cloud leveraging the data cloud platform. Data from various sources are ingested in raw format, parsed, and processed as messages in Cloud Storage buckets using Google Kubernetes Engine (GKE) and stored as individual events in Cloud BigTable. A mapping engine maps the data to proprietary data models stored in BigQuery to store the produced data as datasets. Customers use visualization dashboards in Looker to access the data exposed as materialized views from within BigQuery. In many cases, these views are directly accessible by the customer per their use case.
Power Retail Profitable Growth with Analytics Hub
SoundCommerce adopted BigQuery and the recent release of Analytics Hub – a data exchange that enables BigQuery users to efficiently and securely share data. This feature ensures a more scalable direct access experience for SoundCommerce’s current and future customers. It meets brands where they are in their data maturity by giving them the keys to own their data and control their analytics-driven business outcomes. With this feature, retailers can customize their analysis with additional data they own and manage.
“Retail Brands need flexible data models to make key business decisions in real-time to optimize contribution profit and shopper lifetime value,” said SoundCommerce CEO Eric Best. “GCP and BigQuery with Analytics Hub make it easy for SoundCommerce to land and maintain complex data sets, so brands and retailers can drive profitable shopper experiences with every decision.”
SoundCommerce uses Analytics Hub to increase the pace of innovation by sharing datasets with its customers in real time by using the streaming functionality of BigQuery. Customers subscribe to specific datasets through a data exchange as data is generated from external data sources and published into BigQuery. This leads to a natural flow of data that scales easily to hundreds of exchanges and thousands of listings. From the Customer’s viewpoint, Analytics Hub enables them to search listings and coalesce data from other software vendors to produce richer insights. All of the benefits are an add-on to the BigQuery features such as separation of compute and storage, petabyte-scale serverless data warehouse, and tighter integration with several Google Cloud products.
The below diagram shows a view of SoundCommerce sharing datasets with one of its customers:
- A SoundCommerce GCP project that hosts the BigQuery instance contains one or more Source Datasets that are composed into a Shared Dataset for a specific customer. The dataset is wrapped around Materialized views but can include other BigQuery objects such as Tables, Views, Authorized views and datasets, BigQuery ML models, external Tables, etc.
- The same SoundCommerce GCP project contains the data exchange that acts as a container regarding the shared datasets. The exchange is made private to securely share the curated dataset relevant to the customer. The shared dataset is published into the data exchange as a private listing. The listing inherits the security permissions that are configured on the exchange.
- SoundCommerce shares a direct link to the Exchange to the Customer, which they can add as a Linked dataset into their project in their Google Cloud Organization. The shareable link can be pointed to a private listing. From here on, the dataset is visible in the Customer project like any other dataset and immediately available to accept queries and return results. Alternatively, the customer can also view the listing in their own project under Analytics Hub and subscribe to it by adding it as a linked dataset.
SoundCommerce is incrementally onboarding customers to use Analytics Hub for all data sharing use cases. This enables brands to get business insights faster and gain an understanding of their profitable growth quickly. Plus, it gives them the ownership to own their own data and manage it how they see fit for their business. From a technical standpoint, the adoption of Analytics Hub has led to leveraging an inherent capability in BigQuery for data sharing, faster scaling, and reducing operational overhead to onboard customers.
The Built with BigQuery advantage for ISVs
Through Built with BigQuery launched in April as part of the Google Data Cloud Summit, Google is helping tech companies like SoundCommerce build innovative applications on Google’s data cloud with simplified access to technology, helpful and dedicated engineering support, and joint go-to-market programs. Participating companies can:
- Get started fast with a Google-funded, pre-configured sandbox.
- Accelerate product design and architecture through access to designated experts from the ISV Center of Excellence who can provide insight into key use cases, architectural patterns, and best practices.
- Amplify success with joint marketing programs to drive awareness, generate demand, and increase adoption.
BigQuery gives ISVs the advantage of a powerful, highly scalable data warehouse that’s integrated with Google Cloud’s open, secure, sustainable platform. And with a huge partner ecosystem and support for multi-cloud, open source tools, and APIs, Google provides technology companies the portability and extensibility they need to avoid data lock-in.
Click here to learn more about Built with BigQuery.
We thank the many Google Cloud team members including Yemi Falokun in Partner Engineering who contributed to close collaboration with the SoundCommerce team.
Partnering with Google Cloud is the Key Behind Recent Healthcare Innovations
8819
Of your peers have already read this article.
2:00 Minutes
The most insightful time you'll spend today!
It’s simply amazing to witness how some of our systems integrators employ Google Cloud solutions to drive innovation in ways we at Google may never have considered—especially in healthcare. According to analyst firm MarketsandMarkets, the market for the Cloud in healthcare is projected to grow 43% between 2020 and 2025 to nearly $65 billion, fueled by the need for better technology infrastructures and faster digital transformation.
Healthcare and life sciences communities are looking to Google Cloud and its Service engagement model partners to improve collaboration and activate the power of medical data. These transformations deliver robust data analytics and bring a much deeper perspective into health epidemics like COVID-19 to help save lives.
In the healthcare and life sciences industry, our Google Cloud partners and customers provide constant energy and inspiration–and are the magic in some key healthcare innovations globally.
Let me show you how they are solving real-world business challenges.
Improving collaboration in Healthcare
Cloudbakers and Comanche County Memorial Hospital transitioned 2,000 employees to Google Workspace to improve collaboration, reduce costs, and increase security. By implementing a system that requires less maintenance while enabling mobile access to data and true collaboration, Comanche County Memorial Hospital saves $175,000 annually on licenses and helps medical professionals spend more time with patients.
“We chose Google Workspace because it cost a quarter of what we were paying previously, offers the kind of modern features that healthcare facilities need, and gave us data security and peace of mind.” —James Wellman, CIO, Comanche County Memorial Hospital
Accessing previously locked down medical data
Google Cloud and Quantiphi supported advances in cloud-based machine learning services to reduce infrastructure costs, unlock new paths of treatment, and dramatically reduce the amount of time it takes to evaluate scanned imagery following a stroke. John Hopkins University BIOS Division has been working on medical imaging to accelerate insights from scans on approximately 500 patients from 2,500 hours to 90 minutes, and lead to more accurate decision-making for brain injury patients that will ultimately improve medical outcomes.
“We’ve aligned closely with the goal of showing that a cloud-based, AI-driven approach is robust and that it can be performed while protecting personal PHI. In terms of costs, the cloud has definitely reduced some of the traditional financial demands of our research.” —Daniel F. Hanley, Jr., M.D., Director, Johns Hopkins University BIOS Division
Improving data access with the flexibility of Google Cloud
With the help of MediaAgility, TRIARQ migrated to Google Cloud to modernize their platform, build new applications, and expand their global footprint. With BigQuery, TRIARQ can now consolidate all transactional data, past and present, into a single location to report on specific insights and predict future data from e-prescriptions to complex revenue-cycle data and value-base analysis.
“The future of this industry will need to be a global one, and we can no longer be stuck in legacy systems if we want to survive. Having a team that is passionate about supporting us in this journey is definitely a plus point.”—Yaw Kwakye, Co-founder and Chief Architect, TRIARQ
Increasing visibility to COVID-19 outbreaks for actionable insights
As part of its response to the COVID-19 pandemic, HCA Healthcare chose to work with Google Cloud and SADA to create a national portal that increases visibility into outbreaks in 3,100 counties across the country in just 8 weeks. Now, the portal generates 30,000 new analytical views each day that can help inform private and public sector decision making for reopenings, closures, hot spots, and many other population health management activities.
“This project required deep knowledge of AI, and consumer-facing platforms, as well as healthcare. Google brought together the ideal combination of product, people, and partners. Google Cloud’s healthcare-specific products along with SADA’s expertise in the healthcare IT space made this partnership the perfect choice to move quickly and intelligently.”—Dr. Edmund Jackson, Chief Data Officer, HCA Healthcare
We’re committed to building the technology, resources, and services through Partner Advantage to help our partners address this opportunity. Looking for a solution focused partner in your region who has achieved Expertise and/or Specialization in your industry? Search our Global Partner Directory. Not yet a Google Cloud partner? Visit Partner Advantage and learn how to become one today!
Held Back by Database Scalability, This Financial Services Company Switches to Google Cloud and Cloud Spanner
11553
Of your peers have already read this article.
4:30 Minutes
The most insightful time you'll spend today!
Azimut Group operates an international network of companies handling investment and asset management, mutual funds, hedge funds, and insurance. Founded in Milan, Italy in 1988, Azimut Group today has branches in fifteen countries, including Brazil, China, and the USA.
“We have subsidiaries and manage funds all over the world,” explains Simone Bertolotti, IT Manager at Azimut Holding S.p.a. “That means that any technology that we put in place has to cover needs from many different countries.”
“When complicated analysis has to be executed, we have to increase our table space in a couple of minutes so that the AI can drill down into the data and deliver the information we need.”
—Simone Bertolotti, IT Manager, Azimut Holding S.p.a.
Azimut manages its funds with investment advisors who use information sourced from Bloomberg, Reuters and others. “They use a huge amount of data,” says Simone. “They work with spreadsheets, algorithms, formulae and they analyse data in minutes.” In finance, every second is crucial, which is why Azimut decided to develop a risk management dashboard that can process information even more quickly, then distribute it worldwide.
“When an advisor manages data, that data is used to make immediate decisions on funds, capital movements or whether to sell stock,” says Simone. “They have to be ready to make recommendations for any amount of data that comes to them. For our dashboard, that means that when additional information arrives or complicated analysis has to be executed, we have to increase our table space in a couple of minutes so that the AI can drill down into the data and deliver the information we need.”
Generating insights at speed
Investors and investment managers make decisions based on the most accurate, up-to-date information possible. For Azimut Group, information sourced through financial data vendors such as Bloomberg and Reuters provided only part of the data that the group required.
“We looked to collect information from a range of different providers,” explains Simone, “then analyse it to develop a predictive algorithm that could work faster than an advisor stationed at the terminal. We set ourselves the challenge to try to manipulate that data to add new insights into our matrix, so that every one of our branches across the world can see risk information about the funds in real-time.”
“We compared Google Cloud Platform’s performance with our previous cloud provider, and saw huge benefits of switching to Google. For me, the key performance issue is scaling. With Google Cloud Platform I know that I can increase and decrease my infrastructure quickly, when I need it.
—Simone Bertolotti, IT Manager, Azimut Holding S.p.a.
The first cloud provider Azimut used to build its system struggled to scale quickly to meet different kinds of data challenges. “If we wanted to add more cores, that was fine,” says Simone. “But the previous cloud provider made it complicated to raise the amount of space in a database infrastructure and scale up to demand. Scaling up for more in-depth analysis would take a day, and our need was immediate.”
That’s why Azimut switched one year ago to Google Cloud Platform to run the 150 VMs on its risk analysis platform. “We compared Google Cloud Platform’s performance with our previous cloud provider, and saw huge benefits of switching to Google. For me, the key performance issue is scaling,” says Simone. “With Google Cloud Platform I know that I can increase and decrease my infrastructure quickly, when I need it. Instead of waiting a day to scale up infrastructure, we can request and add space to our database in a couple of minutes.”
The infrastructure of Azimut’s solution handles around 800TB of data per month, and Google’s global network of servers and high-speed connections ensure that it gets to where it’s most needed by the most direct route. Impressed by the speed, security and availability of Google Cloud Platform, Azimut has moved its intranet on to Google Cloud Platform, too, eliminating the need for staff to login with VPNs.
“Instead of waiting a day to scale up infrastructure, we can request and add space to our database in a couple of minutes.”
—Simone Bertolotti, IT Manager, Azimut Holding S.p.a.
Driving ahead with Noovle
For Azimut, migrating the risk management dashboard is the latest of many Google product collaborations with cloud consultancy Noovle. “Everything started five years ago,” says Simone, “when Noovle assisted us in migrating to Gmail from our on-premise email solution. From G Suite to Google Cloud Platform, we’ve had a great relationship. Noovle provides consultancy services, support for mobility, and external advisors who work on our premises, such as when they trained us how to broadcast our meetings on Google Hangouts. As an independent company, we know we can trust them for transparent advice. All they care about is the best way to get a job done and to help us reach our goals.”
New app, new customers
In a business case comparison, Google Cloud Platform cost Azimut 35% less to run than the previous cloud provider. Now the group is building a major new mobile application on Google App Engine to be released in 2018.
“The new mobile application will allow customers to trade directly, without human advisors, by proposing different investment solutions depending on targets the customers set,” says Simone. “So if a customer aims to make money with investments, they enter their relevant personal information and we carry out the necessary regulatory checks and suggest what they could buy. The entire project will be based on Google Cloud Platform, so customers can control their investments through the app while we manage the fund, using Google Cloud Spanner on the backend.”
1 Developer. 5 Months. A Revenue Generating App With 100K Users With Firebase
7856
Of your peers have already read this article.
5:30 Minutes
The most insightful time you'll spend today!
This is a guest post authored by Firebase customer, Anton Ivanov, Founder & CEO of DealCheck
Real estate investing is a fantastic way to build a stream of passive income and grow your wealth. Numerous studies have pointed out that real estate investing has created more millionaires throughout history than any other form of investing (like this one and this one). So why don’t more people do it?
I asked myself this very question a few years ago after talking to a group of friends about the success I’ve had with real estate, and listening to their reasons why they think it’s out of their reach.
A common theme among them was that they viewed it as something too difficult to learn and master. There were too many steps, the learning curve was steep and there was a lot of room for mistakes for somebody just starting out, especially when analyzing the financial performance of potential investment properties.
Traditionally, most investors used spreadsheets to do the math – which works only if you know what and how you’re calculating something. But if you don’t know that, it’s very easy to make mistakes and overlook things. And no one wants to make mathematical errors before a huge purchase like an investment property.
Where do I even begin?!
And that’s when I had the idea to build DealCheck – a cloud-based, easy-to-use property analysis tool for real estate investors and agents. I wanted to create a platform that would help new investors learn the ropes and avoid costly mistakes, but at the same time provide the flexibility to perform more advanced analysis with a click of a button.
Making real estate investing easier and more accessible.
The Challenges of Solo Development
I was working as a front-end engineer at the time, so I knew I could build the UI myself, but what about the back-end, data storage, authentication, and a bunch of other things you need for a full-functioning cloud app?
I didn’t know anybody I could bring on as a co-founder, so I set out to research what technologies and platforms I could leverage to help me with the back-end and server infrastructure.
Firebase kept popping up again and again and I began to look at it in more detail. It was then recently acquired by Google and its collection of BaaS (backend-as-a-service) modules seemed to offer the exact solution I needed to build DealCheck.
I was especially impressed with the documentation for each feature and how well all of the different technologies could be tied together to create one unified platform.
It wasn’t long before I signed up and started building the first MVP of the app.
Using Firebase to Quickly Build a Scalable Backend
As the only developer on the project, I had limited time and resources to spend on building the back-end, so I set out to use every Firebase feature that was available at the time to my advantage.
My goal was actually to write as little server-side code as possible and instead focus on leveraging the different Firebase modules to solve three specific challenges:
Challenge #1 – Authentication and User Management
The first one was authentication and user management. DealCheck’s users needed the ability to create their accounts so they can view and analyze properties on any device (more on that later). I wanted to have the ability to sign in with email, Facebook or a Google account.
Firebase Authentication was designed specifically for this purpose and I used it to handle pretty much the entire authentication flow. Out-of-the-box, it has support for all the major social networks, cross-network credential linking and the basic account management operations like email changes, password resets and account deletions.
There was no server-side code required at all – I just needed to build the UI on the front-end.
Email, Facebook and Google sign in powered by Firebase.
And as an added benefit, Firebase Authentication ties directly into the Realtime Database product to create a declarative permissions and access control framework that’s easy to implement and maintain. This helped me make sure user data was protected from unauthorized access, but also facilitate data sharing among users.
Challenge #2 – Cloud Storage with Cross-Device Sync
Next up was data storage. I knew that I wanted DealCheck’s users to be able to use the app and analyze properties online, on iOS and Android. So I needed a real-time, cloud-based database solution that could sync data across any device.
Syncing data across web and mobile is not easy!
Firebase Realtime Database is a NoSQL, JSON-based database solution that was designed exactly for this purpose, and I was actually surprised how great it worked. I used the official AngularJS bindings for Firebase on the front-end to read and write to it directly from the client.
I had to do some extra work on mobile to implement an offline mode with syncing after reconnections, but all-together the code required to make everything work was minimal.
As I mentioned, Firebase Authentication tied directly to the database to facilitate access control, so I really didn’t need to do anything extra there. And I was able to set up automatic daily backups of all the data with a click of a button.
Challenge #3 – Third-Party Integrations
Up to now, I had written exactly 0 lines of server-side code and everything was handled by the client directly. As DealCheck’s development progressed, however, I knew that I would need a server to handle some operations that could not be done in the client.
I wasn’t very experienced with server maintenance and DevOps, but fortunately the Firebase Cloud Functions product was able to solve all of my needs. Cloud Functions are essentially single-purpose functions that can be triggered (or executed) based on a specific HTTP request or events coming from the Authentication, Realtime Database or other Firebase products.
Each function can be run once based on a specific event trigger to perform its prescribed task. You don’t have to worry about provisioning a server instance or managing load – everything is done automatically for you by Firebase.
What’s even cooler, is that Cloud Functions can access the Realtime Database and Cloud Storage buckets of the same project, performing operations on them server-side, as needed.
This is how DealCheck processes subscription payments through Stripe, validates Apple and Google Play mobile subscription receipts, integrates with third-party APIs and updates database records without user interaction.
Bringing in sales comparable data from third-party providers into DealCheck.
Cloud Functions became the “glue” that tied the entire back-end infrastructure together.
Growing from an MVP to 100,000 Users with Firebase
The first version of the DealCheck app was built and launched in less than 5 months with just me on the development team. I definitely don’t think that would have been possible without Firebase powering the back-end infrastructure. Maybe the project wouldn’t have ever launched at all.
While Firebase is awesome for quick MVP development, it’s definitely designed to power production applications at scale as well. As DealCheck grew from a small side-project to one of the most popular real estate apps with over 100k users, all of the Firebase products that we use scaled to support the increasing load.
Moreover, the fantastic interoperability of all Firebase modules allows us to develop and release new features much faster because of the reduced coding requirements and ease of configuration.
So next time you’re looking to build an ambitious project with a small team – take a look at how Firebase can help you reduce development time and provide a suite of powerful tools that scale as your business grows.
This is exactly how DealCheck grew from a simple idea to make property analysis easier and faster, to an app that is helping tens of thousands of people grow their wealth and passive income through real estate investing. It’s a truly awesome and fulfilling experience to see your work positively impact so many people and it wouldn’t have been possible without Firebase.
Guide for Measuring Cloud Spanner Performance for Your Custom Workload
4820
Of your peers have already read this article.
3:00 Minutes
The most insightful time you'll spend today!
Database migration to a new database platform or technology can be daunting for various reasons. One of the common concerns is database performance. It is hard to evaluate database performance early in the evaluation cycle without performing actual data migration and application changes. This becomes even more important in the case of Cloud Spanner where modernization is required at both the database and application layers.
The traditional approach to evaluate database performance is to deploy the application and database together, migrate historical data to the new database and then simulate load tests by running an application on the new database. Doing all this for an early performance evaluation of Cloud Spanner may feel like a lot of effort.
In this post, we will explore a middle ground to performance testing using JMeter. Performance test Cloud Spanner for a custom workload before making application code changes and executing data migration. More detailed step-by-step guide is published here.
Goals
- Estimate the number of Cloud Spanner nodes needed (and get a cost estimate).
- Performance test the most frequently used set of queries and transactions.
- Demonstrate the ability to scale horizontally.
- Better understand the optimizations needed for schema and sql queries.
- Determine latency of DML operations.
Limitations
- You cannot test using non-java client libraries.
- It is more complex to test non-jdbc compliant features like mutations, parallel reads etc.
Preparing for performance tests
- Identify top SQL queries, latency, frequency / hour and avg number of rows returned or updated for each. This information will also serve as a baseline for the current system.
- Determine Cloud Spanner region / multi-region deployment. Ideally, load should be generated from the Cloud Spanner instance’s leader region for minimum latency and best performance. Read Demystifying Cloud Spanner multi-region configurations for more details on various configurations of Cloud Spanner.
- Estimate the number of Cloud Spanner nodes required for a given workload based on (step 1). It is recommended to have a minimum of 2 nodes for linear scaling.
Note: Peak performance numbers of regional performance and multi regional performance are published. It is based on a 1KB single row transaction with no secondary indexes. - Request quota for Cloud Spanner nodes on a given region / multi-region. It can take up to 1 business day.
Setting up Cloud Spanner
Creating the schema for Cloud Spanner
You can use the following tools to generate a schema for Cloud Spanner if you are migrating from the following source databases. Alternatively, you can model the schema manually. Schema design has a huge performance impact, hence it is recommended to review the schema very carefully.
| Source | Target | Tool(s) | |
| 1. | MySQL / MariaDB | Cloud Spanner | HarbourBridgeStriim |
| 2. | Postgresql | Cloud Spanner | HarbourBridgeStriim |
| 3. | Oracle | Cloud Spanner | Striim |
| 4. | SQL Server | Cloud Spanner | Striim |
Note: Manual review and tuning of schema will be needed to optimize and mitigate potential hotspots. You will need to keep in mind schema design best practices when modeling your schema.
Populating seed data into Cloud Spanner
Performance of a database depends on the amount of data present. Existing data (and indexes) determines how much data is scanned on select queries and therefore performance. Hence, it is important to seed data into Cloud Spanner before performance testing.
For most realistic results, data should be migrated from an existing production source. Sometimes you cannot do that due to schema changes. One way is to utilize a custom ETL job to export and transform data and then import into Cloud Spanner. Another alternative could be to mock seed data using JMeter(More details in later sections).
JMeter performance tests
Writing Tests
JMeter will interact with Cloud Spanner just like your application. It will perform DML operations the same way as your application does. Hence, tests need to simulate production like transactions. For example if a transaction is made up of insert and/or update statements in several tables within transaction boundaries then JMeter should mimic the same.
JMeter has the following hierarchy:
Test Plan > Thread Group(s) > Sampler(s) (aka test)
Test plan is a top level component. It can contain global properties and libraries (like jdbc driver etc).
Thread Group(s) are representative of a database transaction. It contains one or more sampler(s). All the samplers within a thread group execute serially.
Sampler(s) should represent a single DML call. If your transaction needs to have multiple dml calls, you should create multiple samplers. Typically you will use JDBC Sampler for database calls. You can also use JSR 223 Sampler as described here, in case you need to use mutations or parallel reads etc. Results from one sampler can be passed to the next, as in real world application.
Executing tests and collecting results
JMeter tests should be executed as physically close to Cloud Spanner instances as possible to minimize network latency. Therefore it is best to execute them from Compute Engine (via private service connect) from the same region as the Cloud Spanner instance. In-case of multi region Cloud Spanner, GCE instances should be created in the Leader Region of the Cloud Spanner instance for lowest network latency.
JMeter should be executed via command line, it is a CPU intensive application so make sure you have allocated enough resources on the VM.
Refer to the Cloud Spanner monitoring dashboard to ensure that your Cloud Spanner instance’s CPU utilization is at or below the recommended values. In addition, use introspection tools to investigate performance issues with your database.
You might need to optimize your queries (add indexes) and re-execute tests in multiple waves to tune the performance as needed.
Try it yourself
Follow a detailed step by step tutorial on Measure Cloud Spanner performance test using JMeter to test Cloud Spanner performance yourself.
3649
Of your peers have already watched this video.
3:10 Minutes
The most insightful time you'll spend today!
What is Google Cloud SQL?
Cloud SQL is a fully managed relational database for MySQL, PostgreSQL, and SQL Server.
It reduces maintenance cost and automates database provisioning, storage capacity management, replication, and backups. It offers quick setup, with standard connection drivers and built-in migration tools.
How Do You Set It Up?
Cloud SQL is easy to setup. You select the region and zone where you would like the instance to be, and it gets created there.
You also have a lot of configuration options, where you can select the machine type with the right number of CPU and amount of memory you need.
Choose storage each type between solid state and hard disk drives, depending on latency queries per second and cost requirements and set storage capacity.
Higher storage capacity leads to better performance.
What About Back Up?
Cloud SQL also offers automated backups and recovery options.
You can set time slots and locations for backups. For production applications, it is recommended to enable high availability, or HA.
By enabling this feature, the database instancewill automatically failover to another zone in your selected region in case of an outage.
You can also create cross-regional replicas to protect from regional failures.
In addition, you can enable automatic storage increase to add more storage when nearing capacity.
How Do You Migrate an Existing MySQL Database to Cloud SQL?
Cloud console makes it very easy by providing a migrate data button, which guides you through easy steps.
First, you provide your data source details, things like public IP address, port number,
and your replication credentials.
Second, you create a Cloud SQL read replica, just like we discussed in the creation process, using a SQL dump file.
Third, you sync the read replica with source. And, finally, you promote the read replica to primary instance with very low downtime.
Is Data Safe in Cloud SQL?
Like anything else in Google Cloud,the data in Cloud SQL is encrypted at rest and in transit.
External connections can be encrypted using SSL or Cloud SQL Proxy, which is a tool to help you connect to your Cloud SQL instance from your local machines.
You can use Cloud SQL as a relational database for your applications that are hosted within Google Cloud, like App Engine, Cloud Run, Compute Engine, Kubernetes Engine, or Cloud Functions.
You can also connect your Cloud SQL database with applications that are hosted outside of Google Cloud.
How Much Does It Cost and What Are Some Uses Cases?
Cloud SQL pricing varies depending on MySQL, PostgreSQL,or SQL Server.
Broadly speaking, though, it’s the combination of the type of instance, storage, or network you use. SQL Server also has some licensing costs.
Since Cloud SQL is a relational database, you can use it with any online transaction processing apps, such as order or payment processing apps, where you need to handle frequent queries with fast response times.
More Relevant Stories for Your Company
Benefits of BigQuery for SAP Customers
IDC recently examined the benefits of implementing BigQuery for SAP data. The findings reveal massive improvements to the SAP customers' overall business results due to faster access to data insights, lower data warehouse operation cost, and increased productivity among the data warehouse and development teams. Download the report now!
Unified, Flexible and Accessible: How Companies’ Data Help Them Achieve More on Google Cloud
As the volume of data that people and businesses produce continues to grow exponentially, it goes without saying that data-driven approaches are critical for tech companies and startups across all industries. But our conversations with customers, as well as numerous industry commentaries, reiterate that managing data and extracting value from
How to Migrate an Oracle Database to Google Cloud
As more enterprises migrate a growing number of workloads to the cloud, migrating databases, too, has become a key focus. But migrating databases is also really tough. This is due to a number of different reasons. The first one is that there's a lot of proprietary technology and functionality that's
A Breakdown of Cloud-based Data Ingestion Practices
Businesses around the globe are realizing the benefits of replacing legacy data silos with cloud-based enterprise data warehouses, including easier collaboration across business units and access to insights within their data that were previously unseen. However, bringing data from numerous disparate data sources into a single data warehouse requires you
