Salta el contingut

Import i exportació de dades

Per què cal moure dades entre sistemes

Cap base de dades viu aïllada. Un DBA es troba constantment amb la necessitat de moure dades cap a fora o cap a dins del SGBD, per motius molt diferents del backup:

  • Migracions: canviar de motor (per exemple, d'Oracle a PostgreSQL) o de proveïdor de núvol.
  • Integració entre sistemes: un ERP exporta un fitxer CSV cada nit que un altre sistema ha d'importar.
  • Càrregues massives inicials: poblar una base de dades nova amb dades històriques que arriben en Excel, CSV o JSON.
  • Intercanvi amb tercers: enviar un extracte de dades a un client, un auditor o una administració pública en un format estàndard.
  • Anàlisi de dades: exportar un resultat a CSV perquè un analista el treballi amb Python, R o Excel.

A diferència del backup (que reprodueix l'estat exacte d'una BD per recuperar-la en el mateix motor), la importació i exportació de dades treballa amb formats oberts i portables (CSV, JSON, XML) que qualsevol sistema pot llegir, independentment del SGBD d'origen o destí.

Qüestionari inicial

  1. Quina diferència hi ha entre fer un backup amb pg_dump i exportar una taula a CSV?
  2. Per què un fitxer CSV és més portable entre sistemes que un backup binari?
  3. Quins problemes esperaries si has d'importar un CSV amb 2 milions de files a una taula amb una clau forana?
  4. Com creus que es podria detectar que una importació ha fallat a mitges?

Eines d'importació i exportació per motor

Cada SGBD ofereix eines natives, tant en línia d'ordres com gràfiques, per moure dades des de i cap a fitxers externs. Totes comparteixen el mateix objectiu, però amb sintaxi i comportament diferents.

-- COPY: la comanda nativa, molt més ràpida que INSERT fila a fila.
-- Cal executar-la des del servidor (accés al sistema de fitxers) o des de psql amb \copy (des del client).

-- Exportar una taula sencera a CSV
COPY clients TO '/tmp/clients.csv' WITH (FORMAT csv, HEADER true);

-- Exportar el resultat d'una consulta
COPY (SELECT id, nom, total FROM comandes WHERE data >= '2027-01-01')
TO '/tmp/comandes_2027.csv' WITH (FORMAT csv, HEADER true, DELIMITER ';');

-- Des del client (psql), quan no es té accés al sistema de fitxers del servidor
-- \copy clients TO 'clients.csv' WITH (FORMAT csv, HEADER true)
Eines gràfiques: pgAdmin (assistent d'importació/exportació a la pestanya Import/Export Data), DBeaver (assistent universal de transferència de dades).

-- SELECT ... INTO OUTFILE: equivalent a COPY TO, escriu des del servidor
SELECT id, nom, total
FROM comandes
WHERE data >= '2027-01-01'
INTO OUTFILE '/var/lib/mysql-files/comandes_2027.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

# mysqldump també pot exportar en format tabular (TSV), no només SQL
mysqldump --tab=/tmp/export gbd_practica clients

# mysqlsh (MySQL Shell) permet exportar a JSON directament
mysqlsh --sql -e "SELECT * FROM clients" --result-format=json > clients.json
Eines gràfiques: MySQL Workbench (Table Data Export/Import Wizard), DBeaver.

# bcp (Bulk Copy Program): eina de línia d'ordres nativa
bcp GBD_Practica.dbo.Clients out clients.csv -c -t, -S localhost -U sa -P "Contrasenya123!"

-- Des de T-SQL, amb l'assistent SSMS (Import/Export Wizard) o BCP integrat
SELECT * FROM Clients
FOR JSON AUTO;  -- exporta el resultat directament com a JSON
Eines gràfiques: SQL Server Management Studio (Import/Export Wizard), Azure Data Studio.

# Data Pump: eina moderna d'exportació/importació (diferent de expdp/impdp orientat a backup lògic complet)
# Per a extractes puntuals, SQL*Plus amb SPOOL és habitual:
sqlplus usuari/contrasenya @extreu_csv.sql

-- extreu_csv.sql
SET COLSEP ','
SET PAGESIZE 0
SET TRIMSPOOL ON
SPOOL clients.csv
SELECT id || ',' || nom || ',' || total FROM clients;
SPOOL OFF
Eines gràfiques: Oracle SQL Developer (Export Wizard, suporta CSV, JSON, XML, INSERT).

AC0372/06/08 — Miniactivitat

RA6 · CA6.4

Identifiqueu, per als 4 motors de treball, quina eina de línia d'ordres i quina eina gràfica utilitzaríeu per exportar el contingut d'una taula a CSV. Elaboreu una taula comparativa amb: nom de l'eina, si cal accés al sistema de fitxers del servidor o no, i un exemple mínim de comanda.

Exportació a diferents formats

El format de sortida condiciona qui podrà llegir les dades després. Els tres formats més habituals en un entorn professional són:

Format Quan usar-lo Limitacions
CSV Fulls de càlcul, càrregues massives, la majoria d'eines d'anàlisi No representa bé dades jeràrquiques o niades; problemes amb comes/salts de línia dins dels valors
JSON APIs, integració amb aplicacions web, dades semiestructurades Fitxers més grans que CSV per a dades tabulars simples
XML Sistemes empresarials/administració pública que encara l'exigeixen, intercanvi amb estàndards sectorials Molt verbós; poc usat per a noves integracions 
-- CSV (ja vist a la secció anterior)
COPY clients TO '/tmp/clients.csv' WITH (FORMAT csv, HEADER true);

-- JSON: construir un array JSON amb json_agg
COPY (
    SELECT json_agg(row_to_json(c))
    FROM clients c
) TO '/tmp/clients.json';

-- XML: PostgreSQL disposa de funcions natives per a XML
SELECT xmlelement(name clients,
    xmlagg(xmlelement(name client, xmlattributes(id, nom))))
FROM clients;

-- JSON amb JSON_ARRAYAGG i JSON_OBJECT (MySQL 8+)
SELECT JSON_ARRAYAGG(JSON_OBJECT('id', id, 'nom', nom, 'total', total))
FROM comandes;

-- JSON natiu amb FOR JSON
SELECT id, nom, total FROM Comandes FOR JSON AUTO;

-- XML natiu amb FOR XML
SELECT id, nom, total FROM Comandes FOR XML AUTO, ROOT('Comandes');

-- JSON amb JSON_OBJECT i JSON_ARRAYAGG (Oracle 12c+)
SELECT JSON_ARRAYAGG(
    JSON_OBJECT('id' VALUE id, 'nom' VALUE nom)
)
FROM clients;

Codificació de caràcters

Un dels errors més freqüents en exportar/importar entre sistemes és la codificació de caràcters (encoding). Un fitxer generat en LATIN1 i importat assumint UTF-8 (o viceversa) corromp accents i caràcters especials silenciosament. Comproveu sempre l'encoding d'origen i destí (SHOW SERVER_ENCODING a PostgreSQL, SHOW VARIABLES LIKE 'character_set%' a MySQL).

AC0372/06/09 — Miniactivitat

RA6 · CA6.5

Exporteu la mateixa consulta (una llista de clients amb almenys un camp de text amb accents) en format CSV i en format JSON. Compareu la mida dels dos fitxers i identifiqueu quin dels dos representaria millor un client amb múltiples telèfons de contacte.

Importació amb diferents formats

La importació és, en general, més delicada que l'exportació: cal validar que les dades entrants respecten els tipus, les restriccions i les claus foranes de la taula destí.

-- Importar un CSV a una taula existent
COPY clients (id, nom, email, total)
FROM '/tmp/clients_nous.csv'
WITH (FORMAT csv, HEADER true);

-- Importar ignorant files amb errors de format (des de PostgreSQL 17, amb ON_ERROR)
COPY clients FROM '/tmp/clients_nous.csv'
WITH (FORMAT csv, HEADER true, ON_ERROR ignore, LOG_VERBOSITY verbose);

-- LOAD DATA: equivalent a COPY FROM, molt més ràpid que INSERT fila a fila
LOAD DATA INFILE '/var/lib/mysql-files/clients_nous.csv'
INTO TABLE clients
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;  -- salta la capçalera


-- BULK INSERT: càrrega massiva des d'un fitxer
BULK INSERT Clients
FROM 'C:\dades\clients_nous.csv'
WITH (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    FIRSTROW = 2,       -- salta la capçalera
    TABLOCK
);

# bcp també permet importar
bcp GBD_Practica.dbo.Clients in clients_nous.csv -c -t, -S localhost -U sa -P "Contrasenya123!"


-- SQL*Loader: l'eina clàssica d'Oracle per a càrregues massives
-- Fitxer de control (clients.ctl):
LOAD DATA
INFILE 'clients_nous.csv'
INTO TABLE clients
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(id, nom, email, total)

sqlldr usuari/contrasenya control=clients.ctl log=clients.log bad=clients.bad

Ordre d'importació i integritat referencial

Si les taules tenen claus foranes, cal importar-les en l'ordre correcte (primer les taules "pare", després les "filles"), o desactivar temporalment la comprovació de restriccions durant la càrrega i reactivar-la després verificant que les dades són consistents. Importar en l'ordre incorrecte és una de les causes més habituals de fallada en una càrrega massiva.

AC0372/06/10 — Miniactivitat

RA6 · CA6.6

Prepareu un fitxer CSV amb 20 files de dades noves per a una taula amb clau forana (per exemple, comandes que referencien clients). Proveu de carregar-lo abans i després d'inserir els clients corresponents, i documenteu l'error exacte que apareix quan l'ordre és incorrecte.

Interpretació de missatges d'error i fitxers de registre

Una importació massiva sol fallar parcialment, no tot o res. Saber llegir el missatge d'error i el fitxer de registre (log) és imprescindible per corregir el problema sense haver de repetir tota la càrrega des de zero.

ERROR:  invalid input syntax for type integer: "N/A"
CONTEXT: COPY clients, line 145, column edat: "N/A"
El missatge indica exactament la línia i la columna del fitxer on ha fallat la conversió de tipus. Amb ON_ERROR ignore (PostgreSQL 17+), les files amb error es descarten i es poden consultar amb LOG_VERBOSITY verbose.

-- LOAD DATA informa d'avisos, no sempre errors bloquejants
SHOW WARNINGS;
-- +---------+------+------------------------------------------+
-- | Level   | Code | Message                                   |
-- +---------+------+------------------------------------------+
-- | Warning | 1366 | Incorrect integer value: 'N/A' for column |
-- +---------+------+------------------------------------------+


-- BULK INSERT genera un fitxer d'errors amb ERRORFILE
BULK INSERT Clients FROM 'clients.csv'
WITH (FIELDTERMINATOR = ',', ERRORFILE = 'clients_errors.log', MAXERRORS = 50);
El fitxer .log associat conté, per a cada fila rebutjada, el número de fila i el motiu del rebuig.

-- SQL*Loader genera SEMPRE 3 fitxers:
clients.log   -- resum: files llegides, carregades, rebutjades, descartades
clients.bad   -- files que no s'han pogut carregar (amb el motiu al .log)
clients.dsc   -- files descartades per no complir una condició WHEN

Estratègia general davant d'un error d'importació

  1. Llegiu el número de línia/fila que indica l'error — gairebé sempre hi apareix.
  2. Identifiqueu si és un problema de tipus de dada, de format (delimitador, encoding) o d'integritat referencial.
  3. Decidiu si val la pena descartar la fila (dada purament errònia) o corregir-la a l'origen i tornar a provar.
  4. En càrregues grans, treballeu sempre amb el mecanisme natiu de "fitxer de rebutjats" del motor (.bad a Oracle, ERRORFILE a SQL Server, ON_ERROR/log a PostgreSQL) en lloc d'aturar tota la càrrega al primer error.

AC0372/06/11 — Miniactivitat

RA6 · CA6.7

Proveu de carregar un CSV amb almenys 3 files defectuoses (un tipus de dada incorrecte, un delimitador mal col·locat i una violació de clau forana). Documenteu, per a cadascuna, el missatge d'error exacte i a quin fitxer o comanda heu hagut de consultar per trobar-lo.

Transferència de dades entre sistemes gestors diferents

Quan origen i destí són motors diferents, cal afegir un pas intermedi: exportar en un format neutre, adaptar els tipus de dades si cal, i importar-lo al motor destí.

flowchart LR
    A[("SGBD origen\n(p. ex. Oracle)")] -->|"1. Exportar"| F["Fitxer intermedi\nCSV / JSON"]
    F -->|"2. Adaptar tipus\nde dades si cal"| F2["Fitxer adaptat"]
    F2 -->|"3. Importar"| B[("SGBD destí\n(p. ex. PostgreSQL)")]

Punts a tenir en compte en una transferència entre motors:

  • Tipus de dades no equivalents: un NUMBER d'Oracle no es mapeja igual a PostgreSQL (NUMERIC, INTEGER...) que a MySQL (DECIMAL, INT...). Cal revisar la taula d'equivalències (vegeu Creació de bases de dades).
  • Autoincrementals: SERIAL/IDENTITY (PostgreSQL), AUTO_INCREMENT (MySQL) i IDENTITY (SQL Server) es comporten de forma similar però no idèntica; en una migració cal decidir si es respecten els valors originals o es regeneren.
  • Dates i zones horàries: el format de data per defecte varia entre motors (YYYY-MM-DD vs DD-MON-YY a Oracle).
  • Eines ETL: per a migracions completes i recurrents, existeixen eines dedicades que automatitzen aquest procés:
    • pgloader: especialitzada a migrar cap a PostgreSQL des de MySQL, SQLite o fitxers pla.
    • DBeaver: assistent universal de transferència de dades entre qualsevol dels motors que suporta.
    • Apache NiFi / Airbyte: eines ETL/ELT de propòsit general per a pipelines de dades recurrents (es veuran amb més profunditat als mòduls de Big Data).

AC0372/06/12 — Miniactivitat

RA6 · CA6.8

Trieu dues taules relacionades (amb clau forana) de la vostra BD de pràctiques en PostgreSQL. Exporteu-les a CSV i importeu-les a MySQL, respectant l'ordre correcte i adaptant els tipus de dades que calgui. Documenteu quins tipus heu hagut de canviar i per què.