CODEWITHSUNDEEP
cassandracql
Arjun Karnwal
Arjun Karnwal

Reputation: 379

Cassandra where clause as a tuple

      Table12
CustomerId   CampaignID
  1              1
  1              2
  2              3
  1              3
  4              2                   
  4              4
  5              5

val CustomerToCampaign = ((1,1),(1,2),(2,3),(1,3),(4,2),(4,4),(5,5))

Is it possible to write a query like

select CustomerId, CampaignID  from Table12 where (CustomerId, CampaignID) in (CustomerToCampaign_1, CustomerToCampaign_2)

???

So the input is a tuple but the columns are not tuple but rather individual columns.

Upvotes: 1

Views: 404

Answers (1)

Aaron
Aaron

Reputation: 57748

Sure, it's possible. But only on the clustering keys. That means I need to use something else as a partition key or "bucket." For this example, I'll assume that marketing campaigns are time sensitive and that we'll get a good distribution and easy of querying by using "month" as the bucket (partition).

CREATE TABLE stackoverflow.customertocampaign (
    campaign_month int,
    customer_id int,
    campaign_id int,
    customer_name text,
    PRIMARY KEY (campaign_month, customer_id, campaign_id)
);

Now, I can INSERT the data described in your CustomerToCampaign variable. Then, this query works:

aploetz@cqlsh:stackoverflow> SELECT campaign_month, customer_id, campaign_id
                             FROM customertocampaign WHERE campaign_month=202004 
                             AND (customer_id,campaign_id) = (1,2);

 campaign_month | customer_id | campaign_id
----------------+-------------+-------------
         202004 |           1 |           2

(1 rows)

Upvotes: 2

Related Questions