BigQuery’s User-friendly SQL is Like a Cool Drink for Hot Summer
5674
Of your peers have already read this article.
2:30 Minutes
The most insightful time you'll spend today!
With summer just around the corner, things are really heating up. But you’re in luck because this month BigQuery is supplying a cooler full of ice cold refreshments with this release of user-friendly SQL capabilities.
We are pleased to announce three categories of BigQuery user-friendly SQL launches: Powerful Analytics Features, Flexible Schema Handling, and New Geospatial Tools.
Powerful Analytics Features
These powerful SQL analytics features provide greater flexibility to analysts for organizing, filtering, and rendering data in BigQuery than ever before. You can enable spreadsheet-like functionality on summarized data using PIVOT and UNPIVOT and filter irrelevant data in analytic functions using QUALIFY.
Through this section, we will become familiar with these new features through examples using the BigQuery Public dataset, usa_names.
PIVOT/UNPIVOT (Preview)
One of the most time-consuming tasks for data analytics practitioners is wrangling data into the right shape. SQL is great for wrangling data, but sometimes you want to reformat a table as you would in a spreadsheet, pivoting rows and columns interchangeably. To support this use case, we are pleased to introduce PIVOT and UNPIVOT operators in BigQuery. PIVOT creates columns from unique values in rows by aggregating values, and UNPIVOT reverses this action.The example below uses PIVOT on bigquery-public-data.usa_names.usa_1910_current to show the number of males and females born each year, representing each gender as a column. Then UNPIVOT reverses this action.
Language: SQL
-- we start with SQL to create a simple table-- we only include gender, year, and number.CREATE TABLEmydataset.sampletable1 AS (SELECTGender,Year,SUM(Number) AS NumberFROM`bigquery-public-data.usa_names.usa_1910_current`WHEREYear >= 2017GROUP BYGender, Year);-- The resulting table:--+----------------------------------------+--| Gender | Year | Number |--+----------------------------------------+--| F | 2019 | 1353716 |--| F | 2017 | 1403989 |--| F | 2018 | 1380382 |--| M | 2018 | 1568678 |--| M | 2019 | 1538056 |--| M | 2017 | 1604609 |--+----------------------------------------+-- use PIVOT to create columns for “female” and “male”CREATE TABLEmydataset.Pivoted ASSELECTyear, male, femaleFROMmydataset.sampletable1PIVOT( SUM(Number) FOR gender IN ('M' AS male,'F' AS female))ORDER BYyear;-- The resulting pivoted table:--+----------------------------------------+--| Year | female | male |--+----------------------------------------+--| 2017 | 1403989 | 1604609 |--| 2018 | 1380382 | 1568678 |--| 2019 | 1353716 | 1538056 |--+----------------------------------------+-- UNPIVOT reverses the row/column rotation of PIVOT.SELECT*FROMmydataset.PivotedUNPIVOT(number FOR gender IN (male AS 'M',female AS 'F'));
QUALIFY (Preview)
More advanced users of SQL know the power of analytic functions (aka window functions). These functions compute values over a group of rows, returning a single result for each row. For example, customers use analytic functions to compute a grand total, subtotal, moving average, rank, and more. With the announcement of support for QUALIFY, BigQuery users can now filter on the results of analytic functions by using the QUALIFY clause.
QUALIFY belongs in the family of query clauses used for filtering along with WHERE and HAVING. The WHERE clause is used to filter individual rows in a query. The HAVING clause is used to filter aggregate rows in a result set after aggregate functions and GROUP BY clauses. The QUALIFY clause is used to filter results of analytic functions.
To show the utility of QUALIFY, the example below uses QUALIFY to return the top 3 female names from each year in the last decade using from bigquery-public-data.usa_names.usa_1910_current.
Language: SQL
-- QUALIFY filters the result of the RANK functionSELECTname,year,SUM(number) AS total,RANK() OVER (PARTITION BY yearORDER BY SUM(number) DESC) AS rankFROM`bigquery-public-data.usa_names.usa_1910_current`WHEREgender = 'F'AND YEAR >= 2010GROUP BY 1,2QUALIFY RANK <= 3ORDER BY 2,4;
Flexible Schema Handling
New SQL for administrators and data engineers enables table renaming for data pipeline processes, as well as flexible column management.
Table Rename (GA)
In data pipeline processes, tables are often created and then renamed so that they can make way for the next iteration of the pipeline run. To accomplish this, customers need a mechanism by which they can create a table and then subsequently rename it. Now if customers want to change this name using SQL, they can. Using the simple syntax that ALTER TABLE RENAME TO provides, customers will be able to rename a table after creation to clear the way for the next iteration of tables in the data pipeline.
Language: SQL
-- create a sample table “tablename” in “mydataset”.-- You will rename this table.CREATE OR REPLACE TABLE dataset.tablename(col1 STRING,col2 NUMERIC);-- if this table “tablename” becomes obsolete-- perform Table Rename to “obsoletetable”ALTER TABLEmydataset.name RENAME TO obsoletetable;
DROP NOT NULL constraints on a column (GA)
While BigQuery has historically provided many tools available in the UI, CLI and APIs, we know that many administrators prefer interfacing with the database using SQL. BigQuery recently released DDL statements which enable data administrators to provision and manage datasets and tables, greatly simplifying provisioning and management. Today, we continue the next addition in this line of releases by announcing ALTER COLUMN DROP NOT NULL constraint on a column:
- ALTER COLUMN DROP NOT NULL allows the administrator to remove the NOT NULL constraint from a column in BigQuery.
Language: SQL
-- create a table to store credit card numbers-- the business requires this field, so-- include a NOT NULL constraintCREATE TABLEmydataset.customers(credit_card_number STRING NOT NULL);-- if needs of the business no longer require this field,-- the customer can allow null entries in this column-- by dropping the constraintALTER TABLEmydataset.customersALTER COLUMN credit_card_number DROP NOT NULL;
CREATE VIEW with column list (GA)
Views are used ubiquitously by BigQuery customers to capture business logic. Oftentimes, BigQuery users have business requirements to assign aliases to columns in views. Now BigQuery supports doing so upon view creation in a column name list format with the release of CREATE VIEW with column list syntax.
Language: SQL
-- aliases list1 and list2 can be assigned in a list formatCREATE VIEWmyview (list1, list2) ASSELECTcolumn_1, column_2FROMmydataset.exampletable
New Geospatial Tools
ST_POINTN, ST_STARTPOINT, and ST_ENDPOINT
Geospatial data is incredibly valuable to data analytics customers dealing with data from the physical world. BigQuery has very strong geospatial function support to help customers process marketing data, track storms, or manage self-driving cars. Particularly for analyzing vehicle or location tracking data, we’re thrilled to provide three new functions to allow users to easily extract or filter on key points:
For example, when working with vehicle histories, ST_POINTN, ST_STARTPOINT, and ST_ENDPOINT allow users to extract elements such as the start and the end of a trip. For identifying origin-destination pairs these functions will make that task much easier.
Language: SQL
-- pull the first, second, penultimate and final points-- from a linestringWITH linestring as (SELECT ST_GeogFromText('linestring(1 1, 2 1, 3 2, 3 3)') g)SELECTST_StartPoint(g) AS first, ST_EndPoint(g) AS last,ST_PointN(g,2) AS second, ST_PointN(g, -2) as second_to_lastFROMlinestring+--------------+--------------+--------------+----------------+| first | last | second | second_to_last |+--------------+--------------+--------------+----------------+| POINT(1 1) | POINT(3 3) | POINT(2 1) | POINT(3 2) |+--------------+--------------+--------------+----------------+
As sure as a hot summer day pairs well with an ice-cold beverage, these new user-friendly SQL features in BigQuery pair well with your data analytics workflows. To learn more about BigQuery, visit our website, and get started immediately with the free BigQuery Sandbox.
3818
Of your peers have already downloaded this article
4:30 Minutes
The most insightful time you'll spend today!
Moving to the cloud offers enormous benefits for businesses. Yet there are risks as well. The challenge is multidimensional, with far reaching implications not just for the solutions that will run in the cloud, but also for the technologies that support them, the people who need to implement them, and the processes that govern them.
The rubric of people, process, and technology is a familiar one. But how do you harness it to move forward?
The Google Cloud Adoption Framework builds a structure on the rubric of people, process, and technology that you can work with, providing a solid assessment of where you are in your journey to the cloud and actionable programs that get you to where you want to be. It’s informed by Google’s own evolution in the cloud and many years of experience helping customers.
You can use the framework yourself to make an assessment of your organization’s readiness for the cloud and what you’ll need to do to fill in the gaps and develop new competencies.This framework can help you find your way, from your first project all the way to becoming a cloud-first organization.
Go Green with Google’s Latest Tool and Pick the Most Sustainable Cloud Region
5659
Of your peers have already read this article.
1:30 Minutes
The most insightful time you'll spend today!
As a Google Cloud customer, your carbon footprint is already carbon neutral: Google first achieved carbon neutrality in 2007, and has been purchasing enough solar and wind energy to match 100% of its global electricity consumption since 2017. Now, Google is targeting a new sustainability goal: operating on carbon-free energy (CFE) 24/7, everywhere, by 2030.
We want to empower you to make more sustainable decisions and progress with us towards this 24/7 carbon-free future. Earlier this year, we published the carbon characteristics of our Google Cloud regions. Later, we introduced a simple tool to help you pick a Google Cloud region, taking variables like price, latency and sustainability into account. Our next question was: what’s the best way to surface that sustainability info when you’re actually picking a region for your cloud resources?
Starting today, we are indicating regions with the lowest carbon impact inside Cloud Console location selectors. Available today for Cloud Run and Datastream, you’ll see it roll out to more Google Cloud offerings over time:
Regions that feature the “Lowest CO2” and the leaf have a CFE% of at least 75%, or, if the information is not available for this region yet, a grid carbon intensity of maximum 200 gCO2eq/kWh. You can read more about how we calculate these metrics in our documentation.
Before releasing this feature, we ran experiments to measure its impact: Users who were exposed to the enhanced region picker were 19% more likely to select a “low carbon” region for their Cloud Run service—a significant lift. These results show that by displaying carbon information in context of when you make the decision of picking a region, we are helping you make more sustainable decisions.
By sharing and displaying carbon information of Google Cloud regions, together we’re making tangible progress towards our goal of a carbon-free future. Learn more about Carbon free energy for Google Cloud regions.
Cloud Migration and Modernization is ‘Easier Done than Said’ with Google Cloud RAMP!
DOWNLOAD INFOGRAPHIC3245
Of your peers have already downloaded this article
3:00 Minutes
The most insightful time you'll spend today!
The reliance on cloud compute, storage and network has accelerated with the growing usage of digital products and services. To keep up with the customer demands and deliver digital solutions, many companies are struggling through their cloud migration and modernization journeys. But not anymore, says Google Cloud with the Rapid Assessment & Migration Program (RAMP)!
Download the infographic to have succinct view of what cloud migration and modernization would look like by RAMPing up! Bid Adieu to delays and budget overspend with the tools, resources, partners and fundings with RAMP.
Efficient, Safe and Dynamic Gaming Experience: Aristocrat’s Digital Journey on Google Cloud
4761
Of your peers have already read this article.
2:00 Minutes
The most insightful time you'll spend today!
Since Aristocrat’s founding in 1953, technology has constantly transformed gaming and the digital demands on our gaming business are a far cry from challenges we faced when we started. As we continue to expand globally, security and compliance are top priorities.
Managing IT security for several gaming subsidiaries and our core business became more complex as we entered into new markets and scaled up our number of users. We needed a centralized platform that could give us full visibility into all of our systems and efficient monitoring capabilities to keep data and applications secure. We also needed the ability to secure our systems without compromising user experiences.
We turned to Google Cloud and Splunk to better manage complexity and support highly efficient, secure, and more dynamic gaming experiences for everyone. We are committed to using today’s modern technologies to give players more optimal experiences.
Bringing our digital footprint into the cloud
When we set out on our digital transformation, we looked to address many business requirements.
These requirements included:
- Regulation: We wanted a platform that could efficiently address our industry’s stringent and global regulatory compliance requirements.
- Player experience: Our IT environment must support smooth gaming experiences to keep users engaged and satisfied.
- Scalability: As we grow and diversify, meeting the changing demands of an increasingly global gaming community, we need an easily scalable platform to align with our current and future needs.
Google Cloud offered us the perfect foundation through solutions such as Compute Engine, Google Kubernetes Engine, BigQuery, and Google Cloud Storage. These acted as the right infrastructure components for us for the following reasons:
- Google Cloud is globally accessible and supports compliance, helping to streamline security and regulatory processes for our team.
- With Google Cloud, we can manage our entire development and delivery processes globally with fast and efficient reconciliation of regional compliance requirements.
- When we need to adjust existing infrastructure or deliver new capabilities, Google Cloud accelerates the process and takes the heavy lifting off of our team.
- Google Cloud allows us to support tens of thousands of players on each of our apps while experiencing minimal downtime and low latency. The importance of this support can’t be underestimated in an industry where players have little to no patience if lags in games occur.
We migrated our back-office IT stack alongside our consumer-facing production applications to Google Cloud given our positive experiences with compliance, security, scalability, and process management. This migration has significantly accelerated our digital transformation while streamlining our infrastructure for faster and more cost-effective performance.
In many ways, Google Cloud has been, with maybe a pun intended, a game-changer for us. For instance, when we suddenly had to support a lot of remote work during the COVID-19 pandemic, native identity and access management tools in Google Cloud allowed us to retire costly VPNs used for backend access and quickly adopt a more easily managed, cost-effective zero-trust security posture.
Accessing vital third-party partners and managed services
Aristocrat has many IT needs best addressed in a multi-cloud environment. Google Cloud is particularly attractive given its strong cloud interoperability, as well as the many products and services available on Google Cloud Marketplace. The marketplace accelerated our deployment of key third-party apps including Splunk and Qualys.
Given the personal information we store and the global regulatory compliance statutes we must oblige, security lies at the heart of our business. Splunk is a critical component of our digital transformation because it offers solutions that provide the enhanced monitoring capabilities and visibility we need. The integration between Splunk and Google Cloud gives us confidence that our data is secure. We know our data can be secure in Google Cloud, while simplified billing through Google Cloud Marketplace makes payments and license tracking easier for our procurement team.
As part of our protected environment, we use the Splunk platform as our security information and event management system, leveraging the InfoSec app for Splunk that provides continuous monitoring and advanced threat detection to significantly improve our security.
We can manipulate and present data in Splunk in a way that provides us with a single pane-of-glass for our hybrid, multi-cloud environment and our third-party apps and systems. Splunk observability tools have likewise helped us to track browser-based applications like our online gaming apps to monitor details related to security and performance.
Splunk and Google Cloud have transformed how we operate. We can now quickly ingest and analyze data at scale within our refined approach to security management by offloading software management to Splunk and Google Cloud. This ability enables us to approach security more strategically, and positions us to integrate more AI/ML capabilities into our products for even greater governance and performance.
This is just the beginning of our journey with Splunk and Google Cloud. We’re excited to see the innovation we can continue bringing to the gaming community worldwide.
Tau VMs Joins Google Cloud to Offer Cost-effective Performance of Scale-out Workloads
5607
Of your peers have already read this article.
2:00 Minutes
The most insightful time you'll spend today!
Scale-out workloads demand the best combination of performance and price to bring down the cost of delivering applications, all while providing an excellent user experience. We are excited to announce a new virtual machine (VM) family, Tau VMs, coming to Google Cloud. Tau VMs extend Compute Engine’s VM offerings with a new option optimized for cost-effective performance of scale-out workloads.
T2D, the first instance type in the Tau VM family, is based on 3rd Gen AMD EPYCTM processors and leapfrogs the VMs for scale-out workloads of any leading public cloud provider available today, both in terms of performance and workload total cost of ownership (TCO). The x86 compatibility provided by these AMD EPYC processor-based VMs gives you market-leading performance improvements and cost savings, without having to port your applications to a new processor architecture.
As illustrated below, Tau VMs offer 56% higher absolute performance and 42% higher price-performance (est. SPECrate2017_int_base) compared to general-purpose VMs from any of the leading public cloud vendors.
SPECrate is a trademark of the Standard Performance Evaluation Corporation. More information available at www.spec.org
What our customers and partners are saying
Snap
“At Snap, it is critical for our business to continue improving our scale-out compute infrastructure for key Snapchat capabilities like AR, Lenses, Spotlight and Maps,” said Cody Powell, Senior Engineering Manager, Snap Inc. “We were impressed when we tested Google Cloud’s new Tau VMs with Google Kubernetes Engine. While it’s early days, we believe we can gain double digits in infrastructure performance improvements for key workloads—enabling us to do more with less and invest even more in new features for our amazing Snapchat community.”
Twitter
“High performance at the right price point is a critical consideration as we work to serve the global public conversation,” said Nick Tornow, Platform Lead, Twitter. “We are excited by initial tests that show potential for double digit performance improvement. We are collaborating with Google Cloud to more deeply evaluate benefits on price and performance for specific compute workloads that we can realize through use of the new Tau VM family.”
DoiT
“DoiT partners with leading cloud vendors who are focused on growth and cost optimization,” said Yoav Toussia-Cohen, CEO, DoiT International. “In our preliminary testing of Google’s new Tau VMs with the Coremark benchmark, we were thrilled to see the incredible performance at 50% better than a comparable ARM-based offering from another leading public cloud. With Tau VMs, Google Cloud has set a new bar for price-performance, making the cloud even more accessible to digital-native companies. We are excited to bring Google’s Tau VMs to our joint customers.”
Designed for demanding scale-out workloads
Tau VMs bring the benefit of Google’s long-standing experience engineering platforms for scale-out workloads to our customers. They come in multiple predefined VM shapes, with up to 60vCPUs per VM, and 4GB of memory per vCPU. They offer up to 32 Gbps networking bandwidth and a wide range of network attached storage options, making Tau VMs ideal for scale-out workloads including web servers, containerized microservices, data-logging processing, media transcoding, and large-scale Java applications.
Google Kubernetes Engine support
Google Kubernetes Engine (GKE) is the de facto standard for organizations looking for advanced container orchestration, delivering the highest levels of reliability, security, and scalability. GKE supports Tau VMs on day 1, helping you optimize price-performance for your containerized workloads. You can add Tau VMs to your GKE clusters by specifying the T2D machine type in your GKE node-pools.
Pricing
Tau VMs will be priced to support significant TCO and price-performance improvements for your cloud applications. A 32vCPU VM with 128GB RAM will be priced at $1.3520 per hour for on-demand usage in us-central1.
Coming soon to a Google Cloud region near you
If you are interested in trying out T2D VMs when they become available in Q3 2021 please sign-up here.
More Relevant Stories for Your Company
Google’s Research and Data Insights Solutions to Power Drug Development and Clinical Research
In order to be successful, research needs to be replicable, so scientists can build on past work and insights. However, an article in Nature warned that as much as 50% of published drug development research could not be reproduced in subsequent trials. As a result, promising drug candidates sometimes led to disappointment,
Frost & Sullivan: State of Digital Transformation in India
Digital transformation is about how digital technologies can connect people and processes to solve challenges that traditional methodologies could not. While this transformation is imperative for businesses of all sizes, the Frost & Sullivan Enterprise Cloud Maturity Index (ECMI) assessment indicates that around 85% of CXOs want to digitally transform
How Google Classroom Helps State of Iowa Employees Serve the Public Better
Organizations are pressed with the need to engage, retain, and upskill employees with many positions staying fully or partially remote as the pandemic continues. The Center for Digital Government (CDG) reports that 74% of state and local governments believe they will continue hybrid operations for the long-term. This transition is
Encryption in Transit: The Next Level in Cloud Security
Security is often a deciding factor when choosing a public cloud provider. At Google Cloud, security is of the utmost importance and the security strategy encompass authentication, integrity, and encryption, for both data at rest and in transit. Hence, Google Cloud encrypts and authenticates all data in transit at one
