ClickHouse常用命令,基本数据类型
一、建库建表基本命令
查看库
show databases;
使用库
use databaseName;
查看表
show tables;
新建库
create database ck_test ;
删除库
drop database ck_test;
新建表
create table ck_test.myOperator(
id String COMMENT '主键',
name String COMMENT '名称',
is_deleted Int32 COMMENT '是否删除 0:否 1:是',
gmt_created DateTime COMMENT '创建时间',
gmt_modified DateTime COMMENT '修改时间'
)ENGINE = Memory;
插入数据
insert into ck_test.myOperator values(1,'马云',1,'2019-01-01 00:00:00','2019-01-01 00:00:00') ;
二、基本数据类型
整型
有符号整型(-2n-1~2n-1-1):
- Int8 - [-128 : 127]
- Int16 - [-32768 : 32767]
- Int32 - [-2147483648 : 2147483647]
- Int64 - [-9223372036854775808 : 9223372036854775807]
无符号整型范围(0~2n-1):
- UInt8 - [0 : 255]
- UInt16 - [0 : 65535]
- UInt32 - [0 : 4294967295]
- UInt64 - [0 : 18446744073709551615]
浮点型
- Float32 - float
- Float64 – double
布尔型
没有单独的类型来存储布尔值。可以使用 UInt8 类型,取值限制为 0 或 1。
字符串
- 变长字符串 String
字符串可以任意长度的。它可以包含任意的字节集,包含空字节。 - 定长字符串 FixedString(N)
固定长度 N 的字符串,N 必须是严格的正自然数。当服务端读取长度小于 N 的字符串时候,通过在字符串末尾添加空字节来达到 N 字节长度。 当服务端读取长度大于 N 的字符串时候,将返回错误消息。
与String相比,极少会使用FixedString,因为使用起来不是很方便。
枚举类型
- Enum8 用 ‘String’= Int8 对描述。
- Enum16 用 ‘String’= Int16 对描述。
CREATE TABLE t_enum( x Enum8('hello' = 1, 'world' = 2))ENGINE = TinyLog
INSERT INTO t_enum VALUES ('hello'), ('world'), ('hello')
从表中查询数据时,ClickHouse 从 Enum 中输出字符串值
数据组
- Array(T)
由 T 类型元素组成的数组。T 可以是任意类型,包含数组类型,但不推荐使用多维数组,ClickHouse 对多维数组的支持有限。
数组里面数据类型必须一致,否则会抛出异常
SELECT array(1, 2) AS x, toTypeName(x);
元组
- Tuple(T1, T2, …)
元组,其中每个元素都可以有单独的类型。
SELECT tuple(1,'a') AS x, toTypeName(x);
日期
- Date
用两个字节存储,表示从 1970-01-01 (无符号) 到当前的日期值, 最小值输出为0000-00-00。
时间戳
- DateTime
用四个字节(无符号的)存储 Unix 时间戳,允许存储与日期类型相同的范围内的值。最小值为 0000-00-00 00:00:00,时间戳类型值精确到秒。
三、表引擎
表引擎(即表的类型)决定了:
数据的存储方式和位置,写到哪里以及从哪里读取数据
- 支持哪些查询以及如何支持。
- 并发数据访问。
- 索引的使用(如果存在)。
- 是否可以执行多线程请求。
- 数据复制参数。
在读取时,引擎只需要输出所请求的列,但在某些情况下,引擎可以在响应请求时部分处理数据。对于大多数正式的任务,应该使用MergeTree族中的引擎。
-
日志引擎
-
TinyLog
该表引擎通常与一次写入方法一起使用:一次写入数据,然后根据需要多次读取。例如,您可以将TinyLog-type表用于小批量处理的中间数据。请注意,将数据存储在大量小表中效率很低。
查询在单个流中执行。换句话说,此引擎旨在用于相对较小的表(最多约1,000,000行)。如果您有许多小表,则使用此表引擎很有意义,因为它比Log引擎更简单(需要打开的文件更少)。
#新建表 create table ck_dwd.tb_tinylog on CLUSTER wedoctor_ch_cluster1 ( id Int8 ,name String ,age Int8 ) engine=TinyLog ; #插入数据 insert into ck_dwd.tb_tinylog values(1,'小红',56),(2,'小明',55),(3,'小白',123) ;
-
StripeLog
该引擎属于日志引擎家族。请在“日志引擎系列”文章中查看日志引擎的常见属性及其区别。
当您需要编写少量数据(少于一百万行)的表时,请使用此引擎。
#新建表 CREATE TABLE ck_dwd.stripe_log_table on CLUSTER wedoctor_ch_cluster1 ( timestamp DateTime, message_type String, message String ) ENGINE = StripeLog ; #插入数据 INSERT INTO ck_dwd.stripe_log_table VALUES (now(),'REGULAR','The first regular message'); INSERT INTO ck_dwd.stripe_log_table VALUES (now(),'REGULAR','The second regular message'),(now(),'WARNING','The first warning message');
-
Log
日志与TinyLog的不同之处在于,列文件中存在一个小的“标记”文件。这些标记写在每个数据块上,并包含偏移量,这些偏移量指示从何处开始读取文件以跳过指定的行数。这样就可以在多个线程中读取表数据。对于并发数据访问,读操作可以同时执行,而写操作则阻止读操作和其他操作。日志引擎不支持索引。同样,如果写入表失败,则表将损坏,并且从表中读取将返回错误。日志引擎适用于临时数据,一次写入表以及测试或演示目的。
-
-
MergeTree Family
-
MergeTree
#建表 create table ck_dwd.tb_merge_tree on CLUSTER wedoctor_ch_cluster1( id Int8 , name String , ctime Date ) engine=MergeTree() order by id partition by name ; #合并多次插入数据的分区 optimize table ck_dwd.tb_merge_tree ;
-
ReplacingMergeTree
该引擎与MergeTree的不同之处在于,它删除具有相同排序键值(ORDER BY表部分,不是PRIMARY KEY)的重复条目。
重复数据删除仅在合并期间发生。合并发生在后台的未知时间,因此您无法为此计划。某些数据可能仍未处理。尽管您可以使用OPTIMIZE查询运行计划外的合并,但不要指望使用它,因为OPTIMIZE查询将读写大量数据。
因此,ReplacingMergeTree它适合在后台清除重复数据以节省空间,但不能保证不存在重复数据。
#建表 CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] ( name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1], name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2], ... ) ENGINE = ReplacingMergeTree([ver]) [PARTITION BY expr] [ORDER BY expr] [PRIMARY KEY expr] [SAMPLE BY expr] [SETTINGS name=value, ...]
-
SummingMergeTree
引擎从MergeTree继承。区别在于,在合并SummingMergeTree表的数据部分时,ClickHouse会将所有具有相同主键(或更准确地说,具有相同排序键)的行替换为一行,该行包含具有数值数据类型的列的汇总值。如果排序键的组成方式是单个键值对应于大量行,则这将大大减少存储量并加快数据选择的速度。
建议与一起使用引擎MergeTree。将完整数据存储在MergeTree表中,并SummingMergeTree用于汇总数据存储,例如,在准备报告时。这样的方法将防止您由于主键构成不正确而丢失有价值的数据。
CREATE TABLE summtt ( key UInt32, value UInt32 ) ENGINE = SummingMergeTree() ORDER BY key
-
CollapsingMergeTree
-
VersionedCollapsingMergeTree
-
AggregatingMergeTree
-
GraphiteMergeTree
-
-
Mysql引擎
MySQL引擎用于将远程的MySQL服务器中的表映射到ClickHouse中,并允许您对表进行INSERT和SELECT查询,以方便您在ClickHouse与MySQL之间进行数据交换。
MySQL数据库引擎会将对其的查询转换为MySQL语法并发送到MySQL服务器中,因此您可以执行诸如SHOW TABLES或SHOW CREATE TABLE之类的操作。
创建数据库
CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster] ENGINE = MySQL('host:port', ['database' | database], 'user', 'password')
引擎参数
· host:port —— MySQL服务器地址。
· database——远程数据库名称。
· user ——MySQL用户。
· password—— 用户密码。
示例:
创建数据库 CREATE DATABASE IF NOT EXISTS ck_mysql ENGINE = MySQL('192.168.56.20:3306', test, root, 'root');
将数据插入ck,mysql的数据也会发生变化