You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by mo...@apache.org on 2023/01/13 16:01:41 UTC
[doris] branch master updated: [feature](multi-catalog) support oracle jdbc catalog (#15862)
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 2580c88c1b [feature](multi-catalog) support oracle jdbc catalog (#15862)
2580c88c1b is described below
commit 2580c88c1b692827d69f57ca13d901570b518c25
Author: Tiewei Fang <43...@users.noreply.github.com>
AuthorDate: Sat Jan 14 00:01:33 2023 +0800
[feature](multi-catalog) support oracle jdbc catalog (#15862)
---
.../docker-compose/oracle/init/01-drop-user.sql | 18 ++++++
.../docker-compose/oracle/init/02-create-user.sql | 19 ++++++
.../docker-compose/oracle/init/03-create-table.sql | 70 ++++++++++++++++++++
.../docker-compose/oracle/init/04-insert.sql | 48 ++++++++++++++
.../docker-compose/oracle/oracle-11.env | 19 ++++++
.../docker-compose/oracle/oracle-11.yaml | 49 ++++++++++++++
docker/thirdparties/start-thirdparties-docker.sh | 7 ++
docker/thirdparties/stop-thirdparties-docker.sh | 3 +
.../docs/ecosystem/external-table/multi-catalog.md | 45 ++++++++++++-
.../Create/CREATE-CATALOG.md | 28 +++++++-
.../docs/ecosystem/external-table/multi-catalog.md | 45 ++++++++++++-
.../Create/CREATE-CATALOG.md | 28 +++++++-
.../org/apache/doris/catalog/JdbcResource.java | 2 +
.../org/apache/doris/external/jdbc/JdbcClient.java | 73 ++++++++++++++++++---
regression-test/conf/regression-conf.groovy | 1 +
.../jdbc_catalog_p0/test_oracle_jdbc_catalog.out | 33 ++++++++++
.../test_oracle_jdbc_catalog.groovy | 74 ++++++++++++++++++++++
17 files changed, 543 insertions(+), 19 deletions(-)
diff --git a/docker/thirdparties/docker-compose/oracle/init/01-drop-user.sql b/docker/thirdparties/docker-compose/oracle/init/01-drop-user.sql
new file mode 100644
index 0000000000..a47a923f97
--- /dev/null
+++ b/docker/thirdparties/docker-compose/oracle/init/01-drop-user.sql
@@ -0,0 +1,18 @@
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied. See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+
+drop user doris_test CASCADE;
diff --git a/docker/thirdparties/docker-compose/oracle/init/02-create-user.sql b/docker/thirdparties/docker-compose/oracle/init/02-create-user.sql
new file mode 100644
index 0000000000..dc59e57b78
--- /dev/null
+++ b/docker/thirdparties/docker-compose/oracle/init/02-create-user.sql
@@ -0,0 +1,19 @@
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied. See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+
+create user doris_test identified by 123456;
+grant connect, resource to doris_test;
diff --git a/docker/thirdparties/docker-compose/oracle/init/03-create-table.sql b/docker/thirdparties/docker-compose/oracle/init/03-create-table.sql
new file mode 100644
index 0000000000..efd648dff6
--- /dev/null
+++ b/docker/thirdparties/docker-compose/oracle/init/03-create-table.sql
@@ -0,0 +1,70 @@
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied. See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+
+create table doris_test.student (
+id number(5),
+name varchar2(20),
+age number(2),
+score number(3,1)
+);
+
+create table doris_test.test_num (
+id int,
+n1 number,
+n2 number(38),
+n3 number(9,2),
+n4 int,
+n5 smallint,
+n6 decimal(5,2),
+n7 float,
+n8 float(2),
+n9 real
+);
+
+create table doris_test.test_int (
+id int,
+tinyint_value1 number(2,0),
+smallint_value1 number(4,0),
+int_value1 number(9,0),
+bigint_value1 number(18,0),
+tinyint_value2 number(3,0),
+smallint_value2 number(5,0),
+int_value2 number(10,0),
+bigint_value2 number(19,0)
+);
+
+create table doris_test.test_char (
+id int,
+country char,
+city nchar(6),
+address varchar2(4000),
+name nvarchar2(6),
+remark long
+);
+
+create table doris_test.test_raw (
+id int,
+raw_value raw(20),
+long_raw_value long raw
+);
+
+create table doris_test.test_date (
+id int,
+t1 date,
+t2 interval year(3) to month,
+t3 interval day(3) to second(6)
+);
diff --git a/docker/thirdparties/docker-compose/oracle/init/04-insert.sql b/docker/thirdparties/docker-compose/oracle/init/04-insert.sql
new file mode 100644
index 0000000000..fd6ea2a57c
--- /dev/null
+++ b/docker/thirdparties/docker-compose/oracle/init/04-insert.sql
@@ -0,0 +1,48 @@
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied. See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+
+insert into doris_test.student values (1, 'alice', 20, 99.5);
+insert into doris_test.student values (2, 'bob', 21, 90.5);
+insert into doris_test.student values (3, 'jerry', 23, 88.0);
+insert into doris_test.student values (4, 'andy', 21, 93);
+
+insert into doris_test.test_num values
+(1, 111, 123, 7456123.89, 573, 34, 673.43, 34.1264, 56.2, 23.231);
+
+insert into doris_test.test_int values
+(1, 99, 9999, 999999999, 999999999999999999, 999, 99999, 9999999999, 9999999999999999999);
+insert into doris_test.test_int values
+(2, -99, -9999, -999999999, -999999999999999999, -999, -99999, -9999999999, -9999999999999999999);
+insert into doris_test.test_int values
+(3, 9.9, 99.99, 999999999, 999999999999999999, 999, 99999, 9999999999, 9999999999999999999);
+
+
+insert into doris_test.test_char values (1, '1', 'china', 'beijing', 'alice', 'abcdefghrjkmnopq');
+insert into doris_test.test_char values (2, '2', 'china', 'shanghai', 'bob', 'abcdefghrjkmnopq');
+insert into doris_test.test_char values (3, '3', 'Americ', 'new york', 'Jerry', 'abcdefghrjkmnopq');
+
+
+insert into doris_test.test_raw values (1, hextoraw('ffff'), hextoraw('aaaa'));
+insert into doris_test.test_raw values (2, utl_raw.cast_to_raw('beijing'), utl_raw.cast_to_raw('shanghai'));
+
+insert into doris_test.test_date (id, t1) values (1, to_date('2022-1-21 5:23:01','yyyy-mm-dd hh24:mi:ss'));
+insert into doris_test.test_date (id, t1) values (2, to_date('20221112203256', 'yyyymmddhh24miss'));
+insert into doris_test.test_date (id, t2) values (3, interval '11' year);
+insert into doris_test.test_date (id, t2) values (4, interval '223-9' year(3) to month);
+insert into doris_test.test_date (id, t3) values (5, interval '12 10:23:01.1234568' day to second);
+
+commit;
diff --git a/docker/thirdparties/docker-compose/oracle/oracle-11.env b/docker/thirdparties/docker-compose/oracle/oracle-11.env
new file mode 100644
index 0000000000..f3d1f22efc
--- /dev/null
+++ b/docker/thirdparties/docker-compose/oracle/oracle-11.env
@@ -0,0 +1,19 @@
+#!/usr/bin/env bash
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+DOCKER_ORACLE_EXTERNAL_PORT=1521
diff --git a/docker/thirdparties/docker-compose/oracle/oracle-11.yaml b/docker/thirdparties/docker-compose/oracle/oracle-11.yaml
new file mode 100644
index 0000000000..93225aacd3
--- /dev/null
+++ b/docker/thirdparties/docker-compose/oracle/oracle-11.yaml
@@ -0,0 +1,49 @@
+#
+# Licensed to the Apache Software Foundation (ASF) under one or more
+# contributor license agreements. See the NOTICE file distributed with
+# this work for additional information regarding copyright ownership.
+# The ASF licenses this file to You under the Apache License, Version 2.0
+# (the "License"); you may not use this file except in compliance with
+# the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+#
+
+version: '3'
+
+services:
+ doris--oracle_11:
+ image: oracleinanutshell/oracle-xe-11g:latest
+ restart: always
+ ports:
+ - ${DOCKER_ORACLE_EXTERNAL_PORT}:1521
+ privileged: true
+ healthcheck:
+ test: [ "CMD", "bash", "-c", "echo 'select 1 from dual;' | ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe /u01/app/oracle/product/11.2.0/xe/bin/sqlplus -s DORIS_TEST/123456@localhost"]
+ interval: 20s
+ timeout: 60s
+ retries: 120
+ volumes:
+ - ./init:/docker-entrypoint-initdb.d
+ environment:
+ - ORACLE_ALLOW_REMOTE=true
+ - ORACLE_ENABLE_XDB=true
+ - DBCA_TOTAL_MEMORY=2048
+ - IMPORT_FROM_VOLUME=true
+ - TZ=Asia/Shanghai
+ networks:
+ - doris--oracle_11
+ hello-world:
+ image: hello-world
+ depends_on:
+ doris--oracle_11:
+ condition: service_healthy
+
+networks:
+ doris--oracle_11:
\ No newline at end of file
diff --git a/docker/thirdparties/start-thirdparties-docker.sh b/docker/thirdparties/start-thirdparties-docker.sh
index 986d04da7a..61e0df9713 100755
--- a/docker/thirdparties/start-thirdparties-docker.sh
+++ b/docker/thirdparties/start-thirdparties-docker.sh
@@ -56,6 +56,13 @@ sudo mkdir -p "${ROOT}"/docker-compose/postgresql/data/data
sudo rm "${ROOT}"/docker-compose/postgresql/data/data/* -rf
sudo docker compose -f "${ROOT}"/docker-compose/postgresql/postgresql-14.yaml --env-file "${ROOT}"/docker-compose/postgresql/postgresql-14.env up -d
+# oracle
+sed -i "s/doris--/${CONTAINER_UID}/g" "${ROOT}"/docker-compose/oracle/oracle-11.yaml
+sudo docker compose -f "${ROOT}"/docker-compose/oracle/oracle-11.yaml --env-file "${ROOT}"/docker-compose/oracle/oracle-11.env down
+sudo mkdir -p "${ROOT}"/docker-compose/oracle/data/
+sudo rm "${ROOT}"/docker-compose/oracle/data/* -rf
+sudo docker compose -f "${ROOT}"/docker-compose/oracle/oracle-11.yaml --env-file "${ROOT}"/docker-compose/oracle/oracle-11.env up -d
+
# hive
# before start it, you need to download parquet file package, see "README" in "docker-compose/hive/scripts/"
sed -i "s/doris--/${CONTAINER_UID}/g" "${ROOT}"/docker-compose/hive/hive-2x.yaml
diff --git a/docker/thirdparties/stop-thirdparties-docker.sh b/docker/thirdparties/stop-thirdparties-docker.sh
index 4ed3e78c18..dc2c5773d8 100755
--- a/docker/thirdparties/stop-thirdparties-docker.sh
+++ b/docker/thirdparties/stop-thirdparties-docker.sh
@@ -33,5 +33,8 @@ sudo docker compose -f "${ROOT}"/docker-compose/mysql/mysql-5.7.yaml --env-file
# pg 14
sudo docker compose -f "${ROOT}"/docker-compose/postgresql/postgresql-14.yaml --env-file "${ROOT}"/docker-compose/postgresql/postgresql-14.env down
+# oracle 11
+sudo docker compose -f "${ROOT}"/docker-compose/oracle/oracle-11.yaml --env-file "${ROOT}"/docker-compose/oracle/oracle-11.env down
+
# hive
sudo docker compose -f "${ROOT}"/docker-compose/hive/hive-2x.yaml --env-file "${ROOT}"/docker-compose/hive/hadoop-hive.env down
diff --git a/docs/en/docs/ecosystem/external-table/multi-catalog.md b/docs/en/docs/ecosystem/external-table/multi-catalog.md
index 53bef24bea..0ff5a471bf 100644
--- a/docs/en/docs/ecosystem/external-table/multi-catalog.md
+++ b/docs/en/docs/ecosystem/external-table/multi-catalog.md
@@ -431,7 +431,7 @@ CREATE CATALOG jdbc PROPERTIES (
**CLICKHOUSE catalog example**
```sql
--- 1.2.0+ Version
+-- The first way
CREATE RESOURCE clickhouse_resource PROPERTIES (
"type"="jdbc",
"user"="default",
@@ -442,7 +442,7 @@ CREATE RESOURCE clickhouse_resource PROPERTIES (
)
CREATE CATALOG jdbc WITH RESOURCE clickhouse_resource;
--- 1.2.0 Version
+-- The second way, note: keys have 'jdbc' prefix in front.
CREATE CATALOG jdbc PROPERTIES (
"type"="jdbc",
"jdbc.jdbc_url" = "jdbc:clickhouse://127.0.0.1:8123/demo",
@@ -450,6 +450,31 @@ CREATE CATALOG jdbc PROPERTIES (
)
```
+**oracle catalog example**
+
+```sql
+-- The first way
+CREATE RESOURCE oracle_resource PROPERTIES (
+ "type"="jdbc",
+ "user"="doris",
+ "password"="123456",
+ "jdbc_url" = "jdbc:oracle:thin:@127.0.0.1:1521:helowin",
+ "driver_url" = "file:/path/to/ojdbc6.jar",
+ "driver_class" = "oracle.jdbc.driver.OracleDriver"
+);
+CREATE CATALOG jdbc WITH RESOURCE oracle_resource;
+
+-- The second way, note: keys have 'jdbc' prefix in front.
+CREATE CATALOG jdbc PROPERTIES (
+ "type"="jdbc",
+ "jdbc.user"="doris",
+ "jdbc.password"="123456",
+ "jdbc.jdbc_url" = "jdbc:oracle:thin:@127.0.0.1:1521:helowin",
+ "jdbc.driver_url" = "file:/path/to/ojdbc6.jar",
+ "jdbc.driver_class" = "oracle.jdbc.driver.OracleDriver"
+);
+```
+
Where `jdbc.driver_url` can be a remote jar package
```sql
@@ -482,7 +507,9 @@ MySQL [(none)]> show catalogs;
2 rows in set (0.02 sec)
```
-> Note: In the `postgresql catalog`, a database for doris corresponds to a schema in the postgresql specified catalog (specified in the `jdbc.jdbc_url` parameter), tables under this database corresponds to tables under this postgresql's schema.
+> Note:
+> 1. In the `postgresql catalog`, a database for doris corresponds to a schema in the postgresql specified catalog (specified in the `jdbc.jdbc_url` parameter), tables under this database corresponds to tables under this postgresql's schema.
+> 2. In the `oracle catalog`, a database for doris corresponds to a user in the oracle, tables under this database corresponds to tables under this oracle's user.
Switch to the jdbc catalog with the `SWITCH` command and view the databases in it:
@@ -765,6 +792,18 @@ For Hive/Iceberge/Hudi
| DECIMAL | DECIMAL | Data that exceeds Doris's maximum Decimal precision is mapped to a STRING |
| Enum/IPv4/IPv6/UUID | STRING | In the display of IPv4 and IPv6, an extra `/` is displayed before the data, which needs to be processed by the `split_part` function |
+#### ORACLE
+ ORACLE Type | Doris Type | Comment |
+|---|---|---|
+| number(p) / number(p,0) | | Doris will choose the corresponding doris type based on the p: p<3 -> TINYINT; p<5 -> SMALLINT; p<10 -> INT; p<19 -> BIGINT; p>19 -> LARGEINT |
+| number(p,s) | DECIMAL | |
+| decimal | DECIMAL | |
+| float/real | DOUBLE | |
+| DATE | DATETIME | |
+| CHAR/NCHAR | CHAR | |
+| VARCHAR2/NVARCHAR2 | VARCHAR | |
+| LONG/ RAW/ LONG RAW/ INTERVAL | TEXT | |
+
## 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 5b2330bc09..b92f712dca 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
@@ -148,7 +148,7 @@ CREATE CATALOG catalog_name PROPERTIES (
**postgresql**
```sql
- -- 1.2.0+ Version
+ -- The first way
CREATE RESOURCE pg_resource PROPERTIES (
"type"="jdbc",
"user"="postgres",
@@ -159,7 +159,7 @@ CREATE CATALOG catalog_name PROPERTIES (
);
CREATE CATALOG jdbc WITH RESOURCE pg_resource;
- -- 1.2.0 Version
+ -- The second way, note: keys have 'jdbc' prefix in front.
CREATE CATALOG jdbc PROPERTIES (
"type"="jdbc",
"jdbc.user"="postgres",
@@ -192,6 +192,30 @@ CREATE CATALOG catalog_name PROPERTIES (
)
```
+ **oracle**
+ ```sql
+ -- The first way
+ CREATE RESOURCE oracle_resource PROPERTIES (
+ "type"="jdbc",
+ "user"="doris",
+ "password"="123456",
+ "jdbc_url" = "jdbc:oracle:thin:@127.0.0.1:1521:helowin",
+ "driver_url" = "file:/path/to/ojdbc6.jar",
+ "driver_class" = "oracle.jdbc.driver.OracleDriver"
+ );
+ CREATE CATALOG jdbc WITH RESOURCE oracle_resource;
+
+ -- The second way, note: keys have 'jdbc' prefix in front.
+ CREATE CATALOG jdbc PROPERTIES (
+ "type"="jdbc",
+ "jdbc.user"="doris",
+ "jdbc.password"="123456",
+ "jdbc.jdbc_url" = "jdbc:oracle:thin:@127.0.0.1:1521:helowin",
+ "jdbc.driver_url" = "file:/path/to/ojdbc6.jar",
+ "jdbc.driver_class" = "oracle.jdbc.driver.OracleDriver"
+ );
+ ```
+
### 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 9c491e541d..bb0ed04890 100644
--- a/docs/zh-CN/docs/ecosystem/external-table/multi-catalog.md
+++ b/docs/zh-CN/docs/ecosystem/external-table/multi-catalog.md
@@ -504,7 +504,7 @@ CREATE CATALOG jdbc PROPERTIES (
**CLICKHOUSE catalog示例**
```sql
--- 1.2.0+ 版本
+-- 方式一
CREATE RESOURCE clickhouse_resource PROPERTIES (
"type"="jdbc",
"user"="default",
@@ -515,7 +515,7 @@ CREATE RESOURCE clickhouse_resource PROPERTIES (
)
CREATE CATALOG jdbc WITH RESOURCE clickhouse_resource;
--- 1.2.0 版本
+-- 方式二，注意有jdbc前缀
CREATE CATALOG jdbc PROPERTIES (
"type"="jdbc",
"jdbc.jdbc_url" = "jdbc:clickhouse://127.0.0.1:8123/demo",
@@ -523,6 +523,31 @@ CREATE CATALOG jdbc PROPERTIES (
)
```
+**ORACLE catalog示例**
+
+```sql
+-- 方式一
+CREATE RESOURCE oracle_resource PROPERTIES (
+ "type"="jdbc",
+ "user"="doris",
+ "password"="123456",
+ "jdbc_url" = "jdbc:oracle:thin:@127.0.0.1:1521:helowin",
+ "driver_url" = "file:/path/to/ojdbc6.jar",
+ "driver_class" = "oracle.jdbc.driver.OracleDriver"
+);
+CREATE CATALOG jdbc WITH RESOURCE oracle_resource;
+
+-- 方式二，注意有jdbc前缀
+CREATE CATALOG jdbc PROPERTIES (
+ "type"="jdbc",
+ "jdbc.user"="doris",
+ "jdbc.password"="123456",
+ "jdbc.jdbc_url" = "jdbc:oracle:thin:@127.0.0.1:1521:helowin",
+ "jdbc.driver_url" = "file:/path/to/ojdbc6.jar",
+ "jdbc.driver_class" = "oracle.jdbc.driver.OracleDriver"
+);
+```
+
其中`jdbc.driver_url`可以是远程jar包：
```sql
@@ -577,7 +602,9 @@ MySQL [(none)]> show databases;
9 rows in set (0.67 sec)
```
-> 注意：在postgresql catalog中，doris的一个database对应于postgresql中指定catalog（`jdbc.jdbc_url`参数中指定的catalog）下的一个schema，database下的tables则对应于postgresql该schema下的tables。
+> 注意：
+> 1. 在postgresql catalog中，doris的一个database对应于postgresql中指定catalog（`jdbc.jdbc_url`参数中指定的catalog）下的一个schema，database下的tables则对应于postgresql该schema下的tables。
+> 2. 在oracle catalog中，doris的一个database对应于oracle中的一个user，database下的tables则对应于oracle该user下的有权限访问的tables。
查看`db1`数据库下的表，并查询：
```sql
@@ -764,6 +791,18 @@ select k1, k4 from table; // Query OK.
| DECIMAL | DECIMAL | 对于超过了Doris最大的Decimal精度的数据，将映射为STRING |
| Enum/IPv4/IPv6/UUID | STRING | 在显示上IPv4,IPv6会额外在数据最前面显示一个`/`,需要自己用`split_part`函数处理 |
+#### ORACLE
+ ORACLE Type | Doris Type | Comment |
+|---|---|---|
+| number(p) / number(p,0) | | Doris会根据p的大小来选择对应的类型：p<3 -> TINYINT; p<5 -> SMALLINT; p<10 -> INT; p<19 -> BIGINT; p>19 -> LARGEINT |
+| number(p,s) | DECIMAL | |
+| decimal | DECIMAL | |
+| float/real | DOUBLE | |
+| DATE | DATETIME | |
+| CHAR/NCHAR | CHAR | |
+| VARCHAR2/NVARCHAR2 | VARCHAR | |
+| LONG/ RAW/ LONG RAW/ INTERVAL | TEXT | |
+
## 权限管理
使用 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 81ee7b30cb..9c71ee73a8 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
@@ -154,7 +154,7 @@ CREATE CATALOG catalog_name PROPERTIES (
**postgresql**
```sql
- -- 1.2.0+ 版本
+ -- 方式一
CREATE RESOURCE pg_resource PROPERTIES (
"type"="jdbc",
"user"="postgres",
@@ -165,7 +165,7 @@ CREATE CATALOG catalog_name PROPERTIES (
);
CREATE CATALOG jdbc WITH RESOURCE pg_resource;
- -- 1.2.0 版本
+ -- 方式二，注意有jdbc前缀
CREATE CATALOG jdbc PROPERTIES (
"type"="jdbc",
"jdbc.user"="postgres",
@@ -198,6 +198,30 @@ CREATE CATALOG catalog_name PROPERTIES (
)
```
+ **oracle**
+ ```sql
+ -- 方式一
+ CREATE RESOURCE oracle_resource PROPERTIES (
+ "type"="jdbc",
+ "user"="doris",
+ "password"="123456",
+ "jdbc_url" = "jdbc:oracle:thin:@127.0.0.1:1521:helowin",
+ "driver_url" = "file:/path/to/ojdbc6.jar",
+ "driver_class" = "oracle.jdbc.driver.OracleDriver"
+ );
+ CREATE CATALOG jdbc WITH RESOURCE oracle_resource;
+
+ -- 方式二，注意有jdbc前缀
+ CREATE CATALOG jdbc PROPERTIES (
+ "type"="jdbc",
+ "jdbc.user"="doris",
+ "jdbc.password"="123456",
+ "jdbc.jdbc_url" = "jdbc:oracle:thin:@127.0.0.1:1521:helowin",
+ "jdbc.driver_url" = "file:/path/to/ojdbc6.jar",
+ "jdbc.driver_class" = "oracle.jdbc.driver.OracleDriver"
+ );
+ ```
+
### Keywords
CREATE, CATALOG
diff --git a/fe/fe-core/src/main/java/org/apache/doris/catalog/JdbcResource.java b/fe/fe-core/src/main/java/org/apache/doris/catalog/JdbcResource.java
index 3862a38e3b..add3009de8 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/catalog/JdbcResource.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/catalog/JdbcResource.java
@@ -66,6 +66,7 @@ public class JdbcResource extends Resource {
public static final String JDBC_ORACLE = "jdbc:oracle";
public static final String JDBC_SQLSERVER = "jdbc:sqlserver";
public static final String JDBC_CLICKHOUSE = "jdbc:clickhouse";
+
public static final String MYSQL = "MYSQL";
public static final String POSTGRESQL = "POSTGRESQL";
public static final String ORACLE = "ORACLE";
@@ -253,6 +254,7 @@ public class JdbcResource extends Resource {
// it will convert to Doris tinyint, not bit.
newJdbcUrl = checkJdbcUrlParam(newJdbcUrl, "yearIsDateType", "true", "false");
newJdbcUrl = checkJdbcUrlParam(newJdbcUrl, "tinyInt1isBit", "true", "false");
+ newJdbcUrl = checkJdbcUrlParam(newJdbcUrl, "useCursorFetch", "false", "true");
}
return newJdbcUrl;
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcClient.java b/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcClient.java
index fa10819a7b..cc8fc80b3d 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcClient.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/external/jdbc/JdbcClient.java
@@ -157,17 +157,18 @@ public class JdbcClient {
stmt = conn.createStatement();
switch (dbType) {
case JdbcResource.MYSQL:
+ case JdbcResource.CLICKHOUSE:
rs = stmt.executeQuery("SHOW DATABASES");
break;
case JdbcResource.POSTGRESQL:
rs = stmt.executeQuery("SELECT schema_name FROM information_schema.schemata "
+ "where schema_owner='" + jdbcUser + "';");
break;
- case JdbcResource.CLICKHOUSE:
- rs = stmt.executeQuery("SHOW DATABASES");
+ case JdbcResource.ORACLE:
+ rs = stmt.executeQuery("SELECT DISTINCT OWNER FROM all_tables");
break;
default:
- throw new JdbcClientException("Not supported jdbc type");
+ throw new JdbcClientException("Not supported jdbc type");
}
while (rs.next()) {
@@ -196,8 +197,7 @@ public class JdbcClient {
rs = databaseMetaData.getTables(dbName, null, null, types);
break;
case JdbcResource.POSTGRESQL:
- rs = databaseMetaData.getTables(null, dbName, null, types);
- break;
+ case JdbcResource.ORACLE:
case JdbcResource.CLICKHOUSE:
rs = databaseMetaData.getTables(null, dbName, null, types);
break;
@@ -226,8 +226,7 @@ public class JdbcClient {
rs = databaseMetaData.getTables(dbName, null, tableName, types);
break;
case JdbcResource.POSTGRESQL:
- rs = databaseMetaData.getTables(null, dbName, null, types);
- break;
+ case JdbcResource.ORACLE:
case JdbcResource.CLICKHOUSE:
rs = databaseMetaData.getTables(null, dbName, null, types);
break;
@@ -297,8 +296,7 @@ public class JdbcClient {
rs = databaseMetaData.getColumns(dbName, null, tableName, null);
break;
case JdbcResource.POSTGRESQL:
- rs = databaseMetaData.getColumns(null, dbName, tableName, null);
- break;
+ case JdbcResource.ORACLE:
case JdbcResource.CLICKHOUSE:
rs = databaseMetaData.getColumns(null, dbName, tableName, null);
break;
@@ -334,6 +332,8 @@ public class JdbcClient {
return postgresqlTypeToDoris(fieldSchema);
case JdbcResource.CLICKHOUSE:
return clickhouseTypeToDoris(fieldSchema);
+ case JdbcResource.ORACLE:
+ return oracleTypeToDoris(fieldSchema);
default:
throw new JdbcClientException("Unknown database type");
}
@@ -563,6 +563,61 @@ public class JdbcClient {
// Todo(zyk): Wait the JDBC external table support the array type then supported clickhouse array type
}
+ public Type oracleTypeToDoris(JdbcFieldSchema fieldSchema) {
+ String oracleType = fieldSchema.getDataTypeName();
+ if (oracleType.startsWith("INTERVAL")) {
+ oracleType = oracleType.substring(0, 8);
+ }
+ switch (oracleType) {
+ case "NUMBER":
+ int precision = fieldSchema.getColumnSize();
+ int scale = fieldSchema.getDecimalDigits();
+ if (scale == 0) {
+ if (precision < 3) {
+ return Type.TINYINT;
+ } else if (precision < 5) {
+ return Type.SMALLINT;
+ } else if (precision < 10) {
+ return Type.INT;
+ } else if (precision < 19) {
+ return Type.BIGINT;
+ } else if (precision < 39) {
+ return Type.LARGEINT;
+ }
+ return ScalarType.createStringType();
+ }
+ if (precision <= ScalarType.MAX_DECIMAL128_PRECISION) {
+ if (!Config.enable_decimal_conversion && precision > ScalarType.MAX_DECIMALV2_PRECISION) {
+ return ScalarType.createStringType();
+ }
+ return ScalarType.createDecimalType(precision, scale);
+ } else {
+ return ScalarType.createStringType();
+ }
+ case "FLOAT":
+ return Type.DOUBLE;
+ case "DATE":
+ return ScalarType.getDefaultDateType(Type.DATETIME);
+ case "VARCHAR2":
+ case "NVARCHAR2":
+ case "CHAR":
+ case "NCHAR":
+ case "LONG":
+ case "RAW":
+ case "LONG RAW":
+ case "INTERVAL":
+ return ScalarType.createStringType();
+ case "BLOB":
+ case "CLOB":
+ case "NCLOB":
+ case "BFILE":
+ case "BINARY_FLOAT":
+ case "BINARY_DOUBLE":
+ default:
+ return Type.UNSUPPORTED;
+ }
+ }
+
public List<Column> getColumnsFromJdbc(String dbName, String tableName) {
List<JdbcFieldSchema> jdbcTableSchema = getJdbcColumnsInfo(dbName, tableName);
List<Column> dorisTableSchema = Lists.newArrayListWithCapacity(jdbcTableSchema.size());
diff --git a/regression-test/conf/regression-conf.groovy b/regression-test/conf/regression-conf.groovy
index 209dd24a3c..d05791ff7d 100644
--- a/regression-test/conf/regression-conf.groovy
+++ b/regression-test/conf/regression-conf.groovy
@@ -73,6 +73,7 @@ sk=""
enableJdbcTest=false
mysql_57_port=3316
pg_14_port=5442
+oracle_11_port=1521
// hive catalog test config
// To enable jdbc test, you need first start hive container.
diff --git a/regression-test/data/jdbc_catalog_p0/test_oracle_jdbc_catalog.out b/regression-test/data/jdbc_catalog_p0/test_oracle_jdbc_catalog.out
new file mode 100644
index 0000000000..0fe8aa929c
--- /dev/null
+++ b/regression-test/data/jdbc_catalog_p0/test_oracle_jdbc_catalog.out
@@ -0,0 +1,33 @@
+-- This file is automatically generated. You should know what you did if you want to edit this
+-- !test0 --
+1 alice 20 99.5
+2 bob 21 90.5
+3 jerry 23 88.0
+4 andy 21 93.0
+
+-- !in_tb --
+1 alice 20
+2 bob 21
+3 jerry 23
+4 andy 21
+
+-- !test1 --
+1 111 123 7456123.89 573 34 673.43 34.1264 60.0 23.231
+
+-- !test2 --
+1 1 china beijing alice abcdefghrjkmnopq
+2 2 china shanghai bob abcdefghrjkmnopq
+3 3 Americ new york Jerry abcdefghrjkmnopq
+
+-- !test3 --
+1 99 9999 999999999 999999999999999999 999 99999 9999999999 9999999999999999999
+2 -99 -9999 -999999999 -999999999999999999 -999 -99999 -9999999999 -9999999999999999999
+3 10 100 999999999 999999999999999999 999 99999 9999999999 9999999999999999999
+
+-- !test5 --
+1 2022-01-21T05:23:01 \N \N
+2 2022-11-12T20:32:56 \N \N
+3 \N 11-0 \N
+4 \N 223-9 \N
+5 \N \N 12 10:23:1.123457000
+
diff --git a/regression-test/suites/jdbc_catalog_p0/test_oracle_jdbc_catalog.groovy b/regression-test/suites/jdbc_catalog_p0/test_oracle_jdbc_catalog.groovy
new file mode 100644
index 0000000000..9d473e7598
--- /dev/null
+++ b/regression-test/suites/jdbc_catalog_p0/test_oracle_jdbc_catalog.groovy
@@ -0,0 +1,74 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite("test_oracle_jdbc_catalog", "p0") {
+ String enabled = context.config.otherConfigs.get("enableJdbcTest");
+ if (enabled != null && enabled.equalsIgnoreCase("true")) {
+ String resource_name = "oracle_catalog_resource";
+ String catalog_name = "oracle_catalog";
+ String internal_db_name = "regression_test_jdbc_catalog_p0";
+ String ex_db_name = "DORIS_TEST";
+ String oracle_port = context.config.otherConfigs.get("oracle_11_port");
+ String SID = "XE"
+
+ String inDorisTable = "doris_in_tb";
+
+ sql """drop catalog if exists ${catalog_name} """
+ sql """drop resource if exists ${resource_name}"""
+
+ sql """create resource if not exists ${resource_name} properties(
+ "type"="jdbc",
+ "user"="doris_test",
+ "password"="123456",
+ "jdbc_url" = "jdbc:oracle:thin:@127.0.0.1:${oracle_port}:${SID}",
+ "driver_url" = "https://doris-community-test-1308700295.cos.ap-hongkong.myqcloud.com/jdbc_driver/ojdbc6.jar",
+ "driver_class" = "oracle.jdbc.driver.OracleDriver"
+ );"""
+
+ sql """CREATE CATALOG ${catalog_name} WITH RESOURCE ${resource_name}"""
+
+ sql """ drop table if exists ${inDorisTable} """
+ sql """
+ CREATE TABLE ${inDorisTable} (
+ `id` INT NULL COMMENT "主键id",
+ `name` string NULL COMMENT "名字",
+ `age` INT NULL COMMENT "年龄"
+ ) DISTRIBUTED BY HASH(id) BUCKETS 10
+ PROPERTIES("replication_num" = "1");
+ """
+
+ sql """switch ${catalog_name}"""
+ sql """ use ${ex_db_name}"""
+
+ order_qt_test0 """ select * from STUDENT order by ID; """
+ sql """ insert into internal.${internal_db_name}.${inDorisTable} select ID, NAME, AGE from STUDENT; """
+ order_qt_in_tb """ select id, name, age from internal.${internal_db_name}.${inDorisTable} order by id; """
+
+ order_qt_test1 """ select * from TEST_NUM order by ID; """
+ order_qt_test2 """ select * from TEST_CHAR order by ID; """
+ order_qt_test3 """ select * from TEST_INT order by ID; """
+ order_qt_test5 """ select * from TEST_DATE order by ID; """
+
+ // The result of TEST_RAW will change
+ // So instead of qt, we're using sql here.
+ sql """ select * from TEST_RAW order by ID; """
+
+
+ sql """drop catalog if exists ${catalog_name} """
+ sql """drop resource if exists jdbc_resource_catalog_pg"""
+ }
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org