Lindorm:Dynamic columns

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

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

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

The following statement is executed to write data to the c6 column in the t_dynamic_columns table. The c6 column is a dynamic column. The operation fails and an error is returned because the value that is written to c6 is gf, which is not a hex string.

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

We recommend that you write byte arrays to dynamic columns by specifying the byte arrays as parameters in SQL statements in your application. If you want to write strings or numeric values to dynamic columns, you must encode the strings or numeric values to byte arrays and then specify the byte arrays as parameters to write them into dynamic columns. The following Java code provides an example on how to write data into dynamic columns by specifying the data as parameters:

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 scenarios in which data can be queried from dynamic columns are classified into the following types:

• 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  | 0x41  |  null   |
  +-----+-------+---------+
  1 rows in set (43 ms)

  Important

  Data in dynamic columns in LindormTable is of the VARBINARY type. Therefore, the query results are returned as hex strings in Lindorm-cli. The 0x41 value in the preceding results is a hex string that indicates the letter A.

  If you want that the results are returned as strings, you must add the -bytesOutputAsString parameter to the command that you use to start Lindorm-cli in the following format:

  ./lindorm-cli -url <jdbc url> -username <Username> -password <Password> -bytesOutputAsString

  In this case, the query results on dynamic columns are returned as strings. For example, if you perform the preceding sample query again after you add the -bytesOutputAsString parameter to the command that you use to start Lindorm-cli, the following result is returned. In the result, the value of the c3 column is A.

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

• 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      | 0x41  | null       |
  |  2  | null  |  null   | null  | ?        |
  +-----+-------+---------+-------+------------+
  2 rows in set (141 ms)

  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_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';

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