SAS Slovakia Newsletter / Tipy a triky

Použitie HASH objektov pre rýchle vyhľadávanie údajov vo viacerých tabuľkách

S rastúcim objemom dát a počtom objektov v dátovom sklade (DWH) je často krát nevyhnutná potreba kombinovať tieto dáta pre vytvorenie požadovaného výstupu.

V súčasnosti sú už k dispozícii viaceré techniky pre vyhľadávanie a spájanie údajov z rôznych tabuliek:

  • HASH objekty
  • SQL join
  • DATA STEP Merge
  • Formáty
  • Polia (Arrays)
  • Využitie indexov

V nasledujúcich riadkoch si bližšie popíšeme najefektívnejšiu z nich (pri splnení určitých predpokladov) – HASH objekty.

Princíp použitia HASH objektov spočíva v tom, že tabuľka, v ktorej sa nachádzajú požadované údaje, sa celá načíta do operačnej pamäte. Následne sa celý proces vyhľadávania dát vykonáva priamo v operačnej pamäti, ktorá je niekoľkonásobne rýchlejšia ako bežný pevný disk, ktorý sa využíva pri iných spôsoboch vyhľadávania údajov. Okrem toho nie je potrebné žiadne zoraďovanie dát pred, resp. počas procesu, ako je to napr. pri použití DATA STEP Merge alebo počas SQL joinu.

Výhody použitia HASH objektov:
  • premenné použité ako primárny kľúč pre spájanie tabuliek môžu byť aj numerické aj textové,
  • môžu byť použité aj primárne kľúče zložené z viacerých premenných,
  • HASH objekt sa jednoducho načíta zo vstupného SAS datasetu,
  • dostatočná úroveň kontroly a flexibility programu,
  • možnosť použitia zreťazeného vyhľadávania.
Nevýhody použitia HASH objektov:
  • možnosť použitia len v SAS DATA stepe,
  • vyžaduje sa unikátny primárny kľúč – toto platí len pre verzie SAS 9.1 a nižšie. Od verzie SAS 9.2 sú duplicity v primárnych kľúčoch povolené,
  • pre efektívnosť načítania dát do HASH objektu je potrebný dostatočný objem operačnej pamäte.

Príklad: Vo vstupnej tabuľke DETAIL sa nachádza atribút VAR, ku ktorému chceme dohľadať dodatočné informácie z tabuľky VAR_DESC.

Vytvorenie tabuľky DETAIL (tabuľka obsahuje 4000 záznamov):

Vytvorenie tabuľky VAR_DESC (tabuľka obsahuje 4 záznamy):

Nasledujúci kód vytvorí tabuľku LOOKUP, ktorá bude obsahovať údaje z tabuľky DETAIL a dohľadané údaje pre atribút VAR z tabuľky VAR_DESC:

Náhľad výstupnej tabuľky:

Do výstupnej tabuľky sa dostali všetky požadované údaje, ktoré sme nadefinovali v HASH objekte v „DefineData“ na základe primárneho kľúča nadefinovaného v „DefineKey“.
Atribút RC, ktorý sme si vytvorili, obsahuje informáciu o úspešnosti dohľadania údajov pre daný primárny kľúč. Obsahuje hodnoty 0 (úspešné) a 160038 (neúspešné).

V prípade, že by názov atribútov pre primárny kľúč nebol rovnaký, musíme použiť upravený kód pre HASH objekty (atribút VAR z tabuľky DETAIL má názov ATTR v tabuľke VAR_DESC, žltou farbou sú označené zmeny v kóde):

Z výstupnej tabuľky je ďalej možné vidieť, že pre primárny kľúč VAR = 20 sú v tabuľke VAR_DESC dostupné 2 údaje, ale do tabuľky LOOKUP sa dostala informácia len o prvom z týchto dvoch údajov. Ak by sme chceli dohľadať všetky (aj duplicitné) údaje použijeme funkcionalitu „MULTIDATA", ktorá je však dostupná len od SAS 9.2:

Náhľad výstupnej tabuľky:

Ako vidíme, do výstupnej tabuľky sa dostali všetky dostupné údaje pre primárny kľúč VAR = 20 a tiež bol vytvorený nový atribút R, ktorý obsahuje príznak, či ešte existuje duplicitná hodnota pre daný primárny kľúč (0-neexistuje, 1-existuje).

Rýchly Test:

Vstupné tabuľky:

DETAIL 4 mil. záznamov 107 MB binary compressed
VAR_DESC 4 záznamy 64 KB binary compressed

Výstupná tabuľka:

LOOKUP 5 mil. záznamov 228 MB binary compressed

Výsledky:

* použitie operačnej pamäte pri SQL Joine závisí od nastavenia parametra SORTSIZE, ktorý definuje objem operačnej pamäte použiteľný pri zoraďovaní dát (pri SQL sa počas procesu zoraďujú dáta pre uskutočnenie joinu)

Zdroje:
O všetkých možnostiach použitia HASH objektov sa dočítate viac na stránke http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a002576871.htm.

Richard Balaj, SAS Consultant