Database and Table Management

Treasure Data uses the same conventions as Relational Database Management Systems (RDBMSs) for managing data sets: Databases and Tables. Each database can contain several tables. However, each table can only belong to one database.

Table of Contents

Prerequisites

  • Basic knowledge of Treasure Data.

Database and Table – explained

A Database is a collection of tables, which is similar to other database systems. Tables contain your actual data. A given table can only belong to a single database.

Management from Web Console

Databases page allows you to navigate/create/delete databases and tables from the browser. This is the preferred way to interact with databases and tables.

Management from Command Line Interface (CLI)

By using the toolbelt, you can manage databases and tables from the command line.

Creating a Database

The ‘db:create’ sub-command creates a database. For example, the following command will create a table named ‘test_db’ in the cloud.

$ td db:create test_db
Untitled-3
The database name only supports alphanumeric characters and underscores. Hyphens cannot be used (an exception will be thrown). Also, the database name must be 3 to 128 characters long.

Your local environment remains unchanged; the database resides solely in the cloud.

Showing a List of Your Databases

The ‘dbs’ sub-command lists all the databases in your account.

$ td dbs
+---------------+
| Name          |
+---------------+
| twitter       |
| follow_events |
| test_db       |
+---------------+
3 rows in set

Removing a Database

The ‘db:delete’ sub-command removes a database. This will remove all tables and all data stored within the database as well.

$ td db:delete test_db
Database 'test_db' is deleted.
Untitled-3
This operation cannot be rolled back. The data will be unrecoverable.

Creating a Table

The ‘table:create’ sub-command creates a table. For example, the following command will create a table named ‘test_table’ within the database ‘test_db’.

$ td table:create test_db test_table
Table 'test_db.test_table' is created.
Untitled-3
The table name only supports alphanumeric characters and underscores. Hyphens cannot be used (an exception will be thrown). Also, the table name must be 3 to 128 characters long.

Much like your database, this table only exists in the cloud. Your local environment remains unchanged.

Showing a List of Your Tables

The ‘tables’ sub-command lists all the tables within the specified database. The number of records in each table will be shown as well (currently zero).

$ td tables test_db
+----------+------------+------+-------+
| Database | Table      | Type | Count |
+----------+------------+------+-------+
| test_db  | test_table | log  | 0     |
+----------+------------+------+-------+
1 row in set

Issuing the ‘tables’ command without an argument will list all the databases and tables that you have created.

$ td tables
+----------+-------------+------+-------------+
| Database | Table       | Type | Count       |
+----------+-------------+------+-------------+
| kzk      | www_access  | log  | 14          |
| sf       | ab          | log  | 212900645   |
| sf       | msgpack_www | log  | 390734      |
| sf       | wikistats3  | log  | 12745020396 |
| sf       | x           | log  | 3           |
| test_db  | test_table  | log  | 0           |
+----------+-------------+------+-------------+
8 rows in set

Dropping Tables

The ‘table:delete’ sub-command removes a table from a database. This will remove all data stored within the table as well.

$ td table:delete test_db test_table
Table 'test_db.test_table' is deleted.
Untitled-3
This operation cannot be rolled back. The data will be unrecoverable.

Expiring Data From A Table

Data expiration is a mechanism that allows the user to set an expiration period (in days) for the data in a table. When an expiration period is set for a specific table, treasure data drops all records in that table whose timestamp is older than the amount of days specified in the expiration period.

To set the expiration period (days) for a table one can use:

Using the Command Line Interface (CLI) to enable and set data expiration

To enable and set the expiration:

$ td table:expire test_db test_table <expiration_days>
Table set to expire data older than <expiration_days> days.

where <expiration_days> is an integer number greater than 0 indicating the maximum age of any record in the table.
To disable the table data expiration simply set the expiration days to 0:

$ td table:expire test_db test_table 0
Data expiration disabled for this table.

Using the Console to enable and set data expiration

Access the database page and then table page from within the Console. Once there, click on the ‘Table Settings’ button.

Console's Table Settings

To enable table data expiration from the table settings page, switch the ‘Expire data?’ field to ‘Yes’, then set the ‘Expire data after’ field underneath to the desired number of days. Finally, click on the ‘Update Table’ button at the bottom of the page.


Last modified: Oct 07 2016 08:36:44 UTC

If this article is incorrect or outdated, or omits critical information, please let us know. For all other issues, please see our support channels.