Skip to content

Commit 087fccd

Browse files
authored
[enhancement](jdbc catalog) Add lowercase column name mapping to Jdbc data source & optimize database and table mapping #27124 (#27130)
1 parent ea40551 commit 087fccd

13 files changed

Lines changed: 196 additions & 96 deletions

File tree

‎docker/thirdparties/docker-compose/oracle/init/03-create-table.sql‎

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -132,6 +132,13 @@ age number(2),
132132
score number(3,1)
133133
);
134134

135+
CREATE TABLE "DORIS_TEST"."student3"
136+
(
137+
"id" NUMBER(5,0),
138+
"NAME" VARCHAR2(20),
139+
"AGE" NUMBER(2,0),
140+
"SCORE" NUMBER(3,1)
141+
);
135142

136143
create table doris_test.test_all_types (
137144
id int,

‎docker/thirdparties/docker-compose/oracle/init/04-insert.sql‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -86,6 +86,8 @@ insert into doris_test."student2" values (2, 'bob', 21, 90.5);
8686
insert into doris_test."student2" values (3, 'jerry', 23, 88.0);
8787
insert into doris_test."student2" values (4, 'andy', 21, 93);
8888

89+
insert into doris_test."student3" values(1, 'doris', 3, 1.0);
90+
8991
insert into doris_test.test_all_types values
9092
(1, 111, 123, 7456123.89, 573, 34, 673.43, 34.1264, 56.2, 23.231,
9193
99, 9999, 999999999, 999999999999999999, 999, 99999, 9999999999, 9999999999999999999,

‎docs/en/docs/lakehouse/multi-catalog/jdbc.md‎

Lines changed: 13 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -52,7 +52,7 @@ PROPERTIES ("key"="value", ...)
5252
| `driver_url ` | Yes | | JDBC Driver Jar |
5353
| `driver_class ` | Yes | | JDBC Driver Class |
5454
| `only_specified_database` | No | "false" | Whether only the database specified to be synchronized. |
55-
| `lower_case_table_names` | No | "false" | Whether to synchronize jdbc external data source table names in lower case. |
55+
| `lower_case_table_names` | No | "false" | Whether to synchronize the database name, table name and column name of jdbc external data source in lowercase. |
5656
| `include_database_list` | No | "" | When only_specified_database=true,only synchronize the specified databases. split with ','. db name is case sensitive. |
5757
| `exclude_database_list` | No | "" | When only_specified_database=true,do not synchronize the specified databases. split with ','. db name is case sensitive. |
5858

@@ -68,7 +68,7 @@ PROPERTIES ("key"="value", ...)
6868

6969
### Lowercase table name synchronization
7070

71-
When `lower_case_table_names` is set to `true`, Doris is able to query non-lowercase databases and tables by maintaining a mapping of lowercase names to actual names on the remote system
71+
When `lower_case_table_names` is set to `true`, Doris is able to query non-lowercase databases and tables and columns by maintaining a mapping of lowercase names to actual names on the remote system
7272

7373
**Notice:**
7474

@@ -78,9 +78,9 @@ When `lower_case_table_names` is set to `true`, Doris is able to query non-lower
7878

7979
For other databases, you still need to specify the real library name and table name when querying.
8080

81-
2. In Doris 2.0.3 and later versions, it is valid for all databases. When querying, all library names and table names will be converted into real names and then queried. If you upgrade from an old version to 2.0. 3, `Refresh <catalog_name>` is required to take effect.
81+
2. In Doris 2.0.3 and later versions, it is valid for all databases. When querying, all database names and table names and columns will be converted into real names and then queried. If you upgrade from an old version to 2.0. 3, `Refresh <catalog_name>` is required to take effect.
8282

83-
However, if the database or table names differ only in case, such as `Doris` and `doris`, Doris cannot query them due to ambiguity.
83+
However, if the database or table or column names differ only in case, such as `Doris` and `doris`, Doris cannot query them due to ambiguity.
8484

8585
3. When the FE parameter's `lower_case_table_names` is set to `1` or `2`, the JDBC Catalog's `lower_case_table_names` parameter must be set to `true`. If the FE parameter's `lower_case_table_names` is set to `0`, the JDBC Catalog parameter can be `true` or `false` and defaults to `false`. This ensures consistency and predictability in how Doris handles internal and external table configurations.
8686

@@ -113,8 +113,8 @@ In some cases, the keywords in the database might be used as the field names. Fo
113113
### Predicate Pushdown
114114

115115
1. When executing a query like `where dt = '2022-01-01'`, Doris can push down these filtering conditions to the external data source, thereby directly excluding data that does not meet the conditions at the data source level, reducing the number of unqualified Necessary data acquisition and transfer. This greatly improves query performance while also reducing the load on external data sources.
116-
117-
2. When `enable_func_pushdown` is set to true, the function condition after where will also be pushed down to the external data source. Currently, only MySQL is supported. If you encounter a function that MySQL does not support, you can set this parameter to false, at present, Doris will automatically identify some functions not supported by MySQL to filter the push-down conditions, which can be checked by explain sql.
116+
117+
2. When `enable_func_pushdown` is set to true, the function conditions after where will also be pushed down to the external data source. Currently, only MySQL and ClickHouse are supported. If you encounter a function that is not supported by MySQL or ClickHouse, you can set this parameter to false. , currently Doris will automatically identify some functions not supported by MySQL and functions supported by CLickHouse for push-down condition filtering, which can be viewed through explain sql.
118118

119119
Functions that are currently not pushed down include:
120120

@@ -123,6 +123,13 @@ Functions that are currently not pushed down include:
123123
| DATE_TRUNC |
124124
| MONEY_FORMAT |
125125

126+
Functions that are currently pushed down include:
127+
128+
| ClickHouse |
129+
|:--------------:|
130+
| FROM_UNIXTIME |
131+
| UNIX_TIMESTAMP |
132+
126133
### Line Limit
127134

128135
If there is a limit keyword in the query, Doris will translate it into semantics suitable for different data sources.

‎docs/zh-CN/docs/lakehouse/multi-catalog/jdbc.md‎

Lines changed: 21 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -44,16 +44,16 @@ PROPERTIES ("key"="value", ...)
4444

4545
## 参数说明
4646

47-
| 参数 | 必须 | 默认值 | 说明 |
48-
|---------------------------|-----|---------|---------------------------------------------------------------------------------------------|
49-
| `user` | 是 | | 对应数据库的用户名 |
50-
| `password` | 是 | | 对应数据库的密码 |
51-
| `jdbc_url` | 是 | | JDBC 连接串 |
52-
| `driver_url` | 是 | | JDBC Driver Jar 包名称 |
53-
| `driver_class` | 是 | | JDBC Driver Class 名称 |
54-
| `lower_case_table_names` | 否 | "false" | 是否以小写的形式同步jdbc外部数据源的库名和表名 |
55-
| `only_specified_database` | 否 | "false" | 指定是否只同步指定的 database |
56-
| `include_database_list` | 否 | "" | 当only_specified_database=true时,指定同步多个database,以','分隔。db名称是大小写敏感的。 |
47+
| 参数 | 必须 | 默认值 | 说明 |
48+
|---------------------------|-----|---------|-----------------------------------------------------------------------|
49+
| `user` | 是 | | 对应数据库的用户名 |
50+
| `password` | 是 | | 对应数据库的密码 |
51+
| `jdbc_url` | 是 | | JDBC 连接串 |
52+
| `driver_url` | 是 | | JDBC Driver Jar 包名称 |
53+
| `driver_class` | 是 | | JDBC Driver Class 名称 |
54+
| `lower_case_table_names` | 否 | "false" | 是否以小写的形式同步jdbc外部数据源的库名和表名以及列名 |
55+
| `only_specified_database` | 否 | "false" | 指定是否只同步指定的 database |
56+
| `include_database_list` | 否 | "" | 当only_specified_database=true时,指定同步多个database,以','分隔。db名称是大小写敏感的。 |
5757
| `exclude_database_list` | 否 | "" | 当only_specified_database=true时,指定不需要同步的多个database,以','分割。db名称是大小写敏感的。 |
5858

5959
### 驱动包路径
@@ -68,7 +68,7 @@ PROPERTIES ("key"="value", ...)
6868

6969
### 小写表名同步
7070

71-
当 `lower_case_table_names` 设置为 `true` 时,Doris 通过维护小写名称到远程系统中实际名称的映射,能够查询非小写的数据库和表
71+
当 `lower_case_table_names` 设置为 `true` 时,Doris 通过维护小写名称到远程系统中实际名称的映射,能够查询非小写的数据库和表以及列
7272

7373
**注意:**
7474

@@ -78,9 +78,9 @@ PROPERTIES ("key"="value", ...)
7878

7979
对于其他数据库,仍需要在查询时指定真实的库名和表名。
8080

81-
2. 在 Doris 2.0.3 及之后的版本,对所有的数据库都有效,在查询时,会将所有的库名和表名转换为真实的名称,再去查询,如果是从老版本升级到 2.0.3 ,需要 `Refresh <catalog_name>` 才能生效。
81+
2. 在 Doris 2.0.3 及之后的版本,对所有的数据库都有效,在查询时,会将所有的库名和表名以及列名转换为真实的名称,再去查询,如果是从老版本升级到 2.0.3 ,需要 `Refresh <catalog_name>` 才能生效。
8282

83-
但是,如果数据库或者表名只有大小写不同,例如 `Doris` 和 `doris`,则 Doris 由于歧义而无法查询它们。
83+
但是,如果库名、表名或列名只有大小写不同,例如 `Doris` 和 `doris`,则 Doris 由于歧义而无法查询它们。
8484

8585
3. 当 FE 参数的 `lower_case_table_names` 设置为 `1` 或 `2` 时,JDBC Catalog 的 `lower_case_table_names` 参数必须设置为 `true`。如果 FE 参数的 `lower_case_table_names` 设置为 `0`,则 JDBC Catalog 的参数可以为 `true` 或 `false`,默认为 `false`。这确保了 Doris 在处理内部和外部表配置时的一致性和可预测性。
8686

@@ -114,7 +114,7 @@ select * from mysql_catalog.mysql_database.mysql_table where k1 > 1000 and k3 ='
114114

115115
1. 当执行类似于 `where dt = '2022-01-01'` 这样的查询时,Doris 能够将这些过滤条件下推到外部数据源,从而直接在数据源层面排除不符合条件的数据,减少了不必要的数据获取和传输。这大大提高了查询性能,同时也降低了对外部数据源的负载。
116116

117-
2. 当 `enable_func_pushdown` 设置为true,会将 where 之后的函数条件也下推到外部数据源,目前仅支持 MySQL,如遇到 MySQL 不支持的函数,可以将此参数设置为 false,目前 Doris 会自动识别部分 MySQL 不支持的函数进行下推条件过滤,可通过 explain sql 查看。
117+
2. 当 `enable_func_pushdown` 设置为true,会将 where 之后的函数条件也下推到外部数据源,目前仅支持 MySQL 以及 ClickHouse,如遇到 MySQL 或 ClickHouse 不支持的函数,可以将此参数设置为 false,目前 Doris 会自动识别部分 MySQL 不支持的函数以及 CLickHouse 支持的函数进行下推条件过滤,可通过 explain sql 查看。
118118

119119
目前不会下推的函数有:
120120

@@ -123,6 +123,13 @@ select * from mysql_catalog.mysql_database.mysql_table where k1 > 1000 and k3 ='
123123
| DATE_TRUNC |
124124
| MONEY_FORMAT |
125125

126+
目前会下推的函数有:
127+
128+
| ClickHouse |
129+
|:--------------:|
130+
| FROM_UNIXTIME |
131+
| UNIX_TIMESTAMP |
132+
126133
### 行数限制
127134

128135
如果在查询中带有 limit 关键字,Doris 会将其转译成适合不同数据源的语义。

‎fe/fe-core/src/main/java/org/apache/doris/catalog/JdbcTable.java‎

Lines changed: 29 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -27,6 +27,8 @@
2727
import org.apache.doris.thrift.TTableDescriptor;
2828
import org.apache.doris.thrift.TTableType;
2929

30+
import com.fasterxml.jackson.core.type.TypeReference;
31+
import com.fasterxml.jackson.databind.ObjectMapper;
3032
import com.google.common.base.Strings;
3133
import com.google.common.collect.Maps;
3234
import lombok.Setter;
@@ -47,9 +49,12 @@
4749
public class JdbcTable extends Table {
4850
private static final Logger LOG = LogManager.getLogger(JdbcTable.class);
4951

52+
private static final ObjectMapper objectMapper = new ObjectMapper();
53+
5054
private static final String TABLE = "table";
5155
private static final String REAL_DATABASE = "real_database";
5256
private static final String REAL_TABLE = "real_table";
57+
private static final String REAL_COLUMNS = "real_columns";
5358
private static final String RESOURCE = "resource";
5459
private static final String TABLE_TYPE = "table_type";
5560
private static final String URL = "jdbc_url";
@@ -65,6 +70,7 @@ public class JdbcTable extends Table {
6570
// real name only for jdbc catalog
6671
private String realDatabaseName;
6772
private String realTableName;
73+
private Map<String, String> realColumnNames;
6874

6975
private String jdbcTypeName;
7076

@@ -110,7 +116,7 @@ public String getInsertSql(List<String> insertCols) {
110116
sb.append(getProperRealFullTableName(TABLE_TYPE_MAP.get(getTableTypeName())));
111117
sb.append("(");
112118
List<String> transformedInsertCols = insertCols.stream()
113-
.map(col -> databaseProperName(TABLE_TYPE_MAP.get(getTableTypeName()), col))
119+
.map(col -> getProperRealColumnName(TABLE_TYPE_MAP.get(getTableTypeName()), col))
114120
.collect(Collectors.toList());
115121
sb.append(String.join(",", transformedInsertCols));
116122
sb.append(")");
@@ -200,6 +206,7 @@ public void write(DataOutput out) throws IOException {
200206
serializeMap.put(CHECK_SUM, checkSum);
201207
serializeMap.put(REAL_DATABASE, realDatabaseName);
202208
serializeMap.put(REAL_TABLE, realTableName);
209+
serializeMap.put(REAL_COLUMNS, objectMapper.writeValueAsString(realColumnNames));
203210

204211
int size = (int) serializeMap.values().stream().filter(v -> {
205212
return v != null;
@@ -236,6 +243,9 @@ public void readFields(DataInput in) throws IOException {
236243
checkSum = serializeMap.get(CHECK_SUM);
237244
realDatabaseName = serializeMap.get(REAL_DATABASE);
238245
realTableName = serializeMap.get(REAL_TABLE);
246+
String realColumnNamesJson = serializeMap.get(REAL_COLUMNS);
247+
realColumnNames = objectMapper.readValue(realColumnNamesJson, new TypeReference<Map<String, String>>() {
248+
});
239249
}
240250

241251
public String getResourceName() {
@@ -263,6 +273,14 @@ public String getProperRealFullTableName(TOdbcTableType tableType) {
263273
}
264274
}
265275

276+
public String getProperRealColumnName(TOdbcTableType tableType, String columnName) {
277+
if (realColumnNames == null || realColumnNames.isEmpty() || !realColumnNames.containsKey(columnName)) {
278+
return databaseProperName(tableType, columnName);
279+
} else {
280+
return properNameWithRealName(tableType, realColumnNames.get(columnName));
281+
}
282+
}
283+
266284
public String getTableTypeName() {
267285
return jdbcTypeName;
268286
}
@@ -358,14 +376,13 @@ private void validate(Map<String, String> properties) throws DdlException {
358376
* @param wrapEnd The character(s) to be added at the end of each name component.
359377
* @param toUpperCase If true, convert the name to upper case.
360378
* @param toLowerCase If true, convert the name to lower case.
361-
* <p>
362-
* Note: If both toUpperCase and toLowerCase are true, the name will ultimately be converted to lower case.
363-
* <p>
364-
* The name is expected to be in the format of 'schemaName.tableName'. If there is no '.',
365-
* the function will treat the entire string as one name component.
366-
* If there is a '.', the function will treat the string before the first '.' as the schema name
367-
* and the string after the '.' as the table name.
368-
*
379+
* <p>
380+
* Note: If both toUpperCase and toLowerCase are true, the name will ultimately be converted to lower case.
381+
* <p>
382+
* The name is expected to be in the format of 'schemaName.tableName'. If there is no '.',
383+
* the function will treat the entire string as one name component.
384+
* If there is a '.', the function will treat the string before the first '.' as the schema name
385+
* and the string after the '.' as the table name.
369386
* @return The formatted name.
370387
*/
371388
public static String formatName(String name, String wrapStart, String wrapEnd, boolean toUpperCase,
@@ -386,18 +403,18 @@ public static String formatName(String name, String wrapStart, String wrapEnd, b
386403

387404
/**
388405
* Formats a database name according to the database type.
389-
*
406+
* <p>
390407
* Rules:
391408
* - MYSQL, OCEANBASE: Wrap with backticks (`), case unchanged. Example: mySchema.myTable -> `mySchema.myTable`
392409
* - SQLSERVER: Wrap with square brackets ([]), case unchanged. Example: mySchema.myTable -> [mySchema].[myTable]
393410
* - POSTGRESQL, CLICKHOUSE, TRINO, OCEANBASE_ORACLE, SAP_HANA: Wrap with double quotes ("), case unchanged.
394-
* Example: mySchema.myTable -> "mySchema"."myTable"
411+
* Example: mySchema.myTable -> "mySchema"."myTable"
395412
* - ORACLE: Wrap with double quotes ("), convert to upper case. Example: mySchema.myTable -> "MYSCHEMA"."MYTABLE"
396413
* For other types, the name is returned as is.
397414
*
398415
* @param tableType The database type.
399416
* @param name The name to be formatted, expected in 'schemaName.tableName' format. If no '.', treats entire string
400-
* as one name component. If '.', treats string before first '.' as schema name and after as table name.
417+
* as one name component. If '.', treats string before first '.' as schema name and after as table name.
401418
* @return The formatted name.
402419
*/
403420
public static String databaseProperName(TOdbcTableType tableType, String name) {

‎fe/fe-core/src/main/java/org/apache/doris/catalog/external/JdbcExternalTable.java‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -91,6 +91,8 @@ private JdbcTable toJdbcTable() {
9191
jdbcTable.setRealDatabaseName(((JdbcExternalCatalog) catalog).getJdbcClient().getRealDatabaseName(this.dbName));
9292
jdbcTable.setRealTableName(
9393
((JdbcExternalCatalog) catalog).getJdbcClient().getRealTableName(this.dbName, this.name));
94+
jdbcTable.setRealColumnNames(((JdbcExternalCatalog) catalog).getJdbcClient().getRealColumnNames(this.dbName,
95+
this.name));
9496
jdbcTable.setJdbcTypeName(jdbcCatalog.getDatabaseTypeName());
9597
jdbcTable.setJdbcUrl(jdbcCatalog.getJdbcUrl());
9698
jdbcTable.setJdbcUser(jdbcCatalog.getJdbcUser());

0 commit comments

Comments
 (0)