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