使用CQL 3.0在集合上创建自定义索引 [英] Creating a custom index on a collection using CQL 3.0

问题描述

我一直在关注CQL 3.0 数据建模文档，其描述具有标签的列族歌曲，创建如下：

I have been looking at the CQL 3.0 data modelling documentation which describes a column family of songs with tags, created like this:

CREATE TABLE songs (
    id uuid PRIMARY KEY,
    title text,
    tags set<text>
);

我想获得所有具有特定标签的歌曲的列表，因此我需要添加一个合适的索引。

I would like to get a list of all songs which have a specific tag, so I need to add an appropriate index.

我可以轻松地在 title 列创建索引，但如果我尝试索引标签列，如下所示：

I can create an index on the title column easily enough, but if I try to index the tags column which is a collection, like this:

CREATE INDEX ON songs ( tags );

我从DataStax Java驱动程序1.0.4得到以下错误：

I get the following error from the DataStax Java driver 1.0.4:

Exception in thread "main" com.datastax.driver.core.exceptions.InvalidQueryException: Indexes on collections are no yet supported
at com.datastax.driver.core.exceptions.InvalidQueryException.copy(InvalidQueryException.java:35)
at com.datastax.driver.core.ResultSetFuture.extractCauseFromExecutionException(ResultSetFuture.java:269)

看起来这可能会根据JIRA问题 CASSANDRA-4511 。我目前使用的是Apache Cassandra 1.2.11，但不想升级。根据问题 CASSANDRA-5615 虽然，在Cassandra 1.2.6有是 对 的支持。

It looks like this may be fixed in a later version of Cassandra (2.1) according to JIRA issue CASSANDRA-4511. I am currently using Apache Cassandra 1.2.11 however, and do not want to upgrade yet. According to issue CASSANDRA-5615 though, in Cassandra 1.2.6 there is support for custom indexes on collections.

问题是，只有文档可用状态：

The problem is, the only documentation available states:

Cassandra支持创建自定义索引，这是内部使用的，超出了本文档的范围。

Cassandra supports creating a custom index, which is for internal use and beyond the scope of this document.

但 建议使用以下语法：

CREATE CUSTOM INDEX ON songs ( tags ) USING 'class_name';

指定的 class_name 这个CQL语句？

What is the class_name that is specified in this CQL statement?

有更好的索引标签的方法，以便我可以查询歌曲一个特定的标签？

Is there a better way of indexing the tags so that I can query the songs table for a list of songs that have a specific tag?

推荐答案

您尝试这样做的方式不是在Cassandra视图。您可以根据查询而不是数据构建模型。如果你需要根据标签找到歌曲，那么你制作另一个表，并复制数据。像... ...

The way you are trying to do this isn't the best way to model it within Cassandra in my view. You build models based on your queries, not your data. If you need to find songs based by tag, then you make another table for that and duplicate the data. Something like ...

CREATE TABLE tagged_songs (
  tag varchar,
  song_id uuid,
  song_title varchar,
  ... anything else you might need with your songs here ...
  PRIMARY KEY ((tag), song_id)
);

Cassandra的前提是存储便宜。复制您的数据以满足您的查询。写入速度快，写入相同数据3,4,10次通常很好。

The premise in Cassandra is that storage is cheap. Duplicate your data to meet your queries. Writes are fast, and writing the same data 3,4,10 times is normally fine.

您还希望将歌曲标题和您需要的任何其他信息存储在此表格中。你不想获取一个ID的加载，并在阅读时尝试加入它。这不是关系数据库。

You also want to store your song title and any other info you need into this table. You don't want to grab a load of IDs and try join on it when reading. This isn't a relational DB.

当有人为歌曲添加标签时，您可能需要将标签插入到集合中， tagged_songs表。查询具有标签X的所有歌曲基本上是O（1）。

When someone tags a song, you might want to insert the tag into the set as you have it as present, AND add it to the tagged_songs table too. Querying for all songs with tag X is then basically O(1).

这篇关于使用CQL 3.0在集合上创建自定义索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！