Consulta datos de Amazon S3

En este documento, se describe cómo consultar datos almacenados en una tabla de BigLake de Amazon Simple Storage Service (Amazon S3).

Antes de comenzar

Asegúrate de tener una tabla de Amazon S3 BigLake.

Roles obligatorios

Para consultar tablas de BigLake de Amazon S3, asegúrate de que el emisor de la API de BigQuery tenga los siguientes roles:

  • Usuario de conexión de BigQuery (roles/bigquery.connectionUser)
  • Visualizador de datos de BigQuery (roles/bigquery.dataViewer)
  • BigQuery User (roles/bigquery.user)

El emisor puede ser tu cuenta o una cuenta de servicio de conexión de Amazon S3. 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 que son necesarios para consultar las tablas de BigLake de Amazon S3, expande la sección Permisos necesarios:

Permisos necesarios

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

Consulta tablas de BigLake de Amazon S3

Después de crear una tabla de BigLake de Amazon S3, puedes consultarla con la sintaxis de Google SQL, de la misma manera que si fuese una tabla de BigQuery estándar.

Los resultados de las consultas en caché se almacenan en una tabla temporal de BigQuery. Para consultar una tabla de BigLake temporal, consulta Consulta una tabla de BigLake temporal. Para obtener más información sobre las limitaciones y cuotas de BigQuery Omni, consulta las limitaciones y cuotas.

Cuando crees una reserva en una región de BigQuery Omni, usa la edición Enterprise. Para aprender a crear una reserva con una edición, consulta Crea reservas.

Ejecuta una consulta en una tabla de BigLake de Amazon S3:

SQL

Para consultar la tabla, sigue estos pasos:

  1. En la consola de Google Cloud, ve a la página de BigQuery.

    Ir a BigQuery

  2. En el editor de consultas, escribe la siguiente oración:

    SELECT * FROM DATASET_NAME.TABLE_NAME;

    Reemplaza lo siguiente:

    • DATASET_NAME: Es el nombre del conjunto de datos que creaste.
    • TABLE_NAME: el nombre de la tabla que creaste.

    • Haz clic en Ejecutar.

Para obtener más información sobre cómo ejecutar consultas, visita Ejecuta una consulta interactiva.

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.DatasetId;
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 aws s3 using a permanent table
public class QueryExternalTableAws {

  public static void main(String[] args) throws InterruptedException {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    String datasetName = "MY_DATASET_NAME";
    String externalTableName = "MY_EXTERNAL_TABLE_NAME";
    // Query to find states starting with 'W'
    String query =
        String.format(
            "SELECT * FROM s%.%s.%s WHERE name LIKE 'W%%'",
            projectId, datasetName, externalTableName);
    queryExternalTableAws(query);
  }

  public static void queryExternalTableAws(String query) throws InterruptedException {
    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();

      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 aws external permanent table performed successfully.");
    } catch (BigQueryException e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

Consulta una tabla temporal

BigQuery crea tablas temporales para almacenar los resultados de las consultas. Para recuperar el resultado de la consulta de tablas temporales, puedes usar la consola de Google Cloud o la API de BigQuery.

Selecciona una de las opciones siguientes:

Console

Cuando consultas una tabla de BigLake que hace referencia a datos de la nube externas, puedes ver los resultados de la consulta que se muestran en la consola de Google Cloud.

API

Para consultar una tabla de BigLake con la API, sigue estos pasos:

  1. Crea un objeto de trabajo.
  2. 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.
  3. Para leer filas con el jobs.getQueryResults, pasa la referencia del trabajo determinado y los métodos tabledata.list, pasando la referencia de la tabla determinada del resultado de la consulta.

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?