1

I have a table with schema

CREATE TABLE traffic ( date Date, val1 UInt64, 'val2' UInt64 ... ) ENGINE = ReplicatedMergeTree(date, (val1, val2), 8192);

the partition key is date here. I want to change the order from (val1, val2) to (val2, val1)

I only way i know is rename this table to someting(traffic_temp), create table with name 'trafic' and ordering (val2, val1) and copy the data from temp to traffic and then delete the temp table.

But the dataset is huge, is there any better way to do it??

Tomyhill
  • 79
  • 1
  • 2
  • 10

1 Answers1

0

No other way. Only insert select.

You can use clickhouse-copier but it does the same insert select

Denny Crane
  • 11,574
  • 2
  • 19
  • 30
  • One follow up question. Let's say my table has ordering in fashion Order(val1, val2, val3) now most of my select query works when keys are provided in the above fashion(for 90 % of the use case, this is perfect) but for other 10% case the user do not have value of starting key(val1) and is providing val3. So the performance is obviously bad, but let's say if I want to cater this also without changing the schema?? Is it somewhat possible??? I want to somewhat cater both cases and a bit better performance for second use case. – Tomyhill Nov 24 '20 at 19:56
  • The cardinality of val3 is relatively high(around 20k-40k )that is why it was kept last in the schema ordering. – Tomyhill Nov 24 '20 at 20:11
  • But can I make the performance of second use case a bit better! – Tomyhill Nov 24 '20 at 20:40
  • You can try skip indexes for val3. Sometimes (very rare) skip index can help. I use additional tables in this case to emulate inverted indexes. – Denny Crane Nov 24 '20 at 20:42
  • i tried skipping index, yes performance wise i did not see any gains but number of rows processed is almost reduced to half, i believe that is helpful in reducing IO and cpu consumption right?? – Tomyhill Nov 25 '20 at 07:53