google bigquery - Migración de tablas no particionadas a particionadas

google-bigquery

Question

En junio, el equipo de BQ anunció la compatibilidad con las tablas con particiones de fecha . Pero falta la guía sobre cómo migrar tablas antiguas no particionadas al nuevo estilo.

Estoy buscando una manera de actualizar varias o si no todas las tablas al nuevo estilo.

También fuera del tipo DIA particionado, ¿qué otras opciones están disponibles? ¿La interfaz de usuario de BQ muestra esto, ya que no pude crear una tabla particionada tan nueva desde la interfaz de usuario web de BQ?

Answer 1

de la respuesta de Pavan: tenga en cuenta que este enfoque le cobrará el costo de escaneo de la tabla de origen para la consulta tantas veces como la consulte.

de los comentarios de Pentium10: así que supongamos que tengo varios años de datos, necesito preparar una consulta diferente para cada día y ejecutarlos, y supongo que tengo 1000 días en la historia, necesito pagar 1000 veces el precio de consulta completo desde la fuente ¿mesa?

Como podemos ver, el principal problema aquí es tener un escaneo completo para cada día. El resto es un problema menor y se puede programar fácilmente en cualquier cliente de su elección.

Entonces, a continuación es: ¿Cómo particionar la tabla mientras se evita el escaneo completo de la tabla todos los días?

A continuación, paso a paso, se muestra el enfoque.

Es lo suficientemente genérico para extender / aplicar a cualquier caso de uso real; mientras tanto, estoy usando bigquery-public-data.noaa_gsod.gsod2017 y estoy limitando el "ejercicio" a solo 10 días para que sea legible

Paso 1 - Crear tabla dinámica
En este paso nosotros
a) comprimir el contenido de cada fila en registro / matriz
y
b) ponerlos a todos en la respectiva columna "diaria"

#standardSQL
SELECT
  ARRAY_CONCAT_AGG(CASE WHEN d = ''day20170101'' THEN r END) AS day20170101,
  ARRAY_CONCAT_AGG(CASE WHEN d = ''day20170102'' THEN r END) AS day20170102,
  ARRAY_CONCAT_AGG(CASE WHEN d = ''day20170103'' THEN r END) AS day20170103,
  ARRAY_CONCAT_AGG(CASE WHEN d = ''day20170104'' THEN r END) AS day20170104,
  ARRAY_CONCAT_AGG(CASE WHEN d = ''day20170105'' THEN r END) AS day20170105,
  ARRAY_CONCAT_AGG(CASE WHEN d = ''day20170106'' THEN r END) AS day20170106,
  ARRAY_CONCAT_AGG(CASE WHEN d = ''day20170107'' THEN r END) AS day20170107,
  ARRAY_CONCAT_AGG(CASE WHEN d = ''day20170108'' THEN r END) AS day20170108,
  ARRAY_CONCAT_AGG(CASE WHEN d = ''day20170109'' THEN r END) AS day20170109,
  ARRAY_CONCAT_AGG(CASE WHEN d = ''day20170110'' THEN r END) AS day20170110
FROM (
  SELECT d, r, ROW_NUMBER() OVER(PARTITION BY d) AS line
  FROM (
    SELECT 
      stn, CONCAT(''day'', year, mo, da) AS d, ARRAY_AGG(t) AS r
    FROM `bigquery-public-data.noaa_gsod.gsod2017` AS t 
    GROUP BY stn, d
  ) 
)
GROUP BY line  

Ejecute la consulta anterior en la interfaz de usuario web con pivot_table (o el nombre que prefiera) como destino

Como podemos ver, aquí obtendremos una tabla con 10 columnas, una columna para un día y el esquema de cada columna es una copia del esquema de la tabla original:

Paso 2 - Procesando particiones una por una SOLAMENTE escaneando la columna respectiva (sin escaneo completo de la tabla) - insertando en la partición respectiva

#standardSQL
SELECT r.*
FROM pivot_table, UNNEST(day20170101) AS r

Ejecute la consulta anterior desde la interfaz de usuario web con la tabla de destino denominada mytable $ 20160101

Puedes correr lo mismo para el día siguiente

#standardSQL
SELECT r.*
FROM pivot_table, UNNEST(day20170102) AS r

Ahora debe tener la tabla de destino como mytable $ 20160102, etc.

Debería poder automatizar / escribir este paso con cualquier cliente de su elección

Hay muchas variaciones de cómo puede usar el enfoque anterior: depende de su creatividad

Nota: BigQuery permite hasta 10000 columnas en la tabla, por lo que 365 columnas para los días respectivos de un año definitivamente no es un problema aquí: o) A menos que haya una limitación sobre qué tan atrás puede ir con nuevas particiones, escuché (pero no todavía no tengo la oportunidad de comprobarlo) ahora no hay más de 90 días atrás

Actualizar

Tenga en cuenta: la versión anterior tiene una pequeña lógica adicional de empaquetar todas las celdas agregadas en el menor número final de filas posible.

ROW_NUMBER() OVER(PARTITION BY d) AS line
y entonces
GROUP BY line
junto con
ARRAY_CONCAT_AGG(…)
Haz esto

Esto funciona bien cuando el tamaño de la fila en su tabla original no es tan grande, por lo que el tamaño de la fila combinada final todavía estará dentro del límite de tamaño de las filas que tiene BigQuery (que creo que es de 10 MB a partir de ahora)

Si su tabla de origen ya tiene un tamaño de fila cercano a ese límite, use la versión ajustada a continuación

En esta versión, la agrupación se elimina de modo que cada fila solo tenga valor para una columna

#standardSQL
SELECT
    CASE WHEN d = ''day20170101'' THEN r END AS day20170101,
    CASE WHEN d = ''day20170102'' THEN r END AS day20170102,
    CASE WHEN d = ''day20170103'' THEN r END AS day20170103,
    CASE WHEN d = ''day20170104'' THEN r END AS day20170104,
    CASE WHEN d = ''day20170105'' THEN r END AS day20170105,
    CASE WHEN d = ''day20170106'' THEN r END AS day20170106,
    CASE WHEN d = ''day20170107'' THEN r END AS day20170107,
    CASE WHEN d = ''day20170108'' THEN r END AS day20170108,
    CASE WHEN d = ''day20170109'' THEN r END AS day20170109,
    CASE WHEN d = ''day20170110'' THEN r END AS day20170110
FROM (
    SELECT 
        stn, CONCAT(''day'', year, mo, da) AS d, ARRAY_AGG(t) AS r
    FROM `bigquery-public-data.noaa_gsod.gsod2017` AS t 
    GROUP BY stn, d
)
WHERE d BETWEEN ''day20170101'' AND ''day20170110''

Como puede ver ahora, la tabla dinámica (sparce_pivot_table) es lo suficientemente escasa (mismo 21.5 MB pero ahora 114,089 filas frente a 11,584 filas en pivot_table), por lo que tiene un tamaño promedio de fila de 190B frente a 1.9KB en la versión inicial. Obviamente, es aproximadamente 10 veces menos según el número de columnas en el ejemplo.
Por lo tanto, antes de usar este enfoque, se deben hacer algunas matemáticas para proyectar / estimar qué y cómo se puede hacer.

Aún así: cada celda en la tabla dinámica es una especie de representación JSON de toda la fila en la tabla original. Es tal que contiene no solo valores como lo era para las filas en la tabla original, sino que también tiene un esquema

Como tal, es bastante detallado, por lo tanto, el tamaño de la celda puede ser varias veces mayor que el tamaño original [lo que limita el uso de este enfoque ... a menos que sea aún más creativo: o) ... que todavía tiene muchas áreas aquí aplicar: o)]

Answer 2

A partir de hoy, ahora puede crear una tabla particionada a partir de una tabla no particionada al consultarla y especificar la columna de partición. Pagará por un escaneo completo de la tabla en la tabla original (no particionada). Nota : esto está actualmente en beta.

https://cloud.google.com/bigquery/docs/creating-column-partitions#creating_a_partitioned_table_from_a_query_result

Para crear una tabla particionada a partir de un resultado de consulta, escriba los resultados en una nueva tabla de destino. Puede crear una tabla particionada consultando una tabla particionada o una tabla no particionada. No puede cambiar una tabla estándar existente a una tabla particionada utilizando los resultados de la consulta.

Answer 3

Hasta que la nueva característica se implemente en BigQuery, hay otra forma (mucho más barata) de particionar las tablas mediante Cloud Dataflow . Utilizamos este enfoque en lugar de ejecutar cientos de declaraciones SELECT * , lo que nos habría costado miles de dólares.

1. Cree la tabla particionada en BigQuery usando el comando de partition normal
2. Cree una tubería de flujo de datos y use un receptor BigQuery.IO.Read para leer la tabla
3. Use una transformación de partición para dividir cada fila
4. Usando un máximo de 200 fragmentos / sumideros a la vez (más que eso y alcanza los límites de la API), cree un sumidero BigQuery.IO.Write para cada día / fragmento que escribirá en la partición correspondiente utilizando la sintaxis del decorador de partición - "$YYYYMMDD"
5. Repita N veces hasta que se procesen todos los datos.

Aquí hay un ejemplo en Github para comenzar.

Todavía tiene que pagar por la (s) tubería (s) de Dataflow, pero es una fracción del costo del uso de múltiples SELECT * en BigQuery.

Answer 4

Lo que funciona para mí es el siguiente conjunto de consultas aplicadas directamente en la consulta grande (la consulta grande crea una nueva consulta).

CREATE TABLE (new?)dataset.new_table PARTITION BY DATE(date_column) AS SELECT * FROM dataset.table_to_copy;

Luego, como siguiente paso, dejo caer la tabla:

DROP TABLE dataset.table_to_copy;

Obtuve esta solución de https://fivetran.com/docs/warehouses/bigquery/partition-table usando solo el paso 2

Answer 5

Si tiene tablas fragmentadas hoy, puede usar este enfoque:

https://cloud.google.com/bigquery/docs/creating-partitioned-tables#converting_dated_tables_into_a_partitioned_table

Si tiene una sola tabla no particionada que se convertirá en una tabla particionada, puede intentar el enfoque de ejecutar una consulta SELECT * con permitir resultados grandes y usar la partición de la tabla como destino (similar a cómo reformularía los datos para un dividir):

https://cloud.google.com/bigquery/docs/creating-partitioned-tables#restating_data_in_a_partition

Tenga en cuenta que este enfoque le cobrará el costo de exploración de la tabla de origen para la consulta tantas veces como lo consulte.

Estamos trabajando en algo para mejorar significativamente este escenario en los próximos meses.