Внешние таблицы External Table

добавлено: 18 фев 19
понравилось:0
просмотров: 4016
комментов: 1

теги:

Автор: Myp3_u_K

Чалышев Максим Михайлович
SQL. 5 дней, которые изменят вашу жизнь.
Внешние таблицы – специальный механизм Oracle СУБД с помощью которого можно обращаться в данным , хранящимся в файлах вне базы данных как к обычным таблицам.

Для загрузки данных могут использоваться команды драйвера OracleLoader. В ExternalTable не могут применяться операторы изменения данных (DELETE INSERT UPDATE MERGE).

Но, к таким таблицам вполне могут применяться стандартные запросы SELECT, с использованием групповых операций, агрегатных функций, аналитического SQL.

Все это делает механизм внешних таблиц особенно эффективным для проектов DWH(хранилищ данных) , при формировании ETL(процедур загрузки) для данных.

Теория и практика

Предположим, у нас есть несколько файлов заданного формата, файлы расположены в каталоге temp на диске c:
Необходимо подключить эти файлы как внешние таблицы к экземпляру нашей базы данных.

Название файлов city.csv и man.csv, кодировка UTF8 - ниже представлено содержание этих файлов текстовом виде.

city.csv

1,Москва,10000000
2,Владимир,500000
3,Орел,300000
4,Курск,200000
5,Казань,2000000
7,Котлас,110000
8,Мурманск,400000
9,Ярославль,500000 

man.csv
9152222221,Андрей,Николаев,1,22
9152222222,Максим,Москитов,1,31
9153333333,Олег,Денисов,3,34
9173333334,Алиса,Никифорова,4,31
9173333335,Таня,Иванова,4,31
9213333336,Алексей,Иванов,7,25
9213333331,Андрей,Некрасов,2,27
9213333332,Миша,Рогозин,2,21
9214444444,Алексей,Галкин,1,38

Вы можете создать эти файлы сами с помощью любого текстового редактора. Напоминаю, что кодировка файлов UTF8.
Перед использованием внешних таблиц необходимо создать специальный объект directory указывающий на каталог, где расположены файлы для внешних таблиц

CREATE OR REPLACE DIRECTORY ext_tab_data AS 'c:/temp';

Здесь
• ext_tab_data – название объекта directory
• AS 'c:/temp' – каталог, где расположены файлы для формирования внешних таблиц.
Далее формируем временные таблицы
Создаем таблицу для файла city.csv

CREATE TABLE city_ext (
  CITYCODE      NUMBER,
  CITYNAME      VARCHAR2(50),
  PEOPLES  NUMBER
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY ext_tab_data
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
  )
  LOCATION ('city.csv')
)
PARALLEL 5
REJECT LIMIT UNLIMITED;


Здесь
• city_ext – название таблицы
• TYPE – драйвер загрузки
• DEFAULT DIRECTORY – объект директория, который мы создали
• ACCESS PARAMETERS – параметры загрузки файла , см документацию к LOADER
• LOCATION – название файла на основе которого создается внешняя таблица
Создаем таблицу для файла man .csv
CREATE TABLE man_ext (
  PHONENUM      VARCHAR2(50),
  FIRSTNAME      VARCHAR2(50),
  LASTNAME      VARCHAR2(50),
  CITYCODE      NUMBER,
  YEARSOLD  NUMBER
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY ext_tab_data
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
  )
  LOCATION ('man.csv')
)
PARALLEL 5
REJECT LIMIT UNLIMITED;

Здесь
• city_ext – название таблицы
• TYPE – драйвер загрузки
• DEFAULT DIRECTORY – объект директория, который мы создали
• ACCESS PARAMETERS – параметры загрузки файла, см документацию к LOADER
• LOCATION – название файла на основе которого создается внешняя таблица

Обращаемся к таблицам, которые мы создали с помощью запроса

select * from man_ext inner join city_ext on man_ext.citycode = city_ext.citycode


Используем агрегатную функцию и группировку данных
select cityname, count(man_ext.phonenum)  
from man_ext inner join city_ext on city_ext.citycode = city_ext.citycode 
group by cityname



Соединяем внешнюю таблицу и обычную таблицу в запросе
select * from man inner join city_ext on man.citycode = city_ext.citycode

 

Используем Аналитический SQL
select firstname, lastname, row_number() over (partition by firstname order by firstname) as rn from man_ext select firstname, lastname, row_number() over (partition by firstname order by firstname) as rn from man_ext

 

Важные замечания
• Для внешних таблиц могут создаваться представления и синонимы.
• Кодировка файлов во внешних таблицах должна совпадать с кодировкой базы данных.
• На папки в которых собираются файлы для подготовки внешних таблиц администратор системы должен выдать специальное разрешение на чтение пользователю из, под которого устанавливалась Oracle СУБД.
• Вешние таблицы неэффективны при частом обращении к этим таблицам в высоконагруженных проектах, для таких проектов следует искать другое решение.
Вопросы учеников
Где еще используются на практике внешние таблицы?
Довольно часто внешние таблицы используются при обмене данными между системами
Где можно посмотреть материалы по ACCESS PARAMETERS?
В описании SQLLoader на сайте Oracle.
В разобранном примере используется драйвер SQLLoader можно ли использовать другие драйвера?
Да , например , можно для загрузки данных воспользоваться драйвером DATAPUMP.
Можно ли загрузить данные из внешней таблицы в обычную таблицу?
Да , и на этой возможности основано довольно много ETL.(процедур загрузки) Пример ,
Следующий запрос создаст таблицу cityccode

create table cityccode as
select cityname, count(man_ext.citycode) as ctcode from man_ext inner join city_ext on city_ext.citycode = city_ext.citycode 
group by cityname

Запрос добавляет в эту таблицу данные из внешней таблицы, не забудьте завершить запрос операцией Commit
insert into cityccode
select cityname, count(man_ext.citycode) as ctcode from man_ext inner join city_ext on city_ext.citycode = city_ext.citycode 
group by cityname

Комментарии




Необходимо войти на сайт, чтобы оставлять комментарии