Consulta datos de Cloud Storage en tablas externas

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

Antes de comenzar

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:

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

Según los permisos que tengas, puedes otorgarte estos roles o pedirle a tu administrador que te los otorgue. Para obtener más información sobre cómo otorgar roles, consulta Visualiza los roles que se pueden otorgar en los recursos.

A fin de ver los permisos exactos de BigQuery que se requieren para consultar tablas externas, expande la sección Permisos necesarios:

Permisos necesarios

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

Consulta tablas externas permanentes

Después de crear una tabla externa de Cloud Storage, puedes consultarla con la sintaxis de Google SQL, de la misma manera que si fuera una tabla de BigQuery estándar. Un ejemplo es SELECT field1, field2 FROM mydataset.my_cloud_storage_table;.

Consulta tablas externas temporales

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

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

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.

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.

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:

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

Para hacer una consulta en una tabla temporal vinculada a tu fuente de datos externa por medio de un archivo de definición de tablas, escribe el siguiente comando:

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

Reemplaza lo siguiente:

  • LOCATION: 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 en 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 hacer una consulta en una tabla temporal vinculada a tu fuente de datos externa por medio de una definición de esquema intercalado, escribe el comando siguiente.

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

Reemplaza lo siguiente:

  • LOCATION: 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.

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

    Puedes elegir varios archivos del bucket si especificas un carácter comodín de asterisco (*) en la ruta de acceso file_pattern. Un ejemplo es gs://mybucket/file00*.parquet. Si deseas obtener más información, consulta Compatibilidad de comodines para los URI de Cloud Storage.

    Puedes especificar varios buckets para la opción uris si proporcionas varias rutas de acceso.

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

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

    Cuando especificas valores uris que se orientan a varios archivos, todos esos archivos deben compartir un esquema compatible.

    Para obtener más información sobre cómo usar los URI de Cloud Storage en BigQuery, consulta Ruta de acceso al recurso 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 hacer una consulta en una tabla temporal vinculada a tu fuente de datos externa por medio de un archivo de esquema JSON, escribe el siguiente comando:

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

Reemplaza lo siguiente:

  • LOCATION: 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: 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.

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

    Puedes elegir varios archivos del bucket si especificas un carácter comodín de asterisco (*) en la ruta de acceso file_pattern. Un ejemplo es gs://mybucket/file00*.parquet. Si deseas obtener más información, consulta Compatibilidad de comodines para los URI de Cloud Storage.

    Puedes especificar varios buckets para la opción uris si proporcionas varias rutas de acceso.

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

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

    Cuando especificas valores uris que se orientan a varios archivos, todos esos archivos deben compartir un esquema compatible.

    Para obtener más información sobre cómo usar los URI de Cloud Storage en BigQuery, consulta Ruta de acceso al recurso 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

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

  1. Crea un objeto Job.
  2. Propaga la sección configuration del objeto Job con un objeto JobConfiguration.
  3. Propaga la sección query del objeto JobConfiguration con un objeto JobConfigurationQuery.
  4. Propaga 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 ejecutar la consulta de forma síncrona y pasar el objeto Job.

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.

Para autenticarte en BigQuery, configura las credenciales predeterminadas de la aplicación. Si deseas obtener más información, consulta Configura la autenticación para bibliotecas 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 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 BigQuery para Node.js.

Para autenticarte en BigQuery, configura las credenciales predeterminadas de la aplicación. Si deseas obtener más información, consulta Configura la autenticación para bibliotecas 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 instrucciones de configuración para Python 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 Python.

Para autenticarte en BigQuery, configura las credenciales predeterminadas de la aplicación. Si deseas obtener más información, consulta Configura la autenticación para bibliotecas 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)))

Consulta la 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, 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, 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"'

Reemplaza lo siguiente:

  • 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.

¿Qué sigue?