[Proposal] Support ExternalCatalogResource to simplify external table operation

[HappenLee]

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.