当前位置:网站首页>Clickhouse SQL operation
Clickhouse SQL operation
2022-04-23 17:06:00 【Magic Flute love】
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/
版权声明
本文为[Magic Flute love]所创,转载请带上原文链接,感谢
https://yzsam.com/2022/04/202204231704450625.html
边栏推荐
- Detailed explanation of the penetration of network security in the shooting range
- The new MySQL table has a self increasing ID of 20 bits. The reason is
- Nacos + aspnetcore + Ocelot actual combat code
- [problem solving] [show2012] random tree
- 正则过滤内网地址和网段
- groutine
- TypeError: set_figure_params() got an unexpected keyword argument ‘figsize‘
- C# Task. Delay and thread The difference between sleep
- Shortcut keys (multiline)
- 扫码登录的原理你真的了解吗?
猜你喜欢
C# Task. Delay and thread The difference between sleep
Detailed explanation of the penetration of network security in the shooting range
1-1 NodeJS
Devexpress GridView add select all columns
Shell脚本——Shell编程规范及变量
Detailed explanation of information abstract, digital signature, digital certificate, symmetric encryption and asymmetric encryption
线性代数感悟之2
Detailed explanation of Milvus 2.0 quality assurance system
RTKLIB 2.4.3源码笔记
Milvus 2.0 质量保障系统详解
随机推荐
EF core in ASP Generate core priority database based on net entity model
ClickHouse-SQL 操作
【生活中的逻辑谬误】稻草人谬误和无力反驳不算证明
PostgreSQL column storage and row storage
Grpc gateway based on Ocelot
Lock锁
Shell script -- shell programming specification and variables
【WPF绑定3】 ListView基础绑定和数据模板绑定
Baidu Map Case - modify map style
Shell-sort命令的使用
Signalr can actively send data from the server to the client
freeCodeCamp----prob_ Calculator exercise
PyMySQL
Document operation II (5000 word summary)
JS to find the character that appears three times in the string
Sub database and sub table & shardingsphere
C# Task. Delay and thread The difference between sleep
Copy constructor shallow copy and deep copy
Handwritten event publish subscribe framework
Shortcut keys (multiline)