Synchronize data through external table

Doris can create external tables. Once created, you can query the data of the external table directly with the SELECT statement or import the data of the external table with the INSERT INTO SELECT method.

Doris external tables currently support the following data sources.

  • MySQL
  • Oracle
  • PostgreSQL
  • SQLServer
  • Hive
  • Iceberg
  • ElasticSearch

This document describes how to create external tables accessible through the ODBC protocol and how to import data from these external tables.

Create external table

For a detailed introduction to creating ODBC external tables, please refer to the CREATE ODBC TABLE syntax help manual.

Here is just an example of how to use it.

  1. Create an ODBC Resource

    The purpose of ODBC Resource is to manage the connection information of external tables uniformly.

    1. CREATE EXTERNAL RESOURCE `oracle_test_odbc`
    2. PROPERTIES (
    3.     "type" = "odbc_catalog",
    4.     "host" = "192.168.0.1",
    5.     "port" = "8086",
    6.     "user" = "oracle",
    7.     "password" = "oracle",
    8.     "database" = "oracle",
    9.     "odbc_type" = "oracle",
    10.     "driver" = "Oracle"
    11. );

Here we have created a Resource named oracle_test_odbc, whose type is odbc_catalog, indicating that this is a Resource used to store ODBC information. odbc_type is oracle, indicating that this OBDC Resource is used to connect to the Oracle database. For other types of resources, see the resource management documentation for details.

  1. Create an external table
  1. CREATE EXTERNAL TABLE `ext_oracle_demo` (
  2.   `k1` decimal(9, 3) NOT NULL COMMENT "",
  3.   `k2` char(10) NOT NULL COMMENT "",
  4.   `k3` datetime NOT NULL COMMENT "",
  5.   `k5` varchar(20) NOT NULL COMMENT "",
  6.   `k6` double NOT NULL COMMENT ""
  7. ) ENGINE=ODBC
  8. COMMENT "ODBC"
  9. PROPERTIES (
  10.     "odbc_catalog_resource" = "oracle_test_odbc",
  11.     "database" = "oracle",
  12.     "table" = "baseall"
  13. );

Here we create an ext_oracle_demo external table and reference the oracle_test_odbc Resource created earlier

Import Data

  1. Create the Doris table

    Here we create a Doris table with the same column information as the external table ext_oracle_demo created in the previous step:

    1. CREATE TABLE `doris_oralce_tbl` (
    2.   `k1` decimal(9, 3) NOT NULL COMMENT "",
    3.   `k2` char(10) NOT NULL COMMENT "",
    4.   `k3` datetime NOT NULL COMMENT "",
    5.   `k5` varchar(20) NOT NULL COMMENT "",
    6.   `k6` double NOT NULL COMMENT ""
    7. )
    8. COMMENT "Doris Table"
    9. DISTRIBUTED BY HASH(k1) BUCKETS 2
    10. PROPERTIES (
    11.     "replication_num" = "1"
    12. );

    For detailed instructions on creating Doris tables, see CREATE-TABLE syntax help.

  2. Import data (from ext_oracle_demo table to doris_oralce_tbl table)

    1. INSERT INTO doris_oralce_tbl SELECT k1,k2,k3 FROM ext_oracle_demo limit 200

    The INSERT command is a synchronous command, and a successful return indicates that the import was successful.

Precautions

  • It must be ensured that the external data source and the Doris cluster can communicate with each other, including the network between the BE node and the external data source.
  • ODBC external tables essentially access the data source through a single ODBC client, so it is not suitable to import a large amount of data at one time. It is recommended to import multiple times in batches.