Chapter 1 Introduction

1.1 Session aims

Basics of SQL
Overview of Google BigQuery
Introduce concepts and functions on how to store, manage and query data in Google BigQuery

1.2 What is SQL?

SQL is a standard language used to communicate with relational databases. It is a programming language that allows you to create, modify, and query databases. SQL stands for Structured Query Language, and it is used to manage and manipulate data in relational databases. There are various variants of SQL, we will be using GoogleSQL today, with T-SQL (Microsoft) being the other common variant used within DfT.

1.3 What is BigQuery?

BigQuery is a cloud-based data warehouse that allows you to store, query, and analyze large datasets. It is fully managed by Google Cloud Platform, which means that you don’t have to worry about managing infrastructure or scaling resources. BigQuery is designed to handle massive amounts of data and can perform complex queries quickly and efficiently.To use BigQuery, you’ll need to create a Google Cloud Platform account and enable the BigQuery API. You can then create datasets, tables, and views in the BigQuery web UI or using the BigQuery command-line interface.

1.4 Why use BigQuery?

BigQuery offers several benefits for organizations looking to manage and analyze large datasets. Some of the key benefits include:

Scalability: GCP BigQuery is designed to handle large-scale data processing and storage, enabling your organization to scale up or down based on your current needs. As your organization grows, BigQuery can scale with you without any performance issues.
Performance: BigQuery’s distributed processing engine allows for lightning-fast data analysis and queries, even on large datasets. This can help your organization make decisions more quickly and efficiently.
Integration: BigQuery offers seamless integration with other GCP services, such as Google Data Studio and Google Cloud Storage. This enables you to create a unified data ecosystem, allowing you to extract insights and make data-driven decisions faster.
Security: GCP offers robust security features, including encryption of data at rest and in transit, multi-factor authentication, and identity access management. This ensures that your organization’s data is secure and protected.

1.5 How to get started with BigQuery

To get started with BigQuery, you will need access to a Google Cloud Platform project that has BigQuery enabled. As part of the course we have given you access to the GCP Stats Showcase project

1.6 BigQuery Editor

The BigQuery Editor is a web-based graphical user interface (GUI) for interacting with BigQuery. It provides a convenient way to create, edit, and execute SQL queries, as well as view and manage tables, views, and other database objects.

To access the BigQuery Editor, you can navigate to the BigQuery web UI in the Google Cloud Console. Once you’re in the BigQuery console, you can click on the “Query Editor” tab to launch the editor.

The BigQuery Editor provides a number of features that make it easy to work with data in BigQuery, including:

Syntax highlighting and error checking: The editor highlights SQL syntax and checks for errors as you type, making it easy to catch mistakes before you run your query, as well as suggesting fixes, such as where you’ve referenced variables incorrectly.
Auto-complete: The editor provides auto-complete suggestions as you type, based on the database schema and your query history.
Query history: The editor maintains a history of all the queries you’ve run, making it easy to re-run or modify previous queries.
Visual query builder: The editor provides a visual query builder that allows you to drag and drop tables and columns to build complex queries without writing SQL code.

Overall, the BigQuery Editor is a powerful tool for working with data in BigQuery. Whether you’re a SQL expert or a beginner, the editor provides a user-friendly interface that makes it easy to create, edit, and execute SQL queries.

1.7 BigQuery Terminology

Before we go further it’s important we understand the differences between differences between a Google Cloud Project, Dataset, and Table in BigQuery

Google Cloud Project: A Google Cloud Project is a container that holds all of the resources needed to manage and run your applications on Google Cloud Platform (GCP). This includes compute resources like virtual machines, storage resources like buckets, and services like BigQuery. Each project has a unique project ID and is associated with a billing account.

Dataset: A Dataset is a collection of tables in BigQuery that share a common schema and are stored in the same location. Datasets are contained within a specific GCP project and are used to organize and control access to your data. Datasets can be created, deleted, and modified using the BigQuery UI, the CLI, or the API.

Table: A Table is a collection of data organized into rows and columns. Tables are stored within a Dataset in BigQuery and contain the actual data you want to query. Tables can be created, deleted, and modified using the BigQuery UI, the CLI, or the API. Each table has a schema that defines the structure of the data it contains, including column names, data types, and other attributes.

In summary, a Google Cloud Project is a container for all your GCP resources, including BigQuery. A Dataset is a collection of tables that share a common schema and are stored in a specific GCP project, while a Table is a collection of data organized into rows and columns that is stored within a Dataset. Understanding the differences between these three concepts is crucial for effectively managing and analyzing data using BigQuery.

1.8 Basic SQL Syntax

The basic syntax of a SQL query in BigQuery consists of the following elements:

SELECT statement: used to specify the columns you want to retrieve data from
FROM clause: used to specify the table or tables you want to retrieve data from
WHERE clause: used to filter data based on specific conditions
GROUP BY clause: used to group data based on one or more columns
ORDER BY clause: used to sort data in ascending or descending order based on one or more columns

SQL statements are usually written in uppercase, while column and table names are written in lowercase. SQL syntax is not case-sensitive, but it’s good practice to use consistent casing for readability. In SQL queries in BigQuery you do need to use a semicolon (;) at the end of each query to indicate the end of the code block.

SQL queries can also include various functions and operators to perform calculations, comparisons, and other operations on data. Some commonly used functions in BigQuery include AVG, COUNT, MAX, MIN, SUM, and CONCAT.

An example SQL code snippet might look like:

SELECT *
FROM `mydataset.mytable`
WHERE column1 = 'value'
ORDER BY column2 ASC;

Intro to SQL in BigQuery