Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Как лучше закачать тестовый файл вида...  [new]
Hel975
Member

Откуда: Москва
Сообщений: 1053
Нужно в базу закачивать текстовый файл вида:

*RECORD*
*FIELD* NO
1
*FIELD* TI
...
*FIELD* TX
...
*RECORD*
*FIELD* NO
2
*FIELD* TI
...
*FIELD* TX
...
*THEEND*

Как лучше процесс организовать? Что использовать bcp, SSIS или придется программу загрузки писать?
1 окт 11, 13:01    [11367777]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше закачать тестовый файл вида...  [new]
hpv
Member

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

Я думаю, лучше переформатировать в XML и не париться.
1 окт 11, 15:19    [11367969]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше закачать тестовый файл вида...  [new]
Hel975
Member

Откуда: Москва
Сообщений: 1053
hpv
Hel975,

Я думаю, лучше переформатировать в XML и не париться.


Как переформатировать? Нам так приходят данные от поставщиков.
1 окт 11, 17:51    [11368318]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше закачать тестовый файл вида...  [new]
hpv
Member

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

Как нить так.

Какие размеры файлов? Структура фиксированная?

DECLARE @A VARCHAR(MAX)

SET @A =
'
*RECORD*
*FIELD* NO
1
*FIELD* TI
abc
*RECORD*
*FIELD* NO
2
*FIELD* TI
efg
*THEEND*
'
set @A = replace(@A, char(13) + char(10), '')

set @A =
replace(
'<ROOT>' +
replace(

replace(@A, '*RECORD*', '</TI></RECORD><RECORD>')

,'*THEEND*', '</TI></RECORD></ROOT>')
,'<ROOT></TI></RECORD>', '<ROOT>')



;with cte as
(
select '*FIELD* NO'as SRC, '<NO>'as DST
union all
select '*FIELD* TI'as SRC, '</NO><TI>'as DST
)
SELECT @A = replace(@A, c.SRC, c.DST) 
from cte as c

select cast(@A as xml)
1 окт 11, 21:11    [11368806]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше закачать тестовый файл вида...  [new]
Edkonst2008
Member

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

Все бы ничего, только человек не указал версию сервера, и если он (сервер) ниже 2005, то Ваш код работать не будет...
1 окт 11, 21:31    [11368832]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше закачать тестовый файл вида...  [new]
Hel975
Member

Откуда: Москва
Сообщений: 1053
hpv,

Размер файла 154 mb. В дальнейшем будет больше. Думаете при таком размере все нормально будет?
1 окт 11, 21:32    [11368835]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше закачать тестовый файл вида...  [new]
Hel975
Member

Откуда: Москва
Сообщений: 1053
Edkonst2008
hpv,

Все бы ничего, только человек не указал версию сервера, и если он (сервер) ниже 2005, то Ваш код работать не будет...


версия 2008, код работать будет. тут другой возникает вопрос, придется в таблицу записывать файл сперва.
1 окт 11, 21:35    [11368841]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше закачать тестовый файл вида...  [new]
hpv
Member

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

Да придется залить.
Можно быстро залить операцией openrowset + опция bulk + опция SINGLE_CLOB.

Если сервак норм, то парсинг должен занимать не оч много времени.
1 окт 11, 21:45    [11368852]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше закачать тестовый файл вида...  [new]
Hel975
Member

Откуда: Москва
Сообщений: 1053
hpv
Hel975,

Да придется залить.
Можно быстро залить операцией openrowset + опция bulk + опция SINGLE_CLOB.

Если сервак норм, то парсинг должен занимать не оч много времени.


Спасибо, попробую так сделать.
1 окт 11, 22:03    [11368874]     Ответить | Цитировать Сообщить модератору
 Re: Как лучше закачать тестовый файл вида...  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Пока все гуру отдыхают на выходных, горе-программисты предлагают насиловать сервер парсингом. Зашибись.

Закачайте обычным BCP с использованием файла форматирования, Указывая соответствующие терминаторы:
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="*RECORD*\r\n*FIELD* NO\r\n"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="*\r\nFIELD* TI\r\n"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="*\r\nFIELD* TX\r\n"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="X" xsi:type="SQLVARCHAR" NULLABLE="YES"/>
  <COLUMN SOURCE="2" NAME="ColNO" xsi:type="SQLINT"/>
  <COLUMN SOURCE="3" NAME="ColTI" xsi:type="SQLVARCHAR"/>
  <COLUMN SOURCE="4" NAME="ColTX" xsi:type="SQLVARCHAR"/>
 </ROW>
</BCPFORMAT>
Только укажите правильно (\r\n или \n) и предварительно
  • отрезать *THEEND* в конце файла {$file = gc "From.txt"; $file[0..($file.length-1)] | out-file "To.txt"}
  • или указать число допустимых ошибок и игнорировать ошибку с *THEEND* {-m max_errors}
  • или дописать в конец файла фиктивный кусок строки {Data.txt + End.txt > Load.txt} и затем удалить её после импорта

    Башкой думайте иногда, Блин.
  • 2 окт 11, 16:29    [11369916]     Ответить | Цитировать Сообщить модератору
     Re: Как лучше закачать тестовый файл вида...  [new]
    Hel975
    Member

    Откуда: Москва
    Сообщений: 1053
    Mnior,

    Столько пафоса и много слов, а у самого xsi:type написан неправильно. К тому же получаю ошибку: The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.
    3 окт 11, 11:03    [11372289]     Ответить | Цитировать Сообщить модератору
     Re: Как лучше закачать тестовый файл вида...  [new]
    Hel975
    Member

    Откуда: Москва
    Сообщений: 1053
    Удалось подправить файл форматирования от Mnior и избавиться от ошибки. Но теперь оказалось, что не все поля встречаются всегда. Если нет записей, то поле не добавляется. Можно при этом пользовать bcp? Получаю ошибку Bulk load data conversion error (truncation) for row 1, column 4 (CN).

    Т.е. файл такого вида получается:

    RECORD*
    *FIELD* NO
    1
    *FIELD* TI
    ...
    *FIELD* TX
    ...
    *RECORD*
    *FIELD* NO
    2
    *FIELD* TI
    ...
    *FIELD* TX
    ...
    *FIELD* CN
    ...
    *THEEND*
    3 окт 11, 11:40    [11372572]     Ответить | Цитировать Сообщить модератору
     Re: Как лучше закачать тестовый файл вида...  [new]
    Aleksey V.P.
    Member

    Откуда: Москва
    Сообщений: 575
    Hel975
    Удалось подправить файл форматирования от Mnior и избавиться от ошибки. Но теперь оказалось, что не все поля встречаются всегда. Если нет записей, то поле не добавляется. Можно при этом пользовать bcp? Получаю ошибку Bulk load data conversion error (truncation) for row 1, column 4 (CN).

    Т.е. файл такого вида получается:

    ...


    Действительно, воспользуйся советом про конвертирование в XML.
    3 окт 11, 14:09    [11373989]     Ответить | Цитировать Сообщить модератору
     Re: Как лучше закачать тестовый файл вида...  [new]
    Mnior
    Member

    Откуда: Кишинёв
    Сообщений: 6724
    hpv
    Hel975
    154 mb
    Если сервак норм, то парсинг должен занимать не оч много времени.
    Только сейчас заметил. Трололо явное.

    Hel975
    Mnior,
    Столько пафоса и много слов, а у самого ...

    Удалось подправить файл форматирования от Mnior ...
    Ай спасибо, что поправил себя, а то адекват это редкость. Аж в слезу пробило.
    К слову, под бубунтой править на вскидку файл, это не сервак под рукой иметь.

    Hel975
    оказалось, что не все поля встречаются всегда
    Мда, засада.
    Даже спредлогать скрипт конвертора на PS не буду, ибо займёт минуты. Пишите консольную прогу на .Net (строк 20-50).
    Можете конечно на PS прогонять, а потом перенести.
    3 окт 11, 18:57    [11377012]     Ответить | Цитировать Сообщить модератору
     Re: Как лучше закачать тестовый файл вида...  [new]
    Mind
    Member

    Откуда: Лучший город на Земле
    Сообщений: 2322
    Hel975,

    Я чет не пойму, а SSIS то чем не устроил? Там конечно придется написать немного кода, но не думаю что с этим могут возникнуть проблемы.
    4 окт 11, 00:17    [11378299]     Ответить | Цитировать Сообщить модератору
     Re: Как лучше закачать тестовый файл вида...  [new]
    Mnior
    Member

    Откуда: Кишинёв
    Сообщений: 6724
    Mind
    Я чет не пойму, а SSIS то чем не устроил?
    А какой тут SSIS сдался?
    Ну чем он лучше того же PS?
    4 окт 11, 03:07    [11378484]     Ответить | Цитировать Сообщить модератору
     Re: Как лучше закачать тестовый файл вида...  [new]
    hpv
    Member

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

    1) Вариант с bulk загрузкой я отмел сразу т.к. не знал как сформировать правильный файл с форматированием.

    2) Я сразу спросил у ТС насчет размера файла и 150 метров мне показалось не много для "обычного" сервака

    3) Приложение на .NET я тоже отмёл, т.к. это такое же велосипедное решение как и парсинг на стороне сервера

    4) Про PowerShell я почему то забыл))

    Я считаю если алгоритм парсинга не оч. сложный, то его можно делать и средствами сервера (т.е. без использования .NET)

    Пусть ТС распарсит файл и посмотрим сколько у него это времени займет.
    4 окт 11, 09:51    [11378840]     Ответить | Цитировать Сообщить модератору
     Re: Как лучше закачать тестовый файл вида...  [new]
    DeColo®es
    Member

    Откуда: Москва
    Сообщений: 5503
    Блог
    Mnior
    Пишите консольную прогу на .Net (строк 20-50).
    +1
    Зачастую готовые интеграционные решения и прочие PS способные быстрее решить задачу, но поддерживать изменения в условиях, да еще и без снижения производительности проще на "нормальных" языках.
    4 окт 11, 11:21    [11379478]     Ответить | Цитировать Сообщить модератору
     Re: Как лучше закачать тестовый файл вида...  [new]
    Hel975
    Member

    Откуда: Москва
    Сообщений: 1053
    Aleksey V.P.
    Действительно, воспользуйся советом про конвертирование в XML.


    Локальный комп делал почти 5 минут реплейс, загруженного в переменную текста, так что с xml вариант отпал. Пока сделала загрузку в таблицу с разбивкой по строкам bulk insert'ом и потом SUBSTRING и CHARINDEX и апдейт нужного поля. Но на домашнем компе загрузка занимает почти 8 минут...
    4 окт 11, 17:51    [11383669]     Ответить | Цитировать Сообщить модератору
     Re: Как лучше закачать тестовый файл вида...  [new]
    iljy
    Member

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

    ну сказали же уже. Сделайте простейший парсер, который конвертнет ваш файл либо в XML, либо вообще в csv-файл, а потом зальете его через BCP либо XMLBulkLoad. Работать будет со скоростью чтения-записи диска.
    4 окт 11, 18:16    [11383939]     Ответить | Цитировать Сообщить модератору
     Re: Как лучше закачать тестовый файл вида...  [new]
    Mnior
    Member

    Откуда: Кишинёв
    Сообщений: 6724
    Чёрт, забыл замостить с утра.

    hpv
    2) Я сразу спросил у ТС насчет размера файла и 150 метров мне показалось не много для "обычного" сервака
    3) Приложение на .NET я тоже отмёл, т.к. это такое же велосипедное решение как и парсинг на стороне сервера

    Я считаю если алгоритм парсинга не оч. сложный, то его можно делать и средствами сервера (т.е. без использования .NET)
    Для начала сами напишите парачку парсеров для всех вариантов. И пункт 2 и 3 сразу улетучатся.

    И дело совсем не в сложностях алгоритмов, а в средствах и заточенности среды.

    1. SQL не компилит весь код в оптимальный монолитный кусок машинного кода, он компилит отдельно каждое выражение. Да стайтменты аля INSERT/UPDATE/DELETE/MERGE получаются практически идеальны, но остальные TSQL выражение это скриптовый язык, и переход от одного выражения к другому явно не оптимально (это не коворя про побочные события).
    Серверу нужно минимум команд, а не минимум текста кода. Поэтому циклы (TSQL) отбросим сразу, сервер умрёт, да будь он экстра крутой.

    2. CTE конечно можно, но даже здесь он не дотягивает. И дело даже скорее в необходимой детеринированности и особой модели запросов. Поэтому промежуточные остатки вычислений сожрут львиную долю ресурсов.
    Серверу нужно минимум промежуточных выражений - один "прямой" запрос.
    А не выкрутасы со строками, как в данном случае.
    4 окт 11, 19:04    [11384333]     Ответить | Цитировать Сообщить модератору
     Re: Как лучше закачать тестовый файл вида...  [new]
    Mnior
    Member

    Откуда: Кишинёв
    Сообщений: 6724
    Сгенерил (2 минуты) на сервере файлик на 367 метров, лимон записей, почти 35 лимонов строк, скпировал (30 сек). Написал топорный скриптик на PS:
      $FileReader = New-Object -typename System.IO.StreamReader -argumentlist "test.txt";
      $XmlWriter = [System.Xml.XmlWriter]::Create("test.xml");
        $XmlWriter.WriteStartDocument();
          $XmlWriter.WriteStartElement("ROOT");
            $Line = $FileReader.ReadLine();
            while ($Line -eq "*RECORD*")
            {
              $XmlWriter.WriteStartElement("row");
              $Line = $FileReader.ReadLine();
              while ($Line.StartsWith("*FIELD*"))
              {
                $XmlWriter.WriteAttributeString($Line.SubString(8), $FileReader.ReadLine());
                $Line = $FileReader.ReadLine();
              }
              $XmlWriter.WriteEndElement(); # row
            }
        $XmlWriter.WriteEndElement(); # ROOT
        $XmlWriter.WriteEndDocument();
      $XmlWriter.Flush();
      $XmlWriter.Close();
      $FileReader.Close();
      if ($Line -ne "*THEEND*") { Write-Error "*THEEND* not found!"; }
    
    И запустил. Менее чем за 71/2 минут получил XML (235.5 метров).

    Теперь один к одному скопипастить в .Net консольный проект.
    4 окт 11, 21:19    [11384777]     Ответить | Цитировать Сообщить модератору
     Re: Как лучше закачать тестовый файл вида...  [new]
    рижый
    Guest
    select *
    from xmltable('
    for $r in ora:tokenize(substring-before(substring-after(/rss/channel/item[ends-with(guid, "tid=884639")]/description, "*RECORD*"), "*THEEND*"),
                           "\*RECORD\*")
    return <r>{
       for $f in ora:tokenize($r, "*\*FIELD\*")
       let $x := ora:tokenize($f, "<br />")
       where $x[1] != ""
       return element {normalize-space($x[1])} { $x[2] }
    }</r>
    '  passing httpuritype('https://www.sql.ru/forum/actualrss.aspx?id=1').getxml()
       columns no integer, ti varchar(10), tx varchar(10)
    );
    
    NO                     TI         TX         
    ---------------------- ---------- ---------- 
    1                      ...        ...        
    2                      ...        ...        
    
    5 окт 11, 01:50    [11385471]     Ответить | Цитировать Сообщить модератору
     Re: Как лучше закачать тестовый файл вида...  [new]
    Mnior
    Member

    Откуда: Кишинёв
    Сообщений: 6724
    рижый,
    cool story, bro
    5 окт 11, 09:17    [11385722]     Ответить | Цитировать Сообщить модератору
     Re: Как лучше закачать тестовый файл вида...  [new]
    hpv
    Member

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

    Я вроде нигде не писал, что код на T-SQL будет быстрее .NET.
    Т.к. ТС ничего не написал, про приемлемое время загрузки, то я ему предложил вариант парсинга средствами сиквела.

    Но т.к. ТС спросил, как закачать файл на форуме по сиквелу, а не в ветке .NET, то я посчитал, что у него нет достаточных знаний для разработки на .NET (Ему же еще это дело поддерживать).

    Я полностью согласен, что для каждой задачи свой инструмент, но им еще нужно уметь пользоваться.
    5 окт 11, 10:05    [11385950]     Ответить | Цитировать Сообщить модератору
    Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
    Все форумы / Microsoft SQL Server Ответить