ClickHouse: как сделать LEFT OUTER JOIN по трем индексам?

#sql #база_данных #clickhouse

Две таблицы, meta и event, имеют три одинаково названных столбца с id. При этом в
первой таблице всегда записаны все три id, а во второй только один из трех. 


id1 может повторяться 
id2 может повторяться в рамках id2
id3 может повторяться в рамках id2


Пример дал такой, что эти три условия не соблюдаются. Кажется, проще не обращать
внимания на эту иерархию id.

Нужно эти две таблицы слить в одну, при этом:


заголовок результирующей таблицы должен быть объединением заголовков исходных таблиц. 
каждая строка-событие дополняется всеми meta-полями.
строки из meta-таблицы, не имеющие сопоставления в event-таблице, должны попасть
в результирующую таблицу с пустыми event-полями.


Все что я описал выше очень похоже на LEFT OUTER JOIN. Только вот у меня никак не
получается добиться такого же результат в ClickHouse. Либо заголовок остается от одной
из таблиц, либо записи не дополняются всеми полями.

Пример.

Таблица с метой

| id1   | id2   | id3   | meta |
|-------|-------|-------|------|
| id1_1 | id2_1 | id3_1 | m1   |
| id1_1 | id2_1 | id3_2 | m2   |
| id1_1 | id2_2 | id3_1 | m3   |
| id1_2 | id2_3 | id3_3 | m4   |
| id1_3 | id2_4 | id3_4 | m4   |


Таблица с событиями

| id1   | id2   | id3   | event |
|-------|-------|-------|-------|
| id1_1 |       |       | e1    |  
|       | id2_1 |       | e2    |  
|       |       | id3_1 | e3    |  
| id1_2 |       |       | e4    |  


Ожидаемый результат JOIN'а

| id1   | id2   | id3   | meta | event |
|-------|-------|-------|------|-------|
| id1_1 | id2_1 | id3_1 | m1   | e1    |
| id1_1 | id2_1 | id3_2 | m2   | e1    |
| id1_1 | id2_2 | id3_1 | m3   | e1    |
| id1_1 | id2_1 | id3_1 | m1   | e2    |
| id1_1 | id2_1 | id3_2 | m2   | e2    |
| id1_1 | id2_1 | id3_1 | m1   | e3    |
| id1_1 | id2_2 | id3_1 | m3   | e3    |
| id1_2 | id2_3 | id3_3 | m4   | e4    |
| id1_3 | id2_4 | id3_4 | m4   |       |

    

Ответы

Ответ 1

Судя по документации, вашу задачу можно решить следующим образом:


Создать таблицу
Вставить в нее данные соединения по id1
Вставить в нее данные соединения по id2
Вставить в нее данные соединения по id3


В SQL это было бы эквивалентом

SELECT * FROM meta m INNER JOIN events e ON m.id1=e.id1
UNION ALL
SELECT * FROM meta m INNER JOIN events e ON m.id2=e.id2
UNION ALL
SELECT * FROM meta m INNER JOIN events e ON m.id3=e.id3


Ответ 2

В ClickHouse лучше так совсем не делать и денормализовать всё до одной таблицы events.
Метаданные в зависимости от их природы либо добавлять в отдельные колонки при записи,
либо доставать при чтении с помощью механизма внешних словарей.