421 lines
14 KiB
Markdown
421 lines
14 KiB
Markdown
# AGGREGATED_ALLOTMENT External Tables Setup
|
|
|
|
Ten dokument przedstawia kompletny przykład tworzenia trzech external tables dla tabeli `AGGREGATED_ALLOTMENT` według wzorca FILE PROCESSOR System.
|
|
|
|
## Przegląd tabeli źródłowej
|
|
|
|
**Tabela źródłowa:** `OU_TOP.AGGREGATED_ALLOTMENT`
|
|
**Kolumna kluczowa do partycjonowania:** `ALLOTMENT_DATE` (DATE)
|
|
**Liczba kolumn:** 43
|
|
**Główne kolumny dat:** ALLOTMENT_DATE, VALUE_DATE, MATURITY_DATE
|
|
|
|
## Proces krok po kroku
|
|
|
|
### Krok 1: Tworzenie tabeli template (jeśli nie istnieje)
|
|
|
|
```sql
|
|
-- Sprawdź czy template już istnieje
|
|
SELECT table_name FROM all_tables
|
|
WHERE owner = 'CT_ET_TEMPLATES' AND table_name = 'TOP_AGGREGATED_ALLOTMENT';
|
|
|
|
-- Jeśli nie istnieje, utwórz template table
|
|
CREATE TABLE CT_ET_TEMPLATES.TOP_AGGREGATED_ALLOTMENT
|
|
AS SELECT * FROM OU_TOP.AGGREGATED_ALLOTMENT WHERE 1=2;
|
|
|
|
-- Weryfikacja struktury template
|
|
SELECT column_name, data_type, data_length, nullable
|
|
FROM user_tab_columns
|
|
WHERE table_name = 'TOP_AGGREGATED_ALLOTMENT'
|
|
ORDER BY column_id;
|
|
```
|
|
|
|
**Cel:**
|
|
- Template definiuje strukturę dla external tables
|
|
- Używa `WHERE 1=2` aby skopiować tylko strukturę, bez danych
|
|
- Znajduje się w schemacie `CT_ET_TEMPLATES` dla centralnego zarządzania
|
|
|
|
### Krok 2: Tworzenie trzech external tables
|
|
|
|
**Metoda zalecana:** Użyj pakietu `ODS.FILE_MANAGER_ODS` (działa z każdego kontekstu użytkownika)
|
|
|
|
```sql
|
|
-- External table dla INBOX (pliki przychodzące)
|
|
BEGIN
|
|
ODS.FILE_MANAGER_ODS.CREATE_EXTERNAL_TABLE(
|
|
pTableName => 'TOP_AGGREGATED_ALLOTMENT_INBOX',
|
|
pTemplateTableName => 'CT_ET_TEMPLATES.TOP_AGGREGATED_ALLOTMENT',
|
|
pPrefix => 'INBOX/TOP/AGGREGATED_ALLOTMENT',
|
|
pBucketUri => CT_MRDS.ENV_MANAGER.gvInboxBucketUri
|
|
);
|
|
END;
|
|
/
|
|
|
|
-- External table dla ODS (dane operacyjne)
|
|
BEGIN
|
|
ODS.FILE_MANAGER_ODS.CREATE_EXTERNAL_TABLE(
|
|
pTableName => 'TOP_AGGREGATED_ALLOTMENT_ODS',
|
|
pTemplateTableName => 'CT_ET_TEMPLATES.TOP_AGGREGATED_ALLOTMENT',
|
|
pPrefix => 'ODS/TOP/AGGREGATED_ALLOTMENT',
|
|
pBucketUri => CT_MRDS.ENV_MANAGER.gvDataBucketUri
|
|
);
|
|
END;
|
|
/
|
|
|
|
-- External table dla ARCHIVE (dane historyczne)
|
|
BEGIN
|
|
ODS.FILE_MANAGER_ODS.CREATE_EXTERNAL_TABLE(
|
|
pTableName => 'TOP_AGGREGATED_ALLOTMENT_ARCHIVE',
|
|
pTemplateTableName => 'CT_ET_TEMPLATES.TOP_AGGREGATED_ALLOTMENT',
|
|
pPrefix => 'TOP/AGGREGATED_ALLOTMENT',
|
|
pBucketUri => CT_MRDS.ENV_MANAGER.gvArchiveBucketUri
|
|
);
|
|
END;
|
|
/
|
|
```
|
|
|
|
**Metoda alternatywna:** Użyj pakietu `CT_MRDS.FILE_MANAGER` (wymaga kontekstu użytkownika ODS)
|
|
|
|
```sql
|
|
-- UWAGA: Te komendy muszą być wykonane jako użytkownik ODS
|
|
-- ze względu na AUTHID CURRENT_USER w pakiecie FILE_MANAGER
|
|
|
|
-- External table dla INBOX (pliki przychodzące)
|
|
BEGIN
|
|
CT_MRDS.FILE_MANAGER.CREATE_EXTERNAL_TABLE(
|
|
pTableName => 'TOP_AGGREGATED_ALLOTMENT_INBOX',
|
|
pTemplateTableName => 'CT_ET_TEMPLATES.TOP_AGGREGATED_ALLOTMENT',
|
|
pPrefix => 'INBOX/TOP/AGGREGATED_ALLOTMENT',
|
|
pBucketUri => CT_MRDS.ENV_MANAGER.gvInboxBucketUri
|
|
);
|
|
END;
|
|
/
|
|
|
|
-- External table dla ODS (dane operacyjne)
|
|
BEGIN
|
|
CT_MRDS.FILE_MANAGER.CREATE_EXTERNAL_TABLE(
|
|
pTableName => 'TOP_AGGREGATED_ALLOTMENT_ODS',
|
|
pTemplateTableName => 'CT_ET_TEMPLATES.TOP_AGGREGATED_ALLOTMENT',
|
|
pPrefix => 'ODS/TOP/AGGREGATED_ALLOTMENT',
|
|
pBucketUri => CT_MRDS.ENV_MANAGER.gvDataBucketUri
|
|
);
|
|
END;
|
|
/
|
|
|
|
-- External table dla ARCHIVE (dane historyczne)
|
|
BEGIN
|
|
CT_MRDS.FILE_MANAGER.CREATE_EXTERNAL_TABLE(
|
|
pTableName => 'TOP_AGGREGATED_ALLOTMENT_ARCHIVE',
|
|
pTemplateTableName => 'CT_ET_TEMPLATES.TOP_AGGREGATED_ALLOTMENT',
|
|
pPrefix => 'TOP/AGGREGATED_ALLOTMENT',
|
|
pBucketUri => CT_MRDS.ENV_MANAGER.gvArchiveBucketUri
|
|
);
|
|
END;
|
|
/
|
|
```
|
|
|
|
### Krok 3: Eksport istniejących danych
|
|
|
|
```sql
|
|
-- Eksport wszystkich danych historycznych
|
|
BEGIN
|
|
CT_MRDS.FILE_MANAGER.EXPORT_TABLE_DATA_BY_DATE(
|
|
pSchemaName => 'OU_TOP', -- Schema źródłowa
|
|
pTableName => 'AGGREGATED_ALLOTMENT', -- Tabela źródłowa
|
|
pKeyColumnName => 'ALLOTMENT_DATE', -- Kolumna do partycjonowania
|
|
pBucketName => 'mrds_history_poc', -- Bucket docelowy
|
|
pFolderName => 'TOP/AGGREGATED_ALLOTMENT' -- Folder docelowy
|
|
-- pMinDate domyślnie DATE '1900-01-01' (eksportuje wszystkie dane historyczne)
|
|
-- pMaxDate domyślnie SYSDATE (eksportuje dane do bieżącej daty)
|
|
);
|
|
END;
|
|
/
|
|
|
|
-- Eksport danych z określonego zakresu dat
|
|
BEGIN
|
|
CT_MRDS.FILE_MANAGER.EXPORT_TABLE_DATA_BY_DATE(
|
|
pSchemaName => 'OU_TOP',
|
|
pTableName => 'AGGREGATED_ALLOTMENT',
|
|
pKeyColumnName => 'ALLOTMENT_DATE',
|
|
pBucketName => 'mrds_history_poc',
|
|
pFolderName => 'TOP/AGGREGATED_ALLOTMENT',
|
|
pMinDate => DATE '2024-01-01', -- Od 2024-01-01
|
|
pMaxDate => DATE '2024-12-31' -- Do 2024-12-31
|
|
);
|
|
END;
|
|
/
|
|
|
|
-- Eksport tylko najnowszych danych (ostatnie 90 dni)
|
|
BEGIN
|
|
CT_MRDS.FILE_MANAGER.EXPORT_TABLE_DATA_BY_DATE(
|
|
pSchemaName => 'OU_TOP',
|
|
pTableName => 'AGGREGATED_ALLOTMENT',
|
|
pKeyColumnName => 'ALLOTMENT_DATE',
|
|
pBucketName => 'mrds_history_poc',
|
|
pFolderName => 'TOP/AGGREGATED_ALLOTMENT',
|
|
pMinDate => SYSDATE - 90 -- Ostatnie 90 dni
|
|
);
|
|
END;
|
|
/
|
|
```
|
|
|
|
### Krok 4: Utworzenie kopii bezpieczeństwa (legacy table)
|
|
|
|
```sql
|
|
-- Zmiana nazwy oryginalnej tabeli na legacy
|
|
ALTER TABLE OU_TOP.AGGREGATED_ALLOTMENT
|
|
RENAME TO AGGREGATED_ALLOTMENT_LEGACY;
|
|
|
|
-- Weryfikacja
|
|
SELECT table_name FROM user_tables
|
|
WHERE table_name LIKE '%AGGREGATED_ALLOTMENT%';
|
|
```
|
|
|
|
### Krok 5: Utworzenie widoku kompatybilności
|
|
|
|
```sql
|
|
-- Nadanie uprawnień do external table ODS
|
|
GRANT SELECT ON ODS.TOP_AGGREGATED_ALLOTMENT_ODS TO OU_TOP;
|
|
|
|
-- Utworzenie widoku z oryginalną nazwą tabeli
|
|
CREATE OR REPLACE VIEW OU_TOP.AGGREGATED_ALLOTMENT AS
|
|
SELECT * FROM ODS.TOP_AGGREGATED_ALLOTMENT_ODS;
|
|
|
|
-- Weryfikacja dostępu
|
|
SELECT COUNT(*) FROM OU_TOP.AGGREGATED_ALLOTMENT;
|
|
```
|
|
|
|
## Parametry procedur
|
|
|
|
### CREATE_EXTERNAL_TABLE
|
|
|
|
```sql
|
|
PROCEDURE CREATE_EXTERNAL_TABLE (
|
|
pTableName IN VARCHAR2, -- Nazwa external table do utworzenia
|
|
pTemplateTableName IN VARCHAR2, -- Template table definiujący strukturę
|
|
pPrefix IN VARCHAR2, -- Ścieżka w Oracle Cloud Storage
|
|
pBucketUri IN VARCHAR2, -- URI bucket'a (domyślnie ENV_MANAGER.gvInboxBucketUri)
|
|
pFileName IN VARCHAR2, -- Nazwa pliku (opcjonalne)
|
|
pDelimiter IN VARCHAR2 -- Separator (domyślnie ',')
|
|
);
|
|
```
|
|
|
|
### EXPORT_TABLE_DATA_BY_DATE
|
|
|
|
```sql
|
|
PROCEDURE EXPORT_TABLE_DATA_BY_DATE (
|
|
pSchemaName IN VARCHAR2, -- Schema zawierający tabelę źródłową
|
|
pTableName IN VARCHAR2, -- Nazwa tabeli do eksportu
|
|
pKeyColumnName IN VARCHAR2, -- Kolumna do filtrowania dat
|
|
pBucketName IN VARCHAR2, -- Nazwa bucket'a Oracle Cloud Storage
|
|
pFolderName IN VARCHAR2, -- Ścieżka folderu w bucket'ie
|
|
pMinDate IN DATE, -- Data minimalna (domyślnie DATE '1900-01-01')
|
|
pMaxDate IN DATE, -- Data maksymalna (domyślnie SYSDATE)
|
|
pNamespace IN VARCHAR2, -- OCI namespace (domyślnie ENV_MANAGER.gvNameSpace)
|
|
pRegion IN VARCHAR2, -- OCI region (domyślnie ENV_MANAGER.gvRegion)
|
|
pCredentialName IN VARCHAR2 -- OCI credentials (domyślnie ENV_MANAGER.gvCredentialName)
|
|
);
|
|
```
|
|
|
|
## Struktura przechowywania w chmurze
|
|
|
|
```
|
|
Oracle Cloud Storage Bucket
|
|
├── INBOX/
|
|
│ └── TOP/
|
|
│ └── AGGREGATED_ALLOTMENT/
|
|
│ └── pliki_CSV_lub_PARQUET
|
|
├── ODS/
|
|
│ └── TOP/
|
|
│ └── AGGREGATED_ALLOTMENT/
|
|
│ └── pliki_CSV_lub_PARQUET
|
|
└── TOP/
|
|
└── AGGREGATED_ALLOTMENT/
|
|
├── 2024_01/
|
|
├── 2024_02/
|
|
└── ...
|
|
└── pliki_PARQUET_partycjonowane_miesięcznie
|
|
```
|
|
|
|
## Weryfikacja i testowanie
|
|
|
|
### Sprawdzenie utworzonych external tables
|
|
|
|
```sql
|
|
-- Lista utworzonych external tables
|
|
SELECT table_name, table_type
|
|
FROM user_tables
|
|
WHERE table_name LIKE '%AGGREGATED_ALLOTMENT%'
|
|
ORDER BY table_name;
|
|
|
|
-- Sprawdzenie external locations
|
|
SELECT table_name, location
|
|
FROM user_external_locations
|
|
WHERE table_name LIKE '%AGGREGATED_ALLOTMENT%'
|
|
ORDER BY table_name;
|
|
```
|
|
|
|
### Test dostępu do danych
|
|
|
|
```sql
|
|
-- Test external table INBOX (może być pusta)
|
|
SELECT COUNT(*) FROM ODS.TOP_AGGREGATED_ALLOTMENT_INBOX;
|
|
|
|
-- Test external table ODS (może być pusta do czasu załadowania danych)
|
|
SELECT COUNT(*) FROM ODS.TOP_AGGREGATED_ALLOTMENT_ODS;
|
|
|
|
-- Test external table ARCHIVE (powinna zawierać wyeksportowane dane)
|
|
SELECT COUNT(*) FROM ODS.TOP_AGGREGATED_ALLOTMENT_ARCHIVE;
|
|
|
|
-- Test widoku kompatybilności
|
|
SELECT COUNT(*) FROM OU_TOP.AGGREGATED_ALLOTMENT;
|
|
```
|
|
|
|
### Sprawdzenie wyeksportowanych plików
|
|
|
|
```sql
|
|
-- Lista plików w bucket'ie archive
|
|
SELECT object_name, size_in_bytes, time_created
|
|
FROM DBMS_CLOUD.LIST_OBJECTS(
|
|
credential_name => CT_MRDS.ENV_MANAGER.gvCredentialName,
|
|
location_uri => CT_MRDS.ENV_MANAGER.gvArchiveBucketUri,
|
|
prefix => 'TOP/AGGREGATED_ALLOTMENT'
|
|
)
|
|
ORDER BY time_created DESC;
|
|
```
|
|
|
|
## Konwencje nazewnictwa
|
|
|
|
- **Template tables:** `CT_ET_TEMPLATES.TOP_AGGREGATED_ALLOTMENT`
|
|
- **External tables:**
|
|
- `TOP_AGGREGATED_ALLOTMENT_INBOX` (pliki przychodzące)
|
|
- `TOP_AGGREGATED_ALLOTMENT_ODS` (dane operacyjne)
|
|
- `TOP_AGGREGATED_ALLOTMENT_ARCHIVE` (dane historyczne)
|
|
- **Legacy table:** `AGGREGATED_ALLOTMENT_LEGACY`
|
|
- **Widok kompatybilności:** `AGGREGATED_ALLOTMENT` (oryginalna nazwa)
|
|
|
|
## Rozwiązywanie problemów
|
|
|
|
### Problem: External table nie może znaleźć plików (ORA-29913)
|
|
|
|
```sql
|
|
-- Sprawdź lokalizację external table
|
|
SELECT table_name, location
|
|
FROM user_external_locations
|
|
WHERE table_name = 'TOP_AGGREGATED_ALLOTMENT_ARCHIVE';
|
|
|
|
-- Sprawdź czy pliki istnieją w bucket'ie
|
|
SELECT object_name
|
|
FROM DBMS_CLOUD.LIST_OBJECTS(
|
|
credential_name => CT_MRDS.ENV_MANAGER.gvCredentialName,
|
|
location_uri => CT_MRDS.ENV_MANAGER.gvArchiveBucketUri,
|
|
prefix => 'TOP/AGGREGATED_ALLOTMENT'
|
|
);
|
|
```
|
|
|
|
### Problem: Brak uprawnień
|
|
|
|
```sql
|
|
-- Nadaj uprawnienia SELECT na external tables
|
|
GRANT SELECT ON ODS.TOP_AGGREGATED_ALLOTMENT_INBOX TO OU_TOP;
|
|
GRANT SELECT ON ODS.TOP_AGGREGATED_ALLOTMENT_ODS TO OU_TOP;
|
|
GRANT SELECT ON ODS.TOP_AGGREGATED_ALLOTMENT_ARCHIVE TO OU_TOP;
|
|
|
|
-- Sprawdź uprawnienia
|
|
SELECT grantee, privilege, table_name
|
|
FROM user_tab_privs
|
|
WHERE table_name LIKE '%AGGREGATED_ALLOTMENT%';
|
|
```
|
|
|
|
## Kompletny skrypt setup
|
|
|
|
```sql
|
|
-- ============================================================================
|
|
-- AGGREGATED_ALLOTMENT External Tables Setup - Kompletny skrypt
|
|
-- ============================================================================
|
|
|
|
-- Krok 1: Sprawdź i utwórz template table
|
|
SELECT 'Sprawdzanie template table...' AS status FROM dual;
|
|
|
|
SELECT table_name FROM all_tables
|
|
WHERE owner = 'CT_ET_TEMPLATES' AND table_name = 'TOP_AGGREGATED_ALLOTMENT';
|
|
|
|
-- Jeśli template nie istnieje, utwórz go:
|
|
-- CREATE TABLE CT_ET_TEMPLATES.TOP_AGGREGATED_ALLOTMENT
|
|
-- AS SELECT * FROM OU_TOP.AGGREGATED_ALLOTMENT WHERE 1=2;
|
|
|
|
-- Krok 2: Tworzenie external tables (METODA ZALECANA)
|
|
SELECT 'Tworzenie external tables...' AS status FROM dual;
|
|
|
|
BEGIN
|
|
ODS.FILE_MANAGER_ODS.CREATE_EXTERNAL_TABLE(
|
|
pTableName => 'TOP_AGGREGATED_ALLOTMENT_INBOX',
|
|
pTemplateTableName => 'CT_ET_TEMPLATES.TOP_AGGREGATED_ALLOTMENT',
|
|
pPrefix => 'INBOX/TOP/AGGREGATED_ALLOTMENT',
|
|
pBucketUri => CT_MRDS.ENV_MANAGER.gvInboxBucketUri
|
|
);
|
|
DBMS_OUTPUT.PUT_LINE('✓ Created INBOX external table');
|
|
END;
|
|
/
|
|
|
|
BEGIN
|
|
ODS.FILE_MANAGER_ODS.CREATE_EXTERNAL_TABLE(
|
|
pTableName => 'TOP_AGGREGATED_ALLOTMENT_ODS',
|
|
pTemplateTableName => 'CT_ET_TEMPLATES.TOP_AGGREGATED_ALLOTMENT',
|
|
pPrefix => 'ODS/TOP/AGGREGATED_ALLOTMENT',
|
|
pBucketUri => CT_MRDS.ENV_MANAGER.gvDataBucketUri
|
|
);
|
|
DBMS_OUTPUT.PUT_LINE('✓ Created ODS external table');
|
|
END;
|
|
/
|
|
|
|
BEGIN
|
|
ODS.FILE_MANAGER_ODS.CREATE_EXTERNAL_TABLE(
|
|
pTableName => 'TOP_AGGREGATED_ALLOTMENT_ARCHIVE',
|
|
pTemplateTableName => 'CT_ET_TEMPLATES.TOP_AGGREGATED_ALLOTMENT',
|
|
pPrefix => 'TOP/AGGREGATED_ALLOTMENT',
|
|
pBucketUri => CT_MRDS.ENV_MANAGER.gvArchiveBucketUri
|
|
);
|
|
DBMS_OUTPUT.PUT_LINE('✓ Created ARCHIVE external table');
|
|
END;
|
|
/
|
|
|
|
-- Krok 3: Eksport istniejących danych
|
|
SELECT 'Eksportowanie danych...' AS status FROM dual;
|
|
|
|
BEGIN
|
|
CT_MRDS.FILE_MANAGER.EXPORT_TABLE_DATA_BY_DATE(
|
|
pSchemaName => 'OU_TOP',
|
|
pTableName => 'AGGREGATED_ALLOTMENT',
|
|
pKeyColumnName => 'ALLOTMENT_DATE',
|
|
pBucketName => 'mrds_history_poc',
|
|
pFolderName => 'TOP/AGGREGATED_ALLOTMENT'
|
|
);
|
|
DBMS_OUTPUT.PUT_LINE('✓ Export completed');
|
|
END;
|
|
/
|
|
|
|
-- Krok 4: Backup oryginalnej tabeli
|
|
SELECT 'Tworzenie kopii bezpieczeństwa...' AS status FROM dual;
|
|
-- ALTER TABLE OU_TOP.AGGREGATED_ALLOTMENT RENAME TO AGGREGATED_ALLOTMENT_LEGACY;
|
|
|
|
-- Krok 5: Utworzenie widoku kompatybilności
|
|
SELECT 'Tworzenie widoku kompatybilności...' AS status FROM dual;
|
|
|
|
GRANT SELECT ON ODS.TOP_AGGREGATED_ALLOTMENT_ODS TO OU_TOP;
|
|
|
|
-- CREATE OR REPLACE VIEW OU_TOP.AGGREGATED_ALLOTMENT AS
|
|
-- SELECT * FROM ODS.TOP_AGGREGATED_ALLOTMENT_ODS;
|
|
|
|
-- Weryfikacja
|
|
SELECT 'Weryfikacja setup...' AS status FROM dual;
|
|
|
|
SELECT table_name, table_type
|
|
FROM user_tables
|
|
WHERE table_name LIKE '%AGGREGATED_ALLOTMENT%'
|
|
ORDER BY table_name;
|
|
|
|
SELECT 'Setup zakończony pomyślnie!' AS status FROM dual;
|
|
```
|
|
|
|
Ten kompletny przykład pokazuje wszystkie kroki potrzebne do migracji tabeli `AGGREGATED_ALLOTMENT` do systemu FILE PROCESSOR z trzema external tables dla różnych typów przechowywania danych.
|