How-to guides

Interacting with BigQuery

• Introduction to interacting with BigQuery

  An introduction to methods of interacting with BigQuery.

• BigQuery sandbox

  Get started with BigQuery without creating a billing account for your project.

• Using the BigQuery web UI in the Cloud Console (beta)

  BigQuery exposes a graphical web UI in the Cloud Console that you can use to load and export data, run queries, and perform other user and management tasks in your browser.

• Using the classic BigQuery web UI

  BigQuery exposes a graphical web UI that you can use to load and export data, run queries, and perform other user and management tasks in your browser.

• Using the bq command-line tool

  The bq command-line tool is a python-based tool that accesses BigQuery from the command line.

Running and managing jobs

• Introduction to jobs

  An introduction to BigQuery jobs.

• Running jobs programmatically

  Run BigQuery jobs programmatically using the API and client libraries.

• Managing jobs

  Manage BigQuery jobs including listing jobs, canceling jobs, and rerunning jobs.

Working with datasets

• Introduction to datasets

  An introduction to BigQuery datasets.

• Dataset locations

  An overview of locations for storing BigQuery data.

• Creating datasets

  How to create datasets in BigQuery.

• Copying datasets

  How to copy datasets, including copying datasets across regions.

• Controlling access to datasets

  How to control access to datasets in BigQuery.

• Listing datasets

  How to list datasets in BigQuery projects.

• Getting information about datasets

  How to get dataset information or metadata.

• Updating datasets

  How to update dataset properties including updating descriptions, default expiration times, and access controls.

• Managing datasets

  Manage datasets including renaming datasets, deleting datasets, and copying datasets.

• Availability and durability

  Understand availability and durability of your datasets if a disaster occurs.

Working with table schemas

• Specifying a schema

  How to create and use table schemas.

• Specifying nested and repeated columns

  How to specify nested and repeated columns in table schemas.

• Auto-detecting schemas

  Use schema auto-detection when you load data or query external data.

• Modifying table schemas

  Modify table schema definitions including adding columns, renaming columns, deleting columns, and changing column names, data types, and modes.

Working with tables

• Introduction to tables

  An introduction to BigQuery tables.

• Creating and using tables

  Create and use tables including listing tables, getting information about tables, and controlling access to table data.

• Managing tables

  Manage tables including updating table properties, renaming tables, deleting tables, and copying tables.

• Managing table data

  An overview of working with table data including querying tables, browsing table data, modifying table data, exporting data, and appending or overwriting data.

• Exporting table data

  How to export table data to Cloud Storage.

• Updating table data using DML

  Perform bulk modifications to table data using the Data Manipulation Language in standard SQL.

Working with partitioned tables

• Introduction to partitioned tables

  An introduction to partitioned tables in BigQuery.

• Creating ingestion-time partitioned tables

  How to create and use ingestion-time partitioned tables including listing and getting information about ingestion-time partitioned tables.

• Creating date/timestamp partitioned tables

  How to create and use partitioned tables including listing and getting information about date/timestamp partitioned tables.

• Creating integer range partitioned tables

  How to create and use partitioned tables including listing and getting information about integer range partitioned tables.

• Managing partitioned tables

  How to manage partitioned tables including updating properties, copying partitions, and deleting partitions.

• Managing partitioned table data

  How to manage partitioned table data including browsing data, modifying data, exporting data, and appending or overwriting data.

• Querying partitioned tables

  How to query partitioned tables.

• Updating partitioned table data using DML statements

  How to use DML statements to insert, delete, or update data in partitioned tables.

Working with clustered tables

• Introduction to clustered tables

  An introduction to clustered tables in BigQuery.

• Creating and using clustered tables

  How to create and use clustered tables including listing and getting information about clustered tables.

• Querying clustered tables

  How to query clustered tables.

Working with views

• Introduction to views

  An introduction to BigQuery views.

• Creating views

  How to create views.

• Controlling access to views

  How to control access to views.

• Creating authorized views

  How to create a view that allows you to share query results with users and groups without giving them access to the underlying tables.

• Listing views

  How to list views.

• Getting information about views

  How to get information or metadata about views.

• Updating views

  How to update view properties including descriptions, expiration times, and SQL queries.

• Managing views

  Manage views including copying views, renaming views, and deleting views.

Using Reservations for workload management

• Introduction

  An introduction to BigQuery Reservations.

• Concepts

  Concepts related to Reservations.

• Workload management

  Using Reservations for workload management.

• Monitoring

  How to monitor your Reservation usage and billing.

Getting metadata using INFORMATION_SCHEMA (beta)

• Introduction

  An introduction to INFORMATION_SCHEMA.

• Getting dataset metadata

  Using INFORMATION_SCHEMA to retrieve metadata about datasets.

• Getting routine metadata

  Using INFORMATION_SCHEMA to retrieve metadata about routines.

• Getting table metadata

  Using INFORMATION_SCHEMA to retrieve metadata about tables.

• Getting view metadata

  Using INFORMATION_SCHEMA to retrieve metadata about views.

Labeling BigQuery resources

• Introduction to labels

  An introduction to labeling resources in BigQuery.

• Adding labels

  Add labels to group resources by purpose, environment, department, and so on.

• Viewing labels

  Viewing labels on BigQuery resources.

• Updating labels

  Updating labels on BigQuery resources.

• Filtering resources using labels

  Filtering resources using labels.

• Deleting labels

  Deleting labels on BigQuery resources.

Loading data into BigQuery

• Introduction to loading data

  Load your data into BigQuery from a variety of source formats, including CSV, JSON, Avro, Parquet, ORC and Datastore backups.

• Loading data from Cloud Storage

  Load data from Cloud Storage.

• Loading Avro data from Cloud Storage

  How to load Avro data from Cloud Storage.

• Loading Parquet data from Cloud Storage

  How to load Parquet data from Cloud Storage.

• Loading ORC data from Cloud Storage

  How to load ORC data from Cloud Storage.

• Loading CSV data from Cloud Storage

  How to load CSV data from Cloud Storage.

• Loading JSON data from Cloud Storage

  How to load newline delimited JSON data from Cloud Storage.

• Loading Datastore exports from Cloud Storage

  How to load data from a Datastore export.

• Loading Cloud Firestore exports from Cloud Storage

  How to load data from a Cloud Firestore export.

• Loading data from a local data source

  How to load data from a local data source.

• Streaming data into BigQuery

  Stream your data into BigQuery using the BigQuery API.

Querying BigQuery data

• Introduction to querying data

  An introduction to running queries in BigQuery.

• Running interactive and batch queries

  How to run interactive and batch queries.

• Writing query results

  Write and page through query results.

• Using cached results

  How to use cached query results.

• Running parameterized queries

  How to use parameterized queries.

• Querying data using a wildcard table

  Query several tables concisely using a wildcard table.

• Saving and sharing queries

  Save and share queries.

• Scheduling queries

  Schedule recurring queries in BigQuery.

• Using the query plan explanation

  BigQuery provides diagnostic information about a completed query's execution plan.

• Using the BigQuery connector for Excel

  BigQuery offers a connector that allows you to query BigQuery data to from Excel.

Querying external data sources

• Introduction to external data sources

  Query data directly from external data sources such as Cloud Storage, Cloud Bigtable, and Google Drive.

• Creating a table definition file

  Create a table definition file for an external data source.

• Federated queries with Cloud SQL data

  Query data in BigQuery and Cloud SQL with a federated query.

• Querying Cloud Bigtable data

  Use BigQuery to query data stored in Cloud Bigtable.

• Querying Cloud Storage data

  Use BigQuery to query data stored in Cloud Storage.

• Querying Google Drive data

  Use BigQuery to query data stored in Google Drive.

Controlling BigQuery costs

• Introduction to controlling BigQuery costs

  An introduction to cost control measures for BigQuery.

• Estimating storage and query costs

  How to estimate storage and query costs before loading and querying data.

• Custom cost controls

  Manage costs by setting custom quotas on query processing.

• Best practices for controlling costs

  Best practices for controlling costs in BigQuery.

Securing BigQuery resources

• Predefined roles and permissions

  Information on BigQuery's predefined IAM roles and permissions.

• Primitive roles and permissions

  Information on primitive BigQuery roles and permissions that existed before Cloud IAM.

• Access control examples

  Access control use cases in BigQuery.

• Encryption at rest

  Learn how BigQuery encrypts your data at rest.

• Using Cloud DLP to scan BigQuery data

  Use Cloud DLP to identify and protect sensitive BigQuery data.

• Protecting data with KMS keys

  Use customer-managed encryption keys (CMEK) for BigQuery.

Monitoring and logging

• BigQuery monitoring using Stackdriver Monitoring

  Monitor BigQuery resources using Stackdriver Monitoring.

• Audit logging reference

  Log BigQuery usage using audit logs.

BigQuery API basics

• Introduction to authentication

  Perform authentication in various application scenarios.

• Getting started with authentication

  Get started with application default credentials.

• Authenticating with a service account key file

  Manually create and obtain service account credentials.

• Authenticating with a user account for installed applications

  Perform application authentication using user accounts.

• Authorizing API requests

  Authorize API requests with access tokens.

• Batch requests

  Reduce the number of HTTP connections your client has to make by batching API calls.

• Paging through query results

  Page through query results using the BigQuery API.

• API performance tips

  Techniques you can use to improve the performance of your application.

Best practices

• Controlling costs

  Best practices for controlling costs in BigQuery.

• Optimizing query performance

  Best practices for optimizing query performance in BigQuery.

• Optimizing storage

  Best practices for optimizing BigQuery storage.