Connection clickhouse

Mysql -h 127.0.0.1 -p 9004 -uuser_name -ppassword clickhouse-client-uuser_name –password password –port 9000-h 127.0.0.1

Create a table

CREATE TABLE part_v1 (ID String,URL String,EventTime Date) ENGINE = MergeTree() PARTITION BY toYYYYMMDD(EventTime) ORDER  BY (ID, intHash32(ID)) TTL EventTime + toIntervalDay(19) SAMPLE BY intHash32(ID);Copy the code

The SAMPLE key must also be defined in the primary key

Insert data

INSERT INTO db.table VALUES (1, 1, '2020-08-11', 1)

Modify data, cannot change the primary key, partition key

ALTER table db.table update age = 12 where ID in (select ID from test.part_v2 where EventTime='2019-01-01')

Only MergeTree, Merge, and Distributed support ALTER

Increase the field

ALTER table db.table add column if not exists age UInt8 default 0 AFTER URL

Viewing Partition Information

select table,partition,path from system.parts where table='part_v1'

Deleted partitions

ALTER table db.table DROP PARTITION field_date_1;

Copy partitioned data

ALTER TABLE db.table REPLACE PARTITION 1 FROM 2

Data is deleted and executed asynchronously in the background

ALTER table db.table delete where id >=1; The deleted tasks are saved on the mutations table, Select database, table, mutation_id,block_numbers. Number as num,is_done from system. Mutations

Changing the field Type

ALTER TABLE db.table MODIFY COLUMN age String

Modify the remark

ALTER TABLE db. TABLE COMMENT COLUMN ID' ID';

Delete the field

ALTER TABLE db.table DROP COLUMN age

View the data stored in ZK

select * from system.zookeeper where path='/clickhouse/tables/2-2/test_replication/replicas/test_replication_1';

View table structure

DESC db.table

SHOW CREATE TABLE db.table

Rename table

RENAME TABLE db.table to db.newtable

Delete table

TRUNCATE TABLE db.table

Check the dictionary

select name,type,key,attribute.names,attribute.types from system.dictionaries

Only the MergeTree engine is supported. TTL clearing and partition merging are performed

optimize table test.ttl_table_v1;

You can modify the TTL, add the TTL, but cannot cancel it

alter table test.ttl_table_v1 modify column code String TTL create_time + INTERVAL 1 DAY;

TTL rev. Stop

SYSTEM START TTL MERGES; SYSTEM STOP TTL MERGES;

Buffer table. If the specified conditions are met, the data in the Buffer table will be flushed to the specified table

If the concurrency of Mergetree writes is high, the Mergetree table merge speed may be slower than the write speed. Create table test.buffer_to_memory_1 as test.memory_1 engine = Buffer(test, memory_1, 16, 10,100, 1000, 1000000, 10000000, 100000000);

query_log

select * from system.query_log order by event_time desc limit 10;

trace_log

select * from system.trace_log where query_id = '29786b1e-9380-4373-90b8-9fc68ef89441';

Query the SQL operation in progress

SHOW PROCESSLIST

Import data from mysql

Insert into test.test_order select * from mysql('127.0.0.1:3307','test','test_order','root','123456');

ArgMax takes the maximum value and day the id of the row with the maximum value

select argMax(day, id) from test.daily3;

The output field is in JSON format

select batch_id,concat('[',toString(groupUniqArray(id)),']')json from coupon_day_local group by batch_id;

Formatting returns results

SELECT * FROM test FORMAT JSON SELECT * FROM test FORMAT CSV SELECT * FROM test FORMAT TSKV SELECT * FROM test FORMAT XML