01 Jun 2020 3:55pm GMT

31 May 2020 9:06pm GMT

30 May 2020 2:32pm GMT

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.

Code

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.

Working Example

<script src="https://gist.github.com/paslandau/03c73ee5eef2ce217af82a8f7edcb125.js"><script src="https://gist.github.com/paslandau/03c73ee5eef2ce217af82a8f7edcb125.js">

Run on BigQuery

Open in BigQuery Console

Links

• Gist on Github
• Example on BigQuery

Use cases

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 asc

29 May 2020 2:00pm GMT

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

Code

WITH filtered_data as (
  SELECT
    id
  FROM
    table
  WHERE
    id BETWEEN 5 and 10
)
SELECT
  *
FROM
  filtered_data  

Working Example

<script src="https://gist.github.com/paslandau/662a42456dc9dc77b6cbdb1d6acb8c99.js"><script src="https://gist.github.com/paslandau/662a42456dc9dc77b6cbdb1d6acb8c99.js">

Run on BigQuery

Open in BigQuery Console

Links

• Gist on Github
• Example on BigQuery
• Answer to "How to create temporary table in Google BigQuery" on Stackoverflow

Use cases

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_partition

29 May 2020 1:00pm GMT

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

• MySQL variables:

  SET @foo = 'bar';
  

• PostgreSQL variables:

  foo varchar := 'bar';
  

Code

DECLARE foo_var STRING DEFAULT "foo";

SELECT foo_var

Working Example

<script src="https://gist.github.com/paslandau/0cb51ba9e532a71fff5108f156afd2f5.js"><script src="https://gist.github.com/paslandau/0cb51ba9e532a71fff5108f156afd2f5.js">

Run on BigQuery

Open in BigQuery Console

Links

• Gist on Github
• Example on BigQuery
• Answer to "Setting Big Query variables like mysql" on Stackoverflow

Use cases

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

29 May 2020 12:00pm GMT

28 May 2020 9:41am GMT

Truncated by Planet PHP, read more at the original (another 19178 bytes)

28 May 2020 8:18am GMT

28 May 2020 3:38am GMT

@dead_lugosi Show Notes TwilioQuestLead your intrepid crew on a mission to save The Cloud in TwilioQuest, a PC role-playing game inspired by classics of the 16-bit era." Audio This episode is sponsored by Using the WordPress REST API

The post Interview with Margaret Staples appeared first on Voices of the ElePHPant.

27 May 2020 11:22am GMT