Template - ClickHouse Documentation

Ввод	Вывод	Алиас
✔	✔

Описание

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

Настройка	Описание
`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 задает путь к файлу, содержащему строки формата для строк в следующем синтаксисе:

delimiter_1${column_1:serializeAs_1}delimiter_2${column_2:serializeAs_2} ... delimiter_N

Где:

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

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

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

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

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

Search phrase: ${s:Quoted}, count: ${c:Escaped}, ad price: $$${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.

Search phrase: 'bathroom interior design', count: 2166, ad price: $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}.

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

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

Зачастую развернуть конфигурации формата (задаваемые через format_template_row, format_template_resultset) для формата Template в каталоге на всех узлах кластера затруднительно или невозможно. Кроме того, формат может быть настолько простым, что его не нужно помещать в файл. В таких случаях format_template_row_format (для format_template_row) и format_template_resultset_format (для format_template_resultset) можно использовать, чтобы задать строку шаблона непосредственно в запросе, а не в виде пути к файлу, в котором она находится.

Правила для строк формата и escape-последовательностей такие же, как и для:

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

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

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

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

Query

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    '

/some/path/resultset.format

<!DOCTYPE HTML>
<html> <head> <title>Search phrases</title> </head>
 <body>
  <table border="1"> <caption>Search phrases</caption>
    <tr> <th>Search phrase</th> <th>Count</th> </tr>
    ${data}
  </table>
  <table border="1"> <caption>Max</caption>
    ${max}
  </table>
  <b>Processed ${rows_read:XML} rows in ${time:XML} sec</b>
 </body>
</html>

/some/path/row.format

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

Response

<!DOCTYPE HTML>
<html> <head> <title>Search phrases</title> </head>
 <body>
  <table border="1"> <caption>Search phrases</caption>
    <tr> <th>Search phrase</th> <th>Count</th> </tr>
    <tr> <td></td> <td>8267016</td> </tr>
    <tr> <td>bathroom interior design</td> <td>2166</td> </tr>
    <tr> <td>clickhouse</td> <td>1655</td> </tr>
    <tr> <td>spring 2014 fashion</td> <td>1549</td> </tr>
    <tr> <td>freeform photos</td> <td>1480</td> </tr>
  </table>
  <table border="1"> <caption>Max</caption>
    <tr> <td></td> <td>8873898</td> </tr>
  </table>
  <b>Processed 3095973 rows in 0.1569913 sec</b>
 </body>
</html>

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

Some header
Page views: 5, User id: 4324182021466249494, Useless field: hello, Duration: 146, Sign: -1
Page views: 6, User id: 4324182021466249494, Useless field: world, Duration: 185, Sign: 1
Total rows: 2

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

/some/path/resultset.format

Some header\n${data}\nTotal rows: ${:CSV}\n

/some/path/row.format

Page views: ${PageViews:CSV}, User id: ${UserID:CSV}, Useless field: ${:CSV}, Duration: ${Duration:CSV}, Sign: ${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.

Query

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 Formats|\n|---|\n${data}\n'

Только посмотрите! Нам не пришлось вручную добавлять все эти | и -, чтобы создать эту таблицу в Markdown:

Response

|ClickHouse Formats|
|---|
|`BSONEachRow`|
|`CustomSeparatedWithNames`|
|`Prometheus`|
|`DWARF`|
|`Avro`|

​Описание

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

​format_template_row

​format_template_rows_between_delimiter

​format_template_resultset

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

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

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

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

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

Описание

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

format_template_row

format_template_rows_between_delimiter

format_template_resultset

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

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

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

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

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