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.

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.

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.

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

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.

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.

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

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

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.

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.

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.