LOAD DATA (MySQL): verschil tussen versies

Huidige versie van 20 jan 2021 om 22:50

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.

Wat ik meestal zoek

################################################################################
# Import
################################################################################
#
#
# Select database
########################################
#
use example_cm;


# Drop existing import_tmp table
########################################
#
drop table if exists import_tmp;


# Create new tmp table
########################################
#
create table import_tmp
(
	entity		varchar(255),
	category	varchar(40),
	description	text
)
collate utf8mb4_0900_ai_ci;


# Import table
########################################
#
load data local infile '/tmp/tmp.csv'

	into table import_tmp
	character set utf8
	fields terminated by '\t'
	optionally enclosed by '"'   # Belangrijk!
	lines terminated by '\n'
	ignore 1 rows;


# Check
########################################
#
select * from import_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 - Importeer Amazon productfeed

################################################################################
# Import
################################################################################
#
#
# Drop existing import_tmp table
########################################
#
drop table if exists import_tmp;


# Create new tmp table
########################################
#
create table import_tmp
(
    item_sku					varchar(40),
    external_product_id				varchar(40),
    external_product_id_type			varchar(40),
    item_name					varchar(200),
    description					varchar(2000),
    brand_name					varchar(40),
    manufacturer				varchar(40),
    part_number					varchar(40),
    feed_product_type				varchar(40),
    standard_price				varchar(40),
    sale_price					varchar(40),
    sale_from_date				varchar(40),
    sale_end_date				varchar(40),
    quantity					varchar(40),
    main_image_url				varchar(255),
    other_image_url1				varchar(255),
    other_image_url2				varchar(255),
    other_image_url3				varchar(255),
    update_delete				varchar(40),
    bullet_point1				varchar(500),
    bullet_point2				varchar(500),
    bullet_point3				varchar(500),
    bullet_point4				varchar(500),
    bullet_point5				varchar(500),
    specific_uses_keywords			varchar(40),
    target_audience_keywords			varchar(40),
    generic_keywords				varchar(1000),
    generic_keywords1				varchar(1000),
    generic_keywords2				varchar(1000),
    generic_keywords3				varchar(1000),
    generic_keywords4				varchar(1000),
    generic_keywords5				varchar(1000),
    color_name					varchar(40),
    color_map					varchar(40),
    recommended_browse_nodes			varchar(40),
    website_shipping_weight_unit_of_measure	varchar(40),
    item_height					varchar(40),
    item_length					varchar(40),
    item_width					varchar(40),
    item_dimensions_unit_of_measure		varchar(40),
    item_weight					varchar(40),
    item_weight_unit_of_measure			varchar(40),
    website_shipping_weight			varchar(40),
    country_of_origin				varchar(40),
    package_height				varchar(40),
    package_width				varchar(40),
    package_length				varchar(40),
    package_length_unit_of_measure		varchar(40),
    package_weight				varchar(40),
    package_weight_unit_of_measure		varchar(40),
    currency					varchar(40),
    condition_type				varchar(40),
    fulfillment_latency				varchar(40),
    item_package_quantity			varchar(40),
    material_type				varchar(40),
    item_type					varchar(40),
    tool_type					varchar(40),
    tool_brand					varchar(40),
    sort_order					varchar(40)
)
collate utf8mb4_0900_ai_ci;


# Import table
########################################
#
load data local infile '/tmp/tmp.csv'
	into table import_tmp
	character set latin1
	fields terminated by '\t'
	optionally enclosed by '"'
	lines terminated by '\n'
	ignore 3 rows;


# Check
########################################
#
select * from import_tmp;

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

Complete foutmelding:

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

Casus: Juli 2020

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?

Casus nov. 2020

show global variables like 'local_infile'; gaf als Value ON
Misschien moet ik dit ook zo instellen specifiek voor MySQL Workbench?
Als ik het import-script aanroep vanaf de command line met mysql < 100.sql, krijg ik een vergelijkbare foutmelding: ERROR 2068 (HY000) at line 50: LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.
Dit werkt wel: mysql mysql --local_infile=1 < 100.sql
De regel local_infile=ON in .my.cnf gaf foutmeldingen in mysqldump. Ik heb deze regel weer geactiveerd, en nu werkt mysql < 100.sql wel
Het werkt nog steeds niet in MySQL Workbench. Ook niet na herstarten. Moet ik het voor MySQL Workbench apart configureren?

Oplossing:

Regel OPT_LOCAL_INFILE=1 toevoegen in MySQL Workbench onder

Database » Manage Connections » <Kies connectie> » Advanced » Other

.

Zie [7] voor details & schermafdruk.

Voeg regel OPT_LOCAL_INFILE=1 toe, om commando LOAD DATA te kunnen toepassen

Zie ook

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