Creating a table as copy of rowstore table but as columnstore instead

Help
1

Imagine we have a rowstore table:

create table FINAL(a int, b varchar(80));

show create table FINAL;

CREATE TABLE FINAL (
a int(11) DEFAULT NULL,
b varchar(80) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL
/*!90618 , SHARD KEY () */
);

We usually use a TEMP table to receive Apache spark jobs ingestions, so we usually copy the table structure using: CREATE TABLE FINAL_TEMP LIKE FINAL;

But the command CREATE TABLE … LIKE creates the same way as ROWSTORE table. If we need to have the FINAL_TEMP table with same structure of FINAL table (rowstore) but the FINAL_TEMP table to be columnstore instead, is it possible to use the command:

  • CREATE TABLE FINAL_TEMP (key() using clustered columnstore) LIKE FINAL;
    or even

  • CREATE TABLE FINAL_TEMP (key() using clustered columnstore) AS SELECT * FROM FINAL;

If the FINAL table is ROWSTORE table, what would be the best way to set the above FINAL_TEMP table as COLUMSTORE with same layout of fields/datatypes?

2

probably need to manually write out the fields and add a key() using clustered columnstore. show create table final may be your friend here.

we’re actively interested in improving the UX in this area. i’d probably vote for something like

create columnstore table final_temp like final```
3

Uhn, declaring fields is not going to be straighforward programatically thinking of a generic code to ingest data in any table using Spark, for example to copy the final table structure as a _temp table and then to load data from a dataframe.

Definitely the option to use CREATE COLUMNSTORE TABLE final_temp like final; would be amazing. And coding perspective, i would define the key() using clustered columnstore with the PK fields from the original table (if it is ROWSTORE table) else i would try to copy the existing KEY() fields from the original table (if it is COLUMNSTORE table).

Also we figured out that CREATE TEMPORARY TABLE xxxx LIKE xxx final_cs; (if the final_cs is originally created as COLUMNSTORE) will fail with error: Exception in thread “main” java.sql.SQLException: Feature ‘Columnstore temporary table’ is not supported by MemSQL.

That is also a new feature interesting to be added to the next version of MemSQL.

4

Your last example (create table as select) should work. Did you get an error?

MemSQL [(none)]> use test;
Database changed
MemSQL [test]> create table t(c1 int);
Query OK, 0 rows affected (0.44 sec)

MemSQL [test]> create table c(key() using clustered columnstore) as select * from t;
Query OK, 0 rows affected (0.29 sec)

MemSQL [test]> show create table c;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                       |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| c     | CREATE TABLE `c` (
  `c1` int(11) DEFAULT NULL,
  KEY `__UNORDERED` () /*!90619 USING CLUSTERED COLUMNSTORE */
  /*!90618 , SHARD KEY () */ 
) /*!90623 AUTOSTATS_CARDINALITY_MODE=INCREMENTAL, AUTOSTATS_HISTOGRAM_MODE=CREATE, AUTOSTATS_SAMPLING=ON */ /*!90623 SQL_MODE='STRICT_ALL_TABLES' */ |
5

Yes, it definetely works… checked the code below in my environment and it created…

create table qas2_internal_class.contract_headers_temp(key() using clustered columnstore)
as
select *
from qas2_internal_class.contract_headers
where 1=0;

That solves my need, thanks!