Since its initial release in 1995, MySQL has not only been the de-facto database for many data storage needs, but has gathered much attention over the years as one of the most well-recognized databases around when it comes to Relational Database Management Systems and transactional data processing.

Any application in retail, e-commerce, or banking has likely had its fair share of business depending on a relational database for its transactional needs. Many of these applications are built on MySQL due to its flexibility, open source nature, and strong community support.

Whether in the context of a migration from a relational database (like MySQL or PostgreSQL) , migration from a NoSQL database (like Cassandra), or a green grass workload, many companies turn to Cloud Spanner seeking a high availability SLA (99.99% for regional instances and 99.999% for multi region instances),  unlimited scale,  and low operational overhead – no patching required, no maintenance or other planned downtimes, just to name a few benefits.

The tooling and open source ecosystem around Spanner has evolved and grown ever since the service was introduced. HarbourBridge is a part of this ecosystem, and it’s meant to help customers port their existing MySQL or PostgreSQL schema to a Cloud Spanner schema. 

Application Migration Tips

Despite helpful tools like HarbourBridge, database migrations are never trivial. Here are a few things to pay attention to when migrating from MySQL to Spanner, and how to update your application logic to address them.

Note – The following snippets use the PHP client for Cloud Spanner. A couple of the snippets reference a partial Magento port that our friends over at Searce have been working on. Once you understand the operations, you should be able to implement the same in any of the other languages that Cloud Spanner supports.

Cloud spanner enforces strict data types  

In a MySQL query, the value of an attribute can be referenced as either a string or an integer. Example:

• select * from catalog_eav_attribute where attribute_id = 46;
• select * from catalog_eav_attribute where attribute_id = “46”;

Both are valid and equivalent. 

In Cloud Spanner, the query will return an error if you try to reference an integer type by using a string representation.

Here is an example of a working query from Cloud Spanner:

• select * from catalog_eav_attribute where attribute_id = 46; — this will work
• Select * from catalog_eav_attribute where attribute_id = “46” — will fail, since we are supplying a string with “46” 

You may use a function like the following to assist you with such transformations.

  /**
    * Formats the SQL for Cloud Spanner
    * Example
    * Input SQL : <select statement> WHERE 
    *    (`product_id` = '340') ORDER BY position  ASC
    * Output SQL : <select statement> WHERE 
    *    (`product_id` = 340) ORDER BY position  ASC
    * In the above example integer 
    *`340` is sanitized by removing single quotes.
    * Sanitization is required since Cloud Spanner
    * has strict typing
    * @param string $sql
    * @return string $sql
    */
   public function sanitizeSql(string $sql)
   {
       if (preg_match_all("/('[^']*')/", $sql, $m)) {
           $matches = array_shift($m);
           for($i = 0; $i < count($matches); $i++) {
               $curr =  $matches[$i];
               $curr = filter_var($curr, 
                   FILTER_SANITIZE_NUMBER_INT);
               if (is_numeric($curr)) {
                   $sql = str_replace($matches[$i], 
                       $curr, $sql);
               }
           }
       }
       return $sql;
   }

Using sequences in primary keys is not a Spanner best practice

Cloud Spanner does not implement a sequence generator,  and it is not a Spanner best practice to use sequential IDs because doing so can cause hotspotting.

An alternative mechanism of generating a unique primary key is to use a UUID or any other similar mechanisms that result in non-sequential values. For more information, please refer to this article.

In order to convert all existing primary keys to a UUID pattern, you can change the schema using the snippet here

You may use this code snippet to modify the application code to generate the UUID for the auto increment. There are other ways to do this as well, such as using PHP built-in uniqid function.

  /**
    * Generate UUID.
    * @return string
    */
   public function getAutoIncrement()
   {
       if (function_exists('com_create_guid') === true) {
           return trim(com_create_guid(), '{}');
       }
       return sprintf('%04X%04X-%04X-%04X-%04X-%04X%04X%04X',
           mt_rand(0, 65535), mt_rand(0, 65535),
           mt_rand(0, 65535), mt_rand(16384, 20479), 
           mt_rand(32768, 49151),  mt_rand(0, 65535),
           mt_rand(0, 65535), mt_rand(0, 65535));   
   }

Implicit casting and the need for explicit casting of field data types 

Both MySQL and Cloud Spanner follow the SQL standard, hence much of the query syntax is the same. One notable difference is that the Cloud Spanner field types are implicitly cast to an appropriate data type out of the ones mentioned here. When implicit casting of the field type fails, Spanner returns a read error, so it would be safer to perform the casting to the appropriate type when issuing the SELECT statement.

Modify application code to cast to respective data type before execution of query.

  $con = $this->getSpannerConnection();
 /**
 * Cloud Spanner follows strict type so cast the columns appropriately
*/
$select = $con->addCast($select, "`t_d`.`value`", 'string');
$select = $con->addCast($select, "`t_s`.`value`", 'string');
$select = $con->addCast($select, "IF(t_s.value_id IS NULL, 
    t_d.value, t_s.value)", 'string');
$values = $con->fetchAll($select);

Modify the application code to cast the column to its respective type 

  /**
    * Cast the column with type
    * @param string $sql
    * @param string $col
    * @param string $type
    * @return string
    */
   public function addCast(string $sql,
       string $col, string $type)
   {
      $cast = "cast(".$col." as ".$type.")";
      return str_replace($col, $cast, $sql);
   }

Using interleaved tables to improve read performance

Cloud Spanner’s table interleaving is a great choice for many parent-child relationships where the child table’s primary key includes the parent table’s primary key columns. Interleaving ensures that child rows are collocated with their parent rows, which can significantly improve query performance.

Refer to the statements here for a few samples of creating interleaved tables. To learn more about table interleaving, visit the documentation.

Conclusion

Database migrations are complicated.  Hopefully, using HarbourBridge and the  list of tips in this article can make that task easier.  For additional tips, please take a look at this migration guide and read more about HarbourBridge.

As the Olympics kicked off in Tokyo at the end of July, we found ourselves reflecting on the beauty of diverse countries and cultures coming together to celebrate greatness and sportsmanship. For this month’s blog, we’d like to highlight some key data and analytics performances that should help inspire you to reach new heights in your data journey.

Let’s review the highlights!

BigQuery ML Anomaly Detection: A perfect 10 for augmented analytics

Identifying anomalous behavior at scale is a critical component of any analytics strategy. Whether you want to work with a single frame of data or a time series progression, BigQuery ML allows you to bring the power of machine learning to your data warehouse. 

In this blog released at the beginning of last month, our team walked through both non-time series and time-series approaches to anomaly detection in BigQuery ML:

• Non-time series anomaly detection
  • Autoencoder model (now in Public Preview)
  • K-means model (already in GA)
• Time-series anomaly detection
  • ARIMA_PLUS time series model (already in GA)

These approaches make it easy for your team to quickly experiment with data stored in BigQuery to identify what works best for your particular anomaly detection needs. Once a model has been identified as the right fit, you can easily port that model into the Vertex AI platform for real-time analysis or schedule it in BigQuery for continued batch processing.

App Analytics: Winning the team event

Google provides a broad ecosystem of technologies and services aimed at solving modern day challenges. Some of the best solutions come when those technologies are combined with our data analytics offerings to surface additional insights and provide new opportunities. 

Firebase has deep adoption in the app development community and provides the technology backbone for many organization’s app strategy. This month we launched a design pattern that shows Firebase customers how to use Crashlytics data, CRM, issue tracking, and support data in BigQuery and Looker to identify opportunities to improve app quality and enhance customer experiences.

Crux on BigQuery: Taking gold in the all-around data competition

Crux Informatics provides data services to many large companies to help their customers make smarter business decisions. While they were already operating on a modern stack and not on the hunt for a modern data warehouse, BigQuery became an enticing option due to performance and a more optimal pricing model. Crux also found advantages with lower-cost ingestion and processing engines like Dataflow that allow for streaming analytics.… when it came to building a centralized large-scale data cloud, we needed to invest in a solution that would not only suit our current data storage needs but also enable us to tackle what’s coming, supporting a massive ecosystem of data delivery and operations for thousands of companies.Mark Etherington
Chief Technology Office, Crux Informatics

Technology is a team sport, and Crux found our support team responsive and ready to help. This decision to more deeply adopt Google Cloud’s data analytics offerings provides Crux with the flexibility to manage a constantly evolving data ecosystem and stay competitive.

You can read more about Crux’s decision to adopt BigQuery in this blog.

Google Trends: A classic emerges a champion

Following up on the launch of our Google Trends dataset in June, we delivered some examples of how to use that data to augment your decision making. 

As a quick recap of that dataset, Google Cloud, and in particular BigQuery, provide access to the top 25 trending terms by Nielsen’s Designated Market Area® (DMA) with a weekly granularity. These trending terms are based on search patterns and have historically only been available on the Google Trends website.https://www.youtube.com/embed/9FJAXMF0ASc?enablejsapi=1&

The Google Trends design pattern addresses some common business needs, such as identifying what’s trending geographically near your stores and how to match trending terms to products to identify potential campaigns. 

Dataflow GPU: More power than ever for those streaming sprints

Dataflow is our fully-managed data processing platform that supports both batch and streaming workloads. The ability of Dataflow to scale and easily manage unbounded data has made it the streaming solution of choice for large workloads with high-speed needs in Google Cloud. 

But what if we could take that speed and provide even more processing power for advanced use cases? Our team, in partnership with NVIDIA, did just that by adding GPU support to Dataflow. This allows our customers to easily accelerate compute-intensive processing like image analysis and predictive forecasting with amazing increases in efficiency and speed. 

Take a look at the times below:

Data Fusion: A play-by-play for data integration’s winning performance

Data Fusion provides Google Cloud customers with a single place to perform all kinds of data integration activities. Whether it’s ETL, ELT, or simply integrating with a cloud application, Data Fusion provides a clean UI and streamlined experience with deep integrations to other Google Cloud data systems. Check out our team’s review of this tool and the capabilities it can bring to your organization.

At HSBC, we serve 39 million customers, in-person and online, from consumers to businesses, in 66 countries. We maintain data centers in 21 countries, with more than 94,000 servers. With an on-premises infrastructure supporting our business, we kept running into capacity challenges, which really became an innovation blocker and ultimately a business constraint.

Our teams wanted to do more with data to create better products and services, but the technology tools we had weren’t letting us grow and explore. And that data was growing continually. Just one of our data warehouses had grown 300% from 2014 to 2018.

We had a huge amount of data, but what’s the point of having all that data if we couldn’t get insights and business value from it? We wanted to serve our customers flexibly, in the ways that work best for them. 

We knew moving to cloud would let us store and process more data, but as a global bank, we were moving complex systems that needed to also be secure.

It was a team effort to create the project scope and strategy up front, and it paid off in the end. Our cloud migration now enables us to use an agile, DevOps mindset, so we can fail fast and deliver smaller workloads, with automation built in along the way.

This migration also helped us eliminate technical debt and build a data platform that lets us focus on innovation, not managing infrastructure. Along the way, we invented new technology and built processes that we can use as we continue migrating.

Planning for a cloud move
We chose cloud migration because we knew we needed cloud capabilities for our business to really reach its digital potential. We picked Google Cloud, specifically BigQuery, because it’s super fast over small and large datasets, and because we could use both a SQL interface and Connected Sheets to interact with it.

We had to move our data and its schema into the cloud—without having to manually manage every detail and miss the timelines we had set. Our data warehouse is huge, complex, and mission-critical, and didn’t easily lend itself to fit into existing reference architectures. We needed to plan ahead and automate to make sure the migration was efficient, and to ensure we could simplify data and processes along the way.

The first legacy data warehouse we migrated had been built over a period of 15 years, with 30 years worth of data comprising millions of transactions and 180 TB of data.

It ran 6,500 extract, transform, load (ETL) jobs and more than 2,500 reports, getting data from about 100 sources. Cloud migration choices usually involves either re-engineering or lift-and-shift, but we decided on a different strategy for ours: move and improve.

This allowed us to take full advantage of BigQuery’s capabilities, including its capacity and elasticity, to help solve our essential problem of capacity constraints. 

Taking the first steps to cloud
We started creating our cloud strategy through a mapping exercise, which also helped start the change management process among internal teams. We chose architecture decision records as our migration approach, basing those on technical user journeys, which we mapped out using an agile board. User journeys included things like “change data capture,” “product event handling,” or “slowly changing dimensions.” 

These are typical data warehouse topics that have to be addressed when going through a migration, and we had others more specific to the financial services industry, too.

For example, we needed to make sure the data warehouse would have a consistent, golden source of data at a specific point in time. We considered business impacts as well, so we prioritized initially moving archival and historical data to immediately take load off of the old system.

We also worked to establish metrics early on and introduce new concepts, like managing queries and quotas rather than managing hardware, so that data warehouse users would be prepared for the shift to cloud.

To simplify as we went, we examined what we currently had stored in our data warehouse to see what was used or unused. We worked with stakeholders to assess reports, and identified about 600-plus reports that weren’t being used that we could deprecate. We also examined how we could simplify our ETL jobs to remove the technical debt added by previous migrations, giving our production support teams a bit more sleep at night. 

We used a three-step migration strategy for our data: first, migrating schema to BigQuery; second, migrating the reporting load to BigQuery, adding metadata tagging and performing the reconciliation process; and third, moving historical data by converting all the SQL script into data into BigQuery-compliant scripts. 

Creating new tools for migration automation
In keeping with our automation mantra, we invented multiple accelerators to speed up migration. We developed these to meet the timelines we’d set, and to eliminate human error. 

The schema parser and data reconciliation tool helped us migrate our data layer onto BigQuery. SQL parser helped migrate the data access layer onto Google Cloud Platform (GCP) without having to individually migrate 3,500 SQL instances that don’t have data lineage or documentation. This helped us to prioritize workloads.

And the data lineage tool identified components across layers to find dependencies. This was essential for finding and eliminating integration issues during the planning stage, and for identifying application owners during the migration. Finally, the data reconciliation tool reconciles any discrepancies between the data source and the cloud data target. 

Building a cloud future
We used this first migration in our UK data center as a template, so we now have a tailored process and custom tools that we’re confident using going forward.

Our careful approach has paid off for our teams and our customers. We’re enjoying better development and testing procedures. We’ve created an onboarding path for applications, we have a single source of truth in our data warehouse, and we use authorized views for secure data access. The flexibility and scalable capacity of BigQuery means that users can explore data without constraints and our customers get the information they need, faster. 

Editor’s note: Today we are hearing from Jono MacDougall , Principal Software Engineer at Ravelin. Ravelin delivers market-leading online fraud detection and payment acceptance solutions for online retailers. To help us meet the scaling, throughput, and latency demands of our growing roster of large-scale clients, we migrated to Google Cloud and its suite of managed services, including Cloud Bigtable, the scalable NoSQL database for large workloads.

As a fraud detection company for online retailers, each new client brings new data that must be kept in a secure manner and new financial transactions to analyze. This means our data infrastructure must be highly scalable and constantly maintain low latency. Our goal is to bring these new organizations on quickly without interrupting their business. We help our clients with checkout flows, so we need latencies that won’t interrupt that process—a critical concern in the booming online retail sector. 

We like Cloud Bigtable because it can quickly and securely ingest and process a high volume of data. Our software accesses data in Bigtable every time it makes a fraud decision. When a client’s customer places an order, we need to process their full history and as much data as possible about that customer in order to detect fraud, all while keeping their data secure. Bigtable excels at accessing and processing that data in a short time window. With a customer key, we can quickly access data, bring it into our feature extraction process, and generate features for our models and rules. The data stays encrypted at rest in Bigtable, which keeps us and our customers safe.

Bigtable also lets us present customer profiles in our dashboard to our client, so that if we make a fraud decision, our clients can confirm the fraud using the same data source we use.

We have configured our bigtable clusters to only be accessible within our private network and have restricted our pods access to it using targeted service accounts. This way the majority of our code does not have access to bigtable and only the bits that do the reading and writing have those privileges.

We also use Bigtable for debugging, logging, and tracing, because we have spare capacity and it’s a fast, convenient location. 

We conduct load testings against Bigtable.  We started at a low rate of ~10 Bigtable requests per second and we peaked at ~167000 mixed read and write requests per second  at absolute peak. The only intervention that was done to achieve this was pressing a single button to increase the number of nodes in the database. No other changes were made.

In terms of real traffic to our production system, we have seen ~22,000 req/s (combined read/write) on Bigtable in our live environment as a peak within the last 6 weeks.

Migrating seamlessly to Google Cloud 

Like many startups, we started with Postgres, since it was easy and it was what we knew, but we quickly realized that scaling would be a challenge, and we didn’t want to manage enormous Postgres instances. We looked for a kind of key value store, because we weren’t doing crazy JOINS or complex WHERE clauses. We wanted to provide a customer ID and get everything we knew about it, and that’s where key value really shines.  

I used Cassandra at a previous company, but we had to hire several people just for that chore. At Ravelin we wanted to move to managed services and save ourselves that headache. We were already heavy users and fans of BigQuery, Google Cloud’s serverless, scalable data warehouse, and we also wanted to start using Kubernetes. This was five years ago, and though quite a few providers offer Kubernetes services now, we still see Google Cloud at the top of that stack with Google Kubernetes Engine (GKE). We also like Bigtable’s versioning capability that helped with a use case involving upserts. All of these features helped us choose Bigtable.

Migrations can be intimidating, especially in retail where downtime isn’t an option. We were migrating not just from Postgres to Bigtable, but also from AWS to Google Cloud. To prepare, we ran in AWS like always, but at the same time we set up a queue at our API level to mirror every request over to Google Cloud. We looked at those requests to see if any were failing, and confirmed if the results and response times were the same as in AWS. We did that for a month, fine tuning along the way. 

Then we took the big step and flipped a config flag and it was 100% over to Google Cloud. At the exact same time, we flipped the queue over to AWS so that we could still send traffic into our legacy environment. That way, if anything went wrong, we could fail back without missing data. We ran like that for about a month, and we never had to fail back. In the end, we pulled off a seamless, issue-free online migration to Google Cloud.

Flexing Bigtable’s features

For our database structure, we originally had everything spread across rows, and we’d use a hash of a customer ID as a prefix. Then we could scan each record of history, such as orders or transactions. But eventually we got customers that were too big, where the scanning wasn’t fast enough. So we switched and put all of the customer data into one row and the history into columns. Then each cell was a different record, order, payment method, or transaction. Now, we can quickly look up the one row and get all the necessary details of that customer. Some of our clients send us test customers who place an order, say, every minute, and that quickly becomes problematic if you want to pull out enormous amounts of data without any limits on your row size. The garbage collection feature makes it easy to clean up big customers.  

We also use Bigtable replication to increase reliability, atomicity, and consistency. We need strong consistency guarantees within the context of a single request to our API since we make multiple bigtable requests within that scope. So within a request we always hit the same replica of Bigtable and if we have a failure, we retry the whole request. That allows us to make use of the replica and some of the consistency guarantees, a nice little trade-off where we can choose where we want our consistency to live.https://www.youtube.com/embed/0-eH5u7rrQQ?enablejsapi=1&

We also use BigQuery with Bigtable for training on customer records or queries with complicated WHERE clauses. We put the data in Bigtable, and also asynchronously in BigQuery using streaming inserts, which allows our data scientists to query it in every way you can imagine, build models, and investigate patterns and not worry about query engine limitations. Since our Bigtable production cluster is completely separate, doing a query on BigQuery has no impact on our response times. When we were on Postgres many years ago, it was used for both analysis and real time traffic and it was not the optimal solution for us. We also use Elasticsearch for powering text searches for our dashboard.

If you’re using Bigtable, we recommend three features:

• Key visualizer. If we get latency or errors coming back from Bigtable, we look at the key visualizer first. We may have a hotkey or a wide row, and the visualizer will alert us and provide the exact key range where the key lives, or the row in question. Then we can go in and fix it at that level. It’s useful to know how your data is hitting Bigtable and if you’re using any anti-patterns or if your clients have changed their traffic pattern that exacerbated some issue.
• Garbage collection. We can prevent big row issues by putting size limits in place with the garbage collection policies.  
• Cell versioning. Bigtable has a 3d array, with rows, columns, and cells, which are all the different versions. You can make use of the versioning to get history of a particular value or to build a time series within one row. Getting a single row is very fast in Bigtable so as long as you can keep the data volume in check for that row, making use of cell versions is a very powerful and fast option. There are patterns in the docs that are quite useful and not immediately obvious. For example, one trick is to reverse your timestamps (MAXINT64 – now) so instead of the latest version, you can get the oldest version effectively reversing the cell version sorting if you need it.

Google Cloud and Bigtable help us meet the low-latency demands of the growing online retail sector, with speed and easy integration with other Google Cloud services like BigQuery. With their managed services, we freed up time to focus on innovations and meet the needs of bigger and bigger customers. 

Learn more about Ravelin and Bigtable, and check out our recent blog, How BIG is Cloud Bigtable?

Why do we need a BigQuery anti-pattern recognition tool?

BigQuery is a serverless and cost-effective enterprise data warehouse that works across cloud environments and scales with your data. As with any large scale data-intensive platform, following best practices and avoiding inefficient anti-patterns goes a long way in terms of performance and cost savings. 

Usually SQL optimization requires a significant time investment from engineers, who must read high-complexity queries, devise a variety of approaches to improve performance and efficiency, and test several optimization techniques. The best place to start is to fix anti-patterns, since this only requires easily applicable changes and provides significant performance improvements.

To facilitate the task of identifying and fixing said anti-pattern, Google Professional Services Organization (PSO) and Global Services Delivery (GSD) have developed a BigQuery anti-pattern recognition tool. This tool automates the process of scanning SQL queries, identifying antipatterns, and providing optimization recommendations.

What is the BigQuery anti-pattern recognition tool?

The BigQuery anti-pattern recognition tool let you easily identify performance impacting anti-patterns across a large number of SQL queries in a single go. 

It utilizes ZetaSQL to parse BigQuery SQL queries into abstract syntax trees (AST) and then traverses the tree nodes to detect the presence of anti-patterns. 

The tool takes a BigQuery SQL query as an input, such as:

SELECT
 t.dim1,
 t.metric1
FROM
 `dataset.table` t
WHERE
 t.id NOT IN (
 SELECT
   id
 FROM
   `dataset.table2`)

And produces the output as:

Subquery in filter without aggregation at line 8.

It examines potential optimizations, including:

• Selecting only the necessary columns
• Handling multiple WITH-clause references 
• Addressing subqueries in filters with aggregations
• Optimizing ORDER BY queries with LIMIT
• Enhancing string comparisons
• Improving JOIN patterns
• Avoiding subquery aggregation in the WHERE clause

The solution supports reading from various sources, such as:

• Command line 
• Local files
• Cloud Storage files
• Local folders
• Cloud Storage folders
• CSV (with one query per line)
• INFORMATION_SCHEMA

Additionally, the solution provides flexibility in writing output to different destinations, including:

• Printing to the terminal
• Exporting as CSV

Writing to a BigQuery table

Using the BigQuery anti-pattern recognition tool

The BigQuery anti-pattern recognition tool is hosted on GitHub. Below are the Quick Start steps on using the tool via command line for inline queries. You can also leverage Cloud Run to deploy it as a container on cloud. 

Prerequisites

• Linux OS
• JDK 11 or above is installed
• Maven
• Docker
• gcloud CLI

Quick start: – steps

1. Clone the repo into your local machine.

git clone git@github.com:GoogleCloudPlatform/bigquery-antipattern-recognition.git

2. Build the tool image inside the `bigquery-antipattern-recognition` folder.

mvn clean package jib:dockerBuild -DskipTests

3. Run the tool for a simple inline query.

docker run \
  -i bigquery-antipattern-recognition \
  --query "SELECT * FROM \`project.dataset.table1\`"

4. Below is the output result in the command-line interface:

Additionally, the above tool can read queries from Information Schema and load the output recommendations to a BigQuery table.

Below is an example of the BigQuery anti-pattern recognition tool results exported to a BigQuery table.

Getting started

Ready to start optimizing your BigQuery queries and cutting costs? Check out the tool here and contribute to the tool via GitHub.

Have questions or feedback?

We’re actively working on new features to make the tool as useful to our customers. Use it and tell us what you think! For product feedback/technical questions, reach out to us at bq-antipattern-eng@google.com. If you’re already a BigQuery customer and would like a briefing on the tool, please reach out, we’d be happy to talk.