Piibel ja SQL

Kuidas ma SQL serveriga Piiblit lugesin

Töö ja õpingutega seoses oli mul tarvis ja on ka edaspidi vaja Piiblit lugeda. Ja mitte lihtsalt vaid pisut ka loetut analüüsida. Piinlik tunnistada, et aega süstemaatiliselt ja mõttega järjepanu lugeda on vähevõitu – mõtlesin siis, et kasutan oma oskust ja vahendeid ja teen omal pisut lihtsamaks.

Töövahendid, mis mulle appi tulid olid – SQL Server, Excel ja PowerQuery ning PowerShell.

Aga et kõik ilusasti ära rääkida ja üles tunnistada – alustame algusest.

Piibel Veebis

Kõigepealt oli hea meel leida Piibliseltsi tõlkeversioon veebist – http://piibel.net – täielikul ja täiesti loetaval kujul. Tõsi ta on, siin on vaid eestikeelne versioon – on terve hulk veebilehti, kust saaks heebrea-, kreeka-, ladina-, saksa- ja ingliskeelseid tõlkeid. Nende analüüsimise ja koosvaatlemise jätan järgmiseks korraks.

PiibelDB2

Head sõbrad (kuulsin, et veebiversiooni hoitakse üleval entusiasmist) on veebilehe ehitanud väga hästi ja ülevaatlikult ja mis kõige toredam – lisaks inim-loetavale ka nn masin-loetaval kujul. Lisaks HTML-lehtedele ka XML-formaadis – vaja oli vaid selgeks teha veebiaadresside süsteem ja see oma huvides tööle panna.

PiibelDB3

Aadresside süsteem on iseenesest lihtne:

http://piibel.net/#q=Ps%208 – päring ’PS 8’ viitab Psalmid peatükk 8

http://piibel.net/#q=Ps%208:7 – päring ’PS 8:7’ viitab sama peatüki 7 värsile

http://piibel.net/.xml?q=Ps%208 – sama peatükk XML-formaadis

http://piibel.net/.xml?q=Ps%208:7 – sama värss XML-formaadis

Nüüd oleks vaid vaja koostada Piibli raamatute (ja lühendite) loetelu ning leida iga raamatu peatükkide arv.

Piibel Excelisse

Raamatute loendiga läks hõlpsasti – see on veebilehel täiesti olemas – ripploend soovitava raamatu leidmiseks. Kuidas seda sealt aga kätte saada?

PiibelDB1

Kirjeldan lihtsat sammhaaval teekonda:

  1. Piiblilehel vajutan F12 – avaneb arendaja keskkond. Ära ehmu!
  2. Leiad DOM Exploreri nupu ja seejärel viin kursori ripploendile. Klõps ja alumine sigrimigri (loe HTML kood) keritakse õigesse kohta.
  3. Loendile vastav element kokku, paremklõpsates pihku (Copy käsk – CTRL-C ei toiminud) ja sealt (Paste) Excelisse

PiibelDB4

Nüüd on loend Excelis ja vaja tabeliks vormida. Hea on, kui sul on Excelile (ver 2013) lisatud PowerQuery – otsi ja paigalda, ei maksa midagi aga rõõmu kui palju.

Edasi peab teadma, mida teha

  1. PowerQuery ribalt valin From Table – defineerin oma XMLiga pesa tabeliks, ütlen et päist mul ei ole – ta paneb ise.
  2. Seejärel otsin päringu editori teisenduste (transform) ribalt käsu PARSE from XML. Tulemuseks tabel ühe veeru ja ühe reaga.
  3. Kaks korda laiendan tabelit ja ongi käes raamatute loend. Selle laen Excelisse

Nüüd tuleks tabelit veidi täiendada – tabelis on raamatute nimed koos lühendiga („Esimene Moosese raamat(1Ms)“). Sellest tuleks teha kaks veergu – raamatu nimi ja lühend. Selle lahutamise oleks võinud ju ka PowerQueris teha, sama saaks ka paari valemiga. Seekord olin laisk:

  1. Lisasin veeru nimega Raamat, kirjutasin selle esimesele reale „Esimene Moosese raamat“. Teisele reale sain vaevu kirjutada „T“ ja juba Excel pakkus, et teeb selle minu eest lõpuni.
  2. Lisasin veel ühe veeru nimega Lühend, kirjutasin selle esimesele reale „1Ms“ ja teisel real piisas kirjutada „2“ – taas Excel teadis, kuidas lõpuni teha.

Viimase sammuna tuli tabelisse saada iga raamatu peatükkide arv – seekord jätan saladuseks, kuidas ma seda tegin, aga valmis ta sain (tegelikult lugesin peatükid käsitsi üle – võtsin ette iga raamatu esimese peatüki ja sellelt liikusin eelmisele, mis oligi eelmise raamatu viimane peatükk).

Piibel andmebaasi

Nüüd tuleks siis piiblisalmid lugeda arvutisse ja sealt andmebaasi nii et edasi võtsin ette SQL Management Studio ja ehitasin omale andmebaasi:

CREATE DATABASE Piibel
go
USE Piibel
go
CREATE TABLE dbo.Raamatud    -- siia tabelisse raamatute loetelu
(
	kood int IDENTITY(1,1) PRIMARY KEY,
	Lühend char(5) NULL,
	Nimetus nvarchar(100) NULL,
	Peatükke int NULL,
)

CREATE TABLE dbo.testxml   -- siia tabelisse Piibli peatükid
(
	kood int IDENTITY(1,1) PRIMARY KEY,
	filedata xml NULL,
)

Ning lõpuks protseduuri, mis kõik veebist loetud (see tuleb veel teha) failid loeb andmebaasi:

USE [Piibel]
go
CREATE proc [dbo].[LaePiibel]
as

declare @x nvarchar(max) ;

with numbrid as

	(select ROW_NUMBER() over (order by r1.kood) n from raamatud r1, raamatud r2)

select @x =
(
	select
	' insert testxml (filedata) SELECT convert(xml, bulkcolumn, 2) FROM OPENROWSET ( BULK ''c:\piibel\' + rtrim(r.Lühend) + '-' + rtrim(cast (n.n as char(3))) + '.xml'', SINGLE_BLOB) as T (bulkcolumn); '

	from raamatud r
	join numbrid n on n.n <= r.Peatükke
	order by r.kood, n
	for xml path('')
);

/* -- seda rida saab kasutada testimiseks (lisa -- rea algusse)
select
-- */ execute
(@x)

Ära küsi kuidas, aga see protseduur toimib. Enne tuleb jahh luua kaust (minul siis c:\piibel) ja lugeda sinna veebist XML failid

PowerShelliga Piiblit lugema

PowerShellis alustasin sellest, et kõigepealt tegin C-kettale kausta c:\Piibel

Seejärel koostasin funktsiooni get-piibel, millele antakse ette kaks parameetrit – raamatu lühend (veebiaadressis vajalik) ja peatükkide arv:

function get-piibel ($raamat, $peatükke )
{

1..$peatükke | ForEach-Object {
	$source = "http://piibel.net/.xml?q=" + $raamat + "%20" + $_.ToString() + "&bv=EST_97&bv=EST_68"
	$destination = "c:\piibel\" + $raamat + "-" + $_.ToString() + ".xml"

	Invoke-WebRequest $source -OutFile $destination

	}
}

Need viimased kaks parameetrit veebiaadressi lõppu tagavad, et alla laetakse kaks erinevat tõlkeversiooni – hiljem hea võrrelda.

Ja nüüd jälle minu laiskus – Exceli tabelis lisandub uus veerg PShell valemiga:

="get-piibel " & [@Lühend] & " " & [@Peatükke]

Veerg kätte (Ctrl-tühik, Ctrl-C) ja PowerShelli aknasse (Ctrl-V) ning käima – piibel hakkab laaduma mu arvutisse ja aeg on pisut hinge tõmmata – võtab pisut aega.

Kuna seesama tabel (raamatunimi, lühend jms) tuleks kanda ka SQL-i, siis samamoodi kui veerg PSchell tekitasin ma veeru sql valemiga:

 
="insert Raamatud (Nimetus, lühend, peatükke) values (N'"&ConcatRange(Table1_2[@[Nimetus]:[Peatükke]];"', N'";TRUE)&"')"

Selles valemis see ConcatRange on mu oma selleks otstarbeks tehtud funktsioon – nii saab hõlpsasti kokku panna suvalise pikkusega insert-lauseid, et siis seal va SQL Manageris kasutada – ikka jälle veerg kätte (Ctrl-tühik, Ctrl-C) ja siis Management Studios lahti (Ctrl-V) ja käima (F5)

ConcatRange funktsioon ise on selline – huviline saab kasutada:

Public Function ConcatRange(r As Range, Delimiter As String, SQL As Boolean) As String

Dim x As Range
Dim s As String
s = ""

For Each x In r.Cells
	s = s & IIf(SQL, Replace(x.Value, "'", "''"), x.Value) & Delimiter
Next

ConcatRange = Left(s, Len(s) - Len(Delimiter))

End Function

Nüüd peaks olema XML failid mul arvutis ja raamatute tabel andmebaasis – saab käivitada varem tehtud protseduuri LaePiibel ning naasta andmebaasi poolele.

Tagasi andmebaasis

Kuna raamatute tabel on nüüd laetud ja piiblifailid samuti – vaatame, mis nendega edasi teha. Tegu on XML kirjetega ja neist tuleks andmed välja lugeda – et see toimiks kiiremini, teeme mõned XML-indeksid. See kiirendab oluliselt XML-kirjete töötlemist ja hilisemad päringukomponendid (XQUERY) teisendatakse lihtsateks relatsioonilisteks komponentideks. XML indeksid ei ole muud kui lahti lammutatud XML hoituna relatsioonilisena.

CREATE PRIMARY XML INDEX xml0 ON dbo.testxml(filedata)
go
CREATE XML INDEX xml1 ON dbo.testxml(filedata) USING XML INDEX [xml0] FOR PATH
CREATE XML INDEX xml2 ON dbo.testxml(filedata) USING XML INDEX [xml0] FOR VALUE
CREATE XML INDEX xml3 ON dbo.testxml(filedata) USING XML INDEX [xml0] FOR PROPERTY

Järgmisena teeme mõned päringud ja salvestame nad view-dena

View ‘värsid’ lubab mul lugeda välja kõik raamatute värsid, neid filtreerida raamatute, tõlkeversiooni ja peatükkide kaupa:

CREATE view [dbo].[värsid]
with schemabinding
as

select

	xx.value('./@id', 'int') nr
	, xx.value('./@heading', 'nvarchar(100)') Pealkiri
	, xx.value('.', 'nvarchar(max)') Värss
	, xx.value('../@id', 'int') Peatükk
	, xx.value('../../@title', 'nvarchar(200)') Raamat
	, xx.value('../../@abbrev', 'nvarchar(200)') Lühend
	, xx.value('../../../@title', 'nvarchar(200)') Piibel
	, right(xx.value('../../../@title', 'nvarchar(200)'),4) PiibliAasta
from dbo.testxml Q
cross apply Q.filedata.nodes('//verse') as T2(xx)

Teine pisut keerukam, lubab mul kahte tõlkeversiooni omavahel kõrvutada:

create view [dbo].[võrdlus]
as

select

	v.Raamat, v.Peatükk, v.nr
	, max(case when piibliaasta = 1968 then v.Värss end) Piibel68
	, max(case when piibliaasta = 1997 then v.Värss end) Piibel97
from värsid v
group by
	v.Raamat, v.Peatükk, v.nr

Nüüd tahaks veel mõned päringud teha, mis aitaks otsida kindlaid nimesid või termineid erinevaist raamatuist. LIKE-operaator on siin pisut paha – esiteks liialt aeglane ja teiseks juba tüütu on erinevaid käändeid ja sõnavorme päringusse kirjutada. Heal meelel kasutaks täistekst indeksit, aga XML tabelile (XML indeksitele) on seda üsna tülikas seadistada (vist ei saagi päris korrektselt). Seepärast koostasin tabeli, kuhu salvestan VIEW ’Värsid’ tulemuse. Kuna täistekst index eeldab tabelil unikaalset indeksit, see peaks olema mõistlikus järjestuses, siis selle tabeli tekitamiseks panin kokku päringu:

select *, identity(integer, 1,1) reanr
into t_värsid
from värsid;
go
create unique clustered index x0 on t_värsid (reanr)

Nüüd saab tabelile lisada juba täistekst indeksid ja teha täistekst predikaatidega päringuid

CREATE FULLTEXT CATALOG [FTCatalog] WITH ACCENT_SENSITIVITY = ON AS DEFAULT;
go
CREATE FULLTEXT INDEX ON dbo.t_värsid(Värss) KEY INDEX x0;

Nüüd saab küsida kõikvõimalke toredaid küsimusi kus CONTAINS-predikaadid, nagu:

select
--top 100
* 
from t_värsid v 
join raamatud r on v.Lühend = r.Lühend

where 1 = 1 -- see tingimus on, et saaks teisi sõltumatult välja kommenteerida

and contains (värss, '"ing*"')
--and contains(värss, '"saatan*"')
--and contains(värss, '"kurat*" or "kurad*"')
--and contains(värss, '"deemon*"')
--and contains(värss, '"jumala lapsed" or "jumala pojad"')
--and contains(värss, 'Jophiel')

and PiibliAasta = 1997

order by r.kood, v.Peatükk, v.nr

Lõpetuseks

Kes nüüd luges ja kaasa mõtles ja proovis, sai ehk endalegi samasuguse andmebaasi, kes aga lihtsalt luges ja tahab proovida, saab omale andmebaasi alla laadida ja paigaldada (RESTORE) aadressilt:

http://bit.ly/PiibelDB

Sinna panen kas mõned SQL scriptid ja Exceli tabeli piibliraamatutega.

Posted in Excel, MS SQL Server, Piibel, PowerShell | Tagged , , , | Lisa kommentaar

Isikukood kuupäevaks – vahelduseks midagi lihtsat

Ikka ja jälle otsitakse “lihtsat” viisi, kuidas isikukoodist välja lugeda kuupäev. Panin siis kirja niihästi, kui mina oskaksin:

=TEXT(((CODE(MID("AABBCC";LEFT(G7;1);1))-47)&MID(G7;2;6));"0000\/00\/00")+0

G7 on seal valemis siis lahter, kus asub isikukood. Testitud ja toimib. Ahjah – vastuselahter (-lahtrid) tuleks panna kuupäeva vormingusse!

Henn

Posted in Excel, Isikukood | Tagged , , | Lisa kommentaar

SQL and GEDCOM – VOL 1

Introduction

Two of my hobbies – SQL and genealogy brought me to create SQL database with genealogical data, analyze it, make some statistics and solve graph exercises on it.

Main storage for my genealogical data (and not only for me) is Geni.com and from Geni I can export data to GEDCOM. Some of my friends asked me to make local copy of the info stored in Geni – so this export carries also practical values.

GEDCOM export is a bit limited – 50K person records on time (but in Geni there are close to 100M person records). So, if you want to download a big “family tree”, you need to make several exports. Fortunately same person in different exports is always identified with same id – this allows to merge those exports later.

Today I can play with quite big (in local mean) databases – approx 2M persons. This is a result of close to 200 exports. I can test both – performance things and graph algorithms.

During this I have solved several SQL-oriented problems and built one nice Excel-tool for searching different paths between persons. I can’t share data but I’m happy to share tools I’ve created. So, anyone interested in can try and play with their own exports.

Current article is 1st from a series – preparation. In next article I plan to describe how to extract info from prepared data. The third will demonstrate some path-finders and after that I plan to play a bit with Excel and genealogical data. The last one will try to generate a new GEDCOM with different scope from combined GEDCOM-s – targeted to for other software like FamilyTreeMaker or similar.

0. GEDCOM structure (short review)

GEDCOM is a typical and standardized way to store and exchange genealogical (and not only) information between different applications.

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

GEDCOM file is a text file containing rows with objects, attributes, sub-attributes and references between them. Objects (INDI – person, FAM – family jne), object attributes (NAME, BIRT), components of attributes (components of NAME: GIVN, SURN; components of BIRT: DATE and ADDR), components of components (components of ADDR: CITY, CTRY) etc. Each row begins with number indicating the level (0 – object, 1 – attribute, 2 – sub-attribute etc). That means – bigger level belongs to lower – hierarchical structure.

The sample GEDCOM fragment that was brought above tells that PERSON with ID=“I1448552” has NAME=“Henn Sarv” (first name GIVN “Henn”, family name SURN “Sarv”, nickname NICK “सींग”) and he was born on (BIRT) date (DATE “7 MAR 1955”), in place (ADDR CITY “Tallinn” CTRY “Estonia”).

Object might refer to other objects. Henn’s “object set” has reference to 3 families (FAM) – one where Henn belongs as a child (FAMC with ID=“F6000000001528333119”) and two where Henn belongs as a parent (FAMS).

Families are later in GEDCOM as objects (row-sets) like:

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@

This example describes a family with ID=“F6000000001528333119”. The parents were married (MARR) on date (DATE “28 AUG 1936”), the place of the marriage is missing (not known). The father of the family (HUSB) is the person with ID=“I6000000001528333113”, mother (WIFE) is the person with ID=“I6000000001528640081” and there are 7 kids in the family (CHIL).

1. First phase – reading GEDCOM rows into database

  • problems – UTF-8 / Unicode
  • line numbering

BULK INSERT command is suitable to read a GEDCOM file into database, but there we have 3 problems:

1st – filename parameter can’t be a variable nor an expression – so I have to use built SQL (exec (@x));

2nd – SQL server doesn’t accept (interpret correctly) UTF-8, the format I get from Geni export, so I have to convert from UTF-8 to UNICODE. For that I can use POWERSHELL:

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

3rd is numbering of the lines. In relational databases all sets (tables) are un-ordered, even when table have clustered index because reading the table might happen in parallel. So, during the load I have to assign unique and rising number to each row – order of rows is extremely important in GEDCOM. BULK INSERT allows to load only info I get from file, so INSERT FROM OPENROWSET doesn’t work here.

I solved this in the following way:

  • created table with IDENTITY column for storing table rows
  • created view containg only line-column from that table
  • BULK INSERT target will be this view

Result is that rows from the file will be stored in the table according to their ordinal numbers and they can be uniquely referenced using this number.

For logging and history I created one more table to store loading history.

1.1 Creating necessary objects

  • Table 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,  -- jrknr means [loading] order number
first_nr bigint NOT NULL,
ridu bigint NOT NULL,  -- ridu means count of lines
filename nvarchar(258) NULL,
load_date datetime NULL
)
  • Procedure 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 'allready loaded: ' + @file

1.2 Execution:

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

Once per each file exported from Geni. Sometimes I create those exec-s manually, sometimes using POWERSHELL.

1.3 Correction:

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

2 Initial extraction

Next I need to make some row-level modifications. In case of 1-2 GEDCOM files this isn’t a challenge but when I load together several tens or hundreds of files I need to implement batch-wise processing to keep transaction log and transaction duration under control.

Exported (and now loaded) GEDCOM files contain a lot of meaningless (for my goal) rows (approx 50%), it’s reasonable to filter out the those rows without deleting them, because later I might still need to use them to create a summary GEDCOM file from the merged ones.

For this reason I’ve created a new table containing only required rows and with new columns with parsed dates and id-s.

At the beginning I created a view to show me only the rows I need and then I created a table to store those rows. With small number of rows this technique worked but when I needed to implement batch-wise process (because of the large number of rows) I needed to convert this view to procedure.

2.1 Create objects:

  • 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) [start] -- 1st occurence of @
	from dbo.gedcom_data
),
t2 as
(
	select *
	, case when [start] > 0 then CHARINDEX('@', line, [start]+1) end [stop]
	from t1
	where lvl is not null
),
t3 as
(
	select *
	, case when [start] > 0 and [stop] > 0 then SUBSTRING(line, [start], [stop]-[start]+1) end kood
	, case when [start] = 3 then SUBSTRING(line, [stop] + 2, 4) else SUBSTRING(line, 3, 4) end class
	, case when [start] <> 3 then ltrim(substring(line, 7, 100)) end data
	from t2
)
select nr, lvl,
case when class in ('indi', 'fam') then kood end kood,   -- important to read codes only from right rows
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 -- unneccessary 'classes'
(
'head','titl', 'sour', 'vers', 'subm',
'gedc', 'vers', 'form', 'char', 'nick',
'cont', 'note', 'addr', 'rfn', 'time',
'obje', 'occu', 'phon', 'plac', 'post',
'chan', 'form', 'file', 'city'
)

This view was created step-by-step using CTE (common table expression). At first I needed to find code in rows between „@“ and „@“.

However, MS SQL implementation (T-SQL) doesn’t allow to reuse projected aliases in projection, so I needed to create 3 expressions: t1 find first occurence of „@“ (if exists), t2 find second occurence of „@“(if exists) and t3 extract the required code with SUBSTR.

Level numbering at the beginning of the row goes into [lvl], class name (type of row) into [class]. NB! Expression t3 from t2 needs to differentiate – in rows of level 0, the class comes from the end of the row, but in the other rows it comes from the beginning.

And finally 2 simple conversions:

  1. with class NAME, filling column [name] (with rempved ‘/’)
  2. with class DATE, trying fill [datevalue] and filling [datetext]
  3. and last – excluding rowsd (classes) unused in system
  • 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)

This table is to store (materialize) previous view results. Indexes are added during the work by execution plan recommendations (missing index detail).

  • Protseduur parsed_load

Initially I used INSERT-SELECT to store data but batch-wise processing requires some modifications. I can find row for starting next batch but where on view level wasn’t visible for query optimizer on t1-expression level so where nr > @start was applyed on last expression level. To increase performance I created procedure doing exactly same as INSERT and SELECT.

Initial version of the batch-processing:

declare @start bigint =
(select top 1 nr from parsed_data order by nr desc)
-- most fast way to get 1st number to process

declare @batch int = 1000000

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

The procedure converted from the initial 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
	-- main difference - where is transfered to expression t1
),
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 Execution batch-wise

The last procedure could be executed in the following loop:

select 1 -- to guarantee that @@rowcount > 0
while @@ROWCOUNT > 0
begin
	checkpoint -- to minimize transaction log
	exec Parsed_Load 1000000 --  batchsize
end

Other batch-wise loops I execute in the same way.

The origin rownumbers (column [nr]) are good for referencing last executed row, start a new batch and later reference to “source”.

2.3 Once loaded then archive

  • Gedcom_Data_arhiiv
  • batch-wise delete
  • Truncate table delete

Once the rows are loaded, it is good to archive them. By intent I didn’t use partitioned table – probably better way than I used.

The most simple way to archive loaded rows (at beginning I used this way):

set xact_abort on
begin tran

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

Again, when I load exported GEDCOM files one at a time, this solution works. I tried to reload all my 200 GEDCOM files and the solution failed – transaction log got full.

I made a loop:

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

After that I need to delete the archived rows. Deleting rows (by one or batch-wise) isn’t the shortest way, but it’s dangerous to truncate, because I need to keep identity value and truncate destroys this.

The solution is to simply save the last identity and restore after truncate:

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. Transfer data between rows

  • Date transfer to event rows
  • Code transfer between master (object) and detail (rest) rows

The following step makes the rows “independent”. Current rows depend from the info in other rows. Some (simplest and most important) techniques could be combined to simplify following queries.

  1. 1st is transferring date info. Each DATE-class row (level 3) is immediately followed by event rows (BIRT, DEATH, MARR etc – level 2). It is reasonable to transfer DATE info into matching event row.
  2. 2nd is transferring codes. Person and family rows (level 0) contain the codes of person and family. It would be best for the following queries to have person and family codes in each row they belong to. All rows (level >0) belong to the last (level 0) object row.

The slowest way is to use cursors. It’s tested. One GEDCOM file under cursor might run 15min or longer. With 200 GEDCOM files we get an unacceptable time slice.

3.1 Procedure Date_update 2 versions

The initial procedure simply self-joined the table using neighbor rows. Still, rising number of rows decreases performance too much.

CREATE PROCEDURE [dbo].[Date_Update]
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'

I guessed that I can prevent sort because table (and self) are joined by same [nr], but during testing I didn’t get the best execution plan.

I had to create another version – the new one works so fine that there is no need for batch-wise:

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')

3.2 Procedure Kood-Flow – flowing codes from master rows to others

CREATE PROCEDURE [dbo].[Kood_Flow]  
@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

This procedure virtually makes 2 tables – one contains of the master rows (level 0) containing codes to transfer and the other contains of the detail rows (level >0) belonging to master rows and waiting for the codes.

Once master table is formed (expression ‘koodid’) using lead we can find row of the next master row and all details with [nr] between those 2 masters get code from the previous master.

As we update only code-less rows the procedure can be executed batch-wise.

select 1
while @@ROWCOUNT > 0
exec Kood_Flow 1000000

Next time …

… I will explain how to easily extract person, family and relations.

Posted in Geni, MS SQL Server, Sugupuud | Tagged , | Lisa kommentaar

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.

Posted in MS SQL Server, Sugupuud | Tagged , | Lisa kommentaar

Üks lahtipivootimise näide

Üks lahtipivootimise näide

Mõni päev tagasi tuli mul ette ülesanne – üks suht keeruline risttabel “lahti pivootida” ehk tagasi lametabeliks muuta

Väljakutseks muutis selle vajadus teha kõik valemitega ning vältida igasuguseid makrosid ja mis kõige olulisem

Ma tegin seda!

Ma tegin ka ühe lihtsustatud näite aj jagan seda heal meelel

http://1drv.ms/1JFMtGA (siin on toodud näide Exceli kujul)

Väike samm-sammuline selgitus

Minu näites on algne tabel klassikaline “risttabel”, kus erinevates veergudes atribuudid ja ridades kirjed ning atribuutide väärtused.

Lahtipivootimise tulemuseks peaks olema umbes selline tabel:

Atribuudid eraldi ridadel ja ainult need, mis tabelis ka olemas

Hakatuseks teen vahetabeli. Võib ka lihtsalt laiendada lähtetabelit, kuid ohutuse mõttes tegin vahetabeli. Tegelik algne risttabel ei olnud tabel ja koosnes veel eri laiusega sektsioonidest.

  • Esimene veerg:     Nr =ROW()-ROW(Tabel2)+1

See trikk võimaldab genereerida tabelisse numbreid, kui tabelit allapoole laiendada. Ja see ei sõltu tabeli asukohast

  • Teine veerg:     Nimi =VLOOKUP([@Nr];Tabel1;MATCH(Tabel2[#Päised];Tabel1[#Päised];0);FALSE)

See on üks teine (vana) trikk, mis võimaldab VLOOKUPiga logeda teisest tabelist andmeid veeru nime järgi

  • Kolmas veerg annab atribuutide arvu (> 0)

=COUNTIF(OFFSET(Tabel1[Attr1];[@Nr]-1;0;1;COUNTIF(Tabel1[#Päised];”Attr*”));”>0″)

Antud näite tegin, et ta ei sõltuks atribuudiveergude arvust – reaalses elus ei pruugi atribuudid olla sarnaste niumedega, sel juhul võiks valem olla umbes selline

=COUNTIF(OFFSET(Tabel1[Esimene atribuut];[@Nr]-1;0;1; — siia atribuutide arv tabelis –);”>0″)

Minu algses tabelis oli 17 atribuudiveergu

  • Neljandaks arvutan reanumbri, millest alates tulemuses on SELLE kirje atribuudid

=SUM(Tabel2[[#Päised];[NumOfAttr]]:[@NumOfAttr])-[@NumOfAttr]+1

See on klassikaline “kasvav summa” näide tabeliformaadis

  • Viies veerg on bitmask, mis näitab, millised atribuudid antud kirjes on aj millised mitte

Mask =SUMPRODUCT(

(OFFSET(Tabel1[Attr1];[@Nr]-1;0;1;COUNTIF(Tabel1[#Päised];”Attr*”))>0)

*

(2^(8-COLUMN(OFFSET($A$1;0;0;1;COUNTIF(Tabel1[#Päised];”Attr*”)))))

)

See SUMPRODUCT arvutab bitmaski kasutades kahte vektorit:

Üks vektor on {true,false,…} jada, mis võrdleb atribuudi olemasolu (antud juhul >0)

Teine vektor on 2-astemete (pööratd) jada kujul {256, 128, 64, …}

Nende kahe vektori korrutis kokkuliidetuna annabki bitmaski

  • Kuues veerg on seesama aga kahendkujul (0-1 jada)

Bitmask =DEC2BIN([@Mask];8)&”0″

Juhul kui atribuute on üle 9, tuleb see arvutus tükeldada, kuna dec2bin ei võimalda üle 9 biti

Sellega on vahetabel otsas

Nüüd teeme tulemustabeli

  • Esimene:     NR =ROW()-ROW(Tabel3)+1 — sama trikk, mis eelmises

Siin on maitse küsimus, kas teha algusest peale maksimaalsuurusega tabel ja peita ülearused read IF-funktsiooniga või siis teha tabel ja “venitada” see vajaliku pikkuseni

  • Teine:         LineNr =MATCH([@Nr];Tabel2[ResLine];1)

Annab mulle rea numbri lähte- (ja vahe)tabelis

  • Kolmas:     ResNR =COUNTIF(Tabel3[[#Päised];[LineNr]]:[@LineNr];[@LineNr])

Annab mulle atribuudi järjekorranumbri

Kõik eelnev tagab, et mul on vaid need read, mis lähtetabelis ja vaid need atribuudid, mis lähtetabelis olemas, atribuudiridade arv vastab atribuutude arvule ja atribuutide numbrid algavad 1-st

  • Neljas:         AtrCol =IF([@ResNr]=1;

FIND(“1”;INDEX(Tabel2[BitMask];[@LineNr]));

FIND(“1”;INDEX(Tabel2[BitMask];[@LineNr]);OFFSET([@AttrCol];-1;0)+1)

)

See leiab iga järgmise atribuudi positsiooni tabelis. Tegelikult “1” positsiooni(d) bitmaskis

Kui atribuudi number on 1, siis leiame lihtsa FIND-funktsiooniga. Järgmiste atribuutidega kasutame FIND funktsiooni kolmandat atribuuti (millisest kohast alates), mille me loeme eelmisest reast.

Edasine on lihtne:

Nimi =VLOOKUP([@LineNr];Tabel1;MATCH(Tabel3[#Päised];Tabel1[#Päised];0);FALSE) — taas see vana trikk

AttrName =OFFSET(Tabel1[[#Päised];[Attr1]];0;[@AttrCol]-1) — siin loeme atribuudi nime päisereast

AttrValue =OFFSET(Tabel1[[#Päised];[Attr1]];[@LineNr];[@AttrCol]-1) — siin samamoodi andmereast

Igasugune tagasiside on teretulnud

Henn

Posted in Excel | Tagged | Lisa kommentaar

Simple unpivot example in excel

One unpivoting example

(kohe teen sama loo ka eesti keeles)

Few days ago I had challenge to unpivot one quite complex crosstable.

And challenge was to use formulas to repeat in future with new data and prevent any macros

 

I did it!

 

I made simplification as example I’d like to share

 

http://1drv.ms/1JFMtGA (there is sample workbook)

 

Some step-by-step explanation

(sorry – I use ee-location, so in formulas I use ; rather than , and [#header..] is replace to [#päised..])

 

On my sample I have table with names and 8 columns with attributes

 

Name, Attr1, Attr2, Attr3, …

Henn, , 3, 5

Ants, 1, , 4

Peeter, , , 7

 

Unpivoting result have to be something like

Name, AttrName, AttrValue

Henn, Attr2, 3

Henn, Attr3, 5

Ants, Attr1, 1

Ants, Attr3, 4

Peeter, Attr3, 7

 

1st I create intermediate table. Actually I can extend origin table, but for safety I created new one

btw – in origin – the starting cross wasn’t table and contains several sections

  • 1st column:     Nr =ROW()-ROW(Tabel2)+1

this is trick to generate numbers to table – insensitive of table placement

  • 2nd column:     Nimi =VLOOKUP([@Nr];Tabel1;MATCH(Tabel2[#Päised];Tabel1[#Päised];0);FALSE)

this is another trick to get with VLOOKUP matching attribute from lookup table

  • 3rd in number of attributes > 0

=COUNTIF(OFFSET(Tabel1[Attr1];[@Nr]-1;0;1;COUNTIF(Tabel1[#Päised];”Attr*”));”>0″)

this is actually working with different table with different number of attributes in my actual example I had 17 attributes and with different names.

There I simply count how many attributes current line have

  • 4th is calculate starting rownumber in resulting table

=SUM(Tabel2[[#Päised];[NumOfAttr]]:[@NumOfAttr])-[@NumOfAttr]+1

this is “classical” running total formula working in table

  • 5th is trick – bitmask showing what attributes exist on particular row

Mask =SUMPRODUCT(

        (OFFSET(Tabel1[Attr1];[@Nr]-1;0;1;COUNTIF(Tabel1[#Päised];”Attr*”))>0)

        *    

        (2^(8-COLUMN(OFFSET($A$1;0;0;1;COUNTIF(Tabel1[#Päised];”Attr*”)))))

)

This SUMPRODUCT calculates bitmask on the way where each bit shows presence of attribute.

One vector is {true,false,…} vector comparing attribs

The second vector is list of 2-powers (in descending order) {256, 128, 64, …}

  • 6th step is to create same bitmask in binary format

Bitmask =DEC2BIN([@Mask];8)&”0″

on case more than 9 attributes – dec2bin have to be broken for 2 parts becouse limitation (? why) of dec2bin

This finalizes intermediate table

 

NExt I create resulting table

 

  • 1st     NR =ROW()-ROW(Tabel3)+1 — this is same as in previous table

it’s question of taste – on some cases I create max size table and unhide unneccessary rows with some IF-s. In that case I simply extend the table with autogenerated rows

  • 2nd     LineNr =MATCH([@Nr];Tabel2[ResLine];1)

this gives me line number in origin table:)

  • 3rd     ResNR =COUNTIF(Tabel3[[#Päised];[LineNr]]:[@LineNr];[@LineNr])

gives me ordinar number of attribute

 

all the above garantees me that line number will never bigger than actual number of original lines and attribute number is never bigger than

number of attributes and start with 1.

 

  • 4th     AtrCol =IF([@ResNr]=1;

            FIND(“1”;INDEX(Tabel2[BitMask];[@LineNr]));

            FIND(“1”;INDEX(Tabel2[BitMask];[@LineNr]);OFFSET([@AttrCol];-1;0)+1)

            )

this finds the position of “1” in my bitmap mask – and actually position of attribute column in table

when attribute number = 1, I simply find “1” in bitmask

for next attributes I give additional “starting point” for find reading this from line above

 

Folllowing is simple

Nimi =VLOOKUP([@LineNr];Tabel1;MATCH(Tabel3[#Päised];Tabel1[#Päised];0);FALSE) — this is “old” trick

AttrName =OFFSET(Tabel1[[#Päised];[Attr1]];0;[@AttrCol]-1) — this read attribute name from Header

AttrValue =OFFSET(Tabel1[[#Päised];[Attr1]];[@LineNr];[@AttrCol]-1) — this read attribute value from correct line and column

 

Any comment are welcome

Henn

Posted in Excel | Lisa kommentaar

Vahel võib mõni asi lihtne olla

Äsja seisin silmitsi ühe sageli ettetuleva probleemiga.

Tabelis (Excelis) on koodid ja tooted ja miskid müügiandmed. Toodetel on kood ja nimetus, vaja oleks pivooti kokku võtta. Ainult et – sama koodiga toodet on vahel minetatud ühtemoodi (“Valged Hiired”) vahel teist moodi (“Hiired (valged)”). Kui pivooti panna ridadesse koodid, on kõik valged hiired kokku arvatud, aga kole, kui panna nimetus, on ilus, aga hiired eraldi ridades.

Seekord hästi lihtne valem, mis selle probleemi kõrvaldab. Selleks teed ühe lisaveeru oma tabelisse (näiteks [Ühine Nimetus]) ja kirjutad sinna sellise valemi (eeldame, et tootekood on veerus [Kood] ja nimetus veerus [Nimetus]).

=INDEX([Nimetus];MATCH([@Kood];[Kood];0))

Lihtne ja toimib

Henn

Posted in Excel | Lisa kommentaar