LOAD DATA (MySQL): verschil tussen versies

Versie van 31 okt 2019 10:13

Gebruikelijke instellingen export vanuit Calc

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. 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;

Niet vanuit een sproc

Als je LOAD DATA in een sproc gebruikt, krijg je foutmelding: ERROR 1314: LOAD DATA is not allowed in stored procedures. Iets vergelijkbaars gebeurt er, als je de naam in een variable opslaat met zoiets als load data local infile @input_bestand into table import_tmp;

Importeren in een sproc schijnt niet te mogen vanwege security-issues [2]. Inladen mbv. van een variabele schijnt niet te kunnen vanwege server-sided vs. locale executie Deze pagina.

Oplossingen

Uitvoeren als gewone SQL-code vanuit een script
Via een externe omgeving (cron, shell, php, Pyton, ...) SQL-code genereren. Die schrijf je naar een bestand, en daarna executeer je dat bestand. Voorbeeld
Mbv. commando load_file [3] (nieuw!)

Beperking tav. mappen

Je kunt niet zomaar een willekeurig bestand inlezen in MySQL: Er zijn oa. beperkingen welke mappen MySQL kan benaderen

Syntaxis

Je kunt veel specificeren [4], [5]. 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)

Zie ook

Casussen (tekencodering)
Databestand importeren (MySQL) - Inclusief LOAD_FILE
Opmaak tekstbestanden
Practitioner-plugin (WordPress)
Upload-rapportages importeren (Amazon)
Veldnamen bij import