How to Return a Specific Tablet with a YCQL Query

Question

Using YCQL, what would I need to add to a query for the query to return the specific tablet that the results are on?

[Disclaimer]: This question was first asked on the YugabyteDB Community Slack channel.

score 2 · Answer 1 · answered Apr 30 '21 at 13:45

To determine which partition a row lands, you can feed the partition columns (in the primary key) to the partition_hash function and get back the hash code.

ycqlsh:k> create table t(k text primary key, v text);
ycqlsh:k> insert into t(k, v) values ('a', '1');
ycqlsh:k> insert into t(k, v) values ('b', '2');
ycqlsh:k> insert into t(k, v) values ('c', '3');
ycqlsh:k> select partition_hash(k), k, v from t;
partition_hash(k) | k | v
-------------------+---+---
            27916 | c | 3
            44389 | a | 1
            60372 | b | 2

This would have to be matched with the information in system.partitions table to determine which partition/tablet a particular hash code will land in. [See sample query further down.] Here's another example with a multi-column primary key, where only the user_id column is the partition column.

ycqlsh:k> CREATE TABLE IF NOT EXISTS msgs(
          user_id     text,
          msg_id integer,
          msg    text,
          PRIMARY KEY((user_id), msg_id)
       );
ycqlsh:k> insert into msgs(user_id, msg_id, msg) VALUES ('A', 1, 'foo');
ycqlsh:k> insert into msgs(user_id, msg_id, msg) VALUES ('A', 2, 'bar');
ycqlsh:k> insert into msgs(user_id, msg_id, msg) VALUES ('A', 3, 'jar');
ycqlsh:k> insert into msgs(user_id, msg_id, msg) VALUES ('B', 1, 'foo');
ycqlsh:k> insert into msgs(user_id, msg_id, msg) VALUES ('B', 2, 'bar');
ycqlsh:k> insert into msgs(user_id, msg_id, msg) VALUES ('B', 3, 'jar');
ycqlsh:k> select partition_hash(user_id), user_id, msg_id, msg from msgs;
partition_hash(user_id) | user_id | msg_id | msg
-------------------------+---------+--------+-----
                  44013 |       A |      1 | foo
                  44013 |       A |      2 | bar
                  44013 |       A |      3 | jar
                  52014 |       B |      1 | foo
                  52014 |       B |      2 | bar
                  52014 |       B |      3 | jar
(6 rows)

The value returned by partition_hash is in the [0..64K) space, and to map these back to tablets, the system.partitions table can be queried. For example,

ycqlsh:k> select * from system.partitions where keyspace_name = 'k';
keyspace_name | table_name | start_key | end_key | id                                   | replica_addresses
---------------+------------+-----------+---------+--------------------------------------+-------------------------
            k |       msgs |        0x |  0x7fff | 58097f2e-967f-d785-3a42-f647b498ef08 | {'127.0.0.1': 'LEADER'}
            k |       msgs |    0x7fff |      0x | f7f2a64e-5bf2-fbba-554f-d26008c02bcc | {'127.0.0.1': 'LEADER'}
            k |          t |        0x |  0x7fff | 15363091-6577-9aa6-584c-64304cec7f6e | {'127.0.0.1': 'LEADER'}
            k |          t |    0x7fff |      0x | 0d4e2bcc-ac97-bd9e-8148-7bedb7c4bd86 | {'127.0.0.1': 'LEADER'}

When you utilize the partition_hash, after you query system.partitions, you would just need to find where the partition_hash value falls within the range of the start and end keys in the system.partitions response. Remember, start_key is inclusive, end_key is exclusive.

How to Return a Specific Tablet with a YCQL Query

1 Answers1