mars/confluence/additions/AGGREGATED_ALLOTMENT_External_Tables_Setup.md

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)

-- 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)

-- 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)

-- 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

-- 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)

-- 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

-- 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

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

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

-- 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

-- 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

-- 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)

-- 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ń

-- 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

-- ============================================================================
-- 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.