Managing Table Templates

This notebook shows how you can manage and utilize table templates with the ThanoSQL library. We will cover how to create, update, delete, and list table templates. Stored table templates can then be used to create new tables. First, initialize a ThanoSQL client with your API token and engine URL.

from thanosql import ThanoSQL

client = ThanoSQL(
    api_token="THANOSQL_API_TOKEN",
    engine_url="THANOSQL_ENGINE_URL"
)

Listing Table Templates

You can get the list of stored table templates using the ThanoSQL library. The list() function can be used without any parameters or with optional parameters. To list only table templates which name contains some keyword(s), use the search parameter. By default, results are ordered based on recency. In order to change this ordering, use the order_by parameter. There are three options: recent (default), name_asc (A to Z), and name_desc (Z to A).

table_templates = client.table.template.list(search="search_keyword", order_by="name_asc")
for table_template in table_templates:
    print(table_template.name)

By default, list() includes all versions of stored table templates. To show only the latest versions of each template, set the latest parameter.

client.table.template.list(latest=True)

You can also choose to only show table templates with a certain name and version with get(). While the parameter name is required, version is optional. When not set, all versions of the table template with name specified will be listed. get() also returns the list of all available versions, regardless of whether version is specified or not.

res = client.table.template.get(name="table_template_name", version="1.0")
for table_template in res["table_templates"]:
    print(table_template.name, table_template.version)
for version in res["versions"]:
    print(version)

Creating Table Templates

You can create table templates using the library. However, a few extra imports are needed to construct the table object. You need to first construct Column objects and Constraint objects, if applicable. Optionally, you can also specify a version number and compatibility type. If they are not specified, a default value of 1.0 and ignore will be used.

from thanosql.resources import BaseColumn, Constraints, ForeignKey, PrimaryKey, TableObject, Unique


# construct list of columns
columns = [BaseColumn(type="integer", name="column_1"), BaseColumn(type="varchar", name="column_2"), BaseColumn(type="double precision", name="column_3"), BaseColumn(type="timestamp", name="column_4")]

# construct unique columns (optional)
unique = Unique(name="my_unique_1", columns=["column_2"])

# construct primary key (optional but recommended)
primary_key = PrimaryKey(name="my_pk_1", columns=["column_1"])

# construct foreign keys (requires another existing table -- not table template)
foreign_key = ForeignKey(name="my_fk_1", reference_schema="public", reference_column="another_column_1", reference_table="another_table", column="column_3")

# assemble everything into a table object
table_object = TableObject(columns=columns, constraints=Constraints(unique=[unique], primary_key=primary_key, foreign_keys=[foreign_key]))

# create the table template using ThanoSQL client, with default version and compatibility
res = client.table.template.create(name="my_table_template_1", table_template=table_object)
res

Updating Table Templates

It is not possible to update a table template once it is saved. “Updating” a table template is instead done by creating a new template with the same name but higher version. Consequently, no two table templates have the same name and version: there can be multiple table templates with the same name, but each has to have a unique version. This is done to maintain compatibility. The ThanoSQL convention of versioning is “x.y”, where x ranges from 1 to 9 and y ranges from 0 to 9.

For example, if we want to update the table template we just created by removing all constraints except for the primary key, we can do the following:

table_object_updated = TableObject(columns=columns, constraints=Constraints(primary_key=primary_key))

res = client.table.template.create(name="my_table_template_1", table_template=table_object_updated, version="2.0")
res

Deleting Table Templates

You can remove table templates that you no longer use. Similar to listing table templates of a certain name, you have to provide a target name, but version is optional. If version is not specified, all versions of table templates with the target name will be removed.

client.table.template.delete(name="my_table_template", version="1.0")  # will only delete version 1.0 of "my_table_template"
client.table.template.delete("my_table_template_1")  # will delete all table templates which name is "my_table_template_1"