When you look at YugabyteDB web console, or some yb-admin output, you will see your YSQL tables identified with a table_id or uuid. Here is an example:
This Table ID is important because, even if the YSQL table or index name is displayed as "Table Name", and the YSQL database as "Keyspace", this is not sufficient to identify a table, an index or a partition. This is because the storage in DocDB is common to all APIs. It doesn't know the YSQL schema, or the relation type. And YCQL tables are also there.
Table OID
From the screenshot above, identifying the YSQL table is easy because the "Table OID" is displayed. In this example, I see that the table 00004001000030008000000000004002 is the categories table from the yb_demo_northwind database, with the OID 16386:
yugabyte=# \c yb_demo_northwind
psql (13.5, server 11.2-YB-2.13.1.0-b0)
You are now connected to database "yb_demo_northwind" as user "yugabyte".
select
current_database(), schema, relname, relkind, am, owner, tablespace
from ( -- pg_class -> table or index
select relkind, relname, relnamespace, reltype, relowner, relam, reltablespace from pg_class
where oid=16386
) as c natural left join ( -- pg_namespace -> schema
select oid relnamespace, nspname as schema from pg_namespace
) as n natural left join ( -- pg_authid -> owner
select oid relowner, rolname as owner from pg_authid
) as o natural left join ( -- pg_am -> access method
select oid relam, amname am from pg_am
) as a natural left join ( -- spcname pg_tablespace -> tablespace
select oid reltablespace, spcname as tablespace from pg_tablespace
) as t;
current_database | schema | relname | relkind | am | owner | tablespace
-------------------+--------+------------+---------+----+----------+------------
yb_demo_northwind | public | categories | r | | yugabyte |
UUID
But you may also need to find the table_id from a YSQL table. This is a UUID is built from the keyspace (which is the PostgreSQL database in the case of YSQL) and the table (with the PostgreSQL OID of the relation in the case of YSQL).
Let's decompose my table_id above: 00004001000030008000000000004002 to understand how it is build.
00004001-0000-3000-8000-000000004002
^^^^ -> PostgreSQL Database OID
^ -> UUID Version 3 = name based MD5
^ -> UUID Variant = DCE 1.1, ISO/IEC
^^^^ -> PostgreSQL Relation OID
The code explains it is Repurpose old name-based UUID v3 to embed Postgres oids
OID
This is how I get the table_id from PostgreSQL for my categories table, in my current database, found with the current search_path:
yb_demo_northwind=#
select format('0000%s00003000800000000000%s'
,lpad(to_hex(oid::int), 4, '0')
,lpad(to_hex('categories'::regclass::oid::int),4,'0')
) table_id from pg_database where datname=current_database()
table_id
----------------------------------
00004001000030008000000000004002
(1 row)
I use this, for example, to get directly to the tablet definition as http://yb-tmaster-0:7000/table?id=00004001000030008000000000004002
This endpoint has can also find tablets with a keyspace_name and table_id instead of the id but this works only for YCQL where there is no schema.
yb-admin
Knowing the Table ID is also useful for yb-admin commands, prefixing UUID with tableid.:
$ /home/opc/yugabyte-2.13.1.0/bin/yb-admin list_tablets tableid.00004001000030008000000000004002
Tablet-UUID Range Leader-IP Leader-UUID
e4216871ffd644c3b616d37668400212 partition_key_start: "" partition_key_end: "UU" 10.0.0.61:9100 1e6aa7f085d146e8b29207483592116a
85f1061e6f10410888cbbf6418d7047c partition_key_start: "UU" partition_key_end: "\252\252" 10.0.0.63:9100 9eec605aede445e98ad61bf833dbf6cc
2884fe988f304591b2905e5272a571f9 partition_key_start: "\252\252" partition_key_end: "" 10.0.0.62:9100 4729e36c42934175817c580b52ea6a18
The list_tablets can also work with YSQL table names by prefixing the keyspace with ysql.:
]$ /home/opc/yugabyte-2.13.1.0/bin/yb-admin list_tablets ysql.yb_demo_northwind categories
Tablet-UUID Range Leader-IP Leader-UUID
e4216871ffd644c3b616d37668400212 partition_key_start: "" partition_key_end: "UU" 10.0.0.61:9100 1e6aa7f085d146e8b29207483592116a
85f1061e6f10410888cbbf6418d7047c partition_key_start: "UU" partition_key_end: "\252\252" 10.0.0.63:9100 9eec605aede445e98ad61bf833dbf6cc
2884fe988f304591b2905e5272a571f9 partition_key_start: "\252\252" partition_key_end: "" 10.0.0.62:9100 4729e36c42934175817c580b52ea6a18
However, this works only when there's only one table with this name. If I create another one in another schema:
yb_demo_northwind=# create schema franck;
CREATE SCHEMA
yb_demo_northwind=# create table franck.categories (a int);
CREATE TABLE
yb_demo_northwind=#
The same yb-admin command will now fail to identify the DocDB table by name:
$ /home/opc/yugabyte-2.13.1.0/bin/yb-admin -init_master_addrs=10.0.0.61:7100 list_tablets ysql.yb_demo_northwind categories
Error running list_tablets: Invalid argument (yb/tools/yb-admin_client.cc:406): Namespace 'ysql.yb_demo_northwind' has multiple tables named 'categories', specify table id instead
In short
The combination of keyspace and table names can identify a table only for YCQL. With YSQL there are multiple namespaces within a table, which are the PostgreSQL schemas. You need to use the Table ID, a UUID constructed from the PostgreSQL object identifier (OID) to identify a YSQL relation in DocDB. The queries above can be used for this purpose.
I did some maths with my 14 years old kid during the week-end, and I can now tell you that the function that maps YSQL tables to DocDB tables by name is non-injective. You need to map by UUID to get an injective function. It is still non-surjective if you consider all UUIDs in DocDB because it also holds tables for YCQL. But from an UUID Version 3 it is a bijection, and that's why you can use it in the URL and yb-admin. Other tables, like YCQL or system ones, are UUID Version 4. This is easy to distinguish from the 13th digit in the hexadecimal representation of the Table ID
Top comments (0)