如何获得卡桑德拉最后插入的行？ [英] How to get last inserted row in Cassandra?

问题描述

我想获取Cassandra表中最后插入的行。如何获得？任何的想法？

I want to get last inserted row in Cassandra table. How to get it? Any idea?

我正在开发一个项目，用cassandra代替mysql。我想摆脱所有sql查询，并将它们全部写在cassandra中。

I am developing a project for that I am replacing mysql with cassandra. I want to get rid off all sql queries and writing them all in cassandra.

推荐答案

只不过是要多加一点理解...

Just to impart a little understanding...

与所有Cassandra查询问题一样，该查询需要由专门为其设计的模型来服务。这称为基于查询的建模。查询最后插入的行并不是每个表都内置的固有功能。您需要设计模型来提前支持它。

As with all Cassandra query problems, the query needs to be served by model specifically designed for it. This is known as query-based modeling. Querying the last inserted row is not an intrinsic capability built into every table. You would need to design your model to support that ahead of time.

例如，假设我有一个表为用户存储数据。

For instance, let's say I have a table storing data for users.

CREATE TABLE users (
  username TEXT,
  email TEXT,
  firstname TEXT,
  lastname TEXT,
  PRIMARY KEY (username));

如果我要运行 SELECT * FROM users LIMIT 1 username （我的分区键）的那一行，因为Cassandra就是这样在集群中存储数据的。我无法知道它是否是最后一个添加的，所以这对您来说不是很有用。

If I were to run a SELECT * FROM users LIMIT 1 on this table, my result set would contain a single row. That row would be the one containing the lowest hashed value of username (my partition key), because that's how Cassandra stores data in the cluster. I would have no way of knowing if it was the last one added or not, so this wouldn't be terribly useful to you.

另一方面，我有一个用来跟踪用户对其帐户信息进行的更新的表。

On the other hand, let's say I had a table designed to track updates that users had made to their account info.

CREATE TABLE userUpdates (
  username TEXT,
  lastUpdated TIMEUUID,
  email TEXT,
  firstname TEXT,
  lastname TEXT,
  PRIMARY KEY (username,lastUpdated))
WITH CLUSTERING ORDER BY (lastUpdated DESC);

接下来，我将向上插入3行：

Next I'll upsert 3 rows:

> INSERT INTO userUpdates (username,lastUpdated,email,firstname,lastname) 
  VALUES ('bkerman',now(),'[e-mail]','Bob','Kerman');
> INSERT INTO userUpdates (username,lastUpdated,email,firstname,lastname) 
  VALUES ('jkerman',now(),'[e-mail]','Jebediah','Kerman');
> INSERT INTO userUpdates (username,lastUpdated,email,firstname,lastname) 
  VALUES ('bkerman',now(),'[e-mail]','Bob','Kerman');

> SELECT username, email, dateof(lastUpdated) FROM userupdates;

 username | email             | system.dateof(lastupdated)
----------+-------------------+----------------------------
  jkerman |   [e-mail] |   2016-02-17 15:31:39+0000
  bkerman | [e-mail] |   2016-02-17 15:32:22+0000
  bkerman |   [e-mail] |   2016-02-17 15:31:38+0000

(3 rows)

如果我只是 SELECT用户名，电子邮件，dateof（lastUpdated）FROM userupdates LIMIT 1 ，我将得到Jedediah Kerman的数据，该数据不是最近更新的。但是，如果我将分区限制为 username ='bkerman'，而 LIMIT 1 ，我将获得最多

If I just SELECT username, email, dateof(lastUpdated) FROM userupdates LIMIT 1 I'll get Jedediah Kerman's data, which is not the most-recently updated. However, if I limit my partition to username='bkerman', with a LIMIT 1 I will get the most-recent row for Bob Kerman.

> SELECT username, email, dateof(lastUpdated) FROM userupdates WHERE username='bkerman' LIMIT 1;

 username | email             | system.dateof(lastupdated)
----------+-------------------+----------------------------
  bkerman | [e-mail] |   2016-02-17 15:32:22+0000

(1 rows)

之所以可行，是因为我在 lastUpdated 上指定了降序的集群顺序：

This works, because I specified a clustering order of descending on lastUpdated:

WITH CLUSTERING ORDER BY (lastUpdated DESC);

通过这种方式，将返回每个分区内的结果，并在顶部将最近更新的行返回，因此 LIMIT 1 成为查询最近行的方法。

In this way, results within each partition will be returned with the most-recently upserted row at the top, hence LIMIT 1 becomes the way to query the most-recent row.

总而言之，理解：

• Cassandra通过分区键的哈希值对集群中的数据进行排序。这有助于确保更均匀的数据分配。


• Cassandra 集群命令强制在磁盘中对数据进行磁盘排序。 em>分区键。


• 虽然您将无法获得每个表最近更新的行，但您可以设计模型以返回该行

• Cassandra orders data in the cluster by the hashed value of a partition key. This helps ensure more-even data distribution.
• Cassandra CLUSTERING ORDER enforces on-disk sort order of data within a partition key.
• While you won't be able to get the most-recently upserted row for each table, you can design models to return that row to you for each partition.

tl; dr; Cassandra与MySQL或任何RDBMS有很大不同。如果需要查询最后一个向上插入的行（用于分区），则可能有几种方法可以对表进行建模以支持它。

tl;dr; Querying in Cassandra is MUCH different from that of MySQL or any RDBMS. If querying the last upserted row (for a partition) is something you need to do, there are probably ways in which you can model your table to support it.

这篇关于如何获得卡桑德拉最后插入的行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！