Oracle’da Düz Dosyalardan Veri Sorgulama (External Tables)

Harici tablolar (External Tables), Oracle’ın veritabanları dışında düz dosyalarda depolanan verilerin sorgulanmasını sağlayan teknolojisidir. SQL*Loader tarafından yüklenebilen herhangi bir formatta saklanan herhangi bir veriye erişmek için ORACLE_LOADER sürücüsü kullanılır. Harici tablolar üzerinde DML işlemleri gerçekleştirilemez ancak select, join veya sort işlemleri yapılabilir. Ayrıca harici tablolar için view ve synonymler oluşturulabilir. Verilerin hazırlanmasına gerek olmadığı ve paralel olarak sorgulanabildiği için veri ambarlarının ETL sürecinde faydalıdırlar. Sık sorgulanan tablolar için kullanılmamalıdır.

Sorgulanacak verileri içeren dosyaları (Countries1.txt, Countries2.txt) indirin. Ardından Oracle veritabanı makinası üzerinde bir konuma kaydedin.

$mkdir /data_dir

$ cp Counrtries1.txt Countries2.txt /data_dir/

Ve bu klasörü Oracle veritabanına tanımlayalım.

CREATE OR REPLACE DIRECTORY ext_table_data_dir AS '/data_dir';

Ardından CREATE TABLE … ORGANIZATION EXTERNAL ifadesini kullanarak harici tabloyu oluşturalım. Bu ifade, Oracle veritabanına dosyayı nasıl işleyeceğini ve verileri nasıl yükleyeceğini anlatan bir metadata tanımlar.

CREATE TABLE countries_ext_table (
   country_code VARCHAR2(5),
   country_name VARCHAR2(50),
   country_language VARCHAR2(50)
)
ORGANIZATION EXTERNAL (
   TYPE ORACLE_LOADER
   DEFAULT DIRECTORY ext_table_data_dir
   ACCESS PARAMETERS (
      RECORDS DELIMITED BY NEWLINE
      FIELDS TERMINATED BY ','
      MISSING FIELD VALUES ARE NULL
      (
         country_code CHAR(5),
         country_name CHAR(50),
         country_language CHAR(50)
      )
   )
   LOCATION ('Countries1.txt','Countries2.txt')
)
PARALLEL 5
REJECT LIMIT UNLIMITED;

Harici tablo oluşturulduktan sonra artık normal bir tablo gibi sorgulanabilir.

SQL> SELECT * FROM countries_ext_table ORDER BY country_name;

COUNT COUNTRY_NAME                 COUNTRY_LANGUAGE
----- ---------------------------- -----------------------------
ENG   England                      English
FRA   France                       French
GER   Germany                      German
IRE   Ireland                      English
SCO   Scotland                     English
USA   Unites States of America     English
WAL   Wales                        Welsh

7 rows selected.

SQL>

Eğer yüklenecek dosyalar uygun dizine kaydedilmezse, aşağıdaki sonuç görüntülenecektir.

SQL> SELECT * FROM countries_ext_table ORDER BY country_name;
SELECT *
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file Countries1.txt in EXT_TABLE_DATA_DIR not found
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1

SQL>

Sorunsuz çalışıyorsa tablo için viewlar oluşturabiliriz.

CREATE OR REPLACE VIEW english_speaking_countries AS
  SELECT *
  FROM   countries_ext_table
  WHERE  country_language = 'English'
  ORDER BY country_name;

SELECT *
FROM   english_speaking_countries;

COUNT COUNTRY_NAME                 LANGUAGE
----- ---------------------------- ----------------------------
ENG   England                      English
IRE   Ireland                      English
SCO   Scotland                     English
USA   Unites States of America     English

4 rows selected.

SQL>

Dosyaları harici tablo tarafından okunmadan önce işlemek için PREPROCESSOR deyimi kullanılır. Bu deyim özellikle sıkıştırılmış dosyaları okumak için yararlıdır, çünkü bu sıkıştırılmış dosyalar işlenmeden önce PREPROCESSOR deyiminin tanımladığı komut ile açılır ve doğrudan harici tablo işlemine aktarılırlar.

CREATE OR REPLACE DIRECTORY exec_dir AS '/bin';

CREATE TABLE countries_ext (
  country_code      VARCHAR2(5),
  country_name      VARCHAR2(50),
  country_language  VARCHAR2(50)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY ext_tab_data
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    PREPROCESSOR exec_dir:'zcat'
    FIELDS TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
    (
      country_code      CHAR(5),
      country_name      CHAR(50),
      country_language  CHAR(50)
    )
  )
  LOCATION ('Countries1.txt.gz','Countries2.txt.gz')
)
PARALLEL 5
REJECT LIMIT UNLIMITED;

Çalıştırılabilir dosyayı veya komut dosyasını içeren dizine EXECUTE yetkisi verilmelidir.

Komut satırı parametrelerinin belirtilmesi gerekiyorsa, bunun için bir komut dosyası oluşturulabilir ve bu komut dosyası PREPROCESSOR deyimiyle çağırılması gerekir. Örneğin çalıştırılabilir dizinde, “my_unzip.sh” adlı aşağıdaki içeriğe sahip bir çalıştırılabilir dosyamız olduğunu varsayarsak:

!/bin/bash
/bin/gunzip -c $1

PREPROCESSOR deyimini aşağıdaki şekilde tanımlayabiliriz.

PREPROCESSOR exec_dir:'my_unzip.sh'

Tanımlı Harici Tabloları Görme

[DBA|ALL|USER]_EXTERNAL_TABLES viewları, harici tablolar hakkındaki bilgileri görüntüler.

SELECT COUNT(*) FROM dba_external_tables;

  COUNT(*)
----------
       194

1 row selected.

SQL>

Kaynaklar

1. ORACLE-BASE – External Tables : Querying Data From Flat Files in Oracle, URL: https://oracle-base.com/articles/9i/external-tables-9i