Consulta datos de Cloud Storage

BigQuery admite la consulta de datos de Cloud Storage en los siguientes formatos:

  • Valores separados por comas (CSV)
  • JSON (delimitado por saltos de línea)
  • Avro
  • ORC
  • Parquet
  • Exportaciones de Datastore
  • Exportaciones de Firestore

BigQuery admite la consulta de datos de Cloud Storage de estas clases de almacenamiento:

  • Estándar
  • Nearline
  • Coldline
  • Archivo

Para consultar una fuente de datos externa de Cloud Storage, proporciona la ruta de acceso del URI de Cloud Storage a tus datos y crea una tabla que haga referencia a la fuente de datos. La tabla usada para hacer referencia a la fuente de datos de Cloud Storage puede ser una tabla permanente o temporal.

Asegúrate de tener en cuenta la ubicación de tu conjunto de datos y del depósito de Cloud Storage cuando consultas datos almacenados en Cloud Storage.

Antes de comenzar

Otorga roles de Identity and Access Management (IAM) que les brindan a los usuarios los permisos necesarios para realizar cada tarea de este documento. Los permisos necesarios para realizar una tarea (si existen) se enumeran en la sección “Permisos necesarios” de la tarea.

Recupera el URI de Cloud Storage

Para crear una tabla externa con una fuente de datos de Cloud Storage, debes proporcionar el URI de Cloud Storage.

El URI de Cloud Storage incluye el nombre del bucket y tu objeto (nombre del archivo). Por ejemplo, si el depósito de Cloud Storage se llama mybucket y el archivo de datos se llama myfile.csv, el URI del depósito será gs://mybucket/myfile.csv. Si los datos se separan en varios archivos, puedes usar un comodín en el URI. Para obtener más información, consulta la documentación sobre los URI de solicitud de Cloud Storage.

BigQuery no admite los URI de origen que incluyen varias barras consecutivas después de la doble barra inicial. Los nombres de los objetos de Cloud Storage pueden contener varios caracteres de barras consecutivas (“/”). Sin embargo, BigQuery convierte varias barras consecutivas en una sola barra. Por ejemplo, el URI de origen que se muestra a continuación, a pesar de ser válido en Cloud Storage, no funciona en BigQuery: gs://bucket/my//object//name.

Para recuperar el URI de Cloud Storage, sigue estos pasos:

  1. Abre Cloud Storage Console

    Consola de Cloud Storage

  2. Explora la ubicación del objeto (archivo) que contiene los datos de origen.

  3. En la parte superior de la consola de Cloud Storage, toma nota de la ruta al objeto. Para redactar el URI, reemplaza gs://bucket/file por la ruta adecuada, por ejemplo, gs://mybucket/myfile.json. bucket es el nombre del depósito de Cloud Storage y file es el nombre del objeto (archivo) que contiene los datos.

Tablas externas permanentes y temporales

Puedes consultar una fuente de datos externa en BigQuery; para ello, usa una tabla permanente o una temporal. Una tabla permanente es una tabla que se crea en un conjunto de datos y está vinculada a tu fuente de datos externa. Debido a que la tabla es permanente, puedes usar controles de acceso para compartir la tabla con otras personas que también tengan acceso a la fuente de datos externa subyacente y también puedes hacer consultas en cualquier momento.

Cuando consultas una fuente de datos externa con una tabla temporal, envías un comando que incluye una consulta y crea una tabla no permanente vinculada a la fuente de datos externa. Cuando usas una tabla temporal, no se crea una tabla en uno de tus conjuntos de datos de BigQuery. Debido a que la tabla no se almacena de forma permanente en un conjunto de datos, no puede compartirse con otras personas. Consultar una fuente de datos externa con una tabla temporal es útil para las consultas únicas ad-hoc sobre datos externos o procesos de extracción, transformación y carga (ETL).

Consulta datos de Cloud Storage con tablas externas permanentes

Permisos necesarios

Para consultar datos externos en Cloud Storage mediante una tabla permanente, necesitas permisos para hacer lo siguiente:

  • Ejecutar un trabajo de consulta a nivel de proyecto o superior
  • Crear una tabla que apunte a los datos externos
  • Acceder a la tabla

Si tus datos externos se almacenan en Cloud Storage, también necesitas permisos para acceder al bucket que los contiene.

Permisos para crear y consultar una tabla externa en BigQuery

Para crear y consultar una tabla externa en BigQuery, necesitas los siguientes permisos de IAM:

  • bigquery.tables.create
  • bigquery.tables.getData
  • bigquery.jobs.create

Cada una de los siguientes roles predefinidos de IAM incluye los permisos que necesitas para crear y consultar una tabla externa en BigQuery:

  • roles/bigquery.dataEditor
  • roles/bigquery.dataOwner
  • roles/bigquery.admin (incluye el permiso bigquery.jobs.create)
  • roles/bigquery.user (incluye el permiso bigquery.jobs.create)
  • roles/bigquery.jobUser (incluye el permiso bigquery.jobs.create)

Además, si tienes el permiso bigquery.datasets.create, puedes crear tablas externas en los conjuntos de datos que crees y acceder a ellas. Sin embargo, igual necesitas el permiso bigquery.jobs.create para consultar los datos.

Para obtener más información sobre los roles y permisos de IAM en BigQuery, consulta Roles y permisos predefinidos.

Permisos para consultar datos externos en un bucket de Cloud Storage

Para consultar datos externos en un bucket de Cloud Storage, necesitas los siguientes permisos de IAM:

  • storage.objects.get
  • storage.objects.list (obligatorio si usas un comodín de URI)

El rol predefinido de IAM roles/storage.objectViewer incluye todos los permisos que necesitas para consultar datos externos en un bucket de Cloud Storage.

Permisos de acceso para instancias de Compute Engine

Si, desde una instancia de Compute Engine necesitas consultar una tabla externa vinculada a una fuente de Cloud Storage, la instancia debe tener, al menos, el permiso de acceso de solo lectura de Cloud Storage (https://www.googleapis.com/auth/devstorage.read_only).

Los permisos controlan el acceso de la instancia de Compute Engine a los productos de Google Cloud, incluido Cloud Storage. Las aplicaciones que se ejecutan en la instancia usan la cuenta de servicio adjuntada a la instancia para llamar a las API de Google Cloud.

Si configuras una instancia de Compute Engine de modo que se ejecute como la cuenta de servicio predeterminada de Compute Engine, de forma predeterminada, se le otorga una cantidad de permisos predeterminados, incluido el permiso https://www.googleapis.com/auth/devstorage.read_only.

Si, en cambio, configuras la instancia con una cuenta de servicio personalizada, asegúrate de otorgar de forma explícita el permiso https://www.googleapis.com/auth/devstorage.read_only a la instancia.

Para obtener más información acerca de cómo aplicar alcances a una instancia de Compute Engine, consulta Cambia la cuenta de servicio y los niveles de acceso de una instancia. Para obtener más información sobre las cuentas de servicio de Compute Engine, consulta la sección sobre las cuentas de servicio.

Crea y consulta una tabla externa permanente

Puedes crear una tabla permanente vinculada a tu fuente de datos externa mediante las siguientes opciones:

Para consultar una fuente de datos externa mediante una tabla permanente, debes crear una tabla en un conjunto de datos de BigQuery vinculado a tu fuente de datos externa. Los datos no se almacenan en la tabla de BigQuery. Debido a que la tabla es permanente, puedes usar controles de acceso para compartir la tabla con otras personas que también tengan acceso a la fuente de datos externa subyacente.

Cuando creas una tabla externa permanente, puedes especificar el esquema de las siguientes maneras:

Para crear una tabla externa, sigue estos pasos:

Console

  1. En Cloud Console, abre la página de BigQuery.

Ir a BigQuery

  1. En el panel Explorador, expande tu proyecto y selecciona un conjunto de datos.

  2. Expande la opción Acciones y haz clic en Crear table.

  3. En la página Crear tabla, en la sección Fuente haz lo siguiente:

    • En Create table from (Crear tabla desde), selecciona Google Cloud Storage.

    • En el campo Seleccionar archivo del bucket de GCS, busca el archivo o bucket de Cloud Storage o ingresa el URI de Cloud Storage. Ten en cuenta que no puedes incluir varios URI en Cloud Console, pero se admiten comodines. El bucket de Cloud Storage debe estar en la misma ubicación que el conjunto de datos que contiene la tabla que quieres crear.

    • En Formato de archivo, selecciona el formato de tus datos.

  4. En la sección Destination (Destino) de la página Create table (Crear tabla), haz lo siguiente:

    • En Conjunto de datos, elige el conjunto de datos apropiado.
    • Verifica que el Tipo de tabla esté configurado como Tabla externa.
    • En el campo Nombre de tabla, ingresa el nombre de la tabla que quieres crear en BigQuery.

  5. En la sección Esquema, puedes habilitar la detección automática de esquemas o especificar un esquema de forma manual.

    • Para habilitar la detección automática de esquemas, selecciona la opción Detección automática.

    • Para especificar un esquema de forma manual, deja sin marcar la opción Detección automática y, luego, realiza una de las siguientes acciones:

      • Habilita Editar como texto y, luego, ingresa el esquema de la tabla como un array JSON.

  6. Haga clic en Crear tabla.

Después de crear la tabla permanente, puedes ejecutar una consulta en ella como si fuera una tabla de BigQuery nativa. Cuando se completa tu consulta, puedes exportar los resultados como archivos CSV o JSON, guardarlos como una tabla o en Hojas de cálculo de Google.

bq

Para crear una tabla externa, usa el comando bq mk con la marca --external_table_definition. Esta marca contiene una ruta de acceso a un archivo de definición de tabla o una definición de tabla intercalada.

Opción 1: Archivo de definición de tablas

Usa el comando bq mkdef para crear un archivo de definición de tablas y, luego, pasa la ruta de acceso del archivo al comando bq mk de la siguiente manera:

bq mkdef --source_format=SOURCE_FORMAT \
  BUCKET_URL > DEFINITION_FILE

bq mk --table \
  --external_table_definition=DEFINITION_FILE \
  DATASET_NAME.TABLE_NAME \
  SCHEMA

Aquí:

  • SOURCE_FORMAT es el formato de la fuente de datos externa. Por ejemplo, CSV.
  • BUCKET_URI es tu URI de Cloud Storage.
  • DEFINITION_FILE es la ruta de acceso al archivo de definición de tabla en tu máquina local.
  • DATASET_NAME es el nombre del conjunto de datos que contiene la tabla.
  • TABLE_NAME es el nombre de la tabla que creas.
  • SCHEMA especifica una ruta a un archivo de esquema JSON o especifica el esquema en el formato field:data_type,field:data_type,....

Ejemplo:

bq mkdef --source_format=CSV gs://mybucket/sales.csv > mytable_def

bq mk --table --external_table_definition=mytable_def \
  mydataset.mytable \
  Region:STRING,Quarter:STRING,Total_sales:INTEGER

Para usar la detección automática de esquemas, configura la marca --autodetect=true en el comando mkdef y omite el esquema:

bq mkdef --source_format=CSV --autodetect=true \
  gs://mybucket/sales.csv > mytable_def

bq mk --table --external_table_definition=mytable_def \
  mydataset.mytable

Opción 2: Definición de tabla intercalada

En lugar de crear un archivo de definición de tablas, puedes pasar la definición de tabla directamente al comando bq mk:

bq mk --table \
  --external_table_definition=@SOURCE_FORMAT=BUCKET_URI \
  DATASET_NAME.TABLE_NAME \
  SCHEMA

Aquí:

  • SOURCE_FORMAT es el formato de la fuente de datos externa, por ejemplo, CSV.
  • BUCKET_URI es tu URI de Cloud Storage.
  • DATASET_NAME es el nombre del conjunto de datos que contiene la tabla.
  • TABLE_NAME es el nombre de la tabla que creas.
  • SCHEMA especifica una ruta a un archivo de esquema JSON o especifica el esquema en el formato field:data_type,field:data_type,.... Para usar la detección automática de esquemas, omite este argumento.

Ejemplo:

bq mkdef --source_format=CSV gs://mybucket/sales.csv > mytable_def
bq mk --table --external_table_definition=mytable_def \
  mydataset.mytable \
  Region:STRING,Quarter:STRING,Total_sales:INTEGER

DDL

Puedes crear una tabla externa permanente si ejecutas la instrucción de DDL CREATE EXTERNAL TABLE. Puedes especificar el esquema de manera explícita. Si no especificas un esquema, BigQuery usa la detección automática de esquemas para inferirlo a partir de los datos externos.

En el siguiente ejemplo, se usa la detección automática de esquemas para crear una tabla externa llamada sales que está vinculada a un archivo CSV almacenado en Cloud Storage:

CREATE OR REPLACE EXTERNAL TABLE mydataset.sales
OPTIONS (
  format = 'CSV',
  uris = ['gs://mybucket/sales.csv']
)

En el siguiente ejemplo, se especifica un esquema de manera explícita y se omite la primera fila en el archivo CSV.

CREATE OR REPLACE EXTERNAL TABLE mydataset.sales
(
  Region STRING,
  Quarter STRING,
  Total_Sales INT64
)
OPTIONS (
  format = 'CSV',
  uris = ['gs://mybucket/sales.csv'],
  skip_leading_rows = 1
)

API

Crea un ExternalDataConfiguration cuando uses el método de API tables.insert. Especifica la propiedad schema o establece la propiedad autodetect en true a fin de habilitar la detección automática de esquemas para las fuentes de datos compatibles.

Java

Antes de probar este ejemplo, sigue las instrucciones de configuración para Java incluidas en la guía de inicio rápido de BigQuery sobre cómo usar bibliotecas cliente. Para obtener más información, consulta la documentación de referencia de la API de BigQuery para Java. 

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.CsvOptions;
import com.google.cloud.bigquery.ExternalTableDefinition;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;
import com.google.cloud.bigquery.TableResult;

// Sample to queries an external data source using a permanent table
public class QueryExternalGCSPerm {

  public static void runQueryExternalGCSPerm() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "gs://cloud-samples-data/bigquery/us-states/us-states.csv";
    Schema schema =
        Schema.of(
            Field.of("name", StandardSQLTypeName.STRING),
            Field.of("post_abbr", StandardSQLTypeName.STRING));
    String query =
        String.format("SELECT * FROM %s.%s WHERE name LIKE 'W%%'", datasetName, tableName);
    queryExternalGCSPerm(datasetName, tableName, sourceUri, schema, query);
  }

  public static void queryExternalGCSPerm(
      String datasetName, String tableName, String sourceUri, Schema schema, String query) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      // Skip header row in the file.
      CsvOptions csvOptions = CsvOptions.newBuilder().setSkipLeadingRows(1).build();

      TableId tableId = TableId.of(datasetName, tableName);
      // Create a permanent table linked to the GCS file
      ExternalTableDefinition externalTable =
          ExternalTableDefinition.newBuilder(sourceUri, csvOptions).setSchema(schema).build();
      bigquery.create(TableInfo.of(tableId, externalTable));

      // Example query to find states starting with 'W'
      TableResult results = bigquery.query(QueryJobConfiguration.of(query));

      results
          .iterateAll()
          .forEach(row -> row.forEach(val -> System.out.printf("%s,", val.toString())));

      System.out.println("Query on external permanent table performed successfully.");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

Node.js

Antes de probar este ejemplo, sigue las instrucciones de configuración para Node.js incluidas en la guía de inicio rápido de BigQuery sobre cómo usar bibliotecas cliente. Para obtener más información, consulta la Documentación de referencia de la API de Node.js de BigQuery. 

// Import the Google Cloud client library and create a client
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function queryExternalGCSPerm() {
  // Queries an external data source using a permanent table

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = "my_dataset";
  // const tableId = "my_table";

  // Configure the external data source
  const dataConfig = {
    sourceFormat: 'CSV',
    sourceUris: ['gs://cloud-samples-data/bigquery/us-states/us-states.csv'],
    // Optionally skip header row
    csvOptions: {skipLeadingRows: 1},
  };

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
  const options = {
    schema: schema,
    externalDataConfiguration: dataConfig,
  };

  // Create an external table linked to the GCS file
  const [table] = await bigquery
    .dataset(datasetId)
    .createTable(tableId, options);

  console.log(`Table ${table.id} created.`);

  // Example query to find states starting with 'W'
  const query = `SELECT post_abbr
  FROM \`${datasetId}.${tableId}\`
  WHERE name LIKE 'W%'`;

  // Run the query as a job
  const [job] = await bigquery.createQueryJob(query);
  console.log(`Job ${job.id} started.`);

  // Wait for the query to finish
  const [rows] = await job.getQueryResults();

  // Print the results
  console.log('Rows:');
  console.log(rows);
}

Python

Antes de probar esta muestra, sigue las instrucciones de configuración para Python incluidas en la guía de inicio rápido de BigQuery sobre cómo usar bibliotecas cliente. Si deseas obtener más información, consulta la documentación de referencia de la API de Python de BigQuery. 

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_id = 'my_dataset'

# Configure the external data source
dataset_ref = bigquery.DatasetReference(project, dataset_id)
table_id = "us_states"
schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
]
table = bigquery.Table(dataset_ref.table(table_id), schema=schema)
external_config = bigquery.ExternalConfig("CSV")
external_config.source_uris = [
    "gs://cloud-samples-data/bigquery/us-states/us-states.csv"
]
external_config.options.skip_leading_rows = 1  # optionally skip header row
table.external_data_configuration = external_config

# Create a permanent table linked to the GCS file
table = client.create_table(table)  # API request

# Example query to find states starting with 'W'
sql = 'SELECT * FROM `{}.{}` WHERE name LIKE "W%"'.format(dataset_id, table_id)

query_job = client.query(sql)  # API request

w_states = list(query_job)  # Waits for query to finish
print("There are {} states with names starting with W.".format(len(w_states)))

Consulta datos de Cloud Storage con tablas temporales

Si quieres realizar una consulta sobre una fuente de datos externa sin crear una tabla permanente, debes ejecutar un comando que combine lo siguiente:

  • Un archivo de definición de tablas con una consulta
  • Una definición de esquema intercalado con una consulta
  • Un archivo de definición de esquema JSON con una consulta

Se emplea el archivo de definición de tabla o el esquema proporcionado para crear la tabla externa temporal, y es en esta donde se ejecuta la consulta. La herramienta de línea de comandos de bq y la API admiten las consultas a una fuente de datos externa mediante el uso de una tabla temporal.

Cuando usas una tabla externa temporal, no se crea una tabla en uno de los conjuntos de datos de BigQuery. Debido a que la tabla no se almacena de forma permanente en un conjunto de datos, no puede compartirse con otras personas. Consultar una fuente de datos externa con una tabla temporal es útil para las consultas únicas ad-hoc sobre datos externos o procesos de extracción, transformación y carga (ETL).

Permisos necesarios

Para consultar datos externos en Cloud Storage con una tabla temporal, necesitas permisos para ejecutar trabajos de consulta a nivel de proyecto o superior, además de acceso al conjunto de datos que contiene la tabla que apunta a los datos externos. Para consultar datos en Cloud Storage, también necesitas permisos de acceso al bucket que los contiene.

Permisos para consultar una tabla externa en BigQuery

Para consultar una tabla externa en BigQuery mediante una tabla temporal, necesitas los siguientes permisos de IAM:

  • bigquery.tables.getData
  • bigquery.jobs.create

Cada una de los siguientes roles predefinidos de IAM incluye los permisos que necesitas para consultar una tabla externa en BigQuery mediante una tabla temporal:

  • roles/bigquery.dataEditor
  • roles/bigquery.dataOwner
  • roles/bigquery.admin (incluye el permiso bigquery.jobs.create)
  • roles/bigquery.user (incluye el permiso bigquery.jobs.create)
  • roles/bigquery.jobUser (incluye el permiso bigquery.jobs.create)

Además, si tienes el permiso bigquery.datasets.create, puedes crear tablas externas en los conjuntos de datos que crees y acceder a ellas. Sin embargo, igual necesitas el permiso bigquery.jobs.create para consultar los datos.

Para obtener más información sobre los roles y permisos de IAM en BigQuery, consulta Roles y permisos predefinidos.

Permisos para consultar datos externos en un bucket de Cloud Storage

Para consultar datos externos en un bucket de Cloud Storage, necesitas los siguientes permisos de IAM:

  • storage.objects.get
  • storage.objects.list (obligatorio si usas un comodín de URI)

El rol predefinido de IAM roles/storage.objectViewer incluye todos los permisos que necesitas para consultar datos externos en un bucket de Cloud Storage.

Crea y consulta una tabla temporal

Puedes crear y consultar una tabla temporal vinculada a una fuente de datos externa mediante la herramienta de línea de comandos de bq, la API o las bibliotecas cliente.

bq

Para consultar una tabla temporal vinculada a una fuente de datos externa, usa el comando bq query con la marca --external_table_definition. Cuando usas la herramienta de línea de comandos de bq para consultar una tabla temporal vinculada a una fuente de datos externa, puedes identificar el esquema de la tabla por medio de las siguientes opciones:

  • Un archivo de definición de tablas (almacenado en tu máquina local)
  • Una definición de esquema intercalado
  • Un archivo de esquema JSON (almacenado en tu máquina local)

Opcional: Proporciona la marca --location y configura el valor en tu ubicación.

Para realizar una consulta en una tabla temporal vinculada a tu fuente de datos externa mediante un archivo de definición de tablas, ingresa el comando siguiente.

bq --location=location query \
--external_table_definition=table::definition_file \
'query'

En el ejemplo anterior, se ilustra lo siguiente:

  • location es el nombre de tu ubicación. La marca --location es opcional. Por ejemplo, si usas BigQuery en la región de Tokio, puedes establecer el valor de la marca en asia-northeast1. Puedes configurar un valor predeterminado para la ubicación con el archivo .bigqueryrc.
  • table es el nombre de la tabla temporal que crearás.
  • definition_file es la ruta de acceso al archivo de definición de tabla de tu máquina local.
  • query es la consulta que enviarás a la tabla temporal.

Por ejemplo, con el siguiente comando, se crea y consulta una tabla temporal llamada sales mediante un archivo de definición de tablas denominado sales_def.

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

Para realizar una consulta en una tabla temporal vinculada a tu fuente de datos externa por medio de una definición de esquema intercalado, ingresa el comando siguiente.

bq --location=location query \
--external_table_definition=table::schema@source_format=Cloud Storage URI \
'query'

En el ejemplo anterior, se ilustra lo siguiente:

  • location es el nombre de tu ubicación. La marca --location es opcional. Por ejemplo, si usas BigQuery en la región de Tokio, puedes establecer el valor de la marca en asia-northeast1. Puedes configurar un valor predeterminado para la ubicación con el archivo .bigqueryrc.
  • table es el nombre de la tabla temporal que crearás.
  • schema es la definición de esquema intercalada en el formato field:data_type,field:data_type.
  • source_format es el formato de la fuente de datos externa, por ejemplo, CSV.
  • Cloud Storage URI es tu URI de Cloud Storage.
  • query es la consulta que enviarás a la tabla temporal.

Por ejemplo, con el siguiente comando, se crea y consulta una tabla temporal llamada sales vinculada a un archivo CSV almacenado en Cloud Storage con la siguiente definición de esquema: 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'

Para realizar una consulta en una tabla temporal vinculada a tu fuente de datos externa por medio de un archivo de esquema JSON, ingresa el siguiente comando:

bq --location=location query \
--external_table_definition=schema_file@source_format=Cloud Storage URI \
'query'

En el ejemplo anterior, se ilustra lo siguiente:

  • location es el nombre de tu ubicación. La marca --location es opcional. Por ejemplo, si usas BigQuery en la región de Tokio, puedes establecer el valor de la marca en asia-northeast1. Puedes configurar un valor predeterminado para la ubicación con el archivo .bigqueryrc.
  • schema_file es la ruta al archivo de esquema JSON en tu máquina local.
  • source_format es el formato de la fuente de datos externa, por ejemplo, CSV.
  • Cloud Storage URI es tu URI de Cloud Storage.
  • query es la consulta que enviarás a la tabla temporal.

Por ejemplo, con el comando siguiente, se crea y consulta una tabla temporal llamada sales vinculada a un archivo CSV almacenado en Cloud Storage con el archivo de esquema /tmp/sales_schema.json.

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

API

Java

Antes de probar este ejemplo, sigue las instrucciones de configuración para Java incluidas en la guía de inicio rápido de BigQuery sobre cómo usar bibliotecas cliente. Para obtener más información, consulta la documentación de referencia de la API de BigQuery para Java. 

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.CsvOptions;
import com.google.cloud.bigquery.ExternalTableDefinition;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.TableResult;

// Sample to queries an external data source using a temporary table
public class QueryExternalGCSTemp {

  public static void runQueryExternalGCSTemp() {
    // TODO(developer): Replace these variables before running the sample.
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "gs://cloud-samples-data/bigquery/us-states/us-states.csv";
    Schema schema =
        Schema.of(
            Field.of("name", StandardSQLTypeName.STRING),
            Field.of("post_abbr", StandardSQLTypeName.STRING));
    String query = String.format("SELECT * FROM %s WHERE name LIKE 'W%%'", tableName);
    queryExternalGCSTemp(tableName, sourceUri, schema, query);
  }

  public static void queryExternalGCSTemp(
      String tableName, String sourceUri, Schema schema, String query) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      // Skip header row in the file.
      CsvOptions csvOptions = CsvOptions.newBuilder().setSkipLeadingRows(1).build();

      // Configure the external data source and query job.
      ExternalTableDefinition externalTable =
          ExternalTableDefinition.newBuilder(sourceUri, csvOptions).setSchema(schema).build();
      QueryJobConfiguration queryConfig =
          QueryJobConfiguration.newBuilder(query)
              .addTableDefinition(tableName, externalTable)
              .build();

      // Example query to find states starting with 'W'
      TableResult results = bigquery.query(queryConfig);

      results
          .iterateAll()
          .forEach(row -> row.forEach(val -> System.out.printf("%s,", val.toString())));

      System.out.println("Query on external temporary table performed successfully.");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

Node.js

Antes de probar este ejemplo, sigue las instrucciones de configuración para Node.js incluidas en la guía de inicio rápido de BigQuery sobre cómo usar bibliotecas cliente. Para obtener más información, consulta la Documentación de referencia de la API de Node.js de BigQuery. 

// Import the Google Cloud client library and create a client
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function queryExternalGCSTemp() {
  // Queries an external data source using a temporary table.

  const tableId = 'us_states';

  // Configure the external data source
  const externalDataConfig = {
    sourceFormat: 'CSV',
    sourceUris: ['gs://cloud-samples-data/bigquery/us-states/us-states.csv'],
    // Optionally skip header row.
    csvOptions: {skipLeadingRows: 1},
    schema: {fields: schema},
  };

  // Example query to find states starting with 'W'
  const query = `SELECT post_abbr
  FROM \`${tableId}\`
  WHERE name LIKE 'W%'`;

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
  const options = {
    query,
    tableDefinitions: {[tableId]: externalDataConfig},
  };

  // Run the query as a job
  const [job] = await bigquery.createQueryJob(options);
  console.log(`Job ${job.id} started.`);

  // Wait for the query to finish
  const [rows] = await job.getQueryResults();

  // Print the results
  console.log('Rows:');
  console.log(rows);
}

Python

Antes de probar esta muestra, sigue las instrucciones de configuración para Python incluidas en la guía de inicio rápido de BigQuery sobre cómo usar bibliotecas cliente. Si deseas obtener más información, consulta la documentación de referencia de la API de Python de BigQuery. 

from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

# Configure the external data source and query job.
external_config = bigquery.ExternalConfig("CSV")
external_config.source_uris = [
    "gs://cloud-samples-data/bigquery/us-states/us-states.csv"
]
external_config.schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
]
external_config.options.skip_leading_rows = 1
table_id = "us_states"
job_config = bigquery.QueryJobConfig(table_definitions={table_id: external_config})

# Example query to find states starting with 'W'.
sql = 'SELECT * FROM `{}` WHERE name LIKE "W%"'.format(table_id)

query_job = client.query(sql, job_config=job_config)  # Make an API request.

w_states = list(query_job)  # Wait for the job to complete.
print("There are {} states with names starting with W.".format(len(w_states)))

Consulta datos particionados de forma externa

Consulta las instrucciones para consultar datos de Cloud Storage particionados de forma externa.

Admisión de comodines para el URI de Cloud Storage

Si los datos de Cloud Storage están separados en varios archivos que comparten un nombre base común, puedes usar un comodín en el URI en el archivo de definición de tablas. También puedes usar un comodín cuando creas una tabla externa sin usar un archivo de definición de tablas.

Para agregar un comodín al URI de Cloud Storage, debes agregar un asterisco (*) al nombre base.

Ejemplos:

  • El siguiente URI comodín selecciona todos los archivos en todas las carpetas que comienzan con el prefijo gs://mybucket/fed-samples/fed-sample:

    gs://mybucket/fed-samples/fed-sample*

  • El siguiente URI de comodín solo selecciona archivos con una extensión .csv en la carpeta llamada fed-samples y cualquier subcarpeta de fed-samples:

    gs://mybucket/fed-samples/fed-sample/*.csv

  • El siguiente URI de comodín selecciona archivos con un patrón de nombres de fed-sample*.csv en la carpeta llamada fed-samples. En este ejemplo, no se seleccionan archivos en subcarpetas de fed-samples.

    gs://mybucket/fed-samples/fed-sample*.csv

Cuando usas la herramienta de línea de comandos de bq, es posible que debas descartar el asterisco en algunas plataformas.

Solo puedes usar un comodín para los objetos (nombres de archivos) dentro de tu bucket. El comodín puede aparecer dentro o al final del nombre del objeto. No se admite la adición de un comodín al nombre del bucket. No se admiten varios comodines en el URI de origen. Por ejemplo, la ruta gs://mybucket/fed-*/temp/*.csv no es válida.

Para las exportaciones de Google Datastore, solo se puede especificar un URI, que debe terminar con .backup_info o .export_metadata.

No se permite el carácter comodín de asterisco en los siguientes casos:

  • Cuando creas tablas externas vinculadas a exportaciones de Datastore o Firestore.
  • Cuando cargas datos de exportación de Datastore o Cloud Firestore desde Cloud Storage.

Seudocolumna _FILE_NAME

Las tablas basadas en fuentes de datos externas proporcionan una seudocolumna llamada _FILE_NAME. Esta columna contiene la ruta completamente calificada al archivo al que pertenece la fila. Esta columna solo está disponible para tablas que hagan referencia a datos externos almacenados en Cloud Storage y Google Drive.

El nombre de columna _FILE_NAME está reservado, lo que significa que no puedes crear una columna con ese nombre en ninguna de tus tablas. Para seleccionar el valor de _FILE_NAME, debes usar un alias. En la consulta de ejemplo siguiente, se muestra la selección de _FILE_NAME mediante la asignación del alias fn a la seudocolumna.

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

Aquí:

  • project_id es un ID de proyecto válido (esta marca no es necesaria si usas Cloud Shell o si configuras un proyecto predeterminado en Google Cloud CLI).
  • dataset es el nombre del conjunto de datos que almacena la tabla externa permanente.
  • table_name es el nombre de la tabla externa permanente.

Cuando la consulta tiene un predicado de filtro en la seudocolumna _FILE_NAME, BigQuery intentará omitir los archivos de lectura que no cumplen con el filtro. Se aplican recomendaciones similares a las consultas a tablas particionadas por tiempo de transferencia mediante seudocolumnas cuando se construyen predicados de consulta con la seudocolumna _FILE_NAME.