文档

动态列

更新时间:

云原生多模数据库 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的数据实际是0xEF0x000x11这三个字节,而非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_dynamicc4为动态列,那么查询成功的语句如下示例:

    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
}