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
Последние комментарии