Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Цифра 24 в разряде часов при загрузке данных из файла  [new]
alwan
Member

Откуда:
Сообщений: 54
Камрады!

Столкнулся с такой ситуацией. CSV файл с данными содержит даты, причем есть даты вида '2018-05-05 24:00:00.000000', которые при загрузке sqlldr закономерно вызывают ошибку ORA-01850: hour must be between 0 and 23.

Есть ли возможность корректно загрузить такие данные?

Стандартных способов я не нашел. Самый простой вариант - заменить в файле " 24:00:00.000000" на " 23:59:59.000000", но менять данные, хоть и не значительно, - плохо. Заменить на 00:00:00 нельзя, так как тогда должна измениться дата ('2018-05-05 24:00:00.000000' -> '2018-05-06 00:00:00.000000') а это уже не сделаешь простым реплейсом в редакторе.

Файл формируется из БД DB2 (которая допускает такой формат даты) сотрудниками другого отдела. По поверхностной разведке и чтению интернетов - исправить эту ситуацию на стороне DB2 в момент экспорта нельзя (если неправда - поправьте, пожалуйста).

Буду рад любым идеям.
19 июн 18, 14:11    [21503255]     Ответить | Цитировать Сообщить модератору
 Re: Цифра 24 в разряде часов при загрузке данных из файла  [new]
alwan
Member

Откуда:
Сообщений: 54
Пока мне видится только вариант написать скрипт или программку, которая в файле будет изменять для таких значений время на 00:00:00 и добавлять день к дате соответственно.
В такой случае может быть у кого то есть подобные наработки)
19 июн 18, 14:17    [21503288]     Ответить | Цитировать Сообщить модератору
 Re: Цифра 24 в разряде часов при загрузке данных из файла  [new]
AmKad
Member

Откуда:
Сообщений: 5222
alwan,

View с replace поверх external table.
19 июн 18, 14:19    [21503311]     Ответить | Цитировать Сообщить модератору
 Re: Цифра 24 в разряде часов при загрузке данных из файла  [new]
Vladimir Filin
Member

Откуда: Москва
Сообщений: 110
alwan
....написать скрипт или программку, которая в файле будет изменять для таких значений время на 00:00:00 и добавлять день к дате соответственно...

Если не удобно файлы обрабатывать, то можно на PL/Sql-e функцию написать и вызывать про загрузке. Можно создать виртуальную колонку на её основе: сохраните исходные данные с 24:00:00. Если есть ещё другие преобразования, то я бы так делал. ИМХО.
19 июн 18, 14:27    [21503344]     Ответить | Цитировать Сообщить модератору
 Re: Цифра 24 в разряде часов при загрузке данных из файла  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2798
alwan,

лоадер может(мог) вызывать ф-цию
гляньте "кляузу" EXPRESSION

https://docs.oracle.com/cd/A97630_01/server.920/a96652/ch06.htm#1008669

ф-ция вернет нужную дату

.....
stax
19 июн 18, 14:33    [21503363]     Ответить | Цитировать Сообщить модератору
 Re: Цифра 24 в разряде часов при загрузке данных из файла  [new]
-2-
Member

Откуда:
Сообщений: 15330
alwan
менять данные, хоть и не значительно, - плохо
И что же значит 24:00 в источнике, високосную секунду? Тогда должно быть 23:59:60.
19 июн 18, 15:11    [21503585]     Ответить | Цитировать Сообщить модератору
 Re: Цифра 24 в разряде часов при загрузке данных из файла  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54396
2.3.1 Дата и время в DB2

В DB2 для представления даты и времени имеются следующие типы данных:

DATE - значение, состоящее из трех частей (год, месяц, день). Допустимые значения даты - в пределах от 1 января 0001 г. до 31 декабря 9999 г.
TIME - значение, состоящее из трех частей (часы, минуты, секунды).
Допустимые значения для часов - от 0 до 24, для двух других частей - от 0 до 59.
Если часы установлены в 24, то минуты и секунды могут быть только 0.

TIMESTAMP - значение, состоящее из трех частей (год, месяц, день, часы, минуты, секунды, микросекунды). Допустимые значения для микросекунд - от 0 до 999999.

http://khpi-iip.mipk.kharkiv.edu/library/extent/dbms/sql/23.html
19 июн 18, 15:35    [21503716]     Ответить | Цитировать Сообщить модератору
 Re: Цифра 24 в разряде часов при загрузке данных из файла  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18390
create table dropme_ldr_test(f1 varchar2(100), f2 date);

LOAD DATA characterset cl8mswin1251
INFILE *
replace
into table dropme_ldr_test
FIELDS terminated by ";"
( F1 POSITION(1)
, F2 "CASE when :F2 not like '%24:00:00' then to_date(:F2,'yyyy-mm-dd hh24:mi:ss')
       else to_date(REPLACE(:F2,'24:00:00','00:00:00'),'yyyy-mm-dd hh24:mi:ss')+1
	  END"
)
BEGINDATA
First Rule;2018-06-19 11:30:00
Second Rule;2018-06-19 24:00:00

Table DROPME_LDR_TEST:
  2 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.

SQL> select * from dropme_ldr_test;
F1              F2
--------------- --------------------
First Rule      19.06.2018 11:30:00
Second Rule     20.06.2018

SQL> 
19 июн 18, 16:08    [21503824]     Ответить | Цитировать Сообщить модератору
 Re: Цифра 24 в разряде часов при загрузке данных из файла  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18390
И можно чуть посложнее, но потенциально побыстрее
LOAD DATA characterset cl8mswin1251
INFILE *
replace
into table dropme_ldr_test
WHEN x <> '24:00:00' -- грузим регулярные записи
FIELDS terminated by ";"
( x1 filler POSITION(1), x filler POSITION(*+11) -- вырезаем подстроку из поля с датой-временем для проверки условия WHEN
, F1 position(1)
, F2 DATE "yyyy-mm-dd hh24:mi:ss"
)
into table dropme_ldr_test
WHEN x = '24:00:00' -- специальные записи требуют вызова sql function
FIELDS terminated by ";"
( x1 filler POSITION(1), x filler POSITION(*+11) 
, F1 position(1) -- возврат к началу логической записи
, F2 terminated by whitespace  "to_date(:F2,'yyyy-mm-dd') + 1"
)
BEGINDATA
First Rule;2018-06-19 11:30:00
Second Rule;2018-06-19 24:00:00
19 июн 18, 16:55    [21503937]     Ответить | Цитировать Сообщить модератору
 Re: Цифра 24 в разряде часов при загрузке данных из файла  [new]
alwan
Member

Откуда:
Сообщений: 54
AmKad, Vladimir Filin

Не написал одно обстоятельство. Выгрузкой из файла будет заниматься другой отдел и сам процесс изменить я не могу. Могу только, например, "обработать" исходный файл с данными или, как посоветовал Stax:
Stax
гляньте "кляузу" EXPRESSION


Идея, кстати, очень хорошая, я поначалу тоже глянул в ее сторону, но что-то отпугнуло. С другой стороны, в expression можно реализовать практически любую логику.

И... Пока писал решил попробовать. Все получилось, спасибо большое!)
19 июн 18, 17:41    [21504112]     Ответить | Цитировать Сообщить модератору
 Re: Цифра 24 в разряде часов при загрузке данных из файла  [new]
alwan
Member

Откуда:
Сообщений: 54
[quot andrey_anonymous]
 F2 "CASE when :F2 not like '%24:00:00' then to_date(:F2,'yyyy-mm-dd hh24:mi:ss')
       else to_date(REPLACE(:F2,'24:00:00','00:00:00'),'yyyy-mm-dd hh24:mi:ss')+1
	  END"
[src PLSQL]
[/quot]

Прям так же получилось =))) только формат с долями секунд 

[SRC PLSQL]DTTM   "case instr(:DTTM, ' 24:')
	when 0 then to_timestamp(:DTTM,'YYYY.MM.DD HH24:MI:SS.FF6')
	else to_timestamp(replace(:DTTM,' 24:00:00', ' 00:00:00'),'YYYY.MM.DD HH24:MI:SS.FF6')  end")



Всем спасибо! Вопрос закрыт
19 июн 18, 17:47    [21504156]     Ответить | Цитировать Сообщить модератору
 Re: Цифра 24 в разряде часов при загрузке данных из файла  [new]
alwan
Member

Откуда:
Сообщений: 54
Напортачил с тегами для кода и цитат
19 июн 18, 17:48    [21504163]     Ответить | Цитировать Сообщить модератору
 Re: Цифра 24 в разряде часов при загрузке данных из файла  [new]
alwan
Member

Откуда:
Сообщений: 54
andrey_anonymous
И можно чуть посложнее, но потенциально побыстрее
LOAD DATA characterset cl8mswin1251
INFILE *
replace
into table dropme_ldr_test
WHEN x <> '24:00:00' -- грузим регулярные записи
FIELDS terminated by ";"
( x1 filler POSITION(1), x filler POSITION(*+11) -- вырезаем подстроку из поля с датой-временем для проверки условия WHEN
, F1 position(1)
, F2 DATE "yyyy-mm-dd hh24:mi:ss"
)
into table dropme_ldr_test
WHEN x = '24:00:00' -- специальные записи требуют вызова sql function
FIELDS terminated by ";"
( x1 filler POSITION(1), x filler POSITION(*+11) 
, F1 position(1) -- возврат к началу логической записи
, F2 terminated by whitespace  "to_date(:F2,'yyyy-mm-dd') + 1"
)
BEGINDATA
First Rule;2018-06-19 11:30:00
Second Rule;2018-06-19 24:00:00


Интересная конструкция...
Но в моей ситуации она немного неудобна, так как подобное встречается в нескольких таблицах с большим количеством полей, а операция разовая, поэтому +- в скорости не критичен.
Я правильно понимаю, что в этой ситуации файл будет читаться 2 раза? Потенциальное преимущество будет если строк с 24 часами будет много за счет "terminated by whitespace"?
19 июн 18, 18:12    [21504281]     Ответить | Цитировать Сообщить модератору
 Re: Цифра 24 в разряде часов при загрузке данных из файла  [new]
XMLer
Member

Откуда:
Сообщений: 258
alwan,
Не стоит объединять извлечение данных из файла и приведение типов, сначала прочитай все как строки, сохрани, затем применяй поэтапно трансформации.
20 июн 18, 17:27    [21507533]     Ответить | Цитировать Сообщить модератору
 Re: Цифра 24 в разряде часов при загрузке данных из файла  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2798
XMLer,

зачем заливать в промежуточную, если есть возможность сразу штатно обработать входной лоадером

.....
stax
20 июн 18, 17:33    [21507546]     Ответить | Цитировать Сообщить модератору
 Re: Цифра 24 в разряде часов при загрузке данных из файла  [new]
XMLer
Member

Откуда:
Сообщений: 258
Stax,
например, при возникновении ошибки на одном из этапов, начать с последнего успешного.
20 июн 18, 17:41    [21507580]     Ответить | Цитировать Сообщить модератору
 Re: Цифра 24 в разряде часов при загрузке данных из файла  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2798
XMLer,

я не утверждаю что никогда не надо заливать в промежуточную
в данном случе(и во многих других), имхо, ето излишне

.....
stax
20 июн 18, 17:45    [21507601]     Ответить | Цитировать Сообщить модератору
 Re: Цифра 24 в разряде часов при загрузке данных из файла  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18390
XMLer
alwan,
Не стоит объединять извлечение данных из файла и приведение типов, сначала прочитай все как строки, сохрани, затем применяй поэтапно трансформации.

Если строится постоянно действующий расклюквистый ETL, то рекомендация часто оправдана.
Если речь о разовой/эпизодической загрузке одной-двух таблиц, то овчинка выделки не стоит.
20 июн 18, 20:45    [21507989]     Ответить | Цитировать Сообщить модератору
 Re: Цифра 24 в разряде часов при загрузке данных из файла  [new]
XMLer
Member

Откуда:
Сообщений: 258
andrey_anonymous,
Да, Кэп!
22 июн 18, 15:10    [21513376]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить