Today we’re announcing the HarbourBridge Schema Assistant, which provides a guided schema-design workflow for migrating from MySQL or PostgreSQL to Spanner. HarbourBridge imports dump files (from mysqldump or pg_dump) or directly connects to your source database, and converts the source database schema to an equivalent Spanner schema. The new Schema Assistant capability displays the source schema and Spanner schema side-by-side, highlights errors and walks you through a series of steps to validate and optimize your Spanner schema. It also produces a browsable assessment report with an overall migration-fitness score for Spanner, a table-by-table detailed analysis of type mappings and a list of features used in the source database that aren’t supported by Spanner. It supports editing of table and column names, column types, primary keys and constraints, as well as dropping of tables, columns, foreign keys and secondary indexes.

The new Schema Assistant complements HarbourBridge’s existing data and schema migration capabilities and is a critical step towards our goal of building a complete open-source migration toolkit. HarbourBridge continues to support command-line schema and data migration and turn-key Spanner evaluation.

Complementing the bulk data migration capabilities of HarbourBridge, we are also announcing the ability to migrate change events from MySQL to Cloud Spanner.

Supported Features in Schema Assistant

1. Global type mapping. Users can customize the global mapping for how types should be mapped to Spanner consistently across the schema. For example, mapping large integers in source schema to Spanner’s NUMERIC.
2. Local type mapping. Users can override the custom type mapping for a given table/column.
3. Session management. A session keeps track of all the changes made to the schema mapping.
4. Customization of secondary indexes. Users can add, edit and delete secondary indexes to optimize their Spanner performance.
5. Customization of foreign keys and interleaved tables. Table interleaving is an important design consideration when migrating to Cloud Spanner as explained in more detail in this blog post.

Features in the pipeline

We are already working to further expand the supported set of schema editing features and welcome your feedback. We are particularly excited to expand the Schema Assistant’s design recommendations for optimizing Spanner schemas e.g. in-depth recommendations for primary key design.

HarbourBridge is open source and we gladly accept contributions from the wider community.

Operating for 23 years, Rustomjee has carved a niche for itself in the ever-growing real estate sector. Rustomjee’s portfolio includes 14.32 million square feet of completed projects; 12 million square feet of ongoing development; and another 28 million square feet of planned development. These projects span the best locations of the Mumbai Metropolitan Region. Rustomjee adds value to the lives of its homeowners through its core business, its corporate social responsibility initiatives, and its philanthropy. The business strives to ensure that every blueprint includes child-friendly spaces for parks, playgrounds, and learning rooms, encouraging families to spend quality time with each other.

In the 17 years Rustomjee’s Corporate Head of Information Systems, V M Samir, has worked with the business, it has grown from 100 employees to more than 800 employees – primarily professionals such as engineers, architects, lawyers, accountants, and regulatory consultants. The remainder work in marketing, sales, administration, security, human resources, and other associated areas.

Historically, Samir says, the real estate industry globally has lagged in embracing new workplace technologies, making building business cases, securing sponsorship, completing implementations, and encouraging adoption a difficult task.

G Suite powers cloud journey

Rustomjee started operations running an on-premises email service. However, the business wanted to upgrade its anti-spam capabilities and, in 2007, turned to G Suite. “There was no practical way I could build an anti-spam engine within that service that could match the power of the G Suite anti-spam engine and the intelligence held within its databases,” says Samir. “Our second key reason for moving was the lower cost of G Suite relative to an on-premises service that required us to spend on compute, backup, storage, and administration resources. Running G Suite would also help ensure users could still access their emails if their machines experienced an outage.”

Finally, G Suite enabled Rustomjee to access and compose emails from any location – a luxury for a real estate organization whose workers often attended construction projects with poor connectivity. “Finally, G Suite was the only service in those days that integrated calendar, meeting, and storage repositories through single sign-on. Google was so far ahead of the curve at that time and we saw the potential of G Suite to transform our communications and ultimately our business.”

“We did not have to shut down the business operations during the migration because Google Cloud Platform complemented every idea we had. And when my business users came to work on the Monday morning [after the final migration], everything was stable and the performance had improved. We told them our infrastructure had changed and they should start thanking Google Cloud!”

—V M Samir, Corporate Head, Information Systems, Rustomjee

The business started its cloud and IT modernization journey by decommissioning its on-premises email servers and using G Suite for Business. It began testing in March 2007 and went live with all production email services for 500 users in June 2008.

The intuitive nature and ease of use of G Suite made the transition seamless. “I did not have to undertake a large-scale change management exercise,” says Samir. “Users bought into the program and acquired the necessary knowledge quickly, meaning our adoption rate was extremely fast.”

With G Suite, email became the new norm to complete a range of tasks at Rustomjee. “We started exchanging CAD drawings, videos, high-resolution images, and other large files with our consultants,” says Samir. “These files had been difficult to store in on-premises environments.”

Rustomjee has improved collaboration and performance with G Suite, primarily due to four services. Gmail enables workers to communicate seamlessly externally and with each other, while Calendar enables them to set up and synchronize meetings. These meetings can be conducted through Hangouts Meet. “If a group of people internally need to discuss a work order or contract, they can coordinate calendars, sit at different locations within our organization, and collaborate using audio and video on a common service,” says Samir. “They can also work from and update a single document in Drive.”

With email and other collaboration applications running smoothly, Rustomjee saw an opportunity to enhance its technology infrastructure. The business had started operations running workplace applications on servers in small air-conditioned rooms. These servers ran databases and applications that sent data across a network to endpoints including desktops and laptops.

Expansion and the changing demands of workplace technology prompted Rustomjee to upgrade its capabilities, and the business commissioned a data center. However, Rustomjee’s continuously fast-growing compute and storage requirements, as well as the need to access new technologies to innovate and compete, quickly strained its technology model. “We required eight weeks each time we needed to add new compute and storage to our environment,” says Samir. “In addition, the heavy investments in our captive data center meant we were unable to easily leverage new technologies and decommission old technologies.”

Public cloud supports growth

Rustomjee reviewed its options and decided public cloud services could best meet its ongoing needs. “The rising cost of maintaining old hardware would force us to refresh data center technologies every five to seven years,” says Samir. “More broadly, a hardware-defined data center could not adapt quickly in the cloud-computing world, making it difficult to align compute with growth.”

The business started by moving its corporate website and microsites to a public cloud service to accommodate increased traffic delivered from a change in business strategy. “We saw an opportunity to shift our marketing and advertising from print advertising to digital platforms,” says Samir. “We wanted to be available to buyers from the point at which they start looking at properties online.”

However, Rustomjee’s digital marketing teams found it difficult to scale instances in line with demand, and work with complex user administration screens and control panels.

“With Google Cloud Platform, we’ve achieved a considerable reduction in timelines and simplified technology management and administration. All of our business users are delighted.”

—V M Samir, Corporate Head, Information Systems, Rustomjee

Google Cloud Platform presented an opportunity for Rustomjee to run its websites and microsites in a reliable, scalable, and responsive infrastructure. “Our evaluation confirmed Google Cloud Platform could support our growing demand for compute and storage,” says Samir. ‘In addition, because we undertake projects that are geographically distributed, networking – not only within the data center, but that could be accessed from any location – was very important. Only Google Cloud Platform had its own networking infrastructure.”

“More broadly, in a cloud environment, we could select any database we needed and start consuming operating systems as a service,” he adds. “In addition, we did not have to invest capital in licenses and hardware and we could always resize network bandwidth. Further, we could take advantage of pay-as-we-use cloud services, and link this to business growth.”

Google Cloud Platform also allowed Rustomjee to reduce the size of the teams needed to manage and operate its infrastructure. “When you have infrastructure running in the data center and in disaster recovery locations, you need to have a large pool of resources working around the clock to ensure constant uptime, sound backup, and good replication measures in place,” says Samir. “With the cloud, everything is simplified and we do not have to consider issues like how many hard drives have failed in on a particular morning.”

The business started testing and created its first virtual machine instance to Google Cloud Platform in April 2017. Because the Mumbai data center was not yet operating Rustomjee initially hosted its instances in the Google Cloud Platform Singapore Region. This initial move enabled the business to reduce the number of required virtual machine instances from six to one. After a year of running the website on Google Cloud Platform, the business found the simplicity of scaling compute resources meant its digital marketing teams were running more campaigns and servicing more leads, while compute spend had fallen by 56 percent. Furthermore, Rustomjee was not experiencing latency that impacted the user experience.

The opening of the Google Cloud Platform Region in Mumbai in late 2017 gave Rustomjee an opportunity to use compute, storage, and networking services located domestically. The business decided to go all-in on Google Cloud Platform and migrated a range of applications to the service.

These included an SAP enterprise resource planning system, running initially on Oracle but moved to the SAP HANA in-memory computing platform, with the assistance of advisory consultants from a leading firm. This system is integral to Rustomjee’s successful operations, meaning it has to be highly available and responsive. “All our financials are captured and stored in this system, as well as projects, materials management, order management, financial systems, and sales ordering systems, both procurement and supply-side,” says Samir. “In short, we cannot live without SAP ERP!”

Rustomjee turned to SAP specialist partner InfraBeat Technologies, a leading network implementation provider, and Google engineers to complete the migration successfully. “We were all in this together and collaborated closely to deliver the project successfully,” says Samir. “We were very impressed by the expertise and skills of everyone involved.”

A nine-day migration

With assistance from Google Cloud and the partners, Rustomjee moved all its SAP workloads, from sandbox to development, development to quality, and quality to production, in just nine calendar days, without impacting the business. “We did not have to shut down the business operations during the migration because Google Cloud Platform complemented every idea we had,” says Samir. “And when my business users came to work on the Monday morning [after the final migration], everything was stable and performance had improved. We told them our infrastructure had changed and they should start thanking Google Cloud!”

Moving to Google Cloud Platform enabled the business to complete certain customer invoicing workloads in just two hours, down from 13 hours previously. Backups of the SAP enterprise resource planning system that had taken up to six hours, were now being completed in six minutes.

“We have now fully embraced a cloud-first approach and have moved 100 percent of our workloads to the cloud. We depend totally on Google Cloud Platform to run our business.”

—V M Samir, Corporate Head, Information Systems, Rustomjee

Eight weeks down to 20 minutes

Rustomjee has also cut the eight-week cycles needed to set up the infrastructure and applications for a new real estate project, and integrate it into the SAP system, to just 20 minutes. “With Google Cloud Platform, we’ve achieved a considerable reduction in timelines and simplified management and administration,” says Samir. “All of our business users are delighted.”

With its website and enterprise resource planning system running successfully on Google Cloud Platform, the business decided to move its virtual application delivery environment into the service. “We decided to move all 800 of our employees into the cloud, so they could access applications through any endpoint, be it a mobile phone, tablet, thin client, desktop, or laptop,” says Samir.

The business decided to replace an existing application virtualization product with public images available on Compute Engine, with testing of Android, iOS and Windows operating systems across a range of devices proving highly successful. “Running the remote desktop service in Compute Engine and using public images enabled us to operate a leaner, simplified desktop as a service environment,” says Samir. “We have been able to deploy business function-specific virtual machine instances on the cloud and compartmentalize data to the business functions that need them. The business completed the move – including 8.1TB of data – in just six weeks. “The only workloads we did not move during that period were intensive workloads for visualization,” says Samir.

However, the four teams that did not use the remote desktop as a service environment – and that created visualization-intensive workloads – were working in traditional ways, compromising productivity and increasing risk. “They used to be given a desk in their respective offices and their workstations loaded with the applications,” says Samir. “There was no way of taking hourly backups of these devices and there was no recovery mechanism for the applications, because they were not in the data center.

“The other challenge was what happens to the data, because we operate out of multiple locations. The users in these four teams always had to come back to pre-assigned desks and continue working from there. This was counterproductive.”

The business subsequently implemented virtual machine instances with GPU access for visualization-intensive workloads. “We started evaluating Nvidia T4 (Tesla) GPUs in the Mumbai Region on 13 February and went live on 11 March in Mumbai,” says Samir. “By moving these 40 team members to Google Cloud Platform, we have enabled them to work with visualization workloads from any location, improving productivity and flexibility.”

Focus on core business

With Google Cloud Platform, Rustomjee can focus on running real estate projects and using technology to enable them. “I don’t have to refresh my technology every five to seven years, and I can leverage new technologies as they come on stream,” says Samir.

Reliable application access

With the flexibility and scalability of Google Cloud Platform, Rustomjee is now well-positioned to support further growth and operate in accordance with the time-limited nature of the real estate industry in India. “If we have promised to hand over the keys to a customer by a certain deadline and we fail to do so, we face a penalty of 10 percent of the cost of the project,” explains Samir. “The Indian Government introduced this regime in 2017 and business and technology have to align with its requirements.

“Google Cloud Platform is the right service to move us forward and enable us to overcome these challenges,” he adds. “We have now fully embraced a cloud-first approach and have moved 100 percent of our workloads to the cloud. We depend totally on Google Cloud Platform to run our business.”

At 340 square miles, the City of Memphis is among the largest in the United States in terms of land area. Memphis has over 6,800 lane-miles of city streets, enough to drive back and forth to Los Angeles four times. Keeping these streets well maintained and safe for citizens and visitors is a major priority for the city.

Lots of traffic, lots of roads, and a four-season climate prone to wintertime freeze-thaw-refreeze cycles means the opportunity for potholes. Although the city aims to fill potholes within five business days of notification, it can take longer, especially during winter and early spring. Last year, the city’s Public Works crews repaired some 63,000 potholes, only 20% of which were reported by residents. Approximately 32,000-man-hours each year are spent repairing potholes, with seasonal fluctuations requiring ten to twelve Street Maintenance crews working steadily during the winter months. Still, many went unreported, leading the city to flag pothole request resolution under “needs improvement” on its open data portal website.

Like many large cities, Memphis also struggles with vacant and blighted properties. Nearly 15,000 properties in Memphis are likely vacant, and city officials contend that many are owned by out-of-town investors who live elsewhere and do not take necessary restoration or maintenance steps. These properties can decrease the value of surrounding real estate and discourage new businesses and other residents from moving to an area. Citizen frustration and concerns over the number of blighted properties has made blight eradication a major focus of the City of Memphis.

Historically, residents reported potholes and blighted properties by calling 311, or more recently by using the Memphis 311 app. However, these reports only covered about 20 percent of the problems — often the worst cases. And by the time residents took the initiative to submit a 311 report, they usually weren’t feeling good about the situation.

Recognizing that potholes and vacant properties are often the most visible indicators of whether a city government is doing its job efficiently, Memphis Mayor Jim Strickland and CIO Mike Rodriguez began looking for ways they could apply technology to fix the problems. Mike approached Google for ideas, and Google recommended conducting a machine learning proof-of-concept (POC) with SpringML, a Google Cloud Partner.

“Memphis is focused on easy living, and we want to do everything we can to keep our citizens happy,” says Mike Rodriguez. “Working with Google and SpringML to reduce potholes and urban blight using machine learning and artificial intelligence was an easy decision.”

Bringing machine learning to city operations and budgets

The city’s goal is to detect potholes and abandoned properties by analyzing video footage of roads and residential properties. It wanted to classify potholes by width and depth, and share the information with workers who can repair them. For abandoned properties, it wanted to enable more strategic deployment of resources for homeowners citywide and take action to hold neglectful property owners accountable.

The POC began by training TensorFlow models for ML object detection using preconfigured AI Platform Deep Learning VM Images on Compute Engine. SpringML helped set up cameras and developed a user interface to collect pothole data and automate the 311 ticketing process.

Together, the teams analyzed 30 days of video from a moving city bus and high-resolution video from 360-degree cameras mounted to a code enforcement vehicle, overlaid with data from 311 reports. As the models were refined, accuracy quickly climbed from 50 percent to over 90 percent as models were taught to differentiate a pothole from a manhole cover or other object.

The city also imported routes, potholes, and paving data along with geolocation data from ArcGIS and Google Maps into BigQuery to better understand street conditions and the proximity of potholes to one another. BigQuery also analyzes city property records, tax records, 311 reports, and third-party survey data on-demand to predict where homes are starting to become run down and where neighborhood decay is most likely to occur. The SpringML team created a pilot analysis to begin vacant property protections and developed a user interface tool to interact with the model’s results.

“Google Cloud Platform made it possible for us to experiment with machine learning and artificial intelligence to help solve our city’s problems while working within the budget constraints of a municipal IT organization,” says Mike. “Google turned a ‘nice to have’ into a ‘let’s do this!'”

Identifying 75 percent more potholes

Memphis expects to substantially reduce the number of potholes on its streets, creating a better driving experience for residents and visitors alike. Because drivers won’t be as likely to swerve to miss a pothole, streets will be safer and friendlier to bicycles and scooters. Fewer potholes will also save the city between $10,000 and $20,000 annually in city claims that it pays out in cases where vehicle damage results from a pothole that was not addressed in a timely manner.

“Historically, Public Works has relied primarily upon Street Maintenance crews to proactively locate and fill potholes. As Memphis has over 6,800 lane-miles of public streets, it is a daunting task to reliably survey the entire system in an efficient and systematic way,” says Robert Knecht, Public Works Director for the City of Memphis. “The outcome of the data collected will be invaluable to Public Works so that it can ensure it is managing the city’s street system in a more proactive manner.”

Memphis will be able to better prioritize road maintenance based on condition and impact, increasing the efficiency of its Public Works road crews. Analyzing video of streets also gave the city visibility into issues it wasn’t previously aware of, such as curbs, gutters, and manhole covers that had been mistakenly paved over and need to be excavated. The ML process is easily transferrable to other concerns as well, helping the city identify illegal signs or spools of cable hanging on light posts that could be potentially unsafe.

Helping communities recover and thrive

Memphis is also having success in analyzing predictive trends to combat high rates of abandoned and blighted properties, surpassing 97.5 percent accuracy. “In the past, Public Works experimented with comprehensive, city-wide blight identification by using approximately 200 volunteers to survey and photograph over 237,000 city parcels. This effort was costly, took a long time to complete, and resulted in inconsistent data collection,” says Robert. “Blighted property conditions can change quickly in a city the size of Memphis. Now, with this new technology, Memphis will be able to make a significant difference in the efforts to proactively and comprehensively identify and manage blighted and substandard properties.”

Code Enforcement with better data-driven detection mechanisms enables the city to also identify cases where homeowners are not physically or financially able to keep up with the challenges of homeownership and make them aware of resources that are available to assist them. Memphis Code Enforcement can do a better job of finding people living in derelict properties that pose hazards to inhabitants’ health and safety, and help them fix those problems or find a new place to live.

“Using SpringML and Google Cloud Platform to detect indicators of vacant or blighted properties will help Memphis create safer neighborhoods that will be more attractive to businesses and home buyers,” says Mike. “Property values and employment will go up, crime will go down, and social services can be more focused and effective.”

Revolutionizing service delivery for citizens

Memphis is proving the viability of a cost-effective, cloud-based machine learning model that other cities can follow. The city is already looking into new applications of AI and ML that will further improve city services and help it build a better future for its 652,000 residents.

As part of his commitment to a transparent government, Memphis Mayor Jim Strickland created an open data policy that commits to releasing raw data and sharing it with citizens in a variety of downloadable formats. Going forward, this transparency will help citizens understand how their needs are being served and uncover new, innovative use cases for AI and ML.

“Our goal is to become a smart city, and technologies such as Google Cloud Platform and SpringML put us ahead of the game,” says Mayor Strickland. “Google understands data, and there isn’t a better company to help us analyze our data resources for actionable insights.”