I want to utilize single table design in Scylla without resorting to DynamoDB. I suspected it does something non trivial to support Dynamo GSI Hash and Sort keys so I created a sample table through alternator and when I ran DESC on the keyspace I see the impossible:
CREATE TABLE alternator_dynamo_entities.dynamo_entities (
":attrs" map<text, blob>,
PRIMARY KEY ("PK", "SK")
) WITH CLUSTERING ORDER BY ("SK" ASC) ...
CREATE MATERIALIZED VIEW alternator_dynamo_entities."dynamo_entities:GSI1" AS
WHERE "GSI1PK" IS NOT NULL AND "GSI1SK" IS NOT NULL
PRIMARY KEY ("GSI1PK", "GSI1SK", "PK", "SK")
WITH CLUSTERING ORDER BY ("GSI1SK" ASC, "PK" ASC, "SK" ASC) ...
Evidently I get error trying to replicate this schema manually since only 1 extra column not present in PK is allowed. How is this possible and what should I do to be able to create alternator-like secondary hash and sort keys?
The reason why adding two base attributes to the view key was not allowed wasn’t some laziness or oversight, but rather a genuine problem that can happen if the user can manipulate the “liveness” properties of these two attributes - their timestamp and ttl - separately - and whether we can handle in such case the liveness of the view row.
So, you may be asking now, why did we allow doing this seemingly-dangerous thing in Alternator? The thinking was (and issue #6714 was created to explain this in more detail) that because Alternator does not have TTLs, and doesn’t let you control timestamps (writes always use the current timestamps, later writes always have a later timestamp), it is not vulnerable to this problem. So we allow this case in Alternator, which is lucky because we need this feature for DynamoDB compatibility.
In the aforementioned issue, I also suggested that we should “discuss whether it might be possible for a CQL user who promises to adhere to certain limitations on the TTLs or timestamps, that are similar to Alternator’s, could also be allowed to use multiple regular base columns for the view key.”. But unfortunately, this doesn’t currently exist - the code doesn’t allow you to create such table via CQL.
Thanks a ton! I stumbled upon this exact issue and even ended up down to reading the same unresolved issues with Cassandra. I understand the problem is genuine and requires user to restrict the usage of some advanced features of Scylla. Still, it would be great to eventually expose an option to allow this from CQL and remember not to touch that features. A better option would be to enforce that on DDL level so the table knows it has multiple column MVs and disables conflicting subset of CQL.