ODBC

ODBC 为异构数据库访问提供统一接口，允许应用程序以 SQL 为数据存取标准。通过 DolphinDB 的 ODBC 插件，可以连接其它数据库，将数据导入到 DolphinDB 的内存表或分布式表中；或者将 DolphinDB 内存表导出到其它数据库。ODBC 插件基于 nanodbc 开发。

前置条件

ODBC 插件可以成功连接以下安装于 CentOS 7 的数据库：MySQL, PostgreSQL, SQLServer, Clickhouse, SQLite, Oracle。连接时需要指定数据库名称，如："MySQL"。

使用该插件前，请根据操作系统和数据库安装相应的 ODBC 驱动。

Ubuntu

  • 安装 unixODBC 库

    apt-get install unixodbc unixodbc-dev

  • 安装 SQL Server ODBC 驱动

    apt-get install tdsodbc

  • 安装 PostgreSQL ODBC 驱动

    apt-get install odbc-postgresql

  • 安装 MySQL ODBC 驱动

    apt-get install libmyodbc

  • 安装 SQLite ODBC 驱动

    apt-get install libsqliteodbc

CentOS

# 安装 unixODBC 库
yum install unixODBC  unixODBC-devel

# 安装 MySQL ODBC 驱动
yum install mysql-connector-odbc

Windows

从官网下载并安装 MySQL 或其他数据库的 ODBC 驱动程序。示例如下：

  • MySQL：https://dev.mysql.com/downloads/connector/odbc/
  • MS SQL Server：https://www.microsoft.com/en-us/download/details.aspx?id=53339
  • PostgreSQL：https://www.postgresql.org/ftp/odbc/versions/msi/
  • 配置 ODBC 数据源。例如，在 Windows 操作系统中配置 MySQL 的 ODBC 数据源的操作指导，可以参考： MySQL manual.

Docker 容器环境 （Alpine Linux）

运行以下命令安装 unixODBC 库：

# 安装 unixODBC 库
apk add unixodbc
apk add unixodbc-dev

安装插件

版本要求

  • DolphinDB Server: 2.00.10及更高版本

安装步骤

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

    login("admin", "123456")
listRemotePlugins()

  2. 使用 installPlugin 命令完成插件安装。

    installPlugin("odbc")

  3. 使用 loadPlugin 命令加载插件。

    loadPlugin("odbc")

注意：若在 Alpine Linux 环境中使用插件，加载时可能会出现无法找到依赖库的报错，需要在 DolphinDB 的 server 目录下添加软链接：ln -s /usr/lib/libodbc.so.2 libodbc.so.1

接口说明

connect

语法

odbc::connect(connStr, [dataBaseType])

详情

创建与数据库服务器的连接，返回数据库连接句柄，该句柄将在以后用于访问数据库服务器。

参数

connStr ODBC 连接字符串。有关连接字符串格式的更多信息，请参阅 连接字符串参考。ODBC DSN 必须由系统管理员创建。 有关 DSN 连接字符串的更多信息，请参阅 DSN连接字符串。我们还可以创建到数据库的 DSN-Less 连接。 无需依赖存储在文件或系统注册表中的信息，而是在连接字符串中指定驱动程序名称和所有特定于驱动程序的信息。例如: SQL server 的 DSN-less 连接字符串 和MySQL 的 DSN-less 连接字符串。

dataBaseType 数据库类型。如 "MySQL", "SQLServer", "PostgreSQL", "ClickHouse", "SQLite", "Oracle" 不区分大小写。建议连接时指定该参数，否则写入数据时可能出现报错。

注意：

  • 驱动程序名称可能会有所不同，具体取决于安装的 ODBC 版本。
  • 若数据库连接的端口指定错误，则会出现 server crash。
  • 必须通过 DSN 方式连接 Oracle 数据源，否则连接时用户名和密码可能校验失败；若修改 /etc/odbc.ini 中 DSN 配置的 database 和 password，则需要在 Oracle 命令行中 commit 后才能通过新配置进行连接（也可通过 isql 命令行工具验证配置是否生效）。
  • 通过 freeTDS 访问数据库时，必须保证 freetds.conf 中的 DSN 配置信息正确，否则可能出现 freeTDS crash 的情况。

例子

conn1 = odbc::connect("Dsn=mysqlOdbcDsn")  //mysqlOdbcDsn is the name of data source name
conn2 = odbc::connect("Driver={MySQL ODBC 8.0 UNICODE Driver};Server=127.0.0.1;Database=ecimp_ver3;User=newuser;Password=dolphindb123;Option=3;") 
conn3 = odbc::connect("Driver=SQL Server;Server=localhost;Database=zyb_test;User =sa;Password=DolphinDB123;")

close

语法

odbc::close(conn)

详情

关闭一个 ODBC 连接。

参数

conn 由 odbc::connect 创建的连接句柄。

例子

conn1 = odbc::connect("Dsn=mysqlOdbcDsn") 
odbc::close(conn1)

query

语法

odbc::query(connHandle|connStr, querySql, [t], [batchSize], [transform])

详情

通过 connHandle 或 connStr 查询数据库并返回 DolphinDB 表。

参数

connHandle| connStr 连接句柄或连接字符串。

querySql 表示查询的 SQL 语句。

t 表对象。若指定，查询结果将保存到该表中。请注意，t 的各字段类型必须与 ODBC 返回的结果兼容（见“类型支持”一节），否则将引发异常。

batchSize 从 ODBC 查询到的数据行数到达 batchSize 后，会将当前已经读到的数据追加到表 t 中。默认值为 262,144。

transform 一元函数，且入参必须是一个表。如果指定了 transform 参数，需要先创建分区表，再加载数据。程序会对数据文件中的数据应用 transform 参数指定的函数后，将得到的结果保存到数据库中。

例子

t=odbc::query(conn1,"SELECT max(time),min(time) FROM ecimp_ver3.tbl_monitor;")

execute

语法

odbc::execute(connHandle or connStr, SQLstatements)

详情

执行 SQL 语句。

参数

connHandle| connStr 连接句柄或连接字符串。

SQLstatements SQL 语句。

例子

odbc::execute(conn1,"delete from ecimp_ver3.tbl_monitor where `timestamp` BETWEEN '2013-03-26 00:00:01' AND '2013-03-26 23:59:59'")

append

语法

odbc::append(connHandle, tableData, tablename, [createTableIfNotExist], [insertIgnore])

详情

将 DolphinDB 表追加到连接的数据库。

参数

connHandle 连接句柄。

tableData DolphinDB 表。

tablename 连接的数据库中表的名称。

createTableIfNotExist 布尔值，表示是否创建一个新表，默认值是 true。

insertIgnore 布尔值，表示在插入时是否忽略重复数据，默认值是 false。

例子

t=table(1..10 as id,take(now(),10) as time,rand(1..100,10) as value)
odbc::append(conn1, t,"ddbtale", true)
odbc::query(conn1,"SELECT * FROM ecimp_ver3.ddbtale")

类型支持

查询类型

type in ODBCType in DolphinDB
SQL_BITBOOL
SQL_TINYINT / SQL_SMALLINTSHORT
SQL_INTEGERINT
SQL_BIGINTLONG
SQL_REALFLOAT
SQL_FLOAT/SQL_DOUBLE/SQL_DECIMAL/SQL_NUMERICDOUBLE
SQL_DATE/SQL_TYPE_DATEDATE
SQL_TIME/SQL_TYPE_TIMESECOND
SQL_TIMESTAMP/SQL_TYPE_TIMESTAMPNANOTIMESTAMP
SQL_CHAR(len == 1)CHAR
其它类型STRING

转换类型

DolphinDBPostgreSQLClickHouseOracleSQL ServerSQLiteMySQL
BOOLbooleanBoolchar(1)bitbitbit
CHARchar(1)char(1)char(1)char(1)char(1)char(1)
SHORTsmallintsmallintsmallintsmallintsmallintsmallint
INTintintintintintint
LONGbigintbigintnumberbigintbigintbigint
DATEdatedatedatedatedatedate
MONTHdatedatedatedatedatedate
TIMEtimetimetimetimetimetime
MINUTEtimetimetimetimetimetime
SECONDtimetimetimetimetimetime
DATETIMEtimestampdatetime64datedatetimedatetimedatetime
TIMESTAMPtimestampdatetime64timestampdatetimedatetimedatetime
NANOTIMEtimetimetimetimetimetime
NANOTIMESTAMPtimestampdatetime64timestampdatetimedatetimedatetime
FLOATfloatfloatfloatfloat(24)floatfloat
DOUBLEdouble precisiondoublebinary_doublefloat(53)doubledouble
SYMBOLvarchar(255)varchar(255)varchar(255)varchar(255)varchar(255)varchar(255)
STRINGvarchar(255)varchar(255)varchar(255)varchar(255)varchar(255)varchar(255)

常见问题

  1. 连接 Windows 系统的 ClickHouse，查询得到的结果显示中文乱码。

    解决方法： 请选择 ANSI 的 ClickHouse ODBC 驱动。

  2. 连接 ClickHouse 并读取数据时，datetime 类型数据返回空值或错误值。

    原因：低于 1.1.10 版本的 ClickHouse 的 ODBC 驱动将 datetime 返回为字符串类型，且返回的数据长度错误（长度过短），导致 ODBC 插件无法读取正确的字符串。

    解决方法： 更新驱动到不小于1.1.10的版本。

  3. 使用 yum install mysql-connector-odbc 命令下载并安装 MySQL ODBC 驱动后，因驱动与 MySQL 数据源版本不一致而导致连接 MySQL 数据源时发生错误。

    原因：Yum 仓库未及时更新，通过 yum install mysql-connector-odbc 下载及安装的 MySQL ODBC 驱动与 MySQL 数据源的版本不一致。使用 yum install mysql-connector-odbc 会根据 Yum 仓库 （Yum Repository）的配置情况下载对应版本的 MySQL ODBC 驱动。当 MySQL 数据源的版本较新，例如 8.0 版本时，请确保您本地的 Yum 仓库配置亦为最新。因此，为避免连接 MySQL 数据源时出现连接超时或无法找到 libodbc.so.1 文件等错误，可以通过以下方法获取最新版本的 MySQL ODBC 驱动。

    解决方法：

    方法1：在运行 yum install mysql-connector-odbc 命令前，运行以下命令以确保 MySQL Yum 仓库为最新：

    $> su root
$> yum update mysql-community-release

    有关更多 MySQL Yum 仓库的使用教程， 参考：Installing Additional MySQL Products and Components with Yum。

    方法2：下载指定版本的 MySQL ODBC 驱动，修改 /etc/odbc.ini 文件后，修改 conn 对应语句。例如，当 MySQL 数据源版本为 8.0 时：

    1. 运行以下命令下载对应 MySQL 8.0 版本的 ODBC 驱动：

      wget https://dev.mysql.com/get/Downloads/Connector-ODBC/8.0/mysql-connector-odbc-8.0.32-1.el7.x86_64.rpm
rpm -ivh mysql-connector-odbc-8.0.32-1.el7.x86_64.rpm
rpm -ql mysql-connector-odbc-8.0.32-1.el7.x86_64

    2. 加载插件时，如遇到 libodbc.so.1: cannot open shared object file: No such file or directory 错误，说明依赖库无法找到，则在 libodbc.so.2 与 libodbc.so.1 之间建立软连接，然后重新加载插件。

      cd /usr/lib64
ln -s libodbc.so.2 libodbc.so.1

    3. 复制 /etc/odbcinst.ini 中 [MySQL ODBC 8.0 Unicode Driver] 下 Driver 的指定路径，例如：

      [MySQL ODBC 8.0 Unicode Driver]
Driver=/usr/lib64/libmyodbc8w.so
UsageCount=1

    4. 使用上一步复制的信息以及连接 MySQL 数据源所需的登录信息修改 /etc/odbc.ini：

    解决方法： 请选择 ANSI 的 ClickHouse ODBC 驱动。

  4. 连接 ClickHouse 并读取数据时，datetime 类型数据返回空值或错误值。

    原因： 低于 1.1.10 版本的 ClickHouse 的 ODBC 驱动将 datetime 返回为字符串类型，且返回的数据长度错误（长度过短），导致 ODBC 插件无法读取正确的字符串。

    解决方法： 更新驱动到不小于1.1.10的版本。

  5. 使用 yum install mysql-connector-odbc 命令下载并安装 MySQL ODBC 驱动后，因驱动与 MySQL 数据源版本不一致而导致连接 MySQL 数据源时发生错误。

    原因：Yum 仓库未及时更新，通过 yum install mysql-connector-odbc 下载及安装的 MySQL ODBC 驱动与 MySQL 数据源的版本不一致。使用 yum install mysql-connector-odbc 会根据 Yum 仓库 （Yum Repository）的配置情况下载对应版本的 MySQL ODBC 驱动。当 MySQL 数据源的版本较新，例如 8.0 版本时，请确保您本地的 Yum 仓库配置亦为最新。因此，为避免连接 MySQL 数据源时出现连接超时或无法找到 libodbc.so.1 文件等错误，可以通过以下方法获取最新版本的 MySQL ODBC 驱动。

    解决方法：

    方法1：在运行 yum install mysql-connector-odbc 命令前，运行以下命令以确保 MySQL Yum 仓库为最新：

    $> su root
$> yum update mysql-community-release

    有关更多 MySQL Yum 仓库的使用教程， 参考：Installing Additional MySQL Products and Components with Yum。

    方法2：下载指定版本的 MySQL ODBC 驱动，修改 /etc/odbc.ini 文件后，修改 conn 对应语句。例如，当 MySQL 数据源版本为 8.0 时：

    1. 运行以下命令下载对应 MySQL 8.0 版本的 ODBC 驱动：

      wget https://dev.mysql.com/get/Downloads/Connector-ODBC/8.0/mysql-connector-odbc-8.0.32-1.el7.x86_64.rpm
rpm -ivh mysql-connector-odbc-8.0.32-1.el7.x86_64.rpm
rpm -ql mysql-connector-odbc-8.0.32-1.el7.x86_64

    2. 加载插件时，如遇到 libodbc.so.1: cannot open shared object file: No such file or directory 错误，说明依赖库无法找到，则在 libodbc.so.2 与 libodbc.so.1 之间建立软连接，然后重新加载插件。

      cd /usr/lib64
ln -s libodbc.so.2 libodbc.so.1

    3. 复制 /etc/odbcinst.ini 中 [MySQL ODBC 8.0 Unicode Driver] 下 Driver 的指定路径，例如：

      [MySQL ODBC 8.0 Unicode Driver]
Driver=/usr/lib64/libmyodbc8w.so
UsageCount=1

    4. 使用上一步复制的信息以及连接 MySQL 数据源所需的登录信息修改 /etc/odbc.ini：

      [root@username/]# cat /etc/odbc.ini
[mysql8]
Description=ODBC for MySQL
Driver=/usr/lib64/libmyodbc8w.so
Server=172.17.0.10
Port=3306
Database=test1db
User=root
Password=123456

    5. 修改 conn 连接语句。

      conn = odbc::connect("Driver=MySQL ODBC 8.0 Unicode Driver;Server=172.17.0.10;Port=3306;Database=testdb;User=root;Password=123456;", "MySQL");