Skip to content

[feat](jdbc) support custom function rules in catalog properties (#51471) #51918

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Merged
dataroaring merged 3 commits into from
Jun 20, 2025
Merged

[feat](jdbc) support custom function rules in catalog properties (#51471) #51918

dataroaring merged 3 commits into from
Jun 20, 2025

Conversation

@morningman
Copy link
Contributor

@morningman morningman commented Jun 19, 2025

bp #51471

@morningman morningman requested a review from dataroaring as a code owner June 19, 2025 04:33
@morningman
Copy link
Contributor Author

morningman commented Jun 19, 2025

run buildall

@Thearas
Copy link
Contributor

Thearas commented Jun 19, 2025

Thank you for your contribution to Apache Doris.
Don't know what should be done next? See How to process your PR.

Please clearly describe your PR:

  1. What problem was fixed (it's best to include specific error reporting information). How it was fixed.
  2. Which behaviors were modified. What was the previous behavior, what is it now, why was it modified, and what possible impacts might there be.
  3. What features were added. Why was this function added?
  4. Which code was refactored and why was this part of the code refactored?
  5. Which functions were optimized and what is the difference before and after the optimization?

@morningman
Copy link
Contributor Author

morningman commented Jun 19, 2025

run buildall

morningman and others added 3 commits June 19, 2025 20:39
@morningman
[feat](jdbc) support custom function rules in catalog properties (apa…
6409875 
…che#51471)

Add new jdbc catalog property: `function_rules`.
This property will enable user to customize which function can be pushed
down to jdbc datasource,
and which function should be rewrote to function in jdbc datasource:

eg:
```
create catalog jdbc properties (
    ...
    'function_rules' = '{"pushdown" : {"supported": ["to_date"], "unsupported" : ["abs"]}, "rewrite" : {"to_date" : "date2"}}'
)
```
The value is a json format config:
- `pushdown`: the value of `pushdown` is a map, the `supported` array
indicate the function that can be pushed down to datasource. the
`unsupported` array indicate the function that can not.
- `rewrite`: the value of `rewrite` is map, if the function can be
pushed down to datasource, it will be renamed to the value in rewrite
map.

eg:
```
select * from jdbc_table where abs(k1) > 0 and to_date("k2") = "2022-10-10"
```
will generate sql:
```
select * from jdbc_table where date2("k2") = "2022-10-10"
```

You can see the `abs()` is not pushed down, and `to_date()` is rewrote
to `date2()`.

-------
This change is compatible with previous version.
Previously, there are some hard coded function rules, and also a FE
config `jdbc_mysql_unsupported_pushdown_functions`.

Doris will merge the user specified `function_rules` and the above
rules.
@morningman
1
150ebe4
@morningman
1
1de9133
@morningman
Copy link
Contributor Author

morningman commented Jun 19, 2025

run buildall

@doris-robot
Copy link

doris-robot commented Jun 19, 2025

TPC-H: Total hot run time: 40121 ms 
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpch-tools
Tpch sf100 test result on commit 1de9133d0c926e649fbf7c621b2a9011f1ac8ba6, data reload: false

------ Round 1 ----------------------------------
q1	17611	7209	6637	6637
q2	2064	166	161	161
q3	10634	1166	1195	1166
q4	10218	759	762	759
q5	7737	2920	2867	2867
q6	217	134	134	134
q7	970	643	602	602
q8	9348	1937	2036	1937
q9	6622	6452	6402	6402
q10	7036	2318	2340	2318
q11	462	268	262	262
q12	514	224	220	220
q13	17785	3023	2997	2997
q14	239	211	210	210
q15	499	477	463	463
q16	487	373	374	373
q17	991	567	584	567
q18	7285	6697	6744	6697
q19	1414	1139	1037	1037
q20	455	196	201	196
q21	4094	3131	3249	3131
q22	1119	985	1019	985
Total cold run time: 107801 ms
Total hot run time: 40121 ms

----- Round 2, with runtime_filter_mode=off -----
q1	6843	6662	6603	6603
q2	328	245	228	228
q3	2948	2823	2797	2797
q4	1941	1755	1687	1687
q5	5450	5495	5513	5495
q6	209	127	129	127
q7	2105	1746	1698	1698
q8	3267	3424	3460	3424
q9	8565	8622	8647	8622
q10	3489	3462	3421	3421
q11	594	491	488	488
q12	779	574	569	569
q13	7802	3019	3029	3019
q14	304	259	263	259
q15	509	465	462	462
q16	463	441	424	424
q17	1800	1584	1580	1580
q18	7707	7377	7382	7377
q19	1647	1525	1406	1406
q20	2044	1818	1796	1796
q21	5115	5009	4933	4933
q22	1095	1029	994	994
Total cold run time: 65004 ms
Total hot run time: 57409 ms

@doris-robot
Copy link

doris-robot commented Jun 19, 2025

TPC-DS: Total hot run time: 189550 ms 
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpcds-tools
TPC-DS sf100 test result on commit 1de9133d0c926e649fbf7c621b2a9011f1ac8ba6, data reload: false

query1	958	367	362	362
query2	5646	1902	1824	1824
query3	6616	216	211	211
query4	33964	23514	23219	23219
query5	4087	485	466	466
query6	269	187	183	183
query7	4606	313	305	305
query8	293	235	222	222
query9	9501	2584	2559	2559
query10	466	284	264	264
query11	18043	15289	15248	15248
query12	158	103	103	103
query13	1651	429	417	417
query14	10105	6850	6821	6821
query15	250	173	183	173
query16	7752	408	477	408
query17	1593	553	544	544
query18	2015	300	314	300
query19	334	151	150	150
query20	114	106	113	106
query21	208	103	102	102
query22	4407	4322	4268	4268
query23	34949	33440	33069	33069
query24	11719	2914	2872	2872
query25	623	385	399	385
query26	1282	169	167	167
query27	2928	349	335	335
query28	7811	2094	2088	2088
query29	755	428	413	413
query30	289	156	155	155
query31	998	799	800	799
query32	68	59	59	59
query33	743	296	292	292
query34	890	510	499	499
query35	862	711	702	702
query36	1109	936	927	927
query37	113	66	68	66
query38	3915	3836	3818	3818
query39	1489	1422	1422	1422
query40	241	100	100	100
query41	52	54	47	47
query42	114	103	99	99
query43	512	494	476	476
query44	1337	798	792	792
query45	185	168	172	168
query46	1124	725	699	699
query47	1956	1828	1832	1828
query48	455	388	381	381
query49	942	391	399	391
query50	833	413	411	411
query51	7207	7091	7103	7091
query52	101	92	87	87
query53	263	191	189	189
query54	993	472	469	469
query55	79	84	80	80
query56	285	270	248	248
query57	1284	1164	1149	1149
query58	249	223	225	223
query59	2992	2789	2823	2789
query60	305	273	261	261
query61	150	133	129	129
query62	858	669	674	669
query63	230	195	194	194
query64	3792	772	737	737
query65	3287	3221	3206	3206
query66	1024	318	330	318
query67	15838	15462	15547	15462
query68	4920	579	579	579
query69	414	278	277	277
query70	1173	1079	1120	1079
query71	341	269	272	269
query72	5949	4295	4043	4043
query73	754	353	348	348
query74	10025	9155	8942	8942
query75	3363	2664	2650	2650
query76	2135	1161	1145	1145
query77	396	269	273	269
query78	10621	9671	9536	9536
query79	1847	605	608	605
query80	997	443	439	439
query81	557	224	222	222
query82	917	89	88	88
query83	231	148	147	147
query84	235	81	79	79
query85	1280	317	315	315
query86	435	307	293	293
query87	4329	4249	4233	4233
query88	3577	2359	2323	2323
query89	403	298	300	298
query90	1876	196	189	189
query91	180	151	148	148
query92	75	52	52	52
query93	1321	557	549	549
query94	726	292	300	292
query95	353	255	254	254
query96	600	277	282	277
query97	3304	3158	3111	3111
query98	219	198	198	198
query99	1518	1299	1285	1285
Total cold run time: 295338 ms
Total hot run time: 189550 ms

@doris-robot
Copy link

doris-robot commented Jun 19, 2025

ClickBench: Total hot run time: 29.3 s 
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/clickbench-tools
ClickBench test result on commit 1de9133d0c926e649fbf7c621b2a9011f1ac8ba6, data reload: false

query1	0.03	0.03	0.03
query2	0.06	0.03	0.03
query3	0.23	0.06	0.06
query4	1.62	0.10	0.10
query5	0.54	0.50	0.52
query6	1.14	0.72	0.73
query7	0.02	0.01	0.01
query8	0.05	0.03	0.03
query9	0.56	0.50	0.51
query10	0.55	0.55	0.54
query11	0.14	0.10	0.10
query12	0.16	0.12	0.12
query13	0.60	0.59	0.60
query14	0.76	0.81	0.81
query15	0.85	0.83	0.82
query16	0.38	0.36	0.38
query17	1.05	1.02	1.00
query18	0.24	0.22	0.21
query19	1.86	1.75	1.88
query20	0.01	0.01	0.01
query21	15.40	0.60	0.57
query22	2.28	2.13	1.48
query23	16.95	0.98	0.76
query24	3.51	1.79	0.46
query25	0.37	0.10	0.05
query26	0.43	0.14	0.15
query27	0.05	0.04	0.04
query28	10.06	0.52	0.51
query29	12.56	3.23	3.24
query30	0.25	0.07	0.06
query31	2.86	0.39	0.39
query32	3.23	0.48	0.46
query33	3.02	2.97	3.00
query34	17.10	4.53	4.52
query35	4.57	4.60	4.61
query36	0.65	0.49	0.47
query37	0.08	0.06	0.06
query38	0.04	0.03	0.04
query39	0.04	0.02	0.03
query40	0.17	0.13	0.12
query41	0.07	0.02	0.02
query42	0.03	0.02	0.03
query43	0.04	0.03	0.03
Total cold run time: 104.61 s
Total hot run time: 29.3 s

dataroaring
dataroaring approved these changes Jun 20, 2025
View reviewed changes
Copy link
Contributor

@dataroaring dataroaring left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

LGTM

@dataroaring dataroaring merged commit 459632e into apache:branch-3.0 Jun 20, 2025
22 of 24 checks passed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

@dataroaring dataroaring dataroaring approved these changes

Assignees

No one assigned

Labels

None yet

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

4 participants

@morningman @Thearas @doris-robot @dataroaring