Databases, Tables and Views

Overview of Database and Table

A database is simply a logical grouping of data. Each database will contain a number of tables and views. Tables are where the data resides, it is composed of structured rows and columns. A view is a saved query that relies on a table. Whenever a view is accessed, the underlying query is executed and the results are returned.

Working with Database

Each database will belong to an account. Users can only access databases that belong to their account (when permission is given)

Creating a database

Figure 1.0 Creating a databaseFigure 1.0 Creating a database

Figure 1.0 Creating a database

CREATE DATABASE my_database01;

Note : Database names can only contain alphanumeric characters a-z 0-9 and underscore _ . All names will be converted to lowercase .

Dropping a database

DROP DATABASE my_database01;

Caution: Dropping a database will delete all tables inside the database

Working with Tables

Tables are where the data is stored. Each table belongs to a database. Tables can be created in multiple ways: manually through UI or SQL, or by importing data.

Tables in ByteHouse supports different kinds of properties:

  • Description - User comments

  • Engine - The table engine determines how the data is stored and accessed. Currently, only CnchMergeTree is supported.

  • Order by key (mandatory) - Column used as the primary if the primary key is not specified. The order by key is used to determine how the data is stored physically in the database. Users can specify up to 5 columns in order by key.

  • Primary key (mandatory) - Usually the same as the **Order by key, **the Primary key is used to organize data files.

  • Cluster Key (optional) - Cluster Keys are required for Bucket Tables. If a Cluster Key is provided, the data in a table and its partitions are subdivided into buckets based on a hash function, and this can improve query performance by reducing the amount of data that needs to be processed.

  • Partition key (mandatory) - Partition keys are used to determine in which data partition each table row belongs, to divide a large table into smaller parts.

  • Retention time (optional) - Specific amount of time the data is kept, if done through the UI it will be the number of days.

Caution: Once the data's age is older than the retention time, it is automatically removed.

  • Unique key (optional) - Ensures user can query the latest value of the unique key. A unique key can accept only up to 10 values and cannot contain Nullable columns. There are two levels, partition and table level, to determine the granularity of the unique values.

  • Sample key (optional) - Get a certain fraction of sample data by defining a sample key. Sample Key will use the first primary key by default and must be one of the Order By Key/Primary Key.

Creating a table

A table is always created inside a database. Whenever a database is not specified, ByteHouse will use the default database based on the user's preference.

Figure 2.0 Creating a tableFigure 2.0 Creating a table

Figure 2.0 Creating a table

Note: Creating the table above can also be done through a query

CREATE TABLE `test_db2`.`my_table` 
(
    `id` UInt64,
    `name` String,
    `started` DateTime
)
ENGINE=`CnchMergeTree()`
PRIMARY KEY `id`
ORDER BY `id`
PARTITION BY `toYYYYMMDD`(`started`); 

Note: Once a table is created, only the Retention Time can be updated from the table properties. Columns can still be added after creation.

Altering a Table

Properties of a table that can be modifier by ALTER :

  • Columns

    • Add

    • Drop (Columns that are used as keys cannot be dropped)

Figure 2.1 Updating a tableFigure 2.1 Updating a table

Figure 2.1 Updating a table

-- Add
ALTER TABLE my_database01.my_table ADD COLUMN new_column UInt32;

-- Drop
ALTER TABLE my_database01.my_table DROP COLUMN old_column UInt32;
  • Comments
ALTER TABLE my_database01.my_table COMMENT COLUMN old_column 'my comment'
  • Retention time
-- Add TTL
ALTER TABLE my_database01.my_table MODIFY TTL INTERVAL 3 DAY;

-- Remove TTL
ALTER TABLE my_database01.my_table REMOVE TTL
  • Table name
RENAME TABLE my_database01.my_table TO my_database01.new_table;

Dropping a Table

DROP TABLE my_database01.my_table;

Note: A table cannot be dropped when there is a view that depends on it. The dependent view must be dropped first before the table.

Cloning a Table

Creates a new table with the same schema and data from the original table.

Figure 2.2 Cloning a tableFigure 2.2 Cloning a table

Figure 2.2 Cloning a table

CLONE TABLE orignal_db.orginal_tableINTO new_db.new_tableCOMMENT 'my newly cloned table'

Working with Views

A view is simply a stored query. When accessing a view, the subsequent query is executed and the results are used to display the data.

Creating a View

Figure 3.0 Creating a viewFigure 3.0 Creating a view

Figure 3.0 Creating a view

CREATE VIEW my_database01.my_view AS SELECT * FROM my_database01.my_table;

Dropping a View

DROP VIEW my_database01.my_view;

Did this page help you?