Nested tables support

[Ryssiouk11B]

If I have simple table like this:

CREATE TABLE test.nested
(
account UInt64,
Orders Nested(
id UInt64,
order_id String)
)
ENGINE = MergeTree
ORDER BY account

account │ UInt64
Orders.id │ Array(UInt64)
Orders.order_id │ Array(String)

How I can insert data into this table using clickhouse-cpp API? I’m working with Orders.id and Orders.order_id columns as arrays but when method block.AppendColumn called, I’m getting exception:
all columns in block must have same count of rows.

Is there any existing workaround or plans to extend API for nested tables support?

[rpopescu]

@Ryssiouk11B is there a solution for this?
tagging also @Enmk
much appreciated!

[Ryssiouk11B]

I’m using Clickhouse Restful API to insert data into nested tables. You can use python if it is possible. Python Clickhouse API can work with nested tables.

…

On Oct 19, 2020, at 10:47 AM, rpopescu ***@***.***> wrote: @Ryssiouk11B <https://github.com/Ryssiouk11B> is there a solution for this? tagging also @Enmk <https://github.com/Enmk> much appreciated! — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#40 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AOE66LICUOT3CN7PMIYS25DSLRGPJANCNFSM4NGANFJA>.

[rpopescu]

No, that's not an option. We need to push a lot of data in the shortest time possible (for example, reusing blocks, appending lots of rows before doing an insert), and also consume it in the shortest time possible and in an optimal format.

[romanholidaypancakes]

hi, is this still unusable?

[den818]

I try to write a simple test. And it works. Could you describe the case in more detail?

    client_->Execute("DROP TEMPORARY TABLE IF EXISTS test_clickhouse_nested;");
    client_->Execute("CREATE TEMPORARY TABLE IF NOT EXISTS test_clickhouse_nested "
        " (account UInt64, Orders Nested(id UInt64, order_id String) )");
    auto account = std::make_shared<ColumnUInt64>();
    account->Append(1);
    account->Append(2);
    auto orders_id = std::make_shared<ColumnArrayT<ColumnUInt64>>();
    orders_id->Append({1, 2, 3, 4, 5});
    orders_id->Append({10, 20, 30, 40, 50, 60});
    auto orders_order_id = std::make_shared<ColumnArrayT<ColumnString>>();
    orders_order_id->Append({"a", "b", "c", "d", "e"});
    orders_order_id->Append({"A", "B", "C", "D", "E", "F"});

    Block b;
    b.AppendColumn("account", account);
    b.AppendColumn("Orders.id", orders_id);
    b.AppendColumn("Orders.order_id", orders_order_id);

    client_->Insert("test_clickhouse_nested", b);

    client_->Select("SELECT * FROM test_clickhouse_nested",
        [](const Block& block) {
            std::cerr << PrettyPrintBlock{block} << std::endl;
        }
    );

+---------+--------------------------+--------------------+
| account |     Orders.id            | Orders.order_id    |
+---------+--------------------------+--------------------+
|  UInt64 | Array(UInt64)            |   Array(String)    |
+---------+--------------------------+--------------------+
|       1 | [1, 2, 3, 4, 5]          | [a, b, c, d, e]    |
|       2 | [10, 20, 30, 40, 50, 60] | [A, B, C, D, E, F] |
+---------+--------------------------+--------------------+

[iso01]

I need to support arbitrary level of nested structures. Lets complicated above Orders example with yet another nested layer:

test_clickhouse_nested (account UInt64, Orders Nested(id UInt64, order_id String, Items Nested(id UInt64, description String)))

What code should I write in this case?

[iso01]

If I add

client_->Execute("set flatten_nested=0;");

then a table with arbitrary levels of nested types can be created.
However if try a code inserting a tuple into nested column, I get unsupported exception:

Caught exception: unsupported column type: Nested(id UInt64, order_id String)

Presumably the nested columns are not supported by the c++ client at all.

The case with flatten_nested=1 and replacing true nested types by arrays is not what I need.