文档

动态列

更新时间:

云原生多模数据库 Lindorm宽表引擎支持动态列功能,即创建表时未显式指定的列,在实际业务中动态写入数据并执行查询。本文介绍如何通过Lindorm SQL实现动态列能力的开启、写入以及查询。

背景信息

传统关系型数据库表的Schema必须预先定义,如果要增加列,则需要变更表属性。变更大表的表属性是一个非常耗时的操作。同时,预定义的Schema给业务的设计会带来很多不便。但是Lindorm宽表引擎原生支持动态列,列无需提前定义,您可以直接使用Lindorm SQL来对动态列进行读写操作。

注意事项

如果您需要使用动态列功能,需要注意以下几点:

  • 确保云原生多模数据库 Lindorm宽表引擎为2.2.19及以上版本,具体操作请参见升级小版本

  • Lindorm宽表中动态列类型均为VARBINARY,查询动态列和写入动态列时都必须将动态列类型转化成字节数组。

  • 通过Lindorm SQL管理动态列,查询动态列和写入动态列操作支持以云数据库HBase兼容方式创建和写入的表。

开启动态列

重要

一张表的动态列功能一旦启用则无法关闭。

动态列的开启可以通过以下两种方式:

  • 在创建表格时通过WITH字句开启动态列功能。

    CREATE TABLE tablename (p1 INT, c1 INT, c2 VARCHAR, PRIMARY KEY(p1)) WITH (DYNAMIC_COLUMNS='true');
  • 通过修改表的属性开启动态列功能。

    ALTER TABLE tablename SET 'DYNAMIC_COLUMNS' = 'true';
    说明

    开启动态列后,您可以修改表的属性或者在表的Schema中增加新的列,例如执行以下语句表示新增c3列,数据类型为INT。

    ALTER TABLE t_dynamic_columns ADD COLUMN c3 int;

    如果您在此之前写入c3动态列,由于写入的数据类型均为VARBINARY,查询数据和写入数据时会抛出IllegalDataException异常,所以变更动态列表的Schema时需要注意数据类型不同的情况。如果新增列的数据类型为VARBINARY就不会出现这个异常。

写入动态列

SQL直接写入

写入动态列表的语法与写入普通列表的语法一致,开启动态列之后,可以写入没有预先在Schema中定义的列,但是动态列的类型只能为VARBINARY(即字节数组)。Lindorm支持用户使用Lindorm-cli直接以SQL文本的方式将数据写入动态列,此时UPSERT语句中指定的动态列的值必须为数据的十六进制字符串形式(以下简称 HexString ,表示字节数据的十六进制的字符串)。

说明

一个字节的范围是0~255,十六进制表示为0x00~0xFF。对于一个字节数组{0x00, 0xFF},它的HexString就是00FF,将字节数组转换为HexString的代码请参见字节数组转换为HexString的实现示例

写入动态列的场景示例如下:

  • 执行以下语句在t_dynamic_columns表中写入c3列,c3列为动态列,写入成功。

    UPSERT INTO t_dynamic_columns (p1, c2, c3) VALUES (1, '1', '41');
  • 执行以下语句在t_dynamic_columns表中写入c4列,c4列为动态列,写入成功。

    UPSERT INTO t_dynamic_columns (p1, c4) VALUES (2, 'ef0011');
  • 执行以下语句在t_dynamic_columns表中写入c5列,c5列为动态列。由于动态列c5的值f不是偶数长度的HexString,所以写入失败,需要将f修改为0f

    UPSERT INTO t_dynamic_columns (p1, c5) VALUES (3, 'f');

需要注意的是,以下示例尝试向表t_dynamic_columns中写入动态列c6,但由于指定的值gf不是HexString,所以该语句执行时报错。

UPSERT INTO t_dynamic_columns (p1, c6) VALUES (4, 'gf');

SQL绑参写入(推荐)

相较于上述直接通过SQL文本直接写动态列,更推荐的做法是在应用程序中通过SQL绑参的方式绑定要写入的字节数组实现写入。如果您需要将字符串或者数值型的值写入动态列中,那么需要先将这些值编码成字节数组,然后通过绑参写入。使用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);
}

查询动态列

查询动态列的场景分为以下几种:

  • 查询字段为动态列。查询动态列表的语法与查询普通列表的语法一致,开启动态列之后,可以查询没有预先在Schema中定义的列,如下示例,c3和c4是创建表后新增的动态列。

    SELECT p1, c3, c4 FROM t_dynamic_columns WHERE p1 = 1;

    返回结果如下:

    +-----+-------+---------+
    | p1  |   c3  |   c4    |
    +-----+-------+---------+
    |  1  | 0x41  |  null   |
    +-----+-------+---------+
    1 rows in set (43 ms)
    重要

    由于动态列的类型是VARBINARY,Lindorm-cli会以HexString的形式展示查询结果,例如0x41表示字母A。

    如果期望查询结果以字符串的格式返回,您需要在启动Lindorm-cli时添加-bytesOutputAsString参数,语法如下:

    ./lindorm-cli -url <jdbc url> -username <用户名> -password <密码> -bytesOutputAsString

    此时,动态列的查询结果将转换为字符串类型。例如上述查询示例中的c3的值为A,再次查询上述语句的返回结果如下:

    +-----+-------+---------+
    | p1  |   c3  |   c4    |
    +-----+-------+---------+
    |  1  | A     |  null   |
    +-----+-------+---------+
    1 rows in set (43 ms)
  • 使用SELECT *查询动态列表时,Lindorm SQL为了保证结果集元数据的正确性,需要在查询语句后加LIMIT。

    SELECT * FROM t_dynamic_columns LIMIT 10;

    返回结果如下:

    +-----+-------+---------+-------+------------+
    | p1  |   c1  |   c2    | c3    |     c4     |
    +-----+-------+---------+-------+------------+
    |  1  | null  |  1      | 0x41  | null       |
    |  2  | null  |  null   | null  | ?        |
    +-----+-------+---------+-------+------------+
    2 rows in set (141 ms)
    重要

    对于动态列表的SELECT *查询操作,LIMIT的默认最大值为5000,超过最大值会报错。

  • WHERE条件中使用动态列。为了确保查询语句的性能WHERE条件中需包含主键或索引列,如果您使用Lindorm-cli或者使用SQL查询动态列时,那么查询语句中指定动态列的过滤条件也必须为HexString。

    接续上述的示例,例如表t_dynamic_columns的列c4为动态列,那么查询成功的语句如下示例:

    SELECT p1, c4 FROM t_dynamic_columns WHERE p1 >= 1 AND p1 < 3 AND c4 = 'ef0011';

    作为对比,下述查询示例中由于给动态列c4指定的过滤条件1不是一个HexString,所以该查询执行失败。

    SELECT p1, c1, c4 FROM t_dynamic_columns WHERE p1 = 2 AND c4 = '1';

字节数组转换为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
}
  • 本页导读 (1)