Skip to main content
Metadb allows you to schedule external SQL files to run on a regular basis. This feature is particularly useful for creating and maintaining derived tables based on data from your sources.

​
Overview

External SQL execution is automatically enabled when the “folio” or “reshare” module is specified in your data source configuration. The feature allows you to:
  • Run SQL files on a scheduled basis
  • Create and maintain derived tables
  • Track execution status in system tables
Details such as the location of SQL files and scheduling are currently hardcoded but will be configurable in future releases.

​
Enabling external SQL

To enable external SQL, configure the appropriate Git reference using the alter system command: 
alter system set external_sql_folio = 'refs/tags/v1.8.0';
Setting either parameter to an empty string ('') disables external SQL execution for that repository.

​
SQL file format

When writing external SQL files, follow these formatting rules:

​
Statement separation

Separate each SQL statement with an empty line: 
drop table if exists library_patrons;

create table library_patrons as
select * from library.patron;

​
Schema names

Do not specify schema names when creating tables. Metadb manages schema placement automatically.
-- Correct
create table library_patrons as ...

-- Incorrect
create table my_schema.library_patrons as ...

​
Directives

Directives are special comment lines that control Metadb’s behavior. Each directive must:
  • Begin with --metadb:
  • Appear on its own line
  • Be followed by an empty line before the SQL statements

​
—metadb:table

The --metadb:table directive declares that the SQL file updates a specific table. This enables Metadb to track the update status in the metadb.table_update system table. Syntax: 
--metadb:table <table_name>
​
table_name
string
required
The name of the table being updated. Do not include a schema name.
Example: 
--metadb:table library_patrons

drop table if exists library_patrons;

create table library_patrons as
select
    p.id,
    p.barcode,
    pg.group_name,
    p.active
from library.patron p
left join library.patrongroup pg on p.patrongroup_id = pg.id;
Always include the --metadb:table directive at the beginning of your SQL files to enable status tracking.

​
Complete example

Here’s a complete example showing proper formatting and directive usage: 
--metadb:table user_group

drop table if exists user_group;

create table user_group as
select
    id,
    group_name,
    description,
    created_date
from library.usergroup__
where active = true;

create index idx_user_group_name on user_group(group_name);
  1. Directive line: --metadb:table user_group declares the target table
  2. Empty line: Required separator
  3. Drop statement: Removes existing table if present
  4. Empty line: Statement separator
  5. Create statement: Defines the new table and its data
  6. Empty line: Statement separator
  7. Index creation: Additional optimizations

​
Monitoring execution

Track the status of external SQL execution using the metadb.table_update system table: 
select
    schema_name,
    table_name,
    last_update,
    elapsed_real_time
from metadb.table_update
order by last_update desc;
This query shows:
  • When each table was last updated
  • How long the update took to complete
  • Which schema and table were affected
See System tables for more information.

​
Best practices

Use directives

Always include --metadb:table at the start of each file for proper tracking

Separate statements

Use empty lines between SQL statements for correct parsing

Avoid schemas

Don’t specify schema names in table creation statements

Monitor execution

Check metadb.table_update to verify successful execution

​
Troubleshooting

Verify that the configuration parameter is set:
list config;
Ensure the value is not an empty string ('') and points to a valid Git reference.
Confirm that your SQL file includes the --metadb:table directive at the beginning, followed by an empty line.
Check that:
  • Each statement is separated by an empty line
  • Schema names are not included in table creation
  • The directive line is followed by an empty line

Configuration parameters

Learn about external_sql_folio and external_sql_reshare parameters

System tables

Query execution status in metadb.table_update

Build docs developers (and LLMs) love

Get started for freeTalk to us