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 información sobre los precios, consulta Precios de transferencia de datos.

No se te cobrará por los bytes transferidos mediante la declaración CREATE TABLE AS SELECT (en vista previa) o la declaración INSERT INTO SELECT (en vista previa). Sin embargo, se te cobrará por la 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.

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.

Rol requerido

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.

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

  • Las declaraciones CREATE TABLE y CREATE TABLE IF NOT EXISTS no son compatibles, pero se admite la declaración CREATE OR REPLACE TABLE.

  • Si el resultado de la consulta SELECT supera los 20 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.

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:

SELECT
  table_name, ddl
FROM
  `myproject`.myawsdataset.INFORMATION_SCHEMA.TABLES
WHERE
  table_name = '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:

SELECT
  table_name, ddl
FROM
  `myproject`.myotherdataset.INFORMATION_SCHEMA.TABLES
WHERE
  table_name = '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?