本文介紹如何在AnalyticDB for MySQL湖倉版中查詢並匯入Tablestore資料。
前提條件
背景資訊
Tablestore是阿里雲自研的NoSQL多模型資料庫,支援多元索引查詢。您可以在AnalyticDB for MySQL中調用TableStore API進行資料查詢,例如點查詢、範圍查詢等。同時,支援智能選擇主表、二級索引表和多元索引表,大大提升查詢效能,降低使用者成本。
TableStore執行個體是您使用和管理Table Store服務的實體,每個執行個體相當於一個資料庫。Tablestore的執行個體對應AnalyticDB for MySQL的Schema或Database。Tablestore執行個體如果沒有綁定VPC網路,那麼AnalyticDB for MySQL可以直接存取。如果Tablestore執行個體綁定了VPC網路,那麼請確保AnalyticDB for MySQL和Tablestore執行個體配置的VPC相同。
樣本資料說明
本文樣本中,在Tablestore中建立person
表,並向表中插入4條記錄。如果您已經有資料來源,請跳過該步驟。
id(主鍵) | name | age |
1 | james | 10 |
2 | bond | 20 |
3 | jack | 30 |
4 | lucy | 40 |
操作步驟
進入SQL開發編輯器。
登入雲原生資料倉儲AnalyticDB MySQL控制台,在左上方選擇叢集所在地區。在左側導覽列,單擊集群清單,在湖倉版頁簽下,單擊目的地組群ID。
在左側導覽列,單擊 。
建立外部資料庫。
以建立
adb_external_db
資料庫為例,建庫樣本語句如下:CREATE EXTERNAL database adb_external_db;
建立外表。
說明AnalyticDB for MySQL外表和TableStore中表的欄位名稱、欄位數量、欄位順序一致,欄位類型相容。資料類型映射關係,請參見資料類型映射關係。
在
adb_external_db
資料庫中,建立person
表,建表示例語句如下:CREATE EXTERNAL TABLE IF NOT EXISTS adb_external_db.person ( id int, name string, age int ) ENGINE = 'OTS' TABLE_PROPERTIES = '{ "mapped_name":"person", "location":"https://w0****la.cn-hangzhou.vpc.tablestore.aliyuncs.com" }'
參數
說明
ENGINE='OTS’
外表的儲存引擎。讀寫Tablestore資料時,取值為OTS。
mapped_name
Tablestore執行個體中的表名稱。查看Tablestore執行個體的表名稱,登入Table Store控制台,在執行個體管理頁面查看。
location
Tablestore執行個體的VPC訪問地址。查看Tablestore執行個體的VPC訪問地址,登入Table Store控制台,在執行個體管理頁面查看。
外表建立成功後,AnalyticDB for MySQL自動將Tablestore的表資料對應到AnalyticDB for MySQL表。您可以在AnalyticDB for MySQL中通過SELECT查詢Tablestore中
person
表的資料。SELECT * FROM adb_external_db.person;
返回結果如下:
+------+-------+------+ | id | name | age | +------+-------+------+ | 1 | james | 10 | | 2 | bond | 20 | | 3 | jack | 30 | | 4 | lucy | 40 | +------+-------+------+ 4 rows in set (0.35 sec)
如果需要匯入Tablestore資料到AnalyticDB for MySQL,可查看接下來的步驟。
在AnalyticDB for MySQL中建立資料庫,樣本如下:
CREATE DATABASE adb_demo;
在AnalyticDB for MySQL中建立表用於儲存從Tablestore中匯入的資料,樣本如下:
說明新表和步驟3中建立的外表的欄位順序和欄位數量需要一致,欄位類型相容。
CREATE TABLE IF NOT EXISTS adb_demo.adb_import_test( id int, name string, age int ) DISTRIBUTED BY HASH(id);
向表中寫入資料,樣本如下:
方式一:執行INSERT INTO匯入資料,當主鍵重複時會自動忽略當前寫入資料,不做更新,作用等同於
INSERT IGNORE INTO
,詳情請參見INSERT INTO。樣本如下:INSERT INTO adb_demo.adb_import_test SELECT * FROM adb_external_db.person;
通過SELECT查詢寫入表中的資料,樣本如下:
SELECT * FROM adb_demo.adb_import_test;
返回結果如下:
+------+-------+------+ | id | name | age | +------+-------+------+ | 1 | james | 10 | | 2 | bond | 20 | | 3 | jack | 30 | | 4 | lucy | 40 | +------+-------+------+
方式二:執行INSERT OVERWRITE匯入資料,會覆蓋表中原有的資料。樣本如下:
INSERT OVERWRITE adb_demo.adb_import_test SELECT * FROM adb_external_db.person;
方式三:非同步執行INSERT OVERWRITE匯入資料。通常使用
SUBMIT JOB
提交非同步任務,由後台調度,可以在寫入任務前增加Hint(/*+ direct_batch_load=true*/
)加速寫入任務。詳情請參見非同步寫入。樣本如下:SUBMIT JOB INSERT OVERWRITE adb_demo.adb_import_test SELECT * FROM adb_external_db.person;
返回結果如下:
+---------------------------------------+ | job_id | +---------------------------------------+ | 2020112122202917203100908203303****** |
關於非同步提交任務詳情,請參見非同步提交匯入任務。
資料類型映射關係
Tablestore支援的資料類型與AnalyticDB for MySQL資料類型的映射關係如下:
Tablestore支援的資料類型 | 映射到AnalyticDB for MySQL的資料類型 |
INTEGER(8位元組) | BIGINT(8位元組) |
STRING | VARCHAR |
BINARY | BINARY |
DOUBLE | DOUBLE |
BOOLEAN | BOOLEAN |