INTO OUTFILE (MySQL): verschil tussen versies

Uit De Vliegende Brigade
Naar navigatie springen Naar zoeken springen
 
(35 tussenliggende versies door dezelfde gebruiker niet weergegeven)
Regel 2: Regel 2:
  
 
== Voorbeelden ==
 
== Voorbeelden ==
 +
 +
April-mei 2020:
 +
 +
<pre>
 +
select
 +
  *
 +
from
 +
  product_fba_202004
 +
order by
 +
  sku
 +
into outfile
 +
  '/tmp/product_fba_202004.tsv'
 +
character set
 +
  utf8
 +
fields terminated by
 +
  '\t'
 +
escaped by
 +
    ''
 +
optionally enclosed by
 +
  '"'
 +
lines terminated by
 +
  '\n';
 +
</pre>
 +
 +
Oudere voorbeelden:
  
 
  select * from tabel_export into outfile '/tmp/tabel_export.tsv';
 
  select * from tabel_export into outfile '/tmp/tabel_export.tsv';
Regel 18: Regel 43:
 
  LINES TERMINATED BY '\n';
 
  LINES TERMINATED BY '\n';
  
−
== Voorbeelden: Sprocs + DDL ==
+
== Bestanden kunnen niet overschreven worden ==
  
−
<pre>
+
Je kunt een bestaand bestand ''niet'' overschrijven. Oplossing: DDL gebruiken en een timestamp aan de bestandsnaam toevoegen.
−
CREATE DEFINER=`root`@`localhost` PROCEDURE `export_save`(
 
−
in tabelnaam varchar(100),
 
−
bestandsnaam varchar(100)
 
−
)
 
−
BEGIN
 
  
−
set @ddl=concat
+
== Bestandsnaam mag geen variabele zijn ==
−
(
 
−
"select * from ",tabelnaam," order by internal_sort_order asc ",
 
−
"into outfile '/var/mysql-export/", bestandsnaam, "-",
 
−
DATE_FORMAT( NOW(),'%Y%m%d-%H%i%S'),".tsv'",
 
−
" fields terminated by '\\t' enclosed by '",char(34),
 
−
"' lines terminated by '\\n'"
 
−
);
 
  
−
prepare s1 from @ddl;
+
Helaas: De bestandsnaam mag geen variabele zijn. Ook niet in een sproc. Dit werkt dus niet:
−
execute s1;
 
−
drop prepare s1;
 
−
</pre>
 
  
 
<pre>
 
<pre>
−
-- --------------------------------------------------------------------------------
+
#####################################################################################################################
−
-- Routine DDL
+
# Set file name
−
-- Note: comments before and after the routine body will not be stored by the server
+
#####################################################################################################################
−
-- --------------------------------------------------------------------------------
+
#
−
DELIMITER $$
+
set @file_name =
−
 
+
concat
−
CREATE DEFINER=`root`@`localhost` PROCEDURE `export_save`(
 
−
in bron varchar(100),
 
−
in sorteer varchar(100),
 
−
in bestandsnaam varchar(100)
 
−
)
 
−
BEGIN
 
−
--
 
−
-- Write records to a file
 
−
-- =======================
 
−
--
 
−
-- Input arguments:
 
−
--
 
−
-- * 'bron': Name of a table or view
 
−
-- * 'sorteer': Sort order without the words "order by"
 
−
-- * 'bestandsnaam': filename.
 
−
--
 
−
-- Examples
 
−
-- ========
 
−
--
 
−
-- * call export_save("superquery_tmp","","superquery");
 
−
-- * call export_save("superquery_tmp","internal_sort_order asc","superquery");
 
−
 
 
−
set @ddl=concat
 
 
(
 
(
−
"select * from ",bron,
+
    "/tmp/adgroup-",
−
if
+
    DATE_FORMAT( NOW(),'%Y%m%d-%H%i'),
−
(
+
    ".tsv"
−
sorteer = "",
+
);  
−
" ",
 
−
concat(" order by ",sorteer," ")
 
−
),
 
−
"into outfile '/var/mysql-export/", bestandsnaam, "-",
 
−
DATE_FORMAT( NOW(),'%Y%m%d-%H%i%S'),".tsv'",
 
−
" fields terminated by '\\t' enclosed by '",char(34),
 
−
"' lines terminated by '\\n'"
 
−
);
 
  
−
select @ddl;
 
  
−
prepare s1 from @ddl;
+
#####################################################################################################################
−
execute s1;
+
# Write to file
−
drop prepare s1;
+
#####################################################################################################################
−
 
+
#
−
END
+
select
 +
    *
 +
from
 +
    adgroup
 +
order by
 +
    sort_order,
 +
    campaign_name,
 +
    adgroup_name   
 +
into outfile
 +
    @file_name
 +
character set
 +
    utf8
 +
fields terminated by
 +
    '\t'
 +
escaped by
 +
    ''   
 +
lines terminated by
 +
    '\n';
 
</pre>
 
</pre>
  
−
== Bestanden kunnen niet overschreven worden ==
+
Ook mag je een input-variabele niet gebruiken als bestandsnaam.
  
−
Je kunt een bestaand bestand ''niet'' overschrijven. Oplossing: DDL gebruiken en een timestamp aan de bestandsnaam toevoegen.
+
Oplossing: DDL gebruiken.
  
 
== /var ipv. /tmp ==
 
== /var ipv. /tmp ==
  
−
Schrijven naar <code>/tmp</code> geeft voortdurend vage klachten. Daarom schrijf ik naar <code>/var</code>. Naar
+
Schrijven naar <code>/tmp</code> gaf ooit vage klachten. Toen schrijf ik naar <code>/var</code>. Naar
  
 
  /var/mysql-export
 
  /var/mysql-export
Regel 106: Regel 101:
 
om precies te zijn. Permissies op die map zijn __7, zodat het MySQL-proces kan schrijven.
 
om precies te zijn. Permissies op die map zijn __7, zodat het MySQL-proces kan schrijven.
  
−
== Tekencodering ==
+
In recente jaren gebruik ik gewoon weer /tmp.
 +
 
 +
== Schrijven naar willekeure locaties ==
 +
 
 +
Zie [[Can't create/write to file (MySQL)]].
 +
 
 +
== Escape-codes verwijderen ==
 +
 
 +
Als MySQL schrijft naar een bestand, worden control characters (bv. ''null'' of ''newline'') of andere bijzondere karakters (bv. apostrophe!) voorzien van een escape-prefix (typisch "\"). Da's niet altijd handig.
 +
 
 +
De truuk is, dat je zelf kunt bepalen wat die escape-prefix is. Als je hiervoor een lege string specificeert, is het probleem opgelost.
 +
 
 +
Helaas lijkt dit niet te werken voor NULL-waardes. Zie daarvoor het script hieronder.
  
−
In beginsel wordt een bestand weggeschreven in dezelfde karakterset als de betreffende tabel/kolommen. 't Zou handig zijn als je dit kunt aanpassen tijden het wegschrijven.
+
=== Voorbeeld: Mei 2020 ===
  
−
=== Specificeren bij wegschrijven? ===
+
Een exportbestand bevatte backslashes voor apostrophes.
  
−
[https://stackoverflow.com/questions/9764898/mysql-character-encoding-used-by-select-into Deze posting] suggereert dat je zoiets kunt doen:
+
''' Initiële oplossing '''
  
 
<pre>
 
<pre>
−
select * from sq_tmp
+
select
−
into outfile '/tmp/test02.csv'
+
    campaign_name,
−
character set latin1;
+
    adgroup_name_20_gads_approved as adgroup_name,
 +
    adgroup_status,
 +
    currency_code,
 +
    max_cpc,
 +
    adgroup_type
 +
from
 +
    adgroup
 +
order by
 +
    sort_order,
 +
    campaign_name,
 +
    adgroup_name          # Je kunt sorteren op de alias!
 +
into outfile
 +
    '/tmp/adgroup.tsv'
 +
character set
 +
    utf8
 +
fields terminated by
 +
    '\t'
 +
optionally enclosed by
 +
    '"'
 +
escaped by
 +
    ''                    # ← tjakka!
 +
lines terminated by
 +
  '\n';
 +
</pre>
 +
 
 +
Er ontstond een nieuw probleem: Sommige velden bevatte dubbele aanhalingstekens, bv. ''Vine de "Ensor" 1914''. Deze moesten ge-escaped worden, omdat '"' ook het veld is voor ''optionally enclosed''. Echter: Dat ''optionally enclosed'' boeide niet, want het is allemaal tekst.
 +
 
 +
''' Definitieve versie '''
 +
 
 +
Dit werkt als een tierelier:
 +
 
 +
<pre>
 +
select
 +
    campaign_name,
 +
    adgroup_name_30_applied as adgroup_name,
 +
    adgroup_status,
 +
    currency_code,
 +
    max_cpc,
 +
    adgroup_type
 +
from
 +
    adgroup
 +
order by
 +
    sort_order,
 +
    campaign_name,
 +
    adgroup_name   
 +
into outfile
 +
    '/tmp/adgroup.tsv'
 +
character set
 +
    utf8
 +
fields terminated by
 +
    '\t'
 +
# optionally enclosed by  # Niet!
 +
# '"'
 +
escaped by
 +
    ''
 +
lines terminated by
 +
    '\n';
 +
</pre>
 +
 
 +
=== Script om NULL-waardes te vervangen ===
 +
 
 +
Best simpel, en werkt super. In de loop der jaren broed ik vaak op alternatieve namen, maar tot op heden (lente 2020) houd ik het op ''export_fix_null''
 +
 
 +
<pre>
 +
CREATE DEFINER=`root`@`localhost` PROCEDURE `export_fix_null`(in tblname tinytext )
 +
BEGIN
 +
#
 +
# Replace each NULL values in the given table with "" - Last updated: May 2020
 +
######################################################################################################################################
 +
#
 +
# * Beautifully simple code :)
 +
# * Where-clause should include db-name. Otherwise columns from other databases will be included!
 +
# * Tinytext: Up to 255 characters - That's plenty
 +
#
 +
#
 +
######################################################################################################################################
 +
# DDL
 +
######################################################################################################################################
 +
#
 +
set @string=concat
 +
(
 +
"Update ",tblname," set ",
 +
(
 +
select group_concat(column_name,"=ifnull(",column_name,",'')")
 +
        from information_schema.columns
 +
        where table_name=tblname and table_schema=database()
 +
    ),
 +
    ";"
 +
);
 +
 
 +
 
 +
######################################################################################################################################
 +
# Check
 +
######################################################################################################################################
 +
#
 +
# select @string;
 +
 
 +
 
 +
######################################################################################################################################
 +
# Execute
 +
######################################################################################################################################
 +
#
 +
prepare ps1 from @string;
 +
execute ps1;
 +
drop prepare ps1;
 +
 
 +
 
 +
END
 
</pre>
 
</pre>
  
−
Ik krijg welliswaar geen foutmelding, maar de tekencodering wordt helaas niet aangepast. Verderop in dezelfde posting heeft iemand ditzelfde probleem. De [https://dev.mysql.com/doc/refman/5.7/en/select-into.html MySQL Reference Guide] vermeldt deze optie niet,behalve voor de codering van de ''bestandsnaam''
+
== Casus: Tabs in exportbestand (jan. 2021) ==
 +
 
 +
Dit betreft een export inclusief een 'description'-veld waarin allerlei tekens worden gebruikt, waaronder ''tab''. Dit lijkt te werken:
  
−
=== Converteren bij wegschrijven? ===
+
<pre>
 +
select
 +
  *
 +
from
 +
  product_kbo3_202012
 +
order by
 +
  sku
 +
into outfile
 +
  '/tmp/product_kbo3_202012.tsv'
 +
character set
 +
  utf8
 +
fields terminated by
 +
  '\t'
  
−
[https://stackoverflow.com/questions/9764898/mysql-character-encoding-used-by-select-into Dezelfde posting] suggereert ook nog zoiets als:
+
# De truuk: "enclosed" ipv. "optionally enclosed"
 +
#################################################
 +
#
 +
enclosed by
 +
'"'
  
−
SELECT convert(col_name USING latin1) FROM posts INTO OUTFILE '/tmp/x.csv' …;
+
# Geen escape-character
 +
#################################################
 +
#
 +
# Ik weet niet zeker of dit hier een rol speelt, om bv. "\" voor " te voorkomen
 +
#
 +
escaped by
 +
''
 +
lines terminated by
 +
  '\n';
 +
</pre>
  
−
maar ik zie niet goed hoe ik dat op een gemakkelijke manier kan toepassen op een tabel als geheel. Wat in ieder geval niet werkt:
+
== Zie ook ==
  
−
select convert(* using latin1) from sq_tmp;
+
* [[AppArmor]]
 +
* [[Can't create/write to file (MySQL)]]
 +
* [[INTO OUTFILE & tekencodering (MySQL)]]
  
 
== Bronnen ==
 
== Bronnen ==
Regel 138: Regel 281:
 
* http://dev.mysql.com/doc/refman/5.7/en/select-into.html
 
* http://dev.mysql.com/doc/refman/5.7/en/select-into.html
 
* http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_character_set_filesystem
 
* http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_character_set_filesystem
 +
* https://stackoverflow.com/questions/9140879/mysql-select-into-outfile-export-options
 +
* https://stackoverflow.com/questions/13548118/can-i-use-a-variable-to-specify-outfile-in-mysql

Huidige versie van 19 jan 2021 om 16:28

De gebruikelijke manier om vanuit MySQL records naar een bestand te schrijven, gaat met into outfile.

Voorbeelden

April-mei 2020: 

select
   *
from
   product_fba_202004
order by
   sku
into outfile
   '/tmp/product_fba_202004.tsv'
character set
   utf8
fields terminated by 
   '\t'
escaped by
    ''
optionally enclosed by
   '"'
lines terminated by
   '\n';

Oudere voorbeelden: 

select * from tabel_export into outfile '/tmp/tabel_export.tsv';

SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

SELECT field1, field2
FROM table1
INTO OUTFILE '/path/to/file.csv'
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
FIELDS ESCAPED BY '\'
LINES TERMINATED BY '\n';

Bestanden kunnen niet overschreven worden

Je kunt een bestaand bestand niet overschrijven. Oplossing: DDL gebruiken en een timestamp aan de bestandsnaam toevoegen.

Bestandsnaam mag geen variabele zijn

Helaas: De bestandsnaam mag geen variabele zijn. Ook niet in een sproc. Dit werkt dus niet: 

#####################################################################################################################
# Set file name
#####################################################################################################################
#
set @file_name =
concat
(
    "/tmp/adgroup-",
    DATE_FORMAT( NOW(),'%Y%m%d-%H%i'),
    ".tsv"
);    


#####################################################################################################################
# Write to file
#####################################################################################################################
#
select
    *
from
    adgroup
order by
    sort_order,
    campaign_name,
    adgroup_name    
into outfile
    @file_name
character set
    utf8
fields terminated by
    '\t'
escaped by
    ''    
lines terminated by
    '\n';

Ook mag je een input-variabele niet gebruiken als bestandsnaam.

Oplossing: DDL gebruiken.

/var ipv. /tmp

Schrijven naar /tmp gaf ooit vage klachten. Toen schrijf ik naar /var. Naar 

/var/mysql-export

om precies te zijn. Permissies op die map zijn __7, zodat het MySQL-proces kan schrijven.

In recente jaren gebruik ik gewoon weer /tmp.

Schrijven naar willekeure locaties

Zie Can't create/write to file (MySQL).

Escape-codes verwijderen

Als MySQL schrijft naar een bestand, worden control characters (bv. null of newline) of andere bijzondere karakters (bv. apostrophe!) voorzien van een escape-prefix (typisch "\"). Da's niet altijd handig.

De truuk is, dat je zelf kunt bepalen wat die escape-prefix is. Als je hiervoor een lege string specificeert, is het probleem opgelost.

Helaas lijkt dit niet te werken voor NULL-waardes. Zie daarvoor het script hieronder.

Voorbeeld: Mei 2020

Een exportbestand bevatte backslashes voor apostrophes.

Initiële oplossing 

select
    campaign_name,
    adgroup_name_20_gads_approved as adgroup_name,
    adgroup_status,
    currency_code,
    max_cpc,
    adgroup_type
from
    adgroup
order by
    sort_order,
    campaign_name,
    adgroup_name           # Je kunt sorteren op de alias!
into outfile
    '/tmp/adgroup.tsv'
character set
    utf8
fields terminated by
    '\t'
optionally enclosed by
    '"'
escaped by
    ''                     # ← tjakka!
lines terminated by
   '\n';

Er ontstond een nieuw probleem: Sommige velden bevatte dubbele aanhalingstekens, bv. Vine de "Ensor" 1914. Deze moesten ge-escaped worden, omdat '"' ook het veld is voor optionally enclosed. Echter: Dat optionally enclosed boeide niet, want het is allemaal tekst.

Definitieve versie

Dit werkt als een tierelier: 

select
    campaign_name,
    adgroup_name_30_applied as adgroup_name,
    adgroup_status,
    currency_code,
    max_cpc,
    adgroup_type
from
    adgroup
order by
    sort_order,
    campaign_name,
    adgroup_name    
into outfile
    '/tmp/adgroup.tsv'
character set
    utf8
fields terminated by
    '\t'
# optionally enclosed by   # Niet!
# 	'"'
escaped by
    ''
lines terminated by
    '\n';

Script om NULL-waardes te vervangen

Best simpel, en werkt super. In de loop der jaren broed ik vaak op alternatieve namen, maar tot op heden (lente 2020) houd ik het op export_fix_null 

CREATE DEFINER=`root`@`localhost` PROCEDURE `export_fix_null`(in tblname tinytext )
BEGIN
#
# Replace each NULL values in the given table with "" - Last updated: May 2020
######################################################################################################################################
#
# * Beautifully simple code :)
# * Where-clause should include db-name. Otherwise columns from other databases will be included!
# * Tinytext: Up to 255 characters - That's plenty
#
#
######################################################################################################################################
# DDL
######################################################################################################################################
#
set @string=concat
(
	"Update ",tblname," set ",
	(
	select group_concat(column_name,"=ifnull(",column_name,",'')")
        from information_schema.columns 
        where table_name=tblname and table_schema=database()
    ),
    ";"
);


######################################################################################################################################
# Check
######################################################################################################################################
#
# select @string;


######################################################################################################################################
# Execute
######################################################################################################################################
#
prepare ps1 from @string;
execute ps1;
drop prepare ps1;


END

Casus: Tabs in exportbestand (jan. 2021)

Dit betreft een export inclusief een 'description'-veld waarin allerlei tekens worden gebruikt, waaronder tab. Dit lijkt te werken: 

select
   *
from
   product_kbo3_202012
order by
   sku
into outfile
   '/tmp/product_kbo3_202012.tsv'
character set
   utf8
fields terminated by 
   '\t'

# De truuk: "enclosed" ipv. "optionally enclosed"
#################################################
#
enclosed by
	'"'

# Geen escape-character
#################################################
#
# Ik weet niet zeker of dit hier een rol speelt, om bv. "\" voor " te voorkomen
#
escaped by
	''
lines terminated by
   '\n';

Zie ook

Bronnen

Overgenomen van "https://wiki.devliegendebrigade.nl/index.php?title=INTO_OUTFILE_(MySQL)&oldid=25196"