CREATE ODBC TABLE

Description

Palo supports the reading and writing operations by creating external tables and accessing external data sources through ODBC protocol. Currently the ODBC data sources supported on the cloud include:

• MySQL
• Oracle
• PostgreSQL

When creating the ODBC external table, it is required to fill in the external data source connection information in the table building statement.

There are two ways to provide connection information.

One is to directly connect the information to describe the table building statement.

One is to manage the connection information in a unified way by creating resources, and cite the resources in the table building statement.

It is recommended to use the second method to manage the connection information. Creating resources can be found in Resource Management file.

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

• [db.]tbl_name

  Library name and table name. The names may not be consistent with external data sources, and can be customized. There will be a mapping relationship in properties.

• column_definition

  Column definition. The name of the column should be consistent with the name in the external data source. The number and order of columns may not be the same. Whether the column is allowed to be NULL should be consistent with that in the source database, otherwise problems may occur in query or write.

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

• tbl_comment

  Table comment

• properties

  External data source information.

  1. Define external data sources by referencing resources.

     PROPERTIES (
         "odbc_catalog_resource" = "resource_name",
         "database" = "oracle_db",
         "table" = "oracle_tbl"
     );

     • odbc_catalog_resource：specify the name of resource.
     • database：name of the database in the external data source.
     • table：name of table in the external data source.

  2. Define external data sources directly.

     PROPERTIES (
         "host" = "192.168.0.1",
         "port" = "8086",
         "user" = "test",
         "password" = "test",
         "database" = "test",
         "table" = "baseall",
         "driver" = "oracle",
         "odbc_type" = "oracle"
     );

     • host/port：ODBC protocol connection target of external data source.
     • user/passwd：User name and password for accessing external data source.
     • database/table：name of the corresponding database and table in the external data source.
     • driver：Name of ODBC Driver. Public cloud users may choose:PostgreSQL, MySQL,Oracle,SQLServer.
     • odbc_type: data source type, supporting:oracle, mysql, postgresql.

Example

1. Create a MySQL external table. Use the ODBC Resource created.

   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. Create an Oracle external table. Set the connection mode directly.

   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

Best Practices

1. Query ODBC external tables

   The query of ODBC external table is the same as the query of ordinary table, so the query can be done by using SQL statement.

   In essence, the query of external tables in Palo is to connect and query external data sources through ODBC client on a Compute Node node. Therefore, the external data source and the Compute Node network should be connected in both directions.

   At the same time, The query of external tables in Palo is not in distributed way but by connecting single Client. Therefore, its performance efficiency is far lower than that of querying the table stored in Palo. External tables are more applicable to the correlation query of some frequently updated dimension tables and fact tables stored in Palo, or synchronization of data from an external data source to Palo by INSERT INTO SELECT .

2. Write into external table

   Data can be directly written into ODBC external tables by INSERT command. Specific operations can be found in: Export Data to External Tables.

3. Corresponding relations between column types

   Different data sources have different column types. Here are the mapping relationships between the column types of three ODBC data sources and Palo column types.

   1. MySQL

      MySQL	Doris	Alternative Plan
      BOOLEAN	BOOLEAN
      CHAR	CHAR	Currently support UTF8 encoding only
      VARCHAR	VARCHAR	Currently support UTF8 encoding only
      DATE	DATE
      FLOAT	FLOAT
      TINYINT	TINYINT
      SMALLINT	SMALLINT
      INT	INT
      BIGINT	BIGINT
      DOUBLE	DOUBLE
      DATETIME	DATETIME
      DECIMAL	DECIMAL

   2. PostgreSQL

      PostgreSQL	Doris	Alternative Plan
      BOOLEAN	BOOLEAN
      CHAR	CHAR	Currently support UTF8 encoding only
      VARCHAR	VARCHAR	Currently support UTF8 encoding only
      DATE	DATE
      REAL	FLOAT
      SMALLINT	SMALLINT
      INT	INT
      BIGINT	BIGINT
      DOUBLE	DOUBLE
      TIMESTAMP	DATETIME
      DECIMAL	DECIMAL

   3. Oracle

      Oracle	Doris	Alternative Plan
      Not Support	BOOLEAN	Oracle can use number(1) to replace boolean
      CHAR	CHAR
      VARCHAR	VARCHAR
      DATE	DATE
      FLOAT	FLOAT
      None	TINYINT	Oracle can be replaced with NUMBER
      SMALLINT	SMALLINT
      INT	INT
      None	BIGINT	Oracle can be replaced with NUMBER
      None	DOUBLE	Oracle can be replaced with NUMBER
      DATETIME	DATETIME
      NUMBER	DECIMAL