We wanted to find out which of the most popular database services on two of the biggest cloud provider (AWS and Google), developers preferred to use.

More specifically, we wanted views from enterprises in the region (it’s hard to impossible to find data that’s specific to India.)

To simplify our quest, we looked at the most popular database services on AWS and then looked at the equivalent Google Cloud offering.

That’s how we came to pit AWS RDS and Google Cloud SQL.

For the uninitiated: Cloud SQL is a fully managed database service that makes it easy to set up and manage your relational PostgreSQL, MySQL, and SQL Server databases in the cloud.

It is ideal for: WordPress, backends, game states, CRM tools, MySQL, PostgreSQL, and Microsoft SQL Servers.

Based on real feedback from companies in the APAC region, there are 11 reasons why Google Cloud SQL is better.

In addition, according to Stackshare and ITcentralstation, the biggest reason developers prefer Google Cloud SQL? Because it’s fully managed, easy to set up, easy to manage and it’s really scalable.

As one user says:

Its most valuable feature is that it’s scalable. I can start off with a base of a lot of data and move as much as I want and it’s the same as if asked to do a lot of infrastructure changes… it’s easy to use, simple, and user-friendly. The setup was straightforward. Just a couple of clicks, and we were done. My suggestion to anyone thinking about this solution is to jump into it head-first!

Presently, every government agency has to take a hard look at their data capabilities and decide whether their current infrastructure supports their workflow. For many, it doesn’t. Most data systems are developed with a strict set of parameters in mind before implementation, which can limit flexibility and long-term use. Particularly during a crisis, flexible “living systems” offer tremendous advantages as they’re able to change capacity rapidly. Building living data systems with the cloud in mind allows organizations to respond to a changing world with confidence.

Last summer, the Government Business Council conducted a survey of government employees to understand the impacts of data efficiency on government operations. The report Built to Last: A Survey on Organizational Data Efficiency in Times of Crisis offers key insights into organizational efficacy and whether organizations can adapt to a crisis at speed. It also highlights differences between traditional data systems and living data systems. 

Data needs to be readily available

When the pandemic first hit, many agencies needed to create or transition their systems to allow employees to work remotely. This change tested the limits of existing data systems. Even after finding a cloud service provider, agencies encountered the challenges of migrating their data to the cloud. 

Government organizations had decades of data stored in paper records. Most have been working to transfer these records to a digital format, but the process has been slow. They are also faced with collecting sizable amounts of data in real time from their ongoing services,  which involves interfacing with the public, external vendors, or third-party institutions. 

Building the cloud into a flexible data system can solve both issues. Old records can be digitized and given an easy-to-access home for those who need them. Incoming data, both internal and external, can be made accessible as well. Migrating data to the cloud also doubles as a way to create backups of raw data, adding an extra layer of security. Most importantly, building in the cloud unlocked the capacity to scale when demand rises. 

Data should be updated in real-time

One of the key takeaways from the Government Business Council report is the fact that agencies are better able to adapt at speed when data efficiencies are higher. 74% of organizations with pandemic related functions reported a moderate to severe impact to their jobs at the onset of the pandemic. Of those organizations, the ones reporting their data efficiency as “very good” have largely already recovered. That adaptability directly affects an agency’s ability to make informed decisions during a time of a crisis.

Having a real-time data solution in place lets agencies make near real-time decisions. A great example of this from early in the pandemic is vaccine distribution. Google Cloud supported multiple states, such as the State of Wyoming, in distributing vaccines efficiently while handling challenges such as reaching rural populations. Data systems that gathered real-time patient data made a difference in the number of vaccines distributed. Knowing population data and patient risk factors enabled quick and effective decision-making.

A global pandemic is far from the only crisis that needs effective data analytics. Natural disasters, food deserts, public health issues, and more can all be handled more efficiently by having real-time data at hand. Effective data analytics systems are the digital equal of “having your ear to the ground” in each community. They provide valuable insights into what people  need.

Data needs to be accessible and easy to use

Making data easy to work with and understand sets phenomenal data systems apart from functional ones. Having data in the cloud is a great first step, but agencies need to be able to easily access and quickly use the data to accomplish their goals. This is where traditional data systems fail most often. Traditional IT systems and data strategies are designed for a specific purpose, usually identified before development and implementation begin. That means that when the data living in those systems needs to be used differently, adapting to new requirements can be difficult. 

Data can often feel “locked” in traditional systems; the data is there, but there’s no way to get to it or work with it in a way that meets the needs of a crisis. Flexible data systems address this by allowing for greater accessibility. Google Cloud, for example, has customizable tools, such as Contact Center AI and Document AI, which let agencies work with data in ever-changing ways. This also produces greater data transparency since data sets can be worked with and accessed more easily.

Governments need to respond to the changing needs of their constituents in emergencies. While traditional data systems can handle slowly shifting demands on the system, they do not serve agencies well in a crisis. When urgency, accuracy, and accessibility all matter, flexible systems rise to the challenge. The pandemic has pushed agencies to adapt in real time, and many have realized they need a system that adapts with them.

Google Cloud has a suite of tools to create integrated data ecosystems. These ecosystems can scale with increasing demand, meet dynamic development needs, and adapt to a changing landscape. Data-first decision-making is a core tenet of “living data systems.” Google Cloud data systems have handled everything from administering vaccines to detecting fraud. In each of these applications, a core tenet of data-first decision making was implemented at scale. 

For more insights on how flexible data systems help the public sector, download the full report “Built to Last: A Survey on Organizational Data Efficiency in Times of Crisis.”

At Universe, we serve customers day and night and are always working to make sure they have a great experience, whether online or at one of our live events.

Our technology has to make that possible, and our legacy systems weren’t cutting it anymore. What we needed was a consistent, reliable infrastructure that would help our internal teams provide a fast and innovative ticket-buying experience to customers.

With our data well-managed, we could free up time for our developers to bring new web features to customers, like tailored add-ons at checkout.

Our team of about 20 software engineers needed more flexibility and agility in our infrastructure; we were using various data processing tools, and it wasn’t easy to share data across teams so that everyone saw the same information.

We also needed to incorporate streaming data into the data warehouse to ensure the consistency and integrity of data that’s read in a particular window of time from multiple sources. Our developer teams needed to be able to ship new features faster, and the data back ends were getting in the way.   

In addition, when GDPR regulations went into effect, we needed to make sure all our data was anonymized, and we couldn’t do that with our legacy tools.

Finding the right data tools for the job
To make sure our customers were getting a top-notch online experience, we had to make the right technology choices.

Our first step was to centralize multiple data sources and create a single data warehouse that could serve as the foundation for all of our reporting requirements, both internal and external.

The new technology infrastructure we built had to let us move and analyze data easily, so our teams could focus on using that data and insights to better serve our customers.

Previously, we had lots of siloed systems and applications running in AWS. We did a trial using Redshift, but we needed more flexibility than it offered in how we loaded historical data into our cloud data warehouse. Though we were using MongoDB Atlas for our transactional database, it was important to continue using SQL for querying data.  

The trial task that really sold us on BigQuery was when we wanted to alter a small table that had about 20 million rows, used for internal reporting.

We needed to add a value, but our PostgreSQL system wouldn’t allow it. Using Apache Beam, we set up a simple pipeline that moved data from the original source into BigQuery to see if we could add the column there.

BigQuery ingested the data and let us add the new value in seconds. That was a significant moment that led us to start looking at how we could build end-to-end solutions on Google Cloud. BigQuery gave us multiple options to load our historical data in batches and build powerful pipelines. 

We also explored Google Cloud’s migration tools and data pipeline options. Once we saw how Cloud Dataflow worked, with its Apache Beam back-end, we never looked back. Google Cloud provided us with the data tools we needed to build our data infrastructure. 

Cloud for data, and for users
Introducing new technologies isn’t always simple—companies sometimes avoid it altogether because it’s so hard. But our Google Cloud onboarding process has been easy. 

It took us less than two months to fully deploy our BigQuery data warehouse using the Cloud Dataflow-Apache Beam combination.

Moving to Google Cloud brought us a lot of technology advantages, but it’s also been hugely helpful for our internal users and customers. The data analytics capabilities that we’re now able to offer users has really impressed our internal teams, including developers and DevOps, even those who haven’t used this type of technology before.

Some internal clients are already entirely self-service. We’ve hosted frequent demos, and also hosted some “hack days,” where we share knowledge with our internal teams to show them what’s possible. 

We quickly found that BigQuery helped us solve scale and speed problems.

One of our main pain points had been adding upsell opportunities for customers during the checkout process. The legacy technology hadn’t allowed us to quickly reflect those changes in the data warehouse. With BigQuery, we’re able to do that, and devote fewer resources to making it happen. We’ve also eliminated the time we were spending tuning memory and availability, since BigQuery handles that. Database administration and tuning required specialized knowledge and experience and took up time. With BigQuery, we don’t have to worry about configuring that hardware and software. It just works.

We’ve also eliminated the time we were spending tuning memory and availability, since BigQuery handles that. Database administration and tuning required specialized knowledge and experience and took up time. With BigQuery, we don’t have to worry about configuring that hardware and software. It just works.

Two features in particular that we implemented using BigQuery have helped us improve the performance of our core transactional database. First, using Cloud Dataflow to convert raw MongoDB logs to structured rows under a BigQuery table, which we can then query using SQL to identify slow or underperforming queries. Second, we can now query multiple logging tables using wildcards, since we load Fastly logs to BigQuery. 

Along with MongoDB Atlas as our main transactional database, much of our infrastructure now runs as Google Cloud microservices using Google Kubernetes Engine (GKE), including the home page and our payment system. Kubernetes cron jobs power background scheduled jobs, and we also use Cloud Pub/Sub. Cloud Storage handles any data storage if any space constraints emerge. 

Our overall performance has increased by about 10x with BigQuery. Both our customers and internal clients, like our sales and finance teams, benefit from the new low-latency reporting. Reports that used to be weekly or monthly are now available in near-real time. It’s not only faster to read records, but faster to move the data, too.

We have Cloud Dataflow pipelines that write to multiple places, and the speed of moving and processing data is incredibly helpful. We stream in financial data using Cloud Dataflow in streaming mode, and plan to have different streaming pipelines as we grow. We have several batch pipelines that run every day. We can move terabytes of data without performance issues, and process more than 100,000 rows of data per second from the underlying database. It used to take us a month to move that volume of data into our data warehouse. With BigQuery, it takes two days.

We’re also enjoying how easy and productive these tools are. They make our life as software engineers easier, so we can focus on the problem at hand, not fighting with our tools. 

What’s next for Universe
Our team will continue to push even more into Google Cloud’s data platform. We have plans to explore Cloud Datastore next. We’re also moving our databases to PostgreSQL on GCP, using Cloud Dataflow and Beam. BigQuery’s machine learning tools may also come into play as Universe’s cloud journey evolves, so we can start doing predictive analytics based on our data. We’re looking forward to gaining even more speed and agility to meet our business goals and customer needs.    

Last week in our BigQuery Reference Guide series, we spoke about the BigQuery resource hierarchy – specifically digging into project and dataset structures. This week, we’re going one level deeper and talking through some of the resources within datasets. In this post, we’ll talk through the different types of tables available inside of BigQuery, and how to leverage routines for data transformation. Like last time, we’ll link out to the documentation so you can learn more about using these resources in practice. 

What is a table?

A BigQuery table is a resource that lives inside a dataset. It contains individual records organized into rows, with each record composed of columns (also called fields) where a specified data type is enforced. BigQuery supports numerous different data types including GEOGRAPHY for geospatial data, STRUCT and ARRAY for more complex data, and new parameterized data types to add specific constraints like the number of characters in a string. 

Data access can also be controlled at the table, row and column levels; more details on data governance will be covered later in the series. Metadata, such as descriptions and labels, can be used for surfacing information to end users and as tags for monitoring. You can create and manage a table directly in the UI, through the API / Client SDKs or in a SQL query using a DDL statement.

  bq show \
--schema \
--format=prettyjson \
project1:dataset3.table

Managed and external tables

Managed tables are tables that are backed by native BigQuery storage, which has many benefits that improve query performance including support for partitions and clusters. We’ll cover more details on BigQuery storage later in this series. Another advantage of using a managed table is that BigQuery allows you to use time travel to access data from any point within the last seven days and query data that was updated, expired or deleted. And now you can even create a snapshot of your table, to preserve its contents at a given time. 

  # create a snapshot of transactions in the library_backup dataset as of one hour ago
CREATE SNAPSHOT TABLE
  library_backup.sales
  CLONE retail.transactions
  FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);

While managed tables store data inside BigQuery storage, external tables are backed by storage external to BigQuery. BigQuery currently supports creating an external table from Cloud Storage, Cloud Bigtable and Google Drive. Besides an external table, you can create a connection to Cloud SQL, which is somewhat analogous to an external dataset. Here, you can leverage federated queries to send a query that executes in Cloud SQL but returns the results to be used within BigQuery.

Using external tables or federated queries may result in queries that aren’t as fast as if the data had been stored in BigQuery itself. However, they can be useful for some data transformation patterns –  for example, you may want to schedule a  DDL/DML query that hydrates a managed table using a federated query, which selects and transforms data from Cloud SQL. An external table might also be useful for multi-consumer workflows where BQ storage isn’t the source of truth. Like, if you have a dataproc cluster accessing data in a Cloud Storage bucket that you’re not quite ready to port into BigQuery (although I do recommend taking a look at our connector if you need some convincing). You can learn more about querying external data in this video. 

Logical and materialized views

In BigQuery, you can create a virtual table with a logical view or a materialized view. With logical views, BigQuery will execute the SQL statement to create the view at run time, it will not save the result anywhere. Additionally, you can grant users access to an authorized view to share query results without giving them access to the underlying tables.

  # create a view that aggregates daily sales from a retail transaction table
CREATE VIEW retail.daily_sales as (
SELECT date(t.transaction_timestamp) as date, sum(li.sale_price) as total_sales
  FROM retail.transaction_detail as t
  LEFT JOIN UNNEST(t.line_items) as li
  GROUP BY 1)

 On the other hand, materialized views are re-computed in the background when the base data changes. No user action is required – they are always fresh! Better yet, if a query, or part of a query, against the source table can be resolved by querying the materialized view, BigQuery will reroute for improved performance. However, materialized views use a restricted SQL syntax and a limited set of aggregation functions. You can find details on limitations here.  

  # create a materialized view that aggregates daily sales 
CREATE MATERIALIZED VIEW retail.daily_sales as (
SELECT date(t.transaction_timestamp) as date, sum(li.sale_price) as total_sales
  FROM retail.transaction_detail as t
  LEFT JOIN UNNEST(t.line_items) as li
  GROUP BY 1)

Temporary and cached results tables

Aside from the tables we’ve mentioned so far, you can also create a temporary managed table using the TEMP or TEMPORARY keyword. This table is saved in BigQuery storage and can be referenced for the duration of the script. Temporary tables can be a good alternative to WITH clauses because the defining query is only executed  once as opposed to being inlined every place the alias is referenced.

It’s also important to mention that BigQuery writes all query results to a table – one either explicitly identified by the user or to a cached results table. Temporary, cached results tables are maintained per-user, per-project. There are no storage costs for temporary tables.

User defined functions & procedures

In BigQuery, a routine is either a user defined function (UDF) or a procedure. Routines allow you to re-use logic and handle your data in a unique way. A UDF is a function that is created using either SQL or Javascript, it takes arguments as input and returns a single value as an output. UDFs are often used for cleaning or re-formatting data. For example, extracting parameters from a URL string,  restructuring nested data, or cleaning up strings:

  # UDF to clean up string values
 CREATE OR REPLACE FUNCTION
  my_dataset.cleanse_string_test (text STRING)
  RETURNS STRING
  AS (REGEXP_REPLACE(LOWER(TRIM(text)), '[^a-zA-Z0-9 ]+', ''));

We even have a community driven open-source repository of BigQuery UDFs! Just like logical views, you can create an authorized UDF that protects aspects of the underlying data. For more details on UDFs checkout our video here. You might also want to take a look at table functions – a preview feature where you can create a SQL UDF that returns a table instead of a scalar value. 

Procedures, on the other hand, are blocks of SQL statements that can be called from other queries. Unlike UDFs, stored procedures can return multiple values or no values – which means you can run them to create or modify tables. In BigQuery, you can also leverage scripting capabilities within procedures to control execution flow with IF and WHILE statements. Plus, you can call your UDFs within your procedure! These aspects make procedures great for extract-load-transform (ELT) driven workflows.

  # Procedure to create daily sales rollup, starting from startDate until endDate
 CREATE OR REPLACE PROCEDURE my_dataset.sum_sales(startDate STRING, endDate STRING)
  BEGIN
  CREATE OR REPLACE TABLE retail.sales_result
  AS (SELECT 
          date(t.transaction_timestamp) as date, 
          sum(li.sale_price) as total_sales
      FROM retail.transaction_detail as t
      LEFT JOIN UNNEST(t.line_items) as li
      WHERE transaction_timestamp >= TIMESTAMP(startDate) AND transaction_timestamp <= TIMESTAMP(endDate) 
      GROUP BY 1);
  END;
  CALL retail.sum_sales('2020-08-01', '2020-01-20');

To ensure consistent analytics across your organization, I recommend that you create a library dataset to house UDFs and procedures. You can easily grant everyone in your organization the BigQuery Data Viewer role to the library dataset so that all analysts use consistent and up-to-date logic in their queries. 

Stay tuned!

We hope this gave you an understanding of how to leverage some of the different resources inside of a BigQuery dataset, and to help you make decisions like using native versus external storage, logical versus materialized views, and user defined functions or procedures. 

Next up we’ll be talking about workload management in BigQuery by taking a look at jobs and the reservation model. Be sure to keep an eye out for more in this series by following me on LinkedIn and Twitter, and subscribing to our Youtube channel.