Inkrementalno nalaganje in QVD v Qliku

Ko aplikacija raste, raste tudi čas nalaganja. Tabela z desetimi milijoni vrstic se iz baze bere dolgo, obremenjuje produkcijski sistem in onemogoča pogosto osveževanje. Inkrementalno nalaganje to reši: zgodovino hranite v QVD datoteki, iz baze pa ob vsakem nalaganju preberete le nove in spremenjene vrstice.

Kaj potrebujete

• Enolični ključ vrstice, npr. NarociloID.
• Časovni žig spremembe v izvorni tabeli, npr. Spremenjeno, ki ga baza posodobi ob vsakem vnosu in spremembi. Brez njega inkrementalno nalaganje ni zanesljivo — preverite z ekipo, ki upravlja bazo.
• Prostor za QVD — podatkovna povezava, kamor aplikacija lahko piše.

Trije vzorci

Skripta: dodajanje, spremembe in brisanje

Najpogostejši primer — tabela naročil. Skripta deluje tudi ob prvem zagonu, ko QVD še ne obstaja (takrat naloži vse).

SET vQvd = 'lib://Podatki/QVD/Narocila.qvd';

// 1. Od kdaj beremo? Zadnji časovni žig v obstoječem QVD.
IF Not IsNull(FileSize('$(vQvd)')) THEN
    Zadnje:
    LOAD Max(Spremenjeno) as MaxSpremenjeno
    FROM [$(vQvd)] (qvd);
    LET vOd = Timestamp(Peek('MaxSpremenjeno', 0, 'Zadnje'), 'YYYY-MM-DD hh:mm:ss');
    DROP TABLE Zadnje;
ELSE
    LET vOd = '1900-01-01 00:00:00';
END IF

// 2. Nove in spremenjene vrstice iz baze
LIB CONNECT TO 'ERP';

Narocila:
LOAD NarociloID, KupecID, Datum, Znesek, Spremenjeno;
SQL SELECT NarociloID, KupecID, Datum, Znesek, Spremenjeno
FROM dbo.Narocila
WHERE Spremenjeno >= '$(vOd)';

// 3. Ostale vrstice iz QVD — razen tistih, ki smo jih pravkar prebrali
IF Not IsNull(FileSize('$(vQvd)')) THEN
    CONCATENATE (Narocila)
    LOAD NarociloID, KupecID, Datum, Znesek, Spremenjeno
    FROM [$(vQvd)] (qvd)
    WHERE Not Exists(NarociloID);
END IF

// 4. Izbrisane vrstice: obdržimo le ključe, ki v bazi še obstajajo
INNER JOIN (Narocila)
LOAD NarociloID;
SQL SELECT NarociloID FROM dbo.Narocila;

// 5. Nov QVD za naslednje nalaganje
STORE Narocila INTO [$(vQvd)] (qvd);

Kako deluje

1. Meja: največji Spremenjeno v QVD je trenutek, do katerega imamo podatke.
2. Spremembe: iz baze preberemo vse, kar je novejše ali enako tej meji. Uporabljamo >=, ne >, da ne izpustimo vrstic z enakim časom.
3. Zgodovina: iz QVD dodamo vse vrstice, katerih ključa še nimamo. Spremenjena naročila so tako v tabeli le enkrat — v novi različici iz baze.
4. Brisanje: INNER JOIN s seznamom vseh ključev iz baze odstrani naročila, ki jih v bazi ni več. Branje enega samega stolpca s ključi je hitro tudi pri velikih tabelah. Če brisanj ni, ta korak izpustite.
5. Shranjevanje: posodobljeni QVD je izhodišče za naslednje nalaganje.

Samo dodajanje

Pri tabelah, kjer se vrstice ne spreminjajo, je postopek še preprostejši — ne potrebujete Exists() in koraka za brisanje:

Zadnji:
LOAD Max(PremikID) as MaxID FROM [lib://Podatki/QVD/Premiki.qvd] (qvd);
LET vMaxID = Peek('MaxID', 0, 'Zadnji');
DROP TABLE Zadnji;

Premiki:
SQL SELECT * FROM dbo.Premiki WHERE PremikID > $(vMaxID);

CONCATENATE (Premiki)
LOAD * FROM [lib://Podatki/QVD/Premiki.qvd] (qvd);

STORE Premiki INTO [lib://Podatki/QVD/Premiki.qvd] (qvd);

Optimizirano branje QVD

QVD se najhitreje naloži v optimiziranem načinu, ko Qlik podatke prenese v pomnilnik skoraj brez obdelave. To velja, kadar pri nalaganju:

• polj ne preoblikujete (preimenovanje z as je dovoljeno),
• ne dodajate izračunanih polj,
• kot pogoj uporabite kvečjemu Exists() z enim samim poljem.

Ali je bilo branje optimizirano, preverite v dnevniku nalaganja — pri optimiziranem branju je ob imenu datoteke zapisano qvd optimized. Izračune, ki bi branje upočasnili, raje opravite pred shranjevanjem v QVD.

Delitev QVD po letih

Pri zelo velikih tabelah zgodovino razdelite na več datotek, npr. eno na leto. Spreminjajo se praktično le podatki tekočega leta, zato se starejše datoteke ne prepisujejo:

LET vLeto = Year(Today());
STORE Narocila INTO [lib://Podatki/QVD/Narocila_$(vLeto).qvd] (qvd);

// V končni aplikaciji naložite vse naenkrat z nadomestnim znakom
Narocila:
LOAD * FROM [lib://Podatki/QVD/Narocila_*.qvd] (qvd);

Pogoste napake

• Časovni žig, ki se ne posodablja vedno. Če kakšen postopek v bazi spremeni vrstico brez posodobitve Spremenjeno, je QVD tiho napačen. Enkrat na teden ali mesec zato izvedite polno nalaganje.
• Oblika datuma v SQL. Oblika v WHERE Spremenjeno >= '$(vOd)' mora ustrezati bazi. Za SQL Server deluje YYYY-MM-DD hh:mm:ss, za druge baze preverite.
• Časovni pasovi. Če strežnik Qlik in baza nista v istem časovnem pasu, se lahko spremembe »izgubijo«. Mejo raje zamaknite nekaj minut nazaj — Exists() bo podvojene vrstice tako ali tako izločil.
• Napaka pri nalaganju in poškodovan QVD. Če nalaganje odpove pred STORE, QVD ostane nespremenjen — kar je dobro. Ne brišite QVD na začetku skripte.