Денормализовать таблицу в клик-хаусе с помощью sql-запроса

Исходная таблица имеет формат

CREATE TABLE IF NOT EXISTS src_table (
    str_1 String,
    str_2 String,
    metric_1 UInt64,
    metric_2 UInt8
) ENGINE = Log

денормализованный столбец - str_2, а денормализованная таблица

CREATE TABLE IF NOT EXISTS denorm_table (
    dt Date,
    str_1 String,
    attr_1 UInt64,
    attr_2 UInt64,
    ......
    attr_1000 UInt64,
    attr_1001 UInt8,
    attr_1002 UInt8,
    .....
    attr_2000 UInt8
) ENGINE = MergeTree PARTITION BY (dt) ORDER BY (dt, str_1) SETTINGS index_granularity=8192

Предположим, что столбец str_2 имеет 1000 различных значений (1 ... 1000) и что

attr_1 - значение столбца metric_1, когда str_2 равно 1,

attr_2 - значение столбца metric_1, когда str_2 равно 1,

.....

attr_1001 - значение столбца metric_2, когда str_2 равно 1

...

Запрос денормализации:

 INSERT INTO denorm_table        
(dt, user, attr_1, attr_2, ..., attr_1000, attr_1001, attr_2000)
 SELECT 
'2018-11-01' as dt,
str_1,
arrayElement( groupArray(metric_1), indexOf(groupArray(str_2), '1') ) as attr_1,
arrayElement( groupArray(metric_1), indexOf(groupArray(str_2), '2') ) as attr_2,
......
arrayElement( groupArray(metric_1), indexOf(groupArray(str_2), '1000') ) as attr_1000,
arrayElement( groupArray(metric_2), indexOf(groupArray(str_2), '1001') ) as attr_1001,
.....
arrayElement( groupArray(metric_1), indexOf(groupArray(str_2), '2000') ) as attr_2000
FROM src_table
WHERE str_2 in ('1', '2', .....)
GROUP BY str_1

Для 750 значений столбца str_2 (1502 столбца в денормализованной таблице) запрос работает без проблем.

Но когда количество столбцов денормализованной таблицы равно 2002 (и соответствующие значения str_2 1000), у меня есть socket.timeout: timed out ОШИБКА

  File "/usr/lib/python2.7/site-packages/clickhouse_driver/client.py", line 119, in execute
columnar=columnar
  File "/usr/lib/python2.7/site-packages/clickhouse_driver/client.py", line 192, in process_ordinary_query
columnar=columnar)
  File "/usr/lib/python2.7/site-packages/clickhouse_driver/client.py", line 42, in receive_result
return result.get_result()
  File "/usr/lib/python2.7/site-packages/clickhouse_driver/result.py", line 39, in get_result
for packet in self.packet_generator:
  File "/usr/lib/python2.7/site-packages/clickhouse_driver/client.py", line 54, in packet_generator
packet = self.receive_packet()
  File "/usr/lib/python2.7/site-packages/clickhouse_driver/client.py", line 68, in receive_packet
packet = self.connection.receive_packet()
 File "/usr/lib/python2.7/site-packages/clickhouse_driver/connection.py", line 331, in receive_packet
packet.type = packet_type = read_varint(self.fin)
 File "/usr/lib/python2.7/site-packages/clickhouse_driver/reader.py", line 38, in read_varint
i = _read_one(f)
 File "/usr/lib/python2.7/site-packages/clickhouse_driver/reader.py", line 23, in _read_one
c = f.read(1)
 File "/usr/lib64/python2.7/socket.py", line 380, in read
data = self._sock.recv(left)
socket.timeout: timed out

Есть ли настройка клиент / сервер, которая устранила бы проблему?

Из журналов

2018.12.04 22:49:26.755926 [ 36 ] {} <Trace> SystemLog (system.query_thread_log): Flushing system log
2018.12.04 22:49:26.756233 [ 139 ] {821ce7ea-94b7-4675-96f5-feccb31b0ebe} <Error> executeQuery: Code: 32, e.displayText() = DB::Exception: Attempt to read after eof, e.what() = DB::Exception (from [::1]:52224) (in query:

========= ИЗМЕНИТЬ =========

Я изменил запрос, как показано ниже (@johey), и ошибка больше не повторялась:

WHERE modulo(sipHash64(str_1), 20) = 0 используется для разделения данных на группы вместо выполнения запроса для всех значений в столбце str_1

INSERT INTO dst_table (....)
SELECT
    arrayElement(metric_1_array, indexOf(str_2_array, '1') ) as attr_1,
    arrayElement(metric_1_array, indexOf(str_2_array, '2') ) as attr_2,
    ......
    arrayElement(metric_2_array, indexOf(str_2_array, '1') ) as attr_1001,
    ......
FROM (
    SELECT
        str_1,
        groupArray(metric_1) metric_1_array,
        groupArray(metric_2) metric_2_array,
        groupArray(str_2) str_2_array
    FROM src_table
    WHERE modulo(sipHash64(str_1), 20) = 0
    AND str_2 in ('1', '2', ......)
    GROUP BY str_1
)

sql denormalization clickhouse
person dimitris lepipas    schedule 04.12.2018    source источник
comment
Возможно, это не исправит, но, возможно, вы могли бы немного сократить запрос следующим образом: INSERT INTO denorm_table (...) SELECT '2018-11-01' as dt, str_1, arrayElement(metric1, indexOf(str2, '1') ) as attr_1, arrayElement(metric1, indexOf(str2, '2') ) as attr_2, ... FROM ( SELECT user, str_1, groupArray(metric_1) metric1, groupArray(metric_2) metric2, groupArray(str_2) str2 FROM src_table WHERE str_2 in ('1', '2', .....) ) s GROUP BY user   -  person johey    schedule 04.12.2018
comment
Возможно, глупое замечание (потому что я не понимаю, что вы пытаетесь сделать), но indexOf(groupArray(str_2), '1') также не даст положительного отрицательного результата, когда str_2 равно, например, 1001? Вы же этого не хотите?   -  person johey    schedule 04.12.2018
comment
Этот пример не самодостаточен. Что за столбец user?   -  person Amos    schedule 04.12.2018
comment
user - это str_1 столбец   -  person dimitris lepipas    schedule 04.12.2018
comment
arrayElement( groupArray(metric_1), indexOf(groupArray(str_2), '1') ) --- ›Я хочу получить значение столбца metric_1, которое соответствует значению« 1 »столбца str_2 (создайте массив из 2 столбцов и используйте элементы 2-го массива для индексации 1-го) @johey   -  person dimitris lepipas    schedule 04.12.2018

Ответы (1)


arrow_upward
1
arrow_downward

Похоже, вы взорвали парсер AST. Есть два параметра, ограничивающие размер входных деревьев AST: max_ast_elements и max_expanded_ast_elements. Вы можете попробовать поднять их и посмотреть, поможет ли это.

Вот еще один способ добиться этого процесса денормализации:

SELECT
    arrayStringConcat(arrayConcat(['2018-11-01', str_1], arraySlice(groupArray(toString(metric_1)), 1, 1000), arraySlice(groupArray(toString(metric_2)), 1001, 1000)), ',')
FROM
(
    SELECT *
    FROM src_table
    WHERE toUInt64(str_2) BETWEEN 1 AND 2000
    ORDER BY toUInt64(str_2)
)
GROUP BY str_1
INTO OUTFILE 'xxx.csv'

Затем загрузите этот файл данных csv в denorm_table.

Обновлять

После объединения этого PR можно получить ETL на стороне сервера следующим образом:

INSERT INTO denorm_table
SELECT
    arrayStringConcat(arrayConcat(['2018-11-01', str_1], arraySlice(groupArray(toString(metric_1)), 1, 1000), arraySlice(groupArray(toString(metric_2)), 1001, 1000)), ',')
FROM
(
    SELECT *
    FROM src_table
    WHERE toUInt64(str_2) BETWEEN 1 AND 2000
    ORDER BY toUInt64(str_2)
)
GROUP BY str_1
FORMAT CSV
person Amos    schedule 05.12.2018