This is an automated email from the ASF dual-hosted git repository.
morningman pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 1140092211 [docs](jdbc catalog) add docs for jdbc catalog (#14924)
1140092211 is described below
commit 114009221106f8b32e7f12fdf7e5bcc271236184
Author: Tiewei Fang <[email protected]>
AuthorDate: Fri Dec 9 08:57:39 2022 +0800
[docs](jdbc catalog) add docs for jdbc catalog (#14924)
---
.../docs/ecosystem/external-table/multi-catalog.md | 143 +++++++++++++++++++++
.../Create/CREATE-CATALOG.md | 14 ++
.../docs/ecosystem/external-table/multi-catalog.md | 138 ++++++++++++++++++++
.../Create/CREATE-CATALOG.md | 14 ++
4 files changed, 309 insertions(+)
diff --git a/docs/en/docs/ecosystem/external-table/multi-catalog.md
b/docs/en/docs/ecosystem/external-table/multi-catalog.md
index dabfcf4fb9..7f215f673d 100644
--- a/docs/en/docs/ecosystem/external-table/multi-catalog.md
+++ b/docs/en/docs/ecosystem/external-table/multi-catalog.md
@@ -36,6 +36,7 @@ The new Multi-Catalog function adds a new layer of Catalog to
the original metad
1. Hive MetaStore: Connect to a Hive MetaStore, so that you can directly
access Hive, Iceberg, Hudi and other data in it.
2. Elasticsearch: Connect to an ES cluster and directly access the tables and
shards in it.
+3. JDBC: Connect to the standard database access interface (JDBC) to access
data of various databases. (currently only support `jdbc:mysql`)
This function will be used as a supplement and enhancement to the previous
external table connection method (External Table) to help users perform fast
multi-catalog federated queries.
@@ -307,6 +308,121 @@ Parameter | Description
**elasticsearch.nodes_discovery** | Whether or not to enable ES node
discovery, the default is true. In network isolation, set this parameter to
false. Only the specified node is connected
**elasticsearch.ssl** | Whether ES cluster enables https access mode, the
current FE/BE implementation is to trust all
+### Connect JDBC
+
+
+The following example creates a Catalog connection named jdbc. This jdbc
Catalog will connect to the specified database according to the 'jdbc.jdbc_url'
parameter(`jdbc::mysql` in the example, so connect to the mysql database).
Currently, only the MYSQL database type is supported.
+
+```sql
+CREATE CATALOG jdbc PROPERTIES (
+ "type"="jdbc",
+ "jdbc.user"="root",
+ "jdbc.password"="123456",
+ "jdbc.jdbc_url" = "jdbc:mysql://127.0.0.1:13396/demo",
+ "jdbc.driver_url" = "file:/path/to/mysql-connector-java-5.1.47.jar",
+ "jdbc.driver_class" = "com.mysql.jdbc.Driver"
+);
+```
+
+Where `jdbc.driver_url` can be a remote jar package
+
+```sql
+CREATE CATALOG jdbc PROPERTIES (
+"type"="jdbc",
+"jdbc.user"="root",
+"jdbc.password"="123456",
+"jdbc.jdbc_url" = "jdbc:mysql://127.0.0.1:13396/demo",
+"jdbc.driver_url" = "https://path/jdbc_driver/mysql-connector-java-8.0.25.jar";,
+"jdbc.driver_class" = "com.mysql.cj.jdbc.Driver"
+);
+```
+
+If the `jdbc.driver_url` is a remote jar package in the form of http, the
Doris processing method is:
+1. Only query the meta-data, without inquiring the table data (such as the
operation `show catalogs/database/tables`): This URL will be used to load the
driver class by FE.
+2. When querying the tables in JDBC Catalog (like `select from`): BE will
download the jar package to the local directory `be/lib/udf/`, and the jar
package will be loaded directly from the local path When queried.
+
+Once created, you can view the catalog with the `SHOW CATALOGS` command:
+
+```sql
+MySQL [(none)]> show catalogs;
++-----------+-------------+----------+
+| CatalogId | CatalogName | Type |
++-----------+-------------+----------+
+| 0 | internal | internal |
+| 10480 | jdbc | jdbc |
++-----------+-------------+----------+
+2 rows in set (0.02 sec)
+```
+
+Switch to the jdbc catalog with the `SWITCH` command and view the databases in
it:
+
+```sql
+MySQL [(none)]> switch jdbc;
+Query OK, 0 rows affected (0.02 sec)
+
+MySQL [(none)]> show databases;
++--------------------+
+| Database |
++--------------------+
+| __db1 |
+| _db1 |
+| db1 |
+| demo |
+| information_schema |
+| mysql |
+| mysql_db_test |
+| performance_schema |
+| sys |
++--------------------+
+9 rows in set (0.67 sec)
+```
+
+Show the tables under the `db1` database and query one table:
+
+```sql
+MySQL [demo]> use db1;
+Reading table information for completion of table and column names
+You can turn off this feature to get a quicker startup with -A
+
+Database changed
+MySQL [db1]> show tables;
++---------------+
+| Tables_in_db1 |
++---------------+
+| tbl1 |
++---------------+
+1 row in set (0.00 sec)
+
+MySQL [db1]> desc tbl1;
++-------+------+------+------+---------+-------+
+| Field | Type | Null | Key | Default | Extra |
++-------+------+------+------+---------+-------+
+| k1 | INT | Yes | true | NULL | |
++-------+------+------+------+---------+-------+
+1 row in set (0.00 sec)
+
+MySQL [db1]> select * from tbl1;
++------+
+| k1 |
++------+
+| 1 |
+| 2 |
+| 3 |
+| 4 |
++------+
+4 rows in set (0.19 sec)
+```
+
+#### 参数说明：
+
+参数 | 说明
+---|---
+**jdbc.user** | The username used to connect to the database
+**jdbc.password** | The password used to connect to the database
+**jdbc.jdbc_url** | The identifier used to connect to the specified database
+**jdbc.driver_url** | The url of JDBC driver package
+**jdbc.driver_class** | The class of JDBC driver
+
### Connect Aliyun Data Lake Formation
> [What is Data Lake
> Formation](https://www.alibabacloud.com/product/datalake-formation)
@@ -418,6 +534,33 @@ For Hive/Iceberge/Hudi
| array | | Comming soon |
|other| string ||
+### JDBC
+
+#### MYSQL
+ MYSQL Type | Doris Type | Comment |
+|---|---|---|
+| BOOLEAN | BOOLEAN | |
+| TINYINT | TINYINT | |
+| SMALLINT | SMALLINT | |
+| MEDIUMINT | INT | |
+| INT | INT | |
+| BIGINT | BIGINT | |
+| UNSIGNED TINYINT | SMALLINT | DORIS does not have the UNSIGNED data type, so
expand the type|
+| UNSIGNED MEDIUMINT | INT | DORIS does not have the UNSIGNED data type, so
expand the type|
+| UNSIGNED INT | BIGINT | DORIS does not have the UNSIGNED data type, so
expand the type|
+| UNSIGNED BIGINT | STRING | |
+| FLOAT | FLOAT | |
+| DOUBLE | DOUBLE | |
+| DECIMAL | DECIMAL | |
+| DATE | DATE | |
+| TIMESTAMP | DATETIME | |
+| DATETIME | DATETIME | |
+| YEAR | SMALLINT | |
+| TIME | STRING | |
+| CHAR | CHAR | |
+| VARCHAR | STRING | |
+|
TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB、TINYSTRING、STRING、MEDIUMSTRING、LONGSTRING、BINARY、VARBINARY、JSON、SET、BIT
| STRING | |
+
## Privilege Management
Using Doris to access the databases and tables in the External Catalog is not
controlled by the permissions of the external data source itself, but relies on
Doris's own permission access management.
diff --git
a/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-CATALOG.md
b/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-CATALOG.md
index acf435eb06..88ff3107bb 100644
---
a/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-CATALOG.md
+++
b/docs/en/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-CATALOG.md
@@ -45,6 +45,7 @@ CREATE CATALOG [IF NOT EXISTS] catalog_name
* hms：Hive MetaStore
* es：Elasticsearch
+* jdbc: Database access standard interface (JDBC), currently only support
`jdbc:mysql`
### Example
@@ -71,6 +72,19 @@ CREATE CATALOG [IF NOT EXISTS] catalog_name
);
```
+3. Create catalog jdbc
+
+ ```sql
+ CREATE CATALOG jdbc PROPERTIES (
+ "type"="jdbc",
+ "jdbc.user"="root",
+ "jdbc.password"="123456",
+ "jdbc.jdbc_url" = "jdbc:mysql://127.0.0.1:13396/demo",
+ "jdbc.driver_url" =
"file:/mnt/disk2/ftw/tools/jar/mysql-connector-java-5.1.47/mysql-connector-java-5.1.47.jar",
+ "jdbc.driver_class" = "com.mysql.jdbc.Driver"
+ );
+ ```
+
### Keywords
CREATE, CATALOG
diff --git a/docs/zh-CN/docs/ecosystem/external-table/multi-catalog.md
b/docs/zh-CN/docs/ecosystem/external-table/multi-catalog.md
index 841c6b6766..68f28e9132 100644
--- a/docs/zh-CN/docs/ecosystem/external-table/multi-catalog.md
+++ b/docs/zh-CN/docs/ecosystem/external-table/multi-catalog.md
@@ -36,6 +36,7 @@ under the License.
1. Hive MetaStore：对接一个 Hive MetaStore，从而可以直接访问其中的 Hive、Iceberg、Hudi 等数据。
2. Elasticsearch：对接一个 ES 集群，并直接访问其中的表和分片。
+3. JDBC: 对接数据库访问的标准接口(JDBC)来访问各式数据库的数据。（目前只支持访问MYSQL）
该功能将作为之前外表连接方式（External Table）的补充和增强，帮助用户进行快速的多数据目录联邦查询。
@@ -371,6 +372,117 @@ mysql> select * from test;
之后，可以像正常的 Hive MetaStore 一样，访问 DLF 下的元数据。
+### 连接JDBC
+
+以下示例，用于创建一个名为 jdbc 的 Catalog, 通过jdbc 连接指定的Mysql。
+jdbc Catalog会根据`jdbc.jdbc_url` 来连接指定的数据库（示例中是`jdbc::mysql`,
所以连接MYSQL数据库），当前只支持MYSQL数据库类型。
+```sql
+CREATE CATALOG jdbc PROPERTIES (
+ "type"="jdbc",
+ "jdbc.user"="root",
+ "jdbc.password"="123456",
+ "jdbc.jdbc_url" = "jdbc:mysql://127.0.0.1:13396/demo",
+ "jdbc.driver_url" = "file:/path/to/mysql-connector-java-5.1.47.jar",
+ "jdbc.driver_class" = "com.mysql.jdbc.Driver"
+);
+```
+
+其中`jdbc.driver_url`可以是远程jar包：
+
+```sql
+CREATE CATALOG jdbc PROPERTIES (
+ "type"="jdbc",
+ "jdbc.user"="root",
+ "jdbc.password"="123456",
+ "jdbc.jdbc_url" = "jdbc:mysql://127.0.0.1:13396/demo",
+ "jdbc.driver_url" =
"https://path/jdbc_driver/mysql-connector-java-8.0.25.jar";,
+ "jdbc.driver_class" = "com.mysql.cj.jdbc.Driver"
+);
+```
+
+如果`jdbc.driver_url` 是http形式的远程jar包，Doris对其的处理方式为：
+1. 只查询元数据，不查询表数据情况下（如 `show catalogs/database/tables`
等操作）：FE会直接用这个url来加载驱动类，并进行MYSQL数据类型到Doris数据类型的转换。
+2. 在对jdbc catalog中的表进行查询时（`select
from`）：BE会将该url指定jar包下载到`be/lib/udf/`目录下，查询时将直接用下载后的路径来加载jar包。
+
+创建catalog后，可以通过 SHOW CATALOGS 命令查看 catalog：
+
+```sql
+MySQL [(none)]> show catalogs;
++-----------+-------------+----------+
+| CatalogId | CatalogName | Type |
++-----------+-------------+----------+
+| 0 | internal | internal |
+| 10480 | jdbc | jdbc |
++-----------+-------------+----------+
+2 rows in set (0.02 sec)
+```
+
+通过 SWITCH 命令切换到 es catalog，并查看其中的数据库：
+```sql
+MySQL [(none)]> switch jdbc;
+Query OK, 0 rows affected (0.02 sec)
+
+MySQL [(none)]> show databases;
++--------------------+
+| Database |
++--------------------+
+| __db1 |
+| _db1 |
+| db1 |
+| demo |
+| information_schema |
+| mysql |
+| mysql_db_test |
+| performance_schema |
+| sys |
++--------------------+
+9 rows in set (0.67 sec)
+```
+
+查看`db1`数据库下的表，并查询：
+```sql
+MySQL [demo]> use db1;
+Reading table information for completion of table and column names
+You can turn off this feature to get a quicker startup with -A
+
+Database changed
+MySQL [db1]> show tables;
++---------------+
+| Tables_in_db1 |
++---------------+
+| tbl1 |
++---------------+
+1 row in set (0.00 sec)
+
+MySQL [db1]> desc tbl1;
++-------+------+------+------+---------+-------+
+| Field | Type | Null | Key | Default | Extra |
++-------+------+------+------+---------+-------+
+| k1 | INT | Yes | true | NULL | |
++-------+------+------+------+---------+-------+
+1 row in set (0.00 sec)
+
+MySQL [db1]> select * from tbl1;
++------+
+| k1 |
++------+
+| 1 |
+| 2 |
+| 3 |
+| 4 |
++------+
+4 rows in set (0.19 sec)
+```
+
+#### 参数说明：
+
+参数 | 说明
+---|---
+**jdbc.user** | 连接数据库使用的用户名
+**jdbc.password** | 连接数据库使用的密码
+**jdbc.jdbc_url** | 连接到指定数据库的标识符
+**jdbc.driver_url** | jdbc驱动包的url
+**jdbc.driver_class** | jdbc驱动类
## 列类型映射
@@ -420,6 +532,32 @@ mysql> select * from test;
| array | | 开发中 |
|other| string ||
+### JDBC
+
+#### MYSQL
+ MYSQL Type | Doris Type | Comment |
+|---|---|---|
+| BOOLEAN | BOOLEAN | |
+| TINYINT | TINYINT | |
+| SMALLINT | SMALLINT | |
+| MEDIUMINT | INT | |
+| INT | INT | |
+| BIGINT | BIGINT | |
+| UNSIGNED TINYINT | SMALLINT | Doris没有UNSIGNED数据类型，所以扩大一个数量级|
+| UNSIGNED MEDIUMINT | INT | Doris没有UNSIGNED数据类型，所以扩大一个数量级|
+| UNSIGNED INT | BIGINT |Doris没有UNSIGNED数据类型，所以扩大一个数量级 |
+| UNSIGNED BIGINT | STRING | |
+| FLOAT | FLOAT | |
+| DOUBLE | DOUBLE | |
+| DECIMAL | DECIMAL | |
+| DATE | DATE | |
+| TIMESTAMP | DATETIME | |
+| DATETIME | DATETIME | |
+| YEAR | SMALLINT | |
+| TIME | STRING | |
+| CHAR | CHAR | |
+| VARCHAR | STRING | |
+|
TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB、TINYSTRING、STRING、MEDIUMSTRING、LONGSTRING、BINARY、VARBINARY、JSON、SET、BIT
| STRING | |
## 权限管理
使用 Doris 对 External Catalog 中库表进行访问，并不受外部数据目录自身的权限控制，而是依赖 Doris 自身的权限访问管理功能。
diff --git
a/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-CATALOG.md
b/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-CATALOG.md
index 5e9ccb09dc..52df04995b 100644
---
a/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-CATALOG.md
+++
b/docs/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-CATALOG.md
@@ -49,6 +49,7 @@ CREATE CATALOG [IF NOT EXISTS] catalog_name
* hms：Hive MetaStore
* es：Elasticsearch
+* jdbc：数据库访问的标准接口(JDBC), 当前只支持`jdbc:mysql`
### Example
@@ -75,6 +76,19 @@ CREATE CATALOG [IF NOT EXISTS] catalog_name
);
```
+3. 新建数据目录 jdbc
+
+ ```sql
+ CREATE CATALOG jdbc PROPERTIES (
+ "type"="jdbc",
+ "jdbc.user"="root",
+ "jdbc.password"="123456",
+ "jdbc.jdbc_url" = "jdbc:mysql://127.0.0.1:13396/demo",
+ "jdbc.driver_url" =
"file:/mnt/disk2/ftw/tools/jar/mysql-connector-java-5.1.47/mysql-connector-java-5.1.47.jar",
+ "jdbc.driver_class" = "com.mysql.jdbc.Driver"
+ );
+ ```
+
### Keywords
CREATE, CATALOG
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
