Alter Current Rowstore table into Columnstore table

Off-Topic
1

Is there any way to alter table in rowstore into columnstore?

2

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 |
+------+------+
1 Like
3

Thanks Hanson for your guidance.

4

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?

5

Leonardo,

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: