La guía para el análisis de datos con DuckDB

Este artículo fue publicado como parte del Blogatón de ciencia de datos.

Alcance el siguiente nivel en su carrera de análisis de datos agregando DuckDB a su pila de datos. La guía lo ayudará a comprender la API de Python y varias formas de leer archivos CSV con un script SQL.

La vida de un analista de datos gira en torno a cargar datos a través del servidor SQL, analizarlos utilizando varios paquetes de Python y, finalmente, crear el informe técnico. A veces, estas consultas SQL pueden tardar más en procesarse, ya que se trata de terabytes de datos. En este mundo acelerado, esta estrategia falla estrepitosamente y la mayoría de los análisis se están alejando de las formas tradicionales de realizar análisis de datos. PatoDB resuelve todos los problemas, está integrado con Python & R. Funciona de manera similar a SQLite y se enfoca en proporcionar consultas analíticas más rápidas.

En esta guía, aprenderemos varias formas de ejecutar consultas SQL de DuckDB y también aprenderemos sobre la integración de Deepnote.

PatoDB

DuckDB es un sistema de gestión de bases de datos relacional orientado a tablas y admite consultas SQL para producir resultados analíticos. También viene con varias características que son útiles para el análisis de datos.

Consultas analíticas rápidas

DuckDB está diseñado para ejecutar cargas de trabajo de consultas analíticas más rápidas. Se ejecuta en un motor de ejecución de consultas vectorizado en columnas que ejecuta un gran lote de procesos de una sola vez. Esto hace que sea más rápido correr Procesamiento analítico en línea (OLAP) en comparación con los sistemas tradicionales como PostgreSQL, que procesan cada fila de forma secuencial.

Operación simple

DuckDB adopta la simplicidad y el funcionamiento integrado.

No importa ni copia datos mientras procesa consultas
Integrado dentro de un proceso de host
transferencia de datos de alta velocidad

Rico en funciones

DuckDB permite a los usuarios ejecutar consultas SQL complejas sin problemas. También admite la indexación secundaria para proporcionar un tiempo de consulta rápido dentro de la base de datos de un solo archivo. DuckDB proporciona una integración completa para Python y R para que las consultas se puedan ejecutar dentro del mismo archivo.

Gratis y de código abierto

DuckDB es de uso gratuito y el código completo está disponible en GitHub. Viene con un Licencia MIT lo que significa que puede utilizarlo con fines comerciales.

Creando base de datos

DuckDB proporciona una experiencia lista para usar para que se conecte con una base de datos o cree una nueva con un comando similar duckdb.connect(,read_only=False) .

Como puede ver en la imagen de abajo. MuestraDB Se han creado archivos en su directorio.

import duckdb conn = duckdb.connect ("SampleDB", read_only = False)

DuckDB también le permite crear una base de datos temporal en memoria usando duckdb.connect(). conn.execute() ejecutar todas las solicitudes de consultas en la base de datos. En este ejemplo, vamos a crear una tabla temporal llamada mesa_prueba que contiene i como entero y j como una cuerda. Si está familiarizado con SQL no tendrá problemas para comprender las solicitudes de consulta, pero si es nuevo en SQL, le sugiero que mire este increíble chuleta.

conn = duckdb.connect ()

# ejecutar comandos SQL arbitrarios conn.execute ("CREATE TABLE test_table (i INTEGER, j STRING)")

Insertar valores

Puede insertar valores únicos o múltiples mediante comandos SQL. En esta parte, estamos agregando dos valores a mesa_prueba usando ejecutar.

conn.execute(“INSERTAR IGNORAR EN VALORES test_table (1, 'uno'),(9, 'nueve')”)

Para comprobar si hemos agregado valores correctamente, ejecutaremos SQL para leer todos los valores de i. Para mostrar el resultado de las consultas en pandas marco de datos que agregaremos .fetchdf() como se muestra a continuación. Buen trabajo, Hemos agregado exitosamente nuestro filete dos valores.

conn.execute ("SELECT i from test_table") .fetchdf ()

También podemos usar marcadores de posición para los parámetros y luego agregar una matriz a la mesa_prueba. Si está familiarizado con el marco de Python, encontrará este método fácil de ejecutar. También podemos ejecutar varios valores a la vez usando .executemany(). Como puede ver a continuación, hemos agregado valores con éxito en el mesa_prueba.

conn.execute(“INSERTAR IGNORAR EN VALORES test_table (?, ?)”, [2, 'dos'])

conn.executemany(“INSERTAR IGNORAR EN VALORES de tabla_prueba (?, ?)”, [[3, 'tres'], [4, 'cuatro']])

conn.execute ("SELECT * from test_table") .fetchdf ()

usando un marcador de posición para la inserción de valor

Uso .fetchnumpy() para mostrar sus resultados en forma de NumPy formación. A partir de este punto, esto se volverá bastante interesante, ya que aprenderemos varias formas de ejecutar consultas SQL complejas para el análisis de datos.

conn.execute ("SELECT * FROM test_table"). fetchnumpy ()

{'i': array([1, 9, 2, 3, 4, 1, 9, 2, 3, 4, 1, 9, 2, 3, 4, 1, 9, 2, 3, 4, 1, 9, 2, 3, 4, 1, 9, 2, 3, 4], dtype=int32), 'j': array(['one', 'nine', 'two', 'three', 'four', 'one', 'nine', 'two', 'three', 'four', 'one', 'nine', 'two', 'three', 'four', 'one', 'nine', 'two', 'three', 'four', 'one', 'nine', 'two', 'three', 'four', 'one', 'nine', 'two', 'three', 'four'], dtype=object)}

Marco de datos de Pandas y SQL

En esta sección, jugaremos con el marco de datos de Panda y aprenderemos varias formas de leer .csv archivos. Primero, crearemos un marco de datos de Pandas simple usando un diccionario y luego lo agregaremos a una nueva tabla llamada prueba_df.

Usando el script SQL para encontrar todos los valores de j donde i es mayor que uno. Los resultados de la consulta muestran tres muestras que coinciden con nuestras condiciones.

importar pandas como pd

test_df = pd.DataFrame.from_dict ({"i": [1, 2, 3, 4], "j": ["uno", "dos", "tres", "cuatro"]})

conn.register ("test_df", test_df)

conn.execute ("SELECCIONAR j DE test_df DONDE i> 1"). fetchdf ()

En el siguiente paso, vamos a importar .csv al marco de datos de pandas y luego agréguelo a la nueva tabla banco_df. El conjunto de datos utilizado en este ejemplo es de Kaggle bajo el GPL2 licencia. Los resultados muestran la cantidad de recuperación real cuando la edad es superior a 27. Como podemos ver, las consultas complejas se están volviendo simples de ejecutar.

df = pd.read_csv ("bank_data.csv")

conn.register ("bank_df", df)

conn.execute ("SELECCIONE actual_recovery_amount FROM bank_df DONDE edad> 27"). fetchdf ()

Relaciones

Relation API utiliza consultas programáticas para evaluar cadenas de comandos de relaciones. En resumen, puede ejecutar funciones de Python en relaciones y mostrar los resultados. Los resultados contienen un árbol experimentado, resultados en columnas y resultados en una vista previa. Si se siente confundido en este momento, está bien porque vamos a profundizar en las relaciones y en cómo usarlas para obtener resultados analíticos.

Relaciones de carga

Crear relación a partir del marco de datos de Pandas existente prueba_df usando conn.from_df(test_df) . Al imprimir, rel podemos visualizar el marco de datos completo.

rel = conn.from_df (test_df)

rel

---------------------
-- Expression Tree --
---------------------
pandas_scan(140194817412592)

--------------------- - Columnas de resultados - --------------------- - i (GRANDE) - j (VARCHAR)

--------------------- -- Result Preview -- --------------------- i j BIGINT VARCHAR [ Rows: 4] 1 one 2 two 3 three 4 four

También puedes usar duckdb.df() para obtener resultados similares.

rel = duckdb.df (test_df)

Uso conn.table para crear una relación a partir de la tabla existente. En nuestro caso, estamos usando mesa_prueba.

rel = conn.table ("tabla_prueba")

rel

---------------------
-- Expression Tree --
---------------------
Scan Table [test_table]

--------------------- -- Result Preview -- --------------------- i j INTEGER VARCHAR [ Rows: 5] 1 one 9 nine 2 two 3 three 4 four

Para crear la relación directamente desde .csv archivo, uso .from_csv_auto .

rel = duckdb.from_csv_auto ("bank_data.csv")

rel

---------------------
-- Expression Tree --
---------------------
read_csv_auto(bank_data.csv)

--------------------- - Columnas de resultados - --------------------- - id (INTEGER) - monto_recuperación_esperada (INTEGER) - monto_recuperación_real (DOBLE) - estrategia_de_recuperación (VARCHAR) - edad (INTEGER) - sexo (VARCHAR)
---------------------
-- Result Preview --
---------------------
id	expected_recovery_amount	actual_recovery_amount	recovery_strategy	age	sex INTEGER	INTEGER	DOUBLE	VARCHAR	INTEGER	VARCHAR [ Rows: 10]
2030	194	263.540000	Level 0 Recovery	19	Male 1150	486	416.090000	Level 0 Recovery	25	Female 380	527	429.350000	Level 0 Recovery	27	Male 1838	536	296.990000	Level 0 Recovery	25	Male 1995	541	346.385000	Level 0 Recovery	34	Male 731	548	520.710000	Level 0 Recovery	35	Male 221	549	221.585000	Level 0 Recovery	33	Male 1932	560	373.720000	Level 0 Recovery	19	Female 1828	562	201.865000	Level 0 Recovery	22	Female 2001	565	504.885000	Level 0 Recovery	27	Male

Información relacional

Uso rel.alias para comprobar el nombre dado a la relación. En nuestro caso es datos_bancarios.csv.

rel.alias

'bank_data.csv'

Podemos cambiar el alias actual usando .set_alias . Esto puede resultar útil para unir relaciones similares.

rel2 = rel.set_alias ('banco_datos')

rel2.alias

'bank_data'

El tipo de nuestras relaciones en Subconsulta_Relación.

tipo rel.

'SUBQUERY_RELATION'

Para inspeccionar el nombre de las columnas de la relación, simplemente use .columns .

columnas rel.

['id', 'expected_recovery_amount', 'actual_recovery_amount', 'recovery_strategy', 'age', 'sex']

Uso .types para inspeccionar tipos de columnas.

tipos rel.

['INTEGER', 'INTEGER', 'DOUBLE', 'VARCHAR', 'INTEGER', 'VARCHAR']

Aplicar funciones similares a Python (función única)

Ahora viene la parte divertida. Puede agregar cualquier función con la relación y mostrará resultados aumentados. En nuestro caso, hemos utilizado rel.filter y muestra resultados de edad superior a 18. También muestra información adicional sobre el árbol de expresión, que puede ser bastante útil si tiene que ejecutar una consulta larga y compleja.

rel.filter ('edad> 18')

---------------------
-- Expression Tree --
---------------------
Filter [age>18] read_csv_auto(bank_data.csv)

--------------------- - Columnas de resultados - --------------------- - id (INTEGER) - monto_recuperación_esperada (INTEGER) - monto_recuperación_real (DOBLE) - estrategia_de_recuperación (VARCHAR) - edad (INTEGER) - sexo (VARCHAR)
---------------------
-- Result Preview --
---------------------
id	expected_recovery_amount	actual_recovery_amount	recovery_strategy	age	sex INTEGER	INTEGER	DOUBLE	VARCHAR	INTEGER	VARCHAR [ Rows: 10]
2030	194	263.540000	Level 0 Recovery	19	Male 1150	486	416.090000	Level 0 Recovery	25	Female 380	527	429.350000	Level 0 Recovery	27	Male 1838	536	296.990000	Level 0 Recovery	25	Male 1995	541	346.385000	Level 0 Recovery	34	Male 731	548	520.710000	Level 0 Recovery	35	Male 221	549	221.585000	Level 0 Recovery	33	Male 1932	560	373.720000	Level 0 Recovery	19	Female 1828	562	201.865000	Level 0 Recovery	22	Female 2001	565	504.885000	Level 0 Recovery	27	Male

Usar .project mostrará las columnas mencionadas y en nuestro caso se muestra id y edad.

rel.project ('id, edad')

---------------------
-- Expression Tree --
---------------------
Projection [id, age] read_csv_auto(bank_data.csv)

--------------------- - Columnas de resultados - --------------------- - id (INTEGER) - edad (INTEGER)

Puede transformar los valores de sus columnas agregando un número o usando cualquier función aritmética. En nuestro caso, muestra la edad con +1.

rel.project ('edad + 1')

---------------------
-- Expression Tree --
---------------------
Projection [age + 1] read_csv_auto(bank_data.csv)

--------------------- - Columnas de resultados - --------------------- - edad + 1 (INTEGER)
---------------------
-- Result Preview --
---------------------
age + 1 INTEGER [ Rows: 10]
20 26 28 26 35 36 34 20 23 28

El orden es similar al script SQL ORDER .

rel.order ('sexo')

El .limit muestra las mejores muestras en una tabla. En nuestro caso, solo mostrará los 2 valores superiores.

límite rel. (2)

Apilando todas las funciones en una cadena

Al igual que en R, puede apilar todas sus funciones para obtener la salida SQL. En nuestro caso, muestra los dos primeros cantidad_de_recuperación_real, ordenar por vie de la gente con el edad mayor que 27. Ahora nos estamos dando cuenta de todo el potencial del árbol de expresión.

rel.filter ('edad> 27'). proyecto ('cantidad_recuperación_real'). orden ('sexo'). límite (2)
---------------------
-- Expression Tree --
---------------------
Limit 2 Order [sex DESC] Projection [actual_recovery_amount] Filter [age>27] read_csv_auto(bank_data.csv)

--------------------- - Columnas de resultados - --------------------- - actual_recovery_amount (DOBLE)
---------------------
-- Result Preview --
---------------------
actual_recovery_amount DOUBLE [ Rows: 2]
278.720000 245.000000

Funciones agregadas

La función agregada puede realizar múltiples tareas grupales. En este caso, suma todo el monto de recuperación real.

rel.aggregate ("suma (monto_recuperación_real)")

---------------------
-- Result Preview --
---------------------
sum(actual_recovery_amount) DOUBLE [ Rows: 1]
7529821.469511

La siguiente función mostrará la suma de cantidad_de_recuperación_real por grupo de edad. Esto es genial ya que hemos reducido dos funciones en una.

rel.aggregate ("edad, suma (cantidad_recuperación_real)")

---------------------
-- Result Preview --
---------------------
age	sum(actual_recovery_amount) INTEGER	DOUBLE [ Rows: 10]
19	52787.712089 25	72769.342330 27	67569.292950 34	109902.427032 35	115424.466724 33	138755.807230 22	46662.153746 31	92225.534688 18	39969.573274 32	110627.466806

Si solo desea mostrar la suma de la cantidad real de recuperación, agregue grupo por columnas como entrada secundaria. En nuestro caso, solo muestra la suma de las cantidades reales de recuperación por edad.

rel.aggregate ("suma (monto_recuperación_real)", "edad")

---------------------
-- Result Preview --
---------------------
sum(actual_recovery_amount) DOUBLE [ Rows: 10]
52787.712089 72769.342330 67569.292950 109902.427032 115424.466724 138755.807230 46662.153746 92225.534688 39969.573274 110627.466806

Para mostrar valores únicos, utilice distinct()

rel.distinct ()

---------------------
-- Result Preview --
---------------------
id	expected_recovery_amount	actual_recovery_amount	recovery_strategy	age	sex INTEGER	INTEGER	DOUBLE	VARCHAR	INTEGER	VARCHAR [ Rows: 10]
2030	194	263.540000	Level 0 Recovery	19	Male 1150	486	416.090000	Level 0 Recovery	25	Female 380	527	429.350000	Level 0 Recovery	27	Male 1838	536	296.990000	Level 0 Recovery	25	Male 1995	541	346.385000	Level 0 Recovery	34	Male 731	548	520.710000	Level 0 Recovery	35	Male 221	549	221.585000	Level 0 Recovery	33	Male 1932	560	373.720000	Level 0 Recovery	19	Female 1828	562	201.865000	Level 0 Recovery	22	Female 2001	565	504.885000	Level 0 Recovery	27	Male

Operadores de relaciones múltiples

Podemos crear UNION entre dos relaciones usando .union Esto combina los resultados de ambas relaciones.

unión rel. (rel)
---------------------
-- Expression Tree --
---------------------
Union read_csv_auto(bank_data.csv) read_csv_auto(bank_data.csv)

Uniendo dos relaciones en id columna. Hemos creado rel2 y lo uní a rel basados en ID.

rel2 = duckdb.df (df)

rel.join (rel2, 'id')

---------------------
-- Expression Tree --
---------------------
Join read_csv_auto(bank_data.csv) pandas_scan(139890483423984)

Para unir una relación similar usaremos alias() para cambiar el alias y luego unirlos como se muestra a continuación.

rel.set_alias ('a'). join (rel.set_alias ('b'), 'a.id = b.id')

 - - - - - - - - - - - - Árbol de expresión - - - - - - - - - - - - Únase a read_csv_auto (bank_data.csv) read_csv_auto (bank_data.csv)

Funciones de DuckDB con un DataFrame.

Podemos omitir la creación de relaciones y sumergirnos directamente en el filtrado y la clasificación utilizando duckdb.(,). Los ejemplos muestran cómo puede crear resultados usando el marco de datos de Pandas directamente.

print (duckdb.filter (df, 'edad> 1'))

print (duckdb.project (df, 'edad +1'))

imprimir (duckdb.order (df, 'sexo'))

imprimir (duckdb.limit (df, 2))

De manera similar, podemos crear una cadena de funciones y mostrar los resultados como se muestra a continuación.

duckdb.filter (df, 'edad> 1'). proyecto ('edad + 1'). orden ('sexo'). límite (2)

---------------------
-- Expression Tree --
---------------------
Limit 2 Order [sex DESC] Projection [age + 1] Filter [age>1] pandas_scan(139890483423984)

--------------------- - Columnas de resultados - --------------------- - edad + 1 (GRANDE)
---------------------
-- Result Preview --
---------------------
age + 1 BIGINT [ Rows: 2]
26 20

Resultados de la consulta de relación en DuckDB

Para mostrar los resultados de la relación utilice:

fetchall (): para mostrar todos los valores de los resultados.
fetchdf (): para mostrar los resultados en forma de Pandas Dataframe.
fethnumpy (): para mostrar los resultados en forma de una matriz Numpy.

res = rel.execute ()

imprimir (res.fetchone ())
(2030, 194, 263.54, 'Recuperación de nivel 0', 19, 'Masculino')

imprimir (res.fetchall ())

[(1150, 486, 416.09, 'Recuperación de nivel 0', 25, 'Mujer'), (380, 527, 429.35, 'Recuperación de nivel 0', 27, 'Hombre') .....

También podemos convertir los resultados en el marco de datos de Pandas usando rel.df() or rel.to_df() como se muestra a continuación.

rel.to_df ()

Tablas y DuckDB

También podemos crear tablas en relación usando .create

rel.create ("tabla_prueba2")

En la siguiente parte, aprenderemos varias formas de insertar valores en una relación.

Insertar valores usando values() y insert_into()
crea una relación usando test_table3.
Inserte valores en la relación usando la función .insert simple.

Como podemos ver, hemos agregado con éxito un valor único a la relación.

conn.execute ("CREAR TABLA test_table3 (i INTEGER, j STRING)")

conn.values ([5, 'cinco']). insert_into (“test_table3”)

rel_3 = conn.table ("test_table3")

rel_3.insert ([6, 'seis'])

rel_3

---------------------
-- Expression Tree --
---------------------
Scan Table [test_table3]

--------------------- - Columnas de resultados - --------------------- - i (INTEGER) - j (VARCHAR)
---------------------
-- Result Preview --
---------------------
i	j INTEGER	VARCHAR [ Rows: 2]
5	five 6	six

Función de consulta DuckDB

Para ejecutar consultas SQL directamente, puede usar .query ellos con la relación. Luego agregue la primera entrada como el nombre de la vista de la consulta y la segunda entrada como un script SQL usando el nombre de la vista como se muestra a continuación.

res = rel.query ('my_name_for_rel', 'SELECT * FROM my_name_for_rel LIMIT 5')

Para ver los resultados, usemos fetchall() para mostrar los 5 valores.

res.fetchall ()

[(2030, 194, 263.54, 'Level 0 Recovery', 19, 'Male'), (1150, 486, 416.09, 'Level 0 Recovery', 25, 'Female'), (380, 527, 429.35, 'Level 0 Recovery', 27, 'Male'), (1838, 536, 296.99, 'Level 0 Recovery', 25, 'Male'), (1995, 541, 346.385, 'Level 0 Recovery', 34, 'Male')]

Podemos usar directamente .query() y ejecutar una consulta en el prueba_df mesa. Los resultados muestran los cuatro valores de la tabla. La función de consulta puede realizar todas las funciones en su tabla, marco de datos e incluso en .csv archivos.

res = duckdb.query ('SELECCIONAR * DE test_df')

res.df ()

Para ejecutar una consulta en .csv archivo, usaremos read_csv_auto() en secuencia de comandos SQL. En nuestro caso, estamos leyendo todos los datos directamente de un archivo CSV y mostrándolos en el marco de datos de Pandas.

res = duckdb.query ("SELECCIONAR * DE read_csv_auto ('bank_data.csv')")

res.df ()

Ejecutemos un script complejo para que podamos aprovechar todo el potencial de DuckDB. El resultado a continuación muestra que se recuperó más cantidad de la esperada de una mujer usando la estrategia de nivel 4. En mi opinión, usar consultas SQL para el análisis de datos es rápido y divertido, en lugar de cargar sus datos en pandas y escribir un montón de código para obtener resultados similares.

res = duckdb.query ("SELECCIONAR sexo, SUM (cantidad_esperada_de_recuperación) como se esperaba, SUM (cantidad_de_recuperación_real) como real DESDE 'bank_data.csv' DONDE recovery_strategy LIKE 'Recuperación de nivel 4' GRUPO POR sexo TENIENDO sexo = 'Mujer'”) res. df ()

consulta compleja con funciones agregadas

Célula SQL de Deepnote

Nota profunda, Celda SQL de marco de datos está usando DuckDB en su backend, así que si quieres una manera fácil de usar todas las funciones de DuckDB, te recomiendo que uses la celda Deepnote SQL. Para ejecutar su consulta SQL primero, todos agreguen celdas SQL desde la opción de bloque.

Intente escribir una secuencia de comandos SQL simple. Como podemos ver, estamos leyendo datos directamente de datos_bancarios.csv y los resultados se almacenan en df_1 en forma de Pandas DataFrame. Me encanta esta función, es incluso mejor que cargar tus datos a través de Pandas.

Probemos una consulta SQL más compleja en un archivo CSV. Mostraremos tres columnas sexo, esperadoy real. Luego, seleccionaremos valores donde estrategia_de_recuperación es 'Recuperación de nivel 4' y agrupar por vie. Finalmente, solo mostraremos valores femeninos.

Reflexiones finales sobre DuckDB

DuckDB puede resolver la mayoría de los problemas presentes en las bases de datos SQL tradicionales. Es un sistema de administración de bases de datos sin servidor con consultas de análisis más rápidas y está completamente integrado con Python y R. Si va a implementar una aplicación de ciencia de datos o aprendizaje automático, le sugiero que agregue DuckDB a su pila.

En esta guía, hemos aprendido varias formas de crear una base de datos, agregar tablas y ejecutar consultas. También hemos aprendido sobre relaciones y podemos usarlas para crear funciones complejas. Finalmente, hemos utilizado varias formas de interactuar directamente con archivos CSV y hemos ejecutado múltiples scripts complejos. Esta guía incluye todo tipo de ejemplos, por lo que si te quedas atascado en tu proyecto, puedes volver y aprender algunos trucos. Al final, siempre te sugeriré que te

Recursos para el Aprendizaje

sobre el autor

Abid Ali Awan (@ 1abidaliawan) es un científico de datos profesional certificado al que le encanta construir modelos de aprendizaje automático e investigar las últimas tecnologías de inteligencia artificial. Actualmente, probando productos de IA en PEC-PITC, su trabajo luego se aprueba para ensayos en humanos, como el Clasificador de cáncer de mama. Su visión es construir un producto de inteligencia artificial que identifique a los estudiantes que luchan contra una enfermedad mental.

Proyectos de cartera de ciencia de datos

Los medios que se muestran en este artículo no son propiedad de Analytics Vidhya y se utilizan a discreción del autor.

Relacionado:

PlatoAi. Web3 reinventado. Inteligencia de datos ampliada.
Haga clic aquí para acceder.

Fuente: https://www.analyticsvidhya.com/blog/2021/12/the-guide-to-data-analysis-with-duckdb/

Inteligencia de datos generativa