Clickhouse is a columnar database — suitable for read-mostly, read-intensive, large data repositories

Altinity Cloud playground: https://github.demo.trial.altinity.cloud:8443/play

Don’t do this in Clickhouse because it will select all columns (to get all the data for you). This operation will be slow and will use a lot of resources

SELECT * FROM default.ontime_ref

CH Differences from other databases

• CH datatypes are different! E.g.: Int32
• No primary keys that uniquely identify rows
• No foreign keys
• CH loves wide flat tables

Clickhouse has many table engines, one of the most useful — MergeTree

CREATE TABLE syntax:

CREATE TABLE ontime (
 Year UInt16,
 Quarter UInt8,
 Month UInt8,
 ...
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(FlightDate)
PRIMARY KEY (Carrier, FlightDate)
ORDER BY (Carrier, FlightDate,
 DepTime)

Copy table from another table:

CREATE TABLE table1 
AS table2  -- copy from table2
ENGINE = MergeTree -- any other engine