Spanner uses certain concepts differently from other enterprise database management tools, so you might need to adjust your application to take full advantage of its capabilities. You might also need to supplement Spanner with other services from Google Cloud to meet your needs.

Migration constraints

When you migrate your application to Spanner, you must take into account the different features available. You probably need to redesign your application architecture to fit with Spanner’s feature set and to integrate with additional Google Cloud services.

Stored procedures and triggers

Spanner does not support running user code in the database level, so as part of the migration, you must move business logic implemented by database-level stored procedures and triggers into the application.

Sequences

Spanner does not implement a sequence generator, and as explained below, using monotonically increasing numbers as primary keys is an anti-pattern in Spanner. An alternative way to generate a unique primary key is to use a random UUID.

If sequences are required for external reasons, then you must implement them in the application layer.

Access controls

Spanner supports only database-level access controls using IAM access permissions and roles. Predefined roles can give read-write or read-only access to the database.

If you require finer grained permissions, you must implement them at the application layer. In a normal scenario, only the application should be allowed to read and write to the database.

If you need to expose your database to users for reporting, and want to use fine-grained security permissions (such as table- and view-level permissions), you should export your database to BigQuery.

Read the full article for more, including

• Data validation constraints
• Supported data types
• Migration process
• Transferring your data from Oracle to Spanner
• Maintaining consistency between both databases
• Verifying data consistency
• and more.

It’s simply amazing to witness how some of our systems integrators employ Google Cloud solutions to drive innovation in ways we at Google may never have considered—especially in healthcare.  According to analyst firm MarketsandMarkets, the market for the Cloud in healthcare is projected to grow 43% between 2020 and 2025 to nearly $65 billion, fueled by the need for better technology infrastructures and faster digital transformation.  

Healthcare and life sciences communities are looking to Google Cloud and its Service engagement model partners to improve collaboration and activate the power of medical data.  These transformations deliver robust data analytics and bring a much deeper perspective into health epidemics like COVID-19 to help save lives.

In the healthcare and life sciences industry, our Google Cloud partners and customers provide constant energy and inspiration–and are the magic in some key healthcare innovations globally.  

Let me show you how they are solving real-world business challenges.

Improving collaboration in Healthcare

Cloudbakers and Comanche County Memorial Hospital transitioned 2,000 employees to Google Workspace to improve collaboration, reduce costs, and increase security. By implementing a system that requires less maintenance while enabling mobile access to data and true collaboration, Comanche County Memorial Hospital saves $175,000 annually on licenses and helps medical professionals spend more time with patients.

“We chose Google Workspace because it cost a quarter of what we were paying previously, offers the kind of modern features that healthcare facilities need, and gave us data security and peace of mind.” —James Wellman, CIO, Comanche County Memorial Hospital

Accessing previously locked down medical data

Google Cloud and Quantiphi supported advances in cloud-based machine learning services to reduce infrastructure costs, unlock new paths of treatment, and dramatically reduce the amount of time it takes to evaluate scanned imagery following a stroke.  John Hopkins University BIOS Division has been working on medical imaging to accelerate insights from scans on approximately 500 patients from 2,500 hours to 90 minutes, and lead to more accurate decision-making for brain injury patients that will ultimately improve medical outcomes.  

“We’ve aligned closely with the goal of showing that a cloud-based, AI-driven approach is robust and that it can be performed while protecting personal PHI. In terms of costs, the cloud has definitely reduced some of the traditional financial demands of our research.” —Daniel F. Hanley, Jr., M.D., Director, Johns Hopkins University BIOS Division

Improving data access with the flexibility of Google Cloud

With the help of MediaAgility, TRIARQ migrated to Google Cloud to modernize their platform, build new applications, and expand their global footprint.  With BigQuery, TRIARQ can now consolidate all transactional data, past and present, into a single location to report on specific insights and predict future data from e-prescriptions to complex revenue-cycle data and value-base analysis.

“The future of this industry will need to be a global one, and we can no longer be stuck in legacy systems if we want to survive. Having a team that is passionate about supporting us in this journey is definitely a plus point.”—Yaw Kwakye, Co-founder and Chief Architect, TRIARQ

Increasing visibility to COVID-19 outbreaks for actionable insights

As part of its response to the COVID-19 pandemic, HCA Healthcare chose to work with Google Cloud and SADA to create a national portal that increases visibility into outbreaks in 3,100 counties across the country in just 8 weeks.  Now, the portal generates 30,000 new analytical views each day that can help inform private and public sector decision making for reopenings, closures, hot spots, and many other population health management activities. 

“This project required deep knowledge of AI, and consumer-facing platforms, as well as healthcare. Google brought together the ideal combination of product, people, and partners. Google Cloud’s healthcare-specific products along with SADA’s expertise in the healthcare IT space made this partnership the perfect choice to move quickly and intelligently.”—Dr. Edmund Jackson, Chief Data Officer, HCA Healthcare

We’re committed to building the technology, resources, and services through Partner Advantage to help our partners address this opportunity.  Looking for a solution focused partner in your region who has achieved Expertise and/or Specialization in your industry?  Search our Global Partner Directory.  Not yet a Google Cloud partner? Visit Partner Advantage and learn how to become one today!

Building a strong brand in today’s hyper-competitive business environment takes vision. It also requires a flexible, easily managed approach to digital asset management (DAM), so marketing professionals and other stakeholders can easily share, store, track, and manipulate assets to build the brand.

Many of today’s leading companies, including JetBlue, Slack, TripAdvisor, Lyft, and HealthONE, rely on Brandfolder to deliver consistent, organized, and efficient brand experiences. Brandfolder provides an easy-to-use platform that can scale across an entire company with little end-user training, empowering customers to distribute digital assets wherever they are needed. Customers also gain much greater insight into how those assets are used, and how to use them more effectively in marketing campaigns and brand messaging.

“Google Cloud made it easy to build an ML platform to quickly iterate through different brand intelligence use cases and release data-driven product features into the Brandfolder platform.”

—Ajay Rajasekharan, Head of Data Science, Brandfolder

Brandfolder is constantly advancing its development efforts to introduce new data-driven features without complicating the user experience. Big data, artificial intelligence (AI), and machine learning (ML) are key to meeting customers’ unique business needs, and essential for Brandfolder to compete in the fast-moving DAM industry. To enhance these capabilities, Brandfolder sought a public cloud provider that could help it scale its data pipeline cost effectively while providing access to advanced AI technologies.

After graduating from the Techstars startup accelerator program in 2013, Brandfolder tried two other cloud providers before standardizing on Google Cloud Platform (GCP).

“We saw a difference with Google Cloud from the very beginning because the interactions felt like a strategic relationship,” says Jim Hanifen, Head of Product at Brandfolder. “Google gave us startup credits and a lot of face-to-face support, which we hadn’t experienced with other cloud providers. We decided to move our entire infrastructure to Google Cloud Platform.”

Building an ML platform for brand intelligence

After performing an initial lift-and-shift migration of virtual machines (VMs) onto Compute Engine, Brandfolder built an ML platform using GCP managed services to seamlessly deliver its data products. The platform leverages Cloud SQL, Cloud Storage as the data lake, Cloud Dataproc for cloud-native Apache Spark computing clusters, Cloud Composer as the batch job scheduler, Cloud Pub/Sub as the backbone data pipeline, Container Registry to store Docker images, and Google Kubernetes Engine (GKE) as the application orchestrator. Cloud Dataflow brings data into the data lake and into BigQuery for analysis.

“Google Cloud made it easy to build an ML platform to quickly iterate through different brand intelligence use cases and release data-driven product features into the Brandfolder platform,” says Ajay Rajasekharan, Head of Data Science at Brandfolder, who describes the architecture in a detailed blog. “We simply ingest raw application and event data on one end and output an ML service on the other.”

“Moving to Google Cloud Platform allows us to complete more sophisticated data analysis and ML models much faster, and at a much lower cost. We can create brand-specific ML models 12x faster and get them into production quickly to address our customers’ unique business needs.”

—Brett Nekolny, Head of Engineering, Brandfolder

For many general use cases, Brandfolder does not need to build custom ML models, and instead relies on pre-trained API models from GCP. For example, it uses Vision API and Video Intelligence API to auto-tag creative assets on import to enable fast, intuitive searches across images and videos. When more product- and brand-specific modeling is required to address unique customer use cases, Brandfolder builds and trains custom ML models using its GCP pipeline or Cloud AutoML, a suite of products built on Google transfer learning and neural architecture search technology. For example, if a Brandfolder customer makes different types of grills, Brandfolder can use AutoML Vision to train a model to recognize the different grills.

“Moving to Google Cloud Platform allows us to complete more sophisticated data analysis and ML models much faster, and at a much lower cost,” explains Brett Nekolny, Head of Engineering at Brandfolder. “We can create brand-specific ML models 12x faster and get them into production quickly to address our customers’ unique business needs.”

Industry-leading security and performance

Google Cloud’s security model helps Brandfolder give existing and prospective customers peace of mind that their data will be protected. Cloud Identity & Access Management (Cloud IAM) provides enterprise-grade access control, while Cloud Identity-Aware Proxy (Cloud IAP) enables remote users to work more securely without the hassles of a VPN client. GCP also isolates cloud resources into projects, making it easy to assign permissions and keep data and VMs organized and segregated.

“With Google Cloud, everything begins and ends with security, which makes things very easy for us,” says Jim. “If we’re under a security review, we can submit a Google security white paper. If a potential customer has security concerns, we tell them we are hosted on GCP, and those concerns go away.”

To give customers even better application performance for accessing their brand assets, Brandfolder uses Cloud Memorystore, an in-memory data store service for Redis, to cache data and provide sub-millisecond data access for production applications.

“It was much easier for us to use Cloud Memorystore versus running Redis on our compute instances,” says Brett. “The high availability, replication across zones, and automatic failover with no data loss are big for us.”

Global private network interconnects between Google Cloud and the Fastly content delivery network (CDN) dramatically reduce latency, allowing Brandfolder’s customers to deliver and update even very large creative assets quickly around the world.

“What’s beautiful about the relationship between Google and Fastly is that if one of our customers uploads a new version of an asset, we can propagate that out to Fastly, and the new version will automatically show up in all the places where it’s referenced,” says Brett.

“The ability to quickly solve problems with AI has a substantial impact on our revenue, and that’s more apparent every quarter. Few of our competitors are doing product- or brand-specific modeling because it takes a lot of time and resources. We overcame those hurdles with Google Cloud.”

—Jim Hanifen, Head of Product, Brandfolder

Improving employee and customer productivity

Brandfolder also uses Google solutions for real-time collaboration and productivity, using G Suite to connect employees with intuitive, cloud-based apps. Teams use Gmail, Calendar, Docs, Drive, Sheets, Slides, and Hangouts Meet every day to move the business forward. Many of Brandfolder’s customers are also G Suite users, and Brandfolder offers a plug-in that allows them to view their creative assets inside of Docs and pull images in as needed. Customers can also log into Brandfolder with their G Suite credentials, making the solution even easier to use.

“We’ve been using G Suite since the beginning, and it’s helped us collaborate efficiently to build a successful, growing company,” says Jim. “Our teams expect to have that kind of close collaboration, and everyone here enjoys the G Suite experience.”

Driving 99 percent annual business growth

With automated tagging and other innovative AI-based features, Brandfolder is helping customers locate and distribute assets faster. As a result, Brandfolder is building customer loyalty and increasing sales, growing its business by 99 percent year-over-year. Since moving to GCP, Brandfolder has been able to scale its analytics and data pipeline 50x without a corresponding increase in costs and has not had to expand its development team.

“The ability to quickly solve problems with AI has a substantial impact on our revenue, and that’s more apparent every quarter,” says Jim. “Few of our competitors are doing product- or brand-specific modeling because it takes a lot of time and resources. We overcame those hurdles with Google Cloud.”

Data teams across companies have continuous challenges of consolidating data, processing it and making it useful. They deal with challenges such as a mixture of multiple ETL jobs, long ETL windows capacity-bound on-premise data warehouses and ever-increasing demands from users. They also need to make sure that the downstream requirements of ML, reporting and analytics are met with the data processing. And, they need to plan for the future – how will more data be handled and how new downstream teams will be supported?

Checkout how Independence Health Group is addressing their enterprise data warehouse (EDW) migration in the video above.

Why BigQuery?

On-premises data warehouses become difficult to scale so most companies’ biggest goal is to create a forward looking system to store data that is secure, scalable and cost effective. GCP’s BigQuery is serverless, highly scalable, and cost-effective and is a great technical fit for the EDW use-case. It’s a multicloud data warehouse designed for business agility. But, migrating a large, highly-integrated data warehouse from on-premise to BigQuery is not a flip-a-switch kinda migration. You need to make sure your downstream systems dont break due to inconsistent results in migrating datasets, both during and after the migration. So..you have to plan your migration. 

Data warehouse migration strategy

 The following steps are typical for a successful migration: 

• Assessment and planning: Find the scope in advance to plan the migration of the legacy data warehouse 
  • Identify data groupings, application access patterns and capacities
  • Use tools and utilities to identify unknown complexities and dependencies 
  • Identify required application conversions and testing
  • Determine initial processing and storage capacity for budget forecasting and capacity planning 
  • Consider growth and changes anticipated during the migration period 
  • Develop a future state strategy and vision to guide design
• Migration: Establish GCP foundation and begin migration
  • As the cloud foundation is being set up, consider running focused POCs to validate data migration processes and timelines
  • Look for automated utilities to help with any required code migration
  • Plan to maintain data synchronization between legacy and target EDW during the duration of the migration. This becomes a critical business process to keep the project on schedule.
  • Plan to integrate some enterprise tooling to help existing teams span both environments
  • Consider current data access patterns among EDW user communities and how they will map to similar controls available in Big Query. 
  • Key scope includes code integration and data model conversions
  • Expect to refine capacity forecasts and refine allocation design. In Big Query there are many options to balance cost and performance to maximize business value. For example, you can use either on-demand or flat-rate slot pricing or a combination of both. 
• Validation and testing
  •  Look for tools to allow automated, intelligent data validation 
  • Scope must include both schema and data validation
  • Ideally solutions will allow continuous validation from source to target system during migration
  • Testing complexity and duration will be driven by number and complexity of applications consuming data from the EDW and rate of change of those applications 

A key to successful migration is finding Google Cloud partners with experience migrating EDW workloads. For example, our Google Cloud partner Datametica offers services and specialized Migration Accelerators for each of these migration stages to make it more efficient to plan and execute migrations.

Data warehouse migration: Things to consider

• Financial benefits of open source: Target moving to ‘Open Source’ where none of the services have license fees. For example BigQuery uses Standard SQL; Cloud Composer is managed Apache Airflow, Dataflow is based on Apache Beam. Taking these as managed services provides the financial benefits of open source, but avoids the burden of maintaining open source platforms internally. 
• Serverless: Move to “serverless” big data services. The majority of the services used in a recommended GCP data architecture scale on demand allowing more cost effective alignment with needs. Using fully managed services lets you focus engineering time on business roadmap priorities, not building and maintaining infrastructure. 
• Efficiencies of a Unified platform: Any data warehouse migration involves integration with services that surround the EDW for data ingest and pre-processing and advanced analytics on the data stored in the EDW to maximize business value. A cloud provider like GCP offers a full breadth of integrated and managed ‘big data’ services with built-in machine learning. This can yield significantly reduced long-term TCO by increasing both operational and cost efficiency when compared to EDW-specific point solutions. 
• Establishing a solid cloud foundation: From the beginning, take the time to design a secure foundation that will serve the business and technical needs for workloads to follow. Key features include: Scalable Resource Hierarchy, Multi-layer security, multi-tiered network and data center strategy and automation using Infrastructure-as-Code. Also allow time to integrate cloud-based services into existing enterprise systems such as CI/CD pipelines, monitoring, alerting, logging, process scheduling, and service request management. 
• Unlimited expansion capacity: Moving to cloud sounds like a major step, but really look at this as adding more data centers accessible to your teams. Of course, these data centers offer many new services that are very difficult to develop in-house and provide nearly unlimited expansion capacity with minimal up-front financial commitment. . 
• Patience and interim platforms: Migrating an EDW is typically a long running project. Be ready to design and operate interim platforms for data synchronization, validation and application testing. Consider the impact on up-stream and down-stream systems. It might make sense to migrate and modernize these systems concurrent with the EDW migration since they are probably data sources and sinks and may be facing similar growth challenges. Also be ready to accommodate new business requirements that develop during the migration. Take advantage of the long duration to have existing your operational teams learn new services from the partner leading the deployment so your teams are ready to take over post-migration. 
• Experienced partner: An EDW migration can be a major undertaking with challenges and risks during migration, but offers tremendous opportunities to reduce costs, simplify operations and offer dramatically improved capacities to internal and external EDW users. Selecting the right partner reduces the technical and financial risks, and allows you to plan for and possibly start leveraging these long-term benefits early in the migration process.

Example Data Warehouse Migration Architecture

• Setup foundational elements. In GCP these include, IAM for authorization and access, cloud resource hierarchy, billing, networking, code pipelines, Infrastructure as Code using Cloud Build with Terraform ( GCP Foundation Toolkit), Cloud DNS and a dedicated/partner Interconnect to connect to the current data centers.
• Activate monitoring and security scanning services before real user data is loaded using Cloud Operations for monitoring and logging and Security Command Center for security monitoring. 
• Extract files from on-premise legacy EDW and move to Cloud Storage and establish on-going synchronization using Big Query Transfer services. 
• From Cloud Storage, process the data in Dataflow and Load/Export data to BigQuery. 
• Validate the export using Datametica’s validation utilities running in a GKE cluster and Cloud SQL for auditing and historical data synchronization as needed. Application teams test against the validated data sets throughout the migration process. 
• Orchestrate the entire pipeline using Cloud Composer, integrated with on-prem scheduling services as needed to leverage established processes and keep legacy and new systems in sync. 
• Maintain close coordination with teams/services ingesting new data into the EDW and down-streams analytics teams relying on the EDW data for on-going advanced analytics. 
• Establish fine-grained access controls to data sets and start making the data in Big Query available to existing reporting, visualization and application consumption tools using BigQuery data connectors for ‘down-stream’ user access and testing. 
• Incrementally increase Big Query flat-rate processing capacity to provide the most cost-effective utilization of resources during migration. 

To learn more about migrating from on-premises Enterprise Data Warehouses (EDW) to Bigquery and GCP here.