LOAD DATA (MySQL): verschil tussen versies

Uit De Vliegende Brigade
Naar navigatie springen Naar zoeken springen
Regel 255: Regel 255:
 
* https://dev.mysql.com/doc/refman/5.6/en/stored-program-restrictions.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
 
* 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 ===
 +
 +
* https://stackoverflow.com/questions/59993844/error-loading-local-data-is-disabled-this-must-be-enabled-on-both-the-client

Versie van 6 jul 2020 12:56

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.

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;

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)

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 is disabled (juli 2020)

Complete foutmelding: 

Loading local data is disabled; this must be enabled on both the client and server sides

Zie ook

Bronnen

Loading local data is disabled

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