MariaDB'ye CSV Aktarma: LOAD DATA INFILE ve mariadb-import

Dosyayı aktarmaya hazırlamak

Belgelerdeki aktarma rehberi önce kaynak veriyi, örneğin bir hesap tablosu programından, ayırıcılı bir metin dosyası olarak dışa aktarmanızı önerir. Alan ayırıcı olarak verinizde pek geçmeyen bir karakter seçin; rehber dikey çizgiyi (|) iyi bir seçenek olarak gösterir, sekme de yaygındır. Kayıtları satır sonu karakteriyle ayırın. Dosyadaki sütunların sırası ve sayısı hedef tabloyla aynıysa iş en kolay hâlini alır: tabloda dosyada olmayan sütunlar varsa bunlar varsayılan değerleriyle ya da NULL ile dolar; dosyada tabloda olmayan sütunlar varsa ya sütun eşlemesi yapmanız ya da bu sütunları dosyadan çıkarmanız gerekir.

Başlık ve altbilgi satırlarını, aktarırken IGNORE N LINES ile atlamayı planlamıyorsanız dosyadan silin. Dosyayı sunucuya FTP ile gönderiyorsanız satır sonlarının bozulmaması için ASCII kipini kullanın ve dosyayı web üzerinden erişilemeyen bir dizine koyun. Rehber ayrıca bu araçların temel tip uyumluluğunun ötesinde kapsamlı veri doğrulaması yapmayabileceğini hatırlatır; veriyi aktarmadan önce olabildiğince temizleyin.

LOAD DATA INFILE sözdizimi

Komutun tam sözdizimi aşağıdadır. Dosya adı sabit bir dize olarak verilmelidir. FIELDS ve LINES bölümleri isteğe bağlıdır; verilmezse alanların sekmeyle, satırların satır sonu karakteriyle bittiği varsayılır. İkisi birlikte yazılırsa FIELDS önce gelmelidir. Aynı bölümler SELECT ... INTO OUTFILE ile ortaktır, bu yüzden dışa aktardığınız dosyayı aynı seçeneklerle geri yükleyebilirsiniz.

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name, ...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'CHAR']
        [ESCAPED BY 'CHAR']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES|ROWS}]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]

En basit kullanımda önce hedef veritabanını seçip ayırıcıyı belirtirsiniz. Belgelerdeki örnekte dikey çizgiyle ayrılmış bir dosya prospect_contact tablosuna yükleniyor. Tablo adını veritabanı.tablo biçiminde de yazabilirsiniz; Windows'ta yollar ters değil düz eğik çizgiyle yazılır:

USE sales_dept;

LOAD DATA INFILE '/tmp/prospects.txt'
INTO TABLE prospect_contact
FIELDS TERMINATED BY '|';

Tırnaklı alanlar, satır sonları ve başlık satırı

Alanlar çift tırnak içine alınmışsa ENCLOSED BY '"' kullanılır. Belgelerdeki ilk örnekte her satır tırnakla başlıyor ve tırnak ile Windows tarzı satır sonuyla bitiyor. Dosya FTP ile ikili kipte geldiyse satır sonunu onaltılık değerle de verebilirsiniz; bu durumda değerin etrafına tırnak konmaz:

LOAD DATA INFILE '/tmp/prospects.txt'
INTO TABLE prospect_contact
FIELDS TERMINATED BY '|' ENCLOSED BY '"'
LINES STARTING BY '"' TERMINATED BY '"\r\n';

LOAD DATA INFILE '/tmp/prospects.txt'
INTO TABLE prospect_contact
FIELDS TERMINATED BY '|'
LINES TERMINATED BY 0x0d0a;

OPTIONALLY ENCLOSED BY yalnızca bazı alanların tırnaklı olduğu dosyalarda işe yarar. Tırnak içindeki tırnaklar ters eğik çizgi yerine başka bir karakterle kaçırılmışsa ESCAPED BY ile belirtilir. IGNORE 1 LINES dosyanın ilk satırını, tipik olarak sütun başlıklarını atlar. Dosyadaki sütun sırası tablodan farklıysa komutun sonuna sütun listesi yazılır. Belgelerdeki örnekte dosya soyad, ad, telefon sırasında; tablodaki row_id sütunu listede olmadığı için kendi varsayılan mekanizmasıyla, bu örnekte AUTO_INCREMENT ile dolar:

LOAD DATA INFILE '/tmp/prospects.txt'
INTO TABLE prospect_contact
FIELDS TERMINATED BY '|'
ENCLOSED BY '"'
ESCAPED BY '#'
IGNORE 1 LINES
(name_last, name_first, telephone);

Değerleri yüklerken dönüştürmek: kullanıcı değişkenleri ve SET

Sütun listesinde bir sütun adı yerine @değişken yazabilir, sonra SET bölümünde bu değeri işleyip bir sütuna atayabilirsiniz. Böylece dosyadaki değeri olduğu gibi kaydetmek yerine hesaplanmış bir değer yüklersiniz. Belgelerdeki ilk örnekte virgülle ayrılmış şu içerik kullanılıyor:

2,2
3,3
4,4
5,5
6,8

Dosyadaki iki değer a ve b sütunlarına gidiyor, üçüncü sütun c ise SET ile bu ikisinin toplamı olarak hesaplanıyor:

CREATE TABLE t1 (a INT, b INT, c INT, PRIMARY KEY (a));
LOAD DATA LOCAL INFILE
 '/tmp/loaddata7.dat' INTO TABLE t1 FIELDS TERMINATED BY ',' (a,b) SET c=a+b;
SELECT * FROM t1;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    2 |    2 |    4 |
|    3 |    3 |    6 |
|    4 |    4 |    8 |
|    5 |    5 |   10 |
|    6 |    8 |   14 |
+------+------+------+

İkinci örnekte sekmeyle ayrılmış bir dosyadaki ilk değer bir kullanıcı değişkenine okunup iki katı alınarak yükleniyor. Bu teknik, para birimini dönüştürmek, tarih biçimini düzeltmek ya da boş dizeleri NULL'a çevirmek gibi işler için de kullanılabilir:

LOAD DATA INFILE 'ld.txt' INTO TABLE ld (@i,v) SET i=@i*2;

SELECT * FROM ld;
+------+------+
| i    | v    |
+------+------+
|    2 | a    |
|    4 | b    |
+------+------+

Yinelenen anahtarlar: REPLACE ve IGNORE

Veri yüklediğiniz tabloda zaten satırlar varsa ve tablonun birincil anahtarı ya da benzersiz anahtarı varsa, dosyadaki bir satır mevcut bir anahtarla çakışabilir. Belgelere göre bu durumda komut, tablodaki veriyi korumak için 1062 hatasıyla durur. REPLACE anahtar sözcüğü çakışmada dosyadaki satırın doğru olduğunu varsayar ve tablodaki satırı onunla değiştirir; IGNORE ise tablodaki satırın doğru olduğunu varsayar ve dosyadaki çakışan satırı atlayıp devam eder:

LOAD DATA INFILE '/tmp/prospects.txt'
REPLACE INTO TABLE prospect_contact
FIELDS TERMINATED BY '|';

LOAD DATA INFILE '/tmp/prospects.txt'
IGNORE INTO TABLE prospect_contact
FIELDS TERMINATED BY '|';

Satır düzeyinde ekleme ve güncelleme mantığının (INSERT ... ON DUPLICATE KEY UPDATE ve REPLACE) ayrıntıları MariaDB INSERT, UPDATE ve DELETE yazısında anlatılıyor.

Karakter seti: dosya hangi kodlamada?

LOAD DATA INFILE dosyayı açtığında içeriği character_set_database sistem değişkeninin gösterdiği karakter setiyle okumaya çalışır. Dosya başka bir kodlamayla yazılmışsa komuttaki CHARACTER SET bölümüyle doğru kodlamayı bildirmeniz gerekir. Belgelere göre komut SET NAMES ile ve character_set_client ile verilen karakter setini dikkate almaz; CHARACTER SET binary ise "dönüştürme yapma" anlamına gelir. Komut dosyadaki tüm alanları, sütun tipinden bağımsız olarak aynı karakter setinde kabul eder ve ucs2 karakter setiyle yazılmış dosyalar yüklenemez.

Dosyayı mariadb-dump -T ya da SELECT INTO OUTFILE ile ürettiyseniz istemcide --default-character-set seçeneğini kullanarak çıktının istediğiniz kodlamayla yazılmasını sağlayın. Karışık karakter setleri söz konusuysa belgeler kaçış dizilerinin doğru yorumlanması için hem dışa aktarmada hem içe aktarmada CHARACTER SET bölümünü kullanmayı önerir. Türkçe karakterlerin bozulmaması için tablonuzun ve dosyanızın kodlamasını MariaDB karakter seti ve collation yazısındaki gibi baştan planlayın.

Güvenlik: FILE yetkisi, LOCAL ve secure_file_priv

Sunucu tarafındaki dosyayı okuyan LOAD DATA INFILE için kullanıcının FILE yetkisi olmalıdır; belgelere göre bu, sıradan kullanıcıların sistem dosyalarını okuyamaması içindir. LOAD DATA LOCAL INFILE bu yetkiyi gerektirmez, çünkü dosyayı istemci okur ve içeriği sunucuya gönderir. Yetkileri nasıl vereceğinizi MariaDB GRANT ve REVOKE yazısında bulabilirsiniz.

secure_file_priv sistem değişkeni ayarlıysa yüklenecek dosya o dizinde olmalıdır; değişkenin varsayılanı yoktur ve dinamik değildir, yani seçenek dosyasından ayarlanıp sunucu yeniden başlatılır. Ayrıca belgeler MariaDB'nin systemd birim dosyasının varsayılan olarak /home, /root ve /run/user dizinlerine erişimi kısıtladığını hatırlatır; dosyanız bu dizinlerdeyse sunucu onu okuyamayabilir. Bu kısıtın nasıl değiştirileceği MariaDB systemd ile başlatma ve durdurma yazısının konusudur.

LOCAL özelliği hem sunucuda hem istemcide kapatılabilir. Sunucuda local_infile değişkeni 0 yapılırsa (belgelere göre varsayılanı ON) bu tür yüklemeler hata verir. Komut satırı istemcisinde --local-infile seçeneği LOCAL yeteneğini açar ya da kapatır; sunucu desteklemiyorsa istemcide açmanın etkisi olmaz. Özellik iki taraftan birinde kapalıysa şu hata döner:

The used command is not allowed because the MariaDB server or client
  has disabled the local infile capability

mariadb --local-infile=1 -u uygulama -p magaza

Belgeler ayrıca LOCAL anahtar sözcüğü kullanıldığında STRICT_TRANS_TABLES gibi katı kiplerin devre dışı kaldığını belirtir. Bu yüzden hatalı değerler hata yerine uyarıya dönüşebilir; yüklemeden sonra SHOW WARNINGS ile uyarıları mutlaka inceleyin.

Kilitler, öncelik, tetikleyiciler ve replikasyon

Tablo düzeyinde kilitleme yapan motorlarda (MyISAM, MEMORY ve MERGE) LOW_PRIORITY, ekleme işlemini tabloyu okuyan başka istemci kalmayana kadar erteler. MyISAM'da bunun yerine CONCURRENT ile eşzamanlı ekleme yapılabilir; iki anahtar sözcük aynı ifadede kullanılamaz. Komut çalışırken tablonun INSERT tetikleyicileri de çalışır; tetikleyicilerin maliyeti büyük yüklemelerde fark edilir hâle gelebilir, ayrıntılar MariaDB trigger yazısında.

Belgelere göre LOAD DATA INFILE, ifade tabanlı (statement-based) replikasyon için güvensiz kabul edilir; replikasyon kullanıyorsanız binlog biçiminizi buna göre değerlendirin, ayrıntı için MariaDB binary log yazısına bakın. Uzun süren yüklemelerde başka bir oturumdan SHOW PROCESSLIST ile ilerlemeyi izleyebilirsiniz; komut ilerleme raporlamasını destekler. Bu konuda SHOW PROCESSLIST ve KILL yazısı yardımcı olur.

mariadb-import aracı

mariadb-import metin dosyalarını tablolara yükleyen bir komut satırı aracıdır ve eski adıyla mysqlimport olarak da çağrılabilir (Linux'ta sembolik bağlantı üzerinden). Kullanımı mariadb-import [SEÇENEKLER] veritabanı dosya1 [dosya2 ...] biçimindedir ve sistem kabuğunda çalıştırılır, mariadb istemcisinin içinde değil. En önemli kural şudur: dosya adının uzantısız kısmı tablo adıyla aynı olmalıdır. prospect_contact.txt dosyası prospect_contact tablosuna yüklenir; adlar uymuyorsa dosyayı yeniden adlandırın. Belgelerdeki örnek, yukarıdaki LOAD DATA seçeneklerinin komut satırı karşılıklarını gösterir:

mariadb-import --user='your_username' --password='your_password' \
    --fields-terminated-by='|' --lines-terminated-by='\r\n' \
    --replace --low-priority --fields-enclosed-by='"' \
    --fields-escaped-by='#' --ignore-lines='1' --verbose \
    --columns='name_last,name_first,telephone' \
    sales_dept '/tmp/prospect_contact.txt'

Belgelere göre soket üzerinden bağlanıldığında dosyayı sunucu doğrudan açıp okur, diğer durumlarda dosyayı istemci açar; dosyaların her durumda istemci tarafından okunmasını istiyorsanız --local verin. Birden çok dosyayı paralel yüklemek için --use-threads kullanılabilir:

mariadb-import --use-threads=10 database text-file-name [text-file-name...]

Parolayı komut satırına yazmak belgelerde de güvensiz kabul edilir; parola değeri verilmezse araç terminalden sorar. Sık kullandığınız seçenekleri [mariadb-import] ya da [client] grubuna yazarak seçenek dosyasından okutabilirsiniz.

LOAD DATA kapalıysa: mariadb-dump ile geçici çözüm

Bazı barındırma hizmetleri güvenlik nedeniyle LOAD DATA INFILE ya da mariadb-import kullanımını kapatır. Belgelerdeki rehber bunun için şu yolu önerir: metin dosyasını yerel bir MariaDB sunucusundaki tabloya LOAD DATA ile yükleyin, sonra mariadb-dump --no-create-info ile yalnızca INSERT ifadelerinden oluşan bir SQL dosyası üretin ve bu dosyayı uzak sunucuda mariadb istemcisiyle çalıştırın:

mariadb-dump --user='local_user' --password='local_pass' --no-create-info local_db prospect_contact > /tmp/prospects.sql
mariadb --user='remote_user' --password='remote_pass' remote_sales_dept < /tmp/prospects.sql

mariadb-dump'ın LOAD DATA'daki gibi bir REPLACE bayrağı olmadığı için, hedef tabloda yinelenen satırlar olabilecekse rehber dosyadaki INSERT INTO ifadelerini REPLACE INTO ile değiştirmeyi ve sonucu dikkatle test etmeyi önerir.

Ters yön: SELECT ... INTO OUTFILE

Bir tabloyu sunucuda metin dosyasına yazmak için SELECT ... INTO OUTFILE kullanılır. Hedef dosya önceden var olmamalıdır, üzerine yazılamaz; kullanıcının FILE yetkisi ve sunucunun o dizine yazma izni olmalıdır. secure_file_priv ayarlıysa dosya yalnızca o dizine ya da alt dizinlerine yazılabilir. CHARACTER SET verilmezse dönüştürme yapılmaz. Belgelerdeki örnek CSV biçiminde bir dosya üretir:

SELECT customer_id, firstname, surname FROM customer
  INTO OUTFILE '/exportdata/customers.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n';

Küçük bir uygulama: ürün listesini CSV'den yüklemek

Bir e-ticaret projesinde tedarikçiden noktalı virgülle ayrılmış, ilk satırı başlık olan, Windows satır sonlu ve fiyatı lira cinsinden veren bir CSV aldığınızı düşünelim. Dosya kendi bilgisayarınızda olduğu için LOCAL kullanıyor, mevcut ürünlerin bozulmaması için IGNORE ile çakışan SKU'ları atlıyor, fiyatı kuruşa çevirip ekleme zamanını NOW() ile dolduruyoruz:

CREATE TABLE urunler (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  sku VARCHAR(32) NOT NULL UNIQUE,
  ad VARCHAR(200) NOT NULL,
  fiyat_kurus INT UNSIGNED NOT NULL,
  eklenme DATETIME NOT NULL
) CHARACTER SET utf8mb4 COLLATE utf8mb4_turkish_ci;

LOAD DATA LOCAL INFILE '/home/ali/urunler.csv'
IGNORE INTO TABLE urunler
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(sku, ad, @fiyat)
SET fiyat_kurus = @fiyat * 100, eklenme = NOW();

SHOW WARNINGS;

Sonuçta dosyadaki her satır için SKU ve ad doğrudan sütunlara, fiyat ise kullanıcı değişkeni üzerinden çarpılarak fiyat_kurus sütununa yazılır; tablo zaten aynı SKU'yu içeriyorsa o satır atlanır. LOCAL kullanıldığı için katı kip devre dışıdır, bu yüzden son satırdaki SHOW WARNINGS kesilmiş ya da dönüştürülemeyen değerleri görmenin yoludur. Dosya çok büyükse belgelerdeki hızlı ekleme sayfasının önerilerine bakın: boş bir tabloya yüklemede benzersiz olmayan index'ler otomatik olarak devre dışı bırakılır, gerekirse unique_checks ve foreign_key_checks oturum değişkenleri geçici olarak kapatılabilir.

Sık yapılan hatalar

• Dosyanın sunucuda değil istemcide olduğunu unutmak. Dosya kendi bilgisayarınızdaysa LOCAL gerekir; aksi hâlde sunucu dosyayı kendi diskinde arar.
• FILE yetkisi olmayan kullanıcıyla sunucu tarafı yükleme. LOAD DATA INFILE bu yetkiyi ister.
• secure_file_priv dışındaki dizin. Değişken ayarlıysa dosya o dizinde olmalıdır.
• Yanlış karakter seti. Dosyanın kodlamasını CHARACTER SET ile bildirin; SET NAMES bu komutu etkilemez.
• Satır sonunu yanlış belirtmek. Windows dosyalarında '\r\n' gerekir; yoksa son sütunun sonuna görünmez bir karakter eklenir.
• mariadb-import'ta dosya adı ile tablo adının uyuşmaması. Dosyanın uzantısız adı tablo adı olmalıdır.
• Uyarıları okumamak. Özellikle LOCAL ile katı kip kapalı olduğundan hatalı değerler sessizce dönüştürülebilir.

Sık Sorulan Sorular

LOAD DATA INFILE ile LOAD DATA LOCAL INFILE arasındaki fark nedir?

İlki dosyayı sunucunun dosya sisteminden okur ve FILE yetkisi ister. İkincisinde dosyayı istemci okur ve sunucuya gönderir; FILE yetkisi gerekmez ama hem sunucuda hem istemcide LOCAL desteğinin açık olması gerekir. Belgelere göre LOCAL sunucu tarafı okumaya göre daha yavaştır, ama fark çok büyük değildir.

CSV başlık satırını nasıl atlarım?

Komuta IGNORE 1 LINES ekleyin; mariadb-import için karşılığı --ignore-lines=1'dir.

Yükleme sırasında bir sütunu hesaplayabilir miyim?

Evet. Sütun listesinde değeri bir @değişken'e okuyup SET sütun = ifade ile dönüştürebilirsiniz.

Hangisi daha hızlı: INSERT mi LOAD DATA mı?

Belgelere göre MariaDB'ye veri eklemenin en hızlı yolu LOAD DATA INFILE'dır; SQL ayrıştırması yapılmaz, veri büyük bloklar hâlinde okunur ve boş tabloda benzersiz olmayan index'ler işlem süresince devre dışı kalır. INSERT kullanmak zorundaysanız çok satırlı ekleme ve işlemleri gruplamak yardımcı olur.

Marka adları sahiplerine aittir. Bu yazı bağımsız bir teknik rehberdir; adı geçen kuruluşlarla bayilik veya ortaklık ilişkisi bulunduğu anlamına gelmez.

İlgili içerikler: MariaDB öğrenme rehberi · mariadb-dump ile yedekleme · MariaDB karakter seti ve collation · MariaDB INSERT, UPDATE ve DELETE · MariaDB GRANT ve REVOKE · MariaDB FULLTEXT arama