Lindorm:Dynamic columns

• 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.
• 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.

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

  CREATE TABLE tablename (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 tablename SET 'DYNAMIC_COLUMNS' = 'true';

  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_columns 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 IllegalDataException 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.

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

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);

// Insert three columns p1, c1, and c2 into a table. The p1 and c1 columns are defined in the table schema. The c2 column is not defined and is inserted into the table as a dynamic column. 
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);
}

• The field that you want to query is a dynamic column. 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, c3, c4 FROM t_dynamic_columns WHERE p1 = 1;

  The following result is returned:

  +-----+-------+---------+
  | p1  |   c3  |   c4    |
  +-----+-------+---------+
  |  1  | A     |  null   |
  +-----+-------+---------+
  1 rows in set (43 ms)

  Important If you perform the query in the example in Lindorm-cli, the queried byte arrays in dynamic columns are converted to printable characters in the query results. If the byte arrays in dynamic columns cannot be converted to printable characters, they are displayed as question marks or spaces in the query results.

  In the preceding example, the value in the c3 column is displayed as a printable character A, which is converted from the hex string 0x41 that is stored in the c3 column.

• The SELECT * statement is used to query data in a table for which dynamic columns are enabled. In this case, add a LIMIT clause to the end of the statement. This way, Lindorm SQL can ensure the integrity of the metadata of the result set.

  SELECT * FROM t_dynamic_columns LIMIT 10;

  The following result is returned:

  +-----+-------+---------+-------+------------+
  | p1  |   c1  |   c2    | c3    |     c4     |
  +-----+-------+---------+-------+------------+
  |  1  | null  |  1      | A     | null       |
  |  2  | null  |  null   | null  | ?          |
  +-----+-------+---------+-------+------------+
  2 rows in set (141 ms)

  Note 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_columns is a dynamic column, the following statement can be executed to perform a successful query:

  SELECT p1, c4 FROM t_dynamic_columns WHERE p1 >= 1 AND p1 < 3 AND c4 = '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_columns WHERE p1 = 2 AND c4 = '1';

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.
}