Metadb 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. For example, the user celia can create a table in the schema celia:

Other users typically do not have privileges to access the table by default.

To grant the user rosalind read-only access to the schema and table:

The user rosalind can then access the table.

This section offers a few brief recommendations for working with common data types.

The text data type is recommended for strings of characters.

The varchar(n) type with length n can be used for older database clients that require the length to be specified.

For monetary amounts, numeric(19, 4) is usually a good choice. For exchange rates, numeric(19, 14) may be used.

There are various ways of querying within a range of dates, but the least error-prone is to use inequalities of the form start <= t < end, for example:

An effective way to create a report is to package it as a database function. A database function can define a query and associated parameters. Users can then call the function, specifying a value for each parameter.

For example, suppose that the following query counts the number of loans in a library for each circulated item within a range of dates.

We can create a function to generalize this query. Instead of including the dates directly within the query, we will define them as parameters: start_date and end_date.

Now the function can be called with different arguments to generate reports:

The user lisa who created this function can share it with other users in a similar way as described in the "User workspaces" section above.

Note that the GRANT USAGE ON SCHEMA command does not have to be run again if it was done previously.

Defining shared functions in this way can be used together with a web-based database tool such as CloudBeaver to make reports available to a wider group of users.

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, behind what appears to be a simple table. It is recommended to use database functions instead, as described in the previous section.

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:

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 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:

Metadb expects the database name to be metadb or to begin with metadb_; otherwise it logs a warning message.

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 8550 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-based client, psql, is used to connect to a Metadb server that runs on the same host and listens on a specified port:

For example:

See Reference > Statements for commands that can be issued via psql.

If a Kafka data stream fails and cannot be resumed, it may be necessary to re-stream a complete snapshot of the 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 synchronize with the source, using the following procedure.

Note that during the synchronization 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). Also, periodic transforms and external SQL are paused during synchronization.

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. The schema is intended as a workspace for the user.

By default the user does not have access to data tables. 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:

Metadb 1.1 The MARC transform in Metadb can also be used with LDP (and LDLite). A command-line tool called marct is provided which is a drop-in replacement for ldpmarc.

The system requirements are a subset of those for Metadb:

To build marct:

which creates a bin/ subdirectory and builds the marct executable there:

In LDP, MARC data are read from the tables public.srs_marc and public.srs_records, and the transformed output is written to the table public.srs_marctab.

Typical usage is:

where datadir is a LDP data directory containing ldpconf.json, and ldp1_user is a LDP user to be granted SELECT privileges on the output table.

For example:

Note that marct only grants privileges for a single user. If individual user accounts are configured for LDP, a shell script can be used to grant privileges to the users, for example:

The first time marct runs, it will perform a "full update" of all of the MARC records. In subsequent runs, it will attempt to use "incremental update" to update only records that have changed since the previous run, which can dramatically reduce the running time if the number of changes is small.

However, if very many records have changed, it is possible that incremental update may take longer than full update. If it appears that an incremental update will never finish, it should be canceled, and a full update should be run once before resuming incremental updates. This can be done by using the -f command-line option, which disables incremental update and requires marct to do a full update.