Tencent Cloud TCHouse-D
- Product Introduction
- Purchase Guide
- Getting Started
- Operation Guide
- Monitoring and Alarm Configuration
- Account Privilege Management
- Data Management
- Query Management
- Modify Configurations
- Development Guide
- Design of Data Table
- Importing Data
- Import Methods
- Exporting Data
- Basic Feature
- Query Optimization
- API Documentation
- Making API Requests
- Cluster Operation APIs
- Database and Table APIs
- Cluster Information Viewing APIs
- Hot-Cold Data Layering APIs
- Database and Operation Audit APIs
- User and Permission APIs
- Resource Group Management APIs
- Practical Tutorial
- Performance Testing
- TPC-H Performance Testing
- SSB Performance Testing
DocumentationTencent Cloud TCHouse-DDevelopment GuideImporting DataSynchronizing Data Through External Tables
Synchronizing Data Through External Tables
Last updated: 2024-06-27 11:04:12
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.
Was this page helpful?
You can also Contact Sales or Submit a Ticket for help.
Yes
No