Lindorm:Dynamic columns

Before you use the dynamic column feature, take note of the following items:

• Make sure that the version of LindormTable is 2.2.19 or later. For more information, see Upgrade the minor engine version of a Lindorm instance.Lindorm

• Data in dynamic columns in LindormTable is of the VARBINARY type. You must convert the data type of dynamic columns to byte arrays when you query data in or write data to dynamic columns.

• If you use the HBase API for Java to create a table or write data to a table, you can use Lindorm SQL to query data from and write data to dynamic columns in the table.

You can use one of the following methods to enable dynamic columns:

• Enable dynamic columns by using the WITH clause when you create a table.

  CREATE TABLE t_dynamic (p1 INT, c1 INT, c2 VARCHAR, PRIMARY KEY(p1)) WITH (DYNAMIC_COLUMNS='true');

• Modify the attributes of an existing table to enable dynamic columns.

  ALTER TABLE t_dynamic SET 'DYNAMIC_COLUMNS' = 'true';

  Verify the result

  You can execute the following statement to verify whether dynamic columns are enabled for a table:

  SHOW TABLE VARIABLES FROM t_dynamic LIKE 'DYNAMIC_COLUMNS';

  Note

  After dynamic columns are enabled for a table, you can modify the attributes of the table to add new columns to the schema of the table. For example, you can execute the following statement to add the c3 column of the INT data type to the schema of the table.

  ALTER TABLE t_dynamic ADD COLUMN c3 int;

  If you write data to a dynamic column named c3 before you add the c3 column to the schema of the table, an exception is returned when you insert INT data to or query INT data in c3 because the type of data written to c3 is VARBINARY. This exception is not returned if the data type of the c3 column that you add to the schema of the table is VARBINARY. Therefore, take note of the data types when you change the schema of a table for which dynamic columns are enabled. Do not set the name of a predefined column to the name of an existing dynamic column.

The scenarios in which data can be queried in dynamic columns are classified into the following types:

• The field that you want to query is a dynamic column that is explicitly defined. The syntax that is used to query data in a table for which dynamic columns are enabled is the same as the syntax that is used to query data in a table for which dynamic columns are not enabled. After you enable dynamic columns for a table, you can query data in columns that are not defined in the table schema. In the following example, c3 and c4 are dynamic columns that are added after the table is created.

  SELECT p1, c2, c3, c4 FROM t_dynamic WHERE p1 = 1;

  The following result is returned:

  +----+----+------+------+
  | p1 | c2 |  c3  |  c4  |
  +----+----+------+------+
  | 1  | 1  | 0x41 | null |
  +----+----+------+------+

• To view the dynamic columns contained in a table, you can execute the SELECT * statement to query all columns in the table. In this case, you must add a LIMIT clause to the end of the statement to limit the size of the returned result set. This way, Lindorm SQL can ensure the integrity of the metadata of the result set.

  SELECT * FROM t_dynamic LIMIT 10;

  The following result is returned:

  +----+------+------+------+----------+
  | p1 |  c1  |  c2  |  c3  |    c4    |
  +----+------+------+------+----------+
  | 1  | null | 1    | 0x41 | null     |
  | 2  | null | null | null | 0xef0011 |
  | 3  | null | null | null | 0xef0011 |
  +----+------+------+------+----------+

  Important

  If you use a SELECT * statement with the LIMIT clause specified to query data in a table for which dynamic columns are enabled, the default maximum value of LIMIT is 5,000. You can specify a maximum value. If the queried value exceeds the maximum value, an error is returned.

• Dynamic columns are used in WHERE conditions.

  To ensure query performance, you must include the primary key or index key columns in WHERE conditions. If you use Lindorm-cli or SQL statements to query data in dynamic columns, the values in dynamic columns in WHERE conditions must be hex strings.

  For example, if the c4 column in the table t_dynamic is a dynamic column, the following statement can be executed to perform a successful query:

  SELECT p1, c4 FROM t_dynamic WHERE p1 = 3 AND c4 = x'ef0011';

  In contrast, the following query fails because the value 1 of the c4 column in the WHERE conditions is not a hex string.

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

The following Java code provides an example on how to convert a byte array to a hex string:

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";
  // You can use the getBytes() method of a string to obtain the byte array that corresponds to the string.
  byte[] bytes = s.getBytes(Charset.forName("UTF-8"));
  String hexString = toHexString(bytes);
  System.out.println(hexString); // The output is 48656c6c6f2c20776f726c64.
}