sqlifyStr isn't working for Redshift
Closed this issue · 7 comments
LaizaAngrest commented
Describe the bug
sqlifyStr isn't working for Redshift.
TypeError: column.expr.column.match is not a function
112 | }
113 |
> 114 | if (column.expr?.column && column.expr?.column !== '*' && column.expr.column.match(SPECIAL_CHAR_SREGEX)) {
| ^
115 | specialCharsColumns.push(column.expr.column)
116 | }
117 | })
Database Engine
Redshift
To Reproduce
const query = `with pv as (
select
action_date,
visitor_id_v,
visit_country_name,
referer_channel_group,
email,
sgid,
mp."brand/non-brand" as is_brand
from
dwh_fact_pageviews pv
left join ppc_keywords_mapping mp using (campaign_keyword)
)
select is_brand as "b/nb" from pv where mp."brand/non-brand" = 'brand'`
const sqlParser = new SqlParser()
const parsedQuery = sqlParser.parse(query, 'redshift')
const sqlifyStr = sqlify(parsedQuery, sqlParser, 'redshift')
const expected = `WITH pv AS (SELECT action_date, visitor_id_v, visit_country_name, referer_channel_group, email, sgid, mp."brand/non-brand" AS is_brand FROM dwh_fact_pageviews AS pv LEFT JOIN ppc_keywords_mapping AS mp USING (campaign_keyword)) SELECT is_brand AS "b/nb" FROM pv WHERE mp."brand/non-brand" = 'brand'`
expect(sqlifyStr).toBe(expected)
taozhi8833998 commented
@LaizaAngrest It works in my test cases.
const query = `with pv as (
select
action_date,
visitor_id_v,
visit_country_name,
referer_channel_group,
email,
sgid,
mp."brand/non-brand" as is_brand
from
dwh_fact_pageviews pv
left join ppc_keywords_mapping mp using (campaign_keyword)
)
select is_brand as "b/nb" from pv where mp."brand/non-brand" = 'brand'`
const sqlParser = new SqlParser()
const parsedQuery = sqlParser.parse(query, { database: 'redshift' })
const sqlifyStr = sqlParser.sqlify(parsedQuery.ast, { database: 'redshift'})
const expected = ``WITH "pv" AS (SELECT action_date, visitor_id_v, visit_country_name, referer_channel_group, email, sgid, "mp"."brand/non-brand" AS "is_brand" FROM "dwh_fact_pageviews" AS "pv" LEFT JOIN "ppc_keywords_mapping" AS "mp" USING ("campaign_keyword")) SELECT is_brand AS "b/nb" FROM "pv" WHERE "mp"."brand/non-brand" = 'brand'`
expect(sqlifyStr).toBe(expected)taozhi8833998 commented
@LaizaAngrest looks like you have some codes to get columns from the ast. In order to keep the original quoted, the column now be an object while not string, so the column.expr.column.match should be column.expr.column.expr.value.match. you can try the code below:
const columnStr = typeof column.expr.column === 'string' ? column.expr.column : column.expr.column.expr.value
if (columnStr && columnStr !== '*' && columnStr.match(SPECIAL_CHAR_SREGEX)) {
specialCharsColumns.push(columnStr)
}LaizaAngrest commented
@taozhi8833998 I fixed the code as you said, now I'm dealing with another issue, there is no way to specify columns with quotes like this query:
with tmp_variables_weekly as ( select dateadd('day', -120, current_date) as start, current_date as end_v ), pv as ( select action_date, visitor_id_v, visit_country_name, referer_channel_group, email, sgid, mp."brand/non-brand" as is_brand from dwh_fact_pageviews pv left join test2.ppc_keywords_mapping mp using (campaign_keyword) where pv.action_date >= ( select start from tmp_variables_weekly )) select is_brand as "b/nb" from pv where mp."brand/non-brand" = 'brand'
`SyntaxError: Expected [^"] but "\"" found.
5181 | / param
5182 | / LPAREN __ e:proc_additive_expr __ RPAREN {
> 5183 | // => proc_additive_expr & { parentheses: true; }
| ^
5184 | e.parentheses = true;
5185 | return e;
5186 | }
at s2 (../../node-sql-parser/pegjs/redshift.pegjs:5183:11)`
taozhi8833998 commented
@LaizaAngrest What do you mean: 'specify columns with quotes'. The SQL you provided can be parsed successfully.
LaizaAngrest commented
@taozhi8833998
Sorry that was not the right query.
here is the problem:
it('sqlify long query', async () => {
const sqlParser = new SqlParser()
const parsedQuery = sqlParser.parse(longQuery, WarehouseType.Redshift)
const sqlifyStr = sqlify(parsedQuery, sqlParser, WarehouseType.Redshift)
expect(sqlifyStr).toBeDefined()
// validate that sqlifyStr is valid sql
const parsedQuery2 = sqlParser.parse(sqlifyStr, WarehouseType.Redshift)
const sqlifyStr2 = sqlify(parsedQuery2, sqlParser, WarehouseType.Redshift)
expect(sqlifyStr2).toBeDefined()
})
`SyntaxError: Expected [^"] but "\"" found.
5181 | / param
5182 | / LPAREN __ e:proc_additive_expr __ RPAREN {
> 5183 | // => proc_additive_expr & { parentheses: true; }
| ^
5184 | e.parentheses = true;
5185 | return e;
5186 | }
at s2 (../../node-sql-parser/pegjs/redshift.pegjs:5183:11)`
const longQuery = `
with
tmp_variables_weekly as (
select
dateadd('day', -120, current_date) as start,
current_date as end_v
),
e as (
select
visitor_id,
lower(email) as email
from
dwh_visitor_email_v
where
email like '%@%'
and email not like '%@similarweb%'
),
ee as (
select
lower(email) as email,
sgid
from
dwh_visitor_email_v
where
email not like '%@similarweb%'
and email like '%@%'
and lower(sgid) <> 'na'
group by
1,
2
),
pv as (
select
action_date,
visitor_id_v,
visit_country_name,
referer_channel_group,
email,
sgid,
mp."brand/non-brand" as is_brand
from
dwh_fact_pageviews pv
left join ppc_keywords_mapping mp using (campaign_keyword)
where
lower(pv.site_type) = 'lite'
and pv.is_sw_ip = 0
and pv.is_sw_user = 0
and pv.user_status <> 'Internal'
and pv.referer_channel_group <> 'Internal'
and pv.email not like '%@similarweb%'
and pv.is_dev_or_stage = 0
and pv.is_event = 0
and lower(url_name_short) not similar to '%(chrome-extension|firefox-extension)%'
and pv.action_date >= (
select
start
from
tmp_variables_weekly
)
--and pv.referer_channel_group='ppc'
group by
1,
2,
3,
4,
5,
6,
7
),
ppc_leads as (
select
u.email,
pv.is_brand
from
pv
left join e on e.visitor_id = pv.visitor_id_v
left join ee on ee.sgid = pv.sgid
join (
select
user_create_date::date as creation,
lower(email) as email
from
dwh_dim_users
where
creation >= (
select
start
from
tmp_variables_weekly
)
) u on u.email = coalesce(e.email, ee.email, pv.email)
where
pv.referer_channel_group = 'ppc'
and creation between pv.action_date::date and dateadd('day', 1, pv.action_date::date)
),
users as (
select
u.email,
user_create_date::date as creation,
lead_id,
mir_status,
lead_status,
contact_id,
sf_account_id,
lead_owner_id,
employee_min_group,
use_of_similarweb,
unqualified_reason,
seniority,
case
when swid__r_visits <= 20000 then '0-20K'
when swid__r_visits <= 40000 then '20K-40K'
when swid__r_visits <= 60000 then '40K-60K'
when swid__r_visits <= 80000 then '60K-80K'
when swid__r_visits <= 100000 then '80-100K'
when swid__r_visits <= 200000 then '100-200K'
when swid__r_visits <= 500000 then '200K-500K'
else 'Above 500K'
end as monthly_traffic,
business_unit_group,
sub_division,
domain_type,
-- mp.department_fixed as department,
case
when u.lead_owner_id = '00G6700000ANbrcEAD' then 'Incomplete'
else 'Complete'
end as is_completed,
country as Country,
industry,
coalesce(
nullif(u.routing_group, 'NA'),
user_owner_division
) as routing_group_or_current_owner,
case
when routing_group_or_current_owner = 'MIR' then 'MIR'
when routing_group_or_current_owner ilike '%SDR%' then 'SDR'
when routing_group_or_current_owner ilike '%Sales%' then 'Sales'
when routing_group_or_current_owner ilike '%Reseller%' then 'Sales'
when routing_group_or_current_owner ilike '%CSM%' then 'Sales'
when routing_group_or_current_owner ilike '%Investors%' then 'Sales'
when routing_group_or_current_owner = 'NA' then 'Unrouted'
else 'Other'
end routing_group,
ma.inbound_or_outbound,
sw_fit_score,
case
when ppc_leads.email is not null then 'PPC Lead'
else 'Non PPC'
end is_ppc_lead,
ppc_leads.is_brand
from
dwh_dim_users_v u
left join ma_inbound_outbound_mapping ma using (original_lead_source)
-- left join poc.ma_department_mapping mp using (department)
left join ppc_leads on u.email = ppc_leads.email
where
exist_in_salesforce = 1
and is_deleted_lead = 0
and ignore_user = 0
and u.email not like '%@similarweb%'
),
meetings as (
select
meeting_created_date_id,
meeting_created_date,
meeting_date_id,
contact_id,
meeting_status,
case
when record_type_name = 'MIR Handover' then 'MIR'
when record_type_name = 'SDR' then 'SDR'
when record_type_name in (
'Sales Meeting',
'AM Upsell/Cross-sell',
'SM Upsell/Cross-sell'
) then 'Sales'
else 'Other'
end record_type_name,
meeting_id,
email,
sub_division,
meeting_date,
account_id
from
dwh_dim_meetings_v m
where
meeting_created_date >= (
select
start
from
tmp_variables_weekly
)
and division not like '%SDR%'
and division not in (
'MIR',
'Sales Enablement',
'Sales Operations',
'Strategic Consultant'
)
and is_deleted = 0
),
opp as (
select
o.opportunity_id,
opportunity_created_date_id,
o.opportunity_type,
o.account_id,
o.contact_id,
o.converted_to_pipeline_date::date,
sr.sales_rep_sub_division as sub_division,
o.opportunity_source --'%Handraise%' '%Inbound%' or '%Proactive outreach%' '%outbound%'
,
opportunity_generator --MIR/SDR/SM
from
dwh_dim_opportunities_v o
left join dwh_fact_daily_sales_rep sr on o.opportunity_sales_rep = sr.sales_rep_id
and o.converted_to_pipeline_date::date = sr.date_id::date
where
current_id_deleted = 0
and opportunity_created_date_id >= (
select
start
from
tmp_variables_weekly
)
and opportunity_type = 'New Sale'
),
NT as (
select
subscription_start_date,
email
from
(
select
subscription_start_date::date subscription_start_date,
u.email,
s.zuora_account_id,
trial_start_date,
row_number() over (
partition by
s.zuora_account_id
order by
(
case
when u.trial_start_date <= subscription_start_date then 0
else 1
end
),
trial_start_date desc
) rn
from
dwh_dim_subscriptions s
left join dwh_dim_accounts da on s.zuora_account_id = da.zuora_account_id
left join zuora_contacts as z on z.account_id = s.zuora_account_id
left join dwh_dim_users u on da.sf_or_sw_id = u.sf_or_sw_id
and da.sf_or_sw_id != 'NA'
where
1 = 1
and subscription_start_date between '2022-04-01' and current_date
and s.is_touch_subscription = 0
and s.is_first_paid_subscription = 1
and not (
da.account_email like '%@similarweb.com'
or z.work_email like '%similarweb.com%'
)
and s.sw_account_id > -1
) a
where
1 = 1
and rn = 1
),
mqls as (
with
emails_dates as (
select
d.date_id::date,
p.email
from
dates_list d
full outer join (
select distinct
email
from
dwh_fact_meeting_projections_changes_snapshot
union
select distinct
email
from
dwh_fact_meeting_projections_shopper_changes_snapshot
union
select distinct
email
from
dwh_fact_meeting_projections_si_changes_snapshot
) p on 1 = 1
join dwh_dim_users u on u.email = p.email
where
true
and d.date_id >= '2023-07-09'
and d.date_id <= current_date
group by
1,
2
order by
1,
2
),
snapshots as (
select
date_id::date,
p.email,
prediction_probability_group
from
dwh_fact_meeting_projections_changes_snapshot p
group by
1,
2,
3
union
select
date_id::date,
p.email,
prediction as prediction_probability_group
from
dwh_fact_meeting_projections_shopper_changes_snapshot p
group by
1,
2,
3
union
select
date_id::date,
p.email,
prediction as prediction_probability_group
from
dwh_fact_meeting_projections_si_changes_snapshot p
group by
1,
2,
3
),
snapshot_all as (
select
ed.date_id,
ed.email,
coalesce(
prediction_probability_group,
lag(prediction_probability_group) IGNORE NULLS over (
partition by
ed.email
order by
ed.date_id
)
) as probability_group
from
emails_dates ed
left join snapshots sp on ed.email = sp.email
and ed.date_id = sp.date_id
)
select
*
from
snapshot_all
where
true
and probability_group >= 7
)
-- visitors
select
'Visitors' as kpi,
date_trunc('day', action_date)::date period_ytd,
case
when (referer_channel_group) = 'ppc' then 'PPC Lead'
else 'Non PPC'
end as is_ppc_lead,
is_brand,
'' as domain_type,
'' as industry,
'' as use_of_similarweb,
'' as seniority,
'' as business_unit_group,
'' as employee_min_group,
'' as sub_division,
visit_country_name as Country,
'' as sw_fit_score,
'' as routing_group,
'' as inbound_or_outbound,
'' as is_completed,
'' as monthly_traffic,
count(distinct visitor_id_v) kpi_value
from
pv
where
action_date between (
select
start
from
tmp_variables_weekly
) and (
select
end_v
from
tmp_variables_weekly
)
group by
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12,
13,
14,
15,
16,
17
union all
select
'Leads' as kpi,
date_trunc('day', creation) as period_ytd,
is_ppc_lead,
is_brand,
domain_type,
industry,
use_of_similarweb,
seniority,
business_unit_group,
employee_min_group,
sub_division,
Country,
sw_fit_score,
routing_group,
inbound_or_outbound,
is_completed,
monthly_traffic,
count(distinct email) kpi_value
from
users
where
period_ytd between (
select
start
from
tmp_variables_weekly
) and (
select
end_v
from
tmp_variables_weekly
)
group by
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12,
13,
14,
15,
16,
17
union all
select
'unqualified' as kpi,
date_trunc('day', creation) as period_ytd,
is_ppc_lead,
is_brand,
domain_type,
industry,
use_of_similarweb,
seniority,
business_unit_group,
employee_min_group,
sub_division,
Country,
sw_fit_score,
routing_group,
inbound_or_outbound,
is_completed,
monthly_traffic,
count(distinct email) kpi_value
from
users
where
period_ytd between (
select
start
from
tmp_variables_weekly
) and (
select
end_v
from
tmp_variables_weekly
)
and (
(
mir_status in ('Deprioritized', 'Unqualified')
or lead_status = 'Unqualified'
)
or (unqualified_reason <> 'NA')
)
group by
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12,
13,
14,
15,
16,
17
union all
select
'mql' as kpi,
date_trunc('week', date_id) as period_ytd,
is_ppc_lead,
is_brand,
domain_type,
industry,
use_of_similarweb,
seniority,
business_unit_group,
employee_min_group,
sub_division,
Country,
sw_fit_score,
routing_group,
inbound_or_outbound,
is_completed,
monthly_traffic,
count(distinct u.email) kpi_value
from
mqls mql
join users u using (email)
where
period_ytd between (
select
start
from
tmp_variables_weekly
) and (
select
end_v
from
tmp_variables_weekly
)
group by
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12,
13,
14,
15,
16,
17
union all
select
'Handraises' as kpi,
date_trunc('day', hr.activity_date) period_ytd,
u.is_ppc_lead,
u.is_brand,
u.domain_type,
industry,
use_of_similarweb,
u.seniority,
u.business_unit_group,
u.employee_min_group,
u.sub_division,
u.Country,
sw_fit_score,
case
when is_mir = 'Yes' then 'MIR'
else 'Other'
end as routing_group,
inbound_or_outbound,
is_completed,
monthly_traffic,
count(hr.email) kpi_value
from
dwh_fact_user_handraise hr
inner join users u on hr.email = u.email
and hr.handraise_type <> 'Email Reply'
where
period_ytd between (
select
start
from
tmp_variables_weekly
) and (
select
end_v
from
tmp_variables_weekly
)
group by
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12,
13,
14,
15,
16,
17
union all
select
'Handraises distinct' as kpi,
date_trunc('day', hr.activity_date) period_ytd,
u.is_ppc_lead,
u.is_brand,
u.domain_type,
industry,
use_of_similarweb,
u.seniority,
u.business_unit_group,
u.employee_min_group,
u.sub_division,
u.Country,
sw_fit_score,
case
when is_mir = 'Yes' then 'MIR'
else 'Other'
end as routing_group,
inbound_or_outbound,
is_completed,
monthly_traffic,
count(distinct hr.email) kpi_value
from
dwh_fact_user_handraise hr
inner join users u on hr.email = u.email
and hr.handraise_type <> 'Email Reply'
where
period_ytd between (
select
start
from
tmp_variables_weekly
) and (
select
end_v
from
tmp_variables_weekly
)
group by
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12,
13,
14,
15,
16,
17
union all
select
'NT' as kpi,
date_trunc('day', subscription_start_date) as period_ytd,
is_ppc_lead,
is_brand,
domain_type,
industry,
use_of_similarweb,
seniority,
business_unit_group,
employee_min_group,
sub_division,
Country,
sw_fit_score,
routing_group,
inbound_or_outbound,
is_completed,
monthly_traffic,
count(distinct email) kpi_value
from
users
inner join NT using (email)
where
period_ytd between (
select
start
from
tmp_variables_weekly
) and (
select
end_v
from
tmp_variables_weekly
)
group by
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12,
13,
14,
15,
16,
17
union all
select
'Meetings Created' as kpi,
date_trunc('day', meeting_created_date_id)::date period_ytd,
u.is_ppc_lead,
u.is_brand,
u.domain_type,
u.industry,
use_of_similarweb,
u.seniority,
u.business_unit_group,
u.employee_min_group,
account_sub_division as sub_division,
u.Country,
sw_fit_score,
record_type_name routing_group,
u.inbound_or_outbound,
is_completed,
monthly_traffic,
count(distinct meeting_id) kpi_value
from
meetings m
inner join users u on m.email = u.email
left join dwh_dim_accounts a on m.account_id = a.sf_account_id
and m.account_id <> 'NA'
where
meeting_created_date_id between (
select
start
from
tmp_variables_weekly
) and (
select
end_v
from
tmp_variables_weekly
)
group by
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12,
13,
14,
15,
16,
17
union all
select
'Pipeline' as kpi,
date_trunc('day', converted_to_pipeline_date)::date period_ytd,
u.is_ppc_lead,
u.is_brand,
u.domain_type,
u.industry,
use_of_similarweb,
u.seniority,
u.business_unit_group,
u.employee_min_group,
o.sub_division,
u.Country,
sw_fit_score,
opportunity_generator routing_group,
u.inbound_or_outbound,
is_completed,
monthly_traffic,
count(distinct opportunity_id) kpi_value
from
opp o
inner join users u on o.contact_id = u.contact_id
and u.contact_id <> 'NA'
where
converted_to_pipeline_date between (
select
start
from
tmp_variables_weekly
) and (
select
end_v
from
tmp_variables_weekly
)
group by
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12,
13,
14,
15,
16,
17
union all
select
'opp ten' as kpi,
date_trunc('day', opportunity_created_date_id) period_ytd,
u.is_ppc_lead,
u.is_brand,
u.domain_type,
u.industry,
use_of_similarweb,
u.seniority,
u.business_unit_group,
u.employee_min_group,
o.sub_division,
u.Country,
sw_fit_score,
opportunity_generator routing_group,
u.inbound_or_outbound,
is_completed,
monthly_traffic,
count(distinct opportunity_id) kpi_value
from
opp o
inner join users u on o.contact_id = u.contact_id
and o.contact_id <> 'NA'
where
opportunity_created_date_id between (
select
start
from
tmp_variables_weekly
) and (
select
end_v
from
tmp_variables_weekly
)
group by
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12,
13,
14,
15,
16,
17
union all
select
'lead meeting cycle' as kpi,
meeting_created_date::date period_ytd,
u.is_ppc_lead,
u.is_brand,
u.domain_type,
u.industry,
use_of_similarweb,
u.seniority,
u.business_unit_group,
u.employee_min_group,
m.sub_division,
u.Country,
sw_fit_score,
record_type_name routing_group,
u.inbound_or_outbound,
is_completed,
monthly_traffic,
datediff('day', u.creation, meeting_created_date::date) as kpi_value
from
meetings m
inner join users u on m.contact_id = u.contact_id
and u.contact_id <> 'NA'
where
meeting_created_date between (
select
start
from
tmp_variables_weekly
) and (
select
end_v
from
tmp_variables_weekly
)
group by
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12,
13,
14,
15,
16,
17,
18
union all
select
'Meeting Cycle' as kpi,
meeting_created_date_id::date period_ytd,
u.is_ppc_lead,
u.is_brand,
u.domain_type,
u.industry,
use_of_similarweb,
u.seniority,
u.business_unit_group,
u.employee_min_group,
m.sub_division,
u.Country,
sw_fit_score,
record_type_name routing_group,
u.inbound_or_outbound,
is_completed,
monthly_traffic,
(-1) * datediff('day', meeting_date_id, meeting_created_date_id) as kpi_value
from
meetings m
inner join users u on m.contact_id = u.contact_id
and m.contact_id <> 'NA'
where
period_ytd between (
select
start
from
tmp_variables_weekly
) and (
select
end_v
from
tmp_variables_weekly
)
and m.meeting_status = 'Completed'
group by
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12,
13,
14,
15,
16,
17,
18
union all
select
'10-25-cycle' as kpi,
converted_to_pipeline_date::date period_ytd,
u.is_ppc_lead,
u.is_brand,
u.domain_type,
u.industry,
use_of_similarweb,
u.seniority,
u.business_unit_group,
u.employee_min_group,
o.sub_division,
u.Country,
sw_fit_score,
opportunity_generator routing_group,
u.inbound_or_outbound,
is_completed,
monthly_traffic,
datediff(
'day',
opportunity_created_date_id,
converted_to_pipeline_date
) as kpi_value
from
opp o
inner join users u on o.contact_id = u.contact_id
and o.contact_id <> 'NA'
where
period_ytd between (
select
start
from
tmp_variables_weekly
) and (
select
end_v
from
tmp_variables_weekly
)
and o.converted_to_pipeline_date::date < '2100-01-01'
group by
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12,
13,
14,
15,
16,
17,
18
`
taozhi8833998 commented
@LaizaAngrest Sorry for that. But I still can't reproduce the issue, I can parse the long SQL , and sqlify back, and parse the sqlify back SQL again.
taozhi8833998 commented
@LaizaAngrest It is a comment line, and you can remove it safely.
