Feature Support: Generic Query, How to get schema and data while query?

[lshoo]

Hi, Clickhouse is an OLAP database and I want to make a generic query engine where users can enter any query SQL and return query results, including returning schema and data, and then the application layer displays the data. When querying, the user does not have to specify the data type.

The query result is a two-dimensional data, where one array is the column name and the other data is also a two-dimensional data, e.g., but the schema is not fixed

{
    "schema": ["id","name", "age","birthday"],
    "data": [
        ["20"， ”james1, "18", "2000-01-01"],
        ["21"， ”james2, "19", "2000-01-01"],
        ["22"， ”james2, "10", "2000-01-01"],
        ["23"， ”james4, "30", "2000-01-01"],
        ["24"， ”james5, "26", "2000-01-01"]
        ...
    ]
}

How to do that? thanks.

[lshoo]

Hi @loyd Can you answer this question? thanks

[loyd]

Sorry for the late response, there were health problems.

Relates to https://github.com/loyd/clickhouse.rs/issues/44, but for SELECTs instead of INSERTs. This crate doesn't provide support for dynamic columns and whether it will be supported is questionable (because the crate is based on types). You can use https://github.com/suharev7/clickhouse-rs instead.

[lshoo]

Thanks for your response。 I used the https://github.com/suharev7/clickhouse-rs before， but that don‘t support decimail38+，and amlost stop update now。

[loyd]

Sadly to hear it.

If you need only SELECTs, it theoretically can be done now, but it's a little tricky:

1. Need to request a schema
2. Need to write a struct over Vec<Value> (value should be from serde_value, serde_json or valuable crates).
3. The struct must implement Deserialize by calling methods according to the fetched schema.

Anyway, after https://github.com/loyd/clickhouse.rs/issues/10 the codec will implement deserialize_any and it will be possible just to use some special Value (dedicated to SQL types). At first sight, implementing deserialize_any is not so hard, maybe several evenings, I need to look it closer. However, it will work slower than typed variants.