LOAD DATA (MySQL)

Uit De Vliegende Brigade
Naar navigatie springen Naar zoeken springen
Mijn gebruikelijke instellingen voor export van data vanuit Calc naar een .csv-bestand

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.

Meestal zoek ik zoiets

Dit bestreft een SQL-script, niet een sproc. In de loop der jaren aardig geoptimaliseerd: 

############################################################################################################################################
# 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

Zie Casussen (tekencodering)

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: 

Code: 3948. Loading local data is disabled; this must be enabled on both the client and server sides
  • Ik heb de indruk dat dit samenhangt met recente beveiligingsmaatregelen
  • Ik heb zoiets als local_infile=ON toegevoegd aan ~/.my.cnf en /etc/mysql/my.cnf, maar ik kan nog steeds geen data inladen via MySQL Workbench. Ik kan dat wél via de mysql-command-line-tool. Zou MySQL Workbench een aparte client zijn, die apart geconfigureerd moet worden?

Zie ook

Bronnen

Loading local data is disabled

LOAD DATA not allowed in sprocs

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