tencent cloud

Tencent Cloud TCHouse-D

Product Introduction
Overview
Concepts
Cluster Architecture
Strengths
Scenarios
Purchase Guide
Billing Overview
Renewal Instructions
Overdue Policy
Refund Instructions
Configuration Adjustment Billing Instructions
Getting Started
Using Tencent Cloud TCHouse-D Through the Console
Using Tencent Cloud TCHouse-D Through a Client
Operation Guide
Cluster Operation
Monitoring and Alarm Configuration
Account Privilege Management
Data Management
Query Management
Modify Configurations
Node Management
Log Analysis
SQL Studio
Enabling Resource Isolation
Development Guide
Design of Data Table
Importing Data
Exporting Data
Basic Feature
Query Optimization
Ecological Expansion Feature
API Documentation
History
Introduction
API Category
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
Data Types
Error Codes
Cloud Ecosystem
Granting CAM Policies to Sub-accounts
Query Acceleration for Tencent Cloud DLC
Practical Tutorial
Basic Feature Usage
Advanced Features Usage
Resource Specification Selection and Optimization Suggestions
Naming Specifications and Limits to the Database and Data Table
Table Design and Data Import
Query Optimization
Suggested Usage to Avoid
Accessing TCHouse-D via JDBC over the Public Network
Performance Testing
TPC-H Performance Testing
SSB Performance Testing
TPC-DS Performance Testing
FAQs
Common Operational Issues
Common Errors
Contact Us
Glossary
Product Policy
Service Level Agreement
Privacy Policy
Data Processing And Security Agreement
DocumentaçãoTencent Cloud TCHouse-DDevelopment GuideImporting DataSynchronizing Data Through External Tables

Synchronizing Data Through External Tables

PDF
Modo Foco
Tamanho da Fonte
Última atualização: 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=ODBC
COMMENT "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"
);
For a detailed explanation on creating a Doris table, please see the CREATE-TABLE syntax help.
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.
Anterior: Synchronizing Data with JDBCPróximo: Real-time or Batch Writing of MySQL Data

Ajuda e Suporte

Esta página foi útil?

comentários