BigQuery UNNEST: How to work with nested data in BigQuery

BigQuery UNNEST: How to work with nested data in BigQuery

We use a common example: un-nesting Firebase event data to facilitate data science analysis

We frequently apply machine learning techniques to event-based data in order to generate high-value insights; from predicting customer churn using people’s recent activity of say, a subscription-based product, to scoring how engaged a user is on a piece of content for feeding into a recommendation engine.

Event-based data is a good example of semi-structured-data.

What is semi-structured data?

Structured data is data that adheres to a rigid tabular format. This makes it ideal for storing in say, a database table or a spreadsheet.

Semi-structured data, however, has variations in its structure; attributes are not fixed(equivalent to columns in a database table), and, to further complicate things, the data itself can be nested.

To illustrate this, consider the following use case: We want to consolidate event data generated from 3 different apps that users interact with via mobile devices and laptops/desktop PCs.

Some of the attributes received in the event data will be common across all apps. A timestamp, _for example, to indicate when the event occurred. Other attributes will only apply to certain applications/devices. Furthermore, some of these attributes may have multiple occurrences within the context of a single event, and therefore creates a need for the data to be _nested.

How is semi-structured stored?

Semi-structured data is typically stored as JSON or XML, and is the foundation of no-SQL data stores such as Google’s Firebase, or MongoDB.


What is Geek Coin

What is GeekCash, Geek Token

Best Visual Studio Code Themes of 2021

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

What is firebase,firebase bangla tutorial.

LIKE | COMMENT | SHARE | SUBSCRIBE The Firebase Realtime Database is a cloud-hosted NoSQL database that lets you store and sync data between your users in re...

Firebase Introduction with C#

LIKE | COMMENT | SHARE | SUBSCRIBE Firebase is a mobile and web application development platform developed by Firebase, Inc. in 2011, then acquired by Google...

Create database into firebase

LIKE | COMMENT | SHARE | SUBSCRIBE In this video, I will show you how to Create database into #firebase console. Subscribe & Stay Connected! Thank You! ♥ #Fi...

Save Employee into firebase

LIKE | COMMENT | SHARE | SUBSCRIBE In this video, I will show you how to save employee information into firebase database. Subscribe & Stay Connected! Thank ...

Install firesharp library from nuget

LIKE | COMMENT | SHARE | SUBSCRIBE The Firebase C# Library FireSharp contains development information for the Firebase API, written in C#. This library provi...