What Differences in Functionalities Can I Expect Between Google Cloud’s SQL Database and Standard MySQL? - Build What's Next
Blog

What Differences in Functionalities Can I Expect Between Google Cloud’s SQL Database and Standard MySQL?

3960

Of your peers have already read this article.

5:30 Minutes

The most insightful time you'll spend today!

Keen to try out Google’s fully-managed database service, Cloud SQL, but aren’t sure if it provides the same service and experience you get from your current SQL database? We break down the differences.

In general, the MySQL functionality provided by a Google Cloud’s SQL Database (Cloud SQL) instance is the same as the functionality provided by a locally-hosted MySQL instance.

However, there are a few differences between a standard MySQL instance and a Cloud SQL for MySQL instance.

Unsupported Features

Unsupported Statements

Sending any of the following types of SQL statements will generate an error with the message “Error 1290: The MySQL server is running with the google option so it cannot execute this statement”:

  • LOAD DATA INFILE
  • Note that LOAD DATA LOCALINFILE is supported.
  • SELECT … INTO OUTFILE
  • SELECT … INTO DUMPFILE
  • INSTALL PLUGIN …
  • UNINSTALL PLUGIN
  • CREATE FUNCTION … SONAME …

Unsupported Statements for Second Generation Instances

The following statements are not supported because Second Generation instances use GTID replication:

  • CREATE TABLE … SELECT statements
  • CREATE TEMPORARY TABLE statements inside transactions
  • Transactions or statements that update both transactional and nontransactional tables

For more information, see the MySQL documentation.

Unsupported Functions

  • LOAD_FILE()

Unsupported Client Program Features

  • mysqlimport without using the –local option. This is because of the LOAD DATA INFILE restriction. If you need to load data remotely, use the Cloud SQL import function.
  • mysqldump using the –tab option or options that are used with –tab. This is because the FILE privilege is not granted for instance users. All other mysqldump options are supported.
  • If you want to import databases with binary data into your Cloud SQL for MySQL instance, you must use the –hex-blob option with mysqldump.
  • Although hex-blob is not a required flag when you are using a local MySQL server instance and the mysql client, it is required if you want to import any databases with binary data into your Cloud SQL instance. For more information about importing data, see Importing Data.
  • Not all MySQL options and parameters are enabled for editing as Cloud SQL flags.
  • For Second Generation instances, InnoDB is the only supported storage engine. For help with converting tables from MyISAM to InnoDB, see the MySQL documentation.
  • You cannot import or export triggers, functions, stored procedures, or views into Cloud SQL. However, you can create and use these elements on a Cloud SQL instance.

Notable MySQL Options

Cloud SQL runs MySQL with a specific set of options. If an option might impact how your applications work, we note it here for your information.

skip-name-resolve

This flag impacts how hostnames are resolved for client connections. Learn more.

Cloud SQL for PostgreSQL

Features

  • Fully managed PostgreSQL databases in the cloud, based on the Cloud SQL Second Generation platform.
  • Custom machine types with up to 416 GB of RAM and 64 CPUs.
  • Up to 10TB of storage available, with the ability to automatically increase storage size as needed.
  • Create and manage instances in the Google Cloud Platform Console.
  • Instances available in US, EU, or Asia.
  • Customer data encrypted on Google’s internal networks and in database tables, temporary files, and backups.
  • Support for secure external connections with the Cloud SQL Proxy or with the SSL/TLS protocol.
  • Data replication between multiple zones with automatic failover.
  • Import and export databases using SQL dump files.
  • Support for PostgreSQL client-server protocol and standard PostgreSQL connectors.
  • Automated and on-demand backups.
  • Instance cloning.
  • Integration with Stackdriver logging and monitoring.

Some features are not yet available for Cloud SQL for PostgreSQL:

  • Point-in-time recovery (PITR)
  • Import/export in CSV format using GCP Console or the gcloud command-line tool.

Supported Extensions

Cloud SQL for PostgreSQL supports many PostgreSQL extensions. For a complete list, see PostgreSQL Extensions.

Supported Procedural Languages

Cloud SQL for PostgreSQL supports the PL/pgSQL SQL procedural language.

Supported Languages

You can use Cloud SQL for PostgreSQL with App Engine applications running in the flexible environment that are written in Java, Python, PHP, Node.js, Go, and Ruby. You can also use Cloud SQL for PostgreSQL with external applications using the standard PostgreSQL client-server protocol.

Differences between Cloud SQL and standard PostgreSQL functionality

In general, the PostgreSQL functionality provided by a Cloud SQL instance is the same as the functionality provided by a locally-hosted PostgreSQL instance. However, there are a few differences between a standard PostgreSQL instance and a Cloud SQL for PostgreSQL instance.

Unsupported Features

  • Any features that require SUPERUSER privileges. An exception to this rule is made for the CREATE EXTENSION statement, but only for supported extensions.
  • Custom background workers
  • The psql client in Cloud Shell does not support operations that require a reconnection, such as connecting to a different database using the \c command.

Notable Differences

There are a number of PostgreSQL options and parameters that are not enabled for editing as Cloud SQL flags.

To request the addition of a configurable Cloud SQL flag, use the Cloud SQL Discussion group.

Research Reports

Dataflow Guarantees 50+% Increase in Developer Productivity and Infrastructure Cost Savings: Read More

8364

Of your peers have already read this article.

3:00 Minutes

The most insightful time you'll spend today!

Google Cloud commissioned Forrester Consulting to conduct a study evaluating the benefits, risks and costs of Dataflow on customers' organization. They found financial benefits in 4 areas, 50+% boost in dev productivity & infrastructure cost savings.

In our conversations with technology leaders about data-driven transformation using Google Data Cloud –  industry’s leading unified data and AI solution – , one important topic is incorporating continuous intelligence to move from answering questions such as “What has happened? to questions like “What is happening?” and “What might happen?”. The core to this evolution is the need for an underlying data processing that not only provides powerful real-time capabilities for events happening close to origination, but also brings together existing data sources under one unified data platform to enable organizations to draw insights and take actions holistically. Dataflow, Google’s cloud-native data processing and streaming analytics platform, is a key component of any modern data and AI architecture and data transformation journey, along with BigQuery, Google’s internet-scale warehouse with built-in streaming, BI engine and ML; Pub/Sub, a global no-ops event delivery service; and Looker, a modern BI and embedded analytics platform. One of the key evaluation factors is potential economic value of Dataflow to their organization, particularly in the context of engaging other stakeholders is key for many of the leaders that we engage with. So we commissioned Forrester Consulting to conduct a comprehensive study on the impact that Dataflow had on their organization by interviewing actual customers . 

Today we’re excited to share our commissioned study conducted by Forrester Consulting, the Total Economic Impact™ of Google Cloud Dataflow, which allows data leaders to understand and quantify the benefits of Dataflow, and use cases it enables. Forrester conducted interviews with Dataflow customers to evaluate the benefits, costs, and risks of investing in Dataflow across an organization. Based on their interviews, Forrester identified major financial benefits across four different areas: business growth, infrastructure cost savings, data engineer productivity, and administration efficiency. In fact, Forrester found that customers adopting Dataflow can achieve a 55% boost in developer productivity and a 50% reduction in infrastructure costs. In fact, Forrester projects that customers adopting Dataflow can achieve a range of up to 171% Return on Investment (ROI) and a less than six months payback period. Customers can now use figures in the report to compute their own Return on Investment (ROI) and payback period.

Dataflow.jpg

“Dataflow is integral to accelerating time-to-market, decreasing time-to-production, reducing time to figure out how to use data for use cases, focusing time on value-add tasks, streamlining ingestion, and reducing total cost of ownership.” – Lead technical architect, CPG

Let’s take a deeper look at the ways that Forrester found that Dataflow can help you achieve your goals and unlock your business potential. 

Benefit #1: Increase data engineer productivity by 55%

Developers can choose among a variety of programming languages to define and execute data workflows. Dataflow also seamlessly integrates with other Google Cloud Platform and open source technologies to maximize value and applicability to a wide variety of use cases. Dataflow streamlined workflows with code reusability,dynamic templates, and the simplicity of a managed service. Engineers trusted pipelines to run correctly and adhere to governance. Data engineers avoided laborious issue-monitoring and remediation tasks that were common in the legacy environments such as poor performance, lack of availability, and failed jobs. Teams valued the language flexibility and open source base.

“Dataflow provided us with ETL replacement that opened limitless potential use cases and enabled us to do smarter data enhancement while data remains in motion.” — Director of data projects, financial services

Benefit #2: Reduce infrastructure costs by up-to 50% for batch and streaming workloads 

Dataflow’s serverless autoscaling and discrete control of job needs, scheduling, and regions eliminated overhead and optimized technology spending. Consolidating global data processing solutions to Dataflow further eliminated excess costs while ensuring performance, resilience, and governance across environments. Dataflow’s unified streaming and batch data platform gives organizations the flexibility to define either workload in the same programming model, run it on the same infrastructure, and manage it from a single operational management tool. 

“Our costs with our cloud data platform using Dataflow are just a fraction of the costs we faced before. Now we only pay for cloud infrastructure consumption because the open source base helps us avoid licensing costs. We spend about $120,000 per year with Dataflow, but we’d be spending millions with our old technologies.” – Lead technical architect, CPG

Benefit #3: Increase top-line revenue by improving customer experience and retention with payback time of < 6 months

Streaming analytics is an essential capability in today’s digital world to gain real-time actionable insights. Likewise, organizations must also have flexible, high- performance batch environments to analyze historical data for building machine learning models, business intelligence, and advanced analytics. Dataflow enabled real-time streaming use cases, improved data enrichment, encouraged data exploration,improved performance and resiliency, reduced errors, increased trust, and eliminated barriers to scale. As a result, organizations provided customers with more accurate, relevant, and in-the-moment data-backed services and insights — boosting customer experience, creating new revenue streams, and improving acquisition, retention, and enrichment.

“It’s already been proven that we are getting more business [with Dataflow] because we can turn around results faster for customers.” – VP of technology, financial services technology

“When we provide data to our customers and partners with Dataflow, we are much more confident in those numbers and can provide accurate data within a minute. Our customers and partners have taken note and commented on this. It’s reduced complaints and prevented churn.” – Senior software engineer, media

Other benefits 

Eliminated administrative overhead and toil

As a cloud-native managed service, all administration tasks such as provisioning, scaling, and updates are automatically handled by Google Cloud. Teams no longer need to manage servers and related software for legacy data processing solutions. Admins also streamlined processes for setting up data sources, adding pipelines, and enforcing governance.

Saved business operations costs for support teams and data end users

Dataflow improved the speed, quality, reliability, and ease of access to data for insights for general business users, saving time and empowering users to drive better data-backed outcomes. It also reduced support inquiry volume while automating manual job creation.

What’s next?

Download the Forrester Total Economic Impact study today to dive deep into the economic impact Dataflow can deliver your organization. We would love to partner with you to explore the potential Dataflow can unlock in your teams. Please reach out to our sales team to start a conversation about your data transformation with Google Cloud.

Whitepaper

Accelerate Innovation with Google Cloud’s Managed Database Services

DOWNLOAD WHITEPAPER

1286

Of your peers have already downloaded this article

4:30 Minutes

The most insightful time you'll spend today!

Google Cloud’s managed database services can help you innovate faster and reduce operational overhead. The migration tools and resources included in this whitepaper will help you plan your migration.

This whitepaper provides guidance on:

  • Managing services for maximum compatibility with your workloads
  • Leveraging services that are compatible with the most popular commercial and open source engines, such as MySQL, PostgreSQL, and Redis
  • Using robust tools and services to make migrations simple, secure, and fast with minimal downtime
  • Understanding the value of using Google Cloud’s managed database services
Research Reports

Cloud Databases: An Essential Building Block for Transforming Customer Experiences

DOWNLOAD RESEARCH REPORTS

2753

Of your peers have already downloaded this article

11:30 Minutes

The most insightful time you'll spend today!

Around the globe, companies realize that iterating and innovating the customer experience (CX) is the key to their business transformation and success goals. Customers expect exceptional and speedy service from organizations through personalized experiences and easy-to-use apps.

However, while most companies want data-driven innovation, many of them overlook a key mechanism to do so: operational databases. These databases run a company’s day-to-day operations and power applications around the globe.

“Operational databases are often overlooked because they are behind the scenes,” says Kumar Menon, senior vice president of data fabric and decision science technology at Atlanta-based Equifax, the multinational consumer credit reporting agency. “Companies often focus on providing strong customer-facing applications, but the application will only achieve the desired results with a well-architected operational data capability that helps drive the real-time analytics needed to make the customer experience effective and memorable. It is the backbone of anything that you will build on top of it.”

Better customer experience is a business outcome of using cloud databases to build applications. This dictum stands to reason because moving from a traditional on-premises database to a modern database in the cloud, or simply building new applications in the cloud, allows companies to address operational overhead, unlock new possibilities, implement new features quickly, increase application reliability, and serve users across more regions—all the things that enhance CX and make it possible. Google Cloud sponsored this research by Harvard Business Review Analytic Services to understand how digital innovators are delighting their customers by using modern cloud databases. The report analyzes why operational databases are an essential building block for transformative experiences and highlights some of the critical capabilities leaders should prioritize. Through interviews with technology executives, data leaders, and industry experts, it provides real-world examples of benefits companies are realizing with cloud databases. We hope you find the findings and real-world examples in this report insightful and inspiring.

Blog

Google Cloud and Climate Engine Collaborate to Support Climate Action in Public Sector

4816

Of your peers have already read this article.

1:30 Minutes

The most insightful time you'll spend today!

Google Cloud and Climate Engine partner to build climate resilience with Google Earth Engine's world-class geospatial capacities, combining AI and ML to provide a centralized system to gather, process and analyse earth based data.

While there is uncertainty about how much the climate will change in the future, we know it won’t look like the past. Extreme weather events will increase in frequency and severity; the world will continue to warm, and the cost of climate change will increase.

Government plays a vital role in understanding and responding to these changes quickly. Achieving this improved response time will require data insights to ensure informed decision-making—from local to global scales. The challenge is not only urgent; it’s one of the world’s biggest “big data” problems.

Fortunately, new technologies to help us monitor the Earth are proliferating. Thousands of satellites take millions of images of the planet every day. Sensors generate data about temperature, precipitation, wind, soil conditions, and more—as frequently as every second. We have more information about the planet’s systems than at any other time in history. And the data will only continue to grow. The problem is not the lack of data–it is harnessing this data to drive insights for decision makers to tackle climate change. That’s why Google Cloud has partnered with Climate Engine.

How Climate Engine and Google Cloud enable greater climate resilience

Climate Engine is a scientist-led company that works with Google to accelerate and scale the use of Google Earth Engine’s world-class geospatial capacities (in addition to those of Google Cloud Storage and BigQuery, among other tools) in support of climate action in the public sector. Powered by Google Cloud’s infrastructure, Google Earth Engine (GEE) combines a multi-petabyte catalog of satellite imagery and geospatial datasets with planetary-scale analysis capabilities, enabling scientists, researchers, and developers to detect changes, map trends, and quantify differences on the Earth’s surface. 

With cloud-based technologies, we can leverage massive computing at a scale that generates actionable insights from Earth-based data. These insights help us better manage resources, understand risks, predict changes, and respond to disasters as we meet the challenge of climate change. Geospatial AI combines the power of artificial intelligence (AI) and machine learning (ML) with geospatial analysis. Google Cloud’s Geospatial AI solutions provide departments and agencies with a centralized system to collect, process, and deliver Earth-based data into decision-making contexts.

Climate Engine and Google Cloud provide specialists with the opportunity to go back in time and see how our landscapes have changed due to changes in climate and other human activities over the past few decades. Years of data can now be quantitatively analyzed and visualized in a matter of a few seconds, enabling government agencies to fulfill their mandates by drawing invaluable insights into how landscapes are changing, what physical and natural assets are at risk, and where the opportunities are for reducing emissions and increasing carbon sequestration. 

“This is game changing for natural resource managers and scientists at public institutions at all levels of government,” says Dr. Daniel McEvoy, regional climatologist, at the Desert Research Institute & Western Regional Climate Center, Nevada System of Higher Education.https://www.youtube.com/embed/aPGsi8bd_Zk?enablejsapi=1&

Use cases for geospatial climate information systems

The use cases for this technology are as varied as the climate challenges themselves. These include monitoring, predicting, and analyzing the risks of extreme weather events like floods, wildfire, drought, extreme heat, wind, and other climate hazards. Use cases also include tracking changes in ecosystems, disease vectors, water availability and quality, soil health, growing seasons, air pollution, and more. These use cases are some of the ways that Google Cloud and Climate Engine can help the public sector deliver on government mandates. These provide insights that are helpful for a wide range of departments, and that can be applied in spatial and temporal scales that are meaningful for governments to take action.

“Our planet is changing at a rate that we have never experienced,” says Forrest Melton of the NASA Western Water Applications Office. To respond to these changes, we must understand what is happening across a wide range of environmental variables and at geospatial scales that range from local to global. We now have access to more data about the planet than ever before. The big challenge is converting data into actionable insights and then rapidly integrating these insights into decision-making systems. Climate Engine and Google Cloud help resolve this problem through innovative analytical tools and effective use of cloud computing.” 

Climate change carries an existential risk to our current and future stability and security. Together, we are working to provide transformational technologies that help meet that risk and build a safer, more resilient future for all of us. 

Learn more about Google Cloud’s environmental initiatives here and here.

How-to

No More ‘Tab Game’ with Easy Tutorials on Google Cloud Console

3526

Of your peers have already read this article.

2:00 Minutes

The most insightful time you'll spend today!

There are several factors that make tutorials in the Google Cloud Console great for developers for building applications. Explore guides and tutorials in one window and end swiping around tabs to learn about Google products and latest additions.

When it comes to learning how to implement some technology, we all have our own version of what I call the “tab game”—that is, your setup for all the tabs and windows you need open at once. You may have several monitors so you can see documentation, your IDE, and terminal windows at the same time. You may have several guides and references open at once in one window to get all the information you need.

Actual image of my computer while I'm trying to implement the code of a new product

Personally, I like to work just from my laptop because I like to move around and work from various comfy spots. I think my tab game would probably enrage most devs because it involves a lot of swiping back and forth between windows *and* toggling tabs. It’s not pretty. That is, it wasn’t pretty until I discovered tutorials in the Google Cloud Console!

Here is one way to view some tutorials available in the Google Cloud Console.

Jen really didn’t know about tutorials in the Google Cloud Console?

Yes, I honestly didn’t know about them! I’m sharing about it because if I can work for Google and not know, then I can’t be the only one, and it would be a shame to miss out on this because it’s a brilliant idea. Also I wrote some pretty sweet tutorials for the console, but I swear that the main reason I’m telling you is because it’s a cool thing!

There are several reasons that these tutorials are great:

  • You can view the instructions and the console at the same time. No more playing the tab game!
  • The tutorials include links and highlights, making it easy to find the screens and buttons you’re looking for
  • You can run code from Cloud Shell, so you don’t need a separate window for an IDE
  • You can use the demo data provided to try things out, or you can apply the steps to your existing projects using data that suits your app’s needs
Here are some highlights of the highlights!

Firestore tutorials

I’m developing a series of tutorials in the Google Cloud Console designed to take you through everything you need to know about Firestore–from manually adding data in the Google Cloud Console to triggering Cloud Functions to make changes for you. Below are links and summaries for the currently available tutorials. Check back regularly to find the latest additions as they’re released!

You, too, can see fun updates like these!

Add Data to Firestore

  • Enable Firestore on a project
  • Learn about the Firestore data model
  • Add a collection of documents
  • Add fields to a document
  • Delete documents and collections

Updating Data in Firestore using Node.js or using Python

  • Add a collection of documents
  • Explore available data types
  • Replace the data of document
  • Replace fields in a document
  • Handle special cases: incrementing, timestamps, and arrays

Reading Data from Firestore using Node.js or using Python

  • Add a collection of documents
  • Explore available data types
  • Read a collection
  • Read a single document
  • Order documents
  • Query documents

Transactions in Firestore using Node.js

  • Add a collection of documents
  • Update data without a transaction to observe issue
  • Complete a transaction
  • Complete a batched write

Batched Writes in Firestore using Node.js or using Python

  • Use Cloud Shell and Cloud Shell Editor to write a Node.js or Python app
  • Complete a batched write

Firestore triggers for Cloud Functions

  • Initialize Cloud Functions using the Firebase CLI
  • Write a Cloud Function triggered by a new document write to Firestore

Offline Data in Firestore

  • Add data to Firestore in the Cloud console Firestore dashboard
  • Create a web app that uses Firestore using the Firebase SDK
  • Deploy Firestore security rules that enable access to the required data
  • Enable data persistence in the web app
  • Observe app behavior with and without network connection

Chime in

Is there a particular action or concept in Firestore that you’d like to see a tutorial for? Is there another Google Cloud product that you want to learn more about? Tweet @ThatJenPerson and you may just see your suggestion come to life in the Google Cloud Console!

More Relevant Stories for Your Company

Blog

Built with BigQuery: Retailers Unlock the Value of Data with SoundCommerce

As economic conditions change, retail brands’ reliance on ever-growing customer demand puts these companies at financial and even existential risk. Top-line revenue and active customer growth do not equal profitable growth. Despite multi-billion dollar valuations for some brands, especially those operating the direct-to-consumer model, the rising costs of meeting shoppers’

Blog

Explore the Innovations and Architecture Powering Spanner and BigQuery

Previously, databases had architectures with tightly coupled storage and compute. This resulted in higher latency, and with faster networks these constraints no longer surface. With Google Cloud's BigQuery and CloudSpanner, the storage and compute architecture have been separated, allowing for better scalability and availability to address businesses' high throughput data

Blog

Empower Your Firm’s Data Decisions with Dataplex Data Lineage

Today, we are excited to announce the general availability of Dataplex data lineage — a fully managed Dataplex capability that helps you understand how data is sourced and transformed within the organization. Dataplex data lineage automatically tracks data movement across BigQuery, BigLake, Cloud Data Fusion (Preview), and Cloud Composer (Preview),

Case Study

Indian Retailer Figures Optimizes Hyperlocal Delivery to Increase Customer Experience

Anyone who follows the Indian e-commerce scene knows that one of the largest challenges these companies face is hyperlocal delivery. That was a problem facing Wellness Forever, a retail chain of pharmacies with 150-plus stores across India. “Exactly a year ago, we started our journey of hyperlocal deliveries. This optimization

SHOW MORE STORIES