CREATE ODBC TABLE

Description

PALO 支持以创建外部表的方式，通过 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

1. 创建一个 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"
   );

2. 创建一个 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

典型实践

1. 查询 ODBC 外部表

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

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

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

2. 写入外部表

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

3. 列类型对应关系。

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

   1. MySQL

      MySQL	PALO	替换方案
      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

   2. PostgreSQL

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

   3. Oracle

      Oracle	PALO	替换方案
      不支持	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