通過外表將MongoDB資料匯入至湖倉版 - AnalyticDB

本文介紹如何通過外表查詢MongoDB資料，以及如何將MongoDB中的資料匯入至AnalyticDB for MySQL。

前提條件

AnalyticDB for MySQL叢集的產品系列為湖倉版。
已在AnalyticDB for MySQL控制台叢集資訊頁面的網路資訊列啟用ENI網路開關。
MongoDB執行個體與AnalyticDB for MySQL所屬同一VPC。具體操作，請參見建立叢集。
已將AnalyticDB for MySQL叢集的VPC網段加入MongoDB執行個體的白名單中。具體操作，請參見修改白名單。

匯入MongoDB非嵌套文檔

樣本資料說明

本文樣本的MongoDB資料庫名為test_mongodb，並在該庫中建立名為person的集合，樣本如下：

use test_mongodb;
db.createCollection("person");

向person集合中插入文檔，樣本如下：

db.person.insert({"id":1,"name":"james","age":10});
db.person.insert({"id":2,"name":"bond","age":20});
db.person.insert({"id":3,"name":"jack","age":30});
db.person.insert({"id":4,"name":"lock","age":40});

操作步驟

進入SQL開發編輯器。
1. 登入雲原生資料倉儲AnalyticDB MySQL控制台，在左上方選擇叢集所在地區。在左側導覽列，單擊集群清單，在湖倉版頁簽下，單擊目的地組群ID。
2. 在左側導覽列，單擊作業開發 > SQL開發。
執行以下語句，建立外部資料庫。樣本如下：
```
CREATE EXTERNAL DATABASE adb_external_db;
```

建立外表。樣本如下：

重要

AnalyticDB for MySQL的外表和MongoDB文檔中的欄位（field）名稱、欄位數量、欄位順序、資料類型必須相同。

CREATE EXTERNAL TABLE adb_external_db.person (
  id int,
  name string,
  age int
) ENGINE = 'MONGODB' TABLE_PROPERTIES ='{
"mapped_name":"person",
"location":"mongodb://testuser:****@dds-bp113d414bca8****.mongodb.rds.aliyuncs.com:3717,dds-bp113d414bca8****.mongodb.rds.aliyuncs.com:3717/test_mongodb",
"username":"testuser",
"password":"password",
}';

表 1. 參數說明

參數	說明
mapped_name	MongoDB集合的名稱。本文樣本中為`person`。
location	MongoDB的專用網路地址。如何擷取專用網路的串連地址，請參見執行個體串連地址說明。格式：`mongodb://testuser:**@dds-bp113d414bca8.mongodb.rds.aliyuncs.com:3717,dds-bp113d414bca8.mongodb.rds.aliyuncs.com:3717/database`。樣本：`mongodb://testuser:@dds-bp113d414bca8.mongodb.rds.aliyuncs.com:3717,dds-bp113d414bca8**.mongodb.rds.aliyuncs.com:3717/test_mongodb`。說明在串連MongoDB時，請勿使用Secondary節點地址。
username	MongoDB資料庫的帳號。如何建立資料庫帳號，請參見MongoDB資料庫帳號許可權管理。說明 MongoDB需要在目標資料庫中校正資料庫的帳號和密碼，請使用MongoDB專用網路地址中指定資料庫的帳號，如遇問題，請聯絡支援人員。
password	MongoDB資料庫帳號的密碼。

查詢資料。

外表建立成功後，您可以使用SELECT語句查詢person集合中的資料。

SELECT * FROM adb_external_db.person;

返回結果：

+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    1 | james |   10 |
|    2 | bond  |   20 |
|    3 | jack  |   30 |
|    4 | lock  |   40 |
+------+-------+------+
4 rows in set (0.35 sec)

在AnalyticDB for MySQL叢集中建立資料庫和表，用於儲存從MongoDB中匯入的資料。
1. 建立名為adb_demo的資料庫。
```
CREATE DATABASE adb_demo; 
```
2. 建立名為adb_demo.adb_import_test的資料表。
  重要
  AnalyticDB for MySQL企業版、基礎版及湖倉版中建立的表和AnalyticDB for MySQL外表中的欄位名稱、欄位數量、欄位順序、資料類型必須相同。
```
 CREATE TABLE IF NOT EXISTS adb_demo.adb_import_test(id int,name varchar(1023),age int ) DISTRIBUTED BY HASH(id);
```
將MongoDB中的資料匯入至AnalyticDB for MySQL企業版、基礎版及湖倉版中。
- 方法一：使用INSERT INTO語句匯入資料，當主鍵重複時會自動忽略當前寫入資料，資料不做更新，作用等同於INSERT IGNORE INTO，更多資訊，請參見INSERT INTO。樣本如下：
```
INSERT INTO adb_demo.adb_import_test SELECT * FROM adb_external_db.person;
```
- 方法二：使用INSERT OVERWRITE INTO語句同步匯入資料，會覆蓋表中原有的資料。樣本如下：
```
INSERT OVERWRITE INTO adb_demo.adb_import_test SELECT * FROM adb_external_db.person;
```
- 方法三：使用INSERT OVERWRITE INTO語句非同步匯入資料，更多資訊，請參見非同步寫入。樣本如下：
```
SUBMIT JOB INSERT OVERWRITE adb_demo.adb_import_test SELECT * FROM adb_external_db.person;
```

資料匯入完成後，您可以使用SELECT語句查詢adb_demo.adb_import_test表中的資料。

SELECT * FROM adb_demo.adb_import_test;

返回結果：

+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    1 | james |   10 |
|    2 | bond  |   20 |
|    3 | jack  |   30 |
|    4 | lock  |   40 |
+------+-------+------+

查詢MongoDB嵌套文檔

樣本資料

在資料庫test_mongodb中建立名為test_json的集合，樣本如下：

db.createCollection("test_json");

向test_json集合中插入文檔，其中city和name為嵌套欄位，樣本如下：

db.test_json.insert(    {
        'id': 1,
        'details':{'city': "hangzhou", "name":"jack"}
    })

操作步驟

進入SQL開發編輯器。
1. 登入雲原生資料倉儲AnalyticDB MySQL控制台，在左上方選擇叢集所在地區。在左側導覽列，單擊集群清單，在湖倉版頁簽下，單擊目的地組群ID。
2. 在左側導覽列，單擊作業開發 > SQL開發。
執行以下語句，建立外部資料庫。樣本如下：
```
CREATE EXTERNAL DATABASE adb_external_db;
```
建立外表。樣本如下：
重要
- AnalyticDB for MySQL的外表和MongoDB文檔中的欄位（field）名稱、欄位數量、欄位順序、資料類型必須相同。
- 不支援通過外表將資料寫入MongoDB嵌套文檔。
```
CREATE EXTERNAL TABLE adb_external_db.test_json (
  id int,
  city string,
  name string
) ENGINE = 'MONGODB' TABLE_PROPERTIES ='{
"mapped_name":"test_json",
"location":"mongodb://testuser:****@dds-bp113d414bca8****.mongodb.rds.aliyuncs.com:3717,dds-bp113d414bca8****.mongodb.rds.aliyuncs.com:3717/test_mongodb",
"username":"testuser",
"password":"password",
"COLUMN_MAPPING":"city,details.city;name,details.name",
}';
```
參數說明：
- COLUMN_MAPPING：定義外表欄位與MongoDB欄位的映射關係。例如：外表的city欄位與MongoDB文檔的details.city欄位為映射關係。
- 外表中其他參數的詳細說明，請參見參數說明。
查詢資料。
外表建立成功後，您可以使用SELECT語句查詢test_json集合中的資料。
```
SELECT * FROM adb_external_db.test_json;
```
返回結果：
```
+------+----------+-------+
| id   |  city    |  name |
+------+----------+-------+
|    1 | hangzhou |  jack |
+------+----------+-------+
```
說明
如果要將MongoDB嵌套文檔匯入到AnalyticDB for MySQL中，需要先建立用於儲存MongoDB嵌套文檔的資料庫和資料表，具體操作，請參見匯入MongoDB非嵌套文檔中的步驟5~7。

查詢ObjectId欄位

樣本資料

在資料庫test_mongodb中建立名為test_objectid的集合，樣本如下：

db.createCollection("test_objectid");

向test_objectid集合中插入文檔，樣本如下：

db.test_objectid.insert(    {
        'id': 1,
    })

查詢test_objectid集合中的文檔，樣本如下：

db.test_objectid.find()

返回結果：

{
   "_id":"ObjectId("641002ad883a73eb0d7291a7")"
   "id":1
}

操作步驟

進入SQL開發編輯器。
1. 登入雲原生資料倉儲AnalyticDB MySQL控制台，在左上方選擇叢集所在地區。在左側導覽列，單擊集群清單，在湖倉版頁簽下，單擊目的地組群ID。
2. 在左側導覽列，單擊作業開發 > SQL開發。
執行以下語句，建立外部資料庫。樣本如下：
```
CREATE EXTERNAL DATABASE adb_external_db;
```

建立外表。樣本如下：

重要

AnalyticDB for MySQL的外表和MongoDB文檔中的欄位（field）名稱、欄位數量、欄位順序、資料類型必須相同。

CREATE EXTERNAL TABLE adb_external_db.test_objectid (
  id int,
  _id objectid
) ENGINE = 'MONGODB' TABLE_PROPERTIES ='{
"mapped_name":"test_objectid",
"location":"mongodb://testuser:****@dds-bp113d414bca8****.mongodb.rds.aliyuncs.com:3717,dds-bp113d414bca8****.mongodb.rds.aliyuncs.com:3717/test_mongodb",
"username":"testuser",
"password":"password",
}';

查詢資料。
外表建立成功後，您可以使用SELECT語句查詢test_objectid集合中的資料。
```
SELECT cast(_id as string) FROM adb_external_db.test_objectid;
```
返回結果：
```
+----------------------------+
|    CAST(_id AS string)     | 
+----------------------------+
|  641002ad883a73eb0d7291a7  |
+----------------------------+
```
說明
如果要將ObjectId欄位匯入到AnalyticDB for MySQL中，需要先建立用於儲存ObjectId欄位的資料庫和資料表，具體操作，請參見匯入MongoDB非嵌套文檔中的步驟5~7。

外表支援的資料類型

AnalyticDB for MySQL外表	MongoDB資料表
Boolean	Boolean
ObjectId	ObjectId
String	String
Int	32-bit Integer、Int
Bigint	64-bit Integer Long
Double	Double
Date	Date