MySQL

DolphinDB的MySQL导入插件可将MySQL中的数据表或语句查询结果高速导入DolphinDB,并且支持数据类型转换。本插件的部分设计参考了来自Yandex.Clickhouse的mysqlxx组件。

在插件市场安装插件

版本要求

  • DolphinDB Server: 2.00.10及更高版本

安装步骤

  1. 在DolphinDB 客户端中使用 listRemotePlugins 命令查看插件仓库中的插件信息。

    login("admin", "123456")
    listRemotePlugins()
  2. 使用 installPlugin 命令完成插件安装。

    installPlugin("mysql")
  3. 使用 loadPlugin 命令加载插件。

    loadPlugin("mysql")

接口说明

请注意:使用插件函数前需使用loadPlugin函数导入插件。

mysql::connect

语法

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

参数

  • host: MySQL服务器的地址,类型为string。
  • port: MySQL服务器的端口,类型为int。
  • user: MySQL服务器的用户名,类型为string。
  • password: MySQL服务器的密码,类型为string。
  • db: 要使用的数据库名称,类型为string。

详情

与MySQL服务器建立一个连接。返回一个MySQL连接的句柄,用于loadloadEx等操作。

例子

conn = mysql::connect(`127.0.0.1, 3306, `root, `root, `DolphinDB)

mysql::showTables

语法

mysql::showTables(connection)

参数

  • connection: 通过mysql::connect获得的MySQL连接句柄。

详情

列出建立MySQL连接时指定的数据库中包含的所有表。

例子

conn = mysql::connect(`192.168.1.16, 3306, `root, `root, `DolphinDB)
mysql::showTables(conn)

output:
  Tables_in_DolphinDB
  -------------------
  US

mysql::extractSchema

语法

mysql::extractSchema(connection, tableName)

参数

  • connection: 通过mysql::connect获得的MySQL连接句柄。
  • tableName: MySQL表名,类型为string。

详情

生成指定数据表的结构。

例子

conn = mysql::connect(`192.168.1.16, 3306, `root, `root, `DolphinDB)
mysql::extractSchema(conn, `US)

output:
        name    type   DolphinDBType
        PERMNO  int(11)     INT
        date    date        DATE
        SHRCD   int(11)     INT
        TICKER  varchar(10) STRING
        ...
        PRC     double      DOUBLE

mysql::load

语法

mysql::load(connection, table_or_query, [schema], [startRow], [rowNum], [allowEmptyTable])

参数

  • connection: 通过mysql::connect获得的MySQL连接句柄。
  • table_or_query: 一张MySQL中表的名字,或者类似 select * from table limit 100 的合法MySQL查询语句,类型为string。
  • schema: 包含列名和列的数据类型的表。如果我们想要改变由系统自动决定的列的数据类型,需要在schema表中修改数据类型,并且把它作为load函数的一个参数。
  • startRow: 读取MySQL表的起始行数,若不指定,默认从数据集起始位置读取。若'table_or_query'是查询语句,则这个参数不起作用。
  • rowNum: 读取MySQL表的行数,若不指定,默认读到数据集的结尾。若'table_or_query'是查询语句,则这个参数不起作用。
  • allowEmptyTable : 一个布尔值,表示是否允许从MySQL读取空表,默认为不允许。

详情

将MySQL表或者SQL查询结果导入DolphinDB中的内存表。支持的数据类型以及数据转化规则可见用户手册数据类型章节。

例子

conn = mysql::connect(`192.168.1.18, 3306, `root, `root, `DolphinDB)
tb = mysql::load(conn, `US,,0,123456)
select count(*) from tb
conn = mysql::connect(`127.0.0.1, 3306, `root, `root, `DolphinDB)
tb = mysql::load(conn, "SELECT PERMNO FROM US LIMIT 123456")
select count(*) from tb
mysql::load(conn, "SELECT now(6)");

mysql::loadEx

语法

mysql::loadEx(connection, dbHandle,tableName,partitionColumns,table_or_query,[schema],[startRow],[rowNum],[transform],[sortColumns],[keepDuplicates],[sortKeyMappingFunction])

参数

  • connection: 通过mysql::connect获得的MySQL连接句柄。
  • dbHandle 与 tableName: 若要将输入数据文件保存在分布式数据库中,需要指定数据库句柄和表名。
  • partitionColumns: 字符串标量或向量,表示分区列。在组合分区中,partitionColumns是字符串向量。
  • table_or_query: MySQL中表的名字,或者类似 select * from table limit 100 的合法MySQL查询语句,类型为string。
  • schema: 包含列名和列的数据类型的表。若要修改由系统自动检测的列的数据类型,需要在schema表中修改数据类型,并且把它作为load函数的一个参数。
  • startRow: 读取MySQL表的起始行数,若不指定,默认从数据集起始位置读取。若'table_or_query'是查询语句,则这个参数不起作用。
  • rowNum: 读取MySQL表的行数,若不指定,默认读到数据集的结尾。若'table_or_query'是查询语句,则这个参数不起作用。
  • transform: 导入到DolphinDB数据库前对MySQL表进行转换,例如替换列。
  • sortColumns: 字符串标量或向量,用于指定表的排序列,写入的数据将按 sortColumns 进行排序,只在创建 TSDB 引擎表时需要。
  • keepDuplicates: 指定在每个分区内如何处理所有 sortColumns 之值皆相同的数据,ALL 用于保留所有数据,为默认值,LAST 仅保留最新数据,FIRST 仅保留第一条数据,只在创建 TSDB 引擎表时需要。
  • sortKeyMappingFunction: 由一元函数对象组成的向量,其长度与索引列一致,即 sortColumns 的长度 - 1。用于指定应用在索引列中各列的映射函数,以减少 sort key 的组合数,该过程称为 sort key 降维,只在创建 TSDB 引擎表时需要。

详情

将MySQL中的表或查询结果转换为DolphinDB数据库的分布式表,然后将表的元数据加载到内存中。支持的数据类型,以及数据转化规则可见数据类型章节。

例子

  • 将数据导入磁盘上的分区表
dbPath = "C:/..."
db = database(dbPath, RANGE, 0 500 1000)
mysql::loadEx(conn, db,`tb, `PERMNO, `US)
tb = loadTable(dbPath, `tb)
dbPath = "C:/..."
db = database(dbPath, RANGE, 0 500 1000)
mysql::loadEx(conn, db,`tb, `PERMNO, "SELECT * FROM US LIMIT 1000");
tb = loadTable(dbPath, `tb)
  • 将数据导入内存中的分区表

直接原表导入

db = database("", RANGE, 0 50000 10000)
tb = mysql::loadEx(conn, db,`tb, `PERMNO, `US)

通过SQL导入

db = database("", RANGE, 0 50000 10000)
tb = mysql::loadEx(conn, db,`tb, `PERMNO, "SELECT * FROM US LIMIT 100");
  • 将数据导入DFS分布式文件系统中的分区表

直接原表导入

db = database("dfs://US", RANGE, 0 50000 10000)
mysql::loadEx(conn, db,`tb, `PERMNO, `US)
tb = loadTable("dfs://US", `tb)

通过SQL导入

db = database("dfs://US", RANGE, 0 50000 10000)
mysql::loadEx(conn, db,`tb, `PERMNO, "SELECT * FROM US LIMIT 1000");
tb = loadTable("dfs://US", `tb)

导入前对MySQL表进行转换

db = database("dfs://US", RANGE, 0 50000 10000)
def replaceTable(mutable t){
	return t.replaceColumn!(`svalue,t[`savlue]-1)
}
t=mysql::loadEx(conn, db, "",`stockid, 'select  * from US where stockid<=1000000',,,,replaceTable)

mysql::close

语法

mysql::close(connection)

参数

  • connection: 通过mysql::connect获得的MySQL连接句柄。

详情

断开连接,关闭 MySQL 句柄。

例子

mysql::close(conn)

支持的数据类型

整型

MySQL类型对应的DolphinDB类型
bit(1)-bit(8)CHAR
bit(9)-bit(16)SHORT
bit(17)-bit(32)INT
bit(33)-bit(64)LONG
tinyintCHAR
tinyint unsignedSHORT
smallintSHORT
smallint unsignedINT
mediumintINT
mediumint unsignedINT
intINT
int unsignedLONG
bigintLONG
bigint unsigned(不支持) LONG
  • DolphinDB中数值类型均为有符号类型。为了防止溢出,所有无符号类型会被转化为高一阶的有符号类型。例如,无符号CHAR转化为有符号SHORT,无符号SHORT转化为有符号INT,等等。64位无符号类型不予支持。
  • DolphinDB不支持 unsigned long long 类型。若MySQL中的类型为 bigint unsigned, 可在load或者loadEx的schema参数中设置为DOUBLE或者FLOAT。
  • DolphinDB中各类整形的最小值为NULL值,如 CHAR 的-128,SHORT的-32,768,INT的-2,147,483,648以及LONG的-9,223,372,036,854,775,808。

小数类型

MySQL类型对应的DolphinDB类型
doubleDOUBLE
floatFLOAT
newdecimal/decimal(1-9 length)DECIMAL32
newdecimal/decimal(10-18 length)DECIMAL64
newdecimal/decimal(19-38 length)DECIMAL128
newdecimal/decimal(lenght < 1 || length > 38)抛出异常

注:

  • IEEE754浮点数类型皆为有符号数。
  • 浮点类型 float 和 double 可转化为 DolphinDB 中的数值相关类型(BOOL, CHAR, SHORT, INT, LONG, FLOAT, DOUBLE)。
  • newdecimal/decimal 类型目前仅可转化为 DOUBLE。

时间类型

MySQL类型对应的DolphinDB类型
dateDATE
timeTIME
datetimeDATETIME
timestampTIMESTAMP
yearINT

以上类型皆可转化为DolphinDB中的时间相关类型(DATE, MONTH, TIME, MINUTE, SECOND, DATETIME, TIMESTAMP, NANOTIME, NANOTIMESTAMP)。

字符串类型

MySQL类型对应的DolphinDB类型
char (len <= 10)SYMBOL
varchar (len <= 10)SYMBOL
char (len > 10)STRING
varchar (len > 10)STRING
other string typesSTRING

长度不超过10的char和varchar将被转化为SYMBOL类型,其余转化为STRING类型。

string类型可以转化为转化为DolphinDB中的字符串相关类型(STRING, SYMBOL)。

枚举类型

MySQL类型对应的DolphinDB类型
enumSYMBOL

enum类型可以转化为DolphinDB中的字符串相关类型(STRING, SYMBOL),默认转化为SYMBOL类型。

导入数据性能

硬件环境

  • CPU: i7-7700 3.60GHZ
  • 硬盘: SSD,读速为每秒460~500MB。

数据集导入性能

美国股票市场从1990年至2016年的每日数据,共50,591,907行,22列,6.5GB。 导入耗时160.5秒。