ScalarDB Admin API & Schema Loader

ScalarDB is a database-agnostic universal transaction manager on top of several databases, such as relational databases and NoSQL databases. ScalarDB manages transaction logs (e.g., before and after images in WAL) in application-managed records so that it can achieve transactions without depending on the capabilities of underlying databases.

Although the approach is excellent for achieving database-agnostic transactions properly, it requires application developers to create ScalarDB-specific schemas, i.e., adding extra columns for ScalarDB to manage transaction logs, which is error-prone and tricky. To mitigate the problem, we provide an Admin API and Schema Loader, a command-line tool that wraps the Admin API.

In this blog post, I will explain the overview and some details about them.

The Architecture of the management of database schema of ScalarDB

Overview of the Admin API

The Admin API provides several methods related to database schema management, such as the creation, deletion, and alteration of the database schema.

In this overview, we will briefly show how to create and delete a namespace and a table; if you would like to know more, please refer to the Admin API documentation.

An instance of the Admin object can be retrieved by using the same configuration file as the transaction manager.

TransactionFactory transactionFactory =
   TransactionFactory.create("<Path to ScalarDB config properties>");
DistributedTransactionAdmin admin = transactionFactory.getTransactionAdmin();

Then, we will create a table named “customers” which has four columns: “customer_id”, “name”, “credit_limit” and “credit_total”. The “customer_id” column is the partition key.

TableMetadata customersTableMetadata =
   TableMetadata.newBuilder()
       .addPartitionKey("customer_id")
       .addColumn("customer_id", DataType.INT)
       .addColumn("name", DataType.TEXT)
       .addColumn("credi_limit", DataType.INT)
       .addColumn("credit_total", DataType.INT)
       .build();
admin.createTable("sample", "customers", customersTableMetadata);

We also need to create a special table called Coordinator table which is used by the Transactional API to track the statuses of transactions.

admin.createCoordinatorTables();

The table is ready to use and data can now be inserted using the Transactional API.

To delete the ScalarDB environment, we can delete the namespace and table with :

admin.dropTable("sample", "customers");
admin.dropNamespace("sample");

Finally, we also need to delete the Coordinator table.

admin.dropCoordinatorTables();

Overview of the Schema Loader

The Schema Loader is a CLI written in Java that internally uses the Admin API. It provides several commands related to database schema creation, deletion, and alteration.

Regarding the typical usage of the Admin API and the Schema Loader, the former can be more appropriate for testing and debugging while the latter is more practical for setting up and tearing down the schema of an application running in production. The Admin API provides more control and richer functionality for corner use cases, while the Schema Loader satisfies most use cases for bulk schema creation and deletion.

Here, we will briefly show how to create and delete a namespace and a table; if you would like to know more, please refer to the Schema loader documentation.

First, we need to create a JSON file describing the schema of the same table of the previous section. In this sample, the schema file contains a single table, but any number of tables can be defined.

{
 "sample.customers": {
   "transaction": true,
   "partition-key": [
     "customer_id"
   ],
   "columns": {
     "customer_id": "INT",
     "name": "TEXT",
     "credit_limit": "INT",
     "credit_total": "INT"
   }
 }
}

Then, we can register the schema. The namespace and the table present in the schema file, as well as the Coordinator table, will be created if they do not already exist.

java -jar scalardb-schema-loader-3.8.0.jar -f <Path to JSON schema file> -c <Path to ScalarDB config properties> --coordinator

The table is ready for interactions with the Transactional API.

To delete the ScalarDB schemas, we need to add the “-D” option to the previous command. The “customers” table, its namespace and the Coordinator table will be deleted.

java -jar scalardb-schema-loader-3.8.0.jar -f <Path to JSON schema file> -c <Path to ScalarDB config properties> --coordinator -D

Responsibilities of the Admin API

When you create a table through the Admin API, the Admin API creates the table in the underlying storage and does two additional things:

• Adds extra columns for transaction metadata (e.g, transaction ID and the status).
• Registers the metadata of the created table in a separate table.

Transaction Metadata

The transaction metadata is ScalarDB-specific internal metadata for managing transactions, which corresponds to a write-ahead log (WAL) in a database system.

For example, when ScalarDB is coupled with MySQL, the Admin API will add the following transaction metadata columns (in green font) to the “sample.customers” table (see the schema definition in the previous section).

Schema of the “customers” table with added transaction metadata columns (in green font)

Coordinator table

As described above, ScalarDB uses a dedicated table called Coordinator table to keep track of the statuses of ongoing and past transactions. Managing this table schema is another element that the Admin is responsible for.

Table Metadata

The table metadata is ScalarDB-specific internal metadata for managing the schema of the user’s table. ScalarDB relies on the stored table metadata to track if a given namespace or table exists. They are also used to validate the query content. It checks if the targeted metadata for the table exists and verifies that the name, type, and function of each column match the content in the `scalardb.metadata` database.

Since table metadata are read very frequently by the query and transaction processing. The table metadata are cached to improve the query efficiency.

Each user table’s column will have an entry in the “scalardb.metadata” database. You can see all the entries for the “sample.customers” table below.

Entries for the “customers” table in the ScalarDB metadata table

Summary

In this blog, we described that the Admin API and the Schema Loader are used to manage user schema for ScalarDB. We then introduced briefly how to use both of them to create and delete a database schema. We also detailed that the Admin API appends metadata columns to each created table and uses a Coordinator table to manage transactions as part of a write-ahead logging (WAL) protocol. Lastly, we saw that the Admin API stores the table metadata of the user’s schema for query validation purposes.