System Commands

Metadb extends SQL with commands for system administration, configuration management, and table operations. These commands are only available when connecting to the Metadb server.

Alter System

Change a server configuration parameter.

Syntax

alter system set configuration_parameter = 'value'

Parameters

configuration_parameter

string

required

Name of a configuration parameter.

value

string

required

New value of the parameter. This is always a string constant.

Some parameters require restarting the server before they will take effect. See the Configuration Parameters reference for details on each parameter.

Available Configuration Parameters

checkpoint_segment_size

Sets the maximum number of change events processed before a checkpoint occurs.

Default: '3000'
Requires restart: Yes

alter system set checkpoint_segment_size = '5000';

max_poll_interval

Sets the timeout in milliseconds for the interval between polls of a Kafka data source.

Default: '1800000' (30 minutes)
Requires restart: Yes

alter system set max_poll_interval = '2400000';

external_sql_folio

Sets the Git reference in the folio-analytics repository for running external SQL.

Default: '' (disabled)
Requires restart: No

alter system set external_sql_folio = 'refs/tags/v1.8.0';

external_sql_reshare

Sets the Git reference in the folio-reshare repository for running external SQL.

Default: '' (disabled)
Requires restart: No

alter system set external_sql_reshare = 'refs/tags/v2.0.0';

Example

alter system set kafka_sync_concurrency = '1';

Alter Table

Change a table definition for tables extracted from data sources.

Syntax

alter table table_name action

where action is one of:
    add column column_name data_type
    alter column column_name type data_type

Parameters

table_name

string

required

Schema-qualified name of a main table.

column_name

string

required

Name of a column to add or alter.

data_type

string

required

The (new) data type of the column. Types currently supported are text and uuid.

Due to concurrency control, alter table acquires stream processing or table locks to execute safely. It automatically waits until it can continue.

Examples

Add a Column

alter table library.patrongroup__ add column description text;

Change Column Type to UUID

alter table library.patron__ alter column patrongroup_id type uuid;

List Commands

Show system configurations and variables.

Syntax

list name

Available List Commands

config

keyword

Display server configuration parameter settings.

list config;

data_mappings

keyword

Display configured data mappings.

list data_mappings;

data_origins

keyword

Display configured data origins.

list data_origins;

data_sources

keyword

Display configured data sources.

list data_sources;

status

keyword

Display current status of system components.

list status;

Example

list status;

Purge Data

Permanently delete data or database objects.

Syntax

purge data drop table table_name [, ... ]

DESTRUCTIVE OPERATION: purge data deletes data permanently. This action cannot be undone.

Parameters

table_name

string

required

The schema-qualified name of the main table to drop. Multiple tables can be specified, separated by commas.

Description

The purge data drop table command removes specified main tables. This also removes the corresponding current tables due to the parent-child relationship.

Cascading Effect: Dropping a main table (e.g., table1__) automatically drops its associated current table (e.g., table1).

Due to concurrency control, purge data drop table acquires stream processing or table locks to execute safely. It automatically waits until it can continue.

Examples

Remove a Single Table

purge data drop table library.old_table__;

Remove Multiple Tables

Remove main tables table1__ and table2__ along with their corresponding current tables:

purge data drop table library.table1__, library.table2__;

Table Naming Conventions

Main Tables
Current Tables

Main tables store the complete history of all changes:

Named with double underscore suffix: table_name__
Contain all record versions over time
Used for historical queries and time-travel

Example: library.patrongroup__

Current tables store only the latest version of each record:

Named without suffix: table_name
Contain only current state
Automatically updated from main tables
Cannot exist without the parent main table

Example: library.patrongroup

System Monitoring

Use these commands to monitor and manage your Metadb instance:

Check Configuration

-- View all configuration parameters
list config;

-- View system status
list status;

View Data Sources

-- List all configured data sources
list data_sources;

-- List all data mappings
list data_mappings;

Monitor System Activity

-- View recent log messages
select * from mdblog('1 hour');

-- View current query processes
select * from ps();

-- Check table update status
select * from metadb.table_update;

Best Practices

Configuration Changes

Always check if a configuration parameter requires a server restart. Test configuration changes in a non-production environment first.

Table Alterations

Plan table schema changes carefully. Use alter table during maintenance windows when possible, as it may need to wait for locks.

Data Purging

Double-check before purging: Always verify the table names before running purge data. Consider backing up data first. Document why data is being purged.

Regular Status Checks

Monitor system status regularly using list status and list config. This helps identify configuration drift and system issues early.

Configuration Management Workflow

Review Current Configuration

list config;

Update Parameter

alter system set checkpoint_segment_size = '5000';

Restart if Required

Check if the parameter requires a server restart. If yes, plan and execute the restart.

Verify Change

list config;

Configuration Parameters

Detailed reference for all configuration parameters

System Functions

System information and monitoring functions

System Tables

System tables for metadata and monitoring

Data Sources

Configure and manage data sources

CLI Commands

SQL Extensions

System Functions

System Tables

Configuration

​Alter System

​Syntax

​Parameters

​Available Configuration Parameters

​Example

​Alter Table

​Syntax

​Parameters

​Examples

​Add a Column

​Change Column Type to UUID

​List Commands

​Syntax

​Available List Commands

​Example

​Purge Data

​Syntax

​Parameters

​Description

​Examples

​Remove a Single Table

​Remove Multiple Tables

​Table Naming Conventions

​System Monitoring

​Check Configuration

​View Data Sources

​Monitor System Activity

​Best Practices

​Configuration Management Workflow

​See Also

Configuration Parameters

System Functions

System Tables

Data Sources

Build docs developers (and LLMs) love

Alter System

Syntax

Parameters

Available Configuration Parameters

Example

Alter Table

Syntax

Parameters

Examples

Add a Column

Change Column Type to UUID

List Commands

Syntax

Available List Commands

Example

Purge Data

Syntax

Parameters

Description

Examples

Remove a Single Table

Remove Multiple Tables

Table Naming Conventions

System Monitoring

Check Configuration

View Data Sources

Monitor System Activity

Best Practices

Configuration Management Workflow

See Also