Cargar datos con operaciones entre nubes

Como administrador o analista de BigQuery, puedes cargar datos de un segmento de Amazon Simple Storage Service (Amazon S3) o de Azure Blob Storage en tablas de BigQuery. Puedes combinar los datos transferidos con los datos presentes en las regiones deGoogle Cloud o aprovechar las funciones de BigQuery, como BigQuery ML. También puedes crear réplicas de vistas materializadas de determinadas fuentes externas para que esos datos estén disponibles en BigQuery.

Puede transferir datos a BigQuery de las siguientes formas:

  • Transfiere datos de archivos de Amazon S3 y Azure Blob Storage a tablas de BigQuery mediante la instrucción LOAD DATA.

  • Filtra datos de archivos de Amazon S3 o Blob Storage antes de transferir los resultados a tablas de BigQuery mediante la instrucción CREATE TABLE AS SELECT. Para añadir datos a la tabla de destino, usa la declaración INSERT INTO SELECT. La manipulación de datos se aplica a las tablas externas que hacen referencia a datos de Amazon S3 o Blob Storage.

  • Crea réplicas de vistas materializadas de datos externos de Amazon S3, Apache Iceberg o Salesforce Data Cloud en un conjunto de datos de BigQuery para que los datos estén disponibles localmente en BigQuery.

Cuotas y límites

Para obtener información sobre las cuotas y los límites, consulta Cuotas y límites de las tareas de consulta.

Antes de empezar

Para proporcionar a Google Cloud el acceso de lectura necesario para cargar o filtrar datos en otras nubes, pide a tu administrador que cree una conexión y la comparta contigo. Para obtener información sobre cómo crear conexiones, consulta Conectarse a Amazon S3 o Blob Storage.

Rol necesario

Para obtener los permisos que necesitas para cargar datos mediante transferencias entre nubes, pide a tu administrador que te conceda el rol de gestión de identidades y accesos Editor de datos de BigQuery (roles/bigquery.dataEditor) en el conjunto de datos. Para obtener más información sobre cómo conceder roles, consulta el artículo Gestionar el acceso a proyectos, carpetas y organizaciones.

Este rol predefinido contiene los permisos necesarios para cargar datos mediante transferencias entre nubes. Para ver los permisos exactos que se necesitan, despliega la sección Permisos necesarios:

Permisos obligatorios

Para cargar datos mediante transferencias entre nubes, se necesitan los siguientes permisos:

  • bigquery.tables.create
  • bigquery.tables.get
  • bigquery.tables.updateData
  • bigquery.tables.update
  • bigquery.jobs.create
  • bigquery.connections.use

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

Para obtener más información sobre los roles de gestión de identidades y accesos en BigQuery, consulta el artículo sobre los roles y permisos de gestión de identidades y accesos de BigQuery.

Precios

Se te facturan los bytes que se transfieren entre nubes mediante la instrucción LOAD. Para obtener información sobre los precios, consulta la sección Transferencia de datos entre nubes de Omni en la página Precios de BigQuery Omni.

Se te facturan los bytes que se transfieren entre nubes mediante la instrucción CREATE TABLE AS SELECT o la instrucción INSERT INTO SELECT, así como la capacidad de computación.

Tanto las instrucciones LOAD como CREATE TABLE AS SELECT requieren slots en las regiones de BigQuery Omni para analizar los archivos de Amazon S3 y Blob Storage con el fin de cargarlos. Para obtener más información, consulta los precios de BigQuery Omni.

En el caso de las réplicas de vistas materializadas de fuentes de datos externas, los costes también pueden incluir los precios de las vistas materializadas.

Prácticas recomendadas para las opciones de carga y filtrado

Cargar datos

Puedes cargar datos en BigQuery con la sentencia LOAD DATA [INTO|OVERWRITE].

Limitaciones

  • La conexión y el conjunto de datos de destino deben pertenecer al mismo proyecto. No se admite la carga de datos entre proyectos.
  • LOAD DATA solo se admite cuando transfieres datos de Amazon Simple Storage Service (Amazon S3) o Azure Blob Storage a una región de BigQuery colocada. Para obtener más información, consulta Ubicaciones.
    • Puedes transferir datos de cualquier región US a una multirregión US. También puede transferir datos de cualquier región EU a una multirregión EU.

Ejemplo

Ejemplo 1

En el siguiente ejemplo se carga un archivo Parquet llamado sample.parquet de un segmento de Amazon S3 en la tabla test_parquet con un esquema de detección automática:

LOAD DATA INTO mydataset.testparquet
  FROM FILES (
    uris = ['s3://test-bucket/sample.parquet'],
    format = 'PARQUET'
  )
  WITH CONNECTION `aws-us-east-1.test-connection`

Ejemplo 2

En el siguiente ejemplo se carga un archivo CSV con el prefijo sampled* desde su almacenamiento de blobs en la tabla test_csv con particiones de columna predefinidas por tiempo:

LOAD DATA INTO mydataset.test_csv (Number INT64, Name STRING, Time DATE)
  PARTITION BY Time
  FROM FILES (
    format = 'CSV', uris = ['azure://test.blob.core.windows.net/container/sampled*'],
    skip_leading_rows=1
  )
  WITH CONNECTION `azure-eastus2.test-connection`

Ejemplo 3

En el siguiente ejemplo se sobrescribe la tabla test_parquet con datos de un archivo llamado sample.parquet con un esquema de detección automática:

LOAD DATA OVERWRITE mydataset.testparquet
  FROM FILES (
    uris = ['s3://test-bucket/sample.parquet'],
    format = 'PARQUET'
  )
  WITH CONNECTION `aws-us-east-1.test-connection`

Filtrar datos

Puede filtrar los datos antes de transferirlos a BigQuery mediante las instrucciones CREATE TABLE AS SELECT y INSERT INTO SELECT.

Limitaciones

  • Si el resultado de la consulta SELECT supera los 60 GiB en bytes lógicos, la consulta falla. La tabla no se crea y los datos no se transfieren. Para saber cómo reducir el tamaño de los datos que se analizan, consulta Reducir los datos procesados en las consultas.

  • No se admiten tablas temporales.

  • No se admite la transferencia del formato de datos geoespaciales Well-known binary (WKB).

  • La instrucción INSERT INTO SELECT no admite la transferencia de datos a una tabla agrupada.

  • En la instrucción INSERT INTO SELECT, si la tabla de destino es la misma que la tabla de origen de la consulta SELECT, la instrucción INSERT INTO SELECT no modifica ninguna fila de la tabla de destino. La tabla de destino no se modifica, ya que BigQuery no puede leer datos de diferentes regiones.

  • CREATE TABLE AS SELECT y INSERT INTO SELECT solo se admiten cuando transfieres datos de Amazon S3 o Blob Storage a una región de BigQuery colocada. Para obtener más información, consulta Ubicaciones.

    • Puedes transferir datos de cualquier región US a una multirregión US. También puede transferir datos de cualquier región EU a una multirregión EU.

Ejemplo

Ejemplo 1

Supongamos que tiene una tabla de BigLake llamada myawsdataset.orders que hace referencia a datos de Amazon S3. Quieres transferir datos de esa tabla a una tabla de BigQuery myotherdataset.shipments en la multirregión de EE. UU.

Primero, muestra información sobre la tabla myawsdataset.orders:

    bq show myawsdataset.orders;

El resultado debería ser similar al siguiente:

  Last modified             Schema              Type     Total URIs   Expiration
----------------- -------------------------- ---------- ------------ -----------
  31 Oct 17:40:28   |- l_orderkey: integer     EXTERNAL   1
                    |- l_partkey: integer
                    |- l_suppkey: integer
                    |- l_linenumber: integer
                    |- l_returnflag: string
                    |- l_linestatus: string
                    |- l_commitdate: date

A continuación, muestra información sobre la tabla myotherdataset.shipments:

  bq show myotherdataset.shipments

La salida es similar a la siguiente. Se han omitido algunas columnas para simplificar el resultado.

  Last modified             Schema             Total Rows   Total Bytes   Expiration   Time Partitioning   Clustered Fields   Total Logical
 ----------------- --------------------------- ------------ ------------- ------------ ------------------- ------------------ ---------------
  31 Oct 17:34:31   |- l_orderkey: integer      3086653      210767042                                                         210767042
                    |- l_partkey: integer
                    |- l_suppkey: integer
                    |- l_commitdate: date
                    |- l_shipdate: date
                    |- l_receiptdate: date
                    |- l_shipinstruct: string
                    |- l_shipmode: string

Ahora, con la instrucción CREATE TABLE AS SELECT, puedes cargar datos de forma selectiva en la tabla myotherdataset.orders de la multirregión de EE. UU.:

CREATE OR REPLACE TABLE
  myotherdataset.orders
  PARTITION BY DATE_TRUNC(l_commitdate, YEAR) AS
SELECT
  *
FROM
  myawsdataset.orders
WHERE
  EXTRACT(YEAR FROM l_commitdate) = 1992;

A continuación, puedes realizar una operación de combinación con la tabla recién creada:

SELECT
  orders.l_orderkey,
  orders.l_orderkey,
  orders.l_suppkey,
  orders.l_commitdate,
  orders.l_returnflag,
  shipments.l_shipmode,
  shipments.l_shipinstruct
FROM
  myotherdataset.shipments
JOIN
  `myotherdataset.orders` as orders
ON
  orders.l_orderkey = shipments.l_orderkey
AND orders.l_partkey = shipments.l_partkey
AND orders.l_suppkey = shipments.l_suppkey
WHERE orders.l_returnflag = 'R'; -- 'R' means refunded.

Cuando haya nuevos datos disponibles, añade los datos del año 1993 a la tabla de destino con la instrucción INSERT INTO SELECT:

INSERT INTO
   myotherdataset.orders
 SELECT
   *
 FROM
   myawsdataset.orders
 WHERE
   EXTRACT(YEAR FROM l_commitdate) = 1993;

Ejemplo 2

En el siguiente ejemplo se insertan datos en una tabla con particiones por hora de ingestión:

CREATE TABLE
 mydataset.orders(id String, numeric_id INT64)
PARTITION BY _PARTITIONDATE;

Después de crear una tabla con particiones, puede insertar datos en la tabla con particiones por hora de ingestión:

INSERT INTO
 mydataset.orders(
   _PARTITIONTIME,
   id,
   numeric_id)
SELECT
 TIMESTAMP("2023-01-01"),
 id,
 numeric_id,
FROM
 mydataset.ordersof23
WHERE
 numeric_id > 4000000;

Réplicas de vistas materializadas

Una réplica de vista materializada es una réplica de datos externos de Amazon Simple Storage Service (Amazon S3), Apache Iceberg o Salesforce Data Cloud en un conjunto de datos de BigQuery para que los datos estén disponibles localmente en BigQuery. De esta forma, puedes evitar los costes de salida de datos y mejorar el rendimiento de las consultas. BigQuery te permite crear vistas materializadas en tablas con la caché de metadatos de BigLake habilitada sobre datos de Amazon Simple Storage Service (Amazon S3), Apache Iceberg o Salesforce Data Cloud.

Una réplica de vista materializada te permite usar los datos de la vista materializada de Amazon S3, Iceberg o Data Cloud en las consultas, al tiempo que evitas los costes de salida de datos y mejoras el rendimiento de las consultas. Para ello, una réplica de vista materializada replica los datos de Amazon S3, Iceberg o Data Cloud en un conjunto de datos de una región de BigQuery admitida, de modo que los datos estén disponibles localmente en BigQuery.

Antes de empezar

  1. Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

  2. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

    Roles required to select or create a project

    • Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
    • Create a project: To create a project, you need the Project Creator (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

    Go to project selector

  3. Verify that billing is enabled for your Google Cloud project.

  4. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

    Roles required to select or create a project

    • Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
    • Create a project: To create a project, you need the Project Creator (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

    Go to project selector

  5. Verify that billing is enabled for your Google Cloud project.

  6. Asegúrate de que tienes los permisos de gestión de identidades y accesos (IAM) necesarios para realizar las tareas de esta sección.

    7. Roles obligatorios

    Para obtener los permisos que necesitas para realizar las tareas de esta sección, pide a tu administrador que te asigne el rol de gestión de identidades y accesos Administrador de BigQuery (roles/bigquery.admin). Para obtener más información sobre cómo conceder roles, consulta el artículo Gestionar el acceso a proyectos, carpetas y organizaciones.

    Este rol predefinido contiene los permisos necesarios para realizar las tareas de esta sección. Para ver los permisos exactos que se necesitan, despliega la sección Permisos necesarios:

    Permisos obligatorios

    Para realizar las tareas de esta sección, se necesitan los siguientes permisos:

    • bigquery.tables.create
    • bigquery.tables.get
    • bigquery.tables.getData
    • bigquery.tables.replicateData
    • bigquery.jobs.create

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

    Para obtener más información sobre la gestión de identidades y accesos de BigQuery, consulta el artículo sobre los roles y permisos de gestión de identidades y accesos de BigQuery.

    Preparar un conjunto de datos para réplicas de vistas materializadas

    Antes de crear una réplica de una vista materializada, debe completar las siguientes tareas:

    1. Crea un conjunto de datos en una región que admita Amazon S3.
    2. Crea una tabla de origen en el conjunto de datos que has creado en el paso anterior. La tabla de origen puede ser de cualquiera de los siguientes tipos:

    Crear réplicas de vistas materializadas

    Selecciona una de las opciones siguientes:

    Consola

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

      Ir a BigQuery

    2. En el panel Explorador, vaya al proyecto y al conjunto de datos en los que quiera crear la réplica de la vista materializada y, a continuación, haga clic en Ver acciones > Crear tabla.

    3. En la sección Fuente del cuadro de diálogo Crear tabla, haga lo siguiente:

      1. En Crear tabla a partir de, selecciona Tabla o vista ya creada.
      2. En Proyecto, introduce el proyecto en el que se encuentra la tabla o la vista de origen.
      3. En Conjunto de datos, introduce el conjunto de datos en el que se encuentra la tabla o la vista de origen.
      4. En Vista, introduce la tabla o la vista de origen que vas a replicar. Si eliges una vista, debe ser una vista autorizada. De lo contrario, todas las tablas que se usen para generar esa vista deben estar en el conjunto de datos de la vista.

    4. Opcional: En Tiempo máximo de obsolescencia de la vista materializada local, introduce un max_staleness valor para tu vista materializada local.

    5. En la sección Destino del cuadro de diálogo Crear tabla, haga lo siguiente:

      1. En Proyecto, introduce el proyecto en el que quieras crear la réplica de la vista materializada.
      2. En Conjunto de datos, introduzca el conjunto de datos en el que quiera crear la réplica de la vista materializada.
      3. En Replica materialized view name (Nombre de la vista materializada de réplica), escribe un nombre para la réplica.

    6. Opcional: Especifica etiquetas y opciones avanzadas para la réplica de la vista materializada. Si no especifica un conjunto de datos para Conjunto de datos de vista materializada local, se creará automáticamente uno en el mismo proyecto y región que los datos de origen, y se llamará bq_auto_generated_local_mv_dataset. Si no especificas un nombre para Nombre de vista materializada local, se creará automáticamente en el mismo proyecto y región que los datos de origen y se le asignará el prefijo bq_auto_generated_local_mv_.

    7. Haz clic en Crear tabla.

    Se crea una vista materializada local (si no se ha especificado) y se autoriza en el conjunto de datos de origen. A continuación, se crea la réplica de la vista materializada en el conjunto de datos de destino.

    SQL

    1. Crea una vista materializada en la tabla base del conjunto de datos que has creado. También puedes crear la vista materializada en un conjunto de datos diferente que se encuentre en una región de Amazon S3.
    2. Autoriza la vista materializada en los conjuntos de datos que contengan las tablas de origen usadas en la consulta que creó la vista materializada.
    3. Si has configurado la actualización manual de la caché de metadatos de la tabla de origen, ejecuta el procedimiento del sistema BQ.REFRESH_EXTERNAL_METADATA_CACHE para actualizar la caché de metadatos.
    4. Ejecuta el procedimiento del sistema BQ.REFRESH_MATERIALIZED_VIEW para actualizar la vista materializada.

    5. Para crear réplicas de vistas materializadas, usa la instrucción CREATE MATERIALIZED VIEW AS REPLICA OF:

      CREATE MATERIALIZED VIEW PROJECT_ID.BQ_DATASET.REPLICA_NAME
OPTIONS(replication_interval_seconds=REPLICATION_INTERVAL)
AS REPLICA OF PROJECT_ID.S3_DATASET.MATERIALIZED_VIEW_NAME;

      Haz los cambios siguientes:

      • PROJECT_ID: el nombre del proyecto en el que quieres crear la réplica de la vista materializada. Por ejemplo, myproject.
      • BQ_DATASET: el nombre del conjunto de datos de BigQuery en el que quieres crear la réplica de la vista materializada. Por ejemplo, bq_dataset. El conjunto de datos debe estar en la región de BigQuery que se corresponda con la región de la vista materializada de origen.
      • REPLICA_NAME: el nombre de la réplica de la vista materializada que quieras crear. Por ejemplo, my_mv_replica.
      • REPLICATION_INTERVAL: especifica la frecuencia con la que se replican los datos de la vista materializada de origen en la réplica, en segundos. Debe ser un valor comprendido entre 60 y 3600, ambos inclusive. El valor predeterminado es 300 (5 minutos).
      • S3_DATASET: nombre del conjunto de datos que contiene la vista materializada de origen. Por ejemplo, s3_dataset.
      • MATERIALIZED_VIEW_NAME: el nombre de la vista materializada que se va a replicar (por ejemplo, my_mv).

      En el siguiente ejemplo se crea una réplica de vista materializada llamada mv_replica en bq_dataset:

      CREATE MATERIALIZED VIEW `myproject.bq_dataset.mv_replica`
OPTIONS(
replication_interval_seconds=600
)
AS REPLICA OF `myproject.s3_dataset.my_s3_mv`

    Después de crear la réplica de la vista materializada, el proceso de replicación sondea la vista materializada de origen para detectar cambios y replica los datos en la réplica de la vista materializada, actualizando los datos en el intervalo que haya especificado en la opción replication_interval_seconds o max_staleness. Si consultas la réplica antes de que se complete el primer relleno, se produce un error backfill in progress. Puedes consultar los datos de la réplica de la vista materializada después de que se complete la primera replicación.

    Actualización de datos

    Después de crear la réplica de la vista materializada, el proceso de replicación sondea la vista materializada de origen para detectar cambios y replica los datos en la réplica de la vista materializada. Los datos se replican en el intervalo que hayas especificado en la opción replication_interval_seconds de la instrucción CREATE MATERIALIZED VIEW AS REPLICA OF.

    Además del intervalo de replicación, la actualización de los datos de la réplica de la vista materializada también depende de la frecuencia con la que se actualiza la vista materializada de origen y de la frecuencia con la que se actualiza la caché de metadatos de la tabla de Amazon S3, Iceberg o Data Cloud que usa la vista materializada.

    Para comprobar la actualización de los datos de la réplica de la vista materializada y de los recursos en los que se basa, puedes usar la Google Cloud consola:

    • Para comprobar la actualización de la réplica de la vista materializada, consulta el campo Última modificación del panel Detalles de la réplica de la vista materializada.
    • Para ver la actualización de una vista materializada de origen, consulta el campo Última modificación del panel Detalles de la vista materializada.
    • Para ver la actualización de la caché de metadatos de la tabla de origen de Amazon S3, Iceberg o Data Cloud, consulta el campo Máxima obsolescencia del panel Detalles de la vista materializada.

    Regiones de réplica de vistas materializadas admitidas

    Usa las asignaciones de ubicaciones de la siguiente tabla al crear réplicas de vistas materializadas:

    Ubicación de la vista materializada de origen Ubicación de la réplica de la vista materializada
    aws-us-east-1 US multirregión o cualquiera de las siguientes regiones:
    • northamerica-northeast1
    • northamerica-northeast2
    • us-central1
    • us-east1
    • us-east4
    • us-east5
    • us-south1
    • us-west1
    • us-west2
    • us-west3
    • us-west4
    aws-us-west-2 US multirregión o cualquiera de las siguientes regiones:
    • northamerica-northeast1
    • northamerica-northeast2
    • us-central1
    • us-east1
    • us-east4
    • us-east5
    • us-south1
    • us-west1
    • us-west2
    • us-west3
    • us-west4
    aws-eu-west-1 EU multirregión o cualquiera de las siguientes regiones:
    • europe-central2
    • europe-north1
    • europe-southwest1
    • europe-west1
    • europe-west2
    • europe-west3
    • europe-west4
    • europe-west6
    • europe-west8
    • europe-west9
    • europe-west10
    aws-ap-northeast-2 Cualquiera de las siguientes regiones:
    • asia-east1
    • asia-east2
    • asia-northeast1
    • asia-northeast2
    • asia-northeast3
    • asia-south1
    • asia-south2
    • asia-southeast1
    aws-ap-southeast-2 Cualquiera de las siguientes regiones:
    • australia-southeast1
    • australia-southeast2

    Limitaciones de las réplicas de vistas materializadas

    Precios de las réplicas de vistas materializadas

    El uso de réplicas de vistas materializadas conlleva costes de computación, transferencia de datos saliente y almacenamiento.

    Siguientes pasos