YugabyteDB Tips
  • Home
  • About
  • Featured Videos

Use the YB_LOCAL_TABLETS Sytem View to Map Table Rows to their Tablets

YugabyteDB Tips > Meta-Data > Use the YB_LOCAL_TABLETS Sytem View to Map Table Rows to their Tablets

Use the YB_LOCAL_TABLETS Sytem View to Map Table Rows to their Tablets

Jim KnicelyAugust 5, 2024

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!

It was amazing to watch this duck family navigate around the enormous amount of lily pads
Meta-Data, System Tables, Tablets, YCQL, ysql

Post navigation

Presplit a Materilized View into a Specific Number of Tablets
Navigate Wide Query Output in YSQLSH Using Your Left and Right Arrow Keys



YugabyteDB Tips

Copyright 2025 - Knicely/Li