Metadb 1.0 Documentation

Metadb extends PostgreSQL with features to support analytics such as streaming data sources, data model transforms, and historical data. The data contained in the Metadb database originally come from another place: a data source which could be, for example, a transaction-processing database or a sensor network. Metadb updates its database continuously based on state changes in external data sources.

Tables generated by Metadb have at least these metadata columns, with names that begin with two underscores:

A main table in Metadb has two underscores at the end of its name (e.g., patrongroup__), and it contains both the current state and the history of all previous states provided by the data source. For instance, consider a main table that stores categories of patrons in a library:

In this example, the "undergrad" group with id = 15 has two rows because it was modified on 2022-04-18 19:27:18-00, changing description from 'Student' to 'Undergraduate Student'.

It is often desirable to limit a query to retrieving only current records. This can be done by filtering on __current = TRUE, for example:

For convenience, since this filter is so often used, Metadb provides access to a current table which contains only current records. Every main table has a corresponding current table, which shares the same name but without the two underscores at the end. For instance, the following query is equivalent to the example above:

A current table reflects the current state of each row in the data source, as of the last time the row was updated.

To take another example:

There is a distinction between the __id and id columns. The __id column is a key defined by Metadb to identify each row uniquely; it is present in all tables generated by Metadb. In contrast, the id column only happens to be present in this sample table and has been provided by the data source; it may or may not be a key, and its name, content, or significance may change if schema changes are made in the data source.

Metadb applies transformations to some kinds of data, which results in additional tables being created. One category of data that are transformed in this way is JSON objects. Suppose that our patrongroup current table contains the groupname and description data in JSON fields, for example:

The JSON fields can be selected using SQL:

However, Metadb also generates a transformed table called patrongroup__t. Transformed tables based on JSON objects contain JSON fields extracted into columns, which allows for simpler queries.

In the current version of Metadb, only top-level, scalar JSON fields are extracted into transformed tables.

Main tables are also transformed in the same way. In this case the main transformed table would be called patrongroup__t__.

Note that JSON data are treated as "schemaless," and fields are inferred from their presence in the data rather than read from a JSON schema. As a result, a column is only created from a JSON field if the field is present in at least one JSON record.

To summarize the types of tables that we have covered:

In addition to these table types, some partition tables have names that begin with zzz___. It is recommended that these tables not be used directly in queries, and access to them is generally disabled.

In general, users do not have privileges to modify or create tables in the database. However, Metadb creates a schema for each user where the user does have these privileges, and this serves as an individual workspace. The schema has the same name as the user name. This can be useful as an area for saving the results of queries or importing external data sets.

When using one’s own workspace, it is not necessary to specify the schema name; it will be used by default if no schema is specified.

For example, this table westgroup created by user flopsy will be created in schema flopsy:

Unless configured differently, other users do not have privileges to access the table by default.

Metadb v1.0.6 To grant the user mopsy read-only access to the schema and table:

The mopsy user will have to include the schema name to access the table:

Database views are not supported and should not be created in a Metadb database.

Views can create dependencies that may block required schema changes during data updates. They also hide query complexity, which in analytical workloads can be significant.

Metadb v1.0.5 To show the current Metadb version:

Information about when certain tables were last updated is available in a table called metadb.table_update:

Metadb reports errors and other information in a logging table called metadb.log. For instance to view all log messages from January, 2023:

Metadb v1.0.5 The mdblog() function is provided for convenience and returns ordered log messages, from the past 24 hours by default (or optionally from a specified interval):

When executing a long-running query, the ps() function can be used to check on the status of the query process from another session:

Metadb v1.0.5 Metadb allows scheduling external SQL files to run on a regular basis.

Each SQL statement should be separated from others by an empty line, and any tables created should not specify a schema name.

Comment lines beginning with --metadb: are used for additional directives; each directive should be on a separate line.

It is suggested that each SQL file optionally begin with a --metadb:table directive, optionally followed by --metadb:require directives if needed, and then followed by an empty line, for example:

Metadb extends SQL with statements for configuring and administering the server. These statements are only available when connecting to the Metadb server (not the database).

First set the GOPATH environment variable to specify a path that can serve as the build workspace for Go, e.g.:

Then install goyacc:

Then to build the server:

The build.sh script creates a bin/ subdirectory and builds the metadb executable there:

It is suggested that a metadb user be created and the server run by that user, for example, in /home/metadb.

Metadb makes use of local storage in a data directory which is created using metadb with the init command. In this example we will create the data directory as data/:

This will also create a file metadb.conf in the data directory with the following structure:

This file should be edited to add database connection parameters, for example:

Metadb will assume that the database, superuser, and systemuser defined here already exist; so they should be created before continuing.

It is essential to make regular backups of Metadb and to test the backups.

In general, persistent data are stored in the database, and therefore the database is the most important system that should be backed up often.

The data directory contains the metadb.conf configuration file and is also used for temporary storage. The metadb.conf file should be backed up, or alternatively it should be possible to reconstruct the file’s contents.

To upgrade from any previous version of Metadb, stop the server (if running), and then run the upgrade process in case changes are required:

The upgrade process may, in some instances, take a significant amount of time to run. The database generally remains available to users during this period.

If no changes are needed, the process outputs:

To start the server:

The --memlimit option can be used to set a soft memory limit (in GiB) if needed, for example:

The server listens on port 8440 by default, but this can be set using the --port option. The --debug option enables verbose logging.

To stop the server:

Note that stopping or restarting the server may delay scheduled data updates or cause them to restart.

The server can be set up to run with systemd via a file such as /etc/systemd/system/metadb.service, for example:

Then:

The PostgreSQL terminal client, psql, can be used to connect to the Metadb server:

Metadb v1.0.5 Then for example to show the software version:

A data source is defined using the CREATE DATA SOURCE statement, for example:

Metadb currently supports reading Kafka messages in the format produced by the Debezium PostgreSQL connector for Kafka Connect. Configuration of Kafka, Kafka Connect, Debezium, and PostgreSQL logical decoding is beyond the scope of this documentation, but a few notes are included here.

Data flow in this direction:

To allow capturing data changes in the source PostgreSQL database, logical decoding has to be enabled, in particular by setting wal_level = logical in postgresql.conf.

Next we create a connector configuration file for Kafka Connect:

It is recommended to use the connector configuration settings heartbeat.interval.ms and heartbeat.action.query as above to avoid spikes in disk space consumption within the source database. (See the Debezium PostgreSQL connector documentation for more details.) The schemastopfilter option of the CREATE DATA SOURCE command is used to filter out the heartbeat table.

In the source database:

Then to create the connector:

Note the 1 included in name and database.server.name in the connector configuration. This is suggested as a version number, which can be incremented if the data stream needs to be resynchronized with a new connector.

Metadb requires all streamed tables to have a primary key defined or a replica identity of FULL. Tables that do not meet this requirement should be filtered out in the Debezium PostgreSQL connector configuration by setting schema.exclude.list or table.exclude.list. Otherwise they will generate error messages in the Metadb log.

The replication slot disk usage must be monitored, because under certain error conditions it can grow too large and possibly fill up the disk. To show the disk usage (in the source database):

It is also recommended to allocate plenty of extra disk space in the source database.

To drop the replication slot (in the source database) after deleting a connector:

If a Kafka data stream fails and cannot be resumed, it may be necessary to re-stream data to Metadb. For example, a source database may become unsynchronized with the analytic database, requiring a new snapshot of the source database to be streamed. Metadb can accept re-streamed data in order to resynchronize with the source, using the following procedure.

Note that during the resynchronization process, the analytics database continues to be available to users. However, streaming updates will be slower than usual, and there temporarily may be missing records (until they are re-streamed) or "extra" records (recently deleted in the source database).

To create a new database user account:

In addition to creating the user, this also creates a schema with the same name as the user which can be used as a workspace.

We recommend user names of 3 to 8 characters in length.

To give the user access to all tables generated from a data source (including tables not yet created):

It is possible to make administrative-level changes directly in the underlying PostgreSQL database, such as providing additional tables for users. However, the following guidelines should be followed strictly to avoid disrupting the operation of Metadb:

Before defining a ReShare data source, create a data origin for each consortial tenant. For example:

Then use the module 'reshare' option when creating the data source, and set addschemaprefix to add a reshare_ prefix to the schema names:

Note that the order of commands is important: The initial set of data origins should be created before the data source is created so that schema names of incoming data will be processed correctly. Later, whenever a new consortial tenant is to be added, it should be defined in Metadb using CREATE DATA ORIGIN (and the server restarted) before the tenant is added to ReShare.

In the Debezium PostgreSQL connector configuration, it is suggested that credentials (.+mod_login), the public schema, the Okapi supertenant (supertenant_mod_.+), and mod-pubsub data (pubsub_config,.+_mod_pubsub) be excluded using the schema.exclude.list setting.