Carga datos con operaciones entre nubes

Como administrador o analista de BigQuery, puedes cargar datos desde un bucket de Amazon Simple Storage Service (Amazon S3) o Azure Blob Storage en tablas de BigQuery. Puedes unir los datos transferidos con los de las regiones de Google Cloud o aprovechar las funciones de BigQuery, como BigQuery ML.

Puedes transferir datos a BigQuery de las siguientes maneras:

Cuotas y límites

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

Precios

Se te factura por los bytes que se transfieren entre nubes mediante la declaración LOAD. Para obtener más información, consulta la sección Transferencia de datos entre nubes de Omnis en Precios de BigQuery Omni.

Se te factura por los bytes que se transfieren entre nubes mediante la declaración CREATE TABLE AS SELECT o la declaración INSERT INTO SELECT y por capacidad de procesamiento.

Las declaraciones LOAD y CREATE TABLE AS SELECT requieren ranuras en las regiones de BigQuery Omni a fin de analizar los archivos de Amazon S3 y Blob Storage para cargarlos. Si quieres obtener más información, consulta los precios de BigQuery Omni.

Antes de comenzar

Para proporcionar a Google Cloud acceso de lectura a los archivos en otras nubes, pídele al administrador que cree una conexión y la comparta contigo. Para obtener información sobre cómo crear conexiones, consulta Conéctate a Amazon S3 o Blob Storage.

Función requerida

Para obtener los permisos que necesitas para cargar datos con transferencias entre nubes, pídele a tu administrador que te otorgue el rol de IAM de editor de datos de BigQuery (roles/bigquery.dataEditor) en el conjunto de datos. Para obtener más información sobre cómo otorgar roles, consulta Administra el acceso.

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

Permisos necesarios

Se requieren los siguientes permisos para cargar datos mediante transferencias entre nubes:

  • 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 o con otros roles predefinidos.

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

Carga de datos

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

Limitaciones

  • No se admite la carga de datos en una tabla de destino con partición de tiempo de transferencia.
  • Los trabajos LOAD DATA no se ejecutan en las reservas. Los trabajos usan ranuras a pedido administradas por Google Cloud.
  • 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 es compatible cuando transfieres datos desde un Amazon Simple Storage Service (Amazon S3) o Azure Blob Storage a una región de BigQuery ubicada en el mismo lugar. Para obtener más información, consulta Ubicaciones.
    • Puedes transferir datos desde cualquier región US a una multirregión US. También puedes transferir desde cualquier región EU a una multirregión EU.

Ejemplo

Ejemplo 1

En el siguiente ejemplo, se carga un archivo de Parquet llamado sample.parquet de un bucket 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* de Blob Storage en la tabla test_csv con partición de columnas predefinida 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 reemplaza la tabla existente test_parquet por los 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`

Filtra datos

Puedes filtrar datos antes de transferirlos a BigQuery mediante la declaración CREATE TABLE AS SELECT y la declaración 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 aprender a reducir el tamaño de los datos que se analizan, consulta Reduce los datos procesados en las consultas.

  • Las tablas temporales no son compatibles.

  • No se admite la transferencia del formato de datos geoespaciales binarios conocidos (WKB).

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

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

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

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

Ejemplo

Ejemplo 1

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

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

    bq show myawsdataset.orders;

El resultado es similar a este:

  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

El resultado es similar al siguiente. Algunas columnas se omiten 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 declaración CREATE TABLE AS SELECT, puedes cargar datos de forma selectiva a la tabla myotherdataset.orders en 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;

Luego, puedes realizar una operación de unió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 datos nuevos disponibles, agrega los datos del año 1993 a la tabla de destino mediante la declaració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 particionada por tiempo de transferencia:

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

Después de crear una tabla particionada, puedes insertar datos en la tabla particionada por tiempo de transferencia:

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

Prácticas recomendadas

  • Evita cargar varios archivos de menos de 5 MB. En su lugar, crea una tabla externa para tu archivo y exporta el resultado de la consulta a Amazon S3 o Blob Storage a fin de crear un archivo más grande. Este método ayuda a mejorar el tiempo de transferencia de tus datos.
    • Si deseas obtener información sobre el límite de los resultados de consultas, consulta Tamaño máximo de los resultados de consultas de BigQuery Omni.
  • Si tus datos de origen están en un archivo comprimido en gzip, establece las opciones external_table_options.compression en GZIP mientras creas tablas externas.

¿Qué sigue?