多对多在Cassandra 3 [英] Many-to-many in Cassandra 3

问题描述

在Cassandra中建模多对多关系的正确方法是什么(目前使用3.10)?

What's the right way to model many-to-many relationships in Cassandra (using 3.10 at the moment)?

从我能找到的答案中，建议将归一化为两个关系表(例如，在这里，例如:

From what answers I was able to find, denormalization into two relationship tables is suggested (as in here, for example: Modeling many-to-many relations in Cassandra 2 with CQL3). But there are problems with that on deletes, and those answers are so sparse they do not mention any details on that.

假设我们有以下表格:

CREATE TABLE foo (
  key UUID PRIMARY KEY,
  content TEXT
)
CREATE TABLE bar (
  key UUID PRIMARY KEY,
  content TEXT
)
CREATE TABLE foo_bar (
  foo UUID,
  bar UUID,
  PRIMARY KEY (foo, bar)
)
CREATE TABLE bar_foo (
  bar UUID,
  foo UUID,
  PRIMARY KEY (bar, foo)
)

这似乎是建议的答案.但是，当我们尝试删除bar记录时会发生什么?更新bar_foo表很容易:

This seems to be the suggested answer. However, what happens when we try deleting a bar record? Updating the bar_foo table is easy:

DELETE FROM bar_foo WHERE bar = <bar_key>

但是，尝试更新foo_bar表失败:

However, an attempt to update the foo_bar table fails:

DELETE FROM foo_bar WHERE bar = <bar_key>

，出现以下错误:

InvalidRequest: Error from server: code=2200 [Invalid query] message="Some partition key parts are missing: foo"

这是因为foo_bar表的主键是(foo, bar)，并且我们在DELETE语句的WHERE子句中仅指定了主键的第二部分.显然，Cassandra需要主键的前缀，而没有foo的bar不是前缀.

This is because the primary key for the foo_bar table is (foo, bar), and we specify only the second part of the primary key in the WHERE clause of the DELETE statement. Cassandra, apparently, requires a prefix of the primary key, and bar without foo is not a prefix.

现在，将主键更改为(bar, foo)将无济于事.毕竟，如果foo记录被删除，您会怎么做?而且，无论如何，foo_bar表的全部目的是能够找到与给定foo记录相对应的所有bar记录，并且SELECT语句也需要前缀WHERE子句中的主键(必须必须为foo).

Now, changing the primary key to (bar, foo) won't help. After all, what would you do, then, if a foo record gets deleted? And, in any case, the entire purpose of the foo_bar table is to be able to find all bar records corresponding to a given foo record, and a SELECT statement also requires a prefix of the primary key in the WHERE clause (which must, by necessity, be foo).

也不能执行SELECT，然后执行DELETE，因为bar的SELECT无效，因为它不是主键的前缀.

Can't do SELECT and then DELETE, either, since a SELECT by bar won't work, it not being a prefix of the primary key.

当图片中出现删除时，如何处理多对多关系?甚至有可能正确完成吗?

So what to do with many-to-many relationships, when deletes are in the picture? Is this even possible to accomplish properly?

推荐答案

使用集合. https://docs.datastax.com/en/cql/3.3/cql/cql_using/useSet.html

CREATE TABLE foo (
  key UUID PRIMARY KEY,
  content TEXT
)
CREATE TABLE bar (
  key UUID PRIMARY KEY,
  content TEXT
)
CREATE TABLE foo_jn_bar (
  foo UUID PRIMARY KEY,
  bar set<UUID>
)
CREATE TABLE bar_jn_jn (
  bar UUID PRIMARY KEY,
  foo set<UUID>
)

如果遵循关系习惯，那么磁盘数据将有很大的重复性.

If you follow the relational habit, you will have huge duplicity in disk data.

这篇关于多对多在Cassandra 3的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！