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
的資料實際是0xEF
、0x00
和0x11
這三個位元組,而非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_dynamic
中c4
為動態列,那麼查詢成功的語句如下樣本: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
}