O czym
Jeśli stajesz przed koniecznością połączenia danych z różnych źródeł, potrzebujesz zmigrować informacje, dokonać operacji ETL pomiędzy bazami różnych producentów bądź po prostu zaimportować dane np. z Excela, DB2 czy MySQL do Oracle to Transparent Database Gateways są stworzone właśnie dla Ciebie. Dzięki nim w prosty sposób będziesz mógł zintegrować dane z wielu źródeł, a nawet złączyć je w pojedynczym zapytaniu SQL. Modyfikacja danych w arkuszu Excel z poziomu SQL’a? Zastosowanie grupowania do danych z pliku CSV? Import danych z DB2? Dla Oracle’a żaden problem.
Oracle Database Gateway dla ODBC – Oracle Generic Connectivity
Oracle Database Gateways umożliwiają konfigurację połączenia do dużej ilości zewnętrznych źródeł danych, w tym bezpośrednio do baz takich jak np. DB2, Microsoft SQL Server czy Sybase. Ponieważ istnieje możliwość podłączenia się ze źródłem danych zdefiniowanym w ODBC to właściwie dane z każdej bazy (i nie tylko – np. wartości z plików Excel’a, lub plików CSV) są możliwe do wykorzystania z poziomu języka SQL w Oracle. Wachlarz jest tutaj bardzo szeroki, bo sterowniki ODBC istnieją do takich źródeł jak np. dBase, MySQL, Access, Paradox, Excel, SQLite, PostgreSQL i wiele innych. Można dzięki temu w prosty sposób dokonać migracji wybranych tabel wykorzystując np. CTAS (CREATE TABLE AS SELECT) bądź nawet złączyć tabelę zdalną z lokalną (przykładowo można wyobrazić sobie mobilny system przedstawicieli handlowych z tabelą zamówień łączoną z centralną tabelą klientów). Mechanizm o którym mowa w połączeniu z możliwościami jakie dają widoki zmaterializowane (np. automatyczne odświeżanie, query rewrite itp.) dają możliwość ekstrakcji i ładowania danych bezpośrednio z systemów OLTP róznych producentów do środowiska umożliwiającego analizę kostek OLAP. Pozwala to zaprojektować proces w sposób w pełni zautomatyzowany i dokładnie rozplanowany czasowo. Dodajmy do tego możliwość przeprowadzenia procesu transformacji przy użyciu PL/SQL’a i gotowe środowisko ETL mamy bezpośrednio w bazie danych Oracle.
Budowa i sposób działania Oracle Database Gateways
Transparent Oracle Database Gateways są mechanizmem mającym umożliwić wykorzystanie danych z zewnętrznych źródeł danych w sposób przeźroczysty dla języka SQL. Oznacza to, że możemy łączyć danę w heterogenicznym środowisku rozproszonym w sposób niezależny od ich faktycznego położenia. Izolacja języka SQL od lokalizacji i co ważniejsze technologii w jakiej osadzone są dane jest na tyle daleko idąca, że możemy zmienić lokalizację tabeli odczytywanej poprzez ODG z np. MSSQL na DB2 i bez zmian w kodzie SQL wszystko nadal będzie działać poprawnie. Cały mechanizm opiera się o działanie dwóch współpracujących elementów: komponentu umożliwiającego łączenie się z systemami innymi niż Oracle (serwisem heterogenicznym – Heterogeneous Service) będącym wspólnym dla wszystkich docelowych technologii oraz agenta będącego dedykowanym dla konkretnego dostawcy, współpracującym bezpośrednio z zewnętrznym źródłem danych.
Federacyjne bazy danych
Rozwój technologi Oracle Transparent Gateways oraz podobnych w systemach innych producentów (np. InfoSphere Federation Server) doprowadziło do narodzin federacyjnych baz danych. To koncepcja w której poszczególne, wyspecjalizowane bazy danych obsługują elementy działania przedsiębiorstwa, udostępniając jednocześnie na zewnątrz część informacji dla bazy federacyjnej. Ta ostatnia najczęściej jest elementem ładującym hurtownię danych, bądź zintegrowanym środowiskiem raportowym dla wszystkich systemów OLTP działających w przedsiębiorstwie. Unifikacja dostępu do danych składowanych na różnych serwerach bazodanowych, systemach operacyjnych i maszynach powoduje, że dotychczasowy proces ekstrakcji danych (np. w procesie ETL hurtowni danych), widziany jako zadanie wsadowe, kolejno wyciągające dane z poszczególnych systemów źródłowych staje się przeżytkiem. W zamian otrzymujemy w pełni funkcjonalny serwer bazodanowy, z możliwością generowania wyników zapytań SQL niezależnie od miejsca położenia danych, co oczywiście ułatwia proces ekstrakcji i jednocześnie otwiera szereg dodatkowych możliwości – np. kokpity (dashboards) integrujące dane z wielu źródeł (systemów OLTP) w czasie rzeczywistym, czy możliwość raportowania przekrojowego przez kilka systemów operacyjnych.
Sposób konfiguracji
Poniżej krok po kroku przedstawiono sposób konfiguracji Oracle Database Transparent Gateway dla ODBC (Oracle Generic Connectivity) i połączenie się ze źródłem danych jakim jest plik XLS. nie ma jednak żadnych przeszkód by elementem źródłowym była dowolna inna baza danych dla której istnieje sterownik ODBC – np. dBase, Paradox, DB2 czy MSSql (z tym, że dla tych dwóch ostatnich lepszym rozwiązaniem będzie wyeliminowanie ODBC i skorzystanie z natywnych agentów). Realizacja opisana jest dla systemu Windows, chociaż nie jest to jedyna możliwa opcja.
1. Tworzymy plik XLS z dwiema wartościami w kolumnach A i B (dowolne wartości)
2. W panelu sterowania wybieramy Narzędzia administracyjne -> Źródła danych ODBC
3. Klikamy zakładkę systemowe DNS i klikamy “Dodaj…”
4. Na liście sterowników wybieramy Driver dla Microsoft Excel(*.xls) i klikamy “Zakończ”
Oczywiście wybieramy Microsoft Excel bo prawdopodobnie każdy z Was ma go u siebie zaintalowanego. Nic nie stoi na przeszkodzie by wybrać tutaj sterownik ODBC do dowolnej bazy danych.
5. Pojawia się okienko “ODBC – ustawienia dla programu Microsoft Excel” w którym wpisujemy nazwę źródła danych “excel” oraz klikamy przycisk “Opcje >” celem odznaczenia pola “tylko do odczytu”. Następnie wskazujemy przycisk “Wybierz skoroszyt”
6. W okienku wyboru skoroszytu wskazujemy nasz plik data.xls i zatwierdzamy klikając na “OK”
7. Potwierdzamy także okienko konfiguracji sterownika ODBC i Administratora źródeł ODBC – w obu klikamy “OK”
8. W katalogu $ORACLE_HOME\hs\admin edytujemy plik inithsodbc.ora dodając do niego wpis taki jak na załączonym zrzucie ekranowym. W ten sposób wskazujemy nazwę połączenia ODBC
9. W katalogu $ORACLE_HOME\NETWORK\ADMIN edytujemy plik listener.ora dodając do niego konfigurację procesu nasłuchu usługi heterogenicznej dla odbc (hsodbc) – fragment zaznaczony na zrzucie.
10. Ostatnim plikiem, który trzeba wyedytować jest tnsnames.ora w $ORACLE_HOME\NETWORK\ADMIN gdzie tworzymy nowy wpis – w przykładzie nazwany ODBC_TNS. Ważne oczywiście aby SERVER_ID ustawić na IP swojego serwera, bądź na jego nazwę w sieci.
11. W bazie danych Oracle tworzymy link bazodanowy będący obiektem schematu łączącym nas ze zdalną bazą dostępną przez ODBC (ale baza nie wie czy za ODBC stoi plik Excel, baza DB2 czy MSSQL). Polecenie wygląda następująco:
CREATE DATABASE LINK plik_excel CONNECT TO login IDENTIFIED BY password USING 'ODBC_TNS';
Nazwa użytkownika i hasło w tym przypadku są dowolne (sterownik ODBC dla Excela je ignoruje, ale np. w przypadku podłączenia do DB2 należałoby tu podać login i hasło do tej bazy).
12. Ostatnim krokiem jest restart procesu nasłuchu poleceniem Lsnrctl stop i jego ponowne uruchomienie Lsnrctl start – oba wywoływane z linii komend a następnie restart instancji.
13. Po zalogowaniu się do bazy możemy wykonać zapytanie do zdalnej bazy np.
SELECT * FROM all_tables@plik_excel;
Tu ważna uwaga – plik xls musi być zamkniety w programie Excel, gdyż w przeciwnym wypadku byłby zablokowany i baza nie mogłaby uzyskać do niego dostępu.
14. Możemy spróbować wywołac zapytanie do konkretnego arkusza:
SELECT * FROM Sheet1$@plik_excel;
w tym tworząc bardziej ciekawe zapytania
15. A co z UPDATE’m? Sprawdźmy czy odznaczenie opcji “Tylko do odczytu” w okienku konfiguracyjnym sterownika ODBC dla Excel’a faktycznie działa.
Jak widać można podpiąć dane z Excela do Oracle’a z możliwością ich modyfikacji instrukcją UPDATE! W niektórych wersjach sterownika ODBC dla Excel UPDATE jest operacją niedozwoloną i zakończy się błędem.
16. W moim sterowniku ODBC dla Excela UPDATE jest ok, ale problem jest z transakcjami. Poeksperymentujmy zatem z nimi – wycofajmy poprzedni update (rollback) i zakończmy sesję (jest to konieczne do tego aby plik xls mógł zostać otwarty w Excel’u).
Jak widać transakcyjność w przypadku tego sterownika nie działa. Inaczej byłoby w przypadku sterowników np. dla bazy DB2 – Oracle Gateways oferują nawet mechanizm dwufazowego commita do zapewnienia bezpieczeństwa transakcji, ale jak widać wszystko zależy też od warstwy pośredniej (tu konkretnego sterownika ODBC).
Mechanizm Oracle Database Gateway dla ODBC – Oracle Generic Connectivity jest świetnym narzędziem umożliwiającym szereg reultatów do tej pory nieosiągalnych. Warto poznać jego sposób działania by móc korzystać np. z łatwiejszej migracji danych (po co zrzucać dane do pliku dump, skoro zajmuje to tylko nieporzebnie czas i miejsce, czasem CTAS w zupełności wystarczy).