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

Showing content from https://developers.google.com/bigquery/docs/query-cloud-storage-data below:

Query Cloud Storage data in external tables | BigQuery

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

This document describes how to query data stored in a Cloud Storage external table.

Ensure that you have a Cloud Storage external table.

To query Cloud Storage external tables, ensure you have the following roles:

• BigQuery Data Viewer (roles/bigquery.dataViewer)
• BigQuery User (roles/bigquery.user)
• Storage Object Viewer (roles/storage.objectViewer)

Depending on your permissions, you can grant these roles to yourself or ask your administrator to grant them to you. For more information about granting roles, see Viewing the grantable roles on resources.

To see the exact BigQuery permissions that are required to query external tables, expand the Required permissions section:

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

After creating a Cloud Storage external table, you can query it using GoogleSQL syntax, the same as if it were a standard BigQuery table. For example, SELECT field1, field2 FROM mydataset.my_cloud_storage_table;.

Querying an external data source using a temporary table is useful for one-time, ad-hoc queries over external data, or for extract, transform, and load (ETL) processes.

To query an external data source without creating a permanent table, you provide a table definition for the temporary table, and then use that table definition in a command or call to query the temporary table. You can provide the table definition in any of the following ways:

• A table definition file
• An inline schema definition
• A JSON schema file

The table definition file or supplied schema is used to create the temporary external table, and the query runs against the temporary external table.

When you use a temporary external table, you do not create a table in one of your BigQuery datasets. Because the table is not permanently stored in a dataset, it cannot be shared with others.

You can create and query a temporary table linked to an external data source by using the bq command-line tool, the API, or the client libraries.

You query a temporary table linked to an external data source using the bq query command with the --external_table_definition flag. When you use the bq command-line tool to query a temporary table linked to an external data source, you can identify the table's schema using:

• A table definition file (stored on your local machine)
• An inline schema definition
• A JSON schema file (stored on your local machine)

(Optional) Supply the --location flag and set the value to your location.

To query a temporary table linked to your external data source using a table definition file, enter the following command.

bq --location=LOCATION query \
--external_table_definition=TABLE::DEFINITION_FILE \
'QUERY'

Replace the following:

• LOCATION: the name of your location. The --location flag is optional. For example, if you are using BigQuery in the Tokyo region, you can set the flag's value to asia-northeast1. You can set a default value for the location using the .bigqueryrc file.
• TABLE: the name of the temporary table you're creating.
• DEFINITION_FILE: the path to the table definition file on your local machine.
• QUERY: the query you're submitting to the temporary table.

For example, the following command creates and queries a temporary table named sales using a table definition file named sales_def.

bq query \
--external_table_definition=sales::sales_def \
'SELECT
  Region,
  Total_sales
FROM
  sales'

To query a temporary table linked to your external data source using an inline schema definition, enter the following command.

bq --location=LOCATION query \
--external_table_definition=TABLE::SCHEMA@SOURCE_FORMAT=BUCKET_PATH \
'QUERY'

Replace the following:

• LOCATION: the name of your location. The --location flag is optional. For example, if you are using BigQuery in the Tokyo region, you can set the flag's value to asia-northeast1. You can set a default value for the location using the .bigqueryrc file.
• TABLE: the name of the temporary table you're creating.
• SCHEMA: the inline schema definition in the format field:data_type,field:data_type.
• SOURCE_FORMAT: the format of the external data source, for example, CSV.

• BUCKET_PATH: the path to the Cloud Storage bucket that contains the data for the table, in the format gs://bucket_name/[folder_name/]file_pattern.

  You can select multiple files from the bucket by specifying one asterisk (*) wildcard character in the file_pattern. For example, gs://mybucket/file00*.parquet. For more information, see Wildcard support for Cloud Storage URIs.

  You can specify multiple buckets for the uris option by providing multiple paths.

  The following examples show valid uris values:

  • gs://bucket/path1/myfile.csv
  • gs://bucket/path1/*.parquet
  • gs://bucket/path1/file1*, gs://bucket1/path1/*

  When you specify uris values that target multiple files, all of those files must share a compatible schema.

  For more information about using Cloud Storage URIs in BigQuery, see Cloud Storage resource path.

• QUERY: the query you're submitting to the temporary table.

For example, the following command creates and queries a temporary table named sales linked to a CSV file stored in Cloud Storage with the following schema definition: Region:STRING,Quarter:STRING,Total_sales:INTEGER.

bq query \
--external_table_definition=sales::Region:STRING,Quarter:STRING,Total_sales:INTEGER@CSV=gs://mybucket/sales.csv \
'SELECT
  Region,
  Total_sales
FROM
  sales'

To query a temporary table linked to your external data source using a JSON schema file, enter the following command.

bq --location=LOCATION query \
--external_table_definition=SCHEMA_FILE@SOURCE_FORMAT=BUCKET_PATH \
'QUERY'

Replace the following:

• LOCATION: the name of your location. The --location flag is optional. For example, if you are using BigQuery in the Tokyo region, you can set the flag's value to asia-northeast1. You can set a default value for the location using the .bigqueryrc file.
• SCHEMA_FILE: the path to the JSON schema file on your local machine.
• SOURCE_FORMAT: the format of the external data source, for example, CSV.

• BUCKET_PATH: the path to the Cloud Storage bucket that contains the data for the table, in the format gs://bucket_name/[folder_name/]file_pattern.

  You can select multiple files from the bucket by specifying one asterisk (*) wildcard character in the file_pattern. For example, gs://mybucket/file00*.parquet. For more information, see Wildcard support for Cloud Storage URIs.

  You can specify multiple buckets for the uris option by providing multiple paths.

  The following examples show valid uris values:

  • gs://bucket/path1/myfile.csv
  • gs://bucket/path1/*.parquet
  • gs://bucket/path1/file1*, gs://bucket1/path1/*

  When you specify uris values that target multiple files, all of those files must share a compatible schema.

  For more information about using Cloud Storage URIs in BigQuery, see Cloud Storage resource path.

• QUERY: the query you're submitting to the temporary table.

For example, the following command creates and queries a temporary table named sales linked to a CSV file stored in Cloud Storage using the /tmp/sales_schema.json schema file.

  bq query \
  --external_table_definition=sales::/tmp/sales_schema.json@CSV=gs://mybucket/sales.csv \
  'SELECT
      Region,
      Total_sales
    FROM
      sales'

To run a query using the API, follow these steps:

1. Create a Job object.
2. Populate the configuration section of the Job object with a JobConfiguration object.
3. Populate the query section of the JobConfiguration object with a JobConfigurationQuery object.
4. Populate the tableDefinitions section of the JobConfigurationQuery object with an ExternalDataConfiguration object.
5. Call the jobs.insert method to run the query asynchronously or the jobs.query method to run the query synchronously, passing in the Job object.

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.

Tables based on external data sources provide a pseudocolumn named _FILE_NAME. This column contains the fully qualified path to the file to which the row belongs. This column is available only for tables that reference external data stored in Cloud Storage, Google Drive, Amazon S3, and Azure Blob Storage.

The _FILE_NAME column name is reserved, which means that you cannot create a column by that name in any of your tables. To select the value of _FILE_NAME, you must use an alias. The following example query demonstrates selecting _FILE_NAME by assigning the alias fn to the pseudocolumn.

  bq query \
  --project_id=PROJECT_ID \
  --use_legacy_sql=false \
  'SELECT
     name,
     _FILE_NAME AS fn
   FROM
     `DATASET.TABLE_NAME`
   WHERE
     name contains "Alex"' 

Replace the following:

• PROJECT_ID is a valid project ID (this flag is not required if you use Cloud Shell or if you set a default project in the Google Cloud CLI)
• DATASET is the name of the dataset that stores the permanent external table
• TABLE_NAME is the name of the permanent external table

When the query has a filter predicate on the _FILE_NAME pseudocolumn, BigQuery attempts to skip reading files that do not satisfy the filter. Similar recommendations to querying ingestion-time partitioned tables using pseudocolumns apply when constructing query predicates with the _FILE_NAME pseudocolumn.

• Learn about using SQL in BigQuery.
• Learn about external tables.
• Learn about BigQuery quotas.

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-07 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-07 UTC."],[[["This document details how to query data stored in Cloud Storage external tables within BigQuery, including both permanent and temporary tables."],["Querying permanent external tables in Cloud Storage is done using GoogleSQL syntax, similar to querying standard BigQuery tables, with an example provided."],["Temporary external tables enable one-time or ad-hoc queries over external data and can be defined using a table definition file, an inline schema, or a JSON schema file."],["Specific roles are required to query Cloud Storage external tables, including BigQuery Data Viewer, BigQuery User, and Storage Object Viewer, along with precise permissions like `bigquery.jobs.create` and `bigquery.tables.get`."],["External tables include a `_FILE_NAME` pseudocolumn that reveals the file's full path, and it can be used in queries, although it has restrictions in terms of naming and when a query has a filter predicate on the `_FILE_NAME` pseudocolumn, BigQuery will attempt to skip reading files that do not satisfy the filter."]]],[]]

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