Cassandra销售数据表建模、数据迁移及聚合查询问题求助

Hey there! Let's break down your two core problems and walk through fixes tailored to Cassandra's design principles.

1. Why Your COPY Command Isn't Importing All Data #

The root issue here is your table's primary key design:

PRIMARY KEY (store_nbr)

In Cassandra, the primary key uniquely identifies a row. When you set store_nbr as the sole primary key, every row with the same store_nbr will overwrite the previous one during import. That's exactly why your row count is off—you're only keeping one row per store, not all 10 million sales records.

Fix for Full Data Import #

Adjust the primary key to include a unique identifier for each sales record (like id) as a clustering column. This ensures every row is unique and won't be overwritten:

DROP TABLE IF EXISTS sales;
CREATE TABLE sales (
    id int,
    date date,
    item_nbr int,
    store_nbr int,
    unit_sales decimal,
    PRIMARY KEY (store_nbr, id) -- store_nbr as partition key, id as clustering key
);

Now when you run your COPY command, all 10 million rows will be imported successfully. You can verify with:

SELECT COUNT(*) FROM sales;

2. Why Aggregation Queries Are Slow (And How to Fix It) #

Cassandra is built for fast, targeted lookups—not ad-hoc OLAP-style aggregations like full-table SUM() queries. When you run SELECT store, sum(unit_sales) FROM sales GROUP BY store, Cassandra has to scan every single row across all partitions, which is painfully slow for 10 million records.

The Cassandra-Friendly Solution: Pre-Aggregate Your Data #

Instead of calculating sums on the fly, pre-compute and store the aggregated values. This aligns with Cassandra's "query-first" design philosophy.

Step 1: Create an Aggregation Table

Make a table dedicated to storing total sales per store:

CREATE TABLE store_sales_totals (
    store_nbr int PRIMARY KEY,
    total_unit_sales decimal
);

Step 2: Populate the Aggregation Table

You have a few options to calculate and insert the totals:

• Use CQL Batch + Counter (for real-time ingestion): If you're adding sales data incrementally, use a counter column to update totals as new sales come in.
• Use a Batch Job (for existing data): Use tools like Apache Spark, a Python script, or even cqlsh with post-processing to calculate sums per store from your original CSV or the sales table, then insert into store_sales_totals.

Example Spark SQL snippet (if you have Spark integrated with Cassandra):

INSERT INTO store_sales_totals
SELECT store_nbr, SUM(unit_sales) AS total_unit_sales
FROM sales
GROUP BY store_nbr;

Step 3: Fast Aggregation Queries

Now your query is lightning-fast—just look up the pre-computed total:

SELECT store_nbr, total_unit_sales FROM store_sales_totals;

Or filter for specific stores:

SELECT total_unit_sales FROM store_sales_totals WHERE store_nbr = 123;

Bonus: Retain Detailed Sales + Fast Aggregations #

If you need to keep individual sales records but want fast aggregations, consider a materialized view (note: use cautiously with large datasets, as initial creation may take time):

CREATE MATERIALIZED VIEW store_sales_mv AS
SELECT store_nbr, SUM(unit_sales) AS total_unit_sales
FROM sales
GROUP BY store_nbr
PRIMARY KEY (store_nbr);

Cassandra will automatically update this view as you modify rows in the sales table.

内容的提问来源于stack exchange，提问作者Jonatan Duran Cazar