SQL*Loader — мощный инструмент для импорта различных данных из файла в базу данных.
Если надо загрузить что-либо из файла в БД, то этот инструмент, как раз то что вам поможет.
- Возможности SQL*Loader
- Управляющий файл CTL
- Запуск импорта данных
- Опции командной строки
- Данные для загрузки непосредственно в управляющем файле
- Данные с разными разделителями в данных + форматирование даты
Возможности SQL*Loader
- Загрузка данных из разных файлов в одной сессии
- Загрузка данных в разные таблицы в одной сессии
- Указание кодировки файла данных
- Использование SQL функций перед загрузкой
- Генерация уникальных ключей для колонок
- Ограничение вставляемых данных по условию
- Загрузка двоичных данных
- Запись ошибочных данных в файл ошибок
На входе требуется два файла:
1) Файл данных
2) Управляющий файл (.CTL)
Файл описания структуры данных (loader control file), содержит описание типов данных столбцов, способ разделения столбцов, как обрабатывать данные и т.д. Именно в этом файле задаются настройки обработки файла данных. Часть настроек, можно переопределить через командную строку при запуске Oracle SQL*Loader.
Выходные данные:
1) Данные в таблице БД
2) Файл отчета (Log file)
3) Файл с некорректными данными (Bad files) — данные которые не вставились из-за каких-либо ограничений БД
4) Файл с отвергнутыми данными (Discard files) — данные которые не прошли дополнительные условия
Управляющий файл SQL*Loader (CTL)
Управляющий файл — это простой текстовый файл, который можно редактировать в обычном текстовом редакторе. Именно здесь описываются все правила импорта.
Пример файла «myexample.ctl»:
OPTIONS (SKIP=1) LOAD DATA CHARACTERSET UTF8 INFILE 'myexample.csv' BADFILE 'myexample.bad' DISCARDFILE 'myexample.dsc' TRUNCATE INTO TABLE APPS.XX_MYEXAMPLE FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS ( VAL1, VAL2 "rtrim(trim(:VAL2), chr(10)||chr(13))" )
1 — OPTIONS позволяет задать ряд параметров не в командной строке, а непосредственно в файле.
2 — LOAD DATA требуется в начале управляющего файла
3 — CHARACTERSET если требуется указать кодировку.
4 — INFILE определяет файл с исходными данными.
5 — BADFILE определяет файл с некорректными данными.
6 — DISCARDFILE определяет файл с отброшенными данными.
7 — метод загрузки данных в таблицу
| INSERT | Используется по умолчанию. Таблица перед загрузкой не должна содержать данные. Если в таблице есть строки данных, то выполнение загрузки SQL*Loader-ом не будет выполнено |
| APPEND | Позволяет добавлять строки в таблицу таким образом, чтобы они не оказывали воздействия на уже существующие строки данных. |
| REPLACE | Удаляются все имеющиеся в таблице строки, а затем загружаются новые. При удалении строк срабатывают триггеры существующие на таблице. |
| TRUNCATE | Удаляются все имеющиеся в таблице строки. Триггеры не срабатывают. |
8 — INTO TABLE + название таблицы для вставки данных
9 — FIELDS TERMINATED BY определяет разделитель данных в файле данных
10 — OPTIONALLY ENCLOSED BY ‘»‘ определяет, что данные могут содержать символ обрамления
11 — TRAILING NULLCOLS если поле не имеет данных в файле данных, то записать NULL
12-15 — описание столбцов таблицы, с применяемыми SQL функциями
Запуск импорта данныхSQL*Loader
Для запуска загрузки данных через SQL*Loader
sqlldr APPS/APPS control=./myexample.ctl data=./myexample.csv
Помните, что перед запуском скрипта загрузки, таблица уже должна быть создана.
Опции командной строки SQL*Loader
Список параметров можно получить запустив SQL*Loader без указания параметров:
[*** ~]$ sqlldr
SQL*Loader: Release 10.1.0.5.0 - Production on Fri Aug 8 14:34:23 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Usage: SQLLDR keyword=value [,keyword=value,...]
Valid Keywords:
userid -- ORACLE username/password
control -- control file name
log -- log file name
bad -- bad file name
data -- data file name
discard -- discard file name
discardmax -- number of discards to allow (Default all)
skip -- number of logical records to skip (Default 0)
load -- number of logical records to load (Default all)
errors -- number of errors to allow (Default 50)
rows -- number of rows in conventional path bind array or between direct path data saves
(Default: Conventional path 64, Direct path all)
bindsize -- size of conventional path bind array in bytes (Default 256000)
silent -- suppress messages during run (header,feedback,errors,discards,partitions)
direct -- use direct path (Default FALSE)
parfile -- parameter file: name of file that contains parameter specifications
parallel -- do parallel load (Default FALSE)
file -- file to allocate extents from
skip_unusable_indexes -- disallow/allow unusable indexes or index partitions (Default FALSE)
skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable (Default FALSE)
commit_discontinued -- commit loaded rows when load is discontinued (Default FALSE)
readsize -- size of read buffer (Default 1048576)
external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE (Default NOT_USED)
columnarrayrows -- number of rows for direct path column array (Default 5000)
streamsize -- size of direct path stream buffer in bytes (Default 256000)
multithreading -- use multithreading in direct path
resumable -- enable or disable resumable for current session (Default FALSE)
resumable_name -- text string to help identify resumable statement
resumable_timeout -- wait time (in seconds) for RESUMABLE (Default 7200)
date_cache -- size (in entries) of date conversion cache (Default 1000)
PLEASE NOTE: Command-line parameters may be specified either by
position or by keywords. An example of the former case is 'sqlldr
scott/tiger foo'; an example of the latter is 'sqlldr control=foo
userid=scott/tiger'. One may specify parameters by position before
but not after parameters specified by keywords. For example,
'sqlldr scott/tiger control=foo logfile=log' is allowed, but
'sqlldr scott/tiger control=foo log' is not, even though the
position of the parameter 'log' is correct.
[*** ~]$
Данные для загрузки непосредственно в управляющем файле
Пример данных содержащихся в управляющем файле. Начало данных определяет оператор BEGINDATA
Файл XX_MYEXAMPLE.sql
create table APPS.XX_MYEXAMPLE ( val1 number ,val2 varchar2(100) ,val3 number ) / exit;
Файл XX_MYEXAMPLE.ctl
LOAD DATA INFILE * TRUNCATE INTO TABLE APPS.XX_MYEXAMPLE FIELDS TERMINATED BY ';' TRAILING NULLCOLS ( VAL1, VAL2, VAL3 ) BEGINDATA 100;table;12480 100;chair;3800 200;box;500
Файл test.sh
sqlplus apps/apps @XX_MYEXAMPLE.sql sqlldr apps/apps control=XX_MYEXAMPLE.ctl
Выполняем скрипт test.sh и смотрим результат:
SQL> column VAL2 format a10;
SQL> select * from APPS.XX_MYEXAMPLE t
2 /
VAL1 VAL2 VAL3
---------- ---------- ----------
100 table 12480
100 chair 3800
200 box 500
Плюс создался файл лога XX_MYEXAMPLE.log
SQL*Loader: Release 10.1.0.5.0 - Production on Fri Aug 8 15:19:28 2014 Copyright (c) 1982, 2005, Oracle. All rights reserved. Control File: XX_MYEXAMPLE.ctl Data File: XX_MYEXAMPLE.ctl Bad File: XX_MYEXAMPLE.bad Discard File: none specified (Allow all discards) Number to load: ALL Number to skip: 0 Errors allowed: 50 Bind array: 64 rows, maximum of 256000 bytes Continuation: none specified Path used: Conventional Table APPS.XX_MYEXAMPLE, loaded from every logical record. Insert option in effect for this table: TRUNCATE TRAILING NULLCOLS option in effect Column Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- VAL1 FIRST * ; CHARACTER VAL2 NEXT * ; CHARACTER VAL3 NEXT * ; CHARACTER Table APPS.XX_MYEXAMPLE: 3 Rows successfully loaded. 0 Rows not loaded due to data errors. 0 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null. Space allocated for bind array: 49536 bytes(64 rows) Read buffer bytes: 1048576 Total logical records skipped: 0 Total logical records read: 3 Total logical records rejected: 0 Total logical records discarded: 0 Run began on Fri Aug 08 15:19:28 2014 Run ended on Fri Aug 08 15:19:28 2014 Elapsed time was: 00:00:00.15 CPU time was: 00:00:00.01
Данные с разными разделителями в данных + форматирование даты
Файл XX_MYEXAMPLE2.sql
create table APPS.XX_MYEXAMPLE2 ( id number ,item varchar2(100) ,color varchar2(100) ,price number ,creation_date date ) / exit;
Файл XX_MYEXAMPLE2.ctl
Оператор FILLER — позволяет пропускать колонку в данных и не грузить в таблицу.
LOAD DATA INFILE * TRUNCATE INTO TABLE APPS.XX_MYEXAMPLE2 FIELDS TERMINATED BY ',' TRAILING NULLCOLS ( id, item terminated by "$", color terminated by "^", price , weight FILLER, creation_date DATE "dd-mm-yyyy" ) BEGINDATA 1,table$black^12480,12,30-01-2014 2,table$green^12580,12,11-05-2013 3,chair$black^3800,3,11-08-2014
Запускаем импорт и смотрим результат:
SQL> column item format a10;
SQL> column color format a10;
SQL> select * from APPS.XX_MYEXAMPLE2 t
2 /
ID ITEM COLOR PRICE CREATION_DATE
---------- ---------- ---------- ---------- -------------
1 table black 12480 30.01.2014
2 table green 12580 11.05.2013
3 chair black 3800 11.08.2014
Oracle® Database Utilities 11g Release 2: SQL*Loader Concepts
Последние комментарии