Hurtownie Danych na platformie ORACLE

pozyskiwanie

Zasilanie

Analiza

Raportowanie

Wnioskowanie

OLAP Server

Multiple Table Insert /Upsert

Transportable tablespace.

GATEWAY (homo)

Rafał Kowalski 

Transport danych

1

Wnioskowanie

SQLLDR

External table

Data Pump 

GATEWAY (hetero/homo)

Multiple Table Insert 

Upsert

Index

Constraint

Partitioning

Materialized view

Dimension (Hierarchy)

Fact

Transport danych

SQL*Plus

(

SPOOL

)

OCI, Pro*C

SQLLDR

Tabele zewn

ę

trzne

Pliki 

tekstowe

EKSPORT

IMPORT

Pozyskiwanie

EXPORT

IMPORT

Rafał Kowalski 

Transport danych

2

Pliki binarne

Sieci 

komputerowe

EXPORT

IMPORT

Przenoszenie przestrzeni tabel

Partycjonowanie tabel

Tabele zewn

ę

trzne/Pompa danych

BRAMY HETEROGENICZNE

(np.: odbc)

BRAMY HOMOGENICZNE

(

Rozproszone transakcje)

Zasilanie

SQL 

(eksport)

ZADANIE 4:

Napisa

ć

 skrypt 

EMPEXP.SQL

, który stworzy nast

ę

puj

ą

cy plik tekstowy:

7369;"SMITH";"CLERK";7902;17-12-1980;800;
7499;"ALLEN";"SALESMAN";7698;20-02-1981;1600;300
7521;"WARD";"SALESMAN";7698;22-02-1981;1250;500
7566;"JONES";"MANAGER";7839;02-04-1981;2975;

SET COLSEP ';'

Rafał Kowalski 

Transport danych

3

SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SPOOL &1..txt
SELECT empno||';"'||ename||'";"'||job||'";'||mgr||

';'||TO_CHAR(hiredate,'DD-MM-YYYY')||';'||sal||';'||comm

FROM emp;
SPOOL OUT
SET HEADING ON
SET FEEDBACK ON
SET PAGESIZE 15

SQLLDR

Rafał Kowalski 

Transport danych

4

SQLLDR 

– separator kolumn

CREATE TABLE etl_zaliczenie (

ETL_ZALICZENIE_SEP.CTL

LOAD DATA 

INFILE 'test2.dat'

APPEND

INTO TABLE etl_zaliczenie

FIELDS TERMINATED BY "," 

OPTIONALLY ENCLOSED BY '"' 

(imie, nazwisko, ocena,

"Rafał","Kowalski",5,2003-10-12

„Donald",„Kaczor",3,2003-09-12

ETL_ZALICZENIE_SEP.DAT

Rafał Kowalski 

Transport danych

5

C:\ORACLE9\BIN\SQLLDR userid=scott/tiger control= ETL_ZALICZENIE_SEP.ctl 

ETL_ZALICZENIE_SEP.BAT

CREATE TABLE etl_zaliczenie (

imie VARCHAR2(20)

, nazwisko VARCHAR2(20)

, ocena NUMBER(2)

, datazal DATE

);

(imie, nazwisko, ocena,

datazal DATE "YYYY-MM-DD")

CREATE TABLE etl_zaliczenie (

SQLLDR 

– stały format kolumn

ETL_ZALICZENIE_KOL.CTL

LOAD DATA 

INFILE 'test2.dat'

APPEND 

INTO TABLE etl_zaliczenie

( imie POSITION(1:6)

,nazwisko POSITION(8:15)

,ocena POSITION(17:17)

Rafał  Kowalski 5 2001-10-12

Donald Kaczor   3 2001-09-12

ETL_ZALICZENIE_KOL.DAT

Rafał Kowalski 

Transport danych

6

CREATE TABLE etl_zaliczenie (

imie VARCHAR2(20)

, nazwisko VARCHAR2(20)

, ocena NUMBER(2)

, datazal DATE

);

SQLLDR userid=scott/tiger control= ETL_ZALICZENIE_KOL.ctl 

ETL_ZALICZENIE_KOL.BAT

,ocena POSITION(17:17)

,datazal POSITION(19:29) DATE "YYYY-MM-DD„

)

Import Zaawansowany

OPTIONS (DIRECT=TRUE)

UNRECOVERABLE LOAD DATA

INFILE 'purchases.dat' BADFILE 'purchases.bad'

APPEND

INTO TABLE purchases

PARTITION (purchases_jan2005)

LOAD DATA

Rafał Kowalski 

Transport danych

7

sqlldr USERID=easydw/easydw CONTROL=purchases.ctl

LOG=purchases.log DIRECT=TRUE SKIP_INDEX_MAINTENANCE=TRUE

LOAD DATA

INFILE 'product.dat' append

INTO TABLE product WHEN product_id != BLANKS

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'"

(product_id "upper(:product_id)",

create directory etl_ext_source as '/opt/ora9/oradata/etl';

grant read,write on directory etl_ext_source to public;

CREATE TABLE etl_zaliczenie (

imie

CHAR(20)      

, nazwisko

CHAR(50)

, ocena

NUMBER(2)

)

ORGANIZATION EXTERNAL (

Tabele Zewn

ę

trzne/SqlLoader 

– formatowanie „naturalne”

ETL_ZALICZENIE.DAT

"Rafa";"Kowalski";5;2003-10-12
"Donald"; "Kaczor";3;2003-09-12
"Łakomczucha"; "Mucha";2;2003-10-21

Rafał Kowalski 

Transport danych

8

ORGANIZATION EXTERNAL (

TYPE oracle_loader

DEFAULT DIRECTORY etl_ext_source

ACCESS PARAMETERS (

RECORDS DELIMITED BY newline 

FIELDS TERMINATED BY ';'

OPTIONALLY ENCLOSED BY '"'

)

LOCATION('etl_zaliczenie.dat')

);

UNIX "\n" 

Win "\r\n" 

CREATE TABLE etl_zaliczenie (

imie

CHAR(20)      

, nazwisko

CHAR(50)

, ocena

NUMBER(2)

, 

datazal

DATE

)

ORGANIZATION EXTERNAL (

TYPE oracle_loader

DEFAULT DIRECTORY etl_ext_source

Tabele Zewn

ę

trzne/SqlLoader 

– formatowanie „strukturalne”

ETL_ZALICZENIE.DAT

"Rafa";"Kowalski";5;2003-10-12
"Donald"; "Kaczor";3;2003-09-12
"Łakomczucha"; "Mucha";2;2003-10-21

Rafał Kowalski 

Transport danych

9

DEFAULT DIRECTORY etl_ext_source

ACCESS PARAMETERS (

RECORDS DELIMITED BY "\r\n" 

FIELDS TERMINATED BY ';'

OPTIONALLY ENCLOSED BY '"'

(imie, nazwisko, ocena,

datazal CHAR(10) DATE_FORMAT DATE MASK "YYYY-MM-DD"

) 

)

LOCATION('etl_zaliczenie.dat')

);

UNIX "\n" 

Win "\r\n" 

CREATE TABLE purchases_xt2(

product_id VARCHAR2(8)

,time_key DATE

CREATE TABLE purchases_xt  ORGANIZATION EXTERNAL (

TYPE ORACLE_DATAPUMP

DEFAULT DIRECTORY xt_dir

ACCESS PARAMETERS (version ‘10.2.0’)

LOCATION ('purch_xt.dmp') 

) AS SELECT * FROM purchases;

Tabele Zewn

ę

trzne/DataPump

purchases

xt_dir

purch_xt.dmp

,customer_id VARCHAR2(10)

. . .

) ORGANIZATION EXTERNAL(

TYPE ORACLE_DATAPUMP

DEFAULT DIRECTORY xt_dir

LOCATION ('purch_xt.dmp')

);

Rafał Kowalski 

Transport danych

10

Purchases_xt2

ROZPROSZONE TRANSAKCJE

Lokalna 

hurtownia 

danych

Za siedmioma lasami
... gdzie

ś

 bardzo daleko w 

ś

wiecie,

pracuje operacyjna baza danych „EarthSea„

promocje

vetch = (...)

TNSNAMES.ORA

CREATE  DATABASE LINK vetch

CONNECT TO wsisiz IDENTIFIED BY bartek USING ‘vetch';

Rafał Kowalski 

Transport danych

11

CONNECT TO wsisiz IDENTIFIED BY bartek USING ‘vetch';

INSERT INTO etl_promocje   SELECT * FROM   promotions@vetch;

DROP DATABASE LINK vetch;

CREATE  DATABASE LINK vetch

CONNECT TO wsisiz IDENTIFIED BY bartek 
USING '(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST =62.179.58.161) (PORT = 1521))

(CONNECT_DATA = (SID = XE) ) )';

INSERT INTO etl_promocje   SELECT * FROM   promotions@vetch;

DROP DATABASE LINK vetch;

Brama HETEROGENICZNA

Oracle 
Transparent 
Gateway

Oracle 
Transparent 
Gateway

SERWER ORACLE

<$oracle_home>\network\admin\

listener

.ora

...
(SID_DESC =

(PROGRAM = hsodbc)
(SID_NAME = 

ETL

)

...

Oracle 

Client

<$oracle_home>\hs\admin\

init

etl

.ora

...

NET 8

Rafał Kowalski 

Transport danych

12

Ź

ródło danych

relacyjnych

(

etl_odbc_msacc.mdb

)

Sterownik ODBC

MS Windows: systemowe DSN 

ETL

...

HS_FDS_CONNECT_INFO = 

ETL

...

<$oracle_home>\network\admin\

tnsnames

.ora

...
ETL_MSACC =

(DESCRIPTION =

(ADDRESS = ...)
(CONNECT_DATA = (SID = 

ETL

))

(HS =OK))

SERWER ORACLE

Brama HETEROGENICZNA 

1

Serwer ODBC

a.

Stworzy

ć

katalog i baz

ę

 MS Access dla 

ź

ródeł danych typu ODBC

C:\temp\odbc\etl_odbc_msacc.mdb 

Rafał Kowalski 

Transport danych

13

Brama HETEROGENICZNA

2

Serwer ODBC

b.

Doda

ć

nowe

ź

ródło danych ODBC.

Przej

ść

przez 

ś

cie

ż

k

ę

menu: Start -> Settings -> 

Control panel i otworzy

ć

okno 

ź

ródeł danych ODBC.

Rafał Kowalski 

Transport danych

14

c.

Stworzy

ć

nowe 

ź

ródło typu MS Access 

Driver.

d.

Wypełni

ć

parametry nowego 

ź

ródła ODBC.

Wpisa

ć

nazw

ę

„ETL” i wypełni

ć ś

cie

ż

k

ę

do pliku 

etl_odbc_msacc

.MDB”

Brama HETEROGENICZNA 

3

W katalogu {Oracle HOME} nale

ż

y odszuka

ć

 podkatalog [HS], a w nim 

podkatalog [ADMIN],np.:

C:\ORACLE\ORA81\HS\ADMIN\

W katalogu [ADMIN] nale

ż

y skopiowa

ć

 plik inithsodbc.ora na nowy plik 

opisu poł

ą

czenia 

ź

ródła danych ODBC. Nazwa nowego pliku MUSI 

mie

ć

 posta

ć

: INIT{SID}.ORA, na przykład: "

INIT

ETL

.ORA

".

e.

Przygotowa

ć

informacje steruj

ą

ce bram

ą

 poł

ą

czenia

heterogenicznego.

(Klucz do bramy)

Rafał Kowalski 

Transport danych

15

mie

ć

 posta

ć

: INIT{SID}.ORA, na przykład: "

INIT

ETL

.ORA

".

HS_FDS_CONNECT_INFO = ETL

HS_FDS_TRACE_LEVEL = OFF

#

# Environment variables required for the 

non-Oracle system

#

#set <envvar>=<value>

Przykładowy plik 

INITETL.ORA.

Nazwa 

ź

ródła ODBC

Brama HETEROGENICZNA 

4

W katalogu {Oracle HOME} nale

ż

y odszuka

ć

 katalog [NETWORK], a w 

nim katalog [ADMIN],np.:

C:\ORACLE\ORA81\NETWORK\ADMIN\

W katalogu [ADMIN] nale

ż

y otworzy

ć

 plik parametrów konfiguracyjnych 

LISTENER.ORA i doda

ć

 niezb

ę

dne parametry nowego nasłuchu.

f.

Przygotowa

ć

informacje steruj

ą

ce dla programu Listener instancji 

Oracle.

Poł

ą

czenie bramy z 

nasłuchem ORACLE

Rafał Kowalski 

Transport danych

16

SID_LIST_LISTENER =

(SID_LIST =

*  *  *

(SID_DESC =

(PROGRAM = hsodbc)

(SID_NAME = ETL)

(ORACLE_HOME = c:\oracle9)

)

*  *  *

)

Nazwa zwi

ą

zana 

z plikiem w 
katalogu [HS].

Brama HETEROGENICZNA 

5

Aktywacja nasłuchu

O:\oracle\ora92\hs\admin>lsnrctl stop

LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 - .    .    .

Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.

ł

ą

czy si

ę

 z 

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=super)(PORT=1521)))

Polecenie zako

ń

czone powodzeniem

g.

Reaktywowa

ć

 proces nasłuchu z nowym parametrem.

Rafał Kowalski 

Transport danych

17

Polecenie zako

ń

czone powodzeniem

O:\oracle\ora92\hs\admin>lsnrctl start

.    .    .

Usługa „ETL" ma liczb

ę

 instancji równ

ą

 1.

Instancja „ETL", stan UNKNOWN, ma dla tej usługi 1 

procedur

ę

 obsługi

...

Polecenie zako

ń

czone powodzeniem

O:\oracle\ora92\hs\admin>

Brama HETEROGENICZNA 

5 

konfiguracja poł

ą

czenia klienta

W katalogu {Oracle HOME} nale

ż

y odszuka

ć

 katalog [NETWORK], a w 

nim katalog [ADMIN],np.:

C:\ORACLE\ORA81\NETWORK\ADMIN\

W katalogu [ADMIN] nale

ż

y otworzy

ć

 plik parametrów konfiguracyjnych 

TNSNAMES.ORA i doda

ć

 niezb

ę

dne parametry nowego aliasu

Nowy alias 

h.

Na serwerze klienta trzeba zdefiniowa

ć

 alias poł

ą

czenia z 

serwerem bramy heterogenicznej.

Rafał Kowalski 

Transport danych

18

ETL_MSAC =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)

(HOST = 

koziolek.pacanow.edu.pl

)(PORT = 1521)

)

(CONNECT_DATA = (SID = ETL))

(HS =OK)

)

Nazwa zdefiniowana w pliku 

konfiguracyjnym LISTENER.ORA

Nowy alias 

poł

ą

czenia 

NET8

Brama HETEROGENICZNA 

5 

Odczyt zdalnej bazy danych.

SQL> CREATE DATABASE LINK etl_hg USING ‘ETL_MSAC’;

Powi

ą

zanie bazodanowe zostało utworzone.

SQL> SELECT * FROM zaliczenie@etl_hg;

i.

Stworzy

ć

 logiczne poł

ą

czenie z baz

ą

 danych i odczyta

ć

 informacje.

Rafał Kowalski 

Transport danych

19

. . .

HOKUS POKUS

Konfiguracja bez protokołu NET 8

.

SQL>

SQL> CREATE DATABASE LINK etl_hg USING '(DESCRIPTION =

2      (ADDRESS = (PROTOCOL = TCP)

3        (HOST = koziolek.pacanow.edu.pl)(PORT = 1521))

4      (CONNECT_DATA = (SID = ETL))

5      (HS =OK)

6    )';

Powi

ą

zanie bazodanowe zostało utworzone.

Rafał Kowalski 

Transport danych

20

SQL> SELECT * FROM channels@etl_hg;

SQL> SELECT * FROM countries@etl_hg;

SQL> SELECT * FROM promotions@etl_hg;

SQL> CREATE TABLE channels

AS SELECT * FROM channels@etl_hg;

SQL>

CREATE TABLE  countries

AS SELECT * FROM countries@etl_hg;

SQL>

CREATE TABLE promotions

AS SELECT * FROM promotions@etl_hg;

. . .

KONIEC

Rafał Kowalski 

Transport danych

21