anuraags2719 opened a new issue, #23055:
URL: https://github.com/apache/airflow/issues/23055
### Apache Airflow version
main (development)
### What happened
Hello team,
We have a strange behaviour while working with airflow JDBC hook.
We are using airflow `1.10.11`
Basically, we need to run SQL queries on Presto, and for this for some of
the use cases, we use the JDBC hook by importing as - `from
airflow.hooks.jdbc_hook import JdbcHook`
This works fine for most of the queries, but gave error for some.
The queries are syntactically perfect as it's been run using the Presto
Hook, and it runs just fine.
But while using the JDBC hook, we get a generic message - Error executing
query
Below is the stack trace -
```
Traceback (most recent call last):
File
"/home/ubuntu/.venv/prism/lib/python3.6/site-packages/jaydebeapi/__init__.py",
line 534, in execute
is_rs = self._prep.execute()
jpype._jclass.SQLException: java.sql.SQLException: Error executing query
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File
"/home/ubuntu/.venv/prism/lib/python3.6/site-packages/jaydebeapi/__init__.py",
line 536, in execute
_handle_sql_exception()
File
"/home/ubuntu/.venv/prism/lib/python3.6/site-packages/jaydebeapi/__init__.py",
line 165, in _handle_sql_exception_jpype
reraise(exc_type, exc_info[1], exc_info[2])
File
"/home/ubuntu/.venv/prism/lib/python3.6/site-packages/jaydebeapi/__init__.py",
line 57, in reraise
raise value.with_traceback(tb)
File
"/home/ubuntu/.venv/prism/lib/python3.6/site-packages/jaydebeapi/__init__.py",
line 534, in execute
is_rs = self._prep.execute()
jaydebeapi.DatabaseError: java.sql.SQLException: Error executing query
>>> curs.fetchall()
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File
"/home/ubuntu/.venv/prism/lib/python3.6/site-packages/jaydebeapi/__init__.py",
line 593, in fetchall
row = self.fetchone()
File
"/home/ubuntu/.venv/prism/lib/python3.6/site-packages/jaydebeapi/__init__.py",
line 558, in fetchone
raise Error()
jaydebeapi.Error
>>> curs.close()
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File
"/home/ubuntu/.venv/prism/lib/python3.6/site-packages/jaydebeapi/__init__.py",
line 505, in close
self._close_last()
File
"/home/ubuntu/.venv/prism/lib/python3.6/site-packages/jaydebeapi/__init__.py",
line 515, in _close_last
self._prep.close()
jpype._jclass.SQLException: java.sql.SQLException: Error executing query
```
Not able to understand why exactly getting this.
So tried to debug the query and found we were able to run the query by -
`commenting out a few case when statements from the above query.`
Strangely, this query is running without any issues. Not sure what exactly
is the issue.
We create the JDBC connection as -
```
from airflow.hooks.jdbc_hook import JdbcHook
hook = JdbcHook("JDBC_connection_name")
hook.run(sql_query, True)
```
Would really appreciate if someone could help fix this issue.
Thanks!
-Anuraag
### What you think should happen instead
It should return the query result. As the same query runs with PrestoHook/SQL
### How to reproduce
This is the query. When we uncomment the case when statements, it gives
error -
```select
supplier_id as supplier_id_1,
current_date as dt,
KAM, manager,
catalog_id as catalog_id_1,
campaign_id as campaign_id_1,
days_elapsed,
type_of_campaign,
campaign_type,
budget,
budget_added,
budget_utilised,
utilisation,
base_cpc,
current_cpc,
last_cpc_modified,
ROI,
l_14_Roi,
'https://supplier.meesho.com/panel/v2/new/'||identifier||'/advertisement/details/'||cast(a.campaign_id
as varchar)||'?campaign-id='||cast(a.campaign_id as varchar) as campaign_link,
Action,
case when action in ('Increase Bid','Decrease Bid','STOP: Move Budget to
another campaign') then supplier_id end as supplier_id,
case when action in ('Increase Bid','Decrease Bid','STOP: Move Budget to
another campaign') then campaign_id end as campaign_id,
case when action in ('Increase Bid','Decrease Bid','STOP: Move Budget to
another campaign') then catalog_id end as catalog_id,
case when action in ('Increase Bid','Decrease Bid') then ' '
when action in ('STOP: Move Budget to another campaign') then
'PAUSED' END as catalog_status,
case when action in ('Increase Bid','Decrease Bid') then
cast(round(new_cpc,2) as varchar)
end as cpc
FROM
(select
*
,
case when action='Increase Budget' then round(budget*1.25,0) end as
new_budget,
case
when action='Increase Bid' and ROI>6 and ROI<=8 and utilisation>=0 and
utilisation<10 then least(current_cpc*ROI/6.0, 1.15* current_cpc)
when action='Increase Bid' and ROI>6 and ROI<=8 and utilisation>=10 and
utilisation<20 then least(current_cpc*ROI/6.0, 1.15* current_cpc)
when action='Increase Bid' and ROI>6 and ROI<=8 and utilisation>=20 and
utilisation<30 then least(current_cpc*ROI/6.0, 1.15* current_cpc)
when action='Increase Bid' and ROI>6 and ROI<=8 and utilisation>=30 and
utilisation<40 then least(current_cpc*ROI/6.0, 1.15* current_cpc)
when action='Increase Bid' and ROI>6 and ROI<=8 and utilisation>=40 and
utilisation<50 then least(current_cpc*ROI/6.0, 1.15* current_cpc)
when action='Increase Bid' and ROI>6 and ROI<=8 and utilisation>=50 and
utilisation<60 then least(current_cpc*ROI/6.0, 1.10* current_cpc)
when action='Increase Bid' and ROI>6 and ROI<=8 and utilisation>=60 and
utilisation<70 then least(current_cpc*ROI/6.0, 1.05* current_cpc)
when action='Increase Bid' and ROI>6 and ROI<=8 and utilisation>=70 and
utilisation<80 then least(current_cpc*ROI/6.0, 1.05* current_cpc)
when action='Increase Bid' and ROI>6 and ROI<=8 and utilisation>=80 and
utilisation<90 then least(current_cpc*ROI/6.0, 1.05* current_cpc)
-- when action='Increase Bid' and ROI>8 and ROI<12 and utilisation>=0
and utilisation<10 then least(current_cpc*ROI/8.0, 1.30* current_cpc)
-- when action='Increase Bid' and ROI>8 and ROI<12 and utilisation>=10
and utilisation<20 then least(current_cpc*ROI/8.0, 1.30* current_cpc)
-- when action='Increase Bid' and ROI>8 and ROI<12 and utilisation>=20
and utilisation<30 then least(current_cpc*ROI/8.0, 1.30* current_cpc)
-- when action='Increase Bid' and ROI>8 and ROI<12 and utilisation>=30
and utilisation<40 then least(current_cpc*ROI/8.0, 1.25* current_cpc)
-- when action='Increase Bid' and ROI>8 and ROI<12 and utilisation>=40
and utilisation<50 then least(current_cpc*ROI/8.0, 1.25* current_cpc)
-- when action='Increase Bid' and ROI>8 and ROI<12 and utilisation>=50
and utilisation<60 then least(current_cpc*ROI/8.0, 1.20* current_cpc)
-- when action='Increase Bid' and ROI>8 and ROI<12 and utilisation>=60
and utilisation<70 then least(current_cpc*ROI/8.0, 1.20* current_cpc)
-- when action='Increase Bid' and ROI>8 and ROI<12 and utilisation>=70
and utilisation<80 then least(current_cpc*ROI/8.0, 1.15* current_cpc)
-- when action='Increase Bid' and ROI>8 and ROI<12 and utilisation>=80
and utilisation<90 then least(current_cpc*ROI/8.0, 1.10* current_cpc)
-- when action='Increase Bid' and ROI>=12 and ROI<16 and utilisation>=0
and utilisation<10 then least(current_cpc*ROI/10.0, 1.30* current_cpc)
-- when action='Increase Bid' and ROI>=12 and ROI<16 and utilisation>=10
and utilisation<20 then least(current_cpc*ROI/10.0, 1.30* current_cpc)
-- when action='Increase Bid' and ROI>=12 and ROI<16 and utilisation>=20
and utilisation<30 then least(current_cpc*ROI/10.0, 1.30* current_cpc)
-- when action='Increase Bid' and ROI>=12 and ROI<16 and utilisation>=30
and utilisation<40 then least(current_cpc*ROI/10.0, 1.25* current_cpc)
-- when action='Increase Bid' and ROI>=12 and ROI<16 and utilisation>=40
and utilisation<50 then least(current_cpc*ROI/10.0, 1.25* current_cpc)
-- when action='Increase Bid' and ROI>=12 and ROI<16 and utilisation>=50
and utilisation<60 then least(current_cpc*ROI/10.0, 1.20* current_cpc)
-- when action='Increase Bid' and ROI>=12 and ROI<16 and utilisation>=60
and utilisation<70 then least(current_cpc*ROI/10.0, 1.20* current_cpc)
-- when action='Increase Bid' and ROI>=12 and ROI<16 and utilisation>=70
and utilisation<80 then least(current_cpc*ROI/10.0, 1.15* current_cpc)
-- when action='Increase Bid' and ROI>=12 and ROI<16 and utilisation>=80
and utilisation<90 then least(current_cpc*ROI/10.0, 1.10* current_cpc)
-- when action='Increase Bid' and ROI>=16 and utilisation>=0 and
utilisation<10 then least(current_cpc*ROI/12.0, 1.30* current_cpc)
-- when action='Increase Bid' and ROI>=16 and utilisation>=10 and
utilisation<20 then least(current_cpc*ROI/12.0, 1.30* current_cpc)
-- when action='Increase Bid' and ROI>=16 and utilisation>=20 and
utilisation<30 then least(current_cpc*ROI/12.0, 1.30* current_cpc)
-- when action='Increase Bid' and ROI>=16 and utilisation>=30 and
utilisation<40 then least(current_cpc*ROI/12.0, 1.25* current_cpc)
-- when action='Increase Bid' and ROI>=16 and utilisation>=40 and
utilisation<50 then least(current_cpc*ROI/12.0, 1.25* current_cpc)
-- when action='Increase Bid' and ROI>=16 and utilisation>=50 and
utilisation<60 then least(current_cpc*ROI/12.0, 1.20* current_cpc)
-- when action='Increase Bid' and ROI>=16 and utilisation>=60 and
utilisation<70 then least(current_cpc*ROI/12.0, 1.20* current_cpc)
-- when action='Increase Bid' and ROI>=16 and utilisation>=70 and
utilisation<80 then least(current_cpc*ROI/12.0, 1.15* current_cpc)
-- when action='Increase Bid' and ROI>=16 and utilisation>=80 and
utilisation<90 then least(current_cpc*ROI/12.0, 1.10* current_cpc)
when action='Decrease Bid' and ROI<=6 and utilisation>=80 and
utilisation<90 then greatest(0.95*current_cpc, base_cpc)
when action='Decrease Bid' and ROI<=6 and utilisation>=90 and
utilisation<100 then greatest(0.95*current_cpc, base_cpc)
when action='Decrease Bid' and ROI<=6 and utilisation>=100 then
greatest(0.95*current_cpc, base_cpc) end as new_cpc
from
(select *,
case when days_elapsed>=3 and utilisation>=90 and ROI>=8 then 'Increase
Budget'
when days_elapsed>=3 and ((ROI<3.5 and ROI < l_14_Roi)or (roi<
l_14_Roi*0.4)) then 'STOP: Move Budget to another campaign'
when days_elapsed>=3 and utilisation>=80 and ROI<6 and ROI>=3.5 and
current_cpc>base_cpc then 'Decrease Bid'
when days_elapsed>=3 and utilisation<=90 and ROI>=6 then 'Increase Bid'
end as Action
from
(
select
supplier_id,
KAM,manager,
catalog_id,
campaign_id,
days_elapsed,
type_of_campaign,
campaign_type,
budget,
budget_added_1 as budget_added,
budget_utilised_1 as budget_utilised,
budget_utilised_1*100.0/budget_added_1 as utilisation,
base_cpc,
current_cpc,
last_cpc_modified,
--last_budget_modified,
revenue_2*1.0/budget_utilised_2 as ROI,
l_14_Roi
from
(
select a.supplier_id,
b.catalog_id,
b.campaign_id,
COALESCE(day(current_date- date(cast(last_dt as TIMESTAMP))), days_elapsed)
as days_elapsed,
type_of_campaign,
campaign_type,
A.budget,
case when campaign_type='DAILY_BUDGET' THEN sum(case when
t3.dt>=COALESCE(date(cast(last_dt as TIMESTAMP)), date(start_ts)) then
budget_added end)
WHEN campaign_type='TOTAL_BUDGET' THEN SUM(budget_added) end as
budget_added_1,
case when campaign_type='DAILY_BUDGET' THEN sum(case when
t1.dt>=COALESCE(date(cast(last_dt as TIMESTAMP)), date(start_ts)) then
budget_utilised end)
when campaign_type= 'TOTAL_BUDGET' then sum(budget_utilised)
end as
budget_utilised_1,
sum(case when t1.dt>=COALESCE(date(cast(last_dt as TIMESTAMP)),
date(start_ts)) then budget_utilised end) as budget_utilised_2,
sum(case when t1.dt>=COALESCE(date(cast(last_dt as TIMESTAMP)),
date(start_ts)) then revenue end) as revenue_2,
base_cpc,
current_cpc,
COALESCE(split_part(last_dt,'.',1),'NA') as last_cpc_modified,
l_14_Roi,
sst_poc as KAM,manager
from
(
select id as campaign_id, supplier_id,date(start_ts) start_ts,
COALESCE(date(end_ts),current_date)end_ts, campaign_type, budget,
day(CURRENT_DATE- date(start_ts))days_elapsed
from silver.advertisement__advertisement_campaign
where (lower(name) like '%kam%%support%') and status in
('LIVE', 'PAUSED')
and supplier_id in (select supplier_id from
mercury.gdrive__ads_supplier_spend_commitment group by 1 )
)a
join(select supplier_id, sst_poc,manager from
mercury.gdrive__ads_supplier_spend_commitment where current_date between
date(startdate)
and date(enddate ))c on a.supplier_id=c.supplier_id
left join (select campaign_id, catalog_id from
silver.advertisement__advertisement_campaign_catalog) b on
a.campaign_id=b.campaign_id
left join (select id, identifier from silver.supply__suppliers) e on e.id =
c.supplier_id
LEFT JOIN
(
select dt,catalog_id, campaign_id,type_of_campaign,
sum(budget_added)budget_added from gold.ads_mma_daily_budget
group by 1,2,3,4
)t3 on t3.campaign_id=b.campaign_id and t3.catalog_id=b.catalog_id and t3.dt
>=start_ts and t3.dt<=end_ts
left join
(
SELECT dt,catalog_id, campaign_id, sum(ad_spend)budget_utilised,
sum(revenue)revenue
from gold.supplier_campaign_day_level_performance
where LOWER(who_created)='kam'
group by 1,2,3
) t1 on t1.campaign_id=b.campaign_id and t1.catalog_id=b.catalog_id and
t1.dt=t3.dt
LEFT JOIN
(select catalog_id, min(cpc) base_cpc from
(select catalog_id,cast(ltrim(e.sscat_id) as int) as sscat_id
from
(select catalog_id,
case
when sscat_id like '[%]' then split_part(split_part(sscat_id,'[',2),']',1)
when sscat_id like '[%' then split_part(sscat_id,'[',2)
when sscat_id like '%]' then split_part(sscat_id,']',1)
else sscat_id end as sscat_id
from
(
select catalog_id, sscat_id, typeof(sscat_id)
from (select catalog_id, sscat_id_list from
(select *, row_number() over (partition by catalog_id order by created_at
desc)rnk
from
silver.advertisement__advertisement_campaign_catalog )
where rnk=1
group by 1,2)a
cross join unnest(split(sscat_id_list,',')) as t(sscat_id)
group by 1,2))e
)e
left join(select sscat_id, cpc from
silver.advertisement__advertisement_sscat_campaign_config group by 1,2) c
on e.sscat_id = c.sscat_id
group by 1)t4 on t4.catalog_id=b.catalog_id
left join
(
select * from
(select *, row_number() over (partition by catalog_id, campaign_id order by
dt desc) rk
from
(
select *, lead(dt) over (partition by catalog_id, campaign_id order by dt
desc)last_dt, lead(current_cpc) over (partition by catalog_id, campaign_id
order by dt desc) last_cpc
from
(select catalog_id, campaign_id, cpc as current_cpc, max(processed_at) dt
from silver.ad__interaction_events
group by 1,2,3
order by dt desc)
order by dt desc)
)where rk=1
)t5 on t5.catalog_id=b.catalog_id and t5.campaign_id=b.campaign_id
left join
(
select supplier_id, revenue*1.0/ad_spend as l_14_roi
from
(
select supplier_id,
sum(case when dt between current_date-interval '14' day and
current_date-interval '1'day then ad_spend end) as ad_spend,
sum(case when dt between current_date-interval '14' day and
current_date-interval '1'day then revenue end) as revenue
from
gold.supplier_campaign_day_level_performance
where who_created='KAM'
group by 1
)
order by supplier_id
)t6 on a.supplier_id=t6.supplier_id
where current_cpc !=0 and budget_utilised>0
group by 1,2,3,4,5,6,7,12,13,14,15,16,17
)
ORDER BY SUPPLIER_ID, campaign_id
)
)
order by SUPPLIER_ID
)a
left join (select id, identifier from silver.supply__suppliers group by
1,2)m on a.supplier_id=m.id
order by supplier_id_1, campaign_id_1, catalog_id_1
limit 1```
### Operating System
Ubuntu 18.04.3 LTS
### Versions of Apache Airflow Providers
`airflow 1.10.11`
### Deployment
Virtualenv installation
### Deployment details
_No response_
### Anything else
_No response_
### Are you willing to submit PR?
- [ ] Yes I am willing to submit a PR!
### Code of Conduct
- [X] I agree to follow this project's [Code of
Conduct](https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md)
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
