Skip to main content

INFORMATION_SCHEMA

Gnok ships the SQL-standard INFORMATION_SCHEMA views for catalog introspection — the same surface BI tools, JDBC drivers, and schema-diff scripts expect. Every view is backed by the live catalog (Iceberg metadata for tables / views, in-memory registries for policies) so results reflect reality at query time.

Three rules​

INFORMATION_SCHEMA is per-catalog. There are three ways to scope it:

-- 1. Three-part reference (preferred — explicit, no session state)
SELECT * FROM my_cat.information_schema.tables LIMIT 5;

-- 2. Set a session catalog first
USE CATALOG my_cat;
SELECT * FROM information_schema.tables LIMIT 5;

-- 3. Filter by table_catalog (engine infers + lazy-loads it)
SELECT table_name
FROM information_schema.tables
WHERE table_catalog = 'my_cat' AND table_schema = 'sales';

Form 3 is convenient when no session catalog is set: the engine recognises an information_schema query and extracts the catalog name from the WHERE table_catalog = '<X>' (or catalog_name = '<X>' on schemata) filter, lazy-loading it if needed. If the WHERE has no catalog filter and no session default is registered, the engine falls back to any registered catalog so the bind succeeds (the materialiser walks the catalog registry rather than reading from one specific catalog's metadata).

Catalog-qualifier scoping. When a query uses the three-part form (<catalog>.information_schema.<view>), the materialiser only reads from that catalog, not from every catalog you can access. Use the qualifier to keep introspection on one catalog.

2. Filter with WHERE​

All five views are ordinary tables you query with standard SQL. There is no parameterised-function form today (the TABLE(INFORMATION_SCHEMA.POLICY_REFERENCES(POLICY_NAME => …)) table-function spelling isn't supported yet); use WHERE instead:

SELECT policy_name
FROM my_cat.information_schema.policy_references
WHERE ref_entity_name = 'orders'
  AND policy_kind     = 'ROW_ACCESS_POLICY';

When the table-function grammar ships, existing WHERE-filtered queries keep working unchanged — it'll be additive sugar.

3. JOINs, aggregates, and subqueries work​

IS views materialise into inline data at plan time, and the engine routes scan-free plans through a single-node execution path — so WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, aggregates, scalar subqueries, and JOIN across IS views all work without special-casing.

View catalog​

ViewRowsPurpose
tablesone per tableBase tables in the catalog
columnsone per columnPer-column type metadata
schemataone per schemaNamespace listing
viewsone per viewView listing (body currently NULL — see gaps)
policy_referencesone per bindingRow-access + masking policy-to-object bindings

information_schema.tables​

Lists every base table, view, and materialized view in the catalog.

ColumnTypeDescription
table_catalogVARCHARCatalog name
table_schemaVARCHARSchema (namespace)
table_nameVARCHARTable name
table_typeVARCHARBASE TABLE, VIEW, or MATERIALIZED VIEW
table_ownerVARCHAROwner principal (auto-stamped from JWT user on CREATE TABLE; nullable for legacy tables)
row_countBIGINTApproximate row count from Iceberg manifests (nullable)
bytesBIGINTApproximate byte size (nullable)
createdVARCHARISO-8601 UTC timestamp — auto-stamped on CREATE TABLE (nullable for legacy tables)
commentVARCHARTable comment (nullable)
rls_enabledBOOLEANTRUE iff at least one row-access policy is bound to the table

Row types​

  • BASE TABLE — physical Iceberg table (CREATE TABLE …).
  • VIEW — logical view (CREATE VIEW …); body retrievable via SHOW CREATE VIEW.
  • MATERIALIZED VIEW — managed materialised view (CREATE MATERIALIZED VIEW …). The MV's underlying storage table is not double-counted as a separate BASE TABLE row.

Auto-stamped fields​

Every fresh CREATE TABLE populates two fields automatically:

  • table_owner — taken from the authenticated JWT's preferred_username (username claim). Override at create time via TBLPROPERTIES ('owner' = 'someone-else').
  • created — chrono::Utc::now().to_rfc3339() stamped under both created_at (Gnok Catalog convention) and created-at (Iceberg property name) so reads via either key succeed. Override via TBLPROPERTIES ('created-at' = '2024-01-01T00:00:00Z').

Examples​

-- Every table + view in a specific schema, with type
SELECT table_name, table_type, row_count
FROM my_cat.information_schema.tables
WHERE table_schema = 'sales'
ORDER BY table_type, table_name;

-- Top 10 schemas by base-table count
SELECT table_schema, COUNT(*) AS n
FROM my_cat.information_schema.tables
WHERE table_type = 'BASE TABLE'
GROUP BY table_schema
ORDER BY n DESC
LIMIT 10;

-- Tables protected by row-level security (editor sidebar pattern)
SELECT table_schema, table_name
FROM my_cat.information_schema.tables
WHERE rls_enabled
ORDER BY table_schema, table_name;

information_schema.columns​

Per-column metadata for every table in the catalog.

ColumnTypeDescription
table_catalogVARCHARCatalog name
table_schemaVARCHARSchema
table_nameVARCHARTable name
column_nameVARCHARColumn name
ordinal_positionBIGINT1-based column index in the table
data_typeVARCHARSQL type (e.g., INT, VARCHAR, DECIMAL(10, 2), TIMESTAMP, ARRAY<...>) — same formatter DESCRIBE uses
is_nullableVARCHARYES / NO
column_defaultVARCHARDefault expression — populated from CREATE TABLE col TYPE DEFAULT expr (nullable)
numeric_precisionBIGINTFor DECIMAL / NUMERIC types (nullable)
numeric_scaleBIGINTFor DECIMAL / NUMERIC types (nullable)
commentVARCHARColumn comment from CREATE TABLE col TYPE COMMENT 'x' or ALTER TABLE … ALTER COLUMN col SET COMMENT 'x' (nullable)
is_primary_keyBOOLEANTRUE iff the column participates in the table's PRIMARY KEY (column-level or composite)

data_type uses the same SQL-style formatter as DESCRIBE so the two surfaces agree (INT not Int32, VARCHAR not Utf8, DECIMAL(10, 2) not Decimal128(10, 2)). Column names follow the SQL standard (column_name, is_nullable, column_default) — DESCRIBE keeps its shorter friendly aliases (col_name, nullable, default).

-- Column list for one table
SELECT column_name, data_type, is_nullable, comment, is_primary_key
FROM my_cat.information_schema.columns
WHERE table_schema = 'sales' AND table_name = 'orders'
ORDER BY ordinal_position;

-- Every PRIMARY KEY column in the catalog
SELECT table_schema, table_name, column_name
FROM my_cat.information_schema.columns
WHERE is_primary_key
ORDER BY table_schema, table_name, ordinal_position;

-- Every DECIMAL column with its precision
SELECT table_schema, table_name, column_name, numeric_precision, numeric_scale
FROM my_cat.information_schema.columns
WHERE data_type LIKE 'DECIMAL%';

information_schema.schemata​

Namespace listing — one row per schema.

ColumnTypeDescription
catalog_nameVARCHARCatalog name
schema_nameVARCHARSchema (namespace)
schema_ownerVARCHAROwner principal (nullable)
default_character_set_nameVARCHARSQL-spec placeholder; always UTF8 (nullable)
SELECT schema_name FROM my_cat.information_schema.schemata;

information_schema.views​

View listing — one row per view.

ColumnTypeDescription
table_catalogVARCHARCatalog name
table_schemaVARCHARSchema
table_nameVARCHARView name
view_definitionVARCHARView body (currently NULL — follow-up)
is_updatableVARCHARAlways NO — Gnok views are read-only
SELECT table_schema, table_name
FROM my_cat.information_schema.views
ORDER BY table_schema, table_name;
VIEW_DEFINITION

view_definition is always NULL today because the catalog adapter's TableRef doesn't carry the view body. Use SHOW CREATE VIEW <view> to retrieve the SQL text; a future update will populate the column.

information_schema.policy_references​

The reverse-lookup surface for row-access and masking policies. Each row binds one policy to one object (or column).

ColumnTypeDescription
policy_dbVARCHARPolicy's catalog (mirrors target today; NULL for masking rows)
policy_schemaVARCHARPolicy's schema (NULL for masking rows)
policy_nameVARCHARPolicy name
policy_kindVARCHARROW_ACCESS_POLICY or MASKING_POLICY
ref_database_nameVARCHARTarget catalog
ref_schema_nameVARCHARTarget schema
ref_entity_nameVARCHARTarget table
ref_entity_domainVARCHARTABLE today
ref_column_nameVARCHARMasked column (masking rows only, otherwise NULL)
ref_arg_column_namesVARCHARColumns the policy body references (currently NULL — follow-up)

Query patterns​

-- Studio catalog-tree badge: does this table have a row-access policy?
SELECT COUNT(*) > 0 AS has_rls
FROM my_cat.information_schema.policy_references
WHERE ref_database_name = 'my_cat'
  AND ref_schema_name   = 'sales'
  AND ref_entity_name   = 'orders'
  AND policy_kind       = 'ROW_ACCESS_POLICY';

-- Every column-level mask in the catalog
SELECT policy_name, ref_schema_name, ref_entity_name, ref_column_name
FROM my_cat.information_schema.policy_references
WHERE policy_kind = 'MASKING_POLICY'
ORDER BY ref_schema_name, ref_entity_name, ref_column_name;

-- Where is policy 'pci_mask' applied?
SELECT ref_schema_name, ref_entity_name, ref_column_name
FROM my_cat.information_schema.policy_references
WHERE policy_name = 'pci_mask';

-- Tables with an active row-access policy plus their column count
SELECT t.table_schema, t.table_name, COUNT(DISTINCT c.column_name) AS cols
FROM my_cat.information_schema.tables t
JOIN my_cat.information_schema.columns c
  ON  c.table_catalog = t.table_catalog
  AND c.table_schema  = t.table_schema
  AND c.table_name    = t.table_name
JOIN my_cat.information_schema.policy_references p
  ON  p.ref_database_name = t.table_catalog
  AND p.ref_schema_name   = t.table_schema
  AND p.ref_entity_name   = t.table_name
  AND p.policy_kind       = 'ROW_ACCESS_POLICY'
GROUP BY t.table_schema, t.table_name;

Prefer POLICY_REFERENCES over SHOW ROW POLICIES and SHOW MASKING POLICIES whenever you need joins, filtering, or tool interop — the SHOW variants stay available for interactive inspection of policy bodies and enabled flags.

Client access​

All three client protocols work identically — IS is just SQL.

# HTTP API
curl -s -X POST https://query.example.com/api/query \
  -H "Authorization: Bearer $TOKEN" \
  -H 'Content-Type: application/json' \
  -d '{"sql": "SELECT table_name FROM my_cat.information_schema.tables LIMIT 5"}'
# PostgreSQL wire protocol
psql "host=pg.example.com port=5433 user=my_user dbname=my_catalog sslmode=verify-full" \
  -c "SELECT policy_name FROM my_cat.information_schema.policy_references"
# Flight SQL (any ADBC / JDBC driver) — same SQL, same result

JDBC / ODBC clients also hit the PostgreSQL-compatible pg_catalog views; see the Postgres wire reference for the mapping.

Known gaps​

  • views.view_definition is always NULL. Use SHOW CREATE VIEW <v> to retrieve the body. The catalog's TableRef doesn't carry the text yet; this is a follow-up.
  • policy_references.ref_arg_column_names is always NULL. Parsing row-access expressions to enumerate referenced columns is future work.
  • No tag-based policy inheritance tracked yet. When tag-scoped policies ship, policy_references will gain tag_* lineage columns — an additive change.
  • Cold-start behaviour on policy_references. Row-access and masking registries hydrate lazily from persistent storage on the first authenticated request per engine process. A brand-new coordinator connection that queries policy_references before any policy DDL has been evaluated may return empty. It self-heals as soon as any DDL touches a policy-bearing table. Treat empty as "unknown" rather than "no policies" on the first connection.
  • No table-function syntax yet. A TABLE(INFORMATION_SCHEMA.POLICY_REFERENCES(POLICY_NAME => …)) table-function form is planned as additive sugar; WHERE filtering works today.

See also​

Last updated on