SMACK架构下Cassandra如何按时间戳获取最新N条数据？

Got it, let's break down your problem and fix this properly—Cassandra's query model is all about designing tables around the queries you need, so your current schema isn't optimized for that "get latest 50 records" ask. Here's what's going wrong and how to fix it:

Why Your Original Query Failed #

First, let's clarify the error you hit:

ORDER BY is only supported when the partition key is restricted by an EQ or an IN

Your current table uses uuid as the sole primary key (which makes it the partition key). That means every row lives in its own partition. Cassandra can't sort across partitions efficiently—so when you tried to ORDER BY date without restricting the partition key, it threw that error. Even if you switched to time, duplicates in time mean you can't use it as a standalone clustering key (since primary keys must be unique).

The Solution: Design a Table for "Latest Records" Queries #

Since you need to pull the most recent 50 entries, create a denormalized table (totally acceptable in Cassandra) optimized specifically for this query. Here's how:

Step 1: Create the Optimized Table #

We'll use a fixed partition key to group all records you want to query for "latest" data, then combine time with uuid as clustering keys (to handle duplicate time values and ensure uniqueness):

CREATE TABLE fireman_latest (
    grouping_key text,
    time timestamp,
    uuid uuid,
    date text,
    heartrate int,
    id text,
    location text,
    ratecommunication int,
    temperature int,
    PRIMARY KEY (grouping_key, time, uuid)
) WITH CLUSTERING ORDER BY (time DESC, uuid DESC);

• grouping_key: A fixed value (like all_firemen) that puts all relevant records into one partition. This lets Cassandra sort within the partition efficiently.
• time DESC: Ensures new records are stored at the top of the partition.
• uuid DESC: Acts as a tiebreaker for rows with the same time (since uuid is unique, this guarantees the primary key is unique).

Step 2: Write Data to the New Table #

When inserting or updating fireman data, write to both your original fireman table (for uuid-based queries) and this new fireman_latest table. For the grouping_key, always use the same value (e.g., all_firemen):

INSERT INTO fireman_latest (grouping_key, time, uuid, date, heartrate, id, location, ratecommunication, temperature)
VALUES ('all_firemen', '2024-05-20T14:30:00', uuid(), '2024-05-20', 75, 'fireman_123', 'Station 5', 90, 37);

In your SMACK stack, you can use Spark Streaming or Kafka Connect to automate this dual-write if you're ingesting data via Kafka.

Step 3: Query the Latest 50 Records #

Now fetching the newest entries is straightforward—no ALLOW FILTERING needed (which is a good thing, since ALLOW FILTERING is slow for large datasets):

SELECT * FROM fireman_latest WHERE grouping_key = 'all_firemen' LIMIT 50;

This query will instantly return the 50 most recent records, sorted by time (newest first) and uuid for ties.

Bonus: Scaling for Large Datasets #

If you expect millions of records, a single partition might get too big. In that case, use a time-based grouping key (like daily_group set to 2024-05-20 for a single day). To get the latest 50 records, you'd query the current day's partition first, and if you don't get 50 results, fall back to the previous day's partition. This keeps partitions manageable while still letting you fetch recent data efficiently.

内容的提问来源于stack exchange，提问作者Max0u