Template

Вход	Выход	Псевдоним
✔	✔

Описание

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

Используются следующие настройки:

Setting	Description
format_template_row	Указывает путь к файлу, который содержит строки формата для строк.
format_template_resultset	Указывает путь к файлу, который содержит строки формата для наборов результатов.
format_template_rows_between_delimiter	Указывает разделитель между строками, который выводится (или ожидается) после каждой строки, кроме последней (\n по умолчанию).
format_template_row_format	Указывает строку формата для строк во встроенной спецификации.
format_template_resultset_format	Указывает строку формата для набора результатов во встроенной спецификации.
Некоторые настройки других форматов (например, output_format_json_quote_64bit_integers при использовании экранирования JSON

Настройки и правила экранирования

format_template_row

Настройка format_template_row задаёт путь к файлу, который содержит шаблоны формата для строк со следующим синтаксисом:

разделитель_1${столбец_1:формат_сериализации_1}разделитель_2${столбец_2:формат_сериализации_2} ... разделитель_N

Где:

Часть синтаксиса	Описание
delimiter_i	Разделитель между значениями (символ $ можно экранировать как $$)
column_i	Имя или индекс столбца, значения которого должны быть выбраны или вставлены (если пусто, столбец будет пропущен)
serializeAs_i	Правило экранирования для значений столбца.

Поддерживаются следующие правила экранирования:

Правило экранирования	Описание
CSV, JSON, XML	Аналогично форматам с такими же названиями
Escaped	Аналогично TSV
Quoted	Аналогично Values
Raw	Без экранирования, аналогично TSVRaw
None	Без правила экранирования — см. примечание ниже

Примечание

Если правило экранирования не указано, будет использовано None. XML подходит только для вывода.

Рассмотрим пример. Пусть задана следующая строка формата:

Поисковая фраза: ${s:Quoted}, количество: ${c:Escaped}, цена рекламы: $$${p:JSON};

Следующие значения будут выведены (при использовании SELECT) или ожидаются (при использовании INPUT), расположенные соответственно между разделителями Search phrase:, , count:, , ad price: $ и ;:

• s (с правилом экранирования Quoted)
• c (с правилом экранирования Escaped)
• p (с правилом экранирования JSON)

Например:

• При выполнении INSERT строка ниже соответствует ожидаемому шаблону, и значения bathroom interior design, 2166, $3 будут записаны в столбцы Search phrase, count, ad price.
• При выполнении SELECT строка ниже является результатом вывода, при условии, что значения bathroom interior design, 2166, $3 уже сохранены в таблице в столбцах Search phrase, count, ad price.

Поисковая фраза: 'дизайн интерьера ванной комнаты', количество: 2166, цена объявления: $3;

format_template_rows_between_delimiter

Параметр format_template_rows_between_delimiter задаёт разделитель между строками, который выводится (или ожидается) после каждой строки, кроме последней (\n по умолчанию).

format_template_resultset

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

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

• data — строки с данными в формате format_template_row, разделённые format_template_rows_between_delimiter. Этот плейсхолдер должен быть первым плейсхолдером в форматной строке.
• totals — строка с итоговыми значениями в формате format_template_row (при использовании WITH TOTALS).
• min — строка с минимальными значениями в формате format_template_row (когда extremes установлено в 1).
• max — строка с максимальными значениями в формате format_template_row (когда extremes установлено в 1).
• rows — общее количество выводимых строк.
• rows_before_limit — минимальное количество строк, которое было бы без LIMIT. Выводится только если запрос содержит LIMIT. Если запрос содержит GROUP BY, rows_before_limit_at_least — это точное количество строк, которое было бы без LIMIT.
• time — время выполнения запроса в секундах.
• rows_read — количество прочитанных строк.
• bytes_read — количество прочитанных байт (в несжатом виде).

Для плейсхолдеров data, totals, min и max не должно быть задано правило экранирования (или явно должно быть указано None). Для остальных плейсхолдеров может быть задано любое правило экранирования.

Примечание

Если параметр format_template_resultset является пустой строкой, по умолчанию используется ${data}.

Для запросов INSERT формат позволяет пропускать некоторые столбцы или поля, если задан префикс или суффикс (см. пример).

Встроенная спецификация

Часто бывает сложно или невозможно развернуть конфигурации формата (заданные format_template_row, format_template_resultset) для шаблонного формата в каталог на всех узлах кластера. Кроме того, формат может быть настолько тривиальным, что его не требуется выносить в отдельный файл.

Для таких случаев format_template_row_format (для format_template_row) и format_template_resultset_format (для format_template_resultset) можно использовать для задания строки шаблона непосредственно в запросе, а не как путь к файлу, в котором она содержится.

Примечание

Правила для строк формата и управляющих последовательностей такие же, как и для:

• format_template_row при использовании format_template_row_format.
• format_template_resultset при использовании format_template_resultset_format.

Пример использования

Рассмотрим два примера того, как можно использовать формат Template: сначала для выборки данных, а затем для вставки данных.

Выборка данных

SELECT SearchPhrase, count() AS c FROM test.hits GROUP BY SearchPhrase ORDER BY c DESC LIMIT 5 FORMAT Template SETTINGS
format_template_resultset = '/some/path/resultset.format', format_template_row = '/some/path/row.format', format_template_rows_between_delimiter = '\n    '

<!DOCTYPE HTML>
<html> <head> <title>Поисковые фразы</title> </head>
 <body>
  <table border="1"> <caption>Поисковые фразы</caption>
    <tr> <th>Поисковая фраза</th> <th>Количество</th> </tr>
    ${data}
  </table>
  <table border="1"> <caption>Максимум</caption>
    ${max}
  </table>
  <b>Обработано ${rows_read:XML} строк за ${time:XML} сек.</b>
 </body>
</html>

<tr> <td>${0:XML}</td> <td>${1:XML}</td> </tr>

Результат:

<!DOCTYPE HTML>
<html> <head> <title>Поисковые фразы</title> </head>
 <body>
  <table border="1"> <caption>Поисковые фразы</caption>
    <tr> <th>Поисковая фраза</th> <th>Количество</th> </tr>
    <tr> <td></td> <td>8267016</td> </tr>
    <tr> <td>дизайн интерьера ванной</td> <td>2166</td> </tr>
    <tr> <td>clickhouse</td> <td>1655</td> </tr>
    <tr> <td>мода весна 2014</td> <td>1549</td> </tr>
    <tr> <td>произвольные фотографии</td> <td>1480</td> </tr>
  </table>
  <table border="1"> <caption>Максимум</caption>
    <tr> <td></td> <td>8873898</td> </tr>
  </table>
  <b>Обработано 3095973 строк за 0,1569913 сек.</b>
 </body>
</html>

Вставка данных

Заголовок
Просмотры страниц: 5, ID пользователя: 4324182021466249494, Бесполезное поле: hello, Длительность: 146, Знак: -1
Просмотры страниц: 6, ID пользователя: 4324182021466249494, Бесполезное поле: world, Длительность: 185, Знак: 1
Всего строк: 2

INSERT INTO UserActivity SETTINGS
format_template_resultset = '/some/path/resultset.format', format_template_row = '/some/path/row.format'
FORMAT Template

Некоторый заголовок\n${data}\nВсего строк: ${:CSV}\n

Просмотры страниц: ${PageViews:CSV}, Идентификатор пользователя: ${UserID:CSV}, Неиспользуемое поле: ${:CSV}, Продолжительность: ${Duration:CSV}, Знак: ${Sign:CSV}

PageViews, UserID, Duration и Sign внутри плейсхолдеров — это имена столбцов в таблице. Значения после Useless field в строках и после \nTotal rows: в суффиксе будут игнорироваться. Все разделители во входных данных должны в точности совпадать с разделителями в указанных строках формата.

Встроенная спецификация

Устали вручную форматировать таблицы Markdown? В этом примере мы рассмотрим, как можно использовать формат Template и настройки встроенной спецификации, чтобы решить простую задачу — выполнить SELECT по именам некоторых форматов ClickHouse из таблицы system.formats и отформатировать их как таблицу в формате Markdown. Это можно легко сделать, используя формат Template и настройки format_template_row_format и format_template_resultset_format.

В предыдущих примерах мы указывали строки шаблонов для результирующего набора и строк в отдельных файлах, а пути к этим файлам задавали с помощью настроек format_template_resultset и format_template_row соответственно. Здесь мы сделаем это прямо в запросе, потому что наш шаблон тривиален и состоит лишь из нескольких символов | и - для создания таблицы в формате Markdown. Шаблонную строку для результирующего набора мы зададим с помощью настройки format_template_resultset_format. Чтобы сделать заголовок таблицы, мы добавили |ClickHouse Formats|\n|---|\n перед ${data}. Настройку format_template_row_format мы используем, чтобы задать шаблонную строку |`{0:XML}`| для наших строк. Формат Template вставит наши строки с заданным форматом в плейсхолдер ${data}. В этом примере у нас только один столбец, но при необходимости вы можете добавить больше, добавив {1:XML}, {2:XML} и т. д. в шаблон строки, выбирая правило экранирования по необходимости. В этом примере мы используем правило экранирования XML.

WITH formats AS
(
 SELECT * FROM system.formats
 ORDER BY rand()
 LIMIT 5
)
SELECT * FROM formats
FORMAT Template
SETTINGS
 format_template_row_format='|`${0:XML}`|',
 format_template_resultset_format='|Форматы ClickHouse|\n|---|\n${data}\n'

Посмотрите-ка! Мы избавили себя от необходимости вручную добавлять все эти | и -, чтобы сделать эту markdown-таблицу:

|Форматы ClickHouse|
|---|
|`BSONEachRow`|
|`CustomSeparatedWithNames`|
|`Prometheus`|
|`DWARF`|
|`Avro`|