Bases de datos operativas frente a analíticas: almacenamiento de columnas frente a filas

Las bases de datos se pueden implementar usando Excel, GSheet o usando sistemas ORM grandes. En mi pr√°ctica de an√°lisis de negocios, encontr√© diferentes soluciones. Y desde que llegu√© al an√°lisis de negocios de finanzas y auditor√≠a, cada vez que conoc√≠ un nuevo sistema, me hac√≠a preguntas: ¬Ņen qu√© se diferencian entre s√≠ y qu√© tareas resuelven? Encontr√© algunas respuestas. Este art√≠culo cubrir√° dos prop√≥sitos principales de las bases de datos:


1 - contabilidad de operaciones,
2 - an√°lisis de datos


El primer tipo de tareas lo resuelven los sistemas OLTP: desde el procesamiento de transacciones en línea. El segundo tipo lo resuelven los sistemas OLAP: desde el procesamiento analítico on line


OLTP


El modelo de almacenamiento OLTP se puede comparar con las entradas de la guía telefónica. La fila de la tabla se presenta como un índice y el índice de datos correspondiente: (indexN, data). Por lo tanto, dicha tabla no puede llamarse tabla. Es más bien un libro ordinario, con líneas numeradas. Si necesita escribir una nueva operación en el libro, agregue una línea, asigne un índice y cierre el libro. Etiquetas que sobresalen del libro que le permiten rápidamente O (log n), encontrar la línea deseada y hacer CRUD.


Para fines de contabilidad de operaciones, esta es una presentaci√≥n amigable. Pero no es hostil al an√°lisis de datos, en el que no estamos interesados ‚Äč‚Äčen las l√≠neas en s√≠, sino en los c√°lculos basados ‚Äč‚Äčen el contenido de estas l√≠neas. Y si realiza una consulta anal√≠tica basada en el contenido de las filas, es decir para campos no indexados, estas consultas funcionar√°n m√°s lentamente.


La indexaci√≥n de todos los registros, como sabes, no es una opci√≥n. Aunque el libro se convierte en una tabla, a medida que los atributos est√°n disponibles para la b√ļsqueda r√°pida, tambi√©n ralentiza la creaci√≥n de filas nuevas y actualizadas. Debido a que estas operaciones requerir√°n volver a ordenar toda la matriz.


La compensación entre OLAP y OLTP


En las soluciones 1C, se implementa un compromiso de la siguiente manera. Los eventos al escribir en la base de datos se escriben en varios lugares a la vez. En un lugar, los registros tienen pocos índices y están optimizados para cargas OLTP; en otro lugar, todos los campos indexan los registros y se adaptan para cargas OLAP. Dichas tablas se denominan registros de acumulación y registros de información. Dado que escribir en varios lugares aumenta el espacio ocupado varias veces, para guardar no todos los atributos de transacción caen en los registros, sino solo aquellos que se consideran importantes para esta sección de contabilidad analítica. Tal compromiso se llama modelo ROLAP, es decir mapeo analítico relacional.


OLAP


En SAP, la contraparte alemana 1C fue más allá. El modelo OLTP relacional en este software se puede replicar al modelo OLAP. SAP HANA implementa una estructura de columnas de almacenamiento. Esto significa que las "tablas" se almacenan allí no como un conjunto de filas, sino como un conjunto de columnas.


Se implementa un esquema de almacenamiento similar en soluciones como Google Bigquery, Microsoft SSAS Tabular, Amazon Redshift, Yandex ClickHouse.


La diferencia entre el almacenamiento de columnas y el almacenamiento de filas


Si en una estructura en filas, los datos se almacenan en forma de tuplas "horizontales", cada una de las cuales es una transacción:


period, product, department (Q1, SKU1, 1) (Q1, SKU2, 1) (Q1, SKU1, 1) ... (Q2, SKU1, 1) (Q2, SKU1, 1) (Q3, SKU1, 1) (Q3, SKU1, 1) ... 

Luego, en la columna, dichos datos se almacenan "verticalmente":


 (Q1, Q1, Q1, ... Q2, Q2, Q3, Q3, ...) (SKU1, SKU2, SKU1, ... SKU1, SKU1, SKU1, SKU1, ...) (1,1,1, ... 1,1,1,1, ...) 

Las repeticiones se pueden optimizar condicionalmente de la siguiente manera:


 period = (Q1, {start: 0, count: n}, Q2, {start: n+1; count: m}, ...) product = (SKU1, {start: 0, count: 1}, SKU2, {start: 1; count: 1}, SKU1, {start: 2; count: m}, ...) department = (1,{start:0, count:m}...) 

Si hay una columna para la cual dicha optimización no reducirá el volumen inicial, entonces los datos se almacenan en su forma original.


El propio motor de tabla de columnas selecciona la secuencia de clasificación de columnas, pero si conoce sus datos y los ordena manualmente, esto a menudo aumenta la compresión y facilita las cargas de trabajo analíticas. Mi compresión de tablas individuales superó las 300 veces. En la práctica, dicha estructura de almacenamiento de datos:


  1. le permite comprimir datos al nivel cuando se colocan en la RAM, es decir Poner a disposición cálculos en memoria que no son comparables en velocidad con consultas a bases de datos relacionales
  2. establece sus propias reglas para construir un modelo de datos, ya no requiere tal normalización como en OLTP
  3. define su semántica para construir expresiones analíticas.

Los detalles de las expresiones se describen en detalle:
aquí está para Google BigQuery.
aquí es para Microsoft DAX.


BI como infraestructura base de columna


BI es una solución que sirve a la carga analítica. Y hacen la vida mucho más fácil si se construyen sobre bases de datos de columna. Esto puede ser un grupo casero de ClickHouse-Grafana-Python o un paquete de pila de Google: Bigquery-Data Studio-Dataprep-Dataflow o monolítico Power BI.


Los cubos multidimensionales son otra alternativa OLAP al almacenamiento de columnas. Pero para mí, las expresiones MDX, en comparación con SQL en BQ o DAX, son redundantes y complejas.

Source: https://habr.com/ru/post/442754/


All Articles