Caricare file CSV nel database
ven, 2026-02-06 18:04 —
Shinguz
Recentemente, per un piccolo gioco personale, ho voluto rappresentare su una mappa i luoghi di residenza dei membri della mia associazione (IGOC Mitglieder). Conoscevo gli indirizzi dei membri dell’associazione, ma non le coordinate dei loro luoghi di residenza.
Così mi sono messo alla ricerca delle coordinate e le ho trovate presso l’Ufficio federale di topografia (swisstopo).
I dati sono disponibili in formato CSV. Maggiori dettagli qui: Coordinate delle località svizzere.
Come si caricano questi dati in un database?
Caricamento dei dati con MariaDB/MySQL
MariaDB e MySQL dispongono del comando LOAD DATA INFILE:
SQL> DROP TABLE IF EXISTS wgs84;
SQL> -- SET GLOBAL local_infile = ON; -- Only needed with MySQL
SQL> CREATE TABLE wgs84 (
ortschaftsname VARCHAR(32)
, plz4 SMALLINT
, zusatzziffer SMALLINT
, zip_id SMALLINT UNSIGNED
, gemeindename VARCHAR(32)
, bfs_nr SMALLINT
, kantonskuerzel CHAR(2)
, adressenanteil varchar(8)
, e DOUBLE
, n DOUBLE
, sprache VARCHAR(8)
, validity VARCHAR(12)
);
SQL> -- TRUNCATE TABLE wgs84;
SQL> LOAD DATA LOCAL INFILE '/tmp/AMTOVZ_CSV_WGS84/AMTOVZ_CSV_WGS84.csv'
INTO TABLE wgs84
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
;
Query OK, 5713 rows affected
Records: 5713 Deleted: 0 Skipped: 0 Warnings: 0
Successivamente è possibile interrogare i dati nel database:
SQL> SELECT * FROM wgs84 ORDER BY ortschaftsname LIMIT 5;
+----------------+------+--------------+--------+--------------+--------+----------------+----------------+-------------------+--------------------+---------+------------+
| ortschaftsname | plz4 | zusatzziffer | zip_id | gemeindename | bfs_nr | kantonskuerzel | adressenanteil | e | n | sprache | validity |
+----------------+------+--------------+--------+--------------+--------+----------------+----------------+-------------------+--------------------+---------+------------+
| Aadorf | 8355 | 0 | 4672 | Aadorf | 4551 | TG | 96.802 % | 8.903193007810433 | 47.491079014637265 | de | 2008-07-01 |
| Aadorf | 8355 | 0 | 4672 | Elgg | 294 | ZH | 3.198 % | 8.89206766645808 | 47.4933781685032 | de | 2008-07-01 |
| Aarau | 5000 | 0 | 2913 | Aarau | 4001 | AG | 99.713 % | 8.048148371736266 | 47.38973523857376 | de | 2008-07-01 |
| Aarau | 5000 | 0 | 2913 | Suhr | 4012 | AG | 0.287 % | 8.059410934099922 | 47.383298214804334 | de | 2008-07-01 |
| Aarau | 5004 | 0 | 2932 | Aarau | 4001 | AG | 100 % | 8.060698546432551 | 47.400587704180744 | de | 2008-07-01 |
+----------------+------+--------------+--------+--------------+--------+----------------+----------------+-------------------+--------------------+---------+------------+
5 rows in set
O, per essere più precisi:
SQL> SELECT ortschaftsname AS city, plz4 AS city_code, e AS lon, n AS lat
FROM wgs84 WHERE plz4 IN (8280, 4663, 6043);
+-------------+-----------+-------------------+--------------------+
| city | city_code | lon | lat |
+-------------+-----------+-------------------+--------------------+
| Aarburg | 4663 | 7.904271716719409 | 47.321443418782955 |
| Aarburg | 4663 | 7.889249714098425 | 47.313536073562474 |
| Aarburg | 4663 | 7.880309179095798 | 47.31255194439023 |
| Adligenswil | 6043 | 8.364849060491428 | 47.07037816052481 |
| Kreuzlingen | 8280 | 9.173740257895282 | 47.64491046067056 |
| Kreuzlingen | 8280 | 9.159171428030783 | 47.654149879509134 |
| Kreuzlingen | 8280 | 9.204470741840725 | 47.639949130372145 |
+-------------+-----------+-------------------+--------------------+
7 rows in set (0.003 sec)
Lascio al gentile lettore o alla gentile lettrice il compito di eliminare i duplicati… :-)
Fin qui tutto bene, passiamo ora alle sottigliezze:
Differenze tra MariaDB e MySQL
La procedura sopra descritta funziona perfettamente con MariaDB 11.4 e 11.8. Con MySQL 8.4 ci sono piccole differenze:
Il primo messaggio di errore che impedisce il caricamento è questo:
ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides
Può essere aggirato in modo relativamente semplice con il comando:
SQL> SET GLOBAL local_infile = ON;
Al prossimo tentativo si fallirà come segue:
ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.
Questo problema può essere risolto avviando il client MySQL come segue:
$ mysql --local-infile=1 --user=root test
Fonti
- MariaDB: LOAD DATA INFILE
- MySQL: LOAD DATA Statement
Caricamento dei dati con PostgreSQL
PostgreSQL dispone del comando COPY ... FROM:
postgres=# DROP TABLE IF EXISTS wgs84;
postgres=# CREATE TABLE wgs84 (
ortschaftsname VARCHAR(32)
, plz4 SMALLINT
, zusatzziffer SMALLINT
, zip_id INT
, gemeindename VARCHAR(32)
, bfs_nr SMALLINT
, kantonskuerzel CHAR(2)
, adressenanteil varchar(8)
, e DOUBLE PRECISION
, n DOUBLE PRECISION
, sprache VARCHAR(8)
, validity VARCHAR(12)
);
postgres=# -- TRUNCATE TABLE wgs84;
postgres=# COPY wgs84
FROM '/tmp/AMTOVZ_CSV_WGS84/AMTOVZ_CSV_WGS84.csv'
DELIMITER ';'
CSV HEADER
;
COPY 5713
Anche in questo caso otteniamo il risultato previsto nella forma usuale per PostgreSQL:
postgres=# SELECT ortschaftsname AS city, plz4 AS city_code, e AS lon, n AS lat
FROM wgs84 WHERE plz4 IN (8280, 4663, 6043);
city | city_code | lon | lat
-------------+-----------+-------------------+--------------------
Aarburg | 4663 | 7.904271716719409 | 47.321443418782955
Aarburg | 4663 | 7.889249714098425 | 47.313536073562474
Aarburg | 4663 | 7.880309179095798 | 47.31255194439023
Adligenswil | 6043 | 8.36487538940682 | 47.07037794822416
Kreuzlingen | 8280 | 9.173740257895282 | 47.64491046067056
Kreuzlingen | 8280 | 9.159171428030783 | 47.654149879509134
Kreuzlingen | 8280 | 9.204470741840725 | 47.639949130372145
(7 rows)
Fonti
- PostgreSQL: COPY
Piccole differenze tra MariaDB/MySQL e PostgreSQL
In linea di principio, il comando di caricamento è completamente diverso nei due mondi di database.
In MariaDB e PostgreSQL i comandi funzionano “out-of-the-box”. MySQL ha integrato due ulteriori barriere di sicurezza.
PostgreSQL non conosce i tipi di dati interi USINGED, quindi è necessario utilizzare il tipo di dati immediatamente superiore (INT), che occupa leggermente più spazio rispetto a MariaDB/MySQL.
Osservazioni
Quando abbiamo eseguito lo stesso test pochi giorni fa, si è verificato un errore durante il caricamento. Sembra quindi che anche la fonte dei dati sia leggermente cambiata…
Non sono riuscito a capire rapidamente se esiste uno standard SQL per questi comandi di caricamento e, in caso affermativo, se MariaDB/MySQL o PostgreSQL sono conformi allo standard.
E naturalmente ci sono anche altri modi per inserire i propri dati CSV nel database…
Gli strumenti mariadb-import/mysqlimport vengono utilizzati se si desidera eseguire l’operazione dalla riga di comando. Anche il CSV Storage Engine può essere utilizzato a questo scopo (per i dettagli vedere qui). Una variante ufficialmente supportata è il motore di archiviazione MariaDB CONNECT con il tipo CSV (vedi qui):
SQL> INSTALL SONAME 'ha_connect';
SQL> CREATE TABLE wgs84_fdw
ENGINE = CONNECT
table_type = CSV
file_name='/tmp/AMTOVZ_CSV_WGS84/AMTOVZ_CSV_WGS84.csv'
header = 1
sep_char = ';'
quoted = 0;
SQL> INSERT INTO wgs84 SELECT * FROM wgs84_fdw;
A quanto pare, il motore di archiviazione CONNECT di MariaDB non è più supportato?!? Anche lo strumento mydumper/myloader sembra essere in grado di gestire i file CSV.
E naturalmente il tutto può essere risolto anche a livello applicativo…
Con PostgreSQL ci sono le seguenti possibilità:
postgres=# \copy wgs84 FROM '/tmp/AMTOVZ_CSV_WGS84/AMTOVZ_CSV_WGS84.csv' DELIMITER ';' CSV HEADER
quindi dalla shell:
$ psql --user=dba -c "\copy wgs84 FROM '/tmp/AMTOVZ_CSV_WGS84/AMTOVZ_CSV_WGS84.csv' DELIMITER ';' CSV HEADER"
E la variante tramite Foreign Data Wrapper (FWD). Non l’ho però provata:
postgres=# CREATE EXTENSION postgres_fdw;
postgres=# CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (
datasource 'CSV:/tmp/AMTOVZ_CSV_WGS84/AMTOVZ_CSV_WGS84.csv',
format 'CSV'
)
;
postgres=# CREATE USER MAPPING FOR local_user
SERVER foreign_server
OPTIONS (user 'foreign_user', password 'password')
;
postgres=# CREATE FOREIGN TABLE foreign_table (
id integer NOT NULL,
data text
)
SERVER foreign_server
OPTIONS (schema_name 'some_schema', table_name 'some_table')
;
Questa pagina è stata tradotta utilizzando deepl.com.
