SQL ja GEDCOM – VOL 1

Sissejuhatus

Kaks mu huviala – SQL ja sugupuud viisid mu selleni, et mõtlesin teha andmebaasi sugupuu andmetega, neid siis koos analüüsida, teha mõningast statistikat ja lahendada põnevaid graafiülesandeid.

Põhikoht, kus ma sugupuuandmeid hoian on Geni ja sealt on võimalik andmetest teha väljavõtteid GEDCOM formaadis. Kas paljud mu sõbrad on öelnud, et tuleks teha igaks juhuks koopia seal olevaist andmetest.

GEDCOM export on pisut piiratud – 50000 kirjega, nii et suurema suguvõsa alla-laadimiseks tuleb teha mitu exporti. Õnneks on sama isiku kirjed alati sama koodiga, mis võimaldab neid exporte kokku mängida.

Praeguseks saan mängida üsna suure andmebaasiga, saan testida nii graafiülesannete kiiruseid kui laadimiste jõudluseid.

Olen selle töö käigus päris mitu SQL teemalist probleemi lahendanud ja ehitanud ka Excelis mõnusa töövahendi sugulusteede lugemiseks. Andmeid ma ei jaga, aga loodud vahendid koos väikeste selgitustega jagan heal meelel. Nii, et kes soovib saab alla-laetud GEDCOM failidega samamoodi toimetada.

Praegune artikkel on esimene seeriast – ettevalmistus. Järgmises kirjeldan, kuidas ettevalmistatud andmetest vajalik välja lugeda ja tööle panna, seejärel teen mõned näited kuidas graafiülesandeid – sugulusteid – lahendada ning seejärel vaatame koos üht omatehtud päringuvahendit – Excel koos SQLi ja powerqueryga. Lõpuks on mul plaanis genereerida kokkupandud tabeli najal soovitava hulga kirjetega oma GEDCOM faile, et neid siis kasvõi mõnes sugupuuprogrammis (näiteks FamilyTreeMaker) ära kasutada.

0. GEDCOM faili struktuur ja ülesehitus

GEDCOM on Genealoogiaprogrammide tavaformaat andmete salvestamiseks ja vahetamiseks.

0 @I1448552@ INDI
1 NAME Henn /Sarv/
2 GIVN Henn
2 SURN Sarv
2 NICK सींग
1 SEX M
1 BAPL
2 DATE 20 APR 1929
1 ENDL
2 DATE 21 NOV 1929
1 BIRT
2 DATE 7 MAR 1955
2 ADDR
3 CITY Tallinn
3 CTRY Estonia
1 GRAD
2 DATE 1961
…
1 FAMC @F6000000001528333119@
1 FAMS @F6000000001656449693@
2 NOTE {geni:marriage_order} 1
1 FAMS @F6000000001646452992@
2 NOTE {geni:marriage_order} 2

Fail sisaldab objekte (INDI – isik, FAM – perekond jne) nende atribuute (NAME, BIRT) ja komponente (NAME komponendid GIVN, SURN, BIRT komponendid DATE ja ADDR, omakorda nende komponente (ADDR komponendid CITY, CTRY) jne. Iga rea alguses on tasemenumber (0 – objekt, 1 – atribuut, 2 – selle osaatribuut e komponent jne jne). Loogika on siis selline, et suurema tasemega rida täpsustab-täiendab sellele eelnenud väiksema taseme numbriga kirjet jne.

Nii antud fragment kirjeldab, et Isik koodiga I1448552 kannab nime Henn Sarv (eesnimi GIVN Henn, perenimi SURN Sarv, hüüdnimi NICK सींग) ja on sündinud (BIRT) kuupäeval (DATE 7 MAR 1955) kohas (ADDR CITY Tallinn CTRY Estonia)

Kirjes on ka viited teistele objektidele – nii on viide kolmele perekonnale (FAM) ühele, kuhu Henn kuulub lapsena (FAMC koodiga F6000000001528333119) ja kahele, kuhu Henn kuulub lapsevanemana (FAMS).

Eraldi objektidena on tagapool perekonnad

0 @F6000000001528333119@ FAM
1 MARR
2 DATE 28 AUG 1936
1 HUSB @I6000000001528333113@
1 WIFE @I6000000001528640081@
1 CHIL @I6000000001592998311@
1 CHIL @I6000000001604210254@
1 CHIL @I6000000001604632474@
1 CHIL @I6000000001528309143@
1 CHIL @I6000000001617582391@
1 CHIL @I6000000012433193658@
1 CHIL @I1448552@

See on näiteks perekond koodiga F6000000001528333119, kus vanemad on abiellunud (MARR) kuupäeval (DATE 28 AUG 1936), kus isa (HUSB) on isik koodiga I6000000001528333113, ema (WIFE) isik koodiga I6000000001528640081 ja lapsed (CHIL 7 tükki).

1. Esimene samm – GEDCOM faili lugemine andmebaasi

  • Probleemid – UTF-8 / Unicode
  • Ridade nummerdamine

Selleks, et GEDCOM failid laadida andmebaasi oli hea ja lihtne kasutada BULK INSERT käsku, sellega on aga kolm probleemi.

Esiteks, ei saa faili laadimisel selle nime parameetriga ette anda – seega tuleb kasutada kokkupandud SQL skripti-lauset.

Teiseks, ei tunnista SQL server UTF-8 formaadis faile – seega tuleb allalaetud failid teisendada UTF-8-st UNICODE formaati. Selleks saab hõlpsasti kasutada POWERSHELLi käsku:

Get-Content -Encoding UTF8 -path $x | Set-Content -Encoding Unicode –path $x

Kolmas probleem on faili ridade nummerdamine. SQLis on kõik hulgad (tabelid) „järjestamata“, seda isegi siis kui tabelil on cluster index. GEDCOM faili puhul on aga ridade järjestus ülioluline. Paraku ei saa BULK INSERT käsuga laadida muud kui failist tulev info, INSERT FROM OPENROWSET käsku aga ei õnnestu antud juhul kasutada.

Selle kolmanda probleemi lahendamiseks leidsin sellise viisi:

  • Tegin tabeli, millel IDENT veerg ja kuhu loen tabeliread
  • Tegin VIEW, milles kuvatakse vaid see failirea veerg
  • BULK INSERT käsule annan ette tehtud VIEW

Nii lähevad kirjed tabelisse numbritega ja on alati üheselt viidatavad.

Lisasin ka veel tabeli, kuhu kantakse laadimis-ajalugu, et oleks hea hiljem tuvastada ja vältida topelt laadimisi ning pisut ka logiraamatut pidada.

1.1 Loome vajalikud osad

  • Tabel gedcom_data:
CREATE TABLE dbo.gedcom_data
(
	nr bigint IDENTITY(1,1) PRIMARY KEY,
	line nvarchar(1024) NULL,
)
  • View gedcom_file:
CREATE VIEW dbo.gedcom_file
AS
SELECT line FROM dbo.gedcom_data
  • Table load_history:
CREATE TABLE dbo.load_history
(
jrknr int IDENTITY(1,1) PRIMARY KEY,
first_nr bigint NOT NULL,
ridu bigint NOT NULL,
filename nvarchar(258) NULL,
load_date datetime NULL
)
  • Protseduur Loe_Gedcom:
CREATE PROCEDURE [dbo].[Loe_Gedcom] (@file nvarchar(256))
as
select h.load_date, h.filename from load_history h where h.filename = @file
IF @@ROWCOUNT = 0
BEGIN
	declare @x nvarchar(max) = 
'
DECLARE @i int = (select coalesce(max(nr), 0) from dbo.gedcom_data) + 1
IF @i = 1 set @i = (select coalesce(max(nr), 0) from dbo.gedcom_data_arhiiv) + 1
begin tran;
bulk insert dbo.gedcom_file
from ''' + @file + '''
with (
rowterminator = ''\r''
, datafiletype = ''widechar''
);
insert dbo.load_history (first_nr, ridu, filename) values (@i, @@rowcount, ''' + @file + ''');
commit;
'
execute(@x)
end

else
print 'juba laetud: ' + @file

1.2 Käivitamine:

exec Loe_Gedcom 'c:\kataloog\failinimi.ged'

Selliseid siis 1 iga faili kohta. Ise kasutan selliste SQL lausete ’genereerimiseks’ POWERSHELLi aga võib samaks teha ka SQLCMD protseduuri

1.3 Korrastamine

Kogemus näitab, et loetud ridades tuleb teha paar korrigeerimist (SQLis on seda lihtsam teha kui näiteks Powershellis).

update gedcom_data set line = ltrim(replace(line, char(10), ''))

2 Esmane lahtivõtmine

Järgnevalt tuleks loetud ridades teha mõned teisendused (teisendused rea tasemel). Ilmselt oleks need mõistlik kombineerida eelneva teisendusega (1.3) – tabel vaja läbi käia üks või kaks korda. Ühe faili puhul ei ole see väga mahukas (ridu umbes miljoni ringis), aga kui korraga lugeda sisse nii 100-kond GEDCOM faili, läheb ridade arv suureks ja on mõistlik võimalikult ühe toimetamisega teha reateisendused.

Samuti tuleks väga suure arvu GEDCOM failide puhul implementeerida portsionikaupa töötlus, et hoida kokku transaction logi mahtu.

Kuna GEDCOM sisaldab päris palju kirjeid, mida ma hilisemas analüüsis ei kasuta, siis oleks mõistlik need välja jätta, kustutada aga ei saa, sest üks eesmärk – uue GEDCOM faili genereerimine, eeldab ka neid. Seepärast tegin eraldi tabeli, kuhu eraldan ja täidan need kirjed, mis sisaldavad isiku, perekonna ja suguluse andmeid.

Algselt koostasin view, mille kaudu „paistsid“ need kirjed, mida mul vaja ning tabeli kuhu need salvestada. Väikese arvu puhul nii asi ka toimis, suure mahu puhul tuli hakata kirjeid üle kandma ’portsioni’ kaupa, viewst portsionite lugemine aga ei andnud soovitavat jõudlust. Seepärast ehitasin view baasil ülekandmise protseduuri.

2.1 Loome vajalikud osad

  • View parsed_view
CREATE VIEW [dbo].[parsed_view]
as
with t1 as
(
	select
	nr
	, TRY_PARSE(left(line,2) as int) lvl
	, line
	, CHARINDEX('@', line) algus
	from dbo.gedcom_data
),
t2 as
(
	select *
	, case when algus > 0 then CHARINDEX('@', line, algus+1) end lõpp
	from t1
	where lvl is not null
),
t3 as
(
	select *
	, case when algus > 0 and lõpp > 0 then SUBSTRING(line, algus, lõpp-algus+1) end kood
	, case when algus = 3 then SUBSTRING(line, lõpp + 2, 4) else SUBSTRING(line, 3, 4) end class
	, case when algus <> 3 then ltrim(substring(line, 7, 100)) end data
	from t2
)
select nr, lvl,
case when class in ('indi', 'fam') then kood end kood,   -- oluline, et koodid tulevad vaid õigetest ridadest
class, data
, case when class = 'DATE' then try_parse (data as date using 'en-us') end as datevalue
, case when class = 'DATE' then data end as datetext
, case when class = 'NAME' then REPLACE(data, '/', '') end as name
from t3
where class not in
(
'head','titl', 'sour', 'vers', 'subm',
'gedc', 'vers', 'form', 'char', 'nick',
'cont', 'note', 'addr', 'rfn', 'time',
'obje', 'occu', 'phon', 'plac', 'post',
'chan', 'form', 'file', 'city'
)

Kõnealune view on kokku pandud sammhaaval kasutades CTE (common table expression). Hakatuseks on vaja leida koodid neis ridades, kus need on (tunnuseks @ … @ reas – kontrollisin, et andmetes kahte @ märki ei esine. On küll üks @ – meiliaadressides.

Kuna MS SQL keel (T-SQL) ei võimalda projektsioonis nimetatud avaldise aliast samas projektsioonis uuesti kasutada, siis esimesed kaks CTE-SELECTi on selleks, et saada ’kätte’ kood ilma pikka avaldist kirjutamata, avaldis t1 leiab @ esimese esinemise (kui see on), t2 leiab @ teise positsiooni sealsamas, kui see on ja t3 eraldab SUBSTR funktsiooniga koodi – on ta siis perekonnakirjes (0 @kood@ FAM) või isikukirjes (0 @kood@ INDI)

Rea alguses olev tasemenumber läheb vastusesse veeruks lvl, sellele järgnev kirje klassitunnus veergu class (NB! Avaldises t3 from t2 tehakse vahet – 0-taseme kirjetel tuleb klassi tunnus kirje lõpust, teisel kirje algusest tasemenumbri järelt

Lõpuks tehakse veel paar lihtsat teisendust:

  1. Kui klass on NAME, siis täidetakse veerg name (eemaldatakse kladkriipsud)
  2. Kui klass on DATE, siis proovitakse sisu teisendada kuupäevaformaati
  3. Ning lõpuks filtreeritakse välja need read (klassid), mida minu rakenduses ei kasutata – saab tulemus kompaktsem
  • Table parsed_data
CREATE TABLE dbo.parsed_data(
nr bigint constraint parsed_data_x0 primary key,
lvl int NULL,
kood char(24) NULL,
class char(4) NULL,
data nvarchar(1000) NULL,
datevalue date NULL,
datetext nvarchar(40) NULL,
name nvarchar(128) NULL,
)

CREATE NONCLUSTERED INDEX x1 ON dbo.parsed_data (kood)
INCLUDE (class, data, datevalue, datetext, name)

CREATE NONCLUSTERED INDEX x2 ON dbo.parsed_data (class)
INCLUDE (nr, datevalue, datetext)

CREATE NONCLUSTERED INDEX x3 ON dbo.parsed_data (lvl, kood)
INCLUDE (nr)

CREATE NONCLUSTERED INDEX x4 ON dbo.parsed_data (class)
INCLUDE (nr, kood, data)

See tabel on eelneva view tulemuste salvestamiseks. Indeksid on lisatud töö käigus käivitusplaanide soovituste alusel (missing index detail)

  • Protseduur parsed_load

Algselt kasutasin view tulemuse salvestamiseks INSERT-SELECT kombinatsiooni, kirjete arvu lisandumisel (korraga 100kond GEDCOM faili) tuli hakata laadima portsjonite kaupa

declare @start bigint =
(select top 1 nr from parsed_data order by nr desc)
-- kõige kiirem variant saada viimane number

declare @batch int = 1000000

insert top (@batch) parsed_data
select * from parsed_view
where nr > @start

Paraku aga WHERE fraas alguse leidmiseks ’ei jõua’ views esimese avaldiseni (t1 from gedcom_data) ja seepärast tuli teha laadimiseks eraldi protseduur – aluseks seesama view.

CREATE PROCEDURE dbo.Parsed_Load
@batch int = 1000000
as
declare @start bigint = (select coalesce( (select top 1 nr from parsed_data order by nr desc), 0))
;
with t1 as
(
	select nr
	, TRY_PARSE(left(line,2) as int) lvl
	, line
	, CHARINDEX('@', line) algus
	from dbo.gedcom_data
	where nr > @start
),
t2 as
(
	select *
	, case when algus > 0 then CHARINDEX('@', line, algus+1) end lõpp
	from t1
	where lvl is not null
),
t3 as
(
	select *
	, case when algus > 0 and lõpp > 0 then SUBSTRING(line, algus, lõpp-algus+1) end kood
	, case when algus = 3 then SUBSTRING(line, lõpp + 2, 4) else SUBSTRING(line, 3, 4) end class
	, case when algus <> 3 then ltrim(substring(line, 7, 100)) end data
	from t2
),
t4 as
(
	select nr, lvl,
	case when class in ('indi', 'fam') then kood end kood,   
	-- oluline, et koodid tulevad vaid õigetest ridadest
	class, data
	, case when class = 'DATE' then try_parse (data as date using 'en-us') end as datevalue
	, case when class = 'DATE' then data end as datetext
	, case when class = 'NAME' then REPLACE(data, '/', '') end as name
	from t3
	where class not in
	(
	'head','titl', 'sour', 'vers', 'subm',
	'gedc', 'vers', 'form', 'char', 'nick',
	'cont', 'note', 'addr', 'rfn', 'time',
	'obje', 'occu', 'phon', 'plac', 'post',
	'chan', 'form', 'file', 'city'
	)
)
insert top (@batch) parsed_data
select * from t4

2.2 Käivitamine osade kaupa

Eelolnud protseduuri (sama tehnikat olen ka teiste laadimiste puhul kasutanud, laadimisprotseduuri käivitatakse kuni @@rownount näitab, et midagi on laadida

select 1
while @@ROWCOUNT > 0
begin
	checkpoint
	exec Parsed_Load 1000000
end

Siin on esimene select selleks, et ROWCOUNT oleks kindlasti enne tsükli alustamist väärtustatud. Kui nüüd laadimise ajal (mis võib kesta kuni tund aega), protsess katkestada, jääb juba tehtu baasi.

Algsest laadimisest alles jäetud reanumbrid (nr) on väga hea järjehoidja – kõik tabelid jäävad selle järgi järjestatuks ning nii on hea viimast ja esimest rida leida.

2.3 Peale laadimist arhiivi

  • Gedcom_Data_arhiiv
  • Portsioni kaupa kustutus
  • Truncate table variant

Kui gedcomi read on laetud, teisendatud ja filtreeritud, tõstan GEDCOM_DATA tabeli arhiivi GEDCOM_DATA_ARHIIV. See tagab, et järgmised laadimised ei pea kõiki ridu uuesti läbi käima ega analüüsima.

Kõige lihtsam (ja alguses nii tegingi) viis on

set xact_abort on
begin tran

insert dbo.gedcom_data_arhiiv select * from dbo.gedcom_data
delete dbo.gedcom_data
commit

Keeruliseks läheb ikka ja jälle, kui ridu on palju. Selleks tegin taas tsükli

declare @batch int
declare @start bigint
select @batch = 1000000
while @@rowcount > 0
begin

checkpoint
select @start = coalesce((select top 1 nr from gedcom_data_arhiiv order by nr desc), 0)

insert top (@batch) gedcom_data_arhiiv
select * from gedcom_data where nr > @start

end

Seejärel tuleks gedcom_data tabel kustutada. See on tüütult aeganõudev, aga truncate table paneks identity algusse ja järgmised laadimised loeks ridu valesti. Seepärast kustutan sellise scriptiga:

declare @nr bigint
= (select top 1 nr from gedcom_data order by nr desc)

truncate table gedcom_data

dbcc checkident (gedcom_data, reseed, @nr)

3. Ridade vaheline teisendus

  • Kuupäevade ülekandmine
  • Koodide ülekandmine

Edasine peaks tegema read iseseisvaks. Praegu on GEDCOM faili moodi – ridade tegelik sisu sõltub naaberridadest – see teeb hilisemad päringud jube keerukaks. Mõistlik oleks teha kaks asja

  1. Kuupäeva andmed (DATE) kanda üle sündmuse (BIRT jne) kirjesse. Tegemist on alati naaberkirjetega – kirjele BIRT järgneb alati (kui on olemas) DATE kirje
  2. Isiku ja perekonna koodid tuleks kanda kõigile selle isiku kirjetele. Level-0 kirjed sisaldavad koodi ja see kood kehtib järgmise Level-0 kirjeni.

Üritasin seda korra teha kursoriga – juba esimene katse näitas, et see on mõttetu – ühe GEDCOM faili peal katsetades oleks see juba pea tund aega kestnud. Seepärast naasin ’normaalse’ SQLi juurde.

3.1 Protseduur Date_update ja selle ajalugu

Alguses tekin protseduuri, mis kasutades tabeli šelf-joini sidus tabeli naaberkirjed:

CREATE PROCEDURE [dbo].[Date_Update]
-- kannab kuupäevad LEVEL 3 kirjetelt LEVEL 2 kirjetele
as
update d
set datevalue = coalesce(d.datevalue,e.datevalue), datetext = coalesce(d.datetext,e.datetext)
--select *
from dbo.parsed_data d
join dbo.parsed_data e on d.nr+1 = e.nr
where d.class in ('birt', 'deat', 'marr', 'div') and e.class = 'date'

Kuna tabel on reanumbri (nr) järgi sorteeritud, siis ei toiks nimetet join väga kaua võtta. Kogemus näitas aga, et ikka võttis 100+ gedcomi puhul nii tunni ringis

Asendasin selle siis uue protseduuriga, mis isegi ei nõudnud portsjoni kaupa tüklit:

CREATE PROCEDURE [dbo].[Date_Update_2]
as
with e as
(
	select nr, datevalue, datetext
	, lead(datevalue) over (order by nr) n_datevalue
	, lead(datetext) over (order by nr) n_datetext
	, lead(class) over (order by nr) n_class
	from parsed_data d
)
update parsed_data

set datevalue = coalesce(d.datevalue,n_datevalue), datetext = coalesce(d.datetext,n_datetext)

from parsed_data d
join e on e.nr = d.nr
where n_class = 'date'
and d.class in ('birt', 'deat', 'marr', 'div')

Selline protseduur toimis väga ilusti ja kiirelt

3.2 Protseduur Kood-Flow

Järgmine samm on siis koodide kandmine vaheridadele. Selleks otstarbeks tegin järgmise protseduuri:

CREATE PROCEDURE [dbo].[Kood_Flow]   -- kannab koodid järgnevatele kirjetele
@batch int = 1000000
as
with koodid as
(
	select nr, kood
	from dbo.parsed_data where lvl = 0 and kood is not null
)
, vahemik as
(
	select
	nr, kood,
	(lead(nr) over (order by nr))-1 nnr
	from koodid
)
update top (@batch) v
set kood = i.kood
from dbo.parsed_data v
join vahemik i on v.nr between i.nr and i.nnr
where v.kood is null

Siin protseduuris vaadatakse tabelile otsa kahena – read kus on koodid (mastertabel) ja read kus ei ole koode ning nad joinitakse between prodikaadiga. See lead(nr) selles nn mastertabelis annab ilusti ridade vahemiku, mis kuulub kõnealuse masterkirje juurde. Ja muudetakse vaid neid kirjeid, millel veel ei ole koodi – see võimaldab protseduuri käivitada osade kaupa.

Selle käivitamiseks on taas vaja rowcount tsüklit:

select 1
while @@ROWCOUNT > 0
exec Kood_Flow -- vaikimisi miljon

Edasi…

järgmises osas kirjeldan, kuidas (minu arvates) kõige lihtsamini saadud tabelist eraldada isikukirjed, perekonnakirjed ja suguluskirjed.

This entry was posted in MS SQL Server, Sugupuud and tagged , . Bookmark the permalink.

Lisa kommentaar

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Muuda )

Twitter picture

You are commenting using your Twitter account. Log Out / Muuda )

Facebook photo

You are commenting using your Facebook account. Log Out / Muuda )

Google+ photo

You are commenting using your Google+ account. Log Out / Muuda )

Connecting to %s