We learned in the YugabyteDB Tip View Metadata for YSQL/YCQL/System Tablets on a Server about the new (in YugabyteDB 2024.1) system view yb_local_tablets.
The view has two really informative columns:
| COLUMN | TYPE | DESCRIPTION |
|---|---|---|
| partition_key_start | bytea | Start key of the partition (inclusive). |
| partition_key_end | bytea | End key of the partition (exclusive). |
These fields, in conjunction with the yb_hash_code function in YSQL or the partition_hash function in YCQL, can be used to determine which Tablet a row in a Table belongs.
But first we need to convert the BYTEA values in the yb_local_tablets view to INTEGER because both the yb_hash_code and partition _hash functions return integer values. For convienece and reusability, I do that in a Database View….
CREATE OR REPLACE VIEW yb_local_tablets_vw AS
SELECT *,
COALESCE((('x'||encode(partition_key_start,
'hex'))::BIT(16)::INT), 0) partition_key_start_int,
COALESCE((('x'||encode(partition_key_end,
'hex'))::BIT(16)::INT), 65536) partition_key_end_int
FROM yb_local_tablets;
yugabyte=# CREATE OR REPLACE VIEW yb_local_tablets_vw AS
yugabyte-# SELECT *,
yugabyte-# COALESCE((('x'||encode(partition_key_start,
yugabyte(# 'hex'))::BIT(16)::INT), 0) partition_key_start_int,
yugabyte-# COALESCE((('x'||encode(partition_key_end,
yugabyte(# 'hex'))::BIT(16)::INT), 65536) partition_key_end_int
yugabyte-# FROM yb_local_tablets;
CREATE VIEW
yugabyte=# CREATE TABLE test (id INT PRIMARY KEY, c1 TEXT);
CREATE TABLE
yugabyte=# SELECT tablet_id, partition_key_start, partition_key_end, partition_key_start_int, partition_key_end_int FROM yb_local_tablets_vw WHERE table_name = 'test' ORDER BY partition_key_start_int;
tablet_id | partition_key_start | partition_key_end | partition_key_start_int | partition_key_end_int
----------------------------------+---------------------+-------------------+-------------------------+-----------------------
ea03d811897e4ead902f3c016c264434 | | \x5555 | 0 | 21845
6243cd4762f049779fbfdef4c3d765a0 | \x5555 | \xaaaa | 21845 | 43690
30c6127c8c194888b1f93bc85a0d2338 | \xaaaa | | 43690 | 65536
(3 rows)
Now we can easily map each row in the TEST table to the Tablet it is stored.
yugabyte=# \d test
Table "public.test"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
c1 | text | | |
Indexes:
"test_pkey" PRIMARY KEY, lsm (id HASH)
yugabyte=# INSERT INTO test SELECT g, 'A' FROM generate_series(1, 10) g;
INSERT 0 10
yugabyte=# SELECT *
yugabyte-# FROM (SELECT test.*,
yugabyte(# CASE
yugabyte(# WHEN yb_hash_code(id) >= partition_key_start_int AND yb_hash_code(id) < partition_key_end_int THEN
yugabyte(# y.tablet_id
yugabyte(# END tablet_id
yugabyte(# FROM test
yugabyte(# CROSS JOIN yb_local_tablets_vw y
yugabyte(# WHERE y.table_name = 'test') foo
yugabyte-# WHERE tablet_id IS NOT NULL
yugabyte-# ORDER BY id;
id | c1 | tablet_id
----+----+----------------------------------
1 | A | ea03d811897e4ead902f3c016c264434
2 | A | 30c6127c8c194888b1f93bc85a0d2338
3 | A | 30c6127c8c194888b1f93bc85a0d2338
4 | A | 6243cd4762f049779fbfdef4c3d765a0
5 | A | ea03d811897e4ead902f3c016c264434
6 | A | ea03d811897e4ead902f3c016c264434
7 | A | ea03d811897e4ead902f3c016c264434
8 | A | 30c6127c8c194888b1f93bc85a0d2338
9 | A | 6243cd4762f049779fbfdef4c3d765a0
10 | A | 6243cd4762f049779fbfdef4c3d765a0
(10 rows)
Have Fun!
