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 plano 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 utilizando las herramientas de línea de comandos cut, join, head, comm, sedy 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 del archivo tracks.csv están delimitados por pestañas, y el script awk totaliza los valores en la tercera columna, solo.

$ 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 kmn", 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 estar formuladas en lenguaje de consulta estructurado (SQL) y evaluadas por el DBMS.

La siguiente declaración muestra cómo extraer el contenido de la columna Track de la tabla denominada track:

SELECT Track FROM track
Paris-Metz
Metz-Nancy
Nancy-Vesoul
Vesoul-Mulhouse
(4 lines)

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

q

La primera herramienta se llama simplemente q, y su nombre se deriva de El Q Continuum que se menciona en la serie Star Trek. El paquete Debian correspondiente para q es nombrado Python-q-texto-como-datos. q acepta sentencias SQL y permite la ejecución directa en uno o más archivos CSV.

A continuación verá cómo extraer la primera columna denominada Date desde el archivo CSV.

$ 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 tabulaciones.

Para las otras banderas le recomendamos que eche un vistazo a la q página de uso.

Para recuperar conjuntos de datos según las 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:

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

q sigue el SQL sintaxis utilizada por SQLite. Según el sitio web del proyecto, cualquier expresión SQL estándar, condiciones (tanto WHERE y HAVING), GROUP BY, ORDER BY, etc.están permitidos. en un WHERE cláusula JOINsy subconsultas también son compatibles.

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 utilice una declaración como sigue que demuestre la SUM() función:

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

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

$ 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 sqlite de Python original. Una funcionalidad similar la proporciona el querycsv Módulo de Python, que parece estar bifurcado como el querycsv-redux proyecto.

fsql

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

# 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 el fsql página del proyecto, la herramienta le permite realizar consultas SQL en uno o varios archivos «planos» de varios formatos, como texto plano, CSV y JSON.

El siguiente ejemplo demuestra cómo extraer todos los viajes que tienen una distancia de más de 100 km. Los datos provienen de stdin utilizando cat, y canalizado a fsql, entonces:

$ 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 aquí, fsql produjo una salida basada en líneas que es común para las bases de datos. Usando el parámetro de salida -f además del formato de salida deseado, puede hacer mucho más. Entre otros, fsql admite valores separados por comas (csv), valores separados por tabuladores (tsv), Perl (perl), Notación de objetos JavaScript (JSON) (json) y YAML no es lenguaje de marcado (YAML) (yaml).

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

$ 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 está formateada en estilo YAML:

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

Molinero

Preguntar a expertos en 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, joiny sort.

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

Otros

La lista de herramientas útiles se puede ampliar por un tiempo. D. Bohdan mantiene una actitud bastante lista completa. Entre otros, esto contiene termsql, jq, así como SPAWK que es un AWK con SQL.

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