Clickhouse 生成随机数据

随机函数

clickhouse 是一款性能极高的OLAP数据库，由于不像传统OLTP数据库（诸如 MySQL，Oracle 等），它没有函数，没有存储过程，更没有循环语句。因此创建随机值的方式，也多少与众不同，今天我就来说说它的用法。

首先，输入 /usr/bin/clickhouse-client --host localhost --port 9000 进入命令行模式

整型

在键入 SELECT rand 后，按回车，会有提示

一共有4种

• rand
• rand32
• rand64
• randConstant
  
  要注意随机数种子，因为 rand 是伪随机的。由于机制一样，如果没有设置不同的种子，同一次循环，得到的数值都会相同
  
  randConstant() 在同一条 sql 中，得到的值都保持一致。
  测试语句为：SELECT rand(), rand32(), rand64(), randConstant() FROM numbers(10)
  

浮点

由于clickhouse没有提供随机浮点数的方法，只能用一些方式来模拟

1. 随机一个整数，然后随机一个位置插入小数点
   WITH rand() AS value, length(toString(value)) AS len SELECT value / power(10, rand(0) % (len+1))

• WITH <值> AS <变量> 用在查询函数的最前面，用来定义变量
• length(<字符串>) 用来获取字符串长度
• toString() 可以将对象转为字符串
• FROM numbers(n) 可以用来做循环使用
  

2. toDecimal64(rand(now()) / rand(0), 3)
   语法为：toDecimal(<浮点数>, <保留几位小数>)
   

字符串

自带的方法

clickhouse 自带的字符串随机方法有4个

• randomFixedString
• randomString
• randomPrintableASCII
• randomStringUTF8
  
  使用命令，循环打印这几个函数试试
  WITH 10 AS len SELECT randomFixedString(len), randomString(len), randomPrintableASCII(len), randomStringUTF8(len) FROM numbers(20)
  
  由于命令行界面无法对齐，用DBeaver查看格式化后的数据。可以发现：除了 randomPrintableASCII() 其它都含有很多乱码
  
  通过测试可以看出，length() 函数返回的不是字符串长度，而是字符串所占的字节长度
  
  再来看一下随机字符串们的长度。可以发现 length(<字符串长度>) 得到的是字符串所占用的字节长度，而非字符串长度
• 前面3个，指定n位长度的字符，每个字符仅占用1字节
• UTF8的每个字符占用1-4个字节，所以只有它的长度是不固定的

WITH 10 AS len 
SELECT 
	min(length(randomFixedString(len))),max(length(randomFixedString(len))),
	min(length(randomString(len))), max(length(randomString(len))), 
	min(length(randomPrintableASCII(len))), max(length(randomPrintableASCII(len))), 
	min(length(randomStringUTF8(len))), max(length(randomStringUTF8(len)))
FROM numbers(10000000)

自定义方法

鉴于上面都是乱码，不方便查看，因此有时候需要自定义一些方法

1. hex(int) 方法，将整数转为十六进制字符串 hex(rand())
   
2. 将有乱码的随机字符串进行Base64编码，然后倒置，并去除末尾的 “==”
   注意：SQL与Java不同，编号是从1开始的，而不是0
   
   substring(<起始位置>, <长度>) 起始位置从1开始，并且可以是负数；长度不能是负数
   
   例如：substring(reverse(base64Encode(randomString(<字符串长度>))), 3)
   
3. 指定字符集，通过concat进行连接
   由于没有循环语句，因此要多少，只能自行拼接多少

• concat(str1,str2,str3...) 用来拼接多个字符串 ，里面传多个字符串
• arrayStringConcat([str1,str2,str3...], <分隔符>) 用来将字符串数组连到一起，支持自定义分隔符
  
  WITH <变量值> AS <变量名> 用在sql的最前面，用来声明变量
  下面语句用来在字符串中随机抽取一个字符，然后进行拼接

WITH 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz123456789' AS asc2,
length(asc2) AS asc2Len
SELECT concat(	
		substring(asc2,rand() % asc2Len + 1, 1),
		substring(asc2,rand(null) % asc2Len + 1, 1),
		substring(asc2,rand(0) % asc2Len + 1, 1),
		substring(asc2,rand(1) % asc2Len + 1, 1),
		substring(asc2,rand(2) % asc2Len + 1, 1),
		substring(asc2,rand(now()) % asc2Len + 1, 1)
) AS randStr FROM numbers(10)

UUID

generateUUIDv4 会生成 8-4-4-4-12 的随机 UUID

枚举

枚举字段的定义为 <字段名> Enum8('<key1>' = 0, '<key2>' = 1, ...)
枚举类型的赋值方式，可以用它的 key，也可以用它的 value

数组

1. 随机长度
   range(<数组最大长度>) ，注意：数组最大长度为它的参数大小
   
   range(rand() % <数组长度>) 可以得到一个随机长度的数组。
   注意：a % b 取余的话，最大只能达到 b-1
   
2. 随机正序或倒序
   clickhouse 中没有 true 和 false，因此用 “0、null、NULL” 表示 false，“非0的所有整数” 均为 true
   
   arraySort 的第一个函数是一个匿名函数
   SELECT arraySort((x) -> if(rand()>rand(0),1,-1)*x, [3,2,5,1,6,4,9,8,0]) FROM numbers(10)
   

随机排序

-- 正序
SELECT * FROM numbers(10) ORDER BY number ASC
-- 倒序
SELECT * FROM numbers(10) ORDER BY number DESC
-- 随机排序（不需要加列名）
SELECT * FROM numbers(10) ORDER BY rand()

逐条插入

临时表引擎

通常用于测试，它会创建一张临时表，并对其进行操作，操作完成后自动删除该表

语法：nullable(<表结构>)

INSERT INTO function null('x UInt64') SELECT * FROM numbers_mt(1000000000);

上面的一句 sql 等价于下面3条语句

CREATE TABLE t (x UInt64) ENGINE = Null;
INSERT INTO t SELECT * FROM numbers_mt(1000000000);
DROP TABLE IF EXISTS t;

测试语句

INSERT INTO function null('x UInt64') SELECT * FROM numbers_mt(100000000)
INSERT INTO function null('x UInt64') SELECT power(number, 2) FROM numbers_mt(100000000)

随机数生成表引擎

官网地址为：https://clickhouse.com/docs/en/sql-reference/table-functions/generate/

语法：ENGINE = GenerateRandom(random_seed, max_string_length, max_array_length)

• random_seed 计算机的随机数都是伪随机数，因此需要一个随机种子来标识起点。如果填null，会使用一个随机种子
• max_string_length 随机字符串的最大长度（包括该长度）。默认为10
• max_array_length 随机数组的最大长度（包括该长度）。默认为10

该引擎不支持分布式，且不支持如下特性

• ALTER
• SELECT … SAMPLE
• INSERT
• Indices
• Replication

创建一个 “随机数生成表”

CREATE DATABASE test_rand
DROP TABLE IF EXISTS test_rand.generate_engine_table
CREATE TABLE IF NOT EXISTS test_rand.generate_engine_table (id UInt32,
	name String,
	addr String,
	intValue UInt32,
	floatValue Float32,
	arr Array(UInt8),
	mydate DateTime
) ENGINE = GenerateRandom(null, 5, 3)

这是一个特殊的表，仅能用于查询。它是在你查询时，才自动生成临时数据并返回的，因此每次查询的数据均不同。你必须限制总查询的条数，不然会一直跑下去

例如 SELECT * FROM test_rand.generate_engine_table LIMIT 20

INSERT INTO test5.testMergeTree(id, name, value, date) SELECT id, name, id, now() FROM test5.generate_engine_table LIMIT 1000000000

CentOS 7 时区设置

timedatectl # 查看系统时间方面的各种状态

timedatectl list-timezones # 列出所有时区

timedatectl set-local-rtc 1 # 将硬件时钟调整为与本地时钟一致, 0 为设置为 UTC 时间
timedatectl set-timezone Asia/Shanghai # 设置系统时区为上海
其实不考虑各个发行版的差异化, 从更底层出发的话, 修改时间时区比想象中要简单:

cp /usr/share/zoneinfo/Asia/Shanghai /etc/localtime