Ficheros de texto a bases de datos SQL. Un caso práctico

Nuestra tarea es seleccionar algunas de esas columnas que se pueden encontrar separadas por un “pipe” para crear una tabla en una base de datos tipo SQL (SQLite, MySQL, Postgres, etc.) Es de suponer que cuando dicho fichero* tiene miles de registros la tarea se complica. Para ello vamos a utilizar tres herramientas super potentes: Awk, nl y SQLiteManager, las cuales son muy fáciles de usar.

1. Antecedentes

Tenemos creada nuestra tabla en la base de datos. Primero vamos a examinar cuáles columnas del fichero de texto corresponden con los campos de nuestra tabla. A cada uno de esos campos lo vamos a asociar con un número. Por ejemplo, si nuestro fichero tiene 20 columnas y estas están separadas por un pipe (|). En realidad se pueden separar por otro FS (field separator) como una coma, o un espacio. En este ejemplo, utilizamos el pipe. y a nosotros solo nos interesan las columnas 1, 2, 3, 4, 5, 7, 8, 10, 11 y 14 la selección sería:

$1 "," $2 "," $3 "," $4 "," $5 "," $7 "," $8 "," $10 "," $11 "," $14. 

2. Just Testing

Hagamos una prueba: Abrimos la terminal y escribimos el comando:

awk -F”|” ‘{ print $1 “,” $2 “,” $3 “,” $4 “,” $5 “,” $7 “,” $8 “,” $10 “,” $11 “,” $14 }’ foo.txt

Aquí notamos que el separador de campos, Field Separator (FS), es un pipe; enseguida es necesario abrir unas comillas simples y dentro un par de llaves que es donde insertaremos la acción a ejecutar. Dentro de las llaves le damos un “print” a las columnas a imprimir las cuales irán separadas por unas comillas y dentro unas comas. Fuera de las comillas simples, el siguiente argumento del comando awk es el fichero de texto que deseamos transformar. 

OJO: Antes de darle “enter” es necesario considerar el tamaño del fichero. Si es de varios MB sería conveniente tener una versión reducida para ir experimentando con las salidas generadas y verificar lo que realmente requerimos.

3. La papa

Una vez que ya tenemos el resultado esperado, simplemente rederigimos la salida del comando: al fichero correspondiente:

awk -F"|" '{ print $1 "," $2 "," $3 "," $4 "," $5 "," $7 "," $8 "," $10 "," $11 "," $14 }' foo.txt > foo.csv

Ademas hay que señalar que nuestra tabla va indexada y el fichero de texto original no incluye ningún campo del tipo id… Estelo vamos a agregar con nl:

nl -s, foo.csv > foo2.csv

A continuación vamos a crear una base de datos en sqlite3, luego crearemos la tabla correspondiente y posteriormente la poblaremos con el contenido de “foo2.csv”

a) Crear db: sqlite3 codigos_postales.db

b) Crear la tabla con un script SQL: 

sqlite> .read createTable.sql

sqlite> .table <- Verificamos la tabla “foo” recién creada

4. SQLiteManager

Hasta ahora únicamente hemos trabajado con la terminal. Llego la hora de abrir el Firefox e instalarle un plugin super-recomendado: SQLiteManager (Así como hay un phpMyAdmin para MySQL, hay un SQLiteManager para el SQLite Instálenlo… no se arrepentirán

Desde ahí haremos la importación de nuestro csv y poblaremos la tabla. En la imagen se presenta la tabla cp en la base de datos códigos, también podremos hacer queries y otras operaciones.

Con el Asistente de importación la tarea se facilita aún más:

Y listo, unas cuantas consultas:

5. Importación de bases de datos

Un último comentario: Para MySQL hay un script en Bash, mysql2sqlite.sh,que permite la importación de una base de datos de MySQL a SQLite el cual hace uso extensivo del Awk para lograr la importación. Un ejemplo de uso sería:

./mysql2sqlite.sh -u root -p’tupasswd’ foo | sqlite3 database.sqlite

Aunque el ejemplo está hecho en SQLite, la importación a otros RDBM como MySQL es casi igual de fácil. Se pueden utilizar herramientas como el phpMyAdmin o algunos otras utilerías. Es el mismo caso para PostgreSQL u Oracle. 

Notas

*Por el momento no vamos a tratar hojas de cálculo Excel. Eso lo vere en un post  de cómo utilizar módulos, como el Spreadsheet::ParseExcel en Perl para crear scripts que nos permitan la conversión xls->csv. 

Dónde leer más de esto:

IBM developerWorks

LinuxJournal

Awk Guide <- En particular este es una de los mejores y más completas guías para el Awk

SQLite Guía breve <- Breve guía de opciones para la línea de comandos de sqlite3 

SQLite Manager-Plugin del Firefox <- SQLite Manager Excelente plugin para el Firefox y que permite importar ficheros csv con múltiples formatos

https://github.com/lazierthanthou <- Repo del desarrollador de SQLiteManger. 

Una “manita de gato” pal bló

En estos días retomaremos la blogueada. Mientras tanto, a mejorar algunas cosas

Determinar la cantidad de memoria que utiliza una aplicación en Linux

Introducción
Al estar corriendo una aplicación en servidores compartidos se vuelve necesario tener control de la cantidad de memoria RAM que utiliza un comando y/o aplicación. Para ello sólo hacemos un ps con los siguientes argumentos:

ps -u usuario -o pid,rss,command

En esta entrada vamos a ver cómo mejorar y detallar la info de esto con Perl. De paso también vamos a ver cómo interactúa Perl con otros lenguajes de programación por medio de la función system.
Detalles
La primer columna de la salida de este comando es el PID. La segunda columna es la que nos interesa. Nos marca la memoria en MB que utiliza nuestra aplicación. Finalmente la tercer columna nos marca el comando que ejecutamos.

Podemos seguir aumentando los argumentos para hacer más específica la medición, por ejemplo:

ps -u usuario -o rss,etime,pid,command | grep 'comando'

Tuercas y tornillos
Vamos a necesitar lo siguiente:

Perl

Ruby

yacpi

La maquinaria
Si lo anterior lo vamos a hacer repetidas ocasiones podríamos hacer un script para facilitarnos la existencia y al mismo tiempo obtener más información útil, por ejemplo la temperatura de nuestro equipo. A continuación un script en Perl el cual llama a otro script en Ruby (sólo quería ver como utilizar a Perl como “glue language” ) :


#!/usr/bin/perl -w
#@author  zahori
use strict;
use warnings;
print "This script measures the total memory and
gives you battery, ac adapter, fan and thermal
information used by root\n";
my $USER = "root";
my @process = `ps --no-headers -u $USER -o rss,pid,etime,command --sort rss`;
print "Memory(MB)\tPID\tCOMMAND\n";
print '-' x 128, "\n";
foreach my $proc(@process){
my @process = (my $stuff, my $mmem, my $pid, my $etimeComand) = split /(\d+)\s+(\d+)\s+(.+)/, $proc;
my $mem = $process[1]/1024;
print $mem, "\t",$process[2], "\t",$process[3],"\n";
}
print "The total amount of memory used is:\n";
### Ruby script
my @args = ("ruby", "/home/usuario/perl/test.rb");
system(@args) == 0 or die "The arguments are wrong: $?";
print '-' x 128, "\n";
#Ejecutamos el comando yacpi para determinar condiciones
print "The current conditions at the system are:\n";
print `yacpi -p`;


El script en Ruby más o menos va de esta forma:

#!/usr/bin/env ruby
total = 0.0
`ps --no-headers -u root -o pid,rss,command --sort rss`.split("\n").each do |p|
p =~ /(\d+)\s+(\d+)\s+(.+)/
pid, rss, command = [$1, $2, $3]
rss = rss.to_f
total += rss
end
puts "%.2f MB" % (total / 1024)

Si queremos ejecutar el script desde otro equipo, suponiendo que estamos administrando en forma remota podemos usar ssh

 ssh -l usuario 192.168.1.x ~/perl/memRoot.pl

Conclusiones
El Perl ofrece muchas ventajas al momento de interactuar con otros lenguajes interpretados como Ruby o Python y ya no se diga con la Bash. En algunos casos conviene hacer uso de esta facilidad de interacción. Los scripts anteriores podemos automatizarlos para que el sistema nos avise cuando alguna aplicación está rebasando el límite de memoria contratado o impuesto por las políticas de uso del equipo.