SAS SLovakia newsletter / Tipy a triky

Ako efektívne používať SAS/ACCESS® to ORACLE

V prípade, že Vaše dátové zdroje sú uložené v ORACLE dátových štruktúrach, SAS ponúka možnosť pristupovať k týmto dátam priamo zo SAS aplikácií prostredníctvom SAS/ACCESS® to ORACLE. Aby ste tak mohli urobiť, musíte spĺňať tieto základné požiadavky:

  •  vlastniť licenciu pre Base SAS a licenciu SAS/ACCESS® to ORACLE 
  • mať nainštalovaný ORACLE client software na počítači, z ktorého chcete pristupovať na ORACLE   

V prípade, že spĺňate základné požiadavky, môžete pristupovať k dátam v ORACLE priamo zo SAS aplikácií ako napr. SAS Base, SAS Enterprise Guide, SAS Data Integration Studio atď.

Máte na výber z 2 typov prístupov použitím:

1.     SAS/ACCESS LIBNAME Engines
2.     SQL Pass-Through Facility

1.    SAS/ACCESS LIBNAME Engines

SAS/ACCESS LIBNAME príkaz rozširuje funkcionalitu bežného LIBNAME príkazu o možnosť vytvoreniaSAS knižnice, pripojením sa na relačné databázy napr. ORACLE. Tým pádom môžete používať tieto objekty priamo v DATA stepe alebo v SAS procedúrach tak, ako keby to boli klasické SAS dátové tabuľky.

Použitie údajov databázy SAS procesom cez SAS/Access libname engine typicky znamená, že SAS/Access engine rozdelí spustený SAS program na časť, ktorú vykoná databáza a časť, ktorú vykoná SAS. Toto „preloženie“ pôvodného programu znamená rozpoznanie a rozdelenie jeho logiky, funkčnosti, na:

  • časť ktorá je vykonateľná a následne aj vykonaná databázou  
  • časť, ktorú vykoná SAS proces.

Efektivita programu je zvyčajne tým vyššia, čím väčšia časť funkčnosti sa vykoná v databáze, t.j. v pôvodnom dátovom prostredí, kedy dochádza len k najnevyhnutnejšiemu dátovému prenosu medzi databázou a SAS procesom.

Rozpoznanie a „preloženie“ časti programu na vykonanie v databáze je funkcionalita označovaná ako implicit pass-though.

Syntax pre zadefinovanie SAS knižnice pre ORACLE dáta je:

LIBNAME libref ORACLE ;

SAS/ACCESS-engine-connection-options pre Oracle obsahujú:

  • USER=Oracle-user ID - užívateľské meno pre ORACLE
  • PW=Oracle-password - užívateľské heslo pre ORACLE
  • PATH=Oracle-database-specification - názov ORACLE databázy tzv. SID, názov je case sensitive
  • SCHEMA=schema-name - názov ORACLE schémy do ktorej má užívateľ prístup


Príklad:   

libname oralib oracle user=user1 pw=pasw1 path=DB1 schema=test;   

pozn.: jedna z možností, ako nadefinovať detailnú špecifikáciu PATH resp. SID, je pomocou súboru TNSNAMES.ORA v inštalačnom adresári príslušného ORACLE klienta:

\NETWORK\ADMIN\

Pre informácie o ďalších variantoch pre nastavenie špecifikácie PATH sa obráťte na dokumentáciu ORACLE.

Príklad detailnej špecifikácie v TNSNAMES.ORA:

DB1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.12.134)(PORT = 1234))
    )
    (CONNECT_DATA =
      (SID = DB1)
    )
  )

Užitočné SAS/Access libname options:

  • READBUFF = 250 – nastavuje počet riadkov v bufferi, ktoré sa pri čítaní z databázy do SAS prenášajú v jednej dávke. Štandardne nastavené na 250, potrebné otestovať či zvýšenie nezlepší výkon – typicky sa výkon rastie až po hodnoty na úrovni 5000, či 10000. Zväčšením buffer-a sa znižuje sieťová aktivita na úkor zvýšenia spotreby pamäte v databáze.
  • INSERTBUFF = 10 a UPDATEBUFF=1 – obdobné nastavenia ako READBUFF, funguje ale pri prenášaní údajov zo SAS do databázy. Tzv. „commit“ príkazov vykonávaných v databáze nastane po nastavenom počte vkladaných/aktualizovaných údajov. Zvýšením nastavení sa typicky dosiahne značné zvýšenie rýchlosti operácie, typicky používané nastavenie je rádovo v tisícoch záznamov.

Pre sledovania, aká časť pôvodného programu je prenášaná na vykonanie do databázy existuje option SASTRACE. Nižšie uvedené nastavenie bude do SAS logu uvádzať informáciu o tzv. vláknach, v ktorých sa program vykonáva (t ako threads), o čase trvania (d ako duration) a príkazy vykonané databázou (s ako statement).

option sastrace=',,t,ds' sastraceloc=saslog nostsuffix;

data test;
   set ib.table1 (keep=dt_open account_id);
   where dt_open between  "1jan2008"d and "31dec2008"d;
run;  

Z uvedeného príkladu je do databázy poslaný príkaz:

ORACLE_94: Executed:

SELECT "DT_OPEN", "ACCOUNT_ID" FROM TEST.TABLE1
WHERE "DT_OPEN" BETWEEN TO_TIMESTAMP('01JAN2008:00:00:00','DDMONYYYY:HH24:MI:SS.FF','NLS_DATE_LANGUAGE=American') AND TO_TIMESTAMP('31DEC2008:00:00:00','DDMONYYYY:HH24:MI:SS.FF','NLS_DATE_LANGUAGE=American')

2. SQL Explicit Pass-Through Facility 

SQL Pass-Through Facility komunikuje s ORACLE prostredníctvom SAS/ACCESS engine a umožňuje:

  • spustenie SQL príkazov v natívnej ORACLE syntaxi
  • zobrazenie výstupov naformátovaných pomocou SQL procedúry
  • uložiť výstupy ako SAS dátové tabuľky
  • vytvoriť SAS SQL view, ktoré obsahujú Pass-Through príkazy
  • výber riadkov a stĺpcov podľa podmienok, zoradenie dát, spájanie tabuliek aj v dvoch rozdielnych schémach

Syntax pre použitie SQL Pass-Through je takáto:

PROC SQL;
          CONNECT TO Oracle
                     (USER=Oracle-user-id PW=Oracle-password PATH=Oracle-server);
          SELECT SQL select-expression
          FROM CONNECTION TO Oracle
          (Oracle-query);
          DISCONECT FROM Oracle;
QUIT;

Príklad:

proc sql;
      connect to Oracle (user=user1 pw=pasw1 path=DB1);
       create table test_table  as
      select *
       from connection to Oracle
            (select  *  from test.table1)  ;
       disconnect from Oracle;
quit ;

pozn.: hodnota SCHEMA= nadefinovaná v LIBNAME príkaze je teraz súčasťou dvoj-úrovňového názvu tabuľky v rámci ORACLE SQL príkazu. Tento názov je vo forme: Schema.Table_name  

Preberanie názvov atribútov a dátových typov z ORACLE do SAS

Názvy atribútov
Maximálna dĺžka názvu v SAS je 32 znakov, v ORACLE je to 30 znakov.

Názov v SAS musí začínať písmenom alebo podčiarkovníkom a ďalej môže okrem písmen obsahovať aj čísla a ďalšie podčiarkovníky. Pre názvy v ORACLE platia podobné pravidlá, ale názvy môžu obsahovať aj iné znaky ako napríklad $ a #.

Existuje jednoduchý spôsob ako zabezpečiť, aby sa názvy stĺpcov v tabuľkách načítali s kompatibilnými znakmi z ORACLE do SAS.

Je potrebné zadefinovať DBGEN_NAME= nastavenie v LIBNAME alebo DATA príkaze. Môže obsahovať 2 hodnoty:

DBGEN_NAME=DBMS
­- všetky neštandardné znaky pre SAS názvy sa pretransformujú na podčiarkovn
­-  toto je „default“ nastavenie, čiže je v platnosti aj keď sa implicitne neuvedi
­- príklad: FLIGHT#2   ->   FLIGHT­

DBGEN_NAME=DBMS   
 ­- všetky názvy stĺpcov obsahujúce neštandardné znaky sa pretransformujú na _COLn, kde n je poradové číslo stĺpca začínajúce nulou
 ­- príklad: FLIGHT#2   ->   _COL0 

Rovnako existuje aj podobný spôsob ako zabezpečiť, aby sa tabuľky zobrazovali aj s nekompatibilnými znakmi pre SAS. Tu je jediný rozdiel v tom, že tabuľky, ktorých názvy obsahujú neštandardné znaky pre SAS, sa vôbec v nadefinovanej SAS knižnici nezobrazia.

Nastavenie, ktoré toto zabezpečí je PRESERVE_TAB_NAMES= v LIBNAME príkazy. Môže obsahovať 2 hodnoty:

PRESERVE_TAB_NAMES=NO
- tabuľky, ktorých názov obsahuje neštandardné znaky pre SAS, sa nezobrazia
- toto je „default“ nastavenie, čiže je v platnosti aj keď sa implicitne neuvedie

PRESERVE_TAB_NAMES=YES
- všetky tabuľky sa zobrazia, vrátane tých, ktoré majú v názve neštandardné znaky

pozn.:  podobné nastavenie funguje aj pre názvy stĺpcov: PRESERVE_COL_NAMES s rovnakou funkcionalitou. Pre zrýchlenie napísania kódu je možné použiť nastavenie PRESERVE_NAMES, ktoré zabezpečí nastavenie tak pre názvy tabuliek ako aj stĺpcov.

Príklad:             libname oralib oracle user=user1 pw=pasw1 path=DB1 schema=test;    

Dátové typy

ORACLE podporuje rôzne dátové typy. SAS transformuje tieto dátové typy podľa nasledujúcich pravidiel (uvedené sú len najpoužívanejšie dátové typy): 

ORACLE dátový typ

SAS dátový typ

SAS formát

SAS dĺžka

CHAR(size)

Character

$n. (n=dĺžka v ORACLE podľa „size“)

dĺžka v ORACLE podľa „size“

VARCHAR(size)

Character

$n. (n=dĺžka v ORACLE podľa „size“)

dĺžka v ORACLE podľa „size“

VARCHAR2(size)

Character

$n. (n=dĺžka v ORACLE podľa „size“)

dĺžka v ORACLE podľa „size“

LONG

Character

$200. (Oracle7);
$1024. (Oracle8
a Oracle9)

200 (Oracle7);
1024 (Oracle8
a Oracle9)

RAW

Character

$HEXn. (n=2-krát dĺžka v ORACLE)

2-krát dĺžka v ORACLE

NUMBER

Numeric

žiadny

8 bytes

NUMBER(p,s)

Numeric

p.s (resp. w.d)

8 bytes

DATE

Numeric

DATETIME20.

8 bytes

pozn.:  „size“ môže byť nadefinovaná dvoma spôsobmi:
  1. BYTE napr. VARCHAR2(10 BYTE) – v tomto prípade každý znak predstavuje 1 BYTE a tým pádom SAS nastaví dátový typ na Character o dĺžke 10.
  2. CHAR napr. VARCHAR2(10 CHAR) – v tomto prípade môže každý znak predstavovať 1 až n BYTE, pričom n závisí od konkrétneho nastavenia prostredia ORACLE. SAS podľa toho nastaví dátový typ na Charackter o dĺžke size*n, napr. 10*3=30.

V prípade, že chceme zmeniť dátový typ priamo pri načítavaní dát z ORACLE do SAS, je možné použiť tieto tri spôsoby:

  1. aplikovať špeciálne nastavenie v DATA SET príkaze :

    DBSASTYPE =(column-name-1=<'>SAS-data-type<'>column-name-n=<'>SAS-data-type<'>)
         SAS-data-type môže byť:
    CHAR(dĺžka)
         NUMERIC
    DATETIME
    DATE
    TIME

         Úvodzovky sa vyžadujú len v prípade dátového typu CHAR().
    Príklad:
     Predpokladajme, že premenná POKUS je v ORACLE typu CHAR(10 BYTE). SAS túto premennú načíta ako Character o dĺžke 30 (ak je v ORACLE nastavená dĺžka znaku na max. 3 BYTE).
         My chceme, aby táto premenná mala aj po načítaní do SAS dĺžku 10. To zabezpečíme týmto kódom

         data test;
        set oralib.table1 (dbsastype=(pokus='CHAR(10)'));
    run;

  2. nastaviť dĺžku premennej pred načítaním dátového typu z ORACLU použitím LENGTH príkazu:
    P         ríklad:

    -      načítanie dát s nezmenenými dĺžkami:

    data test;
         set oralib.table1;
    run;
     
    - načítanie dát so zmenenými dĺžkami:

    data test;
         length pokus $10;
         set oralib.table1;
    run;
  3. použiť kompresiu SAS datasetu

vytvorený dataset má pre znakové premenná použitú premenlivú, nie fixnú dĺžku poľa (podobne  ako VARCHAR dátový typ v databázach).

Kompresiou dosiahneme menší počet I/O operácií a menšiu spotrebu diskového priestoru na úkor väčšieho počtu CPU operácií pri ukladaní tabuľky (kompresia údajov) a práci s ňou (dekompresia údajov). Vzhľadom na to, že typicky je „úzkym miestom“ pri spracovaní údajov I/O výkon, býva takýto trade-off veľmi efektívny.

Efektivita kompresie je vždy závislá na konkrétnej dátovej štruktúre (počty, typy, dĺžky atribútov), no aj na samotnom obsahu dát (krátke texty v dlhom znakovom poli, missing hodnoty, atď.).

Samotná kompresia sa vykonáva na úrovni jedného záznamu tabuľky, nie je ekvivalentom kompresie súboru.

 Príklad:

  • kompresia znakových premenných – odporúčaná pri tabuľkách s viacerámi znakovými premennými dlhšími než 8 znakov, no efekt sa prejaví aj pri „prázdnych“ numerických premenných.

         data test(compress=char) /* ekvivalentne compress=yes */;   
         set oralib.table1;
    run;
  • binárna kompresia – efektívna pri tabuľkách s veľkým počtom atribútov (vtedy môže byť efektívnejšia než znaková kompresia), používa sa zvyčajne menej než znaková kompresia

    data test(compress=binary);
             length pokus $10;
         set oralib.table1;
    run;

Predpoklady pre optimálny performance

Pre efektívnu spoluprácu SAS a databázy je dôležité predovšetkým:

  1. Minimalizovať data transfer medzi databázou a SAS = network, t.j. prenos čo najväčšej časti dotazu priamo do databázy, predovšetkým:
    -  vykonanie filtrovacích podmienok v databáze, 
    - vykonanie joinovacích podmienok v databáze, 
    - vykonanie agregačných funkcií v databáze (group by, distinct), 
    -     minimálne dátové konverzie.  
  2. Optimalizovať nevyhnutný prenos dát medzi databázou a SAS – buffering (nastavenie READBUFF, INSERTBUFF, UPDATEBUFF, DBCOMMIT), threading (počet a využitie dostupných CPU/jadier). 

  3. Minimalizovať objem výstupu dotazu v SAS – optimalizácia SAS storage - rozsah a formát uloženia výsledku v SAS – dĺžka riadku, dĺžky premenných, kompresia tabuľky.

Optimalizácia uloženia výsledkov dotazov na databázu do SAS tabuliek znamená predovšetkým zaoberanie sa uložením textových polí a použitím kompresie SAS tabuliek.

V praxi to jednoducho znamená, že je treba dať pozor, aké ORACLE dátové typy sa nachádzajú v dátach, ktoré načítavate do SAS dátového formátu resp. ktoré používate na analýzy v SAS nástrojoch priamo z ORACLE.

Je veľmi dôležité, aby dĺžky charakterových premenných neboli extrémne veľké, pretože to významnou mierou spomaľuje spracovanie dát a trvanie jednotlivých analýz.

V prípade, že napr. dĺžka premennej v ORACLE je typu VARCHAR2(), v ORACLE je to premenná s premenlivou dĺžkou. To znamená, že ORACLE priraďuje dĺžku záznamu podľa aktuálnej hodnoty v danom riadku.

SAS pri použití SAS/ACCESS® to ORACLE však priradí takejto premennej štandardne (bez použitia kompresie) fixnú dĺžku podľa hodnoty „size“ – SAS tabuľky sú štandardne vytvárané s pevnou dĺžkou záznamu. Variabilná dĺžka záznamu sa dosahuje použitím kompresie tabuľky.

To znamená, že ak napr. najdlhšia existujúca hodnota pre atribút MENO je 50 znakov a dátový typ je VARCHAR2(4000 BYTE), tak v ORACLE je najdlhší záznam o dĺžke 50 znakov, ale po načítaní do SAS je to 4000 znakov pre každý záznam.

SAS pracuje s touto dĺžkou, čím sa neúmerne zvyšuje spotreba diskovej kapacity a počet I/O operácií. V prípade, že analyzované dáta obsahujú viac takýchto premenných a dosahujú veľkosť až niekoľko miliónov záznamov, môže dôjsť až k úplnému vyčerpaniu diskovej kapacity pre WORK alebo k výraznému predĺženiu spracovania dát.

Príklad s/bez použitia kompresie (COMPRESS=YES):

Tabuľka 1

Tabuľka 2

 

bez kompresie

s kompresiou

bez kompresie

s kompresiou

počet záznamov

100 000

100 000

premenná I

Numeric 8

Numeric 8

premenná X

Character $50

Character $4000

veľkosť

6.1 MB

2  MB

390.6 MB

2.5 MB

čas potrebný na vytvorenie tabuľky:

real time

0.09 s

0.23 s

15.70 s

0.89 s

cpu time

0.01 s

0.15 s

1.75 s

0.86 s

čas potrebný na vytvorenie tretej tabuľky spojením 2 rovnakých tabuliek:

real time

0.82 s

0.82 s

1 m 07.42 s

50.96 s

cpu time

0.21 s

0.31 s

8.96 s

10.15 s

veľkosť výslednej tabuľky

10.7 MB

2.3 MB

781.2 MB

3.5 MB

použitý diskový priestor pre danú operáciu

12 MB

2.3 MB

2.4 GB

1.2 GB

 

autor: Richard Balaj