关于dolphindb:干货丨如何高速迁移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
...

1. 把 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

1. 加载时把 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 database 将是不二抉择。