全部產品
Search
文件中心

Lindorm:動態列

更新時間:Aug 02, 2024

Lindorm寬表引擎支援動態列功能,即建立表時未顯式指定的列,在實際業務中動態寫入資料並執行查詢。本文介紹如何通過Lindorm SQL實現動態列能力的開啟、寫入以及查詢。

背景資訊

傳統關係型資料庫表的Schema必須預先定義,如果要增加列,則需要變更表屬性。變更大表的表屬性是一個非常耗時的操作。同時,預定義的Schema給業務的設計會帶來很多不便。但是Lindorm寬表引擎原生支援動態列,列無需提前定義,您可以直接使用Lindorm SQL來對動態列進行讀寫操作。

注意事項

如果您需要使用動態列功能,請注意以下幾點:

  • 確保Lindorm寬表引擎為2.2.19及以上版本,具體操作請參見升級小版本

  • Lindorm寬表中動態列類型均為VARBINARY,查詢動態列和寫入動態列時都必須將動態列類型轉化成位元組數組。

  • 通過Lindorm SQL管理動態列,查詢動態列和寫入動態列操作支援以ApsaraDB for HBase相容方式建立和寫入的表。

開啟動態列

重要

一張表的動態列功能一旦啟用則無法關閉。

動態列的開啟可以通過以下兩種方式:

  • 在建立表格時通過WITH子句開啟動態列功能。

    CREATE TABLE t_dynamic (p1 INT, c1 INT, c2 VARCHAR, PRIMARY KEY(p1)) WITH (DYNAMIC_COLUMNS='true');
  • 通過修改表的屬性開啟動態列功能。

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

    結果驗證

    您可以通過以下語句驗證表是否已成功開啟動態列功能。

    SHOW TABLE VARIABLES FROM t_dynamic LIKE 'DYNAMIC_COLUMNS';
    說明

    開啟動態列後,您可以修改表的屬性或者在表的Schema中增加新的列,例如執行以下語句表示新增c3列,資料類型為INT。

    ALTER TABLE t_dynamic ADD COLUMN c3 int;

    如果您在此之前寫入過名為c3的動態列,由於寫入的資料類型均為VARBINARY,查詢資料和寫入資料時會拋出異常,如果新增列的資料類型為VARBINARY就不會出現這個異常。因此,變更動態列表的Schema時需要注意資料類型不同的情況,盡量避免預定義列和動態列重名。

寫入動態列

SQL文本寫入

寫入動態列表的文法與寫入普通列表的文法一致,開啟動態列之後,可以寫入沒有預先在Schema中定義的列,但是動態列的類型只能為VARBINARY(即位元組數組)。Lindorm支援使用者使用Lindorm-cli直接以SQL文本的方式將資料寫入動態列,此時UPSERT語句中指定的動態列的值必須為資料的十六進位字串形式(即使用十六進位字元(0-9, A-F)來表示位元據的形式。以下簡稱HexString)。

說明

一個位元組的範圍是0~255,十六進位表示為0x00~0xFF。對於一個位元組數組{0x00, 0xFF},它的HexString就是00FF,將位元組數群組轉換為HexString的代碼請參見附錄:位元組數群組轉換為HexString的實現樣本

寫入動態列的情境樣本如下:

  • 執行以下語句在t_dynamic表中寫入c3列,c3列為動態列,寫入成功。

    UPSERT INTO t_dynamic (p1, c2, c3) VALUES (1, '1', '41');
  • 執行以下語句在t_dynamic表中寫入c4列,c4列為動態列,寫入成功。

    UPSERT INTO t_dynamic (p1, c4) VALUES (2, 'ef0011');
  • 在SQL引擎2.6.8及以上的版本中,為了避免混淆一般字元串和HexString,支援在SQL文本中通過下述形式指定HexString。

    UPSERT INTO t_dynamic(p1, c4) VALUES (3, x'ef0011');

    上述語句儲存的動態列c4的資料實際是0xEF0x000x11這三個位元組,而非ef0011這個字串(ef0011字串需要佔用6個位元組的儲存空間)。

    說明

    如何查看Lindorm SQL的版本,請參見SQL版本說明

  • 執行以下語句在t_dynamic表中寫入c5列,c5列為動態列。由於動態列c5的值f不是偶數長度的HexString,所以寫入失敗,需要將f修改為0f

    UPSERT INTO t_dynamic (p1, c5) VALUES (4, 'f');
  • 執行以下語句在t_dynamic表中寫入動態列c6,但由於指定的值gf不是HexString,所以該語句執行時報錯。

    UPSERT INTO t_dynamic (p1, c6) VALUES (5, x'gf');

SQL參數化寫入(推薦)

相較於上述直接通過SQL文本直接寫動態列,更推薦的做法是在應用程式中通過SQL綁參的方式綁定要寫入的位元組數組實現寫入。如果您需要將字串或者數值型的值寫入動態列中,那麼需要先將這些值編碼成位元組數組,然後通過綁參寫入。

以表t_dynamic為例,使用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);
}
重要

使用綁參方式寫入資料時,理論上可以通過類似JDBC中的PreparedStatement#setString( )方法傳入一個HexString來寫入資料,但強烈建議不要這樣操作,尤其是使用MySQL協議與Lindorm進行互動時。因為在MySQL協議中,用戶端傳入的字串型參數會以位元組數組的形式傳至服務端,可能導致資料的二義性,因此強烈建議避免這種操作。

查詢動態列

查詢動態列的情境分為以下幾種:

  • 顯式指定查詢欄位為動態列。查詢動態列表的文法與查詢普通列表的文法一致,開啟動態列之後,可以查詢沒有預先在Schema中定義的列,如下樣本,c3和c4是建立表後新增的動態列。

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

    返回結果如下:

    +----+----+------+------+
    | p1 | c2 |  c3  |  c4  |
    +----+----+------+------+
    | 1  | 1  | 0x41 | null |
    +----+----+------+------+
  • 不確定表中已包含哪些動態列時,可以使用SELECT *查詢動態列表。Lindorm SQL為了保證結果集中繼資料的正確性,強制要求在此類查詢語句後添加LIMIT子句來限定結果集的大小。

    SELECT * FROM t_dynamic LIMIT 10;

    返回結果如下:

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

    對於動態列表的SELECT *查詢操作,LIMIT的預設最大值為5000,超過最大值會報錯。

  • WHERE條件中使用動態列。

    為了確保查詢語句的效能WHERE條件中需包含主鍵或索引列,如果您希望在查詢的過濾條件中使用動態列進行過濾,那麼使用SQL文字查詢時,動態列的過濾條件必須指定為HexString;使用綁參方式進行查詢時,過濾條件建議直接指定為原始的位元組數組。

    例如表t_dynamicc4為動態列,那麼查詢成功的語句如下樣本:

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

    作為對比,下述查詢樣本中由於給動態列c4指定的過濾條件1不是一個HexString,所以該查詢執行失敗。

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

動態列資料的顯示

使用不同的命令列工具時,動態列的查詢結果顯示方式不同,具體如下:

Lindorm-cli

HexString

Lindorm-cli會以HexString的形式展示動態列的查詢結果。以表t_dynamic為例,查詢語句如下:

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

返回結果如下:

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

上述結果集中動態列c3的第一行查詢結果0x41表示字母A。

字串格式

如果您期望查詢結果以字串的格式返回,需要通過Lindorm-cli的串連語句末尾添加-bytesOutputAsString參數,樣本如下:

./lindorm-cli -url <jdbc url> -username <使用者名稱> -password <密碼> -bytesOutputAsString
說明

串連參數說明請參見步驟二:串連Lindorm寬表引擎

執行相同查詢語句SELECT p1, c3, c4 FROM t_dynamic WHERE p1 = 1;,將返回如下結果:

+-----+-------+---------+
| p1  |   c3  |   c4    |
+-----+-------+---------+
|  1  | A     |  null   |
+-----+-------+---------+

MySQL命令列

MySQL命令列並不會預設顯示動態列的資料,這些位元據將會以?表示。

附錄:位元組數群組轉換為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
}