Is there any way to alter table in rowstore into columnstore?
You can’t alter a rowstore to make it a columnstore directly, but you can do it like this:
memsql> create table rs(a int, b int); Query OK, 0 rows affected (0.06 sec) memsql> insert rs values(1,2); Query OK, 1 row affected (0.08 sec) memsql> create table cs(a int, b int, key(a) using clustered columnstore); Query OK, 0 rows affected (0.11 sec) memsql> insert into cs select a, b from rs; Query OK, 1 row affected (0.15 sec) Records: 1 Duplicates: 0 Warnings: 0 memsql> drop table rs; Query OK, 0 rows affected (0.04 sec) memsql> alter table cs rename rs; Query OK, 0 rows affected (0.58 sec) Records: 0 Duplicates: 0 Warnings: 0 memsql> select * from rs; +------+------+ | a | b | +------+------+ | 1 | 2 | +------+------+
Thanks Hanson for your guidance.
My Question related to these commands…
Imagine we have a rowstore table rs like you showed above.
we ingest a table in a temp table like rs_temp which we create during ingestion like:
create table rs_temp like rs;
If the rs table is ROWSTORE table, what would be the best way to set the above temp table as COLUMSTORE with same layout of fields/datatypes?
Welcome to the forums!
That’s really a different question that the one for this thread. Would you mind opening a new topic on this one? You can paste this answer for starters:
You can do it like this, if you want to make a columnstore out of an unindexed rowstore as simply as possible:
memsql> create temporary table t(a int, b varchar(80)); Query OK, 0 rows affected (0.10 sec) memsql> create table t_cs(a int, b varchar(80), key() using clustered columnstore); Query OK, 0 rows affected (0.05 sec) memsql> insert into t_cs select * from t; Query OK, 0 rows affected (0.15 sec)
But if you have indexes and keys, need to do upserts, etc., the answer gets more involved. The notation
key() using clustered columnstore
makes a columnstore table that has no sort key. This is the simplest approach to making a columnstore that requires the least design thought. But if you want to get the best possible segment elimination for, say, filters on a datetime (event time) column, you should put a sort key on that. See this topic on how to choose a sort key: