{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Cuando el archivo ya no entra\n", "\n", "Consultar con SQL un archivo más grande que tu memoria, sin montar nada. Y qué hace Spark cuando ni eso alcanza.\n", "\n", "Cuaderno de soluciones del capítulo 19 de **SQL desde cero**, de Miss Yera.\n", "\n", "Corre de arriba abajo. Si lo abres en Google Colab no necesitas instalar nada.\n", "\n", "Capítulo completo: https://missyera.com/guias/sql-desde-cero/consultar-archivos-grandes/\n", "\n", "Este es el cuaderno de **soluciones**. Trae el código de cada ejercicio, la\n", "explicación de la trampa y la respuesta del quiz. Si vienes del cuaderno de\n", "práctica sin haberlo intentado, vuelve 🙂" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Antes de empezar\n", "\n", "Se baja la base y se deja lista una función `q()` que corre las consultas." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "import sqlite3\n", "import urllib.request\n", "\n", "import pandas as pd\n", "\n", "urllib.request.urlretrieve(\"https://missyera.com/static/datasets/tienda.db\", \"tienda.db\")\n", "con = sqlite3.connect(\"tienda.db\")\n", "\n", "def q(sql):\n", " \"\"\"Corre las sentencias del bloque y devuelve la ultima como tabla.\n", "\n", " Parte por sentencias igual que la consola de SQLite, porque un bloque puede\n", " traer varias y un CREATE TRIGGER lleva punto y coma dentro de su cuerpo.\n", " \"\"\"\n", " resultado = None\n", " trozo = \"\"\n", " for linea in sql.splitlines(keepends=True):\n", " trozo += linea\n", " if sqlite3.complete_statement(trozo):\n", " if trozo.strip():\n", " cur = con.execute(trozo.strip())\n", " resultado = (pd.DataFrame(cur.fetchall(),\n", " columns=[d[0] for d in cur.description])\n", " if cur.description else None)\n", " trozo = \"\"\n", " if trozo.strip():\n", " cur = con.execute(trozo.strip())\n", " resultado = (pd.DataFrame(cur.fetchall(),\n", " columns=[d[0] for d in cur.description])\n", " if cur.description else None)\n", " con.commit()\n", " return resultado\n", "\n", "q(\"SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "En el capítulo 18 medimos dónde está el listón y llegamos a que\n", "casi nadie lo cruza. Este es la otra mitad: **y si lo cruzas, qué**.\n", "\n", "Porque el capítulo anterior nombraba Spark y Hadoop y no te enseñaba a correr\n", "ninguno, que es como decirte que existe el mar 🌊" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## El problema, en una línea" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Las herramientas que cargan el archivo entero en memoria antes de hacer nada\n", "tienen un techo: el de la máquina. Y ese techo llega antes de lo que parece,\n", "porque **un archivo ocupa varias veces más en memoria que en disco**.\n", "\n", "La culpa la tienen sobre todo las columnas de texto. Míralo con SQL:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "import os\n", "import tempfile\n", "import duckdb\n", "\n", "con = duckdb.connect()\n", "print(con.sql(\"\"\"\n", "SELECT count(*) AS filas,\n", " sum(length(ciudad) + length(segmento) + length(canal)\n", " + length(categoria)) AS letras_de_texto\n", "FROM 'ventas-miss-yera.csv'\n", "\"\"\"))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "92.401 letras en cuatro columnas. En el archivo cada una es un byte. Cargadas\n", "en memoria por una herramienta de análisis, cada texto pasa a ser un objeto con\n", "su propia cabecera, y ahí es donde se multiplica.\n", "\n", "Ese multiplicador está medido en el capítulo de pandas de mi libro de Python.\n", "Acá lo que importa es la consecuencia: con un archivo de 5 GB en disco puedes\n", "necesitar una máquina de 30 para abrirlo, y con SQL no." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## DuckDB: SQL contra el archivo, sin cargarlo" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "print(con.sql(\"\"\"\n", "SELECT canal, count(*) AS ventas, round(avg(compro), 3) AS conversion\n", "FROM 'ventas-miss-yera.csv'\n", "GROUP BY canal ORDER BY ventas DESC\n", "\"\"\"))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Mira el `FROM 'ventas-miss-yera.csv'`. **El archivo es la\n", "tabla.** No hubo que crear una base, ni importar, ni definir columnas.\n", "\n", "Y el SQL es el mismo que llevas quince capítulos escribiendo: el\n", "`GROUP BY` del capítulo 6 funciona igual sobre un\n", "archivo suelto.\n", "\n", "DuckDB se instala con `pip install duckdb` y ya está. Sin\n", "servidor, sin Java, sin configuración. Es a las bases analíticas lo que SQLite es\n", "a las transaccionales." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Ahora uno grande de verdad" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Voy a fabricar un archivo de casi un millón de filas repitiendo el mío:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "carpeta = tempfile.mkdtemp()\n", "grande = os.path.join(carpeta, 'grande.parquet')\n", "\n", "con.sql(\"COPY (SELECT unidades, monto, canal, ciudad, i AS repeticion \"\n", " \"FROM 'ventas-miss-yera.csv', range(1, 300) t(i)) \"\n", " \"TO '\" + grande + \"' (FORMAT parquet)\")\n", "\n", "print('filas:', con.sql(\"SELECT count(*) FROM '\" + grande + \"'\").fetchone()[0])\n", "print('pesa :', round(os.path.getsize(grande) / 1024 / 1024, 2), 'MB')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "908.063 filas en medio mega. Ese es el segundo tema del capítulo." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Parquet, y por qué pesa cincuenta veces menos" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "csv_grande = os.path.join(carpeta, 'grande.csv')\n", "con.sql(\"COPY (SELECT * FROM '\" + grande + \"') TO '\" + csv_grande\n", " + \"' (FORMAT csv, HEADER)\")\n", "\n", "print('parquet:', round(os.path.getsize(grande) / 1024 / 1024, 2), 'MB')\n", "print('csv :', round(os.path.getsize(csv_grande) / 1024 / 1024, 2), 'MB')\n", "print('veces mas chico:', round(os.path.getsize(csv_grande)\n", " / os.path.getsize(grande), 1))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "**Cincuenta veces.** Los mismos datos.\n", "\n", "La razón es que un CSV guarda por filas y Parquet guarda\n", "**por columnas**. Y una columna entera se parece mucho a sí misma:\n", "si la ciudad \"Arequipa\" se repite 164.450 veces, Parquet la escribe una vez y\n", "anota cuántas. Un CSV la escribe 164.450 veces.\n", "\n", "Y trae una segunda ventaja que importa todavía más: si tu consulta pide dos\n", "columnas de catorce, Parquet **lee solo esas dos del disco**. Un\n", "CSV hay que recorrerlo entero para llegar a la columna del final.\n", "\n", "Si te llevas una sola cosa práctica de este capítulo, que sea esta: cuando un\n", "CSV te empiece a molestar, conviértelo a Parquet antes de comprar más\n", "memoria 🧾" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Consultarlo sin cargarlo" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "print(con.sql(\"SELECT ciudad, count(*) AS filas FROM '\" + grande\n", " + \"' GROUP BY ciudad ORDER BY 2 DESC LIMIT 5\"))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Casi un millón de filas agrupadas, y en ningún momento el archivo entero\n", "estuvo en memoria. DuckDB leyó la columna que necesitaba, por trozos, y fue\n", "sumando.\n", "\n", "Eso se llama procesamiento **por streaming**, y es la diferencia\n", "de fondo con pandas: pandas quiere el archivo antes de empezar, y esto empieza y\n", "va leyendo." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Entonces, ¿dónde entra Spark?" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Acá es donde quiero ser clara, porque es lo que el capítulo anterior te debía.\n", "\n", "DuckDB corre en **una máquina**. Aprovecha todos sus núcleos y\n", "lee del disco por trozos, y por eso aguanta archivos de decenas o cientos de\n", "gigas en una laptop decente.\n", "\n", "**Spark reparte el trabajo entre muchas máquinas.** Parte el\n", "archivo, manda un pedazo a cada una, cada una calcula lo suyo y después se\n", "juntan los resultados. Esa es toda la idea, y viene de un artículo de Google de\n", "2004 que se llamaba MapReduce, que es lo que Hadoop implementó primero." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "| | DuckDB | Spark |\n", "|---|---|---|\n", "| Dónde corre | una máquina | varias |\n", "| Instalar | un pip | un clúster, o pagarlo |\n", "| El techo | lo que aguante esa máquina | se le añaden máquinas |\n", "| Cuándo | casi siempre | cuando una máquina grande ya no alcanza |" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Y la parte incómoda: **Spark tiene un coste fijo**. Repartir,\n", "mandar y volver a juntar tarda, y con archivos que caben en una máquina esa\n", "gestión pesa más que el cálculo. Es normal ver a Spark perder contra DuckDB en\n", "datos de unos pocos gigas.\n", "\n", "El SQL, eso sí, es casi el mismo. Si sabes escribir esta consulta, sabes\n", "escribirla en Spark 🐣" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## El error que sale al primer intento" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "**Esto revienta a propósito.** Se ejecuta dentro de un `try` para que puedas seguir con \"ejecutar todo\" y aun así ver la queja." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "try:\n", " con.sql(\"SELECT segmento FROM '\" + grande + \"' LIMIT 1\")\n", "except Exception as e:\n", " print(f'{type(e).__name__}: {e}')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Y la queja que tiene que salir es esta:\n", "\n", "```\n", "BinderException: Binder Error: Referenced column \"segmento\" not found in FROM clause!\n", "Candidate bindings: \"monto\", \"repeticion\"\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Cuando escribí el Parquet me llevé solo cinco columnas, así que el segmento no\n", "está ahí. Y fíjate en la segunda línea: DuckDB te sugiere las que más se parecen\n", "a lo que escribiste, que es de las cosas más cómodas que tiene.\n", "\n", "Y esto es un aviso de algo más grande: al convertir a Parquet uno decide qué\n", "columnas viajan, y esa decisión se olvida. Tres meses después alguien pide un\n", "corte por segmento y hay que rehacer la conversión desde el archivo original,\n", "que ojalá alguien haya guardado." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Lo que te llevas" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "- pandas carga el archivo entero; un CSV ocupa varias veces más en memoria que\n", "en disco.\n", "\n", "- DuckDB consulta el archivo directamente con SQL, sin importar nada.\n", "\n", "- Parquet guarda por columnas y suele pesar decenas de veces menos.\n", "\n", "- Parquet lee solo las columnas que pides.\n", "\n", "- DuckDB usa una máquina; Spark reparte entre varias.\n", "\n", "- Spark tiene un coste fijo de repartir, y con pocos gigas suele perder.\n", "\n", "- El SQL es casi el mismo en los tres sitios." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Consultar un archivo suelto sin importarlo\n", "\n", "| PostgreSQL | `CREATE FOREIGN TABLE ... SERVER csv_srv; -- necesita file_fdw` |\n", "|---|---|\n", "| MySQL | `LOAD DATA INFILE 'ventas.csv' INTO TABLE ventas; -- hay que importar` |\n", "| SQL Server | `SELECT * FROM OPENROWSET(BULK 'ventas.csv', FORMAT='CSV') AS t;` |\n", "| SQLite | `.import ventas.csv ventas -- desde la consola, tambien importa` |\n", "\n", "Ninguno de los cuatro hace lo de DuckDB de tratar el archivo como tabla sin mas. SQL Server es el que mas se acerca, y PostgreSQL lo consigue con una extension que hay que instalar aparte." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Comprueba que lo tienes\n", "\n", "Tienes un archivo de 40 GB y una laptop con 16 GB de RAM. ¿Qué pasa si lo abres con pandas?\n", "\n", "a) Se queda sin memoria, porque pandas carga el archivo entero antes de hacer nada\n", "\n", "b) Funciona, porque pandas lee por partes automáticamente\n", "\n", "c) Funciona pero lento, porque usa el disco como memoria\n", "\n", "d) Depende del formato del archivo\n", "\n", "---\n", "\n", "**La correcta es la a.**\n", "\n", "*b)* Lee por partes solo si tú se lo pides con chunksize y escribes el bucle. Un read_csv a secas carga todo.\n", "\n", "*c)* Eso lo hace el sistema operativo con la memoria virtual y es tan lento que en la práctica equivale a que no funcione.\n", "\n", "*d)* El formato cambia cuánto ocupa y cuánto tarda en leerse, y no cambia que pandas quiera cargarlo entero.\n", "\n", "La pregunta no es si tus datos son grandes, es si caben en la memoria de la máquina que tienes 🧾" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Ejercicios" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 1. Parquet lee solo lo que pides\n", "\n", "Compara pedir una columna contra pedirlas todas." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "una = con.sql(\"SELECT count(DISTINCT ciudad) FROM '\" + grande + \"'\").fetchone()\n", "todas = con.sql(\"SELECT count(*) FROM (SELECT * FROM '\" + grande + \"')\").fetchone()\n", "print('ciudades distintas:', una[0])\n", "print('filas totales :', todas[0])" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```\n", "ciudades distintas: 9\n", "filas totales : 908063\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Las dos consultas dan un número y una de ellas tocó una sola columna. Con\n", "catorce columnas y varios gigas, esa diferencia es de minutos. Es la misma idea\n", "de los índices del capítulo 14: leer menos es más rápido que\n", "leer rápido." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 2. El mismo SQL de siempre, sobre el archivo\n", "\n", "Comprueba que no hay que aprender otro lenguaje." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "print(con.sql(\"SELECT ciudad, round(avg(TRY_CAST(monto AS DOUBLE)), 2) AS ticket, \"\n", " \"count(*) AS n FROM '\" + grande + \"' WHERE canal = 'WhatsApp' \"\n", " \"GROUP BY ciudad HAVING count(*) > 20000 ORDER BY ticket DESC\"))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```\n", "┌──────────┬────────┬───────┐\n", "│ ciudad │ ticket │ n │\n", "│ varchar │ double │ int64 │\n", "├──────────┼────────┼───────┤\n", "│ Chiclayo │ 892.98 │ 34385 │\n", "│ Piura │ 805.25 │ 38571 │\n", "│ Arequipa │ 761.19 │ 39767 │\n", "│ Trujillo │ 726.09 │ 33189 │\n", "│ Cusco │ 684.33 │ 33488 │\n", "└──────────┴────────┴───────┘\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "WHERE, GROUP BY, HAVING y ORDER BY, todo del capítulo 6, sobre\n", "un archivo de novecientas mil filas que nunca se cargó. No hay sintaxis nueva\n", "que aprender: lo que cambia es dónde vive el dato.\n", "\n", "Fíjate en el `TRY_CAST`, que no está de adorno. La columna\n", "`monto` llegó como texto porque el archivo trae suciedad, y sin el\n", "cast DuckDB se niega a promediarla, con un error que lista todas las versiones\n", "de `avg` que sí existen. Es el mismo `errors='coerce'` de\n", "pandas y el mismo problema del capítulo 4: cambiar de\n", "herramienta no limpia tus datos 🧾" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 3. Cuánto de tu archivo es texto\n", "\n", "El texto es lo que se multiplica al cargarlo, así que\n", "conviene saber cuánto tienes." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "print(con.sql(\"\"\"\n", "SELECT\n", " sum(length(ciudad) + length(segmento) + length(canal)\n", " + length(categoria)) AS bytes_de_texto,\n", " sum(length(CAST(unidades AS VARCHAR))\n", " + length(CAST(satisfaccion AS VARCHAR))) AS bytes_de_numeros\n", "FROM 'ventas-miss-yera.csv'\n", "\"\"\"))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```\n", "┌────────────────┬──────────────────┐\n", "│ bytes_de_texto │ bytes_de_numeros │\n", "│ int128 │ int128 │\n", "├────────────────┼──────────────────┤\n", "│ 92401 │ 7368 │\n", "└────────────────┴──────────────────┘\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Doce veces más texto que números. Y el texto es justo lo que peor se\n", "guarda: un número entero cabe en ocho bytes pase lo que pase, y \"Marketplace\"\n", "son once letras cada vez que aparece.\n", "\n", "Por eso Parquet gana tanto en archivos de negocio: están llenos de columnas\n", "de texto que se repiten. Si tu archivo fuera todo números, la diferencia sería\n", "mucho menor." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 4. Convierte tu CSV a Parquet y mide\n", "\n", "Dos líneas que suelen ahorrar una compra de hardware." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "chico = os.path.join(carpeta, 'chico.parquet')\n", "con.sql(\"COPY (SELECT * FROM 'ventas-miss-yera.csv') TO '\" + chico\n", " + \"' (FORMAT parquet)\")\n", "print('csv :', os.path.getsize('ventas-miss-yera.csv'), 'bytes')\n", "print('parquet:', os.path.getsize(chico), 'bytes')\n", "print('ahorro :', round((1 - os.path.getsize(chico)\n", " / os.path.getsize('ventas-miss-yera.csv')) * 100, 1), '%')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```\n", "csv : 278857 bytes\n", "parquet: 88752 bytes\n", "ahorro : 68.2 %\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Un 68% con un archivo chiquito y con muchas columnas distintas. En el archivo\n", "grande de arriba, donde todo se repite, el ahorro fue del 98%. La compresión de\n", "Parquet trabaja mejor cuanto más se repiten los valores de una columna, que es\n", "justo lo que pasa en los archivos de verdad." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 5. Lo que se perdió al convertir\n", "\n", "Comprueba qué columnas viajaron y cuáles no." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "del_grande = [f[0] for f in con.sql(\n", " \"DESCRIBE SELECT * FROM '\" + grande + \"'\").fetchall()]\n", "del_csv = [f[0] for f in con.sql(\n", " \"DESCRIBE SELECT * FROM 'ventas-miss-yera.csv'\").fetchall()]\n", "print('en el parquet:', del_grande)\n", "print('se quedaron :', sorted(set(del_csv) - set(del_grande)))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```\n", "en el parquet: ['unidades', 'monto', 'canal', 'ciudad', 'repeticion']\n", "se quedaron : ['categoria', 'cliente_id', 'compro', 'descuento', 'fecha', 'fecha_ultima_compra', 'id_venta', 'monto_final_facturado', 'satisfaccion', 'segmento']\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Diez columnas se quedaron fuera. `DESCRIBE` es la consulta que hay\n", "que correr **antes** de prometerle a alguien un corte, y la que\n", "nadie corre. Guárdala junto al chequeo de rango del capítulo\n", "20." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 6. El archivo que no existe\n", "\n", "Prueba el error que sale al escribir mal una ruta." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "con.sql(\"SELECT * FROM 'no-existe.parquet'\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```\n", "IOException: IO Error: No files found that match the pattern \"no-existe.parquet\"\n", "```" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Fíjate en la palabra *pattern*. No es casualidad: DuckDB acepta\n", "comodines, así que `FROM 'ventas-*.parquet'` consulta todos los\n", "archivos que empiecen así como si fueran una sola tabla.\n", "\n", "Eso es exactamente lo que hace Spark con un directorio partido en cientos de\n", "trozos, y es la razón de que en big data los datos casi nunca estén en un solo\n", "archivo 🧾" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "---\n", "\n", "Ese era el capítulo 19 de **SQL desde cero**. El texto completo, con las salidas de cada bloque, está en https://missyera.com/guias/sql-desde-cero/consultar-archivos-grandes/\n", "\n", "Que tengas lindo día! 🌸" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "name": "python", "version": "3.11" } }, "nbformat": 4, "nbformat_minor": 5 }