当前位置:网站首页>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
边栏推荐
- Get the column name list of the table quickly in Oracle
- Shell-入门、变量、以及基本的语法
- RTKLIB 2.4.3源码笔记
- Further optimize Baidu map data visualization
- Mock test
- Redis docker installation
- Derivation of Σ GL perspective projection matrix
- Solution architect's small bag - 5 types of architecture diagrams
- 正则过滤内网地址和网段
- Preliminary understanding of promse
猜你喜欢
Bottom processing of stack memory in browser
Nodejs reads the local JSON file through require. Unexpected token / in JSON at position appears
Use between nodejs modules
How vscode compares the similarities and differences between two files
TypeError: set_figure_params() got an unexpected keyword argument ‘figsize‘
Milvus 2.0 détails du système d'assurance de la qualité
SQL database
1-4 configuration executable script of nodejs installation
文件操作《二》(5000字总结篇)
Detailed explanation of information abstract, digital signature, digital certificate, symmetric encryption and asymmetric encryption
随机推荐
An essay on the classical "tear down the wall in thinking"
STM32__ 03 - beginner timer
◰ GL shadow map core steps
Go language, array, string, slice
SQL: How to parse Microsoft Transact-SQL Statements in C# and to match the column aliases of a view
CentOS MySQL multi instance deployment
How to implement distributed locks with redis?
1-2 JSX syntax rules
El cascade and El select click elsewhere to make the drop-down box disappear
Detailed explanation of C webpai route
信息摘要、数字签名、数字证书、对称加密与非对称加密详解
Input file upload
1-2 characteristics of nodejs
oracle 中快速获取表的列名列表
TypeError: set_figure_params() got an unexpected keyword argument ‘figsize‘
Promise (II)
【WPF绑定3】 ListView基础绑定和数据模板绑定
Preliminary understanding of promse
MySQL master-slave configuration under CentOS
Derivation of Σ GL perspective projection matrix