BigQuery Arrays (Repeated Fields)

Рет қаралды 12,022

2 жыл бұрын

In this video, we go over the array data type in BigQuery SQL. Note that every flavor of SQL has a different set of features, so some BigQuery features described here are not present in others.
**Errata: From 5:24-6:27 I flipped OFFSET and SAFE_OFFSET in the video. OFFSET() triggers an error and stops execution if the index is out of bounds. SAFE_OFFSET() returns NULL if the index is out of bounds.
0:05 What are arrays in BigQuery?
0:41 Why use arrays? Denormalization
1:39 Why use arrays? Avoid dealing with columns/rows
2:48 Creating arrays (ARRAY, ARRAY_AGG, GENERATE_ARRAY)
4:55 Arrays and structs (STRUCT)
5:24 Accessing individual elements (OFFSET, ORDINAL, SAFE_OFFSET)
6:27 Accessing multiple elements (UNNEST)
7:19 Unnest with indexes (WITH OFFSET)
8:18 Flattening arrays (CROSS JOIN UNNEST)
9:52 Scanning arrays (ARRAY_LENGTH, EXISTS, IN)
10:24 Other array operations (ARRAY_TO_STRING, ARRAY_CONCAT, ARRAY_CONCAT_AGG)
11:30 Example array dataset - Austin Bikeshare + BigQuery UI
15:14 Simple example queries on array dataset
16:37 Complex example query on array dataset
Jamboard: jamboard.google.com/d/1pUd_YF...
BigQuery Docs:
* cloud.google.com/bigquery/doc...
* cloud.google.com/bigquery/doc...
* cloud.google.com/bigquery/doc...

Пікірлер: 11

@DicksonTsai 2 жыл бұрын

Errata: I flipped OFFSET and SAFE_OFFSET in the video. OFFSET() triggers an error and stops execution if the index is out of bounds. SAFE_OFFSET() returns NULL if the index is out of bounds.

@bloom6874 13 күн бұрын

This is the best video on repeated fields I came across. Everything you need to know about BigQuery Arrays in one video. Thank you very much for the easiest explanation.

@andrzejjackiewicz2788 Жыл бұрын

Świetne wytłumaczenie pracy ze strukturami zagnieżdżonymi!😀

@dwipaal-farisi4107 Жыл бұрын

I've been looking for this. I've searched into the documentations and found it hard to comprehend. This is awesome!

@igorbulenko6335 Жыл бұрын

Very good explanation, maybe the best I've even seen. Thanks a lot

@jay_wright_thats_right 6 ай бұрын

This is a really good video. Thanks!

@AmitSharma-yi9dr 2 жыл бұрын

great explanation! Does Bigquery supports update statement for the array or struct fields? I want to update 1 field of an array in a table.

@ajeet.y 2 жыл бұрын

Long time, please make a playlist on big query and its analytical functions

@rahulmukerjee477 Ай бұрын

At 17:40, inside the WITH clause, why is duration_minutes not referenced as trip.duration_minutes? Whereas in the WHERE clause you've used trip.start_time. Not trying to nitpick, I'm new to BigQuery and trying to understand.

@philiptomlinson4201 2 ай бұрын

Interestingly, Looking at this in 2024 - the 2018 bikes are getting the most use since Jan 2024. more than Double 2021's bikes. 22/23 bikes have been used, but so far not once this year so perhaps those are duds haha