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.
Each database will belong to an account. Users can only access databases that belong to their account (when permission is given)
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 .
DROP DATABASE my_database01;
Caution: Dropping a database will delete all tables inside the database
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
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.
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.
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.
Properties of a table that can be modifier by
Drop (Columns that are used as keys cannot be dropped)
-- Add ALTER TABLE my_database01.my_table ADD COLUMN new_column UInt32; -- Drop ALTER TABLE my_database01.my_table DROP COLUMN old_column UInt32;
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;
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.
Creates a new table with the same schema and data from the original table.
CLONE TABLE orignal_db.orginal_tableINTO new_db.new_tableCOMMENT 'my newly cloned table'
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.
CREATE VIEW my_database01.my_view AS SELECT * FROM my_database01.my_table;
DROP VIEW my_database01.my_view;
Updated over 1 year ago