fechas - mysql to bigquery

Alternativa a BigQuery para datos de tamaño mediano

Question

Este es un seguimiento de la pregunta ¿Por qué BigQuery no se desempeña tan bien en pequeños conjuntos de datos ?

Supongamos que tengo un conjunto de datos que es ~ 1M filas. En la base de datos actual que estamos usando (mysql) las consultas de agregación se ejecutarán bastante lentas, tal vez tomando ~ 10s o más en agregaciones complejas. En BigQuery, el tiempo de inicialización requerido puede hacer que esta consulta tarde unos 3 segundos, mejor que en mysql, pero la herramienta incorrecta para el trabajo, si necesitamos devolver las consultas en 1 o menos.

Entonces, mi pregunta es: ¿cuál sería una buena alternativa al uso de BigQuery para hacer consultas agregadas en conjuntos de datos de tamaño moderado, como las filas de 1-10M? Una consulta de ejemplo podría ser:

SELECT studio, territory, count(*)
FROM mytable
GROUP BY studio, territory
ORDER BY count(*) DESC

Las posibles soluciones que he pensado son ElasticSearch ( https://github.com/NLPchina/elasticsearch-sql ) y Redshift (postgres es muy lento). ¿Cuál sería una buena opción aquí que se puede consultar a través de SQL?

Nota: No estoy buscando por qué o cómo se debe usar BQ, estoy buscando una alternativa para los conjuntos de datos en filas de 10M donde la consulta se puede devolver en menos de 1 s.

Answer 1

Aquí hay algunas alternativas a considerar para datos de este tamaño:

1. Nodo SSD pequeño Redshift pequeño
   • Sin configuración Fácilmente devuelve respuestas en esta cantidad de datos en 1s.
2. Greenplum en una pequeña instancia de T2
   • Postgres-como Similar perf a Redshift. No pagar por el almacenamiento que no necesitará. Comience con su nodo único "sandbox" AMI.
3. MariaDB Columnstore
   • MySQL-como. Antes se llamaba InfiniDB. Muy buen rendimiento. Con el apoyo de MariaDB (la empresa).
4. Taladro apache
   • Drill tiene una filosofía muy similar a BiqQuery pero puede usarse para cualquier lugar (es solo un frasco). Las consultas serán rápidas en este tamaño de datos.

Si la administración baja / inicio rápido es crítico, vaya con Redshift. Si el dinero / flexibilidad es crítico comienza con Drill. Si prefieres MySQL comienza con MariaDB Columnstore.

Answer 2

BigQuery está destinado a rendir mejor al final del flujo de Big Data. Ha sido diseñado para funcionar bien con grandes conjuntos de datos, no pequeños, y no pretende ser un reemplazo de las tecnologías existentes, sino un complemento excelente en ciertas situaciones. Se puede leer un ejemplo en el documento "Google Cloud Big Data y Machine Learning Blog".

Answer 3

Creo que Microsoft SQL Server Analysis Services es una buena opción, yo lo uso, es una base de datos detrás del servicio PowerBI, que tiene una muy buena opción de nivel gratuito.

Si desea una solución local gratuita, siempre puede usar SQL Server Express con la nueva tecnología de almacén de columnas, no la usé yo mismo, pero escuché algunos resultados muy buenos.

Answer 4

Mi respuesta: optimice la estructura de consultas y tablas como se abordó anteriormente (1 segundo o menos). Lea a continuación para obtener más información, porque todos caemos en esta trampa. Nota: Lo anterior no es necesariamente un gran conjunto de datos.

Una gran pregunta. Es tan difícil descifrar cuál es el problema y qué es una solución. Aquí hay una foto de la vieja escuela. En los viejos tiempos, solemos decir que le pregunta a un hardware, sistema operativo o desarrollador cuál es el problema / solución y obtendrá tres respuestas diferentes.

Mi entendimiento es que esta pregunta es para resolver / comparar un problema de rendimiento de SQL con una solución de infraestructura en la nube. Esta pregunta tendrá muchas respuestas diferentes basadas en el fondo. Es confuso, solo tiene instalaciones de bases de datos de la vieja escuela (Mysql, Oracle, MSsql), Base de datos como servicio (DBAAS), soluciones de Big Data Cloud, soluciones de aplicaciones de Big Data (hadoop)

Es tan fácil enredarse con toda esta tecnología. Tal vez aquí hay un poco de claridad.

Los problemas de rendimiento de SQL se pueden resolver en una variedad de puntos de rendimiento (POP).

1. Optimización y optimización de SQL (tablas temporales, en memoria, funciones OLAP, plan de SQL, paralelización, análisis) Herramientas (MySql Workbench, cmdline, Toad, etc.)
2. Optimización de la estructura (tablas, indexación, partición, estructuras pre-AG)
3. Configuración de la base de datos (tamaño de memoria, tamaños de caché, paralelismo, tamaño de bloque, etc.
4. Memoria OS, tamaño de página, procesos)
5. Hardware y red - en su mayoría irrellivant ahora.
6. Aprovisionamiento del servidor.
7. Cloud provisioning y clustering.
8. Infraestructura y decisiones de software.

Conclusión: me detendré aquí, tenemos muchas soluciones para los problemas. Intente comenzar con el uso más básico de una tecnología antes de incurrir en costos para resolver soluciones con tecnologías más grandes. Con suerte, esto le dará al usuario un esqueleto de un camino para trabajar o una terminología para usar cuando haga una pregunta. ¿Cómo puedo obtener x consulta para ejecutar en el tiempo t?

Answer 5

No hablas mucho sobre el espacio problemático en el que estás, pero ¿has considerado pandas python o R? Estas son excelentes herramientas para el análisis / desarrollo de datos.

Asumiendo que tienes a mano Python y pandas, pip install pandas , puedes comenzar con algo como esto:

import pandas as pd
import pyodbc

conn = pyodbc.connect(...) # You''ll need to figure out the settings for your DB here
# this slow but only needs to be done once:
data = pd.read_sql_query(''select * from mytable'') # Load everything into memory 

# Now do the query:
data.groupby([''studio'', ''territory'']).count().sort_values(ascending=False)

Recomiendo probar pandas con Jupyter Notebooks

Answer 6

No utilice COUNT(*) .

Use COUNT() en una sola columna, preferiblemente una indexada como la PRIMARY KEY .

Answer 7

Sé SQL Server, por lo que mi respuesta es parcial.

1. Las filas de 10M deben caber fácilmente en la memoria, por lo que cualquier tipo de agregación debe ser rápida, especialmente si tiene un índice de cobertura. Si no es así, la configuración del servidor puede necesitar ajustes. Además, SQL Server tiene las llamadas tablas en memoria , que pueden ser una buena opción aquí.

2. SQL Server tiene una característica llamada vista indexada . Su consulta de agregación es un caso de uso clásico de una vista indexada. La vista indizada es esencialmente una copia de los datos almacenados en el disco y mantenidos por el servidor automáticamente a medida que cambian los datos subyacentes en la tabla. Disminuye los INSERTOS, ELIMINACIÓN y ACTUALIZACIONES, pero hace que SELECT sea rápido, porque el resumen siempre se calcula previamente. Vea: Lo que puede (y no puede) hacer con vistas indizadas . Otros DBMS deben tener características similares.

Answer 8

Si esa es tu única consulta, entonces esto hará que se ejecute más rápido:

INDEX(studio, territory)  -- in either order.

Si hay otras variaciones, veamoslas, más SHOW CREATE TABLE .

Otra cosa para verificar: ¿Cuánta RAM tiene, y cuál es el valor de innodb_buffer_pool_size ? Esa configuración debe ser aproximadamente el 70% de la RAM (si tiene más de 4 GB de RAM).

Answer 9

Si está buscando los resultados de la consulta OLAP en un segundo lugar, Druid ( http://druid.io/ ) se creó para ese propósito. Es una bestia implementar y ajustar, pero una vez que se configura correctamente para sus datos, es muy rápido. Tiene soporte de transmisión por lo que puede ingerir de Kafka con una semántica exactamente única, lo que es increíble. Escala muy bien de pequeñas a grandes cantidades de datos, aunque tendrá que pagar un costo al igual que lo hace la agregación previa, por lo que si tiene muchas dimensiones, el tamaño de los datos explotará. El soporte de SQL se ha agregado recientemente y está incompleto. Además, no admite uniones, por lo que tiene que estructurar sus datos correctamente para obtener sus respuestas.

Answer 10

Si necesita respuestas en menos de un segundo, debe pensar en la indexación.

Historia tipica

1. MySQL (o cualquier otra base de datos propuesta aquí) es rápida, hasta que ...
2. Un día, algunas de las consultas de agregación comienzan a ejecutarse lentamente. Minutos, horas, días, etc.
3. La solución típica para el paso 2 es la indexación y la agregación previa. Si desea respuestas en menos de un segundo para cierto tipo de preguntas, deberá invertir tiempo y ciclos de optimización para responder a ese tipo de preguntas.
4. La belleza de BigQuery es que puede omitir el paso 3. Lleve esos minutos / horas / días a segundos, con una inversión mínima: cualquier consulta, en cualquier momento.

BigQuery es impresionante porque le da 4. Pero está pidiendo 3, MySQL está bien para eso, Elasticsearch también está bien, cualquier base de datos indexada le dará resultados en menos de un segundo, siempre y cuando invierta tiempo en optimizar su sistema. para cierto tipo de pregunta. Luego, para obtener respuestas a cualquier pregunta arbitraria sin invertir tiempo de optimización, use BigQuery.

BigQuery: responderá preguntas arbitrarias en segundos, no se necesita preparación.

MySQL y alternativas: responderán cierto tipo de preguntas en menos de un segundo, pero llevará tiempo de desarrollo llegar allí.

Answer 11

Si no necesita concurrencia, varios usuarios se conectan simultáneamente y sus datos pueden caber en un solo archivo de disco, entonces SQLite podría ser apropiado.

Como dicen, SQLite no compite con las bases de datos cliente / servidor. SQLite compite con fopen ().

http://www.sqlite.org/whentouse.html