- Product Introduction
- Purchase Guide
- Getting Started
- Operation Guide
- Development Guide
- Design of Data Table
- Importing Data
- Import Overview
- Import Methods
- Synchronizing Data with JDBC
- Synchronizing Data Through External Tables
- Real-time or Batch Writing of MySQL Data
- Importing Data from Kafka
- Importing with DataX
- Importing Doris from Logstash
- Data Transformation, Column Mapping and Filtering
- Importing Strict Mode
- Importing Data in JSON Format
- Exporting Data
- Basic Feature
- Query Optimization
- Ecological Expansion Feature
- Product Policy
- Product Introduction
- Purchase Guide
- Getting Started
- Operation Guide
- Development Guide
- Design of Data Table
- Importing Data
- Import Overview
- Import Methods
- Synchronizing Data with JDBC
- Synchronizing Data Through External Tables
- Real-time or Batch Writing of MySQL Data
- Importing Data from Kafka
- Importing with DataX
- Importing Doris from Logstash
- Data Transformation, Column Mapping and Filtering
- Importing Strict Mode
- Importing Data in JSON Format
- Exporting Data
- Basic Feature
- Query Optimization
- Ecological Expansion Feature
- Product Policy
Note:
The content in this document is only applicable to version 1.1 and earlier; it is not recommended to use external tables for data synchronization after version 1.2.
Doris can create an external table accessed via the ODBC protocol. Once created, you can directly query the data of the external table with the SELECT statement, or import the data of the external table by means of
INSERT INTO SELECT.This document mainly introduces how to create an external table accessed via the ODBC protocol and how to import the data of these external tables. Current supported data sources includes:
MySQL
Oracle
PostgreSQL
SQLServer
Hive (Supported in 1.0 version)
Create External Table
1. Create an ODBC Resource
The purpose of the ODBC Resource is for consolidated management of the external table connection information.
CREATE EXTERNAL RESOURCE `oracle_test_odbc`PROPERTIES ("type" = "odbc_catalog","host" = "192.168.0.10","port" = "8086","user" = "oracle","password" = "oracle","database" = "oracle","odbc_type" = "oracle","driver" = "Oracle");
Here we have created a Resource named
oracle_test_odbc. Its type is odbc_catalog, indicating that it is a Resource for storing ODBC information. odbc_type is oracle, indicating that this ODBC Resource is used to connect to the Oracle Database.2. Create External Table
CREATE EXTERNAL TABLE `ext_oracle_demo` (`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=ODBCCOMMENT "ODBC"PROPERTIES ("odbc_catalog_resource" = "oracle_test_odbc","database" = "oracle","table" = "baseall");
Here, we create a
ext_oracle_demo external table, referencing the previously created oracle_test_odbc Resource.Importing Data
1. Create the Doris Table
Here, we create a Doris table, its column information is the same as the external table
ext_oracle_demo created back then.CREATE TABLE `doris_oralce_tbl` (`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 "")COMMENT "Doris Table"DISTRIBUTED BY HASH(k1) BUCKETS 2;PROPERTIES ("replication_num" = "1");
2. Importing Data (From
ext_oracle_demo table to doris_oracle_tbl table)INSERT INTO doris_oracle_tbl SELECT k1,k2,k3 FROM ext_oracle_demo limit 100;
The INSERT command is a synchronization command; a successful return means successful importing.
Notes
It is necessary to ensure that the external data source and Doris cluster can communicate with each other, including the network between BE node and the external data source.
The ODBC external table essentially accesses a single ODBC client, which is not suitable for importing a large amount of data at one time. It's recommended to import multiple times in batches.
Yes
No
Was this page helpful?