Nella gestione dei database spesso importiamo (ed esportiamo) direttamente i dati in formato SQL all'interno dei nostri backup.
Ma non sempre ci è permesso, poiché in alcuni casi il file messo a disposizione è quello ponte in formato CSV.
Il programma PHPMyAdmin (come pubblicato in precedenza in uno degli articoli per il dEC System IV), può importare i dati (entro i limiti del 128MB che abbiamo impostato nel PHP.INI di versione), ma la sua trasposizione tra formato ponte a formato SQL può essere lungo e complesso.
Addirittura sui sistemi MySQL di Aruba.it con file di notevoli dimensioni (in termini di record più che di dati in se) si può arrivare anche al Gateway timeout senza aver caricato tutto il pacchetto dati.
Il caso specifico a cui mi riferisco è relativo all'archivio dati di PoWo (Plant of the World della Royal Botanic Garden Britannica) che condivide il file del suo archivio dati in formato ponte CSV e non in formato SQL standard.
Le motivazioni alla base possono essere molteplici, ma in generale un plain-text come CSV è fruibile su diversi dispositivi (MySQL e cloni, Access, SQLServer, ecc)
Come importare un pacchetto dati CSV in MySQL
Il persupposto base è quello di poter usare un server (tipo CentOS, AlmaLinux, Ubuntu, ecc) sul quale sono installati un programma FTP e, ovviamente, un DataBase MySQL o fork (MariaDB, PerconaDB, ecc)
Col programma FTP caricare nella cartella remota /var/lib/mysql/piante (dove piante è il nome della collezione dati, ma potrebbe chiamarsi anche pippo ecc) il file CSV da voler importare
Creare (anche tramite l'interfaccia PHPMyAdmin ) a tabella sulla quale si vogliono caricare i dati, ovviamente conforme al tipo di dati del pacchetto.
Nel caso di PoWo, il file da oltre 400MB wcvp_taxon.csv, la prima riga indica i nomi della colonne della tabella:
taxonid|family|genus|specificepithet|infraspecificepithet|scientfiicname|scientfiicnameauthorship|taxonrank|taxonomicstatus|acceptednameusageid|parentnameusageid|originalnameusageid|namepublishedin|nomenclaturalstatus|taxonremarks|scientificnameid|dynamicproperties|references
Ovviamente prima di inserire i dati si deve necessariamente costruire la tabella (a mo' di esempio):
CREATE TABLE `wcvp_taxon` (
`taxonid` int(10) NOT NULL,
`family` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`genus` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`specificepithet` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`infraspecificepithet` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`scientfiicname` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`scientfiicnameauthorship` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`taxonrank` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`taxonomicstatus` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`acceptednameusageid` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`parentnameusageid` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`originalnameusageid` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`namepublishedin` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`nomenclaturalstatus` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`taxonremarks` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`scientificnameid` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`dynamicproperties` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`references` varchar(255) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Cliccando su Esegui a destra (non si vede nell'immagine) si crea al tabella.
Accediamo alla console ed andiamo alla cartella /var/lib/mysql/piante tramite il com,ando cd:
cd /var/lib/mysql/piante
A questo punto dobbiamo accedere tramite console al database:
mysql -u [username] -p [e premere Invio poi mettere password quando richiesta]
Ovviamente username è generalmente, su un server root, e la password è quella scelta in fase di registrazione (se non cambiata)
A questo punto dobbiamo scrivere lo script per importare i dati da csv:
LOAD DATA INFILE 'wcvp_taxon.csv'
INTO TABLE wcvp_taxon
CHARACTER SET UTF8
FIELDS TERMINATED BY '|'
ENCLOSED BY ''
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
La riga 3 dice il tipo di formato carattere da importare, mentre l'ultima dice di saltare l'ultima riga
La riga 4 dice come terminano i campi
La riga 5 chiede come sono definiti i dati (generalmente tramite ", ma in questo caso no)
La riga 6 chiede come termina la riga: \n indica che la riga finisce quando va a capo.
Il tempo di importazione è estremamente rapido:
Circa 10 secondi per 1.431.677 righe o record
A questo punto è possibile esportarla come SQL standard e/o lavorarci sopra
Commenti