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.

Editor’s note: Today’s post is written by Hatim M, Chief Commercial Officer at BungkusIT. The on-demand delivery service delivers packages within the hour for one million customers across Malaysia and uses Google Maps Platform to create a seamless end-to-end delivery experience for its customers.

Imagine it’s the end of a long day at work, and you’re heading home for dinner as you look forward to a restful evening. Then just as you arrive at the doorstep, your phone buzzes with a message from a family member, asking if you could grab a carton of milk from the supermarket.

With moments like these in mind, we founded BungkusIT to alleviate the stress of everyday chores and give people a break from having to run seemingly mundane errands that can often be time-consuming. With so much to do and so few hours in a day, we want to make life simpler by providing our customers with an on-demand delivery service.

As a social enterprise, our mission is to create jobs and increase earning potential for BungkusIT roadies (those who run deliveries), while also increasing online visibility for  local small and medium enterprises through an e-commerce platform. As such, we don’t charge merchants a fee for being featured on our app. 

Our roadies help connect users with products and services that they need. Whether it’s picking up the keys you forgot, or buying a birthday cake for a loved one, our on-demand roadies can help accomplish your task quickly and efficiently. In fact, we’ve helped many seniors—a large and often underserved population—to run errands and provide last mile support.

Providing reliable service, 24/7

From the day we launched, our promise has always been to complete every task given to us within one hour. Google Maps Platform empowers us to fulfil this promise. We understand the importance of selecting a reliable mapping service from the get-go. After exploring different map products, we made the decision to go with Google Maps Platform due to its detailed and accurate location mapping system. With the support of our partner Searce, we’ve been able to quickly integrate all the relevant products on our platform and optimize our API calls.

On a day-to-day basis, our roadies rely on the Places API to navigate their journeys. It pinpoints the exact location set by customers so that our roadies can be sure they arrive at the correct destination. With so many small roads and alleys in Malaysia, it can be challenging to find the exact place based on an address. Dropping a pin on a specific location removes any confusion.

Because we price our services based on distance, it’s business critical to determine the exact distance of a location and the most efficient route to reach it. By automating that process with the Distance Matrix API, we can guarantee that customers don’t get overcharged, while making sure that we’re adequately compensated for our services.

Improving the user experience

At the end of the day, we want to make people’s lives easier. That’s why having an app that is intuitive is key. More than that, we want customers to be able to create a request as quickly as possible. By automatically completing the location a customer is typing after they’ve keyed in the first few characters, Place Autocomplete does that job for us. 

As a fairly young company, we’ve got a long way to go. I believe that we’ll continue evolving alongside Google Maps Platform and exploring the new features it has to offer. We’re already talking to global brands to expand our services and I’m confident that with Google Maps Platform as our maps partner, we will go a long way.

For more information on Google Maps Platform, visit our website.

In previous blog posts, we’ve discussed the benefits that organizations can reap by measuring and then optimizing their overall ESG impact. In this post, we’ll look at the endgame: sustainable transformation into an organization that continually adapts to maximize its resilience while reducing risk. Where will my customers be with the evolving global community landscape? How can I design communities and products with sustainability in mind? Are my assets exposed to physical climate risk — drought, flood, wildfire? Adaptation and resilience are the board and legislative focus areas that the ambition loops created by Google Cloud Ready – Sustainability seek to accelerate. 

This is especially important due to the scrutiny of governments and financial institutions as they evaluate the viability of physical asset investments. These organizations want assurances that their investments will not be under undue environmental risk. Similarly, companies embarking on net-zero transitions can realize interest expense savings by providing the measurable, accountable assurances their financiers demand.

Leveraging analytics for climate and business resilience

We’ve said it before, but it bears repeating: The Google Cloud Ready – Sustainability program challenges the notion that sustainability is just a nice-to-have or merely good PR. Rather, we designed the program to help organizations adapt to a net-zero future with solutions that reduce risk, support growth, provide competitive advantages, and positively impact the bottom line. The following partners provide data and analytics that offer deep insights into the environment, natural resources, and social trends that point the way toward sustainable, profitable growth. 

Atlas AI has built a geospatial artificial intelligence platform that helps every organization anticipate changing societal conditions — where people live, where wealth and poverty are concentrated, how the physical makeup of communities is evolving, and more — to determine where to invest today to prepare for the world of tomorrow. Atlas AI customers, which range from the World Bank and global NGOs to multinational corporations, use the platform to accelerate growth, future-proof supply chains, target resources to vulnerable populations, and build greater resilience to climate change.  

Climate Engine’s SpatiaFi solution, powered by Google Cloud, helps financial services organizations connect economic assets to the world of Earth observation data supporting regulatory reporting, climate risk reduction, and new innovative sustainable finance offerings. Combining billions of planetary observations with peer-reviewed scientific methods, SpatiaFi is connecting Earth science to operational systems offering new levels of accuracy, transparency, scalability, and trust in climate and sustainability data in the context of financial decision-making.

Kumi Analytics‘ KACSAT solution was approved by OxCarbon as a methodology for the calculation and verification of voluntary carbon offsets in support of nature-based solutions. KACSAT leverages multiple satellite platforms and Google Earth Engine‘s state-of-the-art processing tools to enable science-based carbon assessment aligned to the Oxford Offsetting Principles of environmental integrity, additionality, permanence, and achieving net-zero emissions. Environmental organizations, financial institutions, and corporations can now offset their carbon footprints through the reforestation and conservation of millions of hectares of forests — something that was previously limited due to the cost and time of validating high-quality forest projects.

Tensorflight revolutionizes property underwriting with AI. Its cutting-edge tool provides underwriters and insurers with access to rich and highly accurate datasets for commercial and residential properties, enabling them to create superior insurance products. As pioneers of the first property-inspection platform based on convolutional neural networks, Tensorflight’s advanced technology connects seamlessly via API and utilizes ground-level imagery, satellite, and aerial data. This unique approach enables the company to generate precise replacement costs and conduct more comprehensive risk assessments. By automating property inspections and enhancing underwriting accuracy, Tensorflight empowers insurance providers to plan for the future with pragmatic decision-making. With Tensorflight, the future of property underwriting is transformed, offering unparalleled insights, efficiency, and sustainability.

Are you ready for sustainability transformation?

Transforming into a net-zero organization means much more than building resilience to climate change, as important as that is. It also means finding the customers of the future, achieving ongoing energy savings, more sustainable resource usage, and improved transparency to regulatory institutions and consumers. Google Cloud Ready – Sustainability partners offer certified solutions that can help organizations get there.

Look for Google Cloud Ready – Sustainability validated solutions on the Google Cloud Partner Directory Listing, Google Cloud Ready Sustainability Partner Advantage page, and — if applicable — via the Google Cloud Marketplace. We hope to help customers better understand how these technologies can help them meet their ESG goals, find the right solutions for their particular challenges, and implement solutions faster. 

Learn more about the Google Cloud Ready – Sustainability validation initiative and explore all our partners in sustainability transformation.