Consultar datos de Cloud Storage en tablas externas

En este documento se describe cómo consultar los datos almacenados en una tabla externa de Cloud Storage.

Antes de empezar

Asegúrate de tener una tabla externa de Cloud Storage.

Roles obligatorios

Para consultar tablas externas de Cloud Storage, asegúrate de tener los siguientes roles:

  • Lector de datos de BigQuery (roles/bigquery.dataViewer)
  • Usuario de BigQuery (roles/bigquery.user)
  • Lector de objetos de Storage (roles/storage.objectViewer)

En función de tus permisos, puedes asignarte estos roles o pedirle a tu administrador que te los asigne. Para obtener más información sobre cómo conceder roles, consulta el artículo Ver los roles que se pueden conceder en los recursos.

Para ver los permisos exactos de BigQuery que se necesitan para consultar tablas externas, despliega la sección Permisos necesarios:

Permisos obligatorios

También puedes obtener estos permisos con roles personalizados u otros roles predefinidos.

Consultar tablas externas permanentes

Después de crear una tabla externa de Cloud Storage, puede consultarla con la sintaxis de GoogleSQL, como si fuera una tabla estándar de BigQuery. Por ejemplo, SELECT field1, field2 FROM mydataset.my_cloud_storage_table;.

Consultar tablas externas temporales

Consultar una fuente de datos externa mediante una tabla temporal es útil para realizar consultas puntuales y ad hoc sobre datos externos, o para procesos de extracción, transformación y carga (ETL).

Para consultar una fuente de datos externa sin crear una tabla permanente, debes proporcionar una definición de tabla para la tabla temporal y, a continuación, usar esa definición de tabla en un comando o una llamada para consultar la tabla temporal. Puedes proporcionar la definición de la tabla de cualquiera de las siguientes formas:

El archivo de definición de tabla o el esquema proporcionado se usan para crear la tabla externa temporal y la consulta se ejecuta en la tabla externa temporal.

Cuando usas una tabla externa temporal, no creas una tabla en uno de tus conjuntos de datos de BigQuery. Como la tabla no se almacena de forma permanente en un conjunto de datos, no se puede compartir con otros usuarios.

Puedes crear y consultar una tabla temporal vinculada a una fuente de datos externa con la herramienta de línea de comandos bq, la API o las bibliotecas de 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 bq para consultar una tabla temporal vinculada a una fuente de datos externa, puedes identificar el esquema de la tabla de las siguientes formas:

(Opcional) Proporcione la marca --location y asigne el valor a su ubicación.

Para consultar una tabla temporal vinculada a tu fuente de datos externa mediante un archivo de definición de tabla, introduce el siguiente comando.

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

Haz los cambios siguientes:

  • LOCATION: el nombre de tu ubicación. La marca --location es opcional. Por ejemplo, si usas BigQuery en la región de Tokio, puedes definir el valor de la marca como asia-northeast1. Puede definir un valor predeterminado para la ubicación mediante el archivo.bigqueryrc.
  • TABLE: el nombre de la tabla temporal que vas a crear.
  • DEFINITION_FILE: la ruta al archivo de definición de tabla en tu máquina local.
  • QUERY: la consulta que envías a la tabla temporal.

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

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

Para consultar una tabla temporal vinculada a tu fuente de datos externa mediante una definición de esquema insertada, introduce el siguiente comando.

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

Haz los cambios siguientes:

  • LOCATION: el nombre de tu ubicación. La marca --location es opcional. Por ejemplo, si usas BigQuery en la región de Tokio, puedes definir el valor de la marca como asia-northeast1. Puede definir un valor predeterminado para la ubicación mediante el archivo.bigqueryrc.
  • TABLE: el nombre de la tabla temporal que vas a crear.
  • SCHEMA: la definición del esquema insertado en el formato field:data_type,field:data_type.
  • SOURCE_FORMAT: el formato de la fuente de datos externa. Por ejemplo, CSV.

  • BUCKET_PATH: la ruta al segmento de Cloud Storage que contiene los datos de la tabla, con el formato gs://bucket_name/[folder_name/]file_pattern.

    Puede seleccionar varios archivos del segmento especificando un asterisco (*) en el carácter comodín file_pattern. Por ejemplo, gs://mybucket/file00*.parquet. Para obtener más información, consulta Compatibilidad con comodines en URIs de Cloud Storage.

    Puedes especificar varios segmentos para la opción uris proporcionando varias rutas.

    En los siguientes ejemplos se muestran valores de uris válidos:

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

    Si especifica valores de uris que se dirijan a varios archivos, todos esos archivos deben compartir un esquema compatible.

    Para obtener más información sobre cómo usar URIs de Cloud Storage en BigQuery, consulta Ruta de recursos de Cloud Storage.

  • QUERY: la consulta que envías a la tabla temporal.

Por ejemplo, el siguiente comando 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 consultar una tabla temporal vinculada a tu fuente de datos externa mediante un archivo de esquema JSON, introduce el siguiente comando.

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

Haz los cambios siguientes:

  • LOCATION: el nombre de tu ubicación. La marca --location es opcional. Por ejemplo, si usas BigQuery en la región de Tokio, puedes definir el valor de la marca como asia-northeast1. Puede definir un valor predeterminado para la ubicación mediante el archivo.bigqueryrc.
  • SCHEMA_FILE: la ruta al archivo de esquema JSON en tu máquina local.
  • SOURCE_FORMAT: el formato de la fuente de datos externa. Por ejemplo, CSV.

  • BUCKET_PATH: la ruta al segmento de Cloud Storage que contiene los datos de la tabla, con el formato gs://bucket_name/[folder_name/]file_pattern.

    Puede seleccionar varios archivos del segmento especificando un asterisco (*) en el carácter comodín file_pattern. Por ejemplo, gs://mybucket/file00*.parquet. Para obtener más información, consulta Compatibilidad con comodines en URIs de Cloud Storage.

    Puedes especificar varios segmentos para la opción uris proporcionando varias rutas.

    En los siguientes ejemplos se muestran valores de uris válidos:

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

    Si especifica valores de uris que se dirijan a varios archivos, todos esos archivos deben compartir un esquema compatible.

    Para obtener más información sobre cómo usar URIs de Cloud Storage en BigQuery, consulta Ruta de recursos de Cloud Storage.

  • QUERY: la consulta que envías a la tabla temporal.

Por ejemplo, el siguiente comando crea y consulta una tabla temporal llamada sales vinculada a un archivo CSV almacenado en Cloud Storage mediante 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

Para ejecutar una consulta mediante la API, sigue estos pasos:

  1. Crea un Job objeto.
  2. Rellena la sección configuration del objeto Job con un objeto JobConfiguration.
  3. Rellena la sección query del objeto JobConfiguration con un objeto JobConfigurationQuery.
  4. Rellena la sección tableDefinitions del objeto JobConfigurationQuery con un objeto ExternalDataConfiguration.
  5. Llama al método jobs.insert para ejecutar la consulta de forma asíncrona o al método jobs.query para ejecutarla de forma síncrona. En ambos casos, debes pasar el objeto Job.

Java

Antes de probar este ejemplo, sigue las Javainstrucciones de configuración de la guía de inicio rápido de BigQuery con bibliotecas de cliente. Para obtener más información, consulta la documentación de referencia de la API Java de BigQuery.

Para autenticarte en BigQuery, configura las credenciales predeterminadas de la aplicación. Para obtener más información, consulta el artículo Configurar la autenticación para bibliotecas de cliente. 

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 Node.jsinstrucciones de configuración de la guía de inicio rápido de BigQuery con bibliotecas de cliente. Para obtener más información, consulta la documentación de referencia de la API Node.js de BigQuery.

Para autenticarte en BigQuery, configura las credenciales predeterminadas de la aplicación. Para obtener más información, consulta el artículo Configurar la autenticación para bibliotecas de cliente. 

// 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 este ejemplo, sigue las Pythoninstrucciones de configuración de la guía de inicio rápido de BigQuery con bibliotecas de cliente. Para obtener más información, consulta la documentación de referencia de la API Python de BigQuery.

Para autenticarte en BigQuery, configura las credenciales predeterminadas de la aplicación. Para obtener más información, consulta el artículo Configurar la autenticación para bibliotecas de cliente. 

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)))

Consultar la pseudocolumna _FILE_NAME

Las tablas basadas en fuentes de datos externas proporcionan una pseudocolumna llamada _FILE_NAME. Esta columna contiene la ruta completa del archivo al que pertenece la fila. Esta columna solo está disponible en las tablas que hacen referencia a datos externos almacenados en Cloud Storage, Google Drive, Amazon S3 y Azure Blob Storage.

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, debe usar un alias. En la siguiente consulta de ejemplo se muestra cómo seleccionar _FILE_NAME asignando el alias fn a la pseudocolumna. 

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

Haz los cambios siguientes:

  • PROJECT_ID es un ID de proyecto válido (esta marca no es obligatoria si usas Cloud Shell o si has definido 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 pseudocolumna _FILE_NAME, BigQuery intenta omitir la lectura de los archivos que no cumplen el filtro. Las recomendaciones similares a las de consultar tablas con particiones por hora de ingestión mediante pseudocolumnas se aplican al crear predicados de consulta con la pseudocolumna _FILE_NAME.

Siguientes pasos