{ "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 práctica 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", "Los ejercicios están al final y traen una celda vacía debajo de cada uno. Las\n", "respuestas viven en el cuaderno de soluciones, y merece la pena pelearse un\n", "rato antes de abrirlo 💛" ] }, { "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": [ "## Antes de empezar\n", "\n", "Esta celda baja el ayudante que corrige tus ejercicios. Después, en cada\n", "ejercicio que se pueda corregir solo, vas a ver `%%revisa` arriba de la celda:\n", "escribe tu respuesta debajo, ejecuta, y te digo si te salió 💛" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "import urllib.request\n", "\n", "# El ayudante de los cuadernos. Trae la corrección de los ejercicios y, en los\n", "# capítulos de consola, la celda mágica que ejecuta los comandos. Se baja en\n", "# vez de venir pegado aquí para que siempre sea el último.\n", "urllib.request.urlretrieve(\n", " \"https://missyera.com/static/cuadernos/revisa.py\", \"revisa.py\")\n", "import revisa\n", "revisa.carga({\n", " 1: \"Y2l1ZGFkZXMgZGlzdGludGFzOiA5CmZpbGFzIHRvdGFsZXMgICAgIDogOTA4MDYz\",\n", " 2: \"4pSM4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSs4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSs4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSQCuKUgiAgY2l1ZGFkICDilIIgdGlja2V0IOKUgiAgIG4gICDilIIK4pSCIHZhcmNoYXIgIOKUgiBkb3VibGUg4pSCIGludDY0IOKUggrilJzilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilLzilIDilIDilIDilIDilIDilIDilIDilIDilLzilIDilIDilIDilIDilIDilIDilIDilKQK4pSCIENoaWNsYXlvIOKUgiA4OTIuOTgg4pSCIDM0Mzg1IOKUggrilIIgUGl1cmEgICAg4pSCIDgwNS4yNSDilIIgMzg1NzEg4pSCCuKUgiBBcmVxdWlwYSDilIIgNzYxLjE5IOKUgiAzOTc2NyDilIIK4pSCIFRydWppbGxvIOKUgiA3MjYuMDkg4pSCIDMzMTg5IOKUggrilIIgQ3VzY28gICAg4pSCIDY4NC4zMyDilIIgMzM0ODgg4pSCCuKUlOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKUtOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKUtOKUgOKUgOKUgOKUgOKUgOKUgOKUgOKUmA==\",\n", " 3: \"4pSM4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSs4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSQCuKUgiBieXRlc19kZV90ZXh0byDilIIgYnl0ZXNfZGVfbnVtZXJvcyDilIIK4pSCICAgICBpbnQxMjggICAgIOKUgiAgICAgIGludDEyOCAgICAgIOKUggrilJzilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilLzilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilKQK4pSCICAgICAgICAgIDkyNDAxIOKUgiAgICAgICAgICAgICA3MzY4IOKUggrilJTilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilLTilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilIDilJg=\",\n", " 4: \"Y3N2ICAgIDogMjc4ODU3IGJ5dGVzCnBhcnF1ZXQ6IDg4NzUyIGJ5dGVzCmFob3JybyA6IDY4LjIgJQ==\",\n", " 5: \"ZW4gZWwgcGFycXVldDogWyd1bmlkYWRlcycsICdtb250bycsICdjYW5hbCcsICdjaXVkYWQnLCAncmVwZXRpY2lvbiddCnNlIHF1ZWRhcm9uICAgOiBbJ2NhdGVnb3JpYScsICdjbGllbnRlX2lkJywgJ2NvbXBybycsICdkZXNjdWVudG8nLCAnZmVjaGEnLCAnZmVjaGFfdWx0aW1hX2NvbXByYScsICdpZF92ZW50YScsICdtb250b19maW5hbF9mYWN0dXJhZG8nLCAnc2F0aXNmYWNjaW9uJywgJ3NlZ21lbnRvJ10=\",\n", " 6: \"SU9FeGNlcHRpb246IElPIEVycm9yOiBObyBmaWxlcyBmb3VuZCB0aGF0IG1hdGNoIHRoZSBwYXR0ZXJuICJuby1leGlzdGUucGFycXVldCI=\",\n", "}, lenguaje=\"sql\")" ] }, { "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" ] }, { "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": [ "%%revisa 1\n", "-- tu turno" ] }, { "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": [ "%%revisa 2\n", "-- tu turno" ] }, { "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": [ "%%revisa 3\n", "-- tu turno" ] }, { "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": [ "%%revisa 4\n", "-- tu turno" ] }, { "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": [ "%%revisa 5\n", "-- tu turno" ] }, { "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": [ "%%revisa 6\n", "-- tu turno" ] }, { "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 }