HappenLee opened a new issue #4556:
URL: https://github.com/apache/incubator-doris/issues/4556
## 1.Motivation
Currently, Doris supports accessing third-party databases through the
facade of ODBC and mysql. This is the current way to create an external table
```
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 19 ODBC driver",
"type" = "oracle"
);
```
The current account number, password and other information are created in
the form of `properties`. This part needs to input more table building
information. It is not flexible enough. If ten appearances are created, once
you need to change the password and other operations, you need to modify all
tables uniformly.
**This part brings extra burden to the operation and maintenance work, so we
need to carry out unified management of external table attributes.**
## 2.How To Use
Currently, Doris supports saving spark's imported properties through
`Resource`. Doris can try to reuse this logic.
```
create external resource "odbc"
properties
(
"type" = "external_catalog", (required)
"user" = "test",(required）
"password" = "", (required）
"host" = "192.168.0.1", (required）
"port" = "8086", (required）
"type" = "oracle" , (optinal，only odbc exteranl table use ）
"driver" = "Oracle 19 ODBC driver" (optional，only odbc exteranl table use)
)
```
Once created, the resource can be used to build the table.
```
CREATE TABLE `test_mysql` (
`k1` tinyint(4) NOT NULL,
`k2` smallint(6) NOT NULL,
`k3` int(11) NOT NULL,
`k4` bigint(20) NOT NULL,
`k5` decimal(9,3) NOT NULL,
`k6` char(5) NOT NULL,
`k10` date DEFAULT NULL,
`k11` datetime DEFAULT NULL,
`k7` varchar(20) NOT NULL,
`k8` double NOT NULL,
`k9` float NOT NULL
) ENGINE=MYSQL
PROPERTIES (
"external_catalog_resource" = "odbc",
"database" = "test",
"table" = "test"
);
```
When user need to modify the corresponding properties. The user only need to
modify the information of the corresponding `resource` which can take effect on
multiple tables.
* Notes
1. Because the table information depends on the resource information, the
resource information may be deleted or modified.
2. Metadata needs to be compatible with the original way of creating tables
and also supports the establishment of tables that depend on Resource and the
original way.
## 3 Summary
**After unifying the above logic, Doris can regard external table as
external resource.** The management of external table information is unified
as the management of resource, which simplifies the management logic and
modification cost.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
