Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://developers.google.com/bigquery/docs/running-queries below:

Run a query | BigQuery

Stay organized with collections Save and categorize content based on your preferences.

This document shows you how to run a query in BigQuery and understand how much data the query will process before execution by performing a dry run.

You can query BigQuery data by using one of the following query job types:

• Interactive query jobs. By default, BigQuery runs queries as interactive query jobs, which are intended to start executing as quickly as possible.

• Batch query jobs. Batch queries have lower priority than interactive queries. When a project or reservation is using all of its available compute resources, batch queries are more likely to be queued and remain in the queue. After a batch query starts running, the batch query runs the same as an interactive query. For more information, see query queues.

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

You can run query jobs by using the following methods:

• Compose and run a query in the Google Cloud console.
• Run the bq query command in the bq command-line tool.
• Programmatically call the jobs.query or jobs.insert method in the BigQuery REST API.
• Use the BigQuery client libraries.

BigQuery saves query results to either a temporary table (default) or permanent table. When you specify a permanent table as the destination table for the results, you can choose whether to append or overwrite an existing table, or create a new table with a unique name.

To get the permissions that you need to run a query job, ask your administrator to grant you the following IAM roles:

• BigQuery Job User (roles/bigquery.jobUser) on the project.
• BigQuery Data Viewer (roles/bigquery.dataViewer) on all tables and views that your query references. To query views, you also need this role on all underlying tables and views. If you're using authorized views or authorized datasets, you don't need access to the underlying source data.

For more information about granting roles, see Manage access to projects, folders, and organizations.

These predefined roles contain the permissions required to run a query job. To see the exact permissions that are required, expand the Required permissions section:

The following permissions are required to run a query job:

• bigquery.jobs.create on the project from which the query is being run, regardless of where the data is stored.
• bigquery.tables.getData on all tables and views that your query references. To query views, you also need this permission on all underlying tables and views. If you're using authorized views or authorized datasets, you don't need access to the underlying source data.

You might also be able to get these permissions with custom roles or other predefined roles.

Access Denied: Project [project_id]: User does not have bigquery.jobs.create
permission in project [project_id].

This error occurs when a principal lacks permission to create a query jobs in the project.

Resolution: An administrator must grant you the bigquery.jobs.create permission on the project you are querying. This permission is required in addition to any permission required to access the queried data.

For more information about BigQuery permissions, see Access control with IAM.

To run an interactive query, select one of the following options:

1. Go to the BigQuery page.

   Go to BigQuery

2. Click add_box SQL query.

3. In the query editor, enter a valid GoogleSQL query.

   For example, query the BigQuery public dataset usa_names to determine the most common names in the United States between the years 1910 and 2013:

   SELECT
     name, gender,
     SUM(number) AS total
   FROM
     `bigquery-public-data.usa_names.usa_1910_2013`
   GROUP BY
     name, gender
   ORDER BY
     total DESC
   LIMIT
     10;
   

4. Optional: To automatically display code suggestions when you type a query, click settings More, and then select SQL autocomplete. If you don't need autocomplete suggestions, deselect SQL autocomplete. This also turns off the project name autofill suggestions.

5. Optional: To select additional query settings, click settings More, and then click Query settings.

6. Click play_circle Run.

   If you don't specify a destination table, the query job writes the output to a temporary (cache) table.

   You can now explore the query results in the Results tab of the Query results pane.

7. Optional: To sort the query results by column, click arrow_drop_down Open sort menu next to the column name and select a sort order. If the estimated bytes processed for the sort is more than zero, then the number of bytes is displayed at the top of the menu.

8. Optional: To see visualization of your query results, go to the Visualization tab. You can zoom in or zoom out of the chart, download the chart as a PNG file, or toggle the legend visibility.

   In the Visualization configuration pane, you can change the visualization type and configure the measures and dimensions of the visualization. Fields in this pane are prefilled with the initial configuration inferred from the destination table schema of the query. The configuration is preserved between following query runs in the same query editor.

   For Line, Bar, or Scatter visualizations, the supported dimensions are INT64, FLOAT64, NUMERIC, BIGNUMERIC, TIMESTAMP, DATE, DATETIME, TIME, and STRING data types, while the supported measures are INT64, FLOAT64, NUMERIC, and BIGNUMERIC data types.

   If your query results include the GEOGRAPHY type, then Map is the default visualization type, which lets you visualize your results on an interactive map.

9. Optional: In the JSON tab, you can explore the query results in the JSON format, where the key is the column name and the value is the result for that column.

1. In the Google Cloud console, activate Cloud Shell.

   Activate Cloud Shell

   At the bottom of the Google Cloud console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.

2. Use the bq query command. In the following example, the --use_legacy_sql=false flag lets you use GoogleSQL syntax.

   bq query \
       --use_legacy_sql=false \
       'QUERY'

   Replace QUERY with a valid GoogleSQL query. For example, query the BigQuery public dataset usa_names to determine the most common names in the United States between the years 1910 and 2013:

   bq query \
       --use_legacy_sql=false \
       'SELECT
         name, gender,
         SUM(number) AS total
       FROM
         `bigquery-public-data.usa_names.usa_1910_2013`
       GROUP BY
         name, gender
       ORDER BY
         total DESC
       LIMIT
         10;'
   

   The query job writes the output to a temporary (cache) table.

   Optionally, you can specify the destination table and location for the query results. To write the results to an existing table, include the appropriate flag to append (--append_table=true) or overwrite (--replace=true) the table.

   bq query \
       --location=LOCATION \
       --destination_table=TABLE \
       --use_legacy_sql=false \
       'QUERY'

   Replace the following:

   • LOCATION: the region or multi-region for the destination table—for example, US

     In this example, the usa_names dataset is stored in the US multi-region location. If you specify a destination table for this query, the dataset that contains the destination table must also be in the US multi-region. You cannot query a dataset in one location and write the results to a table in another location.

     You can set a default value for the location using the .bigqueryrc file.

   • TABLE: a name for the destination table—for example, myDataset.myTable

     If the destination table is a new table, then BigQuery creates the table when you run your query. However, you must specify an existing dataset.

     If the table isn't in your current project, then add the Google Cloud project ID using the format PROJECT_ID:DATASET.TABLE—for example, myProject:myDataset.myTable. If --destination_table is unspecified, a query job is generated that writes the output to a temporary table.

To run a query using the API, insert a new job and populate the query job configuration property. Optionally specify your location in the location property in the jobReference section of the job resource.

Poll for results by calling getQueryResults. Poll until jobComplete equals true. Check for errors and warnings in the errors list.

Before trying this sample, follow the C# setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery C# API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

Before trying this sample, follow the Go setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Go API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

Before trying this sample, follow the Java setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Java API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

To run a query with a proxy, see Configuring a proxy.

Before trying this sample, follow the Node.js setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Node.js API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

Before trying this sample, follow the PHP setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery PHP API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

Before trying this sample, follow the Python setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Python API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

Before trying this sample, follow the Ruby setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Ruby API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

To run a batch query, select one of the following options:

1. Go to the BigQuery page.

   Go to BigQuery

2. Click add_box SQL query.

3. In the query editor, enter a valid GoogleSQL query.

   For example, query the BigQuery public dataset usa_names to determine the most common names in the United States between the years 1910 and 2013:

   SELECT
     name, gender,
     SUM(number) AS total
   FROM
     `bigquery-public-data.usa_names.usa_1910_2013`
   GROUP BY
     name, gender
   ORDER BY
     total DESC
   LIMIT
     10;
   

4. Click settings More, and then click Query settings.

5. In the Resource management section, select Batch.

6. Optional: Adjust your query settings.

7. Click Save.

8. Click play_circle Run.

   If you don't specify a destination table, the query job writes the output to a temporary (cache) table.

1. In the Google Cloud console, activate Cloud Shell.

   Activate Cloud Shell

   At the bottom of the Google Cloud console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.

2. Use the bq query command and specify the --batch flag. In the following example, the --use_legacy_sql=false flag lets you use GoogleSQL syntax.

   bq query \
       --batch \
       --use_legacy_sql=false \
       'QUERY'

   Replace QUERY with a valid GoogleSQL query. For example, query the BigQuery public dataset usa_names to determine the most common names in the United States between the years 1910 and 2013:

   bq query \
       --batch \
       --use_legacy_sql=false \
       'SELECT
         name, gender,
         SUM(number) AS total
       FROM
         `bigquery-public-data.usa_names.usa_1910_2013`
       GROUP BY
         name, gender
       ORDER BY
         total DESC
       LIMIT
         10;'
   

   The query job writes the output to a temporary (cache) table.

   Optionally, you can specify the destination table and location for the query results. To write the results to an existing table, include the appropriate flag to append (--append_table=true) or overwrite (--replace=true) the table.

   bq query \
       --batch \
       --location=LOCATION \
       --destination_table=TABLE \
       --use_legacy_sql=false \
       'QUERY'

   Replace the following:

   • LOCATION: the region or multi-region for the destination table—for example, US

     In this example, the usa_names dataset is stored in the US multi-region location. If you specify a destination table for this query, the dataset that contains the destination table must also be in the US multi-region. You cannot query a dataset in one location and write the results to a table in another location.

     You can set a default value for the location using the .bigqueryrc file.

   • TABLE: a name for the destination table—for example, myDataset.myTable

     If the destination table is a new table, then BigQuery creates the table when you run your query. However, you must specify an existing dataset.

     If the table isn't in your current project, then add the Google Cloud project ID using the format PROJECT_ID:DATASET.TABLE—for example, myProject:myDataset.myTable. If --destination_table is unspecified, a query job is generated that writes the output to a temporary table.

To run a query using the API, insert a new job and populate the query job configuration property. Optionally specify your location in the location property in the jobReference section of the job resource.

When you populate the query job properties, include the configuration.query.priority property and set the value to BATCH.

Poll for results by calling getQueryResults. Poll until jobComplete equals true. Check for errors and warnings in the errors list.

Before trying this sample, follow the Go setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Go API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

To run a batch query, set the query priority to QueryJobConfiguration.Priority.BATCH when creating a QueryJobConfiguration.

Before trying this sample, follow the Java setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Java API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

Before trying this sample, follow the Node.js setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Node.js API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

Before trying this sample, follow the Python setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Python API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

Running a continuous query job requires additional configuration. For more information, see Create continuous queries.

When you run a query, you can specify the following settings:

• A destination table for the query results.

• The priority of the job.

• Whether to use cached query results.

• The job timeout in milliseconds.

• Whether to use session mode.

• The type of encryption to use.

• The maximum number of bytes billed for the query.

• The dialect of SQL to use.

• The location in which to run the query. The query must run in the same location as any tables referenced in the query.

• The reservation to run your query in (Preview).

Optional job creation mode can improve the overall latency of queries that run for a short duration, such as those from dashboards or data exploration workloads. This mode executes the query and returns the results inline for SELECT statements without requiring the use of jobs.getQueryResults to fetch the results. Queries using optional job creation mode don't create a job when executed unless BigQuery determines that a job creation is necessary to complete the query.

To enable optional job creation mode, set the jobCreationMode field of the QueryRequest instance to JOB_CREATION_OPTIONAL in the jobs.query request body.

When the value of this field is set to JOB_CREATION_OPTIONAL, BigQuery determines if the query can use the optional job creation mode. If so, BigQuery executes the query and returns all results in the rows field of the response. Since a job isn't created for this query, BigQuery doesn't return a jobReference in the response body. Instead, it returns a queryId field, which you can use to get insights about the query using the INFORMATION_SCHEMA.JOBS view. Since no job is created, there is no jobReference that can be passed to jobs.get and jobs.getQueryResults APIs to lookup these queries.

If BigQuery determines that a job is required to complete the query, a jobReference is returned. You can inspect the job_creation_reason field in INFORMATION_SCHEMA.JOBS view to determine the reason that a job was created for the query. In this case, you should use jobs.getQueryResults to fetch the results when the query is complete.

When you use the JOB_CREATION_OPTIONAL value, the jobReference field might not be present in the response. Check if the field exists before accessing it.

When JOB_CREATION_OPTIONAL is specified for multi-statement queries (scripts), BigQuery might optimize the execution process. As part of this optimization, BigQuery might determine that it can complete the script by creating fewer job resources than the number of individual statements, potentially even executing the entire script without creating any job at all. This optimization depends on BigQuery's assessment of the script, and the optimization might not be applied in every case. The optimization is fully automated by the system. No user controls or actions are required.

To run a query using optional job creation mode, select one of the following options:

1. Go to the BigQuery page.

   Go to BigQuery

2. Click add_box SQL query.

3. In the query editor, enter a valid GoogleSQL query.

   For example, query the BigQuery public dataset usa_names to determine the most common names in the United States between the years 1910 and 2013:

   SELECT
     name, gender,
     SUM(number) AS total
   FROM
     `bigquery-public-data.usa_names.usa_1910_2013`
   GROUP BY
     name, gender
   ORDER BY
     total DESC
   LIMIT
     10;
   

4. Click settings More, and then choose the Optional job creation query mode. To confirm this choice, click Confirm.

5. Click play_circle Run.

1. In the Google Cloud console, activate Cloud Shell.

   Activate Cloud Shell

   At the bottom of the Google Cloud console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.

2. Use the bq query command and specify the --job_creation_mode=JOB_CREATION_OPTIONAL flag. In the following example, the --use_legacy_sql=false flag lets you use GoogleSQL syntax.

   bq query \
       --rpc=true \
       --use_legacy_sql=false \
       --job_creation_mode=JOB_CREATION_OPTIONAL \
       --location=LOCATION \
       'QUERY'

   Replace QUERY with a valid GoogleSQL query, and replace LOCATION with a valid region where the dataset is located. For example, query the BigQuery public dataset usa_names to determine the most common names in the United States between the years 1910 and 2013:

   bq query \
       --rpc=true \
       --use_legacy_sql=false \
       --job_creation_mode=JOB_CREATION_OPTIONAL \
       --location=us \
       'SELECT
         name, gender,
         SUM(number) AS total
       FROM
         `bigquery-public-data.usa_names.usa_1910_2013`
       GROUP BY
         name, gender
       ORDER BY
         total DESC
       LIMIT
         10;'
   

   The query job returns the output inline in the response.

   Note: you may use --apilog=stdout to log API requests and responses to extract the queryId if needed.

To run a query in optional job creation mode using the API, run a query synchronously and populate the QueryRequest property. Include the jobCreationMode property and set its value to JOB_CREATION_OPTIONAL.

Check the response. If jobComplete equals true and jobReference is empty, read the results from the rows field. You can also get the queryId from the response.

If jobReference is present, you can check jobCreationReason for why a job was created by BigQuery. Poll for results by calling getQueryResults. Poll until jobComplete equals true. Check for errors and warnings in the errors list.

Available version: 2.51.0 and up

Before trying this sample, follow the Java setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Java API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

To run a query with a proxy, see Configuring a proxy.

Available version: 3.34.0 and up

Before trying this sample, follow the Python setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Python API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

Available version: 8.1.0 and up

Before trying this sample, follow the Node.js setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Node.js API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

Available version: 1.69.0 and up

Before trying this sample, follow the Go setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Go API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

Available version: JDBC v1.6.1 and up

Requires setting JobCreationMode=2 in the connection string.

    jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;JobCreationMode=2;Location=US;
  

Available version: ODBC v3.0.7.1016 and up

Requires setting JobCreationMode=2 in the .ini file.

    [ODBC Data Sources]
    Sample DSN=Simba Google BigQuery ODBC Connector 64-bit
    [Sample DSN]
    JobCreationMode=2
  

For information about quotas regarding interactive and batch queries, see Query jobs.

You can get information about queries as they are executing by using the jobs explorer or by querying the INFORMATION_SCHEMA.JOBS_BY_PROJECT view.

A dry run in BigQuery provides the following information:

• estimate of charges in on-demand mode
• validation of your query
• approximate bytes processed by your query in capacity mode

Dry runs don't use query slots, and you are not charged for performing a dry run. You can use the estimate returned by a dry run to calculate query costs in the pricing calculator.

To perform a dry run, do the following:

1. Go to the BigQuery page.

   Go to BigQuery

2. Enter your query in the query editor.

   If the query is valid, then a check mark automatically appears along with the amount of data that the query will process. If the query is invalid, then an exclamation point appears along with an error message.

Enter a query like the following using the --dry_run flag.

bq query \
--use_legacy_sql=false \
--dry_run \
'SELECT
   COUNTRY,
   AIRPORT,
   IATA
 FROM
   `project_id`.dataset.airports
 LIMIT
   1000'
 

For a valid query, the command produces the following response:

Query successfully validated. Assuming the tables are not modified,
running this query will process 10918 bytes of data.

To perform a dry run by using the API, submit a query job with dryRun set to true in the JobConfiguration type.

Before trying this sample, follow the Go setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Go API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

Before trying this sample, follow the Java setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Java API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

Before trying this sample, follow the Node.js setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Node.js API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

Before trying this sample, follow the PHP setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery PHP API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

Set the QueryJobConfig.dry_run property to True. Client.query() always returns a completed QueryJob when provided a dry run query configuration.

Before trying this sample, follow the Python setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Python API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

• Learn how to manage query jobs.
• Learn how to view query history.
• Learn how to save and share queries.
• Learn about query queues.
• Learn how to write query results.

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-08-14 UTC.

[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-08-14 UTC."],[[["BigQuery supports three types of query jobs: interactive, which run immediately; batch, which are queued and start when resources are available; and continuous, which analyze incoming data in real time."],["You can execute queries through the Google Cloud console, the `bq` command-line tool, the BigQuery REST API, or the BigQuery client libraries."],["Running a dry run of a query will give you an estimate of charges and processed bytes, while validating your query without utilizing query slots or incurring charges."],["Short query optimized mode enhances the performance of brief queries by executing them inline and utilizing a query result cache, but it may fall back to creating a job when necessary."],["To run batch query jobs, set the priority to `BATCH` either through the console or with `--batch` in the command line, or the `configuration.query.priority` property to `BATCH` in the API, allowing for the batch query to be queued until resources are available."]]],[]]

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4