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

• User defined functions
• InnoDB memcached plugin
• Federated Engine
• The SUPER privilege

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.

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

• 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.

When you think about climate change, you might not consider a daily commute to work or a drive around town as big contributing factors. And yet, transport is the fastest growing source of CO2 emissions from fossil fuel, which in turn is the largest contributor to climate change. This is the key insight behind the mission of Spanish carbon-neutral multinational Acciona. The group, which provides sustainable solutions for infrastructure and renewable energy projects across 65 countries, launched an electric scooter sharing service called Acciona Mobility in 2018.

Aiming to contribute to the decarbonization of the transport sector while helping relieve traffic congestion within cities, Acciona Mobility enables users to rent electric scooters powered 100% by energy from renewable sources. Users can find, reserve, and rent the scooters at the tap of their mobile screens via the Acciona Mobility application and pay on the basis of minutes spent riding. The app, which is available free to download online, is also the gateway through which new users can register for the service, which welcomes anyone with a valid driver’s license for motorbikes. After having their right to drive, identity, and card details validated, citizens can start enjoying the service, available 24 hours a day, every day of the year.

“We needed to deploy a reliable infrastructure for a new service before understanding exactly what its scale and demand would be. Our strategy was to adopt a serverless architecture that can grow with us to sustain our long-term vision. This kind of thinking led us directly to Google Cloud.”

—Jose Luis Rosell, CIO Services Division, Acciona

“Acciona Mobility is exciting because it stems from our vision to make people’s lives easier in a sustainable way,” says Jose Luis Rosell, CIO of Acciona’s Services Division. “40% of the pollution near cities comes from private transportation. We want to help solve that problem with zero-carbon, electric, multimodal transportation that’s also convenient for people to use.”

However, Jose Luis says that building the appropriate IT infrastructure to bring the new service to life required external support. “We needed to deploy a reliable infrastructure for a new service before understanding exactly what its scale and demand would be. Our strategy was to adopt a serverless architecture that can grow with us to sustain our long-term vision,” he explains. “This kind of thinking led us directly to Google Cloud.”

Developing the transportation platform of the future

Because Acciona was already a Google Cloud customer, Jose Luis and his team reached the decision to develop the new service on Google Cloud with ease, as he explains: “We had a very positive experience working closely with the Google Cloud team during Acciona’s first project on Google Cloud, and that collaborative mentality is very important for us. We feel Google Cloud is a trustworthy partner that is willing to take risks with us and is flexible enough to lead us through business uncertainty with the right technology,” he says. “So we were confident that its technology and people could help us bring the Acciona Mobility service from theory to reality.”

This time, Acciona partnered with cloud consultancy Altostratus as well, to consult on a selection of Google Cloud products that would help develop the application quickly by making use of managed services. Acciona’s strategy was to launch the new services first in Madrid, where the company is headquartered, and then spread the vision across more cities later, if the idea worked out well in practice. Due to the scale of the project and its data needs, BigQuery was chosen as the primary data warehouse to store all the information related to the service, such as the location of scooters and their availability status. With all its data readily available on BigQuery, Acciona is able to run specific queries that help it to gain insights such as which urban areas have the highest volume of scooters being rented. Knowing this, Acciona can reorganize availability to make sure there are always enough scooters in that specific location.

Integrating it with Pub/Sub and Dataflow, Acciona ensures that the data generated by scooters is ingested and processed in real time so that users searching for scooters nearby, using the Acciona Mobility application on their phones, can always have up-to-date information at hand. Using Google Maps Platform APIs such as the Directions API, Acciona’s mobile app translates the geographical coordinates of scooters into an easy-to-read address displayed beside a visual map. Using Cloud SQL, Acciona automates the storage capacity management of the database as the number of scooters and active users grow.

Meanwhile, Google Cloud Armor protects the service against cyber breaches and distributed denial of service attacks to keep it running uninterrupted. In addition, Google Cloud itself helps to reinforce the cyber security measures with secure-by-default managed services, such as data encryption. “The managed services mean more resilience and more uptime, because we don’t need to worry about maintenance or external threats,“ says Jose Luis of the solutions protecting all information generated by the new service, which as CIO is a topic he holds dearly. “I face cybersecurity issues more confidently as a Google Cloud partner,” he adds.

“Today, we have data coming in every 15 seconds from 10,000 electric scooters around Europe. BigQuery and Cloud SQL-managed services help us handle that data very carefully and with precision so that our users don’t experience any lags when searching for scooters or returning them.”

—Jose Luis Rosell, CIO Services Division, Acciona

Expanding an 100% sustainable transport solution throughout Spain

Within two months of the decision to deploy Acciona Mobility on Google Cloud, the service was ready for launch, filling the streets of Madrid with 500 electric scooters that run 100% on renewable energy. By 2020, the number of scooters has grown to 10,000 and the service has scaled to multiple cities in and outside of Spain, including Lisbon, Milan, and Rome. To date, more than 3.5 million intracity rides have used the Acciona Mobility app, reducing CO2 emissions by more than 1,000 tons.

“Today, we have data coming in every 15 seconds from 10,000 electric scooters around Europe. BigQuery and Cloud SQL-managed services help us handle that data very carefully and with precision so that our users don’t experience any lags when searching for scooters or returning them,” says Jose Luis of the fast-growing scale of the project.

“Google Cloud enables us to keep our services always available and to scale quickly to respond to its growing demand. Our ratings have been very positive as a result. More importantly, our vision of transportation as a cleaner, more sustainable commodity is really spreading and resonating with the public.”

—Jose Luis Rosell, CIO Services Division, Acciona

Set to continue expanding, Acciona is using Google Kubernetes Engine to make sure the Acciona Mobility application has the computing capacity it needs to continue scaling to reach new markets and serve more users. On that note, Google operations tools (formerly Stackdriver) are also being deployed to help monitor, troubleshoot, and improve the performance of the application as it scales.

“Google Cloud enables us to keep our services always available and to scale quickly to respond to its growing demand. Our ratings have been very positive as a result. More importantly, our vision of transportation as a cleaner, more sustainable commodity is really spreading and resonating with the public,” he concludes.

Over the last decade, financial service organizations have been adopting a cloud-first mindset. According to InformationWeek, lower costs and enhanced scalability were the biggest drivers for cloud adoption in financial services, and cloud-native applications allow access to the latest technology and talent, enabling adopters to rebuild transaction processing systems capable of supporting very high volumes and low latency.

Both Neo4j and Google Cloud have been using relationship-based data representations since the beginning, and we’re dedicated to using this technology to help financial services customers drive business transformation. We are excited about the prospects of financial services (FinServ) cloud systems and believe that graph data in the cloud can help solve significant challenges in the industry.

Data Challenge #1: Risk Management and Compliance

First among the top concerns for any CIO moving to the cloud is risk management and compliance. Disconnected, uncontextualized, or stale data create opportunities for fraud and financial crimes to occur. The fact is when it comes to FinServ, the question is not “if” but rather how often an attack will occur.  Unfortunately, incidents have been trending upward over the last decade, and COVID has only exacerbated this reality. Financial crimes affect the bottom line both in the remediation of these crimes and in intangibles like brand value.  

Add to this the complexity of international banking, which makes “compliance” a moving target. Penalties due to noncompliance are a constant concern to any FinServ organization.

The tabular representation of information with a fixed number of columns that never change prevents a description of an ever changing world with changing characteristics. Relational databases are great if the world you describe does not move fast but have limitations when data structures are highly interlinked and not homogeneous.

Neo4j Aura on Google Cloud provides a foundation for creating dynamic, futureproof, scalable applications that adhere to the security standards and protocols today’s financial services organizations require to meet the challenges of finding and preventing bad actors. This also includes enterprise scalability; reaching over 1 Billion nodes and relationships to streamline queries and provide solutions that meet regulatory and privacy compliance across geographies. Neo4j has helped some organizations save billions of USD in fraud in the first year of deployment alone.  

What makes graph technology the best choice for fraud detection use cases is that the relationships between the data-points are as important as the data-points themselves. Let’s take as an example, one John Smith approaches a multi-national banking institution to manage the primary account for his new holding corporation.  

While no one has any record of John R Smith Holdings LLC, the bank’s application built on graph technology understands that there are several well-known entities owned by John Smith Holdings. The application also identifies several well-known board members who bank with this institution. Due to this relationship-driven approach, the bank now understands John R Smith is not “John Smith,” who previously attempted to open an account for his holding corporation, which had no information associated with it prior to two months ago.

Data Challenge #2 Manual Processes and Inefficiencies 

The ubiquity of the cloud offers an opportunity to deploy automation at unprecedented levels to tackle the errors and inefficiencies that manual processing allows to creep into processes. When data comes from disparate, perhaps legacy systems – which may have become siloed and “untouchable” over the years – further complexity arises. As an example, if someone in sales types “John Smith” into a CRM system not knowing that John R Smith is the spelling in the customer data master, it may result in two separate and potentially conflicting records. Being able to join those records together in a mastered view helps to solve this problem. In addition, low data quality equates to an increase in risk, costs, and implementation times for new systems. 

Neo4j Aura on Google Cloud provides automation and artificial intelligence (AI) that reduces manual processes and the errors that accompany them. In this graph architecture each node, which can represent a person, will have labels, relationships, and properties associated with it. This allows for the use of AI which can easily understand that John Smith in the CRM is the same John R Smith in the customer master. The information contained in Neo4j can be connected bi-directionally to ensure consistency across applications and data sources. 

One of the benefits of this approach is that linking information allows organizations to keep the full value of the data, rather than forcing the data into predetermined tabular representations, with the risk of losing valuable information and insights.

Data Challenge #3: Customer Engagement and Insight

Another significant concern is the high expectations today’s customers have for every interaction. End users are accustomed to predictable experiences on their digital devices, and FinServ apps are no exception. Added to this, the “Covid economy” has driven digital adoption significantly across demographics; even among customers who might traditionally have used in-person services. This also equates to increased expectations for personalized, predictable experiences with every digital interaction. We know that latency has always been a key consideration for financial trading, but a recent ComputerWeekly study showed that every financial organization should ensure their visible latency is at 10 milliseconds or less. Customers no longer accept their broadband is at fault.

Finally, blind spots in the customer journey often result in dissatisfaction, which ultimately leads to increased churn. Without gaining actionable insights from your customers, there is no room to innovate and iterate on what they are looking for in your products and services. And this translates to losing market share and competitive advantage.

The NoSQL architecture, specifically the dynamic schema and structure of Neo4j Aura gives you the ability to take charge of your data and make changes according to your development cycles or newer data models. This equates to faster builds, more comprehensive releases and a wider, richer data-set that can be contextualized and understood instantly. Graph technology is the logical choice for building a Customer 360 application. Under this approach organizations not only get valuable insight into the individual client’s behavior and patterns, but also those of their family, friends and colleagues. This allows for stronger personalization, targeted campaigns and successful execution, resulting in increased customer satisfaction and retention levels.

Graph Technology on Google Cloud

Neo4j is a recognized leader in graph database technology and the only fully integrated graph solution on Google Cloud, helping to fill a common need for Google Cloud customers. Both Neo4j and Google Cloud are invested in continuing to grow our partnership and mutual product direction.  

You can find and deploy the Neo4j graph database straight from the Google Cloud marketplace, whether you want to download the software for an on-premises deployment, use the virtual machine image, or use the hosted solution, Aura on Google Cloud, the graph database-as-a-service. In any deployment, you get the same enterprise-grade scalability, reliability, and connectivity along with successful, repeatable use cases you can rely on to resolve your particular challenges and integrated billing. 

For a real-world example of how graph technology can optimize financial services, you can read our Case Study with fintech Current. Current, a leading U.S. financial technology platform with over three million members, used Neo4j Aura on Google Cloud to create a personalization engine based on client relationships. 

To learn more about Neo4j Aura on Google Cloud for FinServ organizations, register for our webinar on Thursday, December 16 with Jim Webber, Chief Scientist, CTO Field Ops at Neo4j and Antoine Larmanjat, Technical Director, Office of the CTO, Google Cloud. 

Click here to Register

With unemployment application submissions reaching record numbers over the past year, state and local agencies in the United States have faced the challenge of processing unprecedented numbers of claims per week. The digital infrastructure most agencies have in place is unable to handle this volume, resulting in constituents waiting longer, and bad actors taking advantage of vulnerable systems. The Department of Labor Inspector General estimates that $63 billion in claims distributed is either an improper payment or fraud. 

Validating claims also requires secure data sharing with other agencies for document and identity verification. Government leaders need a way to allow case adjudicators to quickly and confidently release backlogged claims, integrate with existing systems, and segment legitimate claims from potentially fraudulent ones — all within limited government budgets — securely and at scale.

Implementing a fraud detection solution on Google Cloud

States were under pressure to release payments, while also filtering out potentially fraudulent claims. SpringML and Google Cloud developed a framework to give adjudicators a reliable verification process that quickly filters potentially fraudulent claims, while processing the remaining claims so benefits reach citizens in a timely manner. SpringML and Google Cloud, applied AI-powered machine learning models to detect anomalous patterns in large datasets. Using Google Cloud tools, SpringML implemented a solution to streamline workflows, improve efficiencies, automate processes and identify potentially fraudulent claims.

SpringML used a variety of Google Cloud products to deliver a fraud detection solution, including:

• Google Cloud Storage to store and manage data
• BigQuery to store tabular data and BigQuery Machine Learning (BQML) to conduct machine learning on that data
• AutoML solutions to build predictive models and risk scoring
• Visualization tools such as Looker and Data Studio to present data and help government leaders make informed decisions.

Implementing machine learning to detect improper payments allows agencies to classify claims as “fraud” or “not fraud” based on the number of flags, as well as prioritize the most urgent claims. Deploying intelligent virtual agents to handle frequently asked questions meant that live agents could focus their time on more challenging cases. 

Even once the pandemic is behind us, there will be bad actors trying to take advantage of overwhelmed or legacy systems. We’ve identified a few best practices for agencies managing enormous case loads and looking to improve improper payment analytics: 

• Move your systems to the cloud. Many on-premises legacy systems can’t update their applications and scale to meet the volume of claims. Moving to a cloud environment enables rapid solution deployment and ingestion of large amounts of data without fear of overloading the system. The cloud scales with you–cost-effectively and securely. 
• Understand patterns in the data. The answer is always in the data — we used deep analysis to help uncover suspicious patterns in large data sets. We implemented unsupervised machine learning to learn behaviors and create configurable rules that adjust to new information that comes into the system. We can uncover patterns that are likely associated with fraud – ones that a human might have missed. 
• Use AI/ML tools to automate your existing systems and teams. These tools enable humans to work smarter and more efficiently. We automate anomaly detection and create dashboards for adjudicators to rapidly process claims. We are enabling the Wisconsin Department of Workforce Development by implementing automatic calculations and processing of recharge amounts, resulting in faster processing times and fewer human errors. Proactive fraud detection and timely calculation of recharge payment allowed DWD to ensure the benefits reached the right individuals.
• Build flexibility into your systems. We discovered that fraud patterns change over time. For instance,flags for fraud during March-May 2020 were vastly different from those we found in June-July 2020. Google Cloud tools make it easy to continually update algorithms to detect patterns and integrate external data sources.

Using Google Cloud tools, we can update digital infrastructure and incorporate machine learning best practices to help organizations efficiently process large volumes of claims and identify high probability fraudulent ones. SpringML provides consulting and implementation services and industry-specific analytics solutions that deliver high-impact business value to accelerate data-driven digital transformation. Learn more about fraud detection and how to improve improper payments analytics by watching our webinar. 

If you’re managing a crucial application that has to be fully fault-tolerant, you need your system to be able to handle every fault, no matter the type and scope of failure, with minimal downtime and data loss. Protecting against these faults means juggling numerous variables that can impact performance as well as recovery time and cost.

Today’s managed database services take over the operational complexity that used to exist for database administrators. Growing your organization’s tolerance required adding machines, compute, and storage, plus the operational costs of IT management: performing backups, writing scripts, creating dashboards, and carrying out testing to make sure your platform is ready when problems arise–all in a secure way.

At Google, our Cloud SQL managed database service offers three fault tolerance mechanisms —backup, high availability, and replication—and there are three major factors to consider for each of them:

• RTO (recovery time objective): When a failure happens, how much time can be lost before significant harm occurs?
• RPO (recovery point objective): When a failure happens, how much data can be lost before significant harm occurs?
• Cost: How cost-effective is this solution?  

We’ve heard from customers like Major League Baseball, HSBC, and Equifax that they have strict data-protection needs and require highly fault-tolerant multi-region applications—and they’ve all chosen Cloud SQL to meet those needs. 

Let’s take a closer look at how the decision-making process plays out for each recovery solution.  

High availability (HA)

If your application is business critical, you require minimum RTO and zero RPO— a high availability configuration ensures that you and your customers are protected. If the primary instance fails, there’s another standby instance ready to take over with no data loss. There’s an additional cost here, but doing this manually brings a great operational cost, since you have to detect and verify the fault, do the failover, and make sure it’s correct—you can’t have two primary instances or you risk data corruption—then finally connect the application to the new database.

Cloud SQL removes all that complexity. Choose high availability for a given instance and we’ll replicate the data across multiple zones, synchronously, to each zone’s persistent disk. If an HA instance has a failure, you don’t have to think about when to fail over because Cloud SQL detects the failure and automatically initiates failover, for a full recovery and no data loss within minutes. Cloud SQL also moves the IP address during failover so your application can easily reconnect. MLB, for example, uses Cloud SQL high availability to serve prediction data to live games with minimal downtime. Dev/test instances don’t need those same guarantees, but can use local backups to recover from any potential failure.

Cross-region replica

If a whole Google Cloud region goes down you still need your business to continue to run. That’s where cross-region replication comes in, a hot standby replica in another Google Cloud region provides RTO of minutes and RPO typically less than a minute . If you create a read replica in a region separate from your primary instance and you get hit with a regional outage, your application and database can start serving customers from another region within minutes. But this solution can be complex and enabling it yourself can be difficult and time-consuming. Securing cross-geography traffic demands end-to-end encryption and can bring connectivity issues too. 

This is where the fully managed Cloud SQL solution shines. We offer MySQL, PostgreSQL and SQL Server database engines as a cross-region replication solution that’s easily configured and bolstered by Google’s interconnected global network. Just say, “I’m in U.S. East, I want to create a replica in U.S. West,” and it’s done, reliably and securely.

Backup

When you suffer data loss because of an operations error (for example, a bug in a script dropped your tables) or human error (for example, someone dropped the wrong table by accident), backups help you restore lost data to your Cloud SQL instance. Our low cost backup mechanism features point-in-time, granular recovery, meaning that if you accidentally delete data or something else goes wrong, you can ask for recovery of, for example, the state of that database down to the millisecond, such as Monday at 12:53pm. Your valuable data is replicated multiple times in multiple geographic locations automatically. This enables the automatic handling of failover in cases of major failure. You can always rest assured that your database is available and data is secure, even in the times of major failure crises. 

Cloud SQL provides automated and on-demand backups. With automated backups, Google manages the backups so that you can easily restore them when required. Also, the scheduled backing is automatically taken by default. With on-demand backup, you can create a backup at any time. This could be useful if you are about to perform a risky operation on your database, as Cloud SQL lets you select a custom location for your backup data. When the backup is stored in multiple regions, and there’s an outage in the region that contains the source instance, you can restore a backup to a new or existing instance in a different region. This is also useful if your organization needs to comply with data residency regulations that require you to keep your backups within a specific geographic boundary.

Putting it all together

For critical workloads, MLB configures their Cloud SQL instances with backups, high availability, and cross-region replication. Doing so ensures they can recover from many failure types.

• To recover from human error (“Oops, I didn’t mean to delete that”), MLB uses backups and point-in-time recovery to recovery to a millisecond or specific database transaction
• To automatically recover from primary instance failures and zonal outages, MLB uses Cloud SQL’s high availability configuration
• To protect against regional outages, MLB uses cross-region replication

Creating a robust configuration, like MLB did, takes just a few minutes. Get started in our Console or review documentation.