LOAD DATA (MySQL)
De standaardmanier om rekenbladen te importeren, is door deze in CSV-formaat weg te schrijven en te laden met behulp van het commando LOAD DATA
. Vantevoren moet je de doeltabel defineren, inclusief velden met passende dimensies [1].
LOAD DATA
werkt niet vanuit een sproc; alleen vanuit een gewoon SQL-bestand - Heel onhandig. LOAD_FILE
zou wél werken vanuit sprocs, maar heb ik nog niet aan de praat gekregen. Zie Databestand importeren (MySQL) voor details.
PROBLEEM
Door een bug in MySQL Workbench 8.x, werkt dit niet meer vanuit Workbench: https://bugs.mysql.com/bug.php?id=91891. Het werkt wel vanaf de mysql-client.
Meestal zoek ik zoiets
Dit bestreft een SQL-script, niet een sproc. Meestal een paar keer doorlopen om de dimensies goed te krijgen.
############################################################################################################################################ # Import ############################################################################################################################################ # # Drop existing tmp table ############################################################################# # drop table if exists tmp; # Create new tmp table ############################################################################# # create table tmp ( entity varchar(255), category varchar(40), description text ); # Import table ############################################################################# # load data local infile '/tmp/tmp.csv' into table tmp character set utf8 fields terminated by '\t' optionally enclosed by '"' # Belangrijk! lines terminated by '\n' ignore 1 rows; # Check ############################################################################# # select * from tmp; # Transfer to final table ############################################################################# # # drop table if exists tmp2 # create table tmp2 insert * from tmp;
LOAD DATA not allowed in stored procedures
LOAD DATA is niet toegestaan in sprocs, uit veiligheidsoverwegingen [2]: je krijgt foutmelding ERROR 1314: LOAD DATA is not allowed in stored procedures
. Dit is ook niet mogelijk middels DDL. Dan krijg je een vergelijkbare foutmelding.
Oplossingen
- Uitvoeren als gewone SQL-code
- Uitvoeren vanuit een externe omgeving (cron, shell, php, Pyton, ...) [3]
- Commando
load_file
is wél toegestaan in sprocs [4] - Via UDF's (User Defined Functions): Dan zijn eigen functies die je in C of C++ hebt ontwikkeld. Het is me echter niet duidelijk of je die echt vanuit een sproc kunt aanroepen.
- Deze onduidelijke truuk?
LOAD DATA & variable
Je kunt LOAD DATA niet gebruiken in combinatie met een naam die gegeven is via een variabele, dus zoiets als dit:
load data local infile @input_bestand
Inladen mbv. van een variabele schijnt niet te kunnen vanwege server-sided vs. locale executie Deze pagina.
Beperking tav. mappen
Standaard kun je enkel vanuit een paar mappen bestanden inlezen. Dat kun je aanpassen via environmental variables, bv. in systeembestanden.
Syntaxis
Je kunt veel specificeren [5], [6]. Met bijna elke optie op een aparte regel, krijg je bv. zoiets:
LOAD DATA LOCAL INFILE 'file' IGNORE INTO TABLE 'table' CHARACTER SET UTF8 FIELDS TERMINATED BY '\t' [OPTIONALLY] ENCLOSED BY '"' ESCAPED BY '\\' LINES STARTING BY '' # Alleen regels die met deze tekenreeks beginnen, worden geïmporteerd TERMINATED BY '\n' IGNORE 'number' [{LINES | ROWS}] [(col_name_or_user_var # Kun je hiermee on-the-fly de doeltabel defineren? [, col_name_or_user_var] ...)] [SET col_name={expr | DEFAULT}, [, col_name={expr | DEFAULT}] ...]
Eenvoudiger voorbeeld:
load data local infile "/var/export/import_file.csv" into table import_tmp fields terminated by '\t' -- Tab als veldscheidingsteken optionally enclosed by '"' lines terminated by "\n" -- Alle records zijn afgesloten met een \n ignore 1 rows; -- Regel 1 bevat veldnamen en worden overgeslagen (gebruiken bij testen!)
Eenvoudig voorbeeld
drop table if exists ean_code; CREATE TABLE `webwinkels`.`ean_code` ( `ean_id` VARCHAR(13) NOT NULL, `sku` VARCHAR(45) NULL, PRIMARY KEY (`ean_id`)); load data local infile "/var/mysql-export/ean01.txt" into table ean_code
Voorbeeld - Amazon export-bestand importeren
drop table if exists import_tmp; create table import_tmp ( item_sku varchar(45) null, external_product_id varchar(45) null, external_product_id_type varchar(45) null, item_name varchar(1000) null, bullet_point1 varchar(500) null, bullet_point2 varchar(500) null, bullet_point3 varchar(500) null, bullet_point4 varchar(500) null, bullet_point5 varchar(500) null, generic_keywords1 varchar(1010) null, generic_keywords2 varchar(1010) null, generic_keywords3 varchar(1010) null, generic_keywords4 varchar(1010) null, generic_keywords5 varchar(1010) null, description varchar(1000) null, recommended_browse_nodes varchar(45) null, manufacturer varchar(45) null, feed_product_type varchar(45) null, brand_name varchar(45) null, update_delete varchar(45) null, standard_price varchar(45) null, quantity varchar(45) null, fulfillment_latency varchar(45) null, condition_type varchar(45) null, number_of_items varchar(45) null, main_image_url varchar(100) null, other_image_url1 varchar(100) null, other_image_url2 varchar(100) null, other_image_url3 varchar(100) null, other_image_url4 varchar(100) null, other_image_url5 varchar(100) null, website_shipping_weight varchar(45) null, website_shipping_weight_unit_of_measure varchar(45) null, item_length varchar(45) null, item_width varchar(45) null, item_height varchar(45) null, item_dimensions_unit_of_measure varchar(45) null, item_weight varchar(45) null, item_weight_unit_of_measure varchar(45) null, target_audience_keywords varchar(45) null, package_height varchar(45) null, package_height_unit_of_measure varchar(45) null, package_width varchar(45) null, package_width_unit_of_measure varchar(45) null, package_length varchar(45) null, package_length_unit_of_measure varchar(45) null, package_weight varchar(45) null, package_weight_unit_of_measure varchar(45) null, color_name varchar(45) null, color_map varchar(45) null, material_type varchar(45) null, specific_uses_for_product varchar(45) null, internal_sort_order varchar(5) null ); load data local infile "/var/export/eanbosch.csv" into table import_tmp fields terminated by '\t' -- Tab als veldscheidingsteken optionally enclosed by '"' lines terminated by "\n" -- Alle records zijn afgesloten met een \n ignore 1 rows; -- Regel 1 bevat veldnamen en worden overgeslagen (gebruiken bij testen!)
Voorbeeld - Amazon inventarisbestand
############################################################### # Create tmp input table ############################################################### # drop table if exists import_tmp; CREATE TABLE `dwh`.`import_tmp` ( `sku` VARCHAR(255) NOT NULL, `asin` VARCHAR(255) NULL, `name` VARCHAR(500) NULL, `description` VARCHAR(2000) NULL, PRIMARY KEY (`sku`)) default character set = utf8; ############################################################### # Import table ############################################################### # load data local infile "/var/export/01.csv" into table import_tmp CHARACTER SET UTF8 # Belangrijk! fields terminated by '\t' optionally enclosed by '"' lines terminated by "\n" ignore 1 rows;
CR/LF
Permission denied-foutmelding (juli 2020)
Dit betrof een import via mysql (de command-line tool) ivm. storing MySQL Workbench. foutmelding:
ERROR 29 (HY000) at line 40: File '/tmp/04-example-file-removed-some-columns.csv' not found (OS errno 13 - Permission denied)
Deze foutmelding verdween toen ik load data infile
veranderde in load data local infile
, maar toen kreeg ik de foutmelding hieronder:
Loading local data must be enabled on both the client and server sides (juli 2020)
Complete foutmelding:
Loading local data is disabled; this must be enabled on both the client and server sides
Zie ook
- Casussen (tekencodering)
- Databestand importeren (MySQL) - Inclusief
LOAD_FILE
- Opmaak tekstbestanden
- Practitioner-plugin (WordPress)
- Upload-rapportages importeren (Amazon)
- Veldnamen bij import
Bronnen
- http://www.mysqltutorial.org/import-csv-file-mysql-table/
- http://stackoverflow.com/questions/1310166/how-to-import-an-excel-file-in-to-a-mysql-database
- https://dev.mysql.com/doc/refman/5.7/en/load-data.html
- https://dev.mysql.com/doc/refman/5.6/en/stored-program-restrictions.html
- https://www.morpheusdata.com/blog/2014-12-21-the-fastest-way-to-import-text-xml-and-csv-files-into-mysql-tables
Loading local data is disabled
LOAD DATA not allowed in sprocs
- https://stackoverflow.com/questions/26344348/load-data-into-stored-procedure-mysql
- https://dev.mysql.com/doc/refman/5.6/en/stored-program-restrictions.html
- https://stackoverflow.com/questions/19053263/mysql-load-data-infile-when-conditions-are-met
- https://dev.mysql.com/doc/extending-mysql/5.6/en/adding-udf.html