LindormTable supports dynamic columns. By using this feature, you can dynamically write data to or query data in columns that are not explicitly specified when you create a table. This topic describes how to use Lindorm SQL to enable dynamic columns, and how to write data to or query data in dynamic columns.

Background information

The schema of a traditional relational database table must be predefined. If you want to add columns, you must modify the attributes of the table. The operation of modifying the attributes of a large table requires a long period of time. A predefined schema may be inconvenient for your business design. LindormTable provides the dynamic column feature. You do not need to predefine columns that you want to manage. You can use Lindorm SQL to read data from and write data to dynamic columns.

Usage notes

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.

Enable dynamic columns

Important Dynamic columns cannot be disabled after they are enabled for a 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.

Write data to dynamic columns

Directly write data to dynamic columns by using SQL statements

The syntax that is used to write data to a table for which dynamic columns are enabled is the same as the syntax that is used to write data to a table for which dynamic columns are not enabled. After dynamic columns are enabled for a table, you can write data to columns that are not defined in the table schema. However, the type of data in dynamic columns can only be VARBINARY, which indicates byte arrays. You can execute SQL statements in Lindorm-cli to write data to dynamic columns. The value that you want to write to dynamic columns by using an UPSERT statement must be converted into a hexadecimal string (hex string).

Note A byte can be represented by a decimal number between 0 and 255, or two hexadecimal digits from 0x00 to 0xFF. The hex string for a byte array {0x00, 0xFF} is 00FF. For more information about how to convert a byte array to a hex string, see Convert a byte array to a hex string.

The following examples show how to write data to dynamic columns:

  • The following statement is executed to write data to the c3 column in the t_dynamic_columns table. The c3 column is a dynamic column. The write operation is successful.
    UPSERT INTO t_dynamic_columns (p1, c2, c3) VALUES (1, '1', '41');
  • The following statement is executed to write data to the c4 column in the t_dynamic_columns table. The c4 column is a dynamic column. The write operation is successful.
    UPSERT INTO t_dynamic_columns (p1, c4) VALUES (2, 'ef0011');
  • The following statement is executed to write data to the c5 column in the t_dynamic_columns table. The c5 column is a dynamic column. The write operation fails because the value that is written to the dynamic column c5 is f, which is not a hex string with a length of an even number. You must change the value f to 0f.
    UPSERT INTO t_dynamic_columns (p1, c5) VALUES (3, 'f');
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');

Write data to dynamic columns by specifying parameters in SQL statements

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

Query data in dynamic columns

Scenarios in which data in dynamic columns can be queried 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  | 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';

Convert a byte array to a hex string

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