Import and Export Data

Importing Records

Records kunnen aan tabellen worden toegevoegd met het INSERT statement. Echter bestaat data in diverse formaten en groottes, en het één voor één invoeren van deze records in geen werkbare techniek, met name wanneer men data sets met honderdduizenden records moet migreren. Om ontwikkelaars te helpen bij de aanpak van dit probleem bieden de diverse database leveranciers verschillende hulpmiddelen die het proces van het importeren en exporteren van gegevens in verschillende formaten aanzienlijk vereenvoudig, zoals:

• Door komma’s gescheiden formaten
• Tabs gescheiden formaten
• En XML-gecodeerde gegevens

De instructie INSERT is niet de enige manier gegevens aan een tabel toe te voegen.

MySQL maakt het mogelijk om meerdere records met de LOAD DATA INFILE instructie toe te voegen. Deze instructie kan worden gebruikt om:

• Onbewerkte data van een tekstbestand te lezen (welke zich bevind op op de server of de cliënt kant van de connectie)
• Het ontleden op basis van de kolom en rij afbakeningen
• Het automatisch genereren van INSERT statements om de gegevens aan een tabel toe te voegen

MSSQL maakt gebruik van een BULK INSERT instructie om meerdere records toe te voegen. Echter dient men rekening te houden met volgende:

• het gebruik van field- en rowseperators
• het opgeven van een bcp (bulk-copy-program) format file, indien dit nodig zou zijn om elke field-seperator afzonderlijk te aan te passen, welke nodig zou zijn in het geval van een “quoted comma delimited’ bestand, of men een andere mapping moet maken met betrekking tot de velden
•  

Standaard is MySQL beperkt waarin de te importeren data mag komen te staan, het moet normaliter in de map “C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/” van de server host staan. MSSQL is niet beperkt tot een bepaalde folder waaruit de data opgehaald kan worden, zo zou men bijvoorbeeld ook een UNC pad kunnen kiezen. Voor meer informatie over het gebruik van bcp en format files zie volgende websites:

• https://msdn.microsoft.com/en-us/library/ms188365.aspx,
• https://msdn.microsoft.com/en-us/library/ms191485.aspx,
• https://blogs.msdn.microsoft.com/sqlserverfaq/2010/02/04/how-to-remove-unwanted-quotation-marks-while-importing-a-data-file/,
• https://msdn.microsoft.com/en-us/library/ms141209.aspx,
• http://dba.stackexchange.com/questions/916/how-do-i-copy-a-table-with-select-into-but-ignore-the-identity-property.

SELECT * INTO imported_passengers from pax
WHERE 1=2;

(0 row(s) affected)

BULK INSERT dbo.imported_passengers
FROM '\\milin.cc\users\public\import_passenger_data_1_quoted.txt' 
WITH ( 
    FIRSTROW = 1,
    FORMATFILE = 
        '\\milin.cc\users\public\import_passenger_data_1.bcp',
    KEEPIDENTITY 
    );

(5 row(s) affected)

BULK INSERT dbo.imported_passengers
FROM '\\milin.cc\users\public\import_passenger_data_1_unquoted.txt' 
WITH ( 
    FIELDTERMINATOR =',', 
    FIRSTROW = 1,
    KEEPIDENTITY,
    ROWTERMINATOR = '\n' 
    );

(5 row(s) affected)

SELECT ClassID, PaxName
FROM imported_passengers
WHERE RecordID > 200;

(5 row(s) returned)

LOAD DATA INFILE 
  'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/import_passenger_data_1.txt'
INTO TABLE imported_passengers
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'; 
-- \r = carriage return; \n = newline character

(5 row(s) affected Records: 5 Deleted: 0 Skipped: 0 Warnings: 0)

SELECT ClassID, PaxName
FROM imported_passengers
WHERE RecordID > 200;

(5 row(s) returned)

Standaard gaat MySQL ervan uit dat het gegevensbestand zich op de server host bevindt, op de locatie die in de LOAD DATA INFILE statement is opgegeven. Er is echter maar één enkele specifieke locatie (of een subdirectory van deze locatie) op de server host geldig: dit is de locatie die is ingesteld in de secure_file_priv systeem variabele. U kunt deze locatie terugvinden door het uitvoeren van de volgende instructie:

LOAD DATA LOCAL INFILE 
  '//milin.cc/users/public/import_passenger_data_1.txt'
INTO TABLE imported_passengers
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

Een typische respons op deze instructie is: C:\ProgramData\MySQL\MySQL Server 5.7\Uploads\. Als u een bestand van (of naar) een andere locatie probeert te importeren (of exporteren) dan de secure-file-priv locatie (of een subdirectory van deze locatie), dan geeft de server een foutmelding:

Error Code: 1290. The MySQL server is running with the –secure-file-priv option so it cannot execute this statement ;

Opmerkingen:

• Als secure_file_priv is ingesteld op een lege tekenreeks (“”), dan zal de server zal de import- en exportbewerkingen vanuit elke bestandslocatie accepteren.
• Als secure_file_priv is ingesteld op NULL, zal de server alle import- en exportbewerkingen uitschakelen.
• De secure_file_priv kan niet dynamisch door middel van de SET-instructie worden gewijzigd terwijl de server draait. Om de secure_file_priv te wijzigen dient men de server te stoppen en opnieuw te starten.

Als men in plaats daarvan een data-bestand van de client gastheer wil gebruiken, kunt u het sleutelwoord LOCAL toevoegen aan het LOAD DATA INFILE statement om aan te geven dat MySQL het bestand op het bestandssysteem van de cliënt moet gebruiken. Het volgende voorbeeld toont dit door het laden van gegevens uit een bestand op de cliënt machine:

LOAD DATA LOCAL INFILE 
  '//milin.cc/users/public/import_passenger_data_1.txt'
INTO TABLE imported_passengers
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

Als er minder velden in het gegevensbestand zijn dan in de tabel, of indien de waarden in het gegevensbestand niet in dezelfde volgorde zijn gerangschikt als de velden in de tabel kunt in MySQL aangeven hoe de gegevens in het gegevensbestand aan de velden van de tabel gelinkt moeten worden door het opgeven van een lijst met veldnamen na het LOAD DATA INFILE statement. MySQL zal NULL-waarden invoegen, als het ontbrekende veldwaarden tegenkomt (indien toegestaan door de veldwaarden en eventuele constraints). In MSSQL kan u met behulp van het bulk copy format bestand de mapping van de betrokken velden aanpassen.

HTML clipboard

SELECT * INTO imported_passengers from pax
WHERE 1=2;

(0 row(s) affected)

BULK INSERT dbo.imported_passengers
FROM '\\milin.cc\users\public\import_passenger_data_2_quoted.txt' 
WITH ( 
    FIRSTROW = 1,
    FORMATFILE = 
        '\\milin.cc\users\public\import_passenger_data_2.bcp'    
    );

(5 row(s) affected)

BULK INSERT dbo.imported_passengers
FROM '\\milin.cc\users\public\import_passenger_data_2_unquoted.txt' 
WITH ( 
    FIRSTROW = 1,
    FORMATFILE = 
        '\\milin.cc\users\public\import_passenger_data_2_unquoted.bcp'    
    );

(5 row(s) affected)

SELECT FlightID, ClassID, PaxName, PaxRef, Note
FROM imported_passengers;

(5 row(s) returned)

CREATE TABLE imported_passengers 
    LIKE pax;

(0 row(s) affected)

LOAD DATA INFILE 
  'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/import_passenger_data_2.txt'
INTO TABLE imported_passengers
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
(PaxName, FlightID, FlightDate, ClassID);

(5 row(s) affected Records: 5 Deleted: 0 Skipped: 0 Warnings: 0)

SELECT FlightID, ClassID, PaxName, PaxRef, Note
FROM imported_passengers;

(5 row(s) returned)

Exporting Records

Net zoals je in MySQL gegevens kan importeren vanuit een bestand naar een tabel met het LOAD DATA INFILE statement, kun je ook records uit een tabel halen om in een bestand op te slaan met de SELECT … INTO OUTFILE constructie. Deze constructie laat alles toe wat men zou kunnen doen met doen de reguliere SELECT-instructie om het vervolgens door te sturen naar een bestand. MSSQL hanteert het command-line hulpprogramma bcp.exe of de import/export wizard van de Server Managment Studio. Voor uitgebreide informatie zie: https://msdn.microsoft.com/en-us/library/ms162802.aspx, https://msdn.microsoft.com/en-us/library/ms140052.aspx.

bcp “SELECT AirportID, AirportName FROM FlightsAirport.dbo.airport” queryout “\\milin.cc\users\public\export_airport_1.txt” -T -c -t -S HQ-ML-FS-01

SELECT AirportID, AirportName
FROM airport
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/export_airport_1.txt'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n';

(15 row(s) affected)

In MySQL wordt het bestand dat opgegeven is in de INTO OUTFILE clausule weggeschreven naar het bestandssysteem van de server en het doelbestand mag nog niet bestaan. Omdat dit bestand wordt geschreven dat uitgevoerd wordt in de MySQL server proces van de gebruiker moet de gebruiker over de juiste machtigingen beschikken om bestanden naar de opgegeven locatie te kunnen schrijven. Om veiligheidsredenen laat MySQL niet toe dat het doelbestand worden geschreven naar het client bestandssysteem. De cliënttoepassing dient zelf de nodige bestanden uit het server-systeem halen met behulp van externe methoden.

Opmerking: Als u de SELECT … INTO OUTFILE gebruikt u of de LOAD DATA INFILE opdrachten, moet een gebruiker de FILE privilege hebben.

Als u INTO DUMPFILE gebruikt in plaats van een INTO OUTFILE, schrijft MySQL slechts één rij naar het bestand, zonder enige kolom of lijnbeëindiging en zonder het uitvoeren van enige ‘escape’ bewerking. Dit is handig als u een BLOB waarde in een bestand wilt opslaan.

Uiteraard kunt u een WHERE-clausule (en alle andere clausule of trefwoorden gebruiken in een normale SELECT instructie) om de uitvoer verder te beperken.

Het volgende voorbeeld demonstreert hoe er enkel records weggeschreven worden naar het bestand export_airport_2.txt voor luchthavens met minstens drie banen:

bcp “SELECT AirportID, AirportName FROM FlightsAirport.dbo.airport WHERE NumRunways >= 3” queryout “\\milin.cc\users\public\export_airport_2.txt” -T -c -t “,“ -S HQ-ML-FS-01

SELECT AirportID, AirportName
FROM airport
WHERE NumRunways >= 3
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/export_airport_2.txt'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n';

(15 row(s) affected)

Net als bij de MySQL LOAD DATA INFILE instructie, kunt u de veld en record begrenzers (field and record delimiters) opnemen voor de data die moet worden gedumpt. In MSSQL kunt u dit aanpassen door extra parameters mee te geven: -t voor de field limiters en -r voor de row terminators.

Het volgende voorbeeld laat zien hoe u een door tabs gescheiden output bestand kan maken:

bcp “SELECT AirportID, AirportName FROM FlightsAirport.dbo.airport WHERE NumRunways >= 3” queryout “\\milin.cc\users\public\export_airport_3.txt” -T -c -t “\t“ -r “\r\n” -S HQ-ML-FS-01

SELECT AirportID, AirportName
FROM airport
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/export_airport_3.txt'
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n';
(15 row(s) affected)

Tip: U kunt ook de mysqldump tool gebruiken om de inhoud van een databank of tabel naar een bestand te schrijven. Dit hulpprogramma is bedoeld voor back-up en herstel van MySQL databases.

MySQL/MSSQL ondersteunen ook het combineren van de INSERT en SELECT-instructies om records van de ene naar de andere tabel te exporteren. Hier is een voorbeeld, waarin de namen van passagiers uit de pax tafel naar een aparte user tabel wordt gekopieerd:

SELECT LEFT(Paxname, CHARINDEX(' ', Paxname)) AS FirstName,
    RIGHT(Paxname, LEN(Paxname)-(CHARINDEX(' ', Paxname)-1)) AS LastName
INTO "user"
FROM pax;

(6 row(s) affected)

CREATE TABLE user (
    FirstName VARCHAR(255),
    LastName VARCHAR(255)
    );
(0 row(s) affected)
INSERT INTO user (FirstName, LastName)
SELECT SUBSTRING_INDEX(PaxName, ' ', 1), SUBSTRING_INDEX(PaxName, ' ', -1)
FROM pax;

(6 row(s) affected Records: 6 Duplicates: 0 Warnings: 0)

Uiteraard kunt u ook een WHERE clausule aan de SELECT-instructie toevoegen om alleen een subset van de oorspronkelijke tabel naar de nieuwe tabel te kopiëren:

SELECT LEFT(Paxname, CHARINDEX(' ', Paxname)) AS FirstName,
    RIGHT(Paxname, LEN(Paxname)-(CHARINDEX(' ', Paxname)-1)) AS LastName
INTO "user"
FROM pax
WHERE ClassID = 2;

(3 row(s) affected)

CREATE TABLE user (
    FirstName VARCHAR(255),
    LastName VARCHAR(255)
    );

(0 row(s) affected)

INSERT INTO user (FirstName, LastName)
SELECT SUBSTRING_INDEX(PaxName, ' ', 1), SUBSTRING_INDEX(PaxName, ' ', -1)
FROM pax
WHERE ClassID = 2;

(3 row(s) affected Records: 3 Duplicates: 0 Warnings: 0)

s