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.

    Etiquetas:

    Deja una respuesta

    Tu direcci贸n de correo electr贸nico no ser谩 publicada. Los campos obligatorios est谩n marcados con *