Introduction to BigQuery administration

This document provides an introduction to BigQuery administrationtasks, and the BigQuery features thathelp you accomplish them.

BigQuery administrators typically do the following types oftasks:

• Manage resources, such as projects, datasets, and tables.
• Secure resources, so that access is limited to the principals who need it.
• Manage workloads, such as jobs, queries, and compute capacity (reservations).
• Monitor resources, including quotas, jobs, and compute usage.
• Optimize workloads for best performance while controlling costs.
• Troubleshoot error messages, billing issues, and quotas.

This document gives an overview of the features that BigQueryprovides to help you do these tasks.

To take a tour of BigQuery data administration featuresdirectly in the Google Cloud console, clickTake the tour.

Take the tour

Tools

BigQuery provides several interfaces you can use for administration tasks.Often a particular task can be done withmultiple tools, letting you choose the tool that works best for you. Forexample, you can create a table by using theExplorer pane in theGoogle Cloud console, abq mk --table command, or aCREATE TABLE SQLstatement.

• Google Cloud console. The Google Cloud console has several pages dedicatedto BigQuery administration. For more information, seeUse the Google Cloud console.

• SQL statements. The BigQuery page in theGoogle Cloud console hasa query editor where you can do administrative tasks by using DDL and DCLstatements. For more information, seeData definition language (DDL)andData control language (DCL).

  You can use stored procedures to automate administration tasks that useSQL statements. For more information, seeWork with stored procedures.

• bq commands. The bq command-line tool lets you do many administrative tasks by usingbq commands. You can use the bq command-line tool to do tasks that aren'tsupported in the Google Cloud console, to prototype capabilities beforeencoding them in queries or API methods, or if you prefer working in acommand-line interface. For more information, seeUse the bq command-line tool.

Manage resources

BigQuery resources include organizations, folders, projects,datasets, and tables. This section describes how to manageyour organization's resources.

For information about the BigQuery resource hierarchy, seeOrganize BigQuery resources.In particular, you can create an Organization resource, which lets you dosome tasks, such as setting access controls, at the organization level.

Manage datasets

Datasets are containers for tables. You can create tables in a dataset, and thenmanage them as a group. For example, you can configure a dataset's defaulttable expiration time, which applies to all tables in the dataset unless youoverride it. You can copy a group of tables by making a copy of their dataset,and you can control access to tables at the dataset level.

Refer to the following documents for more information about datasetadministration:

• For more information about creating, copying, moving, and updating datasets,seeIntroduction to datasets.
• For more information about dataset-level access control, seeControlling access to datasets andAuthorized datasets.

Manage tables

In BigQuery, data is stored in tables, where it can be queried.You can create tables, load data into tables from various types of sources andin various formats, partition tables based on a specific column or by ingestiontime, cluster tables, update table properties, and export table data.

Refer to the following documents for more information about tableadministration:

• For more information about loading data into BigQuery tables,seeIntroduction to loading tables.
• For more information about managing tables and exporting table data, seeIntroduction to tables.
• For more information about partitioning and clustering tables, seeIntroduction to partitioned tables andIntroduction to clustered tables.

Label resources

To help organize your BigQuery resources, you can add labels toyour datasets, tables, and views. Labels are key-value pairs that you can attachto a resource. After labeling your resources, you can search for them based onlabel values. For example, you could use labels to group datasets by departmentby adding labels likedept:sales,dept:marketing, ordept:analytics.Then you couldbreak down your billed chargesby department using the labels.

For more information, seeIntroduction to labels.

Get resource information

You can get information about your BigQuery resources by queryingtheINFORMATION_SCHEMA views. BigQuery providesviewsfor each resource type. For example, theINFORMATION_SCHEMA.TABLES view containsinformation about your tables.

The following are a few examples of information you can obtain by queryingINFORMATION_SCHEMA views:

• See when a table was created.
• Get the names and data types of each column in a table.
• Find all of the jobs running in a project.
• Get a list of the table snapshots that were created from a basetable.
• For a dataset, table, view, or routine, get the DDL statement that can beused to create the resource.
• Get the options that were used to create a table (for example, tableexpiration).
• Find the partitioning and clustering columns on a table.
• Get a project's assigned reservation and its slot capacity.

For more information, seeIntroduction to BigQueryINFORMATION_SCHEMA.

Copy data

You might want to create copies ofyour data for various reasons, such as protection from human error, or to retaindata for comparison in the future.BigQuery provides several options for copying a table's data froma particular point in time.

• Time travel. You might need to access a table's state as it was sometimewithin the pastweek; for example, if the data became corrupted due to human error.BigQuery retains historical data for your tables forseven days. You can access a table's recent historical data by using thetime travel feature.

  For more information, seeAccess historical data using time travel.

• Table snapshots.If you want to be able to access a table's state from earlier than one week inthe past, consider creatingtable snapshots periodically.Table snapshots are lightweight, read-only copies that let youpreserve the state of your tables indefinitely. With table snapshots, forexample, you can compare a table's current data to the data from the beginningof the year, which isn't possible by using time travel.You are only charged for storing the data that differs between the basetable and its table snapshot.

  For more information, seeIntroduction to table snapshots.

• Table clones.If you want to make a lightweight, writable copy of a table, you can usetable clones. You only pay for storage ofthe data that differs between a base table and its table clone. For example, youcould create table clones in a test environment so that you can experiment withcopies of production data without impacting the production data and withouthaving to pay for storage of full copies of the tables.

  For more information, seeIntroduction to table clones.

Track data lineage

Data lineage is aDataplex Universal Catalog feature thatlets you track how data moves through your systems: where it comes from,where it is passed to, and what transformations are applied to it.For more information about how data lineage can help you track data movement in yourproject, seeAbout data lineagein Dataplex Universal Catalog.

Secure resources

BigQuery security is based onGoogle Cloud Identity and Access Management.BigQuery lets you control access to your resources at manylevels, including access to the organization, folders, projects, datasets,tables, table columns, and table rows.

For information about controlling access to your BigQueryresources, seeOverview of data security and governance.

Manage workloads

BigQuery does many tasks on behalf of your users,including ingesting, querying, and exporting data. Each task is done bya BigQueryjob. This section describes how you can monitorand manage your organization's jobs.

Manage jobs

Jobs are actions that BigQuery runs on a user's behalf to load,export, query, or copy data. When a user initiates one of these tasks by usingtheGoogle Cloud console, thebq command-line tool, aSQL statement, or anAPI call, BigQuery automaticallycreates a job to execute the task.

As a BigQuery administrator, you can monitor, manage, andtroubleshoot your organization's jobs to ensure they are running smoothly.

For more information, seeManage jobs.

Manage reservations

When BigQuery executes queries, it uses units of computing calledslots. BigQuery calculates how manyslots are needed to run each query, depending on the size and complexity of thequery.

BigQuery has two pricing models for charging for the slots thatrun your queries:

• On-demand billing. Your queries use a shared pool of slots, and you arecharged for the number of bytes your queries process. For more information onon-demand billing limits, seeQuery jobs.
• Capacity-based billing. You assign a reservation or capacity commitment to anedition, each of which comes with their own feature set and price point toprovide the best working environment for you.

These pricing models apply per project, so you can have some projects that useon-demand billing and some projects that use capacity-based billing.

With on-demand billing, after your monthly allocation offree usage is consumed, you are charged for thenumber of bytes that each query processes. Throughput is limited to a predefinedslot quota, which is shared among the queries that run in a project.

With BigQuery editions billing, you allocate slots for your organization throughautoscaling reservations and optional, but cheaper, capacity commitments. Slotsfor each edition come at their own price point, and offer their own set offeatures. For more information about BigQuery editions and the features that areassociated with them, seeIntroduction toBigQuery editions.

Refer to the following documents for more information about managing computecapacity for processing your queries:

• For slots, and the trade-offs between on-demand billingand capacity-based billing, seeIntroduction to reservations.

• For the different options for capacity-based billing (monthly or annual commitments), seeSlot commitments.

• For creating pools of capacity-based slots, calledslotreservations, seeWork with slot reservations.

• For allocating slot reservations to specific projects,seeWork with reservation assignments.

• For estimating the right number of slots to allocate to yourworkloads, seeEstimate slot capacity requirements.

Monitor resources

Google Cloud provides the capability to monitor and audit yourresources,including BigQuery resources. This section describes theGoogle Cloud monitoring and auditing capabilities that applyto BigQuery.

For more information, seeIntroduction to BigQuerymonitoring.

The Cloud Monitoring dashboard

Cloud Monitoring provides a dashboard for monitoring BigQuery.Use this dashboard to view information about BigQuery incidents,datasets, tables, projects, query times, and slot utilization.

For more information, seeView the Monitoring dashboard.

Administration charts and alerts

You can use Cloud Monitoring to create custom charts based on theresources, metrics, and any aggregation that you specify.

For more information, seeDashboards and charts.

You can also create alert policies that notify you if the configured alert istriggered. For example, you could create an alert that sends an email to aspecified email address if the execution time of a query exceeds a specifiedlimit.

For more information, seeCreating an alert.

Monitor reservations

You can monitor your slot usage on theCapacity management page of theGoogle Cloud console. You can view your capacity commitments and see where yourslot reservations have been assigned. You can also use theSlot estimator(Preview) to help you estimate your organization'scapacity requirements based on historical performance metrics.

For more information, seeMonitoring BigQuery reservations.

Quotas

Google Cloud sets limits on the use of resources, includingBigQuery resources, both toensure fair usage of shared resources, and to protect you from runaway costs.You can view your usage of BigQuery resources that havequotas, andrequest a higher quota, ifneeded, by using the Google Cloud console.

For more information, seeBigQuery quotas and limits.

Audit logs

Cloud Audit Logs maintain a record of Google Cloud events, including BigQueryevents. You can use theLogs Explorer to query the logs about events relatedto BigQuery jobs, datasets, transfers, and more. TheLogsDashboard displays information about recent errors, and you can useLogs-based metrics to count the log entries that match a given filter.

For more information, see theGoogle Cloud logging documentation.

Optimize workloads

You can optimize your BigQuery configuration to control bothstorage and query processing costs.

• For assistance managing your BigQuery storage costs, seeOptimize storage in BigQuery.

• For assistance managing your BigQuery processing costs, seeControl costs in BigQuery.

• For assistance optimizing your BigQueryqueries, seeIntroduction to optimizing query performance.

• For general information about BigQuery costs, seeBigQuery pricing andBigQuery billing questions.

Reliability guidance

This document provides an understanding of BigQuery reliability,including insight into availability, durability, data consistency,consistency of performance, and data recovery inBigQuery, aswell as a review of error handling considerations. To learn more aboutreliability and disaster planning, seeUnderstand reliability.

Troubleshoot

In addition to the features described in this document for monitoring andmanaging your organization's BigQuery system, the followingresources are available for troubleshooting problems that might arise:

• BigQuery error messages
• BigQuery billing questions
• Troubleshoot quota errors

If you need additional assistance, seeGet support.

What's next

• For a video series on various topics related to BigQueryadministration, seeBigQuery admin reference guide: recap.