Datu-baseak ziztuan bete `INSERT` ordez `COPY` erabiliz

2021.03.19 frikikeriak

Webgune honetako orrialde ezberdinak kuxkuxeatu badituzue, informatika ingeniaria naizela irakurriko zenuten, ikasketaz zein ofizioz. Azkenaldian nire lanak datu-baseak maneiatzean datza, hala nola datuak sartu, ezabatu, eguneratu, eta beste hainbeste.

PostgreSQL database management system (DBMS, euskaraz datu-base kudeaketa-sistema) erabiltzen dut. Unibertsitateko ikasketetan zehar MySQL (orain MariaDB), Oracle eta SQLite-rekin jardun ondoren, Karrera Amaierako Proiektuarekin hastear nenbilenean ezagutu nuen. Zergatik aldaketa? Software askea izateaz gain, besteak beste, PL/pgSQL lengoaia prozeduralagatik —Oracle-ren PL/SQL oinarritua—, non zuzenean datu-basearekin prozesu konplexuagoak garatu baitaitezke.^[1]

Horrelako batean, prozesua nahi baino geldoago zihoala-eta —presarekin nenbilen, agian gehiegi—, bizkortzeko alternatiben bila hasi nintzen. Eta hara non aurkitu nuen COPY, zeinak zuzenean fitxategi mota batzuk —CSV da ohikoena— datu-basera sartzea ahalbidetzen duen. Dokumentazioa irakurri ondoan, psql zerabilen script bat garatu nuen, konponbide azkar, arin eta errazena zelako. Zeharo harrituta geratu nintzen. 40 GB inguru ordu bat baino gutxiagotan sartu ziren, adibidez. Hori bata bestearen atzetik egin nuela, parallel erabili barik.

Sines nazazuen, hurrengo paragrafoetan Python-en garatutako adibide oso batekin erakutsiko dizuet COPY aginduaren potentzia ohiko INSERT aginduarekin alderaturik. Anitzek darabilten psycopg2 moduluaren laguntzaz.^[2]

Lehenik eta behin, datu-base sistemaren osotasuna bermatzearren, badaezpada, berri bat sortzea hobe. Honetarako, createdb kontsola-programa eskaintzen du PostgreSQL-ek.

$ createdb test -e
SELECT pg_catalog.set_config('search_path', '', false);
CREATE DATABASE test;

Jakina, datu-basean lan egiteko konektatzea premiazkoa da, bide batez psycopg2-ek kurtsore deritzona sortuz:

conn = psycopg2.connect(dbname='test', user='postgres')
cur = conn.cursor()

Datuak non edo non kokatu behar dira. Horretarako hiru zutabedun numbers izeneko taula sortuko da:

Zutabea	Esanahia
`number`	Zenbakia bera (hau da, 1).
`cardinal`	Kardinala (bat).
`ordinal`	Ordinala (lehen).

Betiere —usadioz, esperientziagatik— IF NOT EXISTS jarriz, nahiz testuinguru honetan hautazkoa izan:^[3]

CREATE TABLE IF NOT EXISTS numbers (
    number integer,
    cardinal text,
    ordinal text
);

Python-en itxura hau izango duelarik:

create_numbers_table = \
"""
CREATE TABLE IF NOT EXISTS numbers (
    number integer,
    cardinal text,
    ordinal text
);
"""
cur.execute(create_numbers_table)

Ondo, taula sortu da. Hutsik, noski. Orain, inflect moduluaren laguntzarekin, zerotik hasita milioi bat zenbakiren informazio hori idatziko da CSV fitxategi batean —ingelesez—, hurrengo kodearekin:

csv_filename = 'numbers.csv'
columns = ["number", "cardinal", "ordinal"]

p = inflect.engine()
with open(csv_filename, 'w', newline='') as csvfile:
    num_writer = csv.writer(csvfile)
    num_writer.writerow(columns)
    for i in range (0, 1000000):
        num_writer.writerow([i, p.number_to_words(i), p.number_to_words(p.ordinal(i))])

Fitxategi luzeegia da blog-posta batean txertatzeko, beraz lehen 5 lerroekin ideia bat egin dezakezue.

$ head -5 numbers.csv | csview
+--------+----------+---------+
| number | cardinal | ordinal |
+--------+----------+---------+
| 0      | zero     | zeroth  |
| 1      | one      | first   |
| 2      | two      | second  |
| 3      | three    | third   |
+--------+----------+---------+

Esan bezala, taula bi modutan beteko da:

Fitxategi osoa batera, COPY erabiliz:

Komando honen sintaxia nahiko erraza da (ikus esteka). Sarrera estandarretik irakurri nahi denez, numbers.csv ordez STDIN jarriko da. Datu-basearekin bitartekari baten laguntzaz gaudelako hizketan, zer edo zelan esatearren.

Ohartxo bat, badaezpada ere: fitxategia gzip-ekin konprimituta balego, lehenik deskonprimitu egin beharko litzateke eskura ditugun tresnekin. Adibide batzuk: Pythonen, izen bereko modulua dago; POSIX-en, zcat; Javan, GZIPInputStream klasea, etab.

Guzti hau kontuan hartuta, honela geratuko litzateke:
```
 COPY numbers FROM STDIN CSV HEADER DELIMITER ','
```
Zorionez, psycopg2-ek funtzionaltasun honen euskarria du. Kakotxak direla-eta copy_from() kexatu ondoren, copy_expert() erabiltzea hoberena, berariazko erabilpenak ahaltzen baititu.
```
 copy_numbers = "COPY numbers FROM STDIN CSV HEADER DELIMITER ','"
 start = time.perf_counter()
 cur.copy_expert(copy_numbers, open(csv_filename))
 end = time.perf_counter()
 copy_time = end - start
```
Lerro bakoitzeko INSERT bat exekutatuz:

SQL-ko klasikoak diren SELECT, UPDATE eta DELETE-ren senidea,^[4] zeinak errenkada bat taula batean eransten duen. Segurtasuna bermatzearren, nahiz eta localhost-en premiazkoa ez izan, SQL injection lako ahultasunak saihesteko prepared statement deritzona erabiliko da, gisa honetan:
```
 INSERT INTO numbers (number, cardinal, ordinal) VALUES (%s, %s, %s)
```
Exekutatu baino lehen adierazpena prestatuko da. %s horiek, hurrenez hurren, CSV-ko eduki baliokieekin ordezkatuko dira datuak gehitzerakoan; zutabeeen moten arabera egiaztapenak eginez. Nire gomendioa hauek erabiltzearen ohitura hartzea da, zeren jende-modu maltzurrek argitaratutako zerbitzuetan datuak lapurtu edo ezabatzeko aukera ez baitute alferrik galduko. Denbora joan ahala, erreflexu bilakatuko da.

Ahalik eta lasterren exekutatu dadin, sortu berri den numbers.csv fitxategia irakurriko da lerroz lerro. Goiburua taularen zutabe-izenen zerrenda denez, DictReader erabil dezakegu guztia errazteko:
```
 insert_number = "INSERT INTO numbers (number, cardinal, ordinal) VALUES (%s, %s, %s)"
 with open(csv_filename, 'r', newline='') as csvfile:
     num_reader = csv.DictReader(csvfile)
     start = time.perf_counter()
     for row in num_reader:
         cur.execute(insert_number, (row['number'], row['cardinal'], row['ordinal']))
     end = time.perf_counter()
 insert_time = end - start
```

Konturatuko zineten iraupenak copy_time eta insert_time aldagaien bidez kalkulatu direla, hurrenez hurren. Informazio hau kontsolan forma estetiko batean erakusteko, tabulate erabiliko da:

table = [["COPY",copy_time],["INSERT",insert_time]]
headers = ["Statement", "t (s)"]
print(tabulate(table, headers, floatfmt=".2f"))

Kode osoa hemen ikusgai.

pgsql_copy_vs_insert.py

import csv
import time
import inflect
import psycopg2
from tabulate import tabulate

csv_filename = 'numbers.csv'
columns = ["number", "cardinal", "ordinal"]
create_numbers_table = \
"""
CREATE TABLE IF NOT EXISTS test.numbers (
    number integer,
    cardinal text,
    ordinal text
);
"""
copy_numbers = \
"""
COPY test.numbers from STDIN CSV HEADER DELIMITER ',' NULL AS 'null'
"""
insert_number = \
"""
INSERT INTO test.numbers (number, cardinal, ordinal) VALUES (%s, %s, %s)
"""

try:
    p = inflect.engine()

    conn = psycopg2.connect(dbname='test', user='postgres')
    cur = conn.cursor()

    cur.execute(create_numbers_table)

    with open(csv_filename, 'w', newline='') as csvfile:
        num_writer = csv.writer(csvfile)
        num_writer.writerow(columns)
        for i in range (0, 1000000):
            num_writer.writerow([i, p.number_to_words(i), p.number_to_words(p.ordinal(i))])

    start = time.perf_counter()
    cur.copy_expert(copy_numbers, open(csv_filename))
    end = time.perf_counter()
    copy_time = end - start

    with open(csv_filename, 'r', newline='') as csvfile:
        num_reader = csv.DictReader(csvfile)
        start = time.perf_counter()
        for row in num_reader:
            cur.execute(insert_number, (row['number'], row['cardinal'], row['ordinal']))
        end = time.perf_counter()
    insert_time = end - start

    table = [["COPY",copy_time],["INSERT",insert_time]]
    headers = ["Statement", "t (s)"]

    print(tabulate(table, headers, floatfmt=".2f"))

except (Exception, psycopg2.DatabaseError) as error:
    print(error)

finally:
    if conn is not None:
        conn.close()

Nire saiakeren arabera, gutxienez 25 bider azkarragoa da lehena bigarrena baino. Esate baterako:

$ python pgsql_copy_vs_insert.py
Statement      t (s)
-----------  -------
COPY            1.65
INSERT         46.01

Iraupen hauekin, eta mila bider datu gehiagorekin COPY-k, alde batetik, ordu erdi inguru iraungo luke eta INSERT-ek, bestalde, hamabi. parallel gabe, jakina. Benetan aintzat hartzeko gauza, ez da hala?

Gauza konplexuagoak egin nahi izanez gero, Python ere erabil daiteke, bide batez: PL/Python du izena. PL/pgSQL-en ezinezkoak edo motelak diren konputazioak egiteko oso ongi dator. Edozelan ere, C-n edo C++-n hedapenak gara daitezke. ↩︎
Egiari zor, lanean darabildan Java-n garatu nuen lehenik, CopyManager erabiliz, besteak beste. Alde batetik, ez du fama onik, ez (hain) handirik Python-en aldean; bestetik, JDBC (Java Database Connectivity) interfazea azaltzeak ez du merezi. ↩︎
PEP 249 errespetatuz, autocommit modua beti dago itzalita —hau da, datuak ez dira gordeko—; garatzaileak piztu behar du eta commit() erabili datuak gorde daitezen. Hala denean, script hau behin eta berriz exekutatzean taula lehen aldian bakarrik sortuko da. Kontuan izan, baita ere, TRUNCATE erabili ezean etengabe handituko dela. ↩︎
Datu-baseetako CRUD (Create, Read, Update, Delete) oinarrizko operazioak. Hurrenez hurren, INSERT, SELECT, UPDATE eta DELETE. ↩︎

Ohar honi buruzko iruzkinik? Hala bada, bidali iezadazu mezu bat hemengo alternatibaren bat erabiliz!