Custom partitioning.

[Алексей]

I saw latest release (ClickHouse release 1.1.54310) and i was wondering how i can implement "custom partitions" in ClickHouse, so... 

After few attempts to create a table with custom partition key, ClickHouse server noticed to me this error 

"Extended storage definition syntax (PARTITION BY, ORDER BY, SAMPLE BY and SETTINGS clauses) is disabled. Enable it with experimental_allow_extended_storage_definition_syntax user setting".

Help us to understand, how we can  set this flag. Thanks.

[va...@percona.com]

I have it enabled by having following config in config.xml

<profiles>

    <!-- Default settings -->

    <default>

        <experimental_allow_extended_storage_definition_syntax>1</experimental_allow_extended_storage_definition_syntax>

    </default>

</profiles>

[Алексей]

Thanks for your response. Now it works.

Be care, experimental_allow_extended_storage_definition_syntax = 1 is not equal experimental_allow_extended_storage_definition_syntax = true.

[tdingus]

I wonder if you could point out what I am missing: 

I did add the setting(below) in clickhouse-server/config.xml

    <profiles>

      <!-- Default settings -->

      <default>

        <experimental_allow_extended_storage_definition_syntax>1</experimental_allow_extended_storage_definition_syntax>

      </default>

    </profiles>

But I still got, "Code: 0. DB::Exception: Received from localhost:9000, ::1. DB::Exception: Extended storage definition syntax (PARTITION BY, ORDER BY, SAMPLE BY and SETTINGS clauses) is disabled. Enable it with experimental_allow_extended_storage_definition_syntax user setting."

I use clickhouse version version 1.1.54313.

Thanks

[Jason Piper]

Start a CLI sessios via the clickhouse client (i.e. run "clickhouse client") and then run 

SET experimental_allow_extended_storage_definition_syntax = 1;

Then put your CREATE TABLE command in.

[tdingus]

It works. Thanks.

I wonder if it is a bug that the config file doesn't work.

[tdingus]

Comparing custom partition by-day and by-month, do we expect any noticeable differences in data ingestion performance?

[Alex Zatelepin]

If your INSERT batches contain data belonging to X partitions (days), then one insert is equivalent to X inserts into a single partition, so ingestion performance may degrade accordingly. If you mostly insert data into a single partition, there shouldn't be any difference.

[va...@percona.com]

I just tried loading a file with 15bln rows.

With daily partitioning, loading time:

 347m28.575s

With monthly partitioning:

 245m1.150s

[tdingus]

Thanks for testing it!

[Kuo]

if you want to set this as default, add this setting in users.xml, not config.xml

<yandex>

  <profiles>

    <default>  <experimental_allow_extended_storage_definition_syntax>1</experimental_allow_extended_storage_definition_syntax>

    </default>

  </profiles>

</yandex>

在 2017年11月25日星期六 UTC+8下午10:31:29，tdingus写道：