高速遷移MySQL資料到分散式時序資料庫DolphinDB

DolphinDB提供了兩種匯入MySQL資料的方法：ODBC外掛和MySQL外掛。我們推薦使用MySQL外掛匯入MySQL資料，因為它的速度比ODBC匯入更快，匯入6.5G資料，MySQL外掛的速度是ODBC外掛的4倍，並且使用MySQL外掛無需任何配置，而ODBC外掛需要配置資料來源。

在使用MySQL外掛之前，請先參考DolphinDB安裝使用指南安裝DolphinDB。

1.下載外掛

DolphinDB安裝目錄/server/plugins/mysql已經包含MySQL外掛，使用者可以直接使用該外掛。如果使用者需要自行編譯，可以參考https://github.com/dolphindb/DolphinDBPlugin/blob/master/mysql/README_CN.md。

2.載入外掛

在GUI中，使用loadPlugin函式載入MySQL外掛：

loadPlugin(server_dir+"/plugins/mysql/PluginMySQL.txt")

3.介面函式

DolphinDB的MySQL外掛提供了以下介面函式：

• connect
• showTables
• extractSchema
• load
• loadEx

 

我們可以通過以下兩種方式呼叫外掛的介面函式：

（1）moduleName::apiFunction。例如，呼叫MySQL外掛的connect方法。

mysql::connect(host, port, user, password, db)

（2）use moduleName，然後直接呼叫介面函式。只要執行一次use語句後，後續呼叫介面函式都不需要重新執行use函式。因此，我們一般推薦這種呼叫方法。

use mysql
connect(host, port, user, password, db)

 

3.1 connect

語法

connect(host, port, user, password, db)

引數

host是MySQL伺服器的主機名。

port是MySQL伺服器的埠號，預設為3306。

user是MySQL伺服器中的使用者名稱。

password是與user對應的密碼。

db是MySQL中的資料庫名稱。

詳情

建立MySQL連線，返回MySQL的連線控制程式碼。我們建議MySQL使用者的Authentication Type為mysql_native_password。

例子

連線本地MySQL伺服器中的employees資料庫。

conn=connect("127.0.0.1",3306,"root","123456","employees")

 

3.2 showTables

語法

showTables(connection)

引數

connection是connect函式返回的連線控制程式碼。

詳情

返回一個DolphinDB型別的資料表，包含MySQL資料庫中所有表的名稱。

例子

檢視employees資料庫中的表。

showTables(conn);

Tables_in_employees
current_dept_emp
departments
dept_emp
dept_emp_latest_date
dept_manager
employees
salaries
test_datatypes
titles

 

3.2 extractSchema

語法

extractSchema(connection, tableName)

引數

connection是connect函式返回的連線控制程式碼。

tableName是MySQL資料庫中的資料表名稱。

詳情

返回結果是DolphinDB型別的表。第一列是MySQL資料表中的欄位名，第二列是資料匯入到DolphinDB後的資料型別，第三列是MySQL中的資料型別。

例子

檢視employees表中各列的資料型別。

extractSchema(conn,`employees);

name	        type	  MySQL describe type	
emp_no	        LONG	  int(11)	                
birth_date	DATE	  date	                
first_name	STRING	  varchar(14)	        
last_name	STRING	  varchar(16)	        
gender	        SYMBOL	  enum('M','F')	        
hire_date	DATE	  date	               

3.3 load

語法

load(connection, table|query, [schema], [startRow], [rowNum])

引數

connection是connect函式返回的連線控制程式碼。

table是MySQL伺服器中的表名。

query是MySQL中的查詢語句。

schema是DolphinDB型別的表，它包含兩列，第一列是欄位名稱，第二列是資料型別。它是可選引數。使用者可以通過指定該引數來修改資料載入到DolphinDB時的資料型別。

startRow是正整數，表示讀取資料的起始行數。它是可選引數，預設值為0，表示從第一條記錄開始讀取資料。

rowNum是正整數，表示讀取的記錄行數。它是可選引數，如果沒有指定，表示讀取所有的資料。如果第二個引數為query，那麼startRow和rowNum引數無效。

詳情

把MySQL資料載入到DolphinDB的記憶體表中。

例子

1. 把employees表中的所有資料載入到DolphinDB的記憶體表中。

t=load(conn,"employees");

emp_no	birth_date	first_name	last_name	gender	hire_date
10,001	1953.09.02	Georgi	        Facello	        M	1986.06.26
10,002	1964.06.02	Bezalel	        Simmel	        F	1985.11.21
10,003	1959.12.03	Parto	        Bamford	        M	1986.08.28
10,004	1954.05.01	Chirstian	Koblick	        M	1986.12.01
10,005	1955.01.21	Kyoichi	        Maliniak	M	1989.09.12
10,006	1953.04.20	Anneke	        Preusig	        F	1989.06.02
10,007	1957.05.23	Tzvetan	        Zielinski	F	1989.02.10
10,008	1958.02.19	Saniya	        Kalloufi	M	1994.09.15
10,009	1952.04.19	Sumant	        Peac	        F	1985.02.18
10,010	1963.06.01	Duangkaew	Piveteau	F	1989.08.24
...

2. 把employees表中的前10行資料載入到DolphinDB的記憶體表中。

t=load(conn,"select * from employees limit 10");

emp_no	birth_date	first_name	last_name	gender	hire_date
10,001	1953.09.02	Georgi	        Facello	        M	1986.06.26
10,002	1964.06.02	Bezalel	        Simmel	        F	1985.11.21
10,003	1959.12.03	Parto	        Bamford	        M	1986.08.28
10,004	1954.05.01	Chirstian	Koblick	        M	1986.12.01
10,005	1955.01.21	Kyoichi	        Maliniak	M	1989.09.12
10,006	1953.04.20	Anneke	        Preusig	        F	1989.06.02
10,007	1957.05.23	Tzvetan	        Zielinski	F	1989.02.10
10,008	1958.02.19	Saniya	        Kalloufi	M	1994.09.15
10,009	1952.04.19	Sumant	        Peac	        F	1985.02.18
10,010	1963.06.01	Duangkaew	Piveteau	F	1989.08.24

3. 載入時把last_name的資料型別修改為SYMBOL。

schema=select name,type from extractSchema(conn,`employees)
update schema set type="SYMBOL" where name="last_name"
t=load(conn,"employees",schema)
//檢視錶t的結構
schema(t);

chunkPath->
partitionColumnIndex->-1
colDefs->
name       typeString typeInt
---------- ---------- -------
emp_no     LONG       5      
birth_date DATE       6      
first_name STRING     18     
last_name  SYMBOL     18
gender     SYMBOL     17     
hire_date  DATE       6      

3.4 loadEx

語法

loadEx(connection, dbHandle, tableName, partitionColumns, table|query, [schema], [startRow], [rowNum])

引數

connection是connect函式返回的連線控制程式碼。

dbHandle是DolphinDB的資料庫控制程式碼，通常是database函式返回的物件。

tableName是DolphinDB資料庫中的表名。

partitionColumns是字串標量或向量，表示分割槽列。

table是字串，表示MySQL伺服器中表的名稱。

query是MySQL中的查詢語句。

schema是DolphinDB型別的表，它包含兩列，第一列是欄位名稱，第二列是資料型別。它是可選引數。使用者可以通過指定該引數來修改資料載入到DolphinDB時的資料型別。

startRow是正整數，表示讀取資料的起始行數。它是可選引數，預設值為0，表示從第一條記錄開始讀取資料。

rowNum是正整數，表示讀取的記錄行數。它是可選引數，如果沒有指定，表示讀取所有的資料。如果第二個引數為query，那麼startRow和rowNum引數無效。

詳情

把MySQL中的資料載入到DolphinDB的分割槽表中。loadEx不支援把資料載入到DolphinDB的順序分割槽表中。

例子

把employees表載入到DolphinDB的磁碟VALUE分割槽表中。

db=database("H:/DolphinDB/Data/mysql",VALUE,`F`M)
pt=loadEx(conn,db,"pt","gender","employees")
select count(*) from loadTable(db,"pt");

count
300,024

如果需要把資料載入到記憶體分割槽表，只需要把database的路徑改為空字串；如果需要把資料載入到分散式表，只需要把database路徑修改為以“dfs://”開頭的路徑，比如“dfs://mysql”。分散式表需要在叢集中才能使用。叢集部署請參考單伺服器叢集部署和多伺服器叢集部署。

4. 資料型別轉換

使用MySQL外掛把資料匯入到DolphinDB時，會做相應的型別轉換。具體轉換規則如下表所示：

說明：

（1）DolphinDB中的整型（SHORT, INT, LONG）都是有符號的，為了防止溢位，MySQL中的無符號型別在DolphinDB中都會被轉換為高一階的有符號型別。例如，無符號的tinyint轉換為short，無符號的smallint轉換為short等。目前，MySQL外掛不支援64位無符號型別轉換。

（2）在DolphinDB中，整型的最小值表示NULL：CHAR型別的-128，SHORT型別的-32,768，INT型別的-2,147,483,648，LONG型別的-9,223,372,036,854,775,808都表示NULL。

（3）對於MySQL中的bigint unsigned型別，預設會轉換成DolphinDB的LONG型別。如果出現溢位的情況，需要使用者使用schema引數，指定型別為DOUBLE或FLOAT。

（4）MySQL中的char和varchar型別，如果長度小於等於10，會被轉換成DolphinDB的SYMBOL型別，如果長度大於10，會被轉換成DolphinDB的STRING型別。SYMBOL型別在DolphinDB內部儲存為整數，因此資料排序和比較的效率會更高，同時也可以節省儲存空間。但是將字串對映到整數需要時間，對映表也會佔用記憶體。使用者可以根據下面的規則來決定某列是否採用SYMBOL型別：如果該欄位的值會大量重複出現，使用SYMBOL型別。如金融資料中的股票程式碼、交易所、合約程式碼等，物聯網資料中的裝置編號等都是使用SYMBOL型別的典型場景。

5. 效能測試

我們在普通PC上（16G記憶體，4核8執行緒，使用SSD）進行了效能測試。使用的資料集為美國股票市場1990年到2016年的每日報價資料，資料量為6.5G，包含22個欄位，50,591,907行記錄，在MySQL資料庫中磁碟佔用為7.2G。使用loadEx函式把資料從MySQL匯入到DolphinDB的分割槽資料庫耗時僅160.5秒，讀取速度達到了41.4M/s，在 DolphinDB database 中磁碟佔用為1.3G。在同樣的PC上，由於使用ODBC一次性匯入資料會造成MySQL記憶體不足，因此每次匯入100萬條資料，總耗時660秒。將同樣的資料匯入clickhouse耗時171.9秒，讀取速度為37.8M/s。DolphinDB在時間序列資料的處理和分割槽管理上比clickhouse更加方便。如果既要保證效能，同時友好支援時序資料的各種處理和分散式資料庫，那麼DolphinDB將是不二選擇。