Curso
Manipulación de datos en SQL
4 h
335K
Como data scientist, a menudo tendrás que trabajar con enormes volúmenes de datos. Tratar con datos (cuando están presentes en cantidades masivas) no es nada fácil. Para gestionarlos de la forma más eficiente posible, necesitas entender bien cómo se organizan físicamente, de modo que puedas procesarlos con técnicas adecuadas.
SQL es una habilidad imprescindible para cualquier ingeniero/a de software moderno porque la mayoría del software depende de algún tipo de datos e integra bien con un RDBMS (Relational Database Management System). Ya sea una aplicación web, una API o una herramienta interna, siempre hay un RDBMS detrás. Y SQL es el lenguaje para consultar un RDBMS.
Como data scientist, es fundamental conocer SQL y sus técnicas relacionadas. Para poder consultar un RDBMS y obtener respuestas a preguntas concretas sobre los datos con los que trabajas, SQL es el mínimo necesario.
En su último vídeo con DataCamp, David Robinson (Chief Data Scientist @ DataCamp) nos mostró cómo utiliza SQL en un problema de Data Science. Échale un vistazo: su flujo de trabajo es muy interesante.
Hoy vas a aprender una técnica llamada indexación que se centra en la organización de los datos dentro de una base de datos, y vas a implementar algunas de sus variantes con SQL. Esto te dará una visión general de cómo la indexación puede utilizarse para almacenar información en una base de datos y cómo puede reducir los tiempos de ejecución.
Nota: antes de empezar con este tutorial, te recomendamos encarecidamente aprender los fundamentos de SQL si no estás familiarizado/a. El curso Intro to SQL for Data Science de DataCamp es un excelente recurso si quieres repasar tus bases de SQL.
Una breve nota sobre la organización de registros en un archivo
Antes de entrar en la indexación, es esencial entender cómo se organizan físicamente los datos dentro de los archivos. No vamos a profundizar en todos los detalles, pero tener una buena visión de conjunto te ayudará a comprender con claridad la indexación. Vamos allá.
La organización de registros/datos trata, en general, de cómo se almacenan los registros y, a grandes rasgos, puede dividirse en dos tipos:
-
Organización ordenada: todos los registros de un archivo están ordenados según algún valor de clave de búsqueda. Para buscar un elemento en un archivo ordenado suele usarse la búsqueda binaria. Esto hace que la búsqueda sea muy eficiente, ya que su complejidad se mantiene en tiempo logarítmico. Pero insertar en el archivo se vuelve costoso porque quizá tengas que reorganizarlo entero para dar cabida al nuevo elemento.
-
Organización desordenada: en este caso, los registros se insertan donde haya espacio disponible, normalmente al final del archivo. Como se usa búsqueda lineal, la búsqueda no es tan eficiente como en el caso anterior, pero la inserción no es una operación tan costosa.
Aunque tengas una organización ordenada, ¿qué pasa si el tamaño de los bloques del archivo y el tamaño de cada registro son muy grandes? Lo vemos en la siguiente sección.
Necesidad de la indexación
Empezarás esta sección entendiendo por qué usamos índices para almacenar archivos/información de forma eficiente y cómo esto mejora otras operaciones relacionadas.
Imagina que tienes una tabla (relación) con varios registros en una base de datos. Los registros, además, se dividen en 1000 bloques. Gráficamente, la organización sería así:
![\"table](\"https://image.ibb.co/dEc6dV/Capture.jpg\")
De la organización anterior se desprende que:
- El orden se aplica a la primera columna de los registros (piensa que se trata de un sistema de base de datos relacional donde los datos se organizan en tablas).
- El número de bloques en los que se divide el total de datos es de 1000 bloques.
Recuerda que cada registro contiene otras columnas, pero en esta organización el orden se aplica a la primera columna y los bloques se dividen en consecuencia. Si haces una búsqueda binaria para localizar algo en esta organización, el tiempo total será de $\\log_{2}1000$, que equivale a 10 unidades de tiempo. ¿Se puede mejorar este tiempo de búsqueda?
Por supuesto que sí.
Piénsalo como leer un libro sin página de índice. Abres una página al azar (o, en búsqueda binaria, la del medio) y pasas páginas a izquierda y derecha hasta llegar a la que quieres. Eso lleva tiempo, ¿verdad?
Si el libro tuviera índice, la búsqueda sería todavía más eficiente. Irías directo a la página consultando el índice. Podemos aplicar la indexación del mismo modo al caso anterior.
Resumen de cómo aplicar la indexación aquí:
- Mantén un puntero de bloque para cada bloque junto con los valores ordenados usados en la organización anterior.
Esto reducirá el número de bloques necesarios para almacenar los archivos de datos. Ahora, para buscar un registro concreto, solo tendrás que buscar en esta nueva organización con menos bloques y, con esa información, llegarás a tu registro (si existe) mucho más rápido. Vamos a visualizarlo. Si tras indexar obtienes 8 bloques, la organización sería así:
![\"less](\"https://image.ibb.co/n70Uhq/Capture.jpg\")
El tiempo de búsqueda se reducirá drásticamente al haber menos bloques. Supón que quieres buscar el registro 90. En este nuevo esquema indexado, primero localizas su puntero de bloque, que en este caso es 3, y con esa información encuentras los registros originales de una sola vez.
Así, el tiempo de búsqueda será $\\log_{2}8 + 1$ = 4 unidades de tiempo, significativamente menor que antes.
El ejemplo anterior ilustra por qué necesitamos indexar. Algunos puntos a tener en cuenta:
- El orden en los datos originales solo puede aplicarse usando un campo. Luego se indexan las entradas de ese campo. Solo si buscas por ese campo obtendrás un tiempo de búsqueda mejorado. (Muy importante)
- Los índices también están ordenados.
- Un registro de índice contiene dos campos (estructura del archivo de índice):
- La clave del archivo original
- Un puntero al bloque donde está disponible la clave en los datos originales
- Para buscar entre los índices se usa búsqueda binaria.
- Para acceder a un registro usando las entradas indexadas, el número medio de accesos a bloque necesarios es:
$\\log_{2}B_i + 1$, donde $B_i$ es el número de bloques en los registros indexados - Puede crearse un índice sobre cualquier campo de la relación (clave primaria, claves candidatas, campos no clave).
Según el orden de los datos originales y cuántos registros mantengas en el archivo indexado, hay varios esquemas de indexación. En la siguiente sección verás los más populares.
Estrategias de indexación
- Indexación densa: si se crea una entrada de índice para cada valor de clave de búsqueda, hablamos de indexación densa. Observa el siguiente diagrama para entenderlo visualmente.
![\"dense](\"https://image.ibb.co/gLhOaA/Diagrams.jpg\")
- Indexación dispersa: si se crea una entrada de índice solo para algunos registros, es indexación dispersa. Aquí tienes un diagrama ilustrativo.
![\"sparse](\"https://image.ibb.co/nrE5Nq/Diagrams-1.jpg\")
Los diagramas hacen que ambos esquemas se entiendan con facilidad. Un punto clave: el ejemplo anterior de indexación dispersa es una combinación de indexación densa y dispersa. Esto es así porque para cada valor único de clave de búsqueda (1, 2 y 3) hay un índice, pero no existe un índice para cada registro de datos.
Ahora verás otros tipos de esquemas de indexación según el nivel de los registros. En la indexación de un solo nivel, solo hay un archivo de índice. Pero a veces el tamaño del archivo de índice crece tanto que se indexa el propio archivo de índice. En ese caso, se llama indexación multinivel. Profundicemos.
Este es un resumen de las estrategias adicionales de indexación según los niveles.
Indexación de un solo nivel
- Indexación primaria
- Indexación por agrupación (clustered)
- Indexación secundaria
Indexación multinivel
- Árbol B
- Árbol B+
Verás todas las estrategias que pertenecen a la indexación de un solo nivel. Al final del tutorial tendrás un enlace para explorar los esquemas multinivel si te interesan. Veamos ahora la indexación primaria.
Indexación primaria
Un índice primario es un archivo ordenado cuyos registros tienen longitud fija con dos campos:
- El primer campo coincide con la clave primaria del archivo de datos.
- El segundo campo es un puntero al bloque de datos donde está disponible la clave primaria. - Fuente
El índice creado para el primer registro de cada bloque se denomina ancla de bloque. En la indexación primaria, el número de entradas del índice = número de bloques de datos originales. El número medio de accesos a bloque usando un índice primario es:
Consulta el siguiente diagrama para entenderlo mejor: ![\"primary](\"https://image.ibb.co/nJZBCq/Diagrams.jpg\")
La figura de la derecha representa los datos originales divididos en varios bloques. Observa que la columna con los números 1, 2, 3, ... , 9 son las claves primarias. La figura de la izquierda corresponde a las entradas indexadas, donde cada entrada consta de:
- La primera entrada de cada bloque de datos (ancla de bloque)
- La segunda entrada, que indica el puntero de bloque.
Párate un momento y piensa qué tipo de indexación es (¿dispersa o densa?). Usa la sección de comentarios para publicar tu respuesta.
Ahora veremos la indexación por agrupación.
Indexación por agrupación (clustering)
Un índice por agrupación se crea sobre un archivo de datos cuyos registros están ordenados físicamente por un campo no clave que no tiene un valor distinto para cada registro. Este campo se conoce como campo de agrupación y es el que se usa para indexar. De ahí su nombre: índice por agrupación.
![\"clustering](\"https://image.ibb.co/bDtkNq/Diagrams-1.jpg\")
Los diagramas siempre ayudan. Como ves, los datos originales están ordenados por un atributo no clave y, para cada valor distinto de ese atributo, se crea una entrada de índice. El número medio de accesos a bloque necesarios para localizar un registro concreto con este esquema es $\\geq$ $\\log_{2}B_i + 1$, donde $B_i$ es el número de bloques en los registros indexados. Fíjate en el signo $\\geq$.
En la indexación por agrupación, tras localizar el bloque en el que está presente una clave concreta, es posible que tengas que recorrer otros bloques (como se intuye en la figura).
Puedes pensar qué tipo de indexación es (¿dispersa o densa?). Usa la sección de comentarios para publicar tu respuesta. Veamos ahora la indexación secundaria.
Indexación secundaria
Supón que tienes una tabla llamada Employee en tu base de datos. La tabla tiene los siguientes atributos:
- employee_id
- employee_name
- employee_department
- employee_salary
employee_id es su clave primaria. Ya has creado un índice primario en esta tabla basado en employee_id. Pero, al desarrollar una aplicación, descubres que la mayoría de las consultas usan el atributo employee_name. En ese caso, el índice primario no ayuda demasiado y es buena práctica mantener un índice independiente para todos los valores de employee_name. Además, los nombres de empleados no van a estar ordenados en la base de datos. Así que indexarlos acelerará sin duda las consultas que los usen.
Este es un ejemplo clásico de indexación secundaria. Intentemos construir una imagen adecuada también para este caso:
![\"secondary](\"https://image.ibb.co/g6tc2q/Diagrams.jpg\")
Si tienes una representación mejor, compártela en la sección de comentarios.
Ahora verás cómo crear índices en PostgreSQL. Si quieres asentar las bases, echa un ojo al curso de DataCamp Joining Data in PostgreSQL.
Creación de índices en PostgreSQL
Antes de crear índices en una base de datos PostgreSQL, necesitas tener datos en una tabla. Vamos a crear una tabla sencilla llamada Student con los siguientes campos:
- student_id
- student_name
- student_year
Haremos que student_id sea la clave primaria y no permitiremos valores nulos en los nombres y los años.
La consulta sería:
CREATE TABLE STUDENT(
student_id TEXT PRIMARY KEY,
student_name TEXT NOT NULL,
student_year TEXT NOT NULL
);
Una vez creada la tabla, tendrás que insertar algunos datos. Para hacerlo fácil, puedes usar un .csv e importarlo en Student. Puedes importar un archivo .csv compatible a una tabla de PostgreSQL con la siguiente consulta:
COPY STUDENT FROM '/path/to/csv/Student.csv' WITH (FORMAT csv);
La consulta anterior asume que el archivo .csv del que se copian los datos se llama Student.
Veamos ahora los datos. Ejecutar select * from STUDENT; devuelve los siguientes registros: ![\"records\"](\"https://image.ibb.co/kdHjFA/Capture.jpg\")
La consulta select debería devolver un total de 86 registros. Ya estás en disposición de crear índices. Puedes crear índices de una sola columna con la siguiente sintaxis:
CREATE INDEX index_name
ON table_name (column_name);
Creemos un índice sobre el campo student_id (indexación primaria).
CREATE INDEX id_index
ON STUDENT (student_id);
También pueden crearse índices multicolumna:
CREATE INDEX id_index
ON STUDENT (student_id,student_name);
Puedes eliminar un índice con la siguiente sintaxis:
DROP INDEX index_name;
Es difícil percibir el impacto de la indexación en una tabla pequeña como STUDENT. Pero si la tabla fuera grande (piensa en una universidad grande con registros similares), los índices serían vitales.
¡Lo has conseguido!
Enhorabuena por llegar hasta el final. En este tutorial has aprendido qué es la indexación, para qué sirve y sus diferentes esquemas. También has visto cómo crear índices sencillos en PostgreSQL.
No hemos tratado la indexación multinivel en este tutorial. Estos son algunos recursos excelentes si quieres explorarlos:
¿Pero siempre conviene usar índices? Hay casos en los que no es recomendable.
![\"indexing](\"https://image.ibb.co/dA9sJV/Capture.jpg\")
Espero que el tutorial te haya ayudado a afianzar los conceptos básicos de indexación. Cuéntame tus hallazgos interesantes en la sección de comentarios.
Visita el Learn SQL Hub de DataCamp.
Temas
