«Варп-двигатель» для «среза последних»

10.08.20

Разработка - Запросы

Решение, позволяющее получить данные, аналогичные "срезу последних" на два порядка быстрее.

   Сейчас, наверное, всем известно, что перемещаться быстрее, чем скорость света в вакууме, невозможно. Однако мексиканский физик Мигель Алькубьерре предложил концепцию, с помощью которой теоретически можно построить двигатель, который позволит преодолеть этот барьер. Правда для этого потребуется некая экзотическая материя и количество энергии, по оценкам, эквивалентное массе Юпитера.

   А теперь про «срез последних» регистра сведений 1С и проблемы, с ним связанные. Реализация «среза» в 1С, основанная на стандарте SQL, при росте количества записей начинает заметно тормозить. Мы имеем здесь аналогию со скоростью света - при приближении к определенному количеству записей в регистре, получить результат "среза последних" за приемлемое время уже невозможно.

   Альтернативные варианты реализации, например здесь, позволяют ускорить получение результата примерно на 50%. И эта проблема связана не с 1С, а со стандартом SQL.  Если придерживаться стандарта, эффективно решить задачу уже невозможно. Проблема в том, при росте количества записей замедление увеличивается экспоненциально. И выигрыш в несколько десятков процентов не спасет ситуацию.

   Чтобы проиллюстрировать ситуацию, перейдем к цифрам, полученным на практике:
Есть независимый и непериодический регистр сведений с исторически сложившейся структурой, которую уже нельзя изменить по различным причинам (место на диске, недопустимо большое время реструктуризации):

   Три измерения (именно в таком порядке):
      Товар (Ссылка, ~20 000 различных значений)
      Магазин (Ссылка, ~10 000 различных значений)
      Дата (ДатаВремя)
   Четыре ресурса (три числовых и ссылка) и один реквизит с типом «Строка».

 

   Количество записей в регистре на текущий момент приближается к двум миллиардам (1 784 781 077). При таком количестве записей реализация «среза последних» на MS SQL, даже при наличии всех необходимых индексов исполняется около двух часов на сервере (Intel(R) Xeon(R) CPU E5-2697 v3 , 128 Гб ОЗУ).

   Чтобы кардинально решить проблему, данные регистра были выгружены в СУБД ClickHouse. Выгрузка из базы MS SQL в формат CSV выполнялась с помощью утилиты bcp.exe параллельно в 12 потоков и заняла полтора часа. Загрузка из файлов CSV в СУБД ClickHouse осуществлялась последовательно и потребовала всего 14 минут.
Все операции выполнялись из интерфейса специально разработанной для работы с ClickHouse конфигурации. База данных с этой конфигурацией использовала два соединения - HTTP с ClickHouse и HTTP - с базой 1С, которая соответствует базе SQL
(для получения структуры хранения базы данных, необходимой для формирования запроса  при вызове bcp.exe).
Конфигурация расширяет возможности интеграции с ClickHouse - добавляет пакетные запросы, управляемые временные таблицы (с автоудалением после завершения пакетного запроса), сжатие данных при обмене по HTTP с ClickHouse и другие необходимые возможности.


   Теперь самое интересное: СУБД ClickHouse полностью не поддерживает стандарт SQL, но имеет свои расширения стандарта, которые позволяют решать многие задачи гораздо эффективнее и элегантнее.

   Был создан cледующий запрос для получения среза последних по данным таблицы в ClickHouse:

SELECT
    *
FROM
    work.itz AS itz
    INNER JOIN
    (
    SELECT
        tovar,
        magaz,
      
 maxIf(itz.dat, itz.dat <= '<ДатаСреза>') AS dat
    FROM
        work.itz
    GROUP BY (tovar, magaz)
    HAVING maxIf(itz.dat, itz.dat <= '<ДатаСреза>') <> '0000-00-00 00:00:00'
    ) AS srez
USING
  (tovar, magaz, dat)

   Здесь work - имя базы, itz - имя таблицы в ClickHouse, наименование остальных полей (tovar, magaz, dat) соответствуют их сокращенной англоязычной транскрипции.  Строка <ДатаСреза> в тексте запроса заменялась конкретной датой перед исполнением запроса (запрос был отправлен через HTTP интерфейс).
   
    В этом запросе ключевым является использование агрегатной функции max с комбинатором If. Комбинаторы как раз и являются специфическим расширением стандарта SQL в ClickHouse, и предоставляют возможности, отсутствующие в стандарте SQL. О комбинаторах в ClickHouse можно прочесть в открытой документации: https://clickhouse.tech/docs/ru/sql-reference/aggregate-functions/combinators/

   И на том же наборе данных, на котором «срез последних» в MS SQL рассчитывался 2 часа, результат приведенного запроса к CУБД ClickHouse был получен через 40 секунд (около 10 миллионов записей).

   Результат был получен быстрее не на десятки процентов, а на два порядка ! При этом СУБД ClickHouse установлена на одном компьютере с такими же характеристиками, что и компьютер для базы MS SQL (Xeon 14 ядер 28 потоков, 128 Гб ОЗУ). Кстати, сравнение сложности «многоэтажного запроса» в MS SQL и запроса из нескольких строк к СУБД ClickHouse также весьма красноречиво выглядит.

   Вот это и есть «варп-двигатель» для «среза последних». Самая быстрая в мире, по независимым оценкам, СУБД ClickHouse и грамотное использование её возможностей для нестандартного решения типовой задачи. А если учесть, что производительность СУБД ClickHouse практически линейно зависит от количества ядер и компьютеров, на которых она развернута, то "срез последних" для больших данных теперь не является нерешаемой проблемой.


   "ClickHouse не тормозит !" - лозунг, которым руководствуются разработчики СУБД из компании Яндекс, полностью оправдывается при решении частной задачи - получении данных "среза последних".

Быстрый срез последних ClickHouse большие данные миллиарды записей интеграция HTTP bcp.exe SQL

См. также

SALE! 20%

Infostart Toolkit: Инструменты разработчика 1С 8.3 на управляемых формах

Инструментарий разработчика Роли и права Запросы СКД Платформа 1С v8.3 Управляемые формы Запросы Система компоновки данных Конфигурации 1cv8 Платные (руб)

Набор инструментов программиста и специалиста 1С для всех конфигураций на управляемых формах. В состав входят инструменты: Консоль запросов, Консоль СКД, Консоль кода, Редактор объекта, Анализ прав доступа, Метаданные, Поиск ссылок, Сравнение объектов, Все функции, Подписки на события и др. Редактор запросов и кода с раскраской и контекстной подсказкой. Доработанный конструктор запросов тонкого клиента. Продукт хорошо оптимизирован и обладает самым широким функционалом среди всех инструментов, представленных на рынке.

13000 10400 руб.

02.09.2020    122178    670    389    

714

Для чего используют конструкцию запроса "ГДЕ ЛОЖЬ" в СКД на примере конфигурации 1С:ERP

Запросы СКД Платформа 1С v8.3 Запросы Система компоновки данных 1С:ERP Управление предприятием 2 Бесплатно (free)

В типовых конфигурациях разработчики компании 1С иногда используют в отчетах, построенных на СКД, такую конструкцию, как "ГДЕ ЛОЖЬ". Такая конструкция говорит о том, что данные в запросе не будут получены совсем. Для чего же нужен тогда запрос?

13.02.2024    5746    KawaNoNeko    23    

23

Набор-объект для СКД по тексту или запросу

Запросы СКД Платформа 1С v8.3 Управляемые формы Конфигурации 1cv8 Абонемент ($m)

Есть список полей в виде текста, или запрос - закидываем в набор СКД.

1 стартмани

31.01.2024    2000    2    Yashazz    0    

29

Запрос 1С copilot

Инструментарий разработчика Запросы Платформа 1С v8.3 Управляемые формы Конфигурации 1cv8 Абонемент ($m)

Пишем на человеческом языке, что нам надо, и получаем текст запроса на языке 1С. Используются большие языковые модели (LLM GPT) от OpenAI или Яндекс на выбор.

5 стартмани

15.01.2024    6286    31    mkalimulin    25    

50

PrintWizard: поддержка представлений ЗУП в конструкторе

Инструментарий разработчика Запросы Платформа 1С v8.3 Бесплатно (free)

Одной из интересных задач, стоящих в процессе разработки, была поддержка механизма представлений в ЗУП. Но не просто возможность исполнения запросов с ними. Основная проблема была в том, чтобы с ними было удобно работать, а именно: создавать, модифицировать и отлаживать. Кратко о том, что в итоге получилось...

14.12.2023    1742    vandalsvq    7    

29

Объектная модель запроса "Схема запроса" 2

Запросы Платформа 1С v8.3 Запросы Конфигурации 1cv8 Бесплатно (free)

Далеко уже не новый тип данных "Схема запроса". Статья о том, как использовать его "попроще". Примеры создания текста запроса с нуля и изменение имеющегося запроса.

06.12.2023    5388    user1923546    26    

43

Начните уже использовать хранилище запросов

HighLoad оптимизация Запросы

Очень немногие из тех, кто занимается поддержкой MS SQL, работают с хранилищем запросов. А ведь хранилище запросов – это очень удобный, мощный и, главное, бесплатный инструмент, позволяющий быстро найти и локализовать проблему производительности и потребления ресурсов запросами. В статье расскажем о том, как использовать хранилище запросов в MS SQL и какие плюсы и минусы у него есть.

11.10.2023    16186    skovpin_sa    14    

98
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. bulpi 215 10.08.20 12:37 Сейчас в теме
Не понял :
1)"Есть независимый и непериодический регистр сведений"
А при чем тут срез последних к непериодическому регистру ?

2)Начиная с какого-то релиза, можно хранить срез последних в виде готовой таблицы. И получать почти мгновенно.
fishca; dandykry; dabu-dabu; DrAku1a; nnn123; 7OH; +6 Ответить
2. davdykin 25 10.08.20 12:49 Сейчас в теме
(1)Собственно, ничего не мешает реализовать свой "срез последних", если есть измерение дата.
2. Не совсем понятно, а как хранятся эти таблицы? Ведь если периодичность день - это 365 таблиц в год?
3. hobi 615 10.08.20 13:18 Сейчас в теме
(2) Вот, использовался такой запрос для создания таблицы:
Для колонок подобраны оптимальные кодеки сжатия информации, благодаря этому
таблица занимает мало места на диске. Кроме того, вместо GUID ссылок
использовались коды элементов для идентификации.
Для хранения таблицы ClickHouse автоматически разбивает её в соответствии
с заданным ключом партицирования. В запросе ключом является выражение toYYYYMM(dat),
т.е. разбиение по месяцам. А исполнение SELECT-ов распаралеливает на все доступные ядра
компьютера или, если таблица распределенная, то и на все компьютеры. Сам запрос:
CRE ATE   TABLE work.itz
(
    tovar UInt32   CODEC(T64),
    magaz UInt32   CODEC(DoubleDelta, LZ4),
    dat   DateTime CODEC(DoubleDelta, LZ4),
    kol   Int32    CODEC(DoubleDelta, LZ4),
    proc  Int8     CODEC(T64),
    post  UInt32   CODEC(DoubleDelta, LZ4),
    kolr  Decimal(9, 2) CODEC(DoubleDelta, LZ4),
    autor LowCardinality(String)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(dat)
PRIMARY KEY (tovar, magaz)
ORDER BY (tovar, magaz, dat)
SETTINGS index_granularity = 8192
Показать
4. hobi 615 10.08.20 13:23 Сейчас в теме
(2)
Ведь если периодичность день - это 365 таблиц в год?

Периодичность в приведенном примере не день, а секунда. Потому что тип поля dat в данном случае не Date, а DateTime.
5. hobi 615 10.08.20 13:27 Сейчас в теме
(1)
А при чем тут срез последних к непериодическому регистру ?

В данном случае я говорю о функционале, который выполняет получение данных, аналогичных "срезу последнего" по реквизиту с типом ДатаВремя.

(1)
Начиная с какого-то релиза, можно хранить срез последних в виде готовой таблицы. И получать почти мгновенно.

Поскольку механизм хранения и получения данных, аналогичных "срезу последнего" использует внешний сервер (ClickHouse) с интерфейсом HTTP, то с любого релиза. С HTTP даже на 7.7 можно работать.
6. AlanGreenspan 10.08.20 14:00 Сейчас в теме
Как много неприятностей доставляют тормоза при достижении критического количества записей, знаем не понаслышке. Если с Вашим варп-двигателем система летает так, как Вы рассказываете, это маленькая, но очень полезная революция :)
7. hobi 615 10.08.20 14:18 Сейчас в теме
(6)
Если с Вашим варп-двигателем система летает так, как Вы рассказываете

Действительно летает, двигатель от Яндекса. Я просто немного разобрался в нём и понял, как использовать его преимущества при интеграции с 1С. На скриншоте - размеры таблиц, в частности таблица itz с таким большим количеством записей занимает всего 9.4 Гб места на диске.
Вторая таблица (srp) больше 9 миллиардов записей.
Запрос по выгрузке в файл CSV формата левого соединения "среза последнего" на дату из itz с таблицей srp по полям tovar и magaz занял 51 секунду.
В результате получен файл размером 2.7 гигабайта.
Прикрепленные файлы:
8. isaev2016 11.08.20 06:42 Сейчас в теме
А как доливку данных реализовали?
9. hobi 615 11.08.20 09:07 Сейчас в теме
(8)
А как доливку данных реализовали?

Да, заливка как раз и выполнялась с помощью "доливки". Утилитой bcp.exe данные в несколько потоков, по периодам, выгружались в формате CSV в "расшаренную папку" на Ubuntu. Затем полученные файлы последовательно "доливались" в цикле. Если нужно изменить данные за произвольный период, то предварительно нужно удалить данные за этот период. Команда
ALT ER TABLE work.itz DELETE WHERE dat BETWEEN '<Дата1>' AND '<Дата2>' выполняется практически (несколько секунд) мгновенно за любой период, поскольку для таблицы было задано партицирование по месяцам (системе нужно просто удалить несколько файлов партиций и, максимум, в двух удалить часть данных).
ClickHouse и 1С прекрасно дополняют друг-друга. 1С - транзакционная база, но большие объемы данных обрабатывает не очень хорошо. ClickHouse нет транзакций, но практически не имеет ограничений на объемы данных.
10. hobi 615 11.08.20 09:24 Сейчас в теме
(8) Небольшие (для ClickHouse) объемы данных, до нескольких сотен тысяч строк, можно непосредственно с помощью HTTP запроса залить. Т.е. данные для заливки указать в самом HTTP-запросе. Но сотни миллионов быстрее через файл заливать.
11. 7OH 69 12.08.20 00:55 Сейчас в теме
А левое соединение к такому "срезу" непериодического регистра как делать в запросе ?
12. hobi 615 12.08.20 08:51 Сейчас в теме
(11)
А левое соединение к такому "срезу" непериодического регистра как делать в запросе ?

Я в предыдущем своём посте ошибся с тем, какая таблица к какой левым соединением, а исправить пост тут уже нельзя. Собственно, весь код, который формирует результат соединения со срезом (в этом коде уже используется расширение синтаксиса ClickHouse в конфигурации 1С, о котором я говорил в публикации - пакетный запрос, временные таблицы и подстановки). Результат запроса выводится в таблицу <СрезНаДату> с движком File. Т.е. после исполнения запроса получаем файл в формате CSV с заголовками. Пробелы в операторах, типа "DR OP" в коде отсутствуют, их при размещении кода сайт форума зачем-то добавляет. В пакетном запросе вначале создаётся временная таблица со срезом, а затем уже она соединяется с другой. Сам код:


DR OP TABLE IF EXISTS <СрезНаДату>
;
CRE ATE TABLE <СрезНаДату>
(
`Наименование товара` String,
`Код товара` UInt32,
`Номер магазина` UInt32,
`Наименование магазина` String,
`Доля рабочего времени` Decimal(9, 2),
`Количество продаж` Decimal(9, 3),
`Сумма продаж` Decimal(9, 2),
`Количество конечный остаток` Decimal(9, 3),
`Дата остатка в ТЗ` DateTime,
`Остаток в ТЗ` Decimal(9, 3)
)
ENGINE = File(CSVWithNames)
;
CRE ATE TABLE <temp>ost
(
tovar UInt32,
magaz UInt32,
dat DateTime,
kol Int32
)
ENGINE = Memory
;
INS ERT INTO <temp>ost
SEL ECT
tovar,
magaz,
dat,
kol
FR OM
work.itz AS itz
INNER JOIN
(
SEL ECT
tovar,
magaz,
maxIf(itz.dat, itz.dat <= '<ДатаСреза>') AS dat
FR OM
work.itz
GROUP BY (tovar, magaz)
HAVING maxIf(itz.dat, itz.dat <= '<ДатаСреза>') <> '0000-00-00 00:00:00'
) AS srez
USING
(tovar, magaz, dat)
;
INS ERT IN TO <СрезНаДату>
SELE CT
dictGet('dic.tovar' , 'name' , toUInt64(tovar)) AS `Наименование товара`,
tovar AS `Код товара`,
dictGet('dic.magaz' , 'number' , toUInt64(magaz)) AS `Номер магазина`,
dictGet('dic.magaz' , 'name' , toUInt64(magaz)) AS `Наименование магазина`,
dol AS `Доля рабочего времени`,
kol AS `Количество продаж`,
sum AS `Сумма продаж`,
ost AS `Количество конечный остаток`,
ost.dat AS `Дата остатка в ТЗ`,
ost.kol AS `Остаток в ТЗ`
FR OM
work.srp
LEFT JOIN
<temp>ost AS ost
USING (tovar, magaz)
WH ERE
dat = toDate('<ДатаСреза>')
Показать
13. 7OH 69 12.08.20 10:05 Сейчас в теме
(12) То есть живых данных не будет ?
Я бы таки рискнул и регистр переделал.
Не уверен, что постоянная возня + поддержка в будущем того стоит.
14. hobi 615 12.08.20 10:11 Сейчас в теме
(13)
То есть живых данных не будет ?

Про "живые данные" не совсем понятно. Если нужно получить данные в 1С, например в виде таблицы значений, проблем нет.
В конфигурации это уже решено, используется решение hsИнтегратор для обмена с другими базами 1С (через этот механизм, кстати, получается структура хранения базы данных, которая используется для сопоставления имен базы MS SQL с именами метаданных 1С при формировании запроса в bcp.exe. Можно и без bcp.exe получить, так и делается для справочников. Но большие объемы быстрее, чем с помощью bcp.exe, выгрузить не получится) . Можно результат также в ClickHouse в виде таблицы с другим движком сохранить.

Но в данном примере показан вывод в файл CSV, поскольку он используется для загрузки в Power BI.
15. hobi 615 12.08.20 10:28 Сейчас в теме
(13)
Я бы таки рискнул и регистр переделал


На небольших объемах это даст результат. Но попробуйте идеально переделать регистр с несколькими миллиардами записей и получить срез последних. Если бы быстродействие отличалось в разы, это лучший вариант. Но в данном случае отличие на два порядка.
"Варп-двигатель" ученые тоже ведь хотят создать не для того, чтобы в булочную ездить, а перемещаться на очень большие расстояния.
Так и здесь. В реальных базах очень больших таблиц не так уж много, но именно быстрое получение аналитической информации из таких таблиц является проблемой.
Созинов; +1 Ответить
34. hobi 615 14.08.20 02:23 Сейчас в теме
Согласен, нужно провести корректное тестирование. Не предполагал, что будет такой интерес к теме, при сравнении был интересен порядок в быстродействии. Структура хранения данных, действительно, не оптимальна для данного запроса, давно была создана и существует "как есть", изменять не разрешают.

Поэтому предлагаю такой план тестирования, если будут замечания, сообщите.
1. В пустой конфигурации создам регистры srp (она сейчас периодическая до дня) и itz - его сделаю периодическим (до секунды) с кодами, вместо ссылок (в оригинальной базе три ссылочных реквизита заменены в ClickHouse кодами элементов для эффективного сжатия колонок).
2. Перенесу данные из таблиц ClickHouse в базу с этой конфигурацией
3. Выполню запрос, аналогичный описанному выше в (12) , т.е. на конкретную дату левое соединение таблицы srp со срезом последних из таблицы itz на ту же дату. Такой запрос в ClickHouse выполняется за 51 сек, в результате получается файл CSV 2.7 Гб для последующей загрузки в Power BI.

В предлагаемом тесте будут использоваться только механизмы среза последних платформы и типы данных в таблицах разных СУБД будут совпадать, оценка разницы во времени исполнения будет точнее.
16. SlavaKron 12.08.20 12:35 Сейчас в теме
Как, говорите, называется СУБД из компании Яндекс? Просто на 100-й раз тяжело запомнить.
Ta_Da; dandykry; 7OH; +3 Ответить
18. hobi 615 12.08.20 13:44 Сейчас в теме
(16)
Как, говорите, называется СУБД из компании Яндекс?


СУБД ClickHouse https://clickhouse.tech/docs/ru/

А это список компаний, которые работают с большими данными на этой СУБД:
https://clickhouse.tech/docs/ru/introduction/adopters/
17. 7OH 69 12.08.20 13:21 Сейчас в теме
А у вас 2 часа , ну случайно, не скан проходил ?
Не смотрели планы?
---
На большой базе бухгалтерии был запрос, который строился около 3 минут.
После рефакторинга - 2 секунды.
Тоже на два порядка, и при этом типовыми методами.
19. hobi 615 12.08.20 13:50 Сейчас в теме
(17)
Не смотрели планы?


Нет, не смотрел. Но индекс в базе SQL по измерению дата был добавлен. Срез был именно по этому измерению, без каких-либо отборов.
Нужно было получить все данные на дату среза. Т.е. индекс был полностью покрывающим
20. 7OH 69 12.08.20 13:52 Сейчас в теме
(19) в смысле срез без отборов, если у вас регистр не периодический ???
В первую очередь надо было понять, ЧТО у вас происходит, если это реальность, а не реклама.
21. hobi 615 12.08.20 13:59 Сейчас в теме
(20)
в смысле срез без отборов, если у вас регистр не периодический


Чтобы получить данные среза не обязательно нужно использовать механизм из платформы.
В конце концов платформа создаёт запрос для сервера SQL и никто не мешает создать свой собственный
алгоритм среза, сразу для сервера SQL или как, например, здесь:
https://infostart.ru/1c/articles/980323/

И для реализации не обязательно даже использовать стандартное измерение "Период", можно любой другой реквизит, ресурс или измерение
с типом Дата или ДатаВремя. Главное иметь в SQL покрывающий индекс по этому реквизиту, чтобы Scan-а не было.

Стандартное измерение "Период" для периодического регистра сведений используется платформой для автоматического создания индекса Период + ОстальныеИзмерения, который будет покрывающим для запросов по этому измерению
22. 7OH 69 12.08.20 14:09 Сейчас в теме
(21) я к тому веду, что у нас всего в 4 раза меньше записей и это не РС, а бухгалтерия.
И таких проблем нет, нормально строятся и обороты и остатки.
Проблема либо в структуре либо в запросах.
А без измерения, анализа и поиска причины - довольно странно выглядит попытка переноса в "другую" БД.
--
Довольно странно звучит итог - берите вот ЭТУ БД.
А с другими сравнить минимум?
23. hobi 615 12.08.20 14:19 Сейчас в теме
(22)
я к тому веду, что у нас всего в 4 раза меньше записей и это не РС, а бухгалтерия.
И таких проблем нет, нормально строятся и обороты и остатки.
Проблема либо в структуре либо в запросах.
А без измерения, анализа и поиска причины - довольно странно выглядит попытка переноса в "другую" БД.
--
Довольно странно звучит итог - берите вот ЭТУ БД.
А с другими сравнить минимум?


Итога "берите вот ЭТУ БД" здесь нет, как и рекламы. Публикация размещена в разделе "Практика программирования" и она имеет статус "free". Никому ничего не навязываю, не зарабатываю, а делюсь опытом. Если кому-то пригодится, буду рад.

Про регистр оборотов и остатков тоже не говорил, там нет такого экспоненциального замедления, как при получении срезов с большого регистра сведений. Кроме того, пара регистров, которые используются в приведенном выше запросе, занимают примерно одну треть в базе данных и одна из задач - вынести их наружу, чтобы сократить базу. В ClickHouse интересно то, что данные хранятся с очень большой степенью сжатия и занимают раз в десять меньше места.
user1035175; +1 Ответить
24. hobi 615 12.08.20 14:31 Сейчас в теме
(23)
Проблема либо в структуре либо в запросах.


Чтобы получить "срез последних" стандартными средствами SQL (которые использует платформа 1С), нужно сделать левое соединение таблицы с самой собой. Т.е. миллиарды записей с миллиардами.
В ClickHouse комбинатор If вместе с агрегирующей функцией max, позволяет вначале получить миллион записей (Товар, Магазин, Дата максимально близкая снизу к дате среза ) и уже потом соединять с миллиардом.

Чем больше количество дат в регистре, тем больше будет выигрыш. А регистр растет со временем и добавляются новые даты...
Плюс мы имеем большой бонус от быстродействия самой СУБД ClickHouse. База с колоночным хранением быстрее осуществляет поиск данных, причем намного.

Еще к вопросу, почему выбрана СУБД ClickHouse. Вот здесь бенчмарки разных баз данных https://clickhouse.tech/benchmark/dbms/ от самой компании Яндекс, но я не думаю, что публичные данные с какими-то приписками. Сравниваются именно лучшие аналитические базы данных (оптимизированные для поиска данных). Сравнение с транзакционными базами (MS SQL например) при таком основном сценарии работы будет совсем печальным для MS SQL - база оптимизирована для изменения данных
25. 7OH 69 12.08.20 14:54 Сейчас в теме
(24) ну.... если вы в скуле сначала отбор не можете сделать, но продолжайте и дальше в каждое предложение совать название БД , и говорить , что это не реклама )))
На оном отключаю подписку на эту тему.
26. Dilovar9 64 13.08.20 10:54 Сейчас в теме
Возможно я что то пропустил, но как реализовали запросы UPDATE? на сколько мине известно СУБД ClickHouse умеет только читать и добавлять данные.
28. hobi 615 13.08.20 20:59 Сейчас в теме
(26) СУБД ClickHouse уже поддерживает (с некоторыми оговорками) DELETE и UPDATE. По ссылке можно прочитать про "Мутации":
https://clickhouse.tech/docs/ru/sql-reference/statements/alter/:

На данный момент доступны команды:

ALT ER TABLE [db.]table DELETE WHERE filter_expr
Выражение filter_expr должно иметь тип UInt8. Запрос удаляет строки таблицы, для которых это выражение принимает ненулевое значение.

ALT ER TABLE [db.]table UPDATE column1 = expr1 [, ...] WHERE filter_expr


Главное отличие от стандарта SQL, данные удаляются и обновляются не мгновенно. Т.е. Вы даёте команду СУБД, что хотите это сделать, она удалит/обновит через некоторое время. По моим тестам - от 1 до 3 секунд удаляются данные за большой период. Чтобы быть уверенным, что удаление завершено, лучше с тем же условием выполнить SELECT. Если ничего не вернул, значит операция завершена. Для аналитической базы такая особенность некритична.
33. hobi 615 13.08.20 23:01 Сейчас в теме
(26)
Возможно я что то пропустил, но как реализовали запросы UPDATE? на сколько мине известно СУБД ClickHouse умеет только читать и добавлять данные.


Спасибо за вопрос. Почитал еще раз первоисточник, оказывается есть настройка в ClickHouse. По умолчанию все мутации выполняются асинхронно и я посчитал, что это единственно возможное поведение. Нужно всего лишь изменить значение настройки mutations_sync и поведение будет, как в SQL. Т.е. управление вернется только после завершения операций по удалению или изменению данных.

В самом конце страницы https://clickhouse.tech/docs/ru/operations/settings/settings/#mutations_sync информация, на которую я не обратил вначале внимание:

mutations_sync

Позволяет выполнять запросы ALT ER TABLE ... UPDATE|DELETE (мутации) синхронно.

Возможные значения:

0 - мутации выполняются асинхронно.
1 - запрос ждет завершения всех мутаций на текущем сервере.
2 - запрос ждет завершения всех мутаций на всех репликах (если они есть).

Значение по умолчанию: 0.


И еще цитата из документации:
Синхронность запросов ALTER

Для нереплицируемых таблиц, все запросы ALTER выполняются синхронно. Для реплицируемых таблиц, запрос всего лишь добавляет инструкцию по соответствующим действиям в ZooKeeper, а сами действия осуществляются при первой возможности. Но при этом, запрос может ждать завершения выполнения этих действий на всех репликах.

Для запросов ALTER ... ATTACH|DETACH|DROP можно настроить ожидание, с помощью настройки replication_alter_partitions_sync.
Возможные значения: 0 - не ждать, 1 - ждать выполнения только у себя (по умолчанию), 2 - ждать всех.

Для запросов ALT ER TABLE ... UPDATE|DELETE синхронность выполнения определяется настройкой mutations_sync.
27. Xershi 1474 13.08.20 11:29 Сейчас в теме
Насколько понял это статья рассчитана на уровень выше новичка, но еще не эксперта.
На уровне эксперта статья не выдерживает критики.
Дополните эту информацию иначе статья помойка.
29. hobi 615 13.08.20 21:12 Сейчас в теме
(27)
Насколько понял это статья рассчитана на уровень выше новичка, но еще не эксперта.
На уровне эксперта статья не выдерживает критики.
Дополните эту информацию иначе статья помойка.


Собственно и не было цели написать статью в виде обучающего материала или готового решения. Приведена конкретная реализация "Среза последних" на диалекте SQL в ClickHouse и результаты тестирования. Корректность полученных данных "Среза последних" проверялась с результатами, полученными стандартным способом (платформа 1С) и данные совпали. Время исполнения получено намного меньше, поэтому такой вариант планируется к использованию бизнес-аналитиками.

Если есть конкретные вопросы по интеграции ClickHouse и 1С, по возможности отвечу.
30. Xershi 1474 13.08.20 21:14 Сейчас в теме
(29) дополните статью этой информацией, тогда люди вас правильно поймут, пока у вас были только хайповые заголовки и это цепляет, но потом печалит.
headMade; +1 Ответить
31. hobi 615 13.08.20 21:27 Сейчас в теме
(30)
дополните статью этой информацией, тогда люди вас правильно поймут, пока у вас были только хайповые заголовки и это цепляет, но потом печалит.


Трудно понять, какая именно информация интересует. Полное описание интерфейсов к СУБД есть на сайте первоисточника, лучше и актуальнее всё равно не сделать. Как отправить HTTP запрос и получить результаты, тоже вроде бы известно многим. В общем-то я так и разбирался с СУБД, читал первоисточник, отправлял запросы, смотрел результаты. Потом уже с реальными данными начал тестировать.
И далеко не все возможности этой СУБД тестировал, только те, которые для практических задач были нужны.
32. 7OH 69 13.08.20 21:28 Сейчас в теме
Минимум сравнение с другими бд, с замерам. С анализом плана вашего запроса в оригинале и в результатах.
35. user612295_death4321 14.08.20 21:18 Сейчас в теме
Было бы интересно посмотреть на цифры, если переписать "СрезПоследних" на свой "Срез последних" как из примера из комментариев по ссылке. Кажется сомнительной идеей сразу CH поднимать.
36. wtlz 272 15.08.20 14:38 Сейчас в теме
не совсем понял, какую задачу попытались решить? Срез последних в итоге где используется - в ClickHouse? Или передается назад в 1С?
На днях встретился с похожей проблемой, но регистр периодический, в измерении составной тип ссылка+строка, записей 20 млн, срез последних по одному элементу справочника получался средствами платформы 3 секунды (что очень долго). В итоге удалил записи где в измерении строка, убрал составной тип, добавил вирт. отбор и левое соединение с основной таблицей - теперь срез летает (0,001 с.).
Есть независимый и непериодический регистр сведений с исторически сложившейся структурой, которую уже нельзя изменить по различным причинам (место на диске, недопустимо большое время реструктуризации):

- не думали над вариантом создать новый Периодический РС, удалив из измерений ДатуВремя, в потоках (даже по году) заполнить этот РС и сравнить получение среза последних?
37. 7OH 69 15.08.20 15:26 Сейчас в теме
(36) вам изначально могло помочь просто тип передавать в условии, чтобы не сканировало таблицу
38. wtlz 272 15.08.20 16:47 Сейчас в теме
39. hobi 615 15.08.20 22:01 Сейчас в теме
(36)
не совсем понял, какую задачу попытались решить? Срез последних в итоге где используется - в ClickHouse? Или передается назад в 1С?


Задача от бизнес-аналитиков. Данные, которые им требуются, получаются запросом к периодическому регистру сведений (>9 миллиардов записей), которые должны быть связаны левым соединением (по товару и магазину) с данными, аналогичным срезу последнего по измерению дата в непериодическом регистре (1.7 миллиарда записей). Структуру хранения данных изменять нельзя, возможное технологическое окно гораздо меньше времени реструктуризации (на копии базы время проверили), более длительный простой повлечет миллионные убытки компании (простой автотранспорта и несвоевременная доставка товаров в магазины). При этом данные, которые нужны бизнес-аналитикам, нужны на дату без отборов по товарам и магазинам. Т.е. срез на дату нужен не по одному элементу справочника, а по всем. Результирующая таблица имеет примерно 10 миллионов записей (она используется в качестве источника данных для загрузки в Power BI. Непосредственно в Power BI полные данные не загрузить, объем данных превышает ограничения Power BI)

И вторая задача - вынести эту пару регистров из базы данных 1С, поскольку они занимают треть всего объема базы (база уже ~7.5 терабайт). В базе ClickHouse они заняли примерно в 10 раз меньше места на диске.

Получаемые запросом к этим таблицам данные в виде файла формата CSV используются для загрузки в Power BI. По замерам бизнес-аналитиков время на получение данных в формате CSV сократилось в 120 раз.
52. Serg2000mr 311 18.05.23 00:30 Сейчас в теме
(39) Можно создать копию регистра,только с нужной структурой. Изменить код, чтобы новые записи шли в два регистра. Когда будет выполнена синхронизация регистров, перейти на работу с новым.
40. hobi 615 15.08.20 22:51 Сейчас в теме
(36)
какую задачу попытались решить


Уточнение к (39):
Срез последних к периодическому (день) регистру сведений из 9.3 миллиардов записей левым соединением с данными, аналогичным срезу последних по измерению с типом датавремя в непериодическом регистре сведений из 1.7 миллиардов записей. Левое соединение по измерениям товар и магазин, присутствующим в обоих регистрах. Т.е. срез последних соединяем с эмуляцией среза из другого регистра. Отборы по товарам и магазинам в результате не нужны, выгружаются все данные, поскольку дальнейший анализ результата производится уже средствами Power BI.
41. user1035175 2 16.08.20 08:44 Сейчас в теме
Сколько человеко-часов ушло на решение задачи (+/- 2 слона)?
42. hobi 615 16.08.20 09:14 Сейчас в теме
(41)
Сколько человеко-часов ушло на решение задачи (+/- 2 слона)?


Конкретно на задачу от бизнес-аналитика около 4-х часов ( создан и протестирован запрос, указанный в (12) ), но интеграция с ClickHouse уже была реализована и данные регистров были перенесены в базу ClickHouse ранее в рамках задачи по переносу регистров. Испробованы разные способы переноса, например через HTTP запросами к серверу 1С. Быстрее, чем выгрузка утилитой bcp.exe в файлы и загрузка данных полученных файлов в базу ClickHouse нет (по крайней мере я не знаю). Данные небольшого объема (справочники) удобнее через HTTP переносить, коды элементов можно непосредственно в запросе 1С получить.

Большие объемы рабочей базы накладывают ряд ограничений на возможности оперативного изменения конфигурации. Кроме того, что операции по изменению конфигурации выполняются значительно дольше, с базой работают сотни человек, в ночное время - программные роботы, формирующие документы. Поэтому и начат был поиск альтернативных инструментов, которые помогут снизить нагрузку на базу, перенести исторические данные, логи, в другое место.
А конкретная практическая задача поступила уже после проведения работ по интеграции с ClickHouse.
43. kamisov 150 16.08.20 12:08 Сейчас в теме
Пробовали оконные функции mssql?
44. hobi 615 16.08.20 15:32 Сейчас в теме
(43)
Пробовали оконные функции mssql


Нет, в ClickHouse показалось проще. Имена полей SQL базы 1С только в алгоритме выгрузки используются
(они, кстати, отличаются в разных базах, развернутых из одной конфигурации 1С, по факту в нескольких рабочих базах так и есть).
В дальнейшем алгоритмы обработки уже с использованием имен, которые сами выбрали при создании таблиц ClickHouse.
По этой же причине для имен полей в таблицах ClickHouse используется англоязычная сокращенная транскрипция, хотя можно и кириллицу использовать, например: `Номер магазина`. Но следить за пробелами и апострофами неудобно.

Кроме того, опыта разработки на T-SQL не было, а порог "вхождения" в программирование на ClickHouse для меня оказался ниже и многие вещи, которые в SQL пришлось бы вручную делать для работы с данными больших объемов (например - шардирование, репликация), ClickHouse поддерживает автоматически, на уровне платформы. Также привлекла потенциальная возможность наращивания производительности и обрабатываемых объемов добавлением оборудования. И SQL масштабируется, но CH это делает практически линейно от количества ядер и компьютеров. Пока это не потребовалось, но хорошо, что есть.
45. ildarovich 7850 19.08.20 14:59 Сейчас в теме
(0) Добавлю в эту тему ссылку на свою публикацию: Простой способ индексации интервалов. Поскольку она реализует более общий (не зависящий от СУБД) подход к задаче "среза последних". Поэтому имеет прямое отношение к рассматриваемой теме. Вполне возможно, что на его базе можно было решить эту задачу и без выгрузки в стороннюю СУБД и без реструктуризации, создав дополнительную таблицу (регистр сведений). Но это нужно еще посмотреть.
В данной статье, напротив, используется технологическое решение, связанное с использованием ClickHouse.
Ничего против этого не имею.
Но подавать его как наиболее эффективное решение именно для реализации "среза последних" оснований не увидел. Поскольку, как мне кажется, это колоночная СУБД будет настолько же быстрее и на большинстве других запросов для аналитических задач.
У 1С, кстати, с недавного времени, есть свой колоночный "Ускоритель аналитических запросов", который обрабатывает данные в оперативной памяти и автоматически синхронизируется с основной базой. Правда, 1С нацелила его на корпоративный рынок, что, на мой взгляд, ошибка. Разрабатывал механизм, кстати, выходец из Яндекса.
46. ITSun 04.03.21 13:54 Сейчас в теме
Только случайно наткнулся на данную тему.
Очень похоже на рекламу, как там бишь её, СУБД от известной российской ИТ-компании. Прям на языке вертится, а вспомнить не получается.

Автор зарегистрирован на ресурсе 10 лет назад и утверждает, что не имеет опыта разработки на T-SQL. Как-то странно, хотя, всё возможно.

К автору пожелание - улучшить оформление:
- скриншоты;
- выпадающие окна с кодом;
- сравнительные таблицы и диаграммы оформить.

А то реально получается, что заметка опубликована по принципу "джентльменам принято верить на слово".
47. hobi 615 05.03.21 23:02 Сейчас в теме
(46)
(46)
Автор зарегистрирован на ресурсе 10 лет назад и утверждает, что не имеет опыта разработки на T-SQL. Как-то странно, хотя, всё возможно.


Небольшой опыт теперь уже имею, через bcp.exe выгрузку из базы 1С больших объемов данных SQL запросом в CSV с последующей загрузкой в базу ClickHouse (справочники, данные регистров, документы). Убеждать и рекламировать "СУБД от известной российской ИТ-компании" здесь тоже не собираюсь. Скриншот с размерами некоторых таблиц, в которые перенесены данные из базы 1С, и скриншоты интерфейса технологической базы 1С (из которой выполняется загрузка, сверка данных и регламентные расчеты) прикладываю.
На этом скриншоте самая большая таблица > 12 миллиардов записей, в другой базе - данные объединенного журнала регистрации 13-ти больших баз данных 1С имеют размер больше 15 миллиардов записей. И эти данные сейчас уже используются в качестве источника в нескольких критичных по времени исполнения отчетах 1С. В публикации поделился своим небольшим на тот момент опытом, если есть какие-то религиозные предубеждения к другим СУБД, кроме напрямую поддерживаемых платформой 1С, можете игнорировать публикацию. А если есть конкретные вопросы о проблемах, нюансах и сложностях интеграции 1С с данной СУБД, например - быстрая загрузка данных из MS SQL в СУБД, формирование отчетов по данным СУБД с использованием СКД, сверка данных СУБД и 1С, возможности диалекта SQL данной СУБД и адаптация к 1С, система скриптов, исполняющих смешанный код 1С, запросы MS SQL и ClickHouse, отвечу.
Прикрепленные файлы:
48. VVi3ard 52 17.05.21 20:16 Сейчас в теме
Спасибо за статью, не до конца понимаю как выполняется актуализация данных? Добавление новых строк по проведении, удаление при отмене проведения и.т п.
49. hobi 615 18.05.21 10:55 Сейчас в теме
(48)
Данные актуализируются раз в сутки (для целей аналитики этого достаточно).
В рабочей базе 1С выполняется удаленный запрос через механизм hИнтегратор - для каждой даты и типа документов вычисляются суммы по проведенным документам. Это самый простой и быстрый способ сравнения. Контрольные суммы (SHA256) пробовал считать в базе SQL, это надежнее, но получалось дольше в несколько раз.
То же самое выполняется в базе Клик. По всем датам, где обнаружили расхождения, формируются запросы, которые исполняются утилитой bcp.exe (в несколько потоков), выгружаются все данные по проведенным документам за эти даты в формат CSV.
Затем в базе КХ удаляются все данные с расхождениями и загружаются из CSV и запускаются расчеты по изменениям (остатки на каждый день и проч). Перед этим полностью копируются справочники.
На синхронизацию уходит, примерно, полчаса. Различные регламентные расчеты еще часа три. Выполняется по ночам, типичный объем изменений несколько десятков миллионов строк.
Часть данных синхронизируются постоянно, например каждые 3 минуты чеки загружаются с FTP. Скорость загрузки - примерно 10 отчетов из магазинов в секунду, с учетом времени на удаление прежних вариантов за дату от магазинов в базе Клик.
50. Said-We 19.04.23 18:19 Сейчас в теме
(1) А такой схематический запрос будет аналогом среза последних или нет? :-)
sel ect
    *
fr om
    (select
           row_number() over(partition by t.izmer1, t.izmer2...t.izmerN ORDER BY t.period DESC) as npp
          ,t.period

          ,t.izmer1
          ,t.izmer2
           ...
          ,t.izmerN

          ,t.resurs_rekvizit1
          ,t.resurs_rekvizit2
           ...
          ,t.resurs_rekvizitN
     fr om
           PC_name as t
     where
           t.period <= &ДатаХ AND
           условия внутри скобок среза на поля <t.izmer1, t.izmer2...t.izmerN> и на поля <t.resurs_rekvizit1, t.resurs_rekvizit2,...t.resurs_rekvizitN>
     ) as t
wh ere
     t.npp = 1
Показать
51. hobi 615 28.04.23 09:35 Сейчас в теме
(50) оконные функции в ClickHouse появились уже позже публикации.
Срез последних с помощью оконных функций будет эффективнее агрегации и соединения таблиц.
Так что да, оконные функции предпочтительнее для такой задачи.
Еще проще использовать конструкцию LIMIT BY (тоже появилась позже):

SEL ECT * FR OM <таблица>
ORDER BY
измерение1, измерение2,...,измерениеN, dat DESC
LIMIT 1 BY измерение1, измерение2,...,измерениеN

https://clickhouse.com/docs/en/sql-reference/statements/select/limit-by#examples

Пример работающего запроса:
SEL ECT
dat,
magaz,
tovar,
kol
FR OM work.itz
WHERE // отбор в диапазоне магазинов
magaz BETWEEN 10000 AND 11000
ORDER BY
tovar, magaz, dat DESC
LIMIT 1 BY tovar, magaz
SETTINGS
max_memory_usage=40000000000

Показать


Если исходная таблица очень большая, "срез" можно получать частями, в диапазоне какого-либо измерения,
иначе возможна нехватка памяти для выполнения запроса.
А результат можно "накопить" в цикле в другой таблице.
Оставьте свое сообщение