From data centers and cloud regions to subsea cables, Google is committed to connecting the world. Our investments in infrastructure aim to further improve our network—one of the world’s largest—which helps improve global connectivity, supporting  users and Google Cloud customers. Our subsea cables play a starring role in this work, linking up cloud infrastructure that includes more than 100 network edge locations and over 7,500 edge caching nodes. 

As it turns out, readers of this blog seem to find what happens under the sea just as fascinating as what’s going on in the cloud. Posts on our cables are consistently among our most popular, which is why we brought them together for you here so you can take a deeper dive (pun intended).

Here’s a list our most popular posts on our underwater infrastructure:

2021

• Hola, South America! Announcing the Firmina subsea cable
• This bears repeating: Introducing the Echo subsea cable
• The Dunant subsea cable, connecting the US and mainland Europe, is ready for service

2020

• Announcing the Grace Hopper subsea cable, linking the U.S., U.K. and Spain

2019

• Introducing Equiano, a subsea cable from Portugal to South Africa
• A quick hop across the pond: Supercharging the Dunant subsea cable with SDM technology
• Curie subsea cable set to transmit to Chile, with a pit stop to Panama

2018

• Expanding our cloud network for a faster, more reliable experience between Australia and Southeast Asia
• Delivering increased connectivity with our first private trans-Atlantic subsea cable

2017

• Google invests in INDIGO undersea cable to improve cloud infrastructure in Southeast Asia

2016

• New undersea cable expands capacity for Google APAC customers and users
• Google Cloud customers run at the speed of light with new FASTER undersea pipe
• A journey to the bottom of the internet

Our cable systems provide the speed, capacity and reliability Google is known for worldwide, and at Google Cloud, our customers can make use of the same network infrastructure that powers Google’s own services. To learn more, you can view our network on a map, or read more about our network.

Founded in 1853, clothing company Levi Strauss & Co. gained fame for their Levi’s® denim jeans. Over 150 years later, Levi’s is a globally-recognized brand that sells jeans and other apparel in more than 3,100 retail stores across 110 countries.

The summary

During the COVID-19 pandemic, Levi’s saw that their shoppers were investing more time in pre-purchase research. They realized that being able to provide customers with information around the clock in an easily accessible way could greatly improve interest, engagement, and eventually purchase behavior. In June 2020, Levi’s partnered with customer experience automation provider Linc Global to integrate Google’s Business Messages into Levi’s customer experience (CX) strategy and cross-channel chat experiences. Business Messages soon brought in customer satisfaction (CSAT) scores of over 85 percent, and drove 30x more store-related questions than Levi’s onsite web chat. In addition, most Business Messages conversations occurred outside store hours, allowing Levi’s to respond to inquiries more quickly.

The challenge

Levi’s knows customers want to drive these interactions on their own terms and timelines— and on their own preferred communications channel. With store hours often impacted by the pandemic, the company needed 24/7 service to answer the 30% increase in off-hours shoppers. Google’s Business Messages allowed customers to connect with Levi’s without visiting the website, going to a store or making a phone call. Anisa Kumar, VP Global DTC says, “Rolling out CX automation on web chat provided a new way for customers to find answers to their pre purchase and other questions that regularly resulted in a call to customer service. Launching with Google’s Business Messages was the logical next step in providing our customers with a best-in-class customer experience.”

The approach

Levi’s worked with Linc Global, a CX automation platform, to set up Google’s Business Messages on Google Maps for five, then 50, stores, before expanding to more than 200 stores. “Customers can message us through Google Maps even after stores are closed. They can search for products and see product information and availability at their local store, plus get details about hours and COVID-19 related updates to make shopping easier,” said Kumar. The company then added Business Messages on Google Search for a rich messaging experience that synchronized with their regular webchat channel. “Consumers can have their entire messaging experience be on Google—and then visit our website or a physical store to complete a purchase recommended to them in Business Messages,” says Rico Arrastia, VP Product & Experience at Levi Strauss & Co.

The results

Two months after launching Google’s Business Messages, Levi’s CSAT scores for the channel surpassed 85%. And four months after launch, Levi’s found that this new channel drove 30x more store-related questions than web chat.

Additionally, Levi’s noted that 31% of Business Messages conversations were product-related, indicating that shoppers preferred targeted engagement to unstructured browsing. For Levi’s, Business Messages helps cross-sell or suggest products to customers based on an individual’s past purchases, personal information, and behaviors.

Levi’s is now a big proponent of Business Messages as a way to meaningfully engage with customers and stand out from other brands. Arrastia shares, “Business Messages not only creates a stronger customer experience but also enables us to do a much better job at ‘hearing’ shoppers.”

Buffalo Tours serves tons of passengers each month, helping them with various aspects of their travel. These involve tour booking requests from passengers, request processing and information checking. Prior to the Google implementation, Buffalo Tour’s email exchange was supported by another email server. Employees lamented the frequent loss of emails and the ineffective spam filtering system, which resulted in legitimate emails from customers being treated as junk mail. Prior to adopting Gmail, the trend of lost emails or failed delivery accounted for 5% of the business. This means that five out of every 100 incoming requests to Buffalo Tours were lost.

“We are in the business of servicing customers and when missing emails become a regularity, it damages our company’s credibility and image, not to mention a loss of company revenue. Internal communication with the various representative offices is mostly conducted online, and lost emails inhibits workplace collaboration,” said Bui Anh Tuan, IT manager of Buffalo Tours.

Managing the email server was also a challenge as the server was hosted by a network operator but located on-premise. Due to the logistical challenges, each time the server failed, it took some time before the server could be rebooted. The complex system configuration also meant it was not easy to get new employees on board.

Gmail emerged as a viable option to replace Buffalo Tour’s existing email server as it was cloud-based and user-friendly. At the start, Buffalo Tours made use of the free Gmail service for business. Happy with the results, Buffalo Tours decided to switch to G Suite on a permanent basis.

“G Suite has enabled us to manage our emails in a more professional manner. This means improved work efficiency and better customer service. Furthermore, it also offers many other essential services that benefit the work that we do,” said Tuan.

Many employees at Buffalo Tours have been utilizing Gmail as their main communication tool. Buffalo Tours is looking at ways to further utilize the other products within G Suite such as Google Sites, Google Docs, and Google Drive.

Since making the switch to Gmail, the company does not have to bear the cost of buying and maintaining a physical server, resulting in cost savings of around 17% annually. The rate of lost emails is now down to almost zero, which is the icing on the cake for Buffalo Tours.

One of the distinct advantages of Gmail is its impressive spam filter capabilities. For the premium version, this service is enhanced with Blacklist and Whitelist features, making the spam filter even more effective. With the enhanced control features, business and junk mail are easily categorised.

“Our staff can easily input the IDs of the entire office and regular customers to the Whitelist. With a click of a mouse, important business emails are never missed. There is a 10-15% increase in employee productivity now that the issue of lost emails is resolved,” Tuan commented. Gmail has also brought about increased convenience to Buffalo Tours. G Suite is easy to configure for any new employee. All employees need is their username, password and an Internet connection to check emails. What used to take a lot of time and resources is now a simple administration process.

With the current blooming mobility trend, Gmail provides Buffalo Tours with the flexibility to empower employees to work more efficiently. Employees can now access their work emails on their personal smartphones, tablets or other mobile devices. “Since we switched to G Suite, there have been zero customer complaints around the inability to contact the sales division. Meanwhile, our business continues to prosper and our yearly bookings increased by 1.5%. Together with Google, we are confident of our continued growth,” concluded Tuan.

In the last two decades, BHI, once a small construction company in Vernal, Utah, has expanded its operations to dozens of industries in over 25 states. But while the company grew, its technology trailed behind. File sharing and emails both ran off a single server. Editing a document was a grueling undertaking that required an employee to VPN in, patiently download the file, edit it, then upload it again—all from a job site with a limited internet connection. Acknowledging the need for better technology, BHI adopted Google Workspace and AppSheet and transformed its business to be more innovative, productive, and profitable.

Rebuilding collaboration and productivity

BHI first looked to replace its archaic email and file-sharing systems. They found that Google Workspace—as a proven and popular collaboration solution for remote teams and a familiar, preferred platform that most employees use in their personal lives—was the clear choice. BHI migrated its 500 employees to Google Workspace in less than a week. Productivity immediately soared, since employees could collaborate more easily with Google Workspace’s products and access them from any device, from anywhere—even at the construction sites.

For instance, the contract writing process drastically improved with Google Workspace. Version control had always been a struggle—as employees circulated a draft contract from one computer to the next, versions became unruly and randomized, untracked changes popped up, and files went missing. With Google Workspace, the pre-construction, legal, and finance teams can all edit and track changes of a contract from wherever they work. Version control has become a thing of the past, as everyone is always working on the latest version.

By adopting Google Workspace, BHI decentralized its operations and began collaborating in real time across over 150 job sites. Management and IT gained new insights into challenges that they may never have uncovered nor confronted without a cloud-based productivity tool. The IT team also saw an opportunity to overhaul the methods of their deskless workforce, which accounts for 90% of the company. This deskless majority needed to connect directly to the data that influenced, impacted, and comprised their work. They also required more streamlined processes. AppSheet provided the answer to the workforce’s needs, enabling IT to create applications that simplified key tasks so mobile workers could get their jobs done faster and better.

For example, one BHI client required extensive inspections multiple times a day. Before using AppSheet, a BHI inspector would perform the inspection, return to a computer, record the results from paper to a spreadsheet, copy and paste them into a formatted report in a Google Docs, convert the Doc to a PDF, and email the final inspection report to the customer. This manual process took two hours to complete. Now, using AppSheet, the inspector inputs the results on their mobile device while performing the inspection. As soon as the device connects to the internet, it automatically updates the database with the inspector’s inputs, adds the inspection results to a preformatted report built-in Google Docs, and emails the final report as a PDF to the customer. With AppSheet, BHI shortened this inspection process from two hours to six minutes, saving over $50,000 a month in labor. “With Google Workspace and AppSheet, BHI has turned into a digital company with real-time data allowing for real-time decisions, collaboration, and transparency in a decentralized environment,” says Johnny Hacking, Director of IT.

An easier software choice for faster results

BHI also found that building apps with AppSheet is much easier than using other solutions, such as developing with low- or full-code tools or purchasing third-party products. Because AppSheet is a true no-code platform, anyone can learn to build and maintain apps. Since adopting AppSheet, the BHI IT team has become so proficient with the platform that they have eliminated the company’s previous backlog of software needs. Now, when a new site opens up, IT proactively works with the site managers to understand its unique requirements in order to build a custom app. Start to finish, scoping, and building this site-specific app typically takes less than a week—compared to months with traditional development platforms. 

IT team members aren’t the only BHI employees building apps. As Hacking explains, “We have people that aren’t in IT, and when they have an idea we say hey, log into AppSheet, build it out, and then we’ll help out with some final touches on it.” 

Empowering employees outside of IT to build AppSheet apps has benefited teams across the company. For example, one team on a solar farm construction project needed to perform inspections of an array of specialized technical equipment with which IT was not familiar. In this case, the solar team, being the subject matter experts, simply built the app according to their specifications. All IT had to do was jump in at the end to help finalize the app—the final product was completely customized to the solar team’s very specific needs. This highlights the benefits of no-code development: on-the-ground experts build and prototype job-specific apps and IT puts the finishing touches on the functionality, all while keeping guardrails in place.

Using Google Workspace and AppSheet also simplifies BHI’s reliance on third-party software. “Our company divisions are so diverse that they each require different software,” says Hacking. “Plus, if we buy a third-party software, we still have to customize it by getting other software, because they don’t play well together. By replacing this software with AppSheet apps, we save a lot of money. And it’s easy to integrate those apps because on the back end, they’re all connected to Google Sheets.” 

For the few third-party software products that BHI hasn’t been able to replace, they’ve used Apps Scripts to import the software’s data into Google Sheets, so that their data all lives on one platform. Overall, BHI has been able to free up 10% of its total IT spend by replacing third-party software with Google Workspace and AppSheet apps.

Company culture transformed by technology

“Because we’ve used Google Workspace and AppSheet and it’s been so successful, people are very willing to use them,” Hacking says. “And for a construction company to gain so much trust in these platforms in such a short period of time—that’s huge. Now we’re at the point where employees are asking, ‘What else can we do?’”

Adopting Google Workspace and AppSheet has not only made BHI more productive, but also transformed the company culture. Employees feel empowered to be more innovative and are constantly seeking ways to improve. Simplifying employees’ work has led to a direct boost in morale. 

The role of IT has also transformed. As Hacking explains, “In just three years of using these technologies, IT has gone from being support overhead to being proactively brought to the table to take part in the business strategy.” Increased IT visibility and culpability has helped solve business problems and led to increased profitability. 

BHI continues to find new ways to leverage Google Workspace and AppSheet to improve business. In response to COVID, they created a timesheet app that allows employees to clock in without touching a device. Most meetings are now held virtually using Google Meet. Fleet maintenance and repairs are all managed with apps. And, of course, the company has better data and insights than ever. As Hacking puts it, “We’re continuing to stay on the cusp of innovation because of Google Workspace and AppSheet.”

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.