云原生多模数据库 Lindorm宽表引擎支持动态列功能,即创建表时未显式指定的列,在实际业务中动态写入数据并执行查询。本文介绍如何通过Lindorm SQL实现动态列能力的开启、写入以及查询。
背景信息
传统关系型数据库表的Schema必须预先定义,如果要增加列,则需要变更表属性。变更大表的表属性是一个非常耗时的操作。同时,预定义的Schema给业务的设计会带来很多不便。但是Lindorm宽表引擎原生支持动态列,列无需提前定义,您可以直接使用Lindorm SQL来对动态列进行读写操作。
注意事项
如果您需要使用动态列功能,请注意以下几点:
确保云原生多模数据库 Lindorm宽表引擎为2.2.19及以上版本,具体操作请参见升级小版本。
Lindorm宽表中动态列类型均为VARBINARY,查询动态列和写入动态列时都必须将动态列类型转化成字节数组。
通过Lindorm SQL管理动态列,查询动态列和写入动态列操作支持以云数据库HBase兼容方式创建和写入的表。
开启动态列
一张表的动态列功能一旦启用则无法关闭。
动态列的开启可以通过以下两种方式:
在创建表格时通过
WITH
子句开启动态列功能。CREATE TABLE t_dynamic (p1 INT, c1 INT, c2 VARCHAR, PRIMARY KEY(p1)) WITH (DYNAMIC_COLUMNS='true');
通过修改表的属性开启动态列功能。
ALTER TABLE t_dynamic SET 'DYNAMIC_COLUMNS' = 'true';
结果验证
您可以通过以下语句验证表是否已成功开启动态列功能。
SHOW TABLE VARIABLES FROM t_dynamic LIKE 'DYNAMIC_COLUMNS';
说明开启动态列后,您可以修改表的属性或者在表的Schema中增加新的列,例如执行以下语句表示新增c3列,数据类型为INT。
ALTER TABLE t_dynamic ADD COLUMN c3 int;
如果您在此之前写入过名为
c3
的动态列,由于写入的数据类型均为VARBINARY,查询数据和写入数据时会抛出异常,如果新增列的数据类型为VARBINARY就不会出现这个异常。因此,变更动态列表的Schema时需要注意数据类型不同的情况,尽量避免预定义列和动态列重名。
写入动态列
SQL文本写入
写入动态列表的语法与写入普通列表的语法一致,开启动态列之后,可以写入没有预先在Schema中定义的列,但是动态列的类型只能为VARBINARY(即字节数组)。Lindorm支持用户使用Lindorm-cli直接以SQL文本的方式将数据写入动态列,此时UPSERT语句中指定的动态列的值必须为数据的十六进制字符串形式(即使用十六进制字符(0-9, A-F)来表示二进制数据的形式。以下简称HexString)。
一个字节的范围是0~255,十六进制表示为0x00~0xFF。对于一个字节数组{0x00, 0xFF},它的HexString就是00FF,将字节数组转换为HexString的代码请参见附录:字节数组转换为HexString的实现示例。
写入动态列的场景示例如下:
执行以下语句在
t_dynamic
表中写入c3列,c3列为动态列,写入成功。UPSERT INTO t_dynamic (p1, c2, c3) VALUES (1, '1', '41');
执行以下语句在
t_dynamic
表中写入c4列,c4列为动态列,写入成功。UPSERT INTO t_dynamic (p1, c4) VALUES (2, 'ef0011');
在SQL引擎2.6.8及以上的版本中,为了避免混淆普通字符串和HexString,支持在SQL文本中通过下述形式指定HexString。
UPSERT INTO t_dynamic(p1, c4) VALUES (3, x'ef0011');
上述语句保存的动态列
c4
的数据实际是0xEF
、0x00
和0x11
这三个字节,而非ef0011
这个字符串(ef0011
字符串需要占用6个字节的存储空间)。说明如何查看Lindorm SQL的版本,请参见SQL版本说明。
执行以下语句在
t_dynamic
表中写入c5列,c5列为动态列。由于动态列c5的值f
不是偶数长度的HexString,所以写入失败,需要将f
修改为0f
。UPSERT INTO t_dynamic (p1, c5) VALUES (4, 'f');
执行以下语句在
t_dynamic
表中写入动态列c6,但由于指定的值gf
不是HexString,所以该语句执行时报错。UPSERT INTO t_dynamic (p1, c6) VALUES (5, x'gf');
SQL参数化写入(推荐)
相较于上述直接通过SQL文本直接写动态列,更推荐的做法是在应用程序中通过SQL绑参的方式绑定要写入的字节数组实现写入。如果您需要将字符串或者数值型的值写入动态列中,那么需要先将这些值编码成字节数组,然后通过绑参写入。
以表t_dynamic
为例,使用Java代码绑参写入动态列的代码示例如下:
Connection conn = DriverManager.getConnection(lindorm-jdbc-url);
String createTable = "CREATE TABLE testTable (p1 VARCHAR, c1 INT, PRIMARY KEY(p1)) 'DYNAMIC_COLUMNS' = 'true'";
Statement statement = conn.createStatement();
statement.execute(createTable);
//插入3列,其中p1,c1为Schema预先定义好的列,c2没有预先定义,为动态列写入。
String sqlUpsert = "upsert into " + tableName + "(p1, c1, c2) values(?, ?, ?)";
try (PreparedStatement stmt = conn.prepareStatement(sqlUpsert)) {
stmt.setString(1, "pk");
stmt.setInt(2, 4);
stmt.setBytes(3, new byte[] {0,1});
int updated = stmt.executeUpdate();
Assert.assertEquals(1, updated);
}
使用绑参方式写入数据时,理论上可以通过类似JDBC中的PreparedStatement#setString( )
方法传入一个HexString来写入数据,但强烈建议不要这样操作,尤其是使用MySQL协议与Lindorm进行交互时。因为在MySQL协议中,客户端传入的字符串型参数会以字节数组的形式传至服务端,可能导致数据的二义性,因此强烈建议避免这种操作。
查询动态列
查询动态列的场景分为以下几种:
显式指定查询字段为动态列。查询动态列表的语法与查询普通列表的语法一致,开启动态列之后,可以查询没有预先在Schema中定义的列,如下示例,c3和c4是创建表后新增的动态列。
SELECT p1, c2, c3, c4 FROM t_dynamic WHERE p1 = 1;
返回结果如下:
+----+----+------+------+ | p1 | c2 | c3 | c4 | +----+----+------+------+ | 1 | 1 | 0x41 | null | +----+----+------+------+
不确定表中已包含哪些动态列时,可以使用
SELECT *
查询动态列表。Lindorm SQL为了保证结果集元数据的正确性,强制要求在此类查询语句后添加LIMIT
子句来限定结果集的大小。SELECT * FROM t_dynamic LIMIT 10;
返回结果如下:
+----+------+------+------+----------+ | p1 | c1 | c2 | c3 | c4 | +----+------+------+------+----------+ | 1 | null | 1 | 0x41 | null | | 2 | null | null | null | 0xef0011 | | 3 | null | null | null | 0xef0011 | +----+------+------+------+----------+
重要对于动态列表的
SELECT *
查询操作,LIMIT的默认最大值为5000,超过最大值会报错。在
WHERE
条件中使用动态列。为了确保查询语句的性能
WHERE
条件中需包含主键或索引列,如果您希望在查询的过滤条件中使用动态列进行过滤,那么使用SQL文本查询时,动态列的过滤条件必须指定为HexString;使用绑参方式进行查询时,过滤条件建议直接指定为原始的字节数组。例如表
t_dynamic
中c4
为动态列,那么查询成功的语句如下示例:SELECT p1, c4 FROM t_dynamic WHERE p1 = 3 AND c4 = x'ef0011';
作为对比,下述查询示例中由于给动态列c4指定的过滤条件
1
不是一个HexString,所以该查询执行失败。SELECT p1, c1, c4 FROM t_dynamic WHERE p1 = 2 AND c4 = '1';
动态列数据的显示
使用不同的命令行工具时,动态列的查询结果显示方式不同,具体如下:
Lindorm-cli
HexString
Lindorm-cli会以HexString的形式展示动态列的查询结果。以表t_dynamic
为例,查询语句如下:
SELECT p1, c3, c4 FROM t_dynamic WHERE p1 = 1;
返回结果如下:
+----+------+------+------+----------+
| p1 | c1 | c2 | c3 | c4 |
+----+------+------+------+----------+
| 1 | null | 1 | 0x41 | null |
| 2 | null | null | null | 0xef0011 |
| 3 | null | null | null | 0xef0011 |
+----+------+------+------+----------+
上述结果集中动态列c3
的第一行查询结果0x41
表示字母A。
字符串格式
如果您期望查询结果以字符串的格式返回,需要通过Lindorm-cli的连接语句末尾添加-bytesOutputAsString参数,示例如下:
./lindorm-cli -url <jdbc url> -username <用户名> -password <密码> -bytesOutputAsString
连接参数说明请参见步骤二:连接Lindorm宽表引擎。
执行相同查询语句SELECT p1, c3, c4 FROM t_dynamic WHERE p1 = 1;
,将返回如下结果:
+-----+-------+---------+
| p1 | c3 | c4 |
+-----+-------+---------+
| 1 | A | null |
+-----+-------+---------+
MySQL命令行
MySQL命令行并不会默认显示动态列的数据,这些二进制数据将会以?
表示。
附录:字节数组转换为HexString的实现示例
在Java中,使用下述代码可以将一个字节数组转换为HexString。
private static final char[] DIGITS = {
'0', '1', '2', '3', '4', '5', '6', '7',
'8', '9', 'a', 'b', 'c', 'd', 'e', 'f'
};
private static String toHexString(byte[] bytes) {
char[] chars;
int j = 0;
chars = new char[bytes.length * 2];
for (byte b : bytes) {
chars[j++] = DIGITS[(b & 0xF0) >> 4];
chars[j++] = DIGITS[b & 0x0F];
}
return new String(chars, 0, j);
}
public void testToHexString() {
String s = "Hello, world";
// 对于字符串类型,可以直接使用String的getBytes方法获得对象对应的byte[]
byte[] bytes = s.getBytes(Charset.forName("UTF-8"));
String hexString = toHexString(bytes);
System.out.println(hexString); //打印结果为: 48656c6c6f2c20776f726c64
}