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

Откуда:
Сообщений: 12
Всех приветсвую. Я новичок в работе с базами данных, поэтому заранее прошу меня извинить за глупые вопросы. Встала задача проектирования БД и реализации механизма импорта данных.

Суть проблемы заключается в следующем: необходимо импортировать неструктурированные данные из Excel в несколько таблиц на Sql Server.

Импорт будет производится путем запуска приложения на VBA.
Приложение одновременно будет и записывать данные и формировать из уже записанных данных отчёт.

Объем загружаемых данных не больше 10000 строк за 1 запуск приложения. Одновременно работать с базой могут работать до 10 пользователей.

Сейчас я сделал реализацию несколькими способами:

1) На сервере создана таблица для импорта данных с нужными форматами, но без ключей. Проверка форматов происходит на стороне клиентского приложения , далее идет импорт данных в загрузочную таблицу с указанием @@spid пользователя.
Далее клиентское приложение вызывает хранимую процедуру, в которой вызывается транзакция, которая распределяет данные по таблицам, делая проверки на уникальность ключей. После этого процедура стирает данные из загрузочной таблицы, используя как ключ @@spid пользователя.
В этом подходе меня смущает проблема контроля данных в загрузочной таблице, когда одновременно несколько пользователей будут работать.

2) На стороне приложения создается временная таблица , туда происходит импорт данных , а далее приложением вызывается хранимая процедура, которая ссылается в коде на временную таблицу.
В этом подходе не очень нравится, что в коде необходимо формировать запрос на создание временной таблицы.

- Подскажите имеют ли предложенные мною реализации право на жизнь и какие могут быть узкие места в этом случае?
- Прекрасно понимаю, что каждый бизнес-кейс уникален, но все же, как технически лучше реализовывать механизм распределения данных по таблицам?
- Лучше делать это на стороне клиентского приложения или же на стороне сервера?
Буду рад любой помощи.
22 мар 19, 11:16    [21840469]     Ответить | Цитировать Сообщить модератору
 Re: Проектирование механизма импорта данных в БД  [new]
L_argo
Member

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

2-й вариант лучше, ИМХО.
22 мар 19, 13:36    [21840718]     Ответить | Цитировать Сообщить модератору
 Re: Проектирование механизма импорта данных в БД  [new]
aleks222
Member

Откуда:
Сообщений: 923
StarDestroyer89
Суть проблемы заключается в следующем: необходимо импортировать неструктурированные данные из Excel в несколько таблиц на Sql Server.


Открою тебе страшную тайну: "импортировать неструктурированные данные" невозможно.
22 мар 19, 14:15    [21840775]     Ответить | Цитировать Сообщить модератору
 Re: Проектирование механизма импорта данных в БД  [new]
aleks222
Member

Откуда:
Сообщений: 923
L_argo
StarDestroyer89,

2-й вариант лучше, ИМХО.


Это в вас императивный программист не убит. Привет GoTo.

Сервер - лучше.
Хотя бы потому, что если что-то пошло не так - не надо бежать к пользователю - можно осмотреть прямо на сервере.
Более того "проверка форматов" тоже должна быть на сервере. Грузить туда следует "необработанные данные."
22 мар 19, 14:19    [21840787]     Ответить | Цитировать Сообщить модератору
 Re: Проектирование механизма импорта данных в БД  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20491
StarDestroyer89
На стороне приложения создается временная таблица , туда происходит импорт данных , а далее приложением вызывается хранимая процедура, которая ссылается в коде на временную таблицу.
Не понял... временная таблица на клиенте - это в Экселе, что ли?

StarDestroyer89
В этом подходе меня смущает проблема контроля данных в загрузочной таблице, когда одновременно несколько пользователей будут работать.
А в чём суть проблемы? Что Вы собираетесь там контролировать?

StarDestroyer89
как технически лучше реализовывать механизм распределения данных по таблицам?
Работу с неструктурированными или денормализованными исходниками по приведению их во вменяемый формат можно оставить клиенту. А вот работу с данными лучше поручать тому, кто предназначен для работы с данными - т.е. серверу БД.

Т.е. клиент - получает сырые данные, первично "причёсывает" их, получая плоскую денормализованную таблицу с выверенными форматами и словарными значениями, и отдаёт серверу. Сервер - проводит окончательный контроль, формализует словарные данные, после чего раскладывает данные по таблицам с сохранением ссылочной целостности. Данные, не прошедшие контроля, возвращаются клиенту. Который проводит дополнительную обработку (возможно, даже ручную) по "причёсыванию" данных, и либо снова отправляет их серверу, либо присоединяет к следующему пакету.

StarDestroyer89
импорт данных в загрузочную таблицу с указанием @@spid пользователя.
Соединение может и порваться... что, начинать всё сначала?
22 мар 19, 14:22    [21840796]     Ответить | Цитировать Сообщить модератору
 Re: Проектирование механизма импорта данных в БД  [new]
uaggster
Member

Откуда:
Сообщений: 811
StarDestroyer89
2) На стороне приложения создается временная таблица , туда происходит импорт данных , а далее приложением вызывается хранимая процедура, которая ссылается в коде на временную таблицу.
В этом подходе не очень нравится, что в коде необходимо формировать запрос на создание временной таблицы.

В зависимости от версии сервера можно и более продвинуто извратиться.
Например, если у вас 2016SP2+ или 2017 сервер, то можно создать в базе memory-optimized table co со стабильностью SCHEMA_ONLY.
А потом взвести на ней безопасность на уровне строк, соответственно чтобы каждый пользователь оперировал своим куском данных.

Ну и дальше - всё, как с темповой таблицей. Но создаешь один раз, и работает быстро.
В MSDN есть пример.
22 мар 19, 15:52    [21840965]     Ответить | Цитировать Сообщить модератору
 Re: Проектирование механизма импорта данных в БД  [new]
StarDestroyer89
Member

Откуда:
Сообщений: 12
Akina
StarDestroyer89
На стороне приложения создается временная таблица , туда происходит импорт данных , а далее приложением вызывается хранимая процедура, которая ссылается в коде на временную таблицу.
Не понял... временная таблица на клиенте - это в Экселе, что ли?

StarDestroyer89
В этом подходе меня смущает проблема контроля данных в загрузочной таблице, когда одновременно несколько пользователей будут работать.
А в чём суть проблемы? Что Вы собираетесь там контролировать?

StarDestroyer89
как технически лучше реализовывать механизм распределения данных по таблицам?
Работу с неструктурированными или денормализованными исходниками по приведению их во вменяемый формат можно оставить клиенту. А вот работу с данными лучше поручать тому, кто предназначен для работы с данными - т.е. серверу БД.

Т.е. клиент - получает сырые данные, первично "причёсывает" их, получая плоскую денормализованную таблицу с выверенными форматами и словарными значениями, и отдаёт серверу. Сервер - проводит окончательный контроль, формализует словарные данные, после чего раскладывает данные по таблицам с сохранением ссылочной целостности. Данные, не прошедшие контроля, возвращаются клиенту. Который проводит дополнительную обработку (возможно, даже ручную) по "причёсыванию" данных, и либо снова отправляет их серверу, либо присоединяет к следующему пакету.

StarDestroyer89
импорт данных в загрузочную таблицу с указанием @@spid пользователя.
Соединение может и порваться... что, начинать всё сначала?



Я просто неверно выразился: временная таблица конечно же создается на сервере, просто скрипт на ее создание я формирую на клиенте - это не очень нравится, так как код получается длинный(в таблице 30 столбцов).

Если соединение разорвется, то пользователь прокрутит VBA-приложение еще раз - тут ничего страшного не случится.
Но тогда в таблице для импорта "зависнут" данные - этот момент нужно как-то контролировать. Очищать всю таблицу я не могу так в этот момент с ней может работать другой пользователь.
22 мар 19, 16:05    [21840994]     Ответить | Цитировать Сообщить модератору
 Re: Проектирование механизма импорта данных в БД  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54120
StarDestroyer89,

механизм строится на постоянку или на время старта системы?
22 мар 19, 16:14    [21841012]     Ответить | Цитировать Сообщить модератору
 Re: Проектирование механизма импорта данных в БД  [new]
StarDestroyer89
Member

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

процесс импорта будет постоянным .
22 мар 19, 16:35    [21841053]     Ответить | Цитировать Сообщить модератору
 Re: Проектирование механизма импорта данных в БД  [new]
uaggster
Member

Откуда:
Сообщений: 811
StarDestroyer89
Очищать всю таблицу я не могу так в этот момент с ней может работать другой пользователь.

С безопасностью на уровне строк - никаких проблем. Очищай на здоровье.
22 мар 19, 17:02    [21841107]     Ответить | Цитировать Сообщить модератору
 Re: Проектирование механизма импорта данных в БД  [new]
Владислав Колосов
Member

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

Сделайте общую папку, в которую 10 человек будут сбрасывать файлы для загрузки.
Создайте пакет Intergation Services, который будет по расписанию смотреть в эту папку и загружать содержимое файлов на сервер.
Идите в кассу за премией.
22 мар 19, 17:36    [21841174]     Ответить | Цитировать Сообщить модератору
 Re: Проектирование механизма импорта данных в БД  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Akina
StarDestroyer89
импорт данных в загрузочную таблицу с указанием @@spid пользователя.
Соединение может и порваться... что, начинать всё сначала?
Временная таблица тоже умрет если соединение порвется.
Ему же не вручную 10000 строк грузить. Ну сколько это займет, пару секунд?
22 мар 19, 19:39    [21841297]     Ответить | Цитировать Сообщить модератору
 Re: Проектирование механизма импорта данных в БД  [new]
Mind
Member

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

Сделайте общую папку, в которую 10 человек будут сбрасывать файлы для загрузки.
Создайте пакет Intergation Services, который будет по расписанию смотреть в эту папку и загружать содержимое файлов на сервер.
Идите в кассу за премией.
Intergation Services отчет тоже нарисует? Ну и пользователям может не понравиться что нет кнопки - "начать загрузку". Кинул файл в папку и сиди жди пока тебе отчет пришлют?
22 мар 19, 19:43    [21841299]     Ответить | Цитировать Сообщить модератору
 Re: Проектирование механизма импорта данных в БД  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
StarDestroyer89
Я просто неверно выразился: временная таблица конечно же создается на сервере, просто скрипт на ее создание я формирую на клиенте - это не очень нравится, так как код получается длинный(в таблице 30 столбцов).
Длинный код это реальная проблема, голодающие дети в Африке отдыхают.

С временной таблицей самый некрасивый момент это то что её создание и использование оторваны друг от друга. Создание в коде в приложении, а использование в процедуре. Вот кто нибудь после вас захочет поменять процедуру, а там используется временная таблица, которая неизвестно где создается. Эту процедуру даже отладить будет нереально без исходников приложения. Короче, если пойдете этим путем, то пишите в процедуре коментарии и желательно скрипт для создания временной таблицы.

Ну и конечно нужно убедиться что у вас весь процессинг в одном коннекшене происходит, иначе получите "объект не найден".
22 мар 19, 19:53    [21841304]     Ответить | Цитировать Сообщить модератору
 Re: Проектирование механизма импорта данных в БД  [new]
StarDestroyer89
Member

Откуда:
Сообщений: 12
Mind
Владислав Колосов
StarDestroyer89,

Сделайте общую папку, в которую 10 человек будут сбрасывать файлы для загрузки.
Создайте пакет Intergation Services, который будет по расписанию смотреть в эту папку и загружать содержимое файлов на сервер.
Идите в кассу за премией.
Intergation Services отчет тоже нарисует? Ну и пользователям может не понравиться что нет кнопки - "начать загрузку". Кинул файл в папку и сиди жди пока тебе отчет пришлют?


Вы правы в данном случае это не подходит, хотя вариант интересный. Попробую его использовать для другого проекта)
22 мар 19, 23:13    [21841384]     Ответить | Цитировать Сообщить модератору
 Re: Проектирование механизма импорта данных в БД  [new]
StarDestroyer89
Member

Откуда:
Сообщений: 12
Mind
StarDestroyer89
Я просто неверно выразился: временная таблица конечно же создается на сервере, просто скрипт на ее создание я формирую на клиенте - это не очень нравится, так как код получается длинный(в таблице 30 столбцов).
Длинный код это реальная проблема, голодающие дети в Африке отдыхают.

С временной таблицей самый некрасивый момент это то что её создание и использование оторваны друг от друга. Создание в коде в приложении, а использование в процедуре. Вот кто нибудь после вас захочет поменять процедуру, а там используется временная таблица, которая неизвестно где создается. Эту процедуру даже отладить будет нереально без исходников приложения. Короче, если пойдете этим путем, то пишите в процедуре коментарии и желательно скрипт для создания временной таблицы.

Ну и конечно нужно убедиться что у вас весь процессинг в одном коннекшене происходит, иначе получите "объект не найден".


Тоесть лучше остановится на первом варианте с постоянной таблицей , сделав изоляцию на уровне строк, как, например, советовал uaggster ?
22 мар 19, 23:16    [21841389]     Ответить | Цитировать Сообщить модератору
 Re: Проектирование механизма импорта данных в БД  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31331
StarDestroyer89
Сейчас я сделал реализацию несколькими способами:

1) На сервере создана таблица

2) На стороне приложения создается временная таблица

- Подскажите имеют ли предложенные мною реализации право на жизнь и какие могут быть узкие места в этом случае?

Первый вариант какой то костыльный.
Проблема с тьем, что при ошибке данные остаюися, нестрашная, нужно просто придерживаться правила, что данные очищаются до использования, а не после.

Второй вариант - это в принципе классика.
Но действительно, нехорошо, что разделяется управление, создание на клиенте, использование на сервере.

3) Можно ещё использовать появившиеся недавно (с 2008) табличные параметры. Очень хороший вариант, хотя реализован в сиквеле кривовато (нужно делать тип данных).

4) Ещё вариант - клиент кладёт структуру данных в XML, процедура его разбирает. Для ваших мизерных нагрузок очень неплох.
23 мар 19, 09:42    [21841532]     Ответить | Цитировать Сообщить модератору
 Re: Проектирование механизма импорта данных в БД  [new]
aleks222
Member

Откуда:
Сообщений: 923
alexeyvg
3) Можно ещё использовать появившиеся недавно (с 2008) табличные параметры. Очень хороший вариант, хотя реализован в сиквеле кривовато (нужно делать тип данных).

4) Ещё вариант - клиент кладёт структуру данных в XML, процедура его разбирает. Для ваших мизерных нагрузок очень неплох.

Ога.
И еще 100500 преобразований из формата в формат.
Нафига?
Завтра принесут еще "данных", которые в ваш универсальный формат не влазют.
И фсе, приплыли.

Православная загрузка должна состоять из изолированных шагов, которые можно проверять-отлаживать независимо
1. Загрузка данных "как есть" в промежуточную-"временную (не в смысле #)" таблицу.
2. Обработка данных и приведение их к нужному виду.
3. Загрузка в окончательные таблицы.

Строить "универсальную" загрузку - в 101% случаев бессмысленно.
23 мар 19, 14:27    [21841671]     Ответить | Цитировать Сообщить модератору
 Re: Проектирование механизма импорта данных в БД  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31331
aleks222
Православная загрузка должна состоять из изолированных шагов, которые можно проверять-отлаживать независимо
1. Загрузка данных "как есть" в промежуточную-"временную (не в смысле #)" таблицу.
2. Обработка данных и приведение их к нужному виду.
3. Загрузка в окончательные таблицы.
С этим я полностью согласен. Самое правильное.

Но бывают маленькие задачки, когда нужно вставить в базу чуть чуть введённых пользователем (например, из выбранного пользователем файлика) данных, их сохранить/обработать, я прямо сразу показатль на экране.
Причём давнных немного и обработка несложная.
Тогда простенькие решения, с передачей данных одним из указанных способов, ИМХО вполне подходят.
23 мар 19, 21:46    [21841782]     Ответить | Цитировать Сообщить модератору
 Re: Проектирование механизма импорта данных в БД  [new]
PizzaPizza
Member

Откуда:
Сообщений: 350
StarDestroyer89
необходимо импортировать неструктурированные данные из Excel

Объем загружаемых данных не больше 10000 строк за 1 запуск приложения. Одновременно работать с базой могут работать до 10 пользователей.


Меня всегда на этапе формулировки формата и объема данных начинают терзать смутные сомнения.

Я б начал с того, что бы посмотреть, откуда эти 10 пользователей берут по 10000 неструктурированных строк. Может быть эти строки не такие уж неструктурированные и вынимаются из соседней базы или генерятся каким либо софтом или оборудованием соответственно в очень даже структурированном формате.

Не, я конечно допускаю, что есть задачи, когда пользователь ручками набивает 10000 строк, но это очень очень редкая задача имхо.
24 мар 19, 02:16    [21841842]     Ответить | Цитировать Сообщить модератору
 Re: Проектирование механизма импорта данных в БД  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31331
PizzaPizza
Я б начал с того, что бы посмотреть, откуда эти 10 пользователей берут по 10000 неструктурированных строк.
Обычно это эксель файлы, присылаемые контрагентами, часто случайными, или такими, с которыми дела делаются раз в год.
Там с трудом добиваются, что бы они не передавали данные по факсу, или устно по телефону, куда там интегрировать их систему в свою :-)
24 мар 19, 09:05    [21841906]     Ответить | Цитировать Сообщить модератору
 Re: Проектирование механизма импорта данных в БД  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54120
alexeyvg
PizzaPizza
Я б начал с того, что бы посмотреть, откуда эти 10 пользователей берут по 10000 неструктурированных строк.
Обычно это эксель файлы, присылаемые контрагентами, часто случайными, или такими, с которыми дела делаются раз в год.
Там с трудом добиваются, что бы они не передавали данные по факсу, или устно по телефону, куда там интегрировать их систему в свою :-)
бардак автоматизировать невозможно (с)
Не пробовали им сайт сделать, пусть вводят сами?
24 мар 19, 09:14    [21841908]     Ответить | Цитировать Сообщить модератору
 Re: Проектирование механизма импорта данных в БД  [new]
StarDestroyer89
Member

Откуда:
Сообщений: 12
PizzaPizza
StarDestroyer89
необходимо импортировать неструктурированные данные из Excel

Объем загружаемых данных не больше 10000 строк за 1 запуск приложения. Одновременно работать с базой могут работать до 10 пользователей.


Меня всегда на этапе формулировки формата и объема данных начинают терзать смутные сомнения.

Я б начал с того, что бы посмотреть, откуда эти 10 пользователей берут по 10000 неструктурированных строк. Может быть эти строки не такие уж неструктурированные и вынимаются из соседней базы или генерятся каким либо софтом или оборудованием соответственно в очень даже структурированном формате.

Не, я конечно допускаю, что есть задачи, когда пользователь ручками набивает 10000 строк, но это очень очень редкая задача имхо.


Вы все правильно подметили: у данных есть конкретный источник данных. Данные берутся из нескольких транзакций ERP системы SAP.
Потом в Excel по определенной логике преобразовываются и далее запускается импорт на SQL, а далее пользователи получают сложный отчет в Excel.

Я знаю, что решение костыльное - в силу определенных бизнес-процессов и политик безопасности я не могу напрямую работать с таблицами SAP, как и настроить регулярную выгрузку на SQL, чтобы сразу строить отчёт.

Поэтому данные уже имеют определенную структуру и форматы - требуется лишь определенная проверка на бизнес-логику.
24 мар 19, 09:59    [21841913]     Ответить | Цитировать Сообщить модератору
 Re: Проектирование механизма импорта данных в БД  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31331
andreymx
alexeyvg
пропущено...
Обычно это эксель файлы, присылаемые контрагентами, часто случайными, или такими, с которыми дела делаются раз в год.
Там с трудом добиваются, что бы они не передавали данные по факсу, или устно по телефону, куда там интегрировать их систему в свою :-)
бардак автоматизировать невозможно (с)
Не пробовали им сайт сделать, пусть вводят сами?
Угу, ушлые менеджеры нашли заводик, где можно разово взять пиломатериалу для стройки, недорого, а ты им такой "пусть сайтик делают" :-)

Не люблю я эти рассуждения о "бардак автоматизировать".

Это реальный бизнес, это жизнь; как вы дома не окружаете себя качественно спроектированным комплексным решением одного бренда (от фундамента и стен, до рубашек и вилок), причём испольуемое статично от рождения до смерти, так и бизнес работает десятилетиями (да и столетиями тоже), решая возникающие задачи теми методами и средствами, которые на данный момент времени существуют, и которые на данный момент времени кажутся наиболее эффективными.

Понятие "автоматизировать бардак" или "костыли" правильны для одного замкнутого разового решения, действительно, с этим надо бороться, но когда так называют все процессы и активы, это термины неправильные, потому что не бывает полезного и настоящего бизнеса, которые спроектирован "из одного бренда". Если только что то совсем мелкое, одноразовое, типа "кафе-контейнер".

Вот, ТС описал, что есть некая система, большая и сложная, ну и решили в неё не лезть, а сделать для простой задачи выгрузку штатными средствами в Эксель. Вполне разумное решение, конечно, оправданно для определённых ситуаций, в других нужно будет использовать какие то "шины", "коннекторы", лезть напрямую к таблицам и т.д.
24 мар 19, 15:49    [21842139]     Ответить | Цитировать Сообщить модератору
 Re: Проектирование механизма импорта данных в БД  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54120
а есть готовый пример, как из vba (именно из vba) из экселя массово закинуть данные в мсскл в обычную таблицу? или ссылку на такой пример
построчный инсерт я делал, хочется массово
25 мар 19, 09:29    [21842432]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить