Ejecución de SQL en datos CSV: conversión y extracción de datos

Muchas herramientas generan datos como valores separados por comas (CSV). Es un formato de datos tabulares simple pero común de texto sin formato que se puede procesar fácilmente. Cada linea de...

Muchas herramientas generan datos como valores separados por comas (CSV). Es un formato de datos tabulares simple pero común de texto sin formato que se puede procesar fácilmente. Cada línea del archivo representa un solo registro. Un registro consta del mismo número de campos o columnas. Por lo general, el delimitador entre los campos individuales es una coma (,), un punto y coma (;), un espacio o un tabulador.

Se pueden procesar uno o más archivos CSV con las herramientas de línea de comandos cut, join, head, comm, sed y awk. Este enfoque funciona muy bien, pero puede llevarle un tiempo encontrar una solución adecuada basada en estos comandos.

El siguiente ejemplo muestra cómo calcular la distancia total de viaje para la lista de viajes que se muestra a continuación. Las columnas en el archivo tracks.csv están delimitadas por pestañas, y el script awk totaliza los valores en la tercera columna, solo.

1
2
3
4
5
6
7
8
$ cat tracks.csv 
Date    Track   Distance
1 Dec 2018  Paris-Metz  300
3 Dec 2018  Metz-Nancy  57
4 Dec 2018  Nancy-Vesoul    156
5 Dec 2018  Vesoul-Mulhouse 112
$ awk -F '\t+' '{ total += $3 } END {printf "total: %d km\n", total}' tracks.csv
total: 625 km

Por el contrario, los sistemas de gestión de bases de datos (DBMS) como MySQL, MariaDB, PostgreSQL y SQLite almacenan datos en un formato que no es de texto sin formato que rara vez se puede leer de forma abierta. Para extraer datos, las declaraciones deben formularse en lenguaje de consulta estructurado (SQL) y evaluarse por el DBMS.

La siguiente declaración muestra cómo extraer el contenido de la columna Pista de la tabla llamada pista:

1
2
3
4
5
6
SELECT Track FROM track
Paris-Metz
Metz-Nancy
Nancy-Vesoul
Vesoul-Mulhouse
(4 lines)

A continuación, explicaremos cómo ejecutar declaraciones SQL directamente en datos CSV. Hay bastantes herramientas inteligentes que hacen posible conectar estos mundos entre sí.

###q

La primera herramienta simplemente se llama q, y su nombre en realidad se deriva de [El continuo Q](https://en.wikipedia.org/wiki/Star_Trek :_The_Q_Continuum) que se menciona en la serie Star Trek. El paquete Debian correspondiente a q se llama python-q-texto-como-datos. q acepta instrucciones SQL y permite la ejecución directa en uno o más archivos CSV.

A continuación, verá cómo extraer la primera columna llamada Fecha del archivo CSV.

1
2
3
4
5
$ q -H -t "SELECT Date FROM tracks.csv"
1 Dec 2018
3 Dec 2018
4 Dec 2018
5 Dec 2018

Las dos banderas utilizadas anteriormente tienen el siguiente significado:

  • -H (--skip-header) : La primera línea del archivo de datos contiene una descripción de las columnas (encabezados).

  • -t: Las columnas están separadas por tablas.

Para las demás banderas te recomendamos echar un vistazo a la q página de uso.

Para recuperar conjuntos de datos basados ​​en condiciones, puede escribir la siguiente declaración que extrae el valor de distancia de todos los conjuntos de datos que almacenan la fecha del 3 de diciembre de 2018:

1
2
$ q -H -t "SELECT Distance FROM tracks.csv WHERE Date = '3 Dec 2018'"
57

q sigue el SQL sintaxis utilizada por SQLite. De acuerdo con el sitio web del proyecto, se permiten expresiones SQL estándar, condiciones (tanto WHERE como HAVING), GROUP BY, ORDER BY, etc. En una cláusula ‘WHERE’, también se admiten ‘JOIN’ y subconsultas.

Esto también incluye una serie de funciones como ABS(), LENGTH(), LOWER() y UPPER(), así como funciones de fecha y hora, funciones agregadas y funciones JSON. Para calcular el total de los viajes individuales, use una declaración como la siguiente que demuestra la función SUM():

1
2
$ q -H -t "SELECT SUM(Distance) FROM tracks.csv"
625

El siguiente ejemplo está tomado del sitio web q y demuestra cómo extraer información del sistema. Combina ps y q en una estación de trabajo normal para calcular los 5 principales ID de usuario con la mayor cantidad de procesos propios, ordenados en orden descendente.

1
2
3
4
5
6
$ ps -ef | q -H "SELECT UID,COUNT(*) cnt FROM - GROUP BY UID ORDER BY cnt DESC LIMIT 5"
root 129
frank 68
postgres 6
www-data 5
avahi 2

q también se conecta a la biblioteca original python sqlite. El módulo Python consultacsv proporciona una funcionalidad similar, que parece estar bifurcado como [consultacsv-redux](https://pypi.org/project/querycsv- redux/) proyecto.

fsql

fsql es parte de un módulo Perl llamado fsql. Actualmente, no está disponible como paquete para Debian o Ubuntu, sino en Comprehensive Perl Archive Network (CPAN). Para usar fsql, puede instalar el módulo Perl con sus dependencias de paquetes usando cpanm de la siguiente manera:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
# cpanm App::fsql
--> Working on App::fsql
Fetching http://www.cpan.org/authors/id/P/PE/PERLANCAR/App-fsql-0.230.tar.gz ... OK
Configuring App-fsql-0.230 ... OK
==> Found dependencies: DBD::CSV, JSON::MaybeXS, Data::Format::Pretty::YAML, Data::Format::Pretty::Console, YAML::XS, Data::Format::Pretty::JSON, File::chdir, Data::Format::Pretty::Perl, Text::LTSV, Perinci::Result::Util, File::Slurper
--> Working on DBD::CSV
Fetching http://www.cpan.org/authors/id/H/HM/HMBRAND/DBD-CSV-0.54.tgz ... OK
Configuring DBD-CSV-0.54 ... OK
==> Found dependencies: SQL::Statement
--> Working on SQL::Statement
Fetching http://www.cpan.org/authors/id/R/RE/REHSACK/SQL-Statement-1.412.tar.gz ... OK
Configuring SQL-Statement-1.412 ... OK

[...]

Building and testing App-fsql-0.230 ... OK
Successfully installed App-fsql-0.230
95 distributions installed

Como se explica en la página del proyecto fsql, la herramienta le permite realizar consultas SQL en uno o varios archivos "planos" de varios formatos, como texto sin formato, CSV y JSON.

El siguiente ejemplo muestra cómo extraer todos los viajes que tienen una distancia de más de 100 km. Los datos se proporcionan desde stdin usando cat y se canalizan a fsql, luego:

1
2
3
4
5
$ cat tracks.csv | fsql 'SELECT Date,Track,Distance FROM stdin WHERE Distance > 100'
Date    Track   Distance
"1 Dec 2018"    Paris-Metz  300
"4 Dec 2018"    Nancy-Vesoul    156
"5 Dec 2018"    Vesoul-Mulhouse 112

Hasta ahora, fsql produjo una salida basada en líneas que es común para las bases de datos. Usando el parámetro de salida -f más el formato de salida deseado, puede hacer mucho más. Entre otros, fsql admite valores separados por comas (csv), valores separados por tablas (tsv), Perl (perl), notación de objetos Javascript (JSON) (json) y YAML Ain\ ’t Lenguaje de marcado (YAML) (yaml).

El siguiente ejemplo genera el resultado de la consulta como una matriz JSON:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
$ cat tracks.csv | fsql 'SELECT Date,Track,Distance FROM stdin WHERE Distance > 60' -f json
[
   [
      "1 Dec 2018",
      "Paris-Metz",
      "300"
   ],
   [
      "4 Dec 2018",
      "Nancy-Vesoul",
      "156"
   ],
   [
      "5 Dec 2018",
      "Vesoul-Mulhouse",
      "112"
   ]
]

Usando la opción -f yaml, la salida se formatea en estilo YAML:

1
2
3
4
$ cat tracks.csv | fsql 'SELECT sum(Distance) FROM stdin' -f yaml
---
-
  - 625

molinero

Preguntar a expertos de Linux a largo plazo sobre Molinero puede resultar un poco sorprendente, ya que es menos conocido que otras opciones. Miller tiene como objetivo combinar la funcionalidad de varias herramientas de texto como cat, grep, sed, awk, join y sort.

La siguiente captura de pantalla usa el subcomando cut y muestra cómo extraer la segunda columna que contiene las rutas. Esta llamada es similar a la consulta SQL SELECT Track from track.

miller cut{.img-responsive}

Otros

La lista de herramientas útiles puede extenderse por un tiempo. D. Bohdan mantiene una bastante lista completa. Entre otros, contiene términosql, jq, así como SPAWK que es un basado en AWK en SQL.

Cosas divertidas, que trataremos con más detalle en uno de los próximos artículos.

Agradecimientos

El autor desea agradecer a Axel Beckert por sus comentarios críticos mientras preparaba este artículo. culo.

Licensed under CC BY-NC-SA 4.0