Overview of BigQuery analytics

This document describes how BigQuery processes queries, and it providesan overview of several features that are useful for understanding andanalyzing your data.

BigQuery is optimized to run analytic queries on large datasets,including terabytes of data in seconds and petabytes in minutes. Understandingits capabilities and how it processes queries can help you maximize your dataanalysis investments.

Analytic workflows

BigQuery supports several data analysis workflows:

• Ad hoc analysis. BigQuery usesGoogleSQL,the SQL dialect in BigQuery, to support ad hocanalysis. You can run queries in the Google Cloud console or throughthird-party toolsthat integrate with BigQuery.

• Geospatial analysis. BigQuery uses geography data types andGoogleSQL geography functions to let you analyze and visualizegeospatial data. For information about these data types and functions, seeIntroduction to geospatial analytics.

• Search for data. You canindex your data toperform flexible, optimizedsearches on unstructuredtext or semi-structured JSON data.

• Search for Google Cloud resources. Usenatural language search (Preview)to discover Google Cloud resources from within BigQuery.

• Machine learning.BigQuery MLuses GoogleSQL queries to let you create and execute machinelearning (ML) models in BigQuery.

• Business intelligence.BigQuery BI Engineis a fast, in-memory analysis service that lets youbuild rich, interactive dashboards and reports without compromisingperformance, scalability, security, or data freshness.

• AI assistance. You can useGemini inBigQuery to prepareand explore your data, generate SQL queries and Python code, and visualizeyour results.

Data exploration

BigQuery can help you understandyour data before you start writing SQL queries. Use the following featuresif you want to find data, are unfamiliar with your data, don't know whichquestions to ask, or need help writing SQL:

• Dataplex Universal Catalog. FindGoogle Cloud resources from within BigQuery, such asdatasets and tables.

• Table explorer. Visually explore therange and frequency of values in your table andinteractively build queries.

• Data insights. Generatenatural language questions about your data, along with the SQLqueries to answer those questions.

• Data profile scan. Seestatistical characteristics of your data, including average, unique, maximum,and minimum values.

• Data canvas. Query your data using naturallanguage, visualize results with charts, and ask follow-up questions.

Queries

The primary way to analyze data in BigQuery is torun a SQL query. TheGoogleSQL dialectsupportsSQL:2011and includes extensions that support geospatial analysis and ML.

Data sources

BigQuery lets you query the following types of data sources:

• Data stored in BigQuery. You canload data into BigQuery,modify existing data by usingdata manipulation language(DML)statements,orwrite query results to a table. You canquery historical data from a point intime within your time travel window.

  You can query data stored in single-region or multi-region locations.A query that accesses data stored in more than one location can be treated as aglobal query(Preview).Queries that reference data in multiple locations are always treated as global queries, even if one region is a single-region location and the otheris a multi-region location that contains the single-region location.

• External data. You can query various external data sources such asCloud Storage, or database services such as Spanner orCloud SQL. For information about how toset up connections to external sources, seeIntroduction to external data sources

• Multi-cloud data. You can query data that's stored in other public cloudssuch as AWS or Azure. For information on how to set up connections toAmazon Simple Storage Service (Amazon S3) or Azure Blob Storage, seeIntroduction to BigQuery Omni.

• Public datasets. You can analyze any ofthe datasets that are available in thepublic dataset marketplace.

• BigQuery sharing (formerly Analytics Hub). You can publish andsubscribe to BigQuery datasets and Pub/Sub topics toshare data across organizational boundaries. For more information, seeIntroduction to BigQuery sharing.

Types of queries

You canquery BigQuery databy using one of the following query job types:

• Interactive query jobs. Bydefault, BigQuery runs queries as interactive query jobs, whichare intended to start executing as quickly as possible.

• Batch query jobs. Batch querieshave lower priority than interactive queries. When a project or reservationis using all of its available compute resources, batch queries are morelikely to be queued and remain in the queue. After a batch query startsrunning, the batch query runs the same as an interactive query. For moreinformation, seequery queues.

• Continuous query jobs.With these jobs, the query runs continuously, letting you analyzeincoming data in BigQuery in real time and then write theresults to a BigQuery table, or export the results toBigtable or Pub/Sub. You can use this capability toperform time sensitive tasks, such as creating and immediately acting oninsights, applying real time machine learning (ML) inference, andbuilding event-driven data pipelines.

You can run query jobs by using the following methods:

• Compose and run a query in theGoogle Cloud console.
• Run thebq query command in thebq command-line tool.
• Programmatically call thejobs.queryorjobs.insertmethod in the BigQueryREST API.
• Use the BigQueryclient libraries.

Multi-statement queries

You can run multiple statements in a sequence, with shared state, by usingmulti-statement queries.Multi-statement queries are often used instored procedures and supportprocedural language statements,which let you define variables and implement control flow.

Saved and shared queries

BigQuery lets yousave queriesandshare querieswith others.

When you save a query, it can be private (visible only to you), shared at theproject level (visible to specific principals), or public (anyone can view it).For more information, seeWork with saved queries.

How BigQuery processes queries

Several processes occur when BigQuery runs a query:

• Execution tree. When you run a query, BigQuerygenerates anexecution tree that breaks the query into stages. These stagescontain steps that can run in parallel.

• Shuffle tier. Stages communicate with one another by using a fast,distributedshuffle tier that stores intermediate data produced by theworkers of a stage. When possible, the shuffle tier leverages technologiessuch as a petabit network and RAM to quickly move data to worker nodes.

• Query plan. When BigQuery has all the information that itneeds to run a query, it generates aquery plan. You canview the query plan inthe Google Cloud console and use it to troubleshoot oroptimize query performance.

• Query execution graph. You can review the query plan information ingraphical format for any query, whether running or completed, and seeperformance insights to help you optimizeyour queries.

• Query monitoring and dynamic planning. Besides the workers that performthe work of the query plan itself, additional workers monitor and direct theoverall progress of work throughout the system. As the query progresses,BigQuery might dynamically adjust the query plan to adapt tothe results of the various stages.

• Query results. When a query is complete, BigQuery writesthe results to persistent storage and returns them to the user. This designlets BigQuery servecached results the next time that query isrun.

Query concurrency and performance

The performance of queries that are run repeatedly on the same data can varybecause of theshared nature of the BigQuery environment, use ofcached query results, or becauseBigQuery dynamically adjusts the query plan while the query runs.For a typical busy system where many queries run concurrently,BigQuery uses several processes to smooth out variances in queryperformance:

• BigQuery runs many queries in parallel and canqueue queries to run when resources areavailable.

• As queries start and finish, BigQuery redistributesresources fairly between new and running queries. This process ensures thatquery performance doesn't depend on the order in which queries are submittedbut rather on the number of queries run at a given time.

Query optimization

When you run a query, you canview the query planin the Google Cloud console. You can also request execution details by usingtheINFORMATION_SCHEMA.JOBS* viewsor thejobs.get REST API method.

The query plan includes details about query stages and steps. These details canhelp you identify ways to improve query performance. For example, if you noticea stage that writes a lot more output than other stages, it might mean that youneed to filter earlier in the query.

For more information about the query plan and query optimization, see thefollowing resources:

• To learn more about the query plan and see examples of how the planinformation can help you to improve query performance, seeQuery plan and timeline.
• For more information about query optimization in general, seeIntroduction to optimizing query performance.

Query monitoring

Monitoring and logging are crucial for running reliable applications in thecloud. BigQuery workloads are no exception, especially if yourworkload has high volumes or is mission critical. BigQueryprovides various metrics, logs, and metadata views to help you monitor yourBigQuery usage.

For more information, see the following resources:

• To learn about monitoring options in BigQuery, seeIntroduction to BigQuery monitoring.
• To learn about audit logs and how to analyze query behavior, seeBigQuery audit logs.

Query pricing

BigQuery offers two pricing models for analytics:

• On-demand pricing.You pay for the data scanned by your queries. You have afixed,query-processing capacityfor each project,and your cost is based on the number of bytes processed.
• Capacity-based pricing.You purchase dedicated query-processing capacity.

For information about the two pricing models and to learn more about making reservationsfor capacity-based pricing, seeIntroduction to reservations.

Quotas and query cost controls

BigQuery enforces project-level quotas on running queries. Forinformation on query quotas, seeQuotas and limits.

To control query costs, BigQuery provides several options,including custom quotas and billing alerts. For more information, seeCreating custom cost controls.

Data analytics features

BigQuery supports both descriptive and predictive analytics andhelps you explore your data with AI powered tools, SQL, machine learning,notebooks, and other third-party integrations.

BigQuery Studio

BigQuery Studio helps you discover, analyze, and runinference on data in BigQuery with the following features:

• A robustSQL editor that provides codecompletion and generation, query validation,and estimation of bytes processed.
• EmbeddedPython notebooks built usingColab Enterprise.Notebooks provide one-click Python development runtimes, andbuilt-in support forBigQuery DataFrames.
• APySpark editorthat lets you create stored Python procedures for Apache Spark.
• Asset management and version history for code assets such as notebooks andsaved queries, built on top ofDataform.
• Assistive code development in the SQL editor and in notebooks, built on top ofGemini generative AI (Preview).
• Dataplex Universal Catalog features fordata discovery,anddata profiling anddata quality scans.
• The ability to viewjob historyon a per-user or per-project basis.
• The ability to analyze saved query results by connecting to other tools suchas Looker and Google Sheets, and to export saved query results foruse in other applications.

• Compute Engine API
• Analytics Hub API
• Dataform API
• Vertex AI API
• BigQuery Connection API
• BigQuery Data Policy API
• BigQuery Reservation API
• Dataplex API

BigQuery ML

BigQuery ML lets you use SQL in BigQuery to performmachine learning (ML) and predictive analytics. For more information,seeIntroduction to BigQuery ML.

TheConversational Analytics Agentlets you chat with your data using conversational language. This agent consistsof one or more data sources and a set of use case-specific instructions forprocessing that data. Conversation analytics supports the use ofsome BigQuery ML functions.

Analytics tools integration

In addition to running queries in BigQuery, you can analyze yourdata with various analytics and business intelligence tools that integrate withBigQuery, such as the following:

• Looker. Looker is an enterprise platform forbusiness intelligence, data applications, and embedded analytics. TheLooker platform works with many datastores includingBigQuery. For information on how to connectLooker to BigQuery, seeUsing Looker.

• Looker Studio. After you run a query, you can launchLooker Studio directly from BigQuery in theGoogle Cloud console. Then, in Looker Studio you can createvisualizations and explore the data that's returned from the query. Forinformation about Looker Studio, seeLooker Studio overview.

• Connected Sheets. You can also launchConnected Sheets directly from BigQuery in theconsole. Connected Sheets runsBigQuery queries on your behalf either upon your request or ona defined schedule. Results of those queries are saved in your spreadsheet foranalysis and sharing. For information about Connected Sheets,seeUsing connected sheets.

• Tableau. You canconnect to a dataset from Tableau. UseBigQuery to power your charts, dashboards, and other datavisualizations.

Third-party tool integration

Several third-party analytics tools work with BigQuery.For example, you can connectTableauto BigQuery data and use its visualization tools to analyze andshare your analysis. For more information on considerations when usingthird-party tools, seeThird-party tool integration.

ODBC and JDBC drivers are available and can be used to integrate yourapplication with BigQuery. The intent of these drivers is to helpusers leverage the power of BigQuery with existing tooling andinfrastructure. For information on latest release and known issues, seeODBC and JDBC drivers for BigQuery.

The pandas libraries likepandas-gbq let you interact withBigQuery data in Jupyter notebooks. For information about thislibrary and how it compares with using the BigQueryPython client library,seeComparison withpandas-gbq.

You can also use BigQuery with other notebooks and analysistools. For more information, seeProgrammatic analysis tools.

For a full list of BigQuery analytics and broader technologypartners, see thePartnerslist on the BigQuery product page.

What's next

• For an introduction and overview of supported SQL statements, seeIntroduction to SQL in BigQuery.
• To learn about the GoogleSQL syntax used for querying data inBigQuery, seeQuery syntax in GoogleSQL.
• Learn how torun a query inBigQuery.
• Learn more aboutoptimizing query performance.
• Learn about getting started withnotebooks.
• Learn how toschedule a recurring query.