CREATE-ODBC-TABLE

更新时间：2021-12-30

CREATE ODBC TABLE

Description

Doris 支持以创建外部表的方式，通过 ODBC 协议访问外部数据源，进行读写操作。目前云上支持的 ODBC 数据源包括：

MySQL
Oracle
PostgreSQL

创建 ODBC 外部表需要在建表语句中填写外部数据源连接信息。

有两种方式提供连接信息。

一种是直接想连接信息描述在建表语句中。

一种是通过创建资源的形式统一管理连接信息，并在建表语句中引用该资源。

建议使用第二种方式，便于连接信息的管理。创建资源可参阅资源管理文档。

CREATA EXTERNAL TABLE [db.]tbl_name
(column_definition[, column_definition, ...])
ENGINE=ODBC
[tbl_comment]
[properties]

[db.]tbl_name

库名和表名。无需和外部数据源一致，可以自定义。在 properties 中会有映射关系。
column_definition

列定义。列的名称需和外部数据源中的名称一致。列数量和顺序无需一致。列是否允许为 NULL，需和源数据库保持一致，否则可能出现查询或写入问题。

col_name DOUBLE NULL COMMENT "abc" col_name VARCHAR(1) NOT NULL COMMENT "def"
tbl_comment

表注释
properties

外部数据源信息。
1. 通过引用资源定义外部数据源。
```
PROPERTIES (
    "odbc_catalog_resource" = "resource_name",
    "database" = "oracle_db",
    "table" = "oracle_tbl"
);
```
  - odbc_catalog_resource：指定资源的名称。
  - database：外部数据源中数据库的名称。
  - table：外部数据源中表的名称。
2. 直接定义外部数据源。
```
PROPERTIES (
    "host" = "192.168.0.1",
    "port" = "8086",
    "user" = "test",
    "password" = "test",
    "database" = "test",
    "table" = "baseall",
    "driver" = "oracle",
    "odbc_type" = "oracle"
);
```
  - host/port：外部数据源的 ODBC 协议连接目标。
  - user/passwd：访问外部数据源的用户名密码。
  - database/table：外部数据源中对应的数据库和表名称。
  - driver：ODBC Driver 的名称。公有云用户可以选择：PostgreSQL，MySQL，Oracle，SQLServer。
  - odbc_type：数据源类型。支持：oracle, mysql，postgresql。

Example

创建一个 MySQL 外部表。使用已创建好的 ODBC Resource。

CREATE EXTERNAL TABLE `baseall_oracle` (
  `k1` decimal(9, 3) NOT NULL COMMENT "",
  `k2` char(10) NOT NULL COMMENT "",
  `k3` datetime NOT NULL COMMENT "",
  `k5` varchar(20) NOT NULL COMMENT "",
  `k6` double NOT NULL COMMENT ""
) ENGINE=ODBC
COMMENT "ODBC"
PROPERTIES (
"odbc_catalog_resource" = "mysql_odbc",
"database" = "test",
"table" = "baseall"
);

创建一个 Oracle 外部表。直接设置连接方式。

CREATE EXTERNAL TABLE `baseall_oracle` (
  `k1` decimal(9, 3) NOT NULL COMMENT "",
  `k2` char(10) NOT NULL COMMENT "",
  `k3` datetime NOT NULL COMMENT "",
  `k5` varchar(20) NOT NULL COMMENT "",
  `k6` double NOT NULL COMMENT ""
) ENGINE=ODBC
COMMENT "ODBC"
PROPERTIES (
    "host" = "192.168.0.1",
    "port" = "8086",
    "user" = "test",
    "password" = "test",
    "database" = "test",
    "table" = "baseall",
    "driver" = "Oracle",
    "odbc_type" = "oracle"
);

Keywords

CREATE, MYSQL, ORACLE, ODBC, EXTERNAL, TABLE

最佳实践

查询 ODBC 外部表

对于 ODBC 的外表的查询和普通表的查询无异，直接使用 SQL 语句进行查询即可。

Doris 对于外部表的查询，本质上是通过某一个 Compute Node 节点上的 ODBC Client 对外部数据源进行连接和查询。所以要求外部数据源和 Compute Node 节点网络是双向连通的。

同时，Doris 查询外部表并不是分布式查询，而是单 Client 方式连接查询。所以性能效率上要远低于查询 Doris 自身存储的表。外部表比较适用的场景是一些频繁更新的维度表和 Doris 中存储的事实表进行关联查询。或者通过 INSERT INTO SELECT 的方式从外部数据源同步数据到 Doris 中。
写入外部表

可以通过 INSERT 命令直接将数据写入 ODBC 外部表。具体操作可以参阅：导出数据到外部表。

列类型对应关系。

不同的数据源的列类型不尽相同，这里罗列三种 ODBC 数据源的列类型和 Doris 列类型的映射关系。

MySQL

MySQL	Doris	替换方案
BOOLEAN	BOOLEAN
CHAR	CHAR	当前仅支持UTF8编码
VARCHAR	VARCHAR	当前仅支持UTF8编码
DATE	DATE
FLOAT	FLOAT
TINYINT	TINYINT
SMALLINT	SMALLINT
INT	INT
BIGINT	BIGINT
DOUBLE	DOUBLE
DATETIME	DATETIME
DECIMAL	DECIMAL

PostgreSQL

PostgreSQL	Doris	替换方案
BOOLEAN	BOOLEAN
CHAR	CHAR	当前仅支持UTF8编码
VARCHAR	VARCHAR	当前仅支持UTF8编码
DATE	DATE
REAL	FLOAT
SMALLINT	SMALLINT
INT	INT
BIGINT	BIGINT
DOUBLE	DOUBLE
TIMESTAMP	DATETIME
DECIMAL	DECIMAL

Oracle

Oracle	Doris	替换方案
不支持	BOOLEAN	Oracle可用number(1) 替换boolean
CHAR	CHAR
VARCHAR	VARCHAR
DATE	DATE
FLOAT	FLOAT
无	TINYINT	Oracle可由NUMBER替换
SMALLINT	SMALLINT
INT	INT
无	BIGINT	Oracle可由NUMBER替换
无	DOUBLE	Oracle可由NUMBER替换
DATETIME	DATETIME
NUMBER	DECIMAL

CREATE-REPOSITORY 信息查看语句

Palo文档 PALO

PALO