Clickhouse - create table is always a table in memory, How do I create table with a specific engine - MergeTree

[M-Kinsman]

Hi,

I'm trying to write code that generates tables on the fly when they don't exist.

Problem is they always end up being of type memory.

I know using straight SQL will fix this but, I'm unsure of how to either

A: Generate the SQL with the specific properties needed

or B: specific the type of engine on creation

This is my current version (I have tried this with AsClickhouse())

using the Clickhouse.Client as provider

` _connection = connection;

        var schemaProvider = _connection.DataProvider.GetSchemaProvider();
        var dbSchema = schemaProvider.GetSchema(_connection);
        if (!dbSchema.Tables.Any(t => t.TableName == typeof(T).Name))
        {
            _connection.CreateTable<T>().WithTableExpression("ENGINE=MergeTree");

}`

and I end up always with Memory

which means my data keeps disappearing after a while.

Any help or advice would be appreciated, thank you.

end result if possible:

`-- drop developerlog table and recreate with mergetree
DROP TABLE IF EXISTS DeveloperLog;

CREATE TABLE DeveloperLog
(
Id UUID,
Message Nullable(String),
Source Nullable(String)
)
ENGINE = MergeTree
ORDER BY (Id);`

I haven't been able to find any docs or code examples for this situation

[igor-tkachev]

You need to define a PrimaryKey for your table.

But I think we need to add explicit API.

[MaceWindu]

Duplicate #4206

[M-Kinsman]

Thank you both, I did see #4206 which was what led me to attempting the tableexpressions - I misread the issue there.

But, to fix my original issue, it is definitely the need to add attributes like primarykey and columns. Thanks for your help.