MySQL to Cloud Spanner via HarbourBridge

Today we’re announcing that HarbourBridge—an open source toolkit that automates much of the manual work of evaluating and assessing Cloud Spanner—supports migrations from MySQL, in addition to existing support for PostgreSQL. This provides a zero-configuration path for MySQL users to try out Cloud Spanner. HarbourBridge bootstraps early stages of migration, and helps get you to the meaty issues as quickly as possible.

Core capabilities

At its core, HarbourBridge provides an automated workflow for loading the contents of an existing MySQL or PostgreSQL database into Spanner. It requires zero configuration—no manifests or data maps to write. Instead, it imports the source database, builds a Spanner schema, creates a new Spanner database populated with data from the source database, and generates a detailed assessment report. HarbourBridge can either import dump files (from mysqldump or pg_dump) or directly connect to the source database. It is intended for loading databases up to a few tens of GB for evaluation purposes, not full-scale migrations.

Bootstrap early-stage migration

HarbourBridge bootstraps early-stage migration to Spanner by using an existing MySQL or PostgreSQL source database to quickly get you running on Spanner. It generates an assessment report with an overall migration-fitness score for Spanner, a table-by-table analysis of type mappings and a list of features used in the source database that aren't supported by Spanner.

View HarbourBridge as a way to get up and running quickly, so you can focus on critical things like tuning performance and getting the most out of Spanner. You will need to tweak and enhance what HarbourBridge produces—more on that later.

Getting started

HarbourBridge can be used with the Cloud Spanner Emulator, or directly with a Cloud Spanner instance. The Emulator is a local, in-memory emulation of Spanner that implements the same APIs as Cloud Spanner’s production service, and allows you to try out Spanner’s functionality without creating a GCP Project. The HarbourBridge README contains a step-by-step quick-start guide for using the tool with a Cloud Spanner instance.

Together, HarbourBridge and the Cloud Spanner Emulator provide a lightweight, open source toolchain to experiment with Cloud Spanner. Moreover, when you want to proceed to performance testing and tuning, switching to a production Cloud Spanner instance is a simple configuration change.

To get started on using HarbourBridge with the Emulator, follow the Emulator instructions. In particular, start the Emulator using Docker and configure the SPANNER_EMULATOR_HOST environment variable (this tells the Cloud Spanner Client libraries to use the Emulator).

Next, install Go and configure the GOPATH environment variable if they are not already part of your environment. Now you can download and install HarbourBridge using

 

GO111MODULE=on \
go get github.com/cloudspannerecosystem/harbourbridge

It should be installed as $GOPATH/bin/harbourbridge. To use HarbourBridge on a MySQL database, run mysqldump and pipe its output to HarbourBridge

mysqldump <opts> db | $GOPATH/bin/harbourbridge -driver=mysqldump

where <opts> are the standard options you pass to mysqldump or mysql to specify host, port, etc., and db is the name of the database to dump.
Similarly, to use HarbourBridge on a PostgreSQL database, run

 pg_dump <opts> db | $GOPATH/bin/harbourbridge -driver=pg_dump

See the Troubleshooting guide if you run into any issues. In addition to creating a new Spanner database with data from the source database, HarbourBridge also generates a schema file, the assessment report, and a bad data file (if any data is dropped). See Files generated by HarbourBridge.

Sample dump files

If you don’t have ready access to a MySQL or PostgreSQL database, the HarbourBridge github repository has some samples. The files cart.mysqldump and cart.pg_dump contain mysqldump and pg_dump output for a very basic shopping cart application (just two tables, one for products and one for user carts). The files singers.mysqldump and singers.pg_dump contain mysqldump and pg_dump output for a version of the Cloud Spanner singers example. To use HarbourBridge on cart.mysqldump, download the file locally and run

$GOPATH/bin/harbourbridge -driver=mysqldump < cart.mysqldump

Next steps

The schema created by HarbourBridge provides a starting point for evaluation of Spanner. While it preserves much of the core structure of your MySQL or PostgreSQL schema, data types will be mapped based on the types supported by Spanner, and unsupported features will be dropped e.g. functions, sequences, procedures, triggers and views. See the assessment report as well as HarbourBridge’s Schema conversion documentation for details.

To test Spanner’s performance, you will need to switch from the Emulator to a Cloud Spanner instance. The HarbourBridge quick-start guide provides details of how to set up a Cloud Spanner instance. To have HarbourBridge use your Cloud Spanner instance instead of the Emulator, simply unset the SPANNER_EMULATOR_HOST environment variable (see the Emulator documentation for context).

To optimize your Spanner performance, carefully review choices of primary keys and indexes—see Keys and indexes. Note that HarbourBridge preserves primary keys from the source database but drops all other indexes. This means that the out-of-the-box performance you get from the schema created by HarbourBridge can be significantly impacted. If this is the case, add appropriate Secondary indexes. In addition, consider using Interleaved tables to optimize table layout and improve the performance of joins.

Recap

HarbourBridge is an open source toolkit for evaluating and assessing Cloud Spanner using an existing MySQL or PostgreSQL database. It automates many of the manual steps so that you can quickly get to important design, evaluation and performance issues, such as. refining choice of primary keys, tuning of indexes, and other optimizations.

We encourage you to try out HarbourBridge, send feedback, file issues, fork and modify the codebase, and send PRs for fixes and new functionality. We have big plans for HarbourBridge, including the addition of user-guided schema conversion (to customize type mappings and provide a guided exploration of indexing, primary key choices, and use of interleaved tables), as well as support for more databases. HarbourBridge is part of the Cloud Spanner Ecosystem, owned and maintained by the Cloud Spanner user community. It is not officially supported by Google as part of Cloud Spanner.

By Nevin Heintze, Cloud Spanner

Science Journal graduates from Google to Arduino

Science Journal is an open source mobile app that enables students in K-12 classrooms to conduct fun, hands-on science experiments using smart devices. Since its launch in May 2016, Science Journal has gone through quite a journey, from collaborating with rock stars to supporting classrooms, through an integration with Google Drive. Now we're pleased to share that Science Journal has graduated from Google and moved over to Arduino, the makers of the popular open source Arduino microcontrollers for students, hobbyists, and professionals around the world. Arduino Science Journal is free, open sourced, and available for download today on Android and iOS. 

We're thrilled to be handing the project to a partner that has a long history of supporting open source and education. The Arduino Science Kit for middle school students was developed in 2019 in partnership with Science Journal. The Arduino Science Journal Android source code and iOS source code, are already available on GitHub along with the Science Journal Arduino firmware. We've put a lot of time and energy into making Science Journal a great app for students and science enthusiasts everywhere, and we're confident that it will continue to thrive in its new home.

Although Google's Science Journal apps are still available on the App and Play Store today, these apps will no longer be supported after December 11, 2020, at which point Google Drive Syncing will stop working and Google's versions of the apps will no longer be available for download. However, existing Science Journal experiments can be exported from Google's Science Journal apps and imported into Arduino Science Journal at any time. You can find more information about this handoff in our Help Center article.

We see this change as a win for Google, Arduino, Science Journal, and for open source overall. Since Science Journal is an app for kids and schools, we wanted to be particularly careful with this transition. By supporting Arduino in releasing their own version of Science Journal and forking our code on GitHub, we were able to effectively hand off the project without transferring any user data or Intellectual Property. We hope this approach can serve as an effective model for future projects that need to reallocate their resources but don't want to let down their users (as we like to say: focus on the user, and all else will follow).

Moving forward, all future updates will be happening through Arduino's versions of the apps. You can stay up-to-date on the Arduino Science Journal website and experiment with their new hands-on activities, and if you have any questions, you can contact them on the Arduino Science Journal Forum.

Although the Science Journal project is moving on from Google, we still think data and scientific literacy are critically important for present and future generations, now more than ever. With the ubiquity of smart devices in classrooms and at home, we think Science Journal remains the perfect solution for parents and teachers looking to provide students with hands-on learning opportunities during this time period. We hope you enjoy using Science Journal as much as we have, and we're excited to see how the project will continue to evolve moving forward.

By Maia Deutsch and the Science Journal Team

Recreating historical streetscapes using deep learning and crowdsourcing

For many, gazing at an old photo of a city can evoke feelings of both nostalgia and wonder. We have Google Street View for places in the present day, but what about places in the past? What was it like to walk through Manhattan in the 1940s? To create a rewarding “time travel” experience for both research and entertainment purposes, Google Research is launching Kartta Labs, an open source, scalable system on Google Cloud and Kubernetes that tackles the difficult problem of reconstructing what cities looked like in the past from scarce historical maps and photos.

Kartta Labs consists of three main parts:

• A temporal map server, which shows how maps change over time;
• A crowdsourcing platform, which allows users to upload historical maps of cities, georectify, and vectorize them (i.e. match them to real world coordinates);
• And an upcoming 3D experience platform, which runs on top of maps creating the 3D experience by using deep learning to reconstruct buildings in 3D from limited historical images and maps data.

Maps & Crowdsourcing

Kartta Labs is a growing suite of open source tools that work together to create a map server with a time dimension, allowing users to populate the service with historically accurate data.

Warper

The entry point to crowdsourcing is Warper, an open source web app based on MapWarper that allows users to upload historical images of maps and georectify them by finding control points on the historical map and corresponding points on a base map.

Once a user uploads a scanned historical map, Warper makes a best guess of the map’s geolocation by extracting textual information from the map. This initial guess is used to place the map roughly in its location and allow the user to georeference the map pixels by placing pairs of control points on the historical map and a reference map. Given the georeferenced points, the application warps the image such that it aligns well with the reference map.

Warper runs as a Ruby on Rails application using a number of open source geospatial libraries and technologies, including but not limited to PostGIS and GDAL. The resulting maps can be exported in PNG, GeoTIFF, and other open formats. Warper also runs a raster tiles server that serves each georectified map at a tile URL. This raster tile server is used to load the georectified map as a background in the Editor application that is described next.

Editor

Editor is an open source web application which is a customized version of the OpenStreetMap editor; customizations include support for time dimension and integration with the other tools in the Kartta Labs suite. Editor allows users to load the georectified historical maps and trace their geographic features (e.g., building footprints, roads, etc.). This traced data is stored in vector format.

Extracted geometries in vector format, as well as metadata (e.g., address, name, and start or end dates), are stored in a geospatial database that can be queried, edited, styled, and rendered into new maps.

Kartta

Finally, the temporal map front end, Kartta (based on Tegola), visualizes the vector tiles allowing the users to navigate historical maps in space and time. Kartta works like any familiar map application (such as Google Maps), but also has a time slider so the user can choose the year at which they want to see the map. By moving the time slider, the user is able to see how features in the map, such as buildings and roads, changes over time.

3D Experience

To actually create the “time traveling” 3D experience, the forthcoming 3D Models module aims to reconstruct the detailed full 3D structures of historical buildings. The module will associate images with maps data, organize these 3D models properly in one repository, and render them on the historical maps with a time dimension.

Preliminary Results

Figure 2 – Bird’s eye view of 3D-reconstructed  Chelsea, Manhattan with a time slider

Figure 3 – Street level view of 3D-reconstructed Chelsea, Manhattan

Conclusion

We developed the tools outlined above to facilitate crowdsourcing and tackle the main challenge of insufficient historical data. We hope Kartta Labs acts as a nexus for an active community of developers, map enthusiasts, and casual users that not only utilizes our historical datasets and open source code, but actively contributes to both. The launch of our implementation of the Kartta Labs suite is imminent—keep an eye out on the Google AI blog for that announcement!

By Raimondas Kiveris – Google Research