Estructura de datos de BigQuery en Google: cómo comenzar con el almacenamiento en la nube

Google BigQuery es un servicio de almacenamiento en la nube que le permite recopilar todos sus datos en un solo sistema y analizarlos fácilmente mediante consultas SQL. Para que los datos sean convenientes para trabajar, deben estar estructurados correctamente. En este artículo, explicaremos cómo crear tablas y conjuntos de datos para cargarlos en Google BigQuery.

Para usar Google BigQuery, debe crear un proyecto en Google Cloud Platform (GCP). Al registrarse, recibirá acceso a todos los productos de Cloud Platform durante un período de prueba gratuito y $300 para gastar en estos productos dentro de los próximos 12 meses.

Después de crear un proyecto en Google Cloud Platform, debe agregar al menos un conjunto de datos a Google BigQuery.

Un conjunto de datos es un contenedor de nivel superior que se utiliza para organizar y controlar el acceso a sus datos. En términos simples, es una especie de carpeta en la que se almacena su información en forma de tablas y vistas.

Abre tu proyecto en GCP, ve a la pestaña BigQuery y haz clic en Crear conjunto de datos :

En la ventana que se abre, especifique un nombre para el conjunto de datos y la vida útil de una tabla. Si desea que las tablas con datos se eliminen automáticamente, especifique cuándo exactamente. O deje la opción Perpetua predeterminada para que las tablas solo se puedan eliminar manualmente.

El campo Sitio de procesamiento es opcional. De forma predeterminada, está configurado para varias regiones de EE. UU. Puede encontrar más información sobre las regiones para almacenar datos en la sección de ayuda.

Después de crear un conjunto de datos, debe agregar una tabla en la que se recopilarán los datos. Una tabla es un conjunto de filas. Cada fila consta de columnas, que también se denominan campos. Hay varias formas de crear una tabla en BigQuery según la fuente de datos:

• Cree manualmente una tabla vacía y configure un esquema de datos para ella
• Crear una tabla usando el resultado de una consulta SQL previamente calculada
• Cargue un archivo desde su computadora (en formato CSV, AVRO, JSON, Parquet, ORC o Google Sheets)
• En lugar de descargar o transmitir datos, puede crear una tabla que haga referencia a una fuente externa: Cloud Bigtable, Cloud Storage o Google Drive.

En este artículo, veremos más de cerca el primer método: crear una tabla manualmente.

Paso 1 Seleccione el conjunto de datos al que desea agregar la tabla, luego haga clic en Crear tabla :

Paso 2. En el campo Origen, seleccione Tabla vacía y, en el campo Tipo de tabla , seleccione Tabla en el formato nativo del objeto de destino. Inventa un nombre para la mesa.

Importante : los nombres de conjuntos de datos, tablas y campos deben estar en caracteres latinos y contener solo letras, números y guiones bajos.

Paso 3 Especifique el esquema de la tabla. El esquema consta de cuatro componentes: dos obligatorios (nombre de columna y tipo de datos) y dos opcionales (modo de columna y descripción). Los tipos y modos de campo correctamente seleccionados facilitarán el trabajo con los datos.

Esquema de ejemplo en BigQuery:

Nombres de columna
En el nombre de la columna, debe especificar el parámetro del que es responsable cada columna: fecha, ID de usuario, productos, etc. Los títulos pueden contener solo letras latinas, números y guiones bajos (máximo 128 caracteres). No se permiten nombres de campo idénticos, incluso si su caso es diferente.

tipo de datos
Al crear una tabla en BigQuery, puede usar los siguientes tipos de campo:

Modos
BigQuery admite los siguientes modos para las columnas de la tabla:

Nota : No es necesario completar el campo Modo. Si no se especifica ningún modo, la columna predeterminada es NULLABLE.

Descripciones de columna
Si lo desea, puede agregar una breve descripción (no más de 1024 caracteres) para cada columna de la tabla para explicar qué significa un parámetro en particular.

Cuando crea una tabla vacía en BigQuery, debe configurar el esquema manualmente. Esto se puede hacer de dos formas:
1. Haga clic en el botón Agregar campo y complete el nombre, el tipo y el modo para cada columna.

2. Ingrese el esquema de la tabla como una matriz JSON usando el modificador Editar como texto .

Además, Google BigQuery puede usar la detección automática de esquemas al cargar datos de archivos CSV y JSON.

Esta opción funciona según el siguiente principio: BigQuery selecciona un archivo aleatorio de la fuente que especifiques, escanea hasta 100 filas de datos en él y usa los resultados como una muestra representativa. Luego verifica cada campo en el archivo cargado e intenta asignarle un tipo de datos basado en los valores de la muestra.

Al cargar archivos de Google, BigQuery puede cambiar el nombre de una columna para que sea compatible con su propia sintaxis SQL. Por lo tanto, le recomendamos que cargue tablas con nombres de campo en inglés; si los nombres están en ruso, por ejemplo, el sistema los renombrará automáticamente. Por ejemplo:

Si, al cargar datos, los nombres de las columnas se ingresaron incorrectamente o desea cambiar los nombres y tipos de las columnas en una tabla existente, puede hacerlo manualmente. Te contamos cómo.

Después de cargar datos en Google BigQuery, el diseño de la tabla puede ser ligeramente diferente al original. Por ejemplo, el nombre de un campo puede haber cambiado debido a un carácter que no es compatible con BigQuery, o el tipo de campo puede ser INTEGER en lugar de STRING. En este caso, puede ajustar manualmente el esquema.

Mediante una consulta SQL, seleccione todas las columnas de la tabla y especifique un nuevo nombre para la columna que desea cambiar de nombre. En este caso, puede sobrescribir la tabla existente o crear una nueva. Solicitar ejemplos:

Usando una consulta SQL, seleccione todos los datos de una tabla y convierta la columna correspondiente a un tipo de datos diferente. Puede usar los resultados de la consulta para sobrescribir una tabla existente o crear una nueva. Ejemplo de solicitud:

Puede cambiar el modo de columna de REQUERIDO a NULLABLE como se describe en la documentación de ayuda. La segunda opción es exportar los datos a Cloud Storage y desde allí devolverlos a BigQuery con el modo correcto para todas las columnas.

Use la consulta SELECT * EXCEPT para excluir una columna (o columnas), luego escriba los resultados de la consulta en la tabla anterior o cree una nueva. Ejemplo de solicitud:

Además, hay una segunda forma de cambiar el esquema que es adecuada para todas las tareas descritas anteriormente: exportar datos y cargarlos en una nueva tabla. Para cambiar el nombre de una columna, puede cargar datos de BigQuery a Cloud Storage, luego exportarlos de Cloud Storage a BigQuery en una tabla nueva o sobrescribir los datos en la tabla anterior usando los Parámetros avanzados:

Puede leer sobre otras formas de cambiar la estructura de la tabla en la documentación de ayuda de Google Cloud Platform.

Puede descargar y cargar datos de BigQuery sin la ayuda de los desarrolladores a través de la interfaz o un complemento especial de OWOX BI. Consideremos cada método en detalle.

Para cargar la información necesaria para el almacenamiento, por ejemplo, datos sobre usuarios y pedidos fuera de línea, abra su conjunto de datos, haga clic en Crear tabla y seleccione la fuente de datos: Cloud Storage, su computadora, Google Drive o Cloud Bigtable. Especifique la ruta al archivo, su formato y el nombre de la tabla donde se cargarán los datos:

Después de hacer clic en Crear tabla, aparecerá una tabla en su conjunto de datos.

También es posible cargar datos procesados ​​desde BigQuery, por ejemplo, para crear un informe a través de la interfaz del sistema. Para hacer esto, abra la tabla deseada con datos y haga clic en el botón Exportar:

El sistema ofrecerá dos opciones: ver los datos en Google Data Studio o subirlos a Google Cloud Storage. Si selecciona la primera opción, irá inmediatamente a Data Studio, donde podrá guardar el informe.

Si elige exportar a Google Cloud Storage, se abrirá una nueva ventana. En él, debe especificar dónde guardar los datos y en qué formato.

El complemento gratuito OWOX BI BigQuery Reports le permite transferir datos de manera rápida y conveniente directamente desde Google BigQuery a Google Sheets y viceversa. Por lo tanto, no necesita preparar archivos CSV ni utilizar servicios de terceros de pago.

Por ejemplo, supongamos que desea cargar datos de pedidos sin conexión a BigQuery para crear un informe de ROPO. Para esto, necesitarás:

1. Instale el complemento BigQuery Reports en su navegador.
2. Abra su archivo de datos en Hojas de cálculo de Google y en la pestaña Complementos , seleccione OWOX BI BigQuery Reports → Cargar datos en BigQuery.
3. En la ventana que se abre, seleccione su proyecto y conjunto de datos en BigQuery e ingrese el nombre deseado para la tabla. Además, seleccione los campos cuyos valores desea cargar. De forma predeterminada, el tipo de todos los campos es STRING, pero recomendamos elegir el tipo de datos según el contexto (por ejemplo, para campos con identificadores numéricos, elija INTEGER; para precios, FLOAT):
   

4. Haga clic en el botón Iniciar carga y sus datos se cargarán en Google BigQuery

También puede usar este complemento para exportar datos de BigQuery a Hojas de cálculo de Google; por ejemplo, para visualizar datos o compartirlos con colegas que no tienen acceso a BigQuery. Para esto:

1. Abre Hojas de cálculo de Google. En la pestaña Complementos , seleccione OWOX BI BigQuery Reports → Agregar un nuevo informe:
   

2. Luego ingrese su proyecto en Google BigQuery y seleccione Agregar nueva consulta.
3. En la nueva ventana, inserte su consulta SQL. Esta podría ser una consulta que cargue datos de una tabla a BigQuery o una consulta que extraiga y calcule los datos necesarios.
4. Cambie el nombre de la consulta para que sea más fácil encontrarla e iniciarla haciendo clic en el botón Guardar y ejecutar.
   

Para cargar datos de BigQuery a Hojas de cálculo de Google de manera regular, puede habilitar las actualizaciones de datos programadas:

1. En la pestaña Complementos , seleccione OWOX BI BigQuery Reports → Programar informe :
   

2. En la ventana que se abre, establezca la hora y la frecuencia de las actualizaciones del informe y haga clic en Guardar :
   

Si aún no ha apreciado los beneficios del almacenamiento en la nube de Google BigQuery, le recomendamos que lo pruebe. Con la ayuda de OWOX BI, puede combinar datos de su sitio web, fuentes de publicidad y sistemas CRM internos en BigQuery para:

• Configure análisis de extremo a extremo y descubra el rendimiento real de su marketing, teniendo en cuenta los pedidos fuera de línea, las devoluciones y todos los pasos del usuario en el camino hacia una compra;
• Cree informes sobre datos completos sin muestrear con cualquier parámetro e indicador;
• Evaluar los canales de adquisición de clientes mediante el análisis de cohortes;
• Descubra cómo su publicidad en línea afecta las ventas fuera de línea;
• Reduzca la parte de los costos de publicidad, extienda el ciclo de vida de los clientes y aumente el LTV de su base de clientes en su conjunto;
• Segmenta a los clientes en función de su actividad y personaliza la comunicación con ellos.

OWOX BI tiene un período de prueba gratuito durante el cual puede probar todas las funciones del servicio.