Hey all,
I’m working on porting a Rowstore table into a Columnstore table, while using the INSERT IGNORE INTO query, a continuation from this older thread of mine:
My original table was created under as a Rowstore using the following:
CREATE TABLE IF NOT EXISTS rs_list (id CHAR(42), name TEXT, type1 TEXT, type2 TEXT, type3 TEXT, type4 TEXT, type5 TEXT, SHARD KEY (id,name,type1,type2,type3,type4,type5), UNIQUE KEY (id,name,type1,type2,type3,type4,type5));
The reason for wanting to convert this into a Columnstore is the data we want to pull far exceeds the amount of RAM available. I am however receiving the following error when attempting to recreate this as a Columnstore with the below settings:
CREATE TABLE IF NOT EXISTS rs_list (id CHAR(42), name TEXT, type1 TEXT, type2 TEXT, type3 TEXT, type4 TEXT, type5 TEXT, KEY (id,name,type1,type2,type3,type4,type5) USING CLUSTERED COLUMNSTORE, SHARD KEY (id,name,type1,type2,type3,type4,type5), UNIQUE KEY (id,name,type1,type2,type3,type4,type5));
ERROR 1706 ER_MEMSQL_FEATURE_LOCKDOWN: Feature ‘UNIQUE index without USING HASH on columnstore table’ is not supported by MemSQL
I then attempted to add USING HASH after the UNIQUE KEY section, and received this new error:
ERROR 1706 ER_MEMSQL_FEATURE_LOCKDOWN: Feature ‘UNIQUE HASH index on columnstore table with multiple columns’ is not supported by MemSQL.
If at all possible, please advise on how to properly create Columnstore tables that are able to take full advantage of the INSERT IGNORE INTO query, while using unique columns.