How do we create global secondary index on set types?

ScyllaDB
1

Hi good people,

I was trying to create secondary index on a column of type “set” but not sure if its possible.

for example, I have a table as follows:

CREATE KEYSPACE company WITH replication = {'class': 'NetworkTopologyStrategy', 'datacenter1': '1'}  AND durable_writes = true;

CREATE TABLE employee (
    id bigint PRIMARY KEY,
    name text,
    address text,
    department text,
    subordinate_ids set<bigint>);

here, I want to create secondary index / materialized views on subordinate_ids .

please suggest a way on how this can be achieved.

Best regards,
Raghu

2

Scylla does support global secondary indexes on collection columns, starting with ScyllaDB 5.2. This is currently in the process of being released, currently at RC3. I suggest taking it for a spin trying out this new feature.

3

Thanks for the reply @Botond_Denes … could you please share any document regarding creating secondary indexes with collection type?

4

See our general documentation on secondary indexes here: Global Secondary Indexes | ScyllaDB Docs and scylladb/secondary_index.md at master · scylladb/scylladb · GitHub.

For a column of set type, the create statement looks something like this:

CREATE INDEX employee_by_subordinate_ids ON company.employee (VALUES(subordinate_ids));
5

worked like a charm! thanks @Botond_Denes

I had to get rid of . in index name for it to work though…

6

Thanks, I edited my answer, can you please check if it is correct now?

7

works as it is now, thanks @Botond_Denes