You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@dolphinscheduler.apache.org by GitBox <gi...@apache.org> on 2022/04/01 12:08:18 UTC
[GitHub] [dolphinscheduler] vankizou opened a new issue #9320: [Bug] [task sql] execute a sql with dynamic table name
vankizou opened a new issue #9320:
URL: https://github.com/apache/dolphinscheduler/issues/9320
### Search before asking
- [X] I had searched in the [issues](https://github.com/apache/dolphinscheduler/issues?q=is%3Aissue) and found no similar issues.
### What happened
Hi. I have a problem when I submit a SQL task. My SQL have a dynamic table name. Because it's a partitioned table by postgresql.
The task definition like below:
The declare SQL is:
```sql
WITH
r00 AS (
SELECT coalesce(rb_build_agg(cast(id as int)), rb_build('{}')) AS id_bitmap
FROM ads.t_dmp_test_user
WHERE (id not in (1,2,3) AND (part_day BETWEEN TO_CHAR(CURRENT_DATE-3000, 'YYYY-MM-DD') AND '2022-03-30' OR jkx_userid is not null))
)
INSERT INTO ads.t_dmp_test_tag_${abc} (tag_name,tag_id,tag_value,bit_list,etl_time,part_day)
SELECT '付费用户',123,'七日内付费用户',r00.id_bitmap,CURRENT_TIMESTAMP,TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD') FROM r00
ON CONFLICT(tag_name, tag_value, part_day)
DO UPDATE SET (tag_name,tag_id,tag_value,bit_list,etl_time,part_day)=ROW(excluded.*)
```
But I got a error when the task executed. The error info like below:
The error message:
```
[ERROR] 2022-04-01 19:27:05.672 [TaskLogInfo- - [taskAppId=TASK-5037179531040_7-267-584]] - execute sql error: ERROR: relation "ads.t_dmp_test_tag_$1" does not exist
Position: 269
[ERROR] 2022-04-01 19:27:05.672 [TaskLogInfo- - [taskAppId=TASK-5037179531040_7-267-584]] - sql task error: org.postgresql.util.PSQLException: ERROR: relation "ads.t_dmp_test_tag_$1" does not exist
Position: 269
```
### What you expected to happen
I hope SQL task can support execute a sql with dynamic table name.
### How to reproduce
It can distinguish the different SQL part, such as table name, where conditions etc.
### Anything else
_No response_
### Version
2.0.3
### Are you willing to submit PR?
- [X] Yes I am willing to submit a PR!
### Code of Conduct
- [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@dolphinscheduler.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [dolphinscheduler] github-actions[bot] commented on issue #9320: [Bug] [task sql] execute a sql with dynamic table name
Posted by GitBox <gi...@apache.org>.
github-actions[bot] commented on issue #9320:
URL: https://github.com/apache/dolphinscheduler/issues/9320#issuecomment-1085829598
### Search before asking
- [X] I had searched in the [issues](https://github.com/apache/dolphinscheduler/issues?q=is%3Aissue) and found no similar issues.
### What happened
Hi. I have a problem when I submit a SQL task. My SQL have a dynamic table name. Because it's a partitioned table by postgresql.
The task definition like below:
The declare SQL is:
```sql
WITH
r00 AS (
SELECT coalesce(rb_build_agg(cast(id as int)), rb_build('{}')) AS id_bitmap
FROM ads.t_dmp_test_user
WHERE (id not in (1,2,3) AND (part_day BETWEEN TO_CHAR(CURRENT_DATE-3000, 'YYYY-MM-DD') AND '2022-03-30' OR jkx_userid is not null))
)
INSERT INTO ads.t_dmp_test_tag_${abc} (tag_name,tag_id,tag_value,bit_list,etl_time,part_day)
SELECT '付费用户',123,'七日内付费用户',r00.id_bitmap,CURRENT_TIMESTAMP,TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD') FROM r00
ON CONFLICT(tag_name, tag_value, part_day)
DO UPDATE SET (tag_name,tag_id,tag_value,bit_list,etl_time,part_day)=ROW(excluded.*)
```
But I got a error when the task executed. The error info like below:
The error message:
```
[ERROR] 2022-04-01 19:27:05.672 [TaskLogInfo- - [taskAppId=TASK-5037179531040_7-267-584]] - execute sql error: ERROR: relation "ads.t_dmp_test_tag_$1" does not exist
Position: 269
[ERROR] 2022-04-01 19:27:05.672 [TaskLogInfo- - [taskAppId=TASK-5037179531040_7-267-584]] - sql task error: org.postgresql.util.PSQLException: ERROR: relation "ads.t_dmp_test_tag_$1" does not exist
Position: 269
```
### What you expected to happen
I hope SQL task can support execute a sql with dynamic table name.
### How to reproduce
It can distinguish the different SQL part, such as table name, where conditions etc.
### Anything else
_No response_
### Version
2.0.3
### Are you willing to submit PR?
- [X] Yes I am willing to submit a PR!
### Code of Conduct
- [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@dolphinscheduler.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [dolphinscheduler] github-actions[bot] commented on issue #9320: [Bug] [task sql] execute a sql with dynamic table name
Posted by GitBox <gi...@apache.org>.
github-actions[bot] commented on issue #9320:
URL: https://github.com/apache/dolphinscheduler/issues/9320#issuecomment-1085829796
Hi:
* Thank you for your feedback, we have received your issue, Please wait patiently for a reply.
* In order for us to understand your request as soon as possible, please provide detailed information、version or pictures.
* If you haven't received a reply for a long time, you can subscribe to the developer's email，Mail subscription steps reference https://dolphinscheduler.apache.org/en-us/community/development/subscribe.html ,Then write the issue URL in the email content and send question to dev@dolphinscheduler.apache.org.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@dolphinscheduler.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [dolphinscheduler] caishunfeng commented on issue #9320: [Bug] [task sql] execute a sql with dynamic table name
Posted by GitBox <gi...@apache.org>.
caishunfeng commented on issue #9320:
URL: https://github.com/apache/dolphinscheduler/issues/9320#issuecomment-1086475036
I think you should change the OUT type to IN type and have a try again.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@dolphinscheduler.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org