SQL operation

Basically, traditional relational databases ( With MySQL For example ) Of SQL sentence ,ClickHouse Basically supported , But there are some differences .

Insert

Basic and standard SQL(MySQL) Almost the same

(1) standard

insert into [table_name] values(...),(....)

(2) Insert from table to table

insert into [table_name] select a,b,c from [table_name_2]

Update and Delete（ Not recommended for use ）

ClickHouse Provides Delete and Update The ability of , This kind of operation is called Mutation Inquire about , It can be seen as Alter A kind of .

Although it can be modified and deleted , But as usual OLTP The database is different ,Mutation A statement is a very “ heavy ” The operation of , And it doesn't support transactions .

“ heavy ” The main reason is that each modification or deletion will result in abandoning the original partition of the target data , Rebuild new partition . So try to make batch changes , Do not operate on small data frequently .

(1) Delete operation

alter table t_order_smt delete where sku_id ='sku_001';

(2) Modify the operating

alter table t_order_smt update total_amount=toDecimal32(2000.00,2) where id =102;

Because the operation is relatively “ heavy ”, therefore Mutation The statement is executed in two steps , In fact, the part of synchronous execution is only to add data, add partitions, and mark the old partitions with logical invalidation marks . Until the partition merge is triggered , Will delete old data to free up disk space , Generally, such functions will not be opened to users , Done by the administrator .

Elegant operation ：

update operation ： When updating, insert the same data as before except for an updated department （order by The fields must be the same ）, And use ReplacingMergeTree engine , Add a logo version Field of , When querying, only query version The largest field is the latest ; Because the engine will perform de duplication operation when merging , Therefore, the old data will be deleted .

Delete operation ： When deleting, identify the deleted field as delete , When querying, only the fields that have not been deleted are queried .

Query operation

ClickHouse Basically the same as the standard SQL Not much difference

* Support subquery

* Support CTE(Common Table Expression Common table expression with Clause )

* Support a wide variety of JOIN, however JOIN The operation cannot use the cache , So even if it's the same twice JOIN sentence ,ClickHouse It will also be regarded as two new SQL

* Window function ( Officials are testing …)

* Custom functions are not supported , See the official website for system functions

* GROUP BY Operation added with rollup \ with cube \ with total Used to calculate subtotals and totals .

insert data

/*  Delete old data  */
alter table t_order_mt delete where 1=1;

insert into t_order_mt values
(101,'sku_001',1000.00,'2020-06-01 12:00:00'),
(101,'sku_002',2000.00,'2020-06-01 12:00:00'),
(103,'sku_004',2500.00,'2020-06-01 12:00:00'),
(104,'sku_002',2000.00,'2020-06-01 12:00:00'),
(105,'sku_003',600.00,'2020-06-02 12:00:00'),
(106,'sku_001',1000.00,'2020-06-04 12:00:00'),
(107,'sku_002',2000.00,'2020-06-04 12:00:00'),
(108,'sku_004',2500.00,'2020-06-04 12:00:00'),
(109,'sku_002',2000.00,'2020-06-04 12:00:00'),
(110,'sku_003',600.00,'2020-06-01 12:00:00');

/*  The query data is as follows  */
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 105 │ sku_003 │       600.00 │ 2020-06-02 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 101 │ sku_001 │      1000.00 │ 2020-06-01 12:00:00 │
│ 101 │ sku_002 │      2000.00 │ 2020-06-01 12:00:00 │
│ 103 │ sku_004 │      2500.00 │ 2020-06-01 12:00:00 │
│ 104 │ sku_002 │      2000.00 │ 2020-06-01 12:00:00 │
│ 110 │ sku_003 │       600.00 │ 2020-06-01 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘
┌──id─┬─sku_id──┬─total_amount─┬─────────create_time─┐
│ 106 │ sku_001 │      1000.00 │ 2020-06-04 12:00:00 │
│ 107 │ sku_002 │      2000.00 │ 2020-06-04 12:00:00 │
│ 108 │ sku_004 │      2500.00 │ 2020-06-04 12:00:00 │
│ 109 │ sku_002 │      2000.00 │ 2020-06-04 12:00:00 │
└─────┴─────────┴──────────────┴─────────────────────┘

with rollup： Remove dimensions from right to left for subtotal , Scroll up

select id , sku_id, sum(total_amount) from t_order_mt group by id, sku_id with rollup;

/*  Reduce dimensions from right to left  */
┌──id─┬─sku_id──┬─sum(total_amount)─┐
│ 110 │ sku_003 │            600.00 │
│ 109 │ sku_002 │           2000.00 │
│ 107 │ sku_002 │           2000.00 │
│ 106 │ sku_001 │           1000.00 │
│ 104 │ sku_002 │           2000.00 │
│ 101 │ sku_002 │           2000.00 │
│ 103 │ sku_004 │           2500.00 │
│ 108 │ sku_004 │           2500.00 │
│ 105 │ sku_003 │            600.00 │
│ 101 │ sku_001 │           1000.00 │
└─────┴─────────┴───────────────────┘
┌──id─┬─sku_id─┬─sum(total_amount)─┐
│ 110 │        │            600.00 │
│ 106 │        │           1000.00 │
│ 105 │        │            600.00 │
│ 109 │        │           2000.00 │
│ 107 │        │           2000.00 │
│ 104 │        │           2000.00 │
│ 103 │        │           2500.00 │
│ 108 │        │           2500.00 │
│ 101 │        │           3000.00 │
└─────┴────────┴───────────────────┘
┌─id─┬─sku_id─┬─sum(total_amount)─┐
│  0 │        │          16200.00 │
└────┴────────┴───────────────────┘

with cube : Remove dimensions from right to left for subtotal , Then remove the dimension from left to right for subtotal , Mostly analysis

select id , sku_id, sum(total_amount) from t_order_mt group by id, sku_id with cube;

/*  Reduce dimensions from right to left  */
┌──id─┬─sku_id──┬─sum(total_amount)─┐
│ 110 │ sku_003 │            600.00 │
│ 109 │ sku_002 │           2000.00 │
│ 107 │ sku_002 │           2000.00 │
│ 106 │ sku_001 │           1000.00 │
│ 104 │ sku_002 │           2000.00 │
│ 101 │ sku_002 │           2000.00 │
│ 103 │ sku_004 │           2500.00 │
│ 108 │ sku_004 │           2500.00 │
│ 105 │ sku_003 │            600.00 │
│ 101 │ sku_001 │           1000.00 │
└─────┴─────────┴───────────────────┘
┌──id─┬─sku_id─┬─sum(total_amount)─┐
│ 110 │        │            600.00 │
│ 106 │        │           1000.00 │
│ 105 │        │            600.00 │
│ 109 │        │           2000.00 │
│ 107 │        │           2000.00 │
│ 104 │        │           2000.00 │
│ 103 │        │           2500.00 │
│ 108 │        │           2500.00 │
│ 101 │        │           3000.00 │
└─────┴────────┴───────────────────┘
┌─id─┬─sku_id──┬─sum(total_amount)─┐
│  0 │ sku_003 │           1200.00 │
│  0 │ sku_004 │           5000.00 │
│  0 │ sku_001 │           2000.00 │
│  0 │ sku_002 │           8000.00 │
└────┴─────────┴───────────────────┘
┌─id─┬─sku_id─┬─sum(total_amount)─┐
│  0 │        │          16200.00 │
└────┴────────┴───────────────────┘

with totals: Only total , A total of

select id, sku_id, sum(total_amount) from t_order_mt group by id, sku_id with totals;

/*  Reduce dimensions from right to left  */
┌──id─┬─sku_id──┬─sum(total_amount)─┐
│ 110 │ sku_003 │            600.00 │
│ 109 │ sku_002 │           2000.00 │
│ 107 │ sku_002 │           2000.00 │
│ 106 │ sku_001 │           1000.00 │
│ 104 │ sku_002 │           2000.00 │
│ 101 │ sku_002 │           2000.00 │
│ 103 │ sku_004 │           2500.00 │
│ 108 │ sku_004 │           2500.00 │
│ 105 │ sku_003 │            600.00 │
│ 101 │ sku_001 │           1000.00 │
└─────┴─────────┴───────────────────┘

Totals:
┌─id─┬─sku_id─┬─sum(total_amount)─┐
│  0 │        │          16200.00 │
└────┴────────┴───────────────────┘

For example, the dimension is a,b

rollup： The first is No group by; And then there was group by a; And finally group by a, b

cube： Is that all situations have to have , No group by;group by a;group by b;group by a, b

totals： No group by and group by a, b

alter operation

Same as MySQL The modified fields are basically the same

/*  New fields  */
alter table tableName add column newcolname String after col1;

/*  Modify field type  */
alter table tableName modify column newcolname String;

/*  Delete field  */
alter table tableName drop column newcolname;

Derived data

clickhouse-client --query "select * from t_order_mt where create_time='2020-06-01 12:00:00'" --format CSVWithNames > /opt/module/data/rs1.csv

–query amount to mysql -e, Do not enter cli Interface execution statement

More supported format references :https://clickhouse.tech/docs/en/interfaces/formats/