BigQuery allows to define nested and repeated fields
in a table. Although this is very powerful, it makes it much more complex to retrieve the
data if one is not used to such structures. Especially beginners tend to use an
UNNEST statement
on the nested fields, followed by a huge
GROUP BY statement on the not-originally-repeated fields. Imho, using
expression subqueries
is oftentimes the better approach here.
SELECT
id,
(SELECT value from t.repeated_fields LIMIT 1)
FROM
table t Caution: When using expression subqueries, you need to make sure that the result is a single value (scalar or array), otherwise you will get the error message
Scalar subquery produced more than one element
In the example code above this is ensured by enforcing one result via LIMIT 1.
The most prominent use case is probably the BigQuery export schema of Google Analytics. To be honest, I also feel that the schema is not very friendly for newcomers with its ~30 RECORD-type (nested) fields and 300+ columns.
In a nutshell, each row represents one session.
A session consists of multiple hits. Those hits are also available in the nested and repeated hits field. But wait, there is more...
Each hit can have a number of so called customDimensions (meta data that can be attached to each hit). So the resulting table structue looks something
like this:
- field_1
- field_2
- hits
- field_1
- field_2
- customDimensions
- index
- value The following example uses the public Google Analytics sample dataset for BigQuery and shows a couple of sample expression subqueries
SELECT
fullVisitorId,
visitStartTime,
TIMESTAMP_SECONDS(visitStartTime) as started_at,
TIMESTAMP_SECONDS(visitStartTime + CAST( (SELECT time from t.hits ORDER BY hitNumber DESC LIMIT 1) /1000 AS INT64)) as ended_at,
(SELECT COUNT(*) from t.hits) as hit_count,
(SELECT page.hostname || page.pagePath from t.hits WHERE isEntrance = TRUE) as landing_page,
(
SELECT
(SELECT COUNT(*) from h.customDimensions)
FROM
t.hits h
WHERE
hitNumber = 1
) as customDimension_count_of_first_hit,
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_20170801` t
ORDER BY
visitStartTime ascIn Google BigQuery we can define named subqueries via WITH clauses.
Those WITH clauses are a very comfortable way to structure complex queries as it allows to reference those queries like actual tables later on.
Note: BigQuery also supports actcual temporary tables via CREATE TEMPORARY TABLE. See the official documention on
temporary tables for further infos.
This is out of scope for this snippet, though.
WITH filtered_data as (
SELECT
id
FROM
table
WHERE
id BETWEEN 5 and 10
)
SELECT
*
FROM
filtered_data 
Named subqueries are a great way to structure complex queries and give sub-results a meaningful name. When working with partitioned tables, I always use temporary tables via WITH to make sure I restrict the query to scan only a limited number of partitions.
Conceptual example:
DECLARE from_date TIMESTAMP DEFAULT "2018-04-09";
DECLARE to_date TIMESTAMP DEFAULT "2018-04-10";
WITH huge_table_partition as(
SELECT
*
FROM
huge_table
WHERE
_PARTITIONTIME BETWEEN from_date AND to_date
)
SELECT
*
FROM
huge_table_partitionWe can use variables by defining them with a DECLARE statement,
e.g.
DECLARE foo STRING DEFAULT "foo";
#DECLARE <variable> <type> DEFAULT <value>;with <type> being one of the BigQuery's built-in standard-sql data types
This is equivalent to variables of other SQL databases, e.g.
SET @foo = 'bar';foo varchar := 'bar';DECLARE foo_var STRING DEFAULT "foo";
SELECT foo_var
Hardcoding variables is generally considered a bad practice as it makes it harder to understand and modify a query. A frequent use case for me is the definition of date ranges (from and to dates) that are used for querying partitioned tables:
DECLARE from_date DATE DEFAULT DATE("2018-04-09");
DECLARE to_date DATE DEFAULT DATE("2018-04-10");
WITH data as(
SELECT
1 as id,
DATE("2018-04-08") AS date,
UNION ALL SELECT 2, DATE("2018-04-09")
UNION ALL SELECT 3, DATE("2018-04-10")
UNION ALL SELECT 4, DATE("2018-04-11")
)
SELECT
id,
date
FROM
data
WHERE
date BETWEEN from_date AND to_date
Why the Grumpy Designer Will Keep Working from Home, Thank You – A look at why the work-from-home lifestyle is too good to give up.
An Infinitely Scrollable Vertical Menu – Learn how to create a unique scrolling effect on a classic vertical menu.
The Third Age of JavaScript – A look at what the next 10 years may bring to JavaScript.
How to Spot Terrible Client Business Ideas – Tips for professionally-distancing yourself from ideas destined to fail.
The Easiest Guide to Webflow for Beginners – Get started with the Webflow app using this helpful guide.
Best GitHub Repos for Web Developers – Level up on your knowledge by following this list of repositories.
The Fastest Google Fonts – Benchmarking Google Fonts to see which embed technique performs the best.
Keyframes.app – Generate awesome CSS animations with this web-based tool.
5 Things a Modern CMS Shouldn’t Do – The modern CMS has made great strides, but there are still areas that need improvement.
Fluor.js – A tiny JavaScript library for easily adding interactions and effects to your website.
Gradihunt – Find or generate the perfect CSS gradient for your projects.
10 Free InDesign Business Proposal Templates – Make a great first impression with these beautiful InDesign templates.
TabNotes – A free Chrome extension that lets you take Markdown-supported notes in a new browser tab.
Figurative – Get all the things you love about Figma on your iPad Pro with this free iOS app.
Let’s Make One of Those Fancy Scrolling Animations Used on Apple Product Pages – Got Apple envy? Use this tutorial to recreate one of their most prominent website effects.
The 10 Best HDR Effect Photoshop Actions – Use this collection to create professional-grade effects on your images.
The post Weekly News for Designers № 542 appeared first on Speckyboy Design Magazine.