Google BigQuery for Programmers: Analyze & Visualize

Explore BigQuery, Google's managed warehousing service for analytics, scalable to petabytes, with SQL, in-house machine learning, and no admin tasks; it isn't optimized for inserting, updating, or deleting data.

Discover how Google BigQuery stores data as a columnar database, saving each column in its own memory block for faster retrieval and why inserts and updates are less efficient.

Understand how BigQuery slots price and allocate resources for queries, including the default 2000-slot quota. See how extra slots boost processing and time sharing slows queries when slots are limited.

Explore BigQuery data structures—projects, data sets, and tables, with schemas, durability, and access controls. Learn to create tables via empty schema, query results, or XML, JSON, or CSV.

Access the Google Cloud Platform console, open BigQuery, and use the explorer pane. Create a new dataset in Vast Collective, with id examples in US central one Iowa.

Create empty table in BigQuery by selecting a dataset, naming the employees table, and defining a schema with id, first name, last name, and salary, then insert data with SQL.

Learn to create a table from a query in BigQuery by setting destination dataset and table, choosing write options (empty, append, overwrite), and running the query to produce a table.

Upload a csv from local storage to BigQuery to create a table from external data. Auto-detect schema and skip header row, with fields like heart disease (boolean) and bmi.

Practice creating and importing a csv dataset into BigQuery, build a birthplaces lookup table, and generate targeted natality tables with selected fields using SQL queries.

Import a csv into BigQuery, create Natality 2020 and birthplaces tables, then write SQL to select birth month, birth time, birth place, and infant weight, and join tables.

Explore the basics of sql in the bigquery context, including table schemas (student, course, study), primary keys, and writing queries to retrieve and analyze data.

Explore the select statement to retrieve data from the database by specifying columns, sources, and optional where conditions, and learn its execution order from cross product to final projection.

Learn how to join multiple tables and avoid the cross product. Use the from clause, foreign keys, and where conditions to retrieve meaningful data about students and courses.

Learn how SQL aggregate functions like count, max, and min compute over rows to yield a single value, and why you cannot mix aggregate functions with regular columns in select.

Learn how the group by clause partitions a table into groups after the from and where clauses, enabling per-group aggregates such as count in BigQuery.

Practice writing SQL queries to list courses with at least four credit points and to count students named John. Also compute each studied course's average and Nicholas's average credit points.

Practice BigQuery queries with exercises on courses, students, and studied tables to list courses with at least four points, compute student averages, and count John.

Use BigQuery as an analytical engine to explore data, run SQL queries, understand data structure, uncover insights, and set the stage for applying machine learning techniques.

This dataset overview guides loading the heart 2020 patient data into BigQuery, auto-detecting schema, and preparing to run sql queries to analyze risk factors and health indicators.

Write and run queries in the BigQuery editor, retrieve a single column for efficient columnar processing, and count heart disease cases to validate data with syntax checks and memory estimates.

Explore how to count male and female heart disease patients by gender and compare BMI across status, noting max BMI anomalies and the value of using average BMI.

Use union all to combine results from multiple queries, create a risk factor column, and enrich data to reveal how smoking, alcohol, and stroke relate to heart disease.

Generate a code table mapping each age category to a unique numeric code using the row number function in BigQuery, then save it as a new table.

Load natality 2020 data into BigQuery and write queries to analyze birth data: gender counts for 2020, average infant weight, birthplace value one hospital and monthly mother ages for boys.

Analyze Natality 2020 in BigQuery: count births by gender, compute average infant weight by gender, and determine hospital mother BMI, for each month, max and min mother age among boys.

Explore normalization in BigQuery: compare normalized and denormalized schemas, learn join strategies and group by implications for storage, query performance, and parallel queries.

BigQuery enables nested and repeated fields to preserve data structure and improve query efficiency. A line dataset demonstrates this by nesting line properties and listing stations as a repeated field.

Create a BigQuery table with nested and repeated fields for Python course students. Define id, details (gender and birth date), grades (repeated integers), and exam grade.

Query nested and repeated fields in BigQuery, access nested attributes like details.birthdate and details.gender, count by gender, and analyze repeated grades using unnest, array length, and joins to compute averages.

Learn how BigQuery partitions tables into integer and time-based partitions to speed queries, with notes on benefits, granularity limits, metadata overhead, and repartitioning challenges.

Create a partitioned BigQuery table with id (integer) and birthday (date time), and partition by ingestion time or by id with start id, end id, and interval.

Explore partitioning and clustering in BigQuery, where partitioned tables can be clustered up to four fields to sort data and limit scans for faster, cheaper queries.

Practice loading a london underground stations json into bigquery to explore nested and repeated fields, including lines and geometry, and answer queries on counts and 2016 night traffic for piccadilly.

Explore data visualization to reveal patterns in large datasets. Use Looker Studio to visualize data from BigQuery, focusing on essential features to see data visually.

Explore a BigQuery table of pupil grades and visualize it in Looker Studio, using an uploaded CSV, previewing the data, and generating dashboards with built-in diagrams.

Explore the Looker Studio UI by loading the pupils grades table from BigQuery, review green imported columns, data types, blue record count, and the toolbar for pages and charts.

Create and customize a scoreboard in Google BigQuery for programmers, choosing metrics like record count and average age, and style it with fonts and labels for clear data storytelling.

Create pie and line charts in BigQuery to analyze data by gender, age, and IDs. Visualize record count, average math grades, and historic grades to spot correlations.

Create a calculated field named average by averaging English, history, math, and science, then save it and use it in a smooth line chart sorted by the average.

Create bar charts to visualize data distribution by gender and ages 14, 13 and 15, using the record count metric and styling options for bars.

Learn to use box plots to reveal distribution and outliers across categories by computing min, max, mean, median, and the 25th and 75th percentiles. Save and share your visualization.

Practice visualizing natality 2020 data in BigQuery by creating a scoreboard, a monthly births bar chart, a gender pie chart, and a mother-father age line chart in the local studio.

Learn to prepare and clean data in BigQuery to turn raw data from diverse sources into insights, avoiding garbage in, garbage out.

Clean and prepare data for analysis by removing duplicates, handling missing values, formatting units, scaling features, enriching data, and encoding categoricals for effective BigQuery analytics.

Preprocess the used cars dataset in BigQuery by cleaning and preparing features like brand, mileage, model date, engine, fuel type, and transmission to predict final price.

Clean the data by removing unneeded columns such as currency, description, item condition, and redundant manufacturer. Cast mileage and engine to integers and save results to a new data set.

Remove duplicates by using a with statement to create a temp table of distinct rows, compare counts, and save a new table with unique records.

Remove null values by counting nulls per column with to_json_string and regexp_extract, then filter out rows where vehicle transmission or fuel type are null.

Transform strings into categorized numbers by using dense rank to assign a unique category to each value, as shown with fuel type, brand, and transmission in the used cars dataset.

Visualize the dataset with line, scatter, and box plots to reveal that model date strongly predicts car price, while brand shows weak correlation and mileage outliers exist.

Learn to remove outliers in BigQuery by filtering mileage and model date, selecting key fields, saving the cleaned dataset, and preparing data for machine learning and data analytics.

Recaps cleaning and feature engineering on the used cars dataset from Pakistan, covering removal of columns, conversions, deduplication, null handling, encoding, outlier removal, and a clean dataset for price prediction.

Clean and prepare natality 2020 data by keeping mother age, cigarettes smoked, height, bmi, prepreg weight, delivery weight, prediabetes; convert units, remove duplicates, visualize infant weight correlation in lucid studio.

Explore how machine learning uses large data to identify patterns and build models that predict or classify tasks, from spam detection to stock trends, with BigQuery context.

Learn linear regression, a foundational algorithm that fits a regression line to predict a numeric outcome from data, using training and testing sets to assess error and accuracy in BigQuery.

Create a linear model in BigQuery by setting model type to linear regression, selecting the target label and features, then run and review the model in the models folder.

Evaluate your machine learning model in BigQuery by using the ML.EVALUATE function to obtain metrics such as the R square score and mean absolute error, guiding improvements for linear models.

Run an evaluation in BigQuery with the linear model, and interpret metrics like R score, mean absolute error, and explained variance to guide improvements.

Use BigQuery ml.predict to generate predictions from a trained model by selecting the model and input data (table or query) with matching columns. See predicted labels for rows.

Build a linear regression model on natality 2020 dataset to predict infant weight, save it with create model, evaluate with r-squared and mean absolute error, then generate predictions to improve.

Explore how Google BigQuery stores and retrieves large data, load your own data into BigQuery, query with SQL, and analyze with data preparation, data visualization, and built-in machine learning algorithms.

Unlock free programming resources that reveal how computers work under the hood, with access to the Learn to Code course, over 100 related videos, and a programmers career paths book.