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 [email protected] 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 https://github.com/ClickHouse/clickhouse-cpp/issues/40#issuecomment-712217145, 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] |
+---------+--------------------------+--------------------+