我有一个雪花MERGE语句，它自己可以成功执行，但当我将它封装在过程中时，它会抱怨其中一列"无效标识符"；。
这是我的SQL语句：
MERGE INTO WAREHOUSE.ART_TWITTER_TRANSACTION_FACT t
USING (
with artist_twitter as
(select lower(trim(twitter_handle_1)) as twitter_handle, twitter_id_1 as twitter_id
from warehouse.artist_dim
where twitter_id_1 is not null
and twitter_handle_1 is not null
and twitter_id_1 <> '0'
and twitter_filter_flag = 1
group by
lower(trim(twitter_handle_1)), twitter_id_1
union
select lower(trim(twitter_handle_2)) as twitter_handle, twitter_id_2 as twitter_id
from warehouse.artist_dim
where twitter_id_2 is not null
and twitter_handle_2 is not null
and twitter_id_2 <> '0'
and twitter_filter_flag = 1
group by
lower(trim(twitter_handle_2)), twitter_id_2
union
select lower(trim(twitter_handle_3)) as twitter_handle, twitter_id_3 as twitter_id
from warehouse.artist_dim
where twitter_id_3 is not null
and twitter_handle_3 is not null
and twitter_id_3 <> '0'
and twitter_filter_flag = 1
group by
lower(trim(twitter_handle_3)), twitter_id_3
union
select lower(trim(twitter_handle_4)) as twitter_handle, twitter_id_4 as twitter_id
from warehouse.artist_dim
where twitter_id_4 is not null
and twitter_handle_4 is not null
and twitter_id_4 <> '0'
and twitter_filter_flag = 1
group by
lower(trim(twitter_handle_4)), twitter_id_4
union
select lower(trim(twitter_handle_5)) as twitter_handle, twitter_id_5 as twitter_id
from warehouse.artist_dim
where twitter_id_5 is not null
and twitter_handle_5 is not null
and twitter_id_5 <> '0'
and twitter_filter_flag = 1
group by
lower(trim(twitter_handle_5)), twitter_id_5
)
, count_artist_by_twitter_handle as
(
select trim(a.twitter_handle) as twitter_handle, count(distinct b.artist_name) as unique_artists
from artist_twitter a
inner join warehouse.artist_dim b
on a.twitter_handle = b.twitter_handle_1
or a.twitter_handle = b.twitter_handle_2
or a.twitter_handle = b.twitter_handle_3
or a.twitter_handle = b.twitter_handle_4
or a.twitter_handle = b.twitter_handle_5
where b.twitter_filter_flag = 1
group by trim(a.twitter_handle)
)
, count_art_by_artist as
(
select a.artist_name
, lower(trim(c.twitter_handle)) as twitter_handle
, count(distinct a.collection_contract_uid || '-' || a.collection_item_id) as unique_art
, row_number() over (partition by lower(trim(c.twitter_handle)) order by unique_art desc) as rank
from warehouse.art_dim a
inner join warehouse.artist_dim b
on a.artist_name = b.artist_name
inner join artist_twitter c
on (c.twitter_handle = b.twitter_handle_1
or c.twitter_handle = b.twitter_handle_2
or c.twitter_handle = b.twitter_handle_3
or c.twitter_handle = b.twitter_handle_4
or c.twitter_handle = b.twitter_handle_5 )
group by a.artist_name, lower(trim(c.twitter_handle))
order by lower(trim(c.twitter_handle)), rank
)
, pick_ranked_artist as
(
select
artist_name
,twitter_handle
from count_art_by_artist
where rank = 1
)
, twitter_handle_artist_cnt_ranked as
(select a.twitter_handle, a.unique_artists,
case when b.artist_name is not null then b.artist_name
else c.artist_name end artist_name
from count_artist_by_twitter_handle a
left join pick_ranked_artist b
on a.twitter_handle = b.twitter_handle
left join (select twitter_handle_1 as twitter_handle, artist_name from warehouse.artist_dim
where artist_type = 'NON-ARTIST'
and twitter_handle_1 is not null
union
select twitter_handle_2, artist_name from warehouse.artist_dim
where artist_type = 'NON-ARTIST'
and twitter_handle_2 is not null
union
select twitter_handle_3, artist_name from warehouse.artist_dim
where artist_type = 'NON-ARTIST'
and twitter_handle_3 is not null
union
select twitter_handle_4, artist_name from warehouse.artist_dim
where artist_type = 'NON-ARTIST'
and twitter_handle_4 is not null
union
select twitter_handle_5, artist_name from warehouse.artist_dim
where artist_type = 'NON-ARTIST'
and twitter_handle_5 is not null
) c
on a.twitter_handle = c.twitter_handle
)
, artist_twitter_associations as
(select
a.tweet_id
,a.tweet_conversation_id
,a.twitter_author_id
,b.twitter_id
,b.twitter_handle
,c.artist_name
,a.tweet_url
,a.tweet_expanded_url
,a.tweet_display_url
,a.tweet_unwound_url
,a.tweet_text
,a.tweet_url_status
,a.tweet_url_title
,a.tweet_url_description
,a.tweet_public_retweet_count
,a.tweet_public_reply_count
,a.tweet_public_like_count
,a.tweet_public_quote_count
,a.as_of_datetime
,a.tweet_date
,row_number() over (partition by a.tweet_id order by a.as_of_datetime desc) as rank
from stage.twitter_tweet_history a
left join artist_twitter b
on a.twitter_author_id = b.twitter_id
left join twitter_handle_artist_cnt_ranked c
on b.twitter_handle = c.twitter_handle
group by
a.tweet_id
,a.tweet_conversation_id
,a.twitter_author_id
,b.twitter_id
,b.twitter_handle
,c.artist_name
,a.tweet_url
,a.tweet_expanded_url
,a.tweet_display_url
,a.tweet_unwound_url
,a.tweet_text
,a.tweet_url_status
,a.tweet_url_title
,a.tweet_url_description
,a.tweet_public_retweet_count
,a.tweet_public_reply_count
,a.tweet_public_like_count
,a.tweet_public_quote_count
,a.as_of_datetime
,a.tweet_date
)
,ranked_tweet_associations as
(
select
tweet_id
,tweet_conversation_id
,twitter_author_id
,twitter_id
,twitter_handle
,artist_name
,tweet_url
,tweet_expanded_url
,tweet_display_url
,tweet_unwound_url
,tweet_text
,tweet_url_status
,tweet_url_title
,tweet_url_description
,tweet_public_retweet_count
,tweet_public_reply_count
,tweet_public_like_count
,tweet_public_quote_count
,as_of_datetime
,tweet_date
from artist_twitter_associations
where rank = 1
)
, derive_link_info as
(select tweet_id, twitter_author_id, twitter_handle, artist_name,
as_of_datetime, tweet_date,
case when tweet_unwound_url is null
then tweet_expanded_url
else tweet_unwound_url end as url_to_parse
from ranked_tweet_associations
)
, split_url as
(
select tweet_id, twitter_author_id, twitter_handle, artist_name
, as_of_datetime, tweet_date
, url_to_parse
, split_part(url_to_parse, '/', 0) as site_url_0
, split_part(url_to_parse, '/', 1) as site_url_1
, split_part(url_to_parse, '/', 2) as site_url_2
, split_part(url_to_parse, '/', 3) as site_url_3
, split_part(url_to_parse, '/', 4) as site_url_4
, split_part(url_to_parse, '/', 5) as site_url_5
, split_part(url_to_parse, '/', 6) as site_url_6
, split_part(url_to_parse, '/', 7) as site_url_7
, case when site_url_3 = 'niftygateway.com'
then 'nifty'
else 'other' end as site
, case when site = 'nifty' and site_url_4 = 'collections'
then site_url_5
when site = 'nifty' and site_url_4 = 'profile'
then site_url_5
when site = 'nifty' and site_url_4 = 'itemdetail'
then site_url_6
else 'Other'
end as artist_info
from derive_link_info
)
,identify_art_or_artist as
(
select a.tweet_id
, a1.tweet_conversation_id
, a.twitter_author_id
, a.twitter_handle
, a.artist_name as tweet_artist_name
, a.as_of_datetime
, a.tweet_date
, a1.tweet_text
, a1.tweet_public_retweet_count
, a1.tweet_public_reply_count
, a1.tweet_public_like_count
, a1.tweet_public_quote_count
,a1.tweet_url
,a1.tweet_expanded_url
,a1.tweet_display_url
,a1.tweet_unwound_url
,a1.tweet_url_status
,a1.tweet_url_title
,a1.tweet_url_description
, a.url_to_parse
, a.site_url_4
, a.site_url_5
, a.site_url_6
, a.site_url_7
, a.site
, case when a.site = 'nifty'
then
case when a.site_url_4 = 'itemdetail' and a.site_url_5 = 'primary'
then b.artist_name
when a.site_url_4 = 'itemdetail' and a.site_url_5 = 'secondary'
then c.artist_name
when a.site_url_4 = 'profile'
then a.site_url_5
when a.site_url_4 = 'collections'
then d.artist_name
end
else 'Other' end as art_artist_name
, case when a.site = 'nifty'
then case when a.site_url_4 = 'itemdetail' and a.site_url_5 = 'primary'
then b.collection_contract_uid
when a.site_url_4 = 'itemdetail' and a.site_url_5 = 'secondary'
then c.collection_contract_uid
when a.site_url_4 = 'profile'
then 'None'
when a.site_url_4 = 'collections'
then 'None'
end
else 'None' end as collection_contract_uid
, case when a.site = 'nifty'
then case when a.site_url_4 = 'itemdetail' and a.site_url_5 = 'primary'
then b.collection_item_id
when a.site_url_4 = 'itemdetail' and a.site_url_5 = 'secondary'
then 'None'
when a.site_url_4 = 'profile'
then 'None'
when a.site_url_4 = 'collections'
then 'None'
end
else 'None' end as collection_item_id
, case when a.site = 'nifty'
then case when a.site_url_4 = 'itemdetail' and a.site_url_5 = 'primary'
then 'None'
when a.site_url_4 = 'itemdetail' and a.site_url_5 = 'secondary'
then a.site_url_7
when a.site_url_4 = 'profile'
then 'None'
when a.site_url_4 = 'collections'
then 'None'
end
else 'None' end as token_id
from split_url a
inner join ranked_tweet_associations a1
on a.tweet_id = a1.tweet_id
left join warehouse.art_dim b
on a.site_url_6 = b.collection_contract_uid
and a.site_url_7 = b.collection_item_id
left join ( select collection_contract_uid, artist_name
from warehouse.art_dim
group by collection_contract_uid, artist_name) c
on a.site_url_6 = c.collection_contract_uid
left join (select artist_name from warehouse.art_dim
group by artist_name) d
on a.site_url_5 = d.artist_name
)
select
a.tweet_id
,a.collection_contract_uid
,a.collection_item_id
,a.token_id
,a.tweet_artist_name
,a.twitter_handle as tweet_twitter_handle
,a.art_artist_name
,NVL(b.art_name,'None') as art_name
,a.tweet_text
,a.tweet_conversation_id
,a.twitter_author_id
,a.tweet_date
,a.tweet_public_retweet_count
,a.tweet_public_reply_count
,a.tweet_public_like_count
,a.tweet_public_quote_count
,a.tweet_url
,a.tweet_expanded_url
,a.tweet_display_url
,a.tweet_unwound_url
,a.tweet_url_status
,a.tweet_url_title
,a.tweet_url_description
,a.as_of_datetime
,current_timestamp
from identify_art_or_artist a
left join warehouse.art_dim b
on a.collection_contract_uid = b.collection_contract_uid
and a.collection_item_id = b.collection_item_id
) s
ON s.tweet_id = t.tweet_id
AND s.collection_contract_uid = t.collection_contract_uid
AND s.collection_item_id = t.collection_item_id
AND s.token_id = t.token_id
AND s.tweet_artist_name = t.tweet_artist_name
AND s.tweet_twitter_handle = t.tweet_twitter_handle
AND s.art_artist_name = t.art_artist_name
AND NVL(s.art_name,'None') = NVL(t.art_name,'None')
AND s.tweet_text = t.tweet_text
AND s.tweet_conversation_id = t.tweet_conversation_id
AND s.twitter_author_id = t.twitter_author_id
AND s.tweet_date = t.tweet_date
AND NVL(s.tweet_url,'x') = NVL(t.tweet_url,'x')
AND NVL(s.tweet_expanded_url,'x') = NVL(t.tweet_expanded_url,'x')
AND NVL(s.tweet_display_url,'x') = NVL(t.tweet_display_url,'x')
AND NVL(s.tweet_unwound_url,'x') = NVL(t.tweet_unwound_url,'x')
AND NVL(s.tweet_url_status,'x') = NVL(t.tweet_url_status,'x')
AND NVL(s.tweet_url_title,'x') = NVL(t.tweet_url_title,'x')
AND NVL(s.tweet_url_description,'x') = NVL(t.tweet_url_description,'x')
AND s.tweet_public_retweet_count = t.tweet_public_retweet_count
AND s.tweet_public_reply_count = t.tweet_public_reply_count
AND s.tweet_public_like_count = t.tweet_public_like_count
AND s.tweet_public_quote_count = t.tweet_public_quote_count
WHEN NOT MATCHED THEN INSERT
(
tweet_id
,collection_contract_uid
,collection_item_id
,token_id
,tweet_artist_name
,tweet_twitter_handle
,art_artist_name
,art_name
,tweet_text
,tweet_conversation_id
,twitter_author_id
,tweet_date
,tweet_public_retweet_count
,tweet_public_reply_count
,tweet_public_like_count
,tweet_public_quote_count
,tweet_url
,tweet_expanded_url
,tweet_display_url
,tweet_unwound_url
,tweet_url_status
,tweet_url_title
,tweet_url_description
,as_of_datetime
,insert_date
)
VALUES
(s.tweet_id
,s.collection_contract_uid
,s.collection_item_id
,s.token_id
,s.tweet_artist_name
,s.tweet_twitter_handle
,s.art_artist_name
,s.art_name
,s.tweet_text
,s.tweet_conversation_id
,s.twitter_author_id
,s.tweet_date
,s.tweet_public_retweet_count
,s.tweet_public_reply_count
,s.tweet_public_like_count
,s.tweet_public_quote_count
,s.tweet_url
,s.tweet_expanded_url
,s.tweet_display_url
,s.tweet_unwound_url
,s.tweet_url_status
,s.tweet_url_title
,s.tweet_url_description
,s.as_of_datetime
,current_timestamp)
;
这与程序中的陈述完全相同：
create or replace procedure NFT_DEV_DB.WAREHOUSE.load_art_twitter_transaction_fact()
returns string
language javascript
execute as owner
as
$$
// Construct the merge sql statement
// Snowflake currently only supports a single sql statement execution in a procedure.
// Due to this limitation, sql updates and inserts must be formatted as a merge statement.
// This procedure loads new or changed tweets from landing.twitter_tweet_changes to stage.twitter_tweet_history.
// It will check each field for changes so if a tweet comes through on multiple twitter pulls it will only
// be inserted if there is something different since the last pull of that record.
var sql_command =
`
MERGE INTO WAREHOUSE.ART_TWITTER_TRANSACTION_FACT t
USING (
with artist_twitter as
(select lower(trim(twitter_handle_1)) as twitter_handle, twitter_id_1 as twitter_id
from warehouse.artist_dim
where twitter_id_1 is not null
and twitter_handle_1 is not null
and twitter_id_1 <> '0'
and twitter_filter_flag = 1
group by
lower(trim(twitter_handle_1)), twitter_id_1
union
select lower(trim(twitter_handle_2)) as twitter_handle, twitter_id_2 as twitter_id
from warehouse.artist_dim
where twitter_id_2 is not null
and twitter_handle_2 is not null
and twitter_id_2 <> '0'
and twitter_filter_flag = 1
group by
lower(trim(twitter_handle_2)), twitter_id_2
union
select lower(trim(twitter_handle_3)) as twitter_handle, twitter_id_3 as twitter_id
from warehouse.artist_dim
where twitter_id_3 is not null
and twitter_handle_3 is not null
and twitter_id_3 <> '0'
and twitter_filter_flag = 1
group by
lower(trim(twitter_handle_3)), twitter_id_3
union
select lower(trim(twitter_handle_4)) as twitter_handle, twitter_id_4 as twitter_id
from warehouse.artist_dim
where twitter_id_4 is not null
and twitter_handle_4 is not null
and twitter_id_4 <> '0'
and twitter_filter_flag = 1
group by
lower(trim(twitter_handle_4)), twitter_id_4
union
select lower(trim(twitter_handle_5)) as twitter_handle, twitter_id_5 as twitter_id
from warehouse.artist_dim
where twitter_id_5 is not null
and twitter_handle_5 is not null
and twitter_id_5 <> '0'
and twitter_filter_flag = 1
group by
lower(trim(twitter_handle_5)), twitter_id_5
)
, count_artist_by_twitter_handle as
(
select trim(a.twitter_handle) as twitter_handle, count(distinct b.artist_name) as unique_artists
from artist_twitter a
inner join warehouse.artist_dim b
on a.twitter_handle = b.twitter_handle_1
or a.twitter_handle = b.twitter_handle_2
or a.twitter_handle = b.twitter_handle_3
or a.twitter_handle = b.twitter_handle_4
or a.twitter_handle = b.twitter_handle_5
where b.twitter_filter_flag = 1
group by trim(a.twitter_handle)
)
, count_art_by_artist as
(
select a.artist_name
, lower(trim(c.twitter_handle)) as twitter_handle
, count(distinct a.collection_contract_uid || '-' || a.collection_item_id) as unique_art
, row_number() over (partition by lower(trim(c.twitter_handle)) order by unique_art desc) as rank
from warehouse.art_dim a
inner join warehouse.artist_dim b
on a.artist_name = b.artist_name
inner join artist_twitter c
on (c.twitter_handle = b.twitter_handle_1
or c.twitter_handle = b.twitter_handle_2
or c.twitter_handle = b.twitter_handle_3
or c.twitter_handle = b.twitter_handle_4
or c.twitter_handle = b.twitter_handle_5 )
group by a.artist_name, lower(trim(c.twitter_handle))
order by lower(trim(c.twitter_handle)), rank
)
, pick_ranked_artist as
(
select
artist_name
,twitter_handle
from count_art_by_artist
where rank = 1
)
, twitter_handle_artist_cnt_ranked as
(select a.twitter_handle, a.unique_artists,
case when b.artist_name is not null then b.artist_name
else c.artist_name end artist_name
from count_artist_by_twitter_handle a
left join pick_ranked_artist b
on a.twitter_handle = b.twitter_handle
left join (select twitter_handle_1 as twitter_handle, artist_name from warehouse.artist_dim
where artist_type = 'NON-ARTIST'
and twitter_handle_1 is not null
union
select twitter_handle_2, artist_name from warehouse.artist_dim
where artist_type = 'NON-ARTIST'
and twitter_handle_2 is not null
union
select twitter_handle_3, artist_name from warehouse.artist_dim
where artist_type = 'NON-ARTIST'
and twitter_handle_3 is not null
union
select twitter_handle_4, artist_name from warehouse.artist_dim
where artist_type = 'NON-ARTIST'
and twitter_handle_4 is not null
union
select twitter_handle_5, artist_name from warehouse.artist_dim
where artist_type = 'NON-ARTIST'
and twitter_handle_5 is not null
) c
on a.twitter_handle = c.twitter_handle
)
, artist_twitter_associations as
(select
a.tweet_id
,a.tweet_conversation_id
,a.twitter_author_id
,b.twitter_id
,b.twitter_handle
,c.artist_name
,a.tweet_url
,a.tweet_expanded_url
,a.tweet_display_url
,a.tweet_unwound_url
,a.tweet_text
,a.tweet_url_status
,a.tweet_url_title
,a.tweet_url_description
,a.tweet_public_retweet_count
,a.tweet_public_reply_count
,a.tweet_public_like_count
,a.tweet_public_quote_count
,a.as_of_datetime
,a.tweet_date
,row_number() over (partition by a.tweet_id order by a.as_of_datetime desc) as rank
from stage.twitter_tweet_history a
left join artist_twitter b
on a.twitter_author_id = b.twitter_id
left join twitter_handle_artist_cnt_ranked c
on b.twitter_handle = c.twitter_handle
group by
a.tweet_id
,a.tweet_conversation_id
,a.twitter_author_id
,b.twitter_id
,b.twitter_handle
,c.artist_name
,a.tweet_url
,a.tweet_expanded_url
,a.tweet_display_url
,a.tweet_unwound_url
,a.tweet_text
,a.tweet_url_status
,a.tweet_url_title
,a.tweet_url_description
,a.tweet_public_retweet_count
,a.tweet_public_reply_count
,a.tweet_public_like_count
,a.tweet_public_quote_count
,a.as_of_datetime
,a.tweet_date
)
,ranked_tweet_associations as
(
select
tweet_id
,tweet_conversation_id
,twitter_author_id
,twitter_id
,twitter_handle
,artist_name
,tweet_url
,tweet_expanded_url
,tweet_display_url
,tweet_unwound_url
,tweet_text
,tweet_url_status
,tweet_url_title
,tweet_url_description
,tweet_public_retweet_count
,tweet_public_reply_count
,tweet_public_like_count
,tweet_public_quote_count
,as_of_datetime
,tweet_date
from artist_twitter_associations
where rank = 1
)
, derive_link_info as
(select tweet_id, twitter_author_id, twitter_handle, artist_name,
as_of_datetime, tweet_date,
case when tweet_unwound_url is null
then tweet_expanded_url
else tweet_unwound_url end as url_to_parse
from ranked_tweet_associations
)
, split_url as
(
select tweet_id, twitter_author_id, twitter_handle, artist_name
, as_of_datetime, tweet_date
, url_to_parse
, split_part(url_to_parse, '/', 0) as site_url_0
, split_part(url_to_parse, '/', 1) as site_url_1
, split_part(url_to_parse, '/', 2) as site_url_2
, split_part(url_to_parse, '/', 3) as site_url_3
, split_part(url_to_parse, '/', 4) as site_url_4
, split_part(url_to_parse, '/', 5) as site_url_5
, split_part(url_to_parse, '/', 6) as site_url_6
, split_part(url_to_parse, '/', 7) as site_url_7
, case when site_url_3 = 'niftygateway.com'
then 'nifty'
else 'other' end as site
, case when site = 'nifty' and site_url_4 = 'collections'
then site_url_5
when site = 'nifty' and site_url_4 = 'profile'
then site_url_5
when site = 'nifty' and site_url_4 = 'itemdetail'
then site_url_6
else 'Other'
end as artist_info
from derive_link_info
)
,identify_art_or_artist as
(
select a.tweet_id
, a1.tweet_conversation_id
, a.twitter_author_id
, a.twitter_handle
, a.artist_name as tweet_artist_name
, a.as_of_datetime
, a.tweet_date
, a1.tweet_text
, a1.tweet_public_retweet_count
, a1.tweet_public_reply_count
, a1.tweet_public_like_count
, a1.tweet_public_quote_count
,a1.tweet_url
,a1.tweet_expanded_url
,a1.tweet_display_url
,a1.tweet_unwound_url
,a1.tweet_url_status
,a1.tweet_url_title
,a1.tweet_url_description
, a.url_to_parse
, a.site_url_4
, a.site_url_5
, a.site_url_6
, a.site_url_7
, a.site
, case when a.site = 'nifty'
then
case when a.site_url_4 = 'itemdetail' and a.site_url_5 = 'primary'
then b.artist_name
when a.site_url_4 = 'itemdetail' and a.site_url_5 = 'secondary'
then c.artist_name
when a.site_url_4 = 'profile'
then a.site_url_5
when a.site_url_4 = 'collections'
then d.artist_name
end
else 'Other' end as art_artist_name
, case when a.site = 'nifty'
then case when a.site_url_4 = 'itemdetail' and a.site_url_5 = 'primary'
then b.collection_contract_uid
when a.site_url_4 = 'itemdetail' and a.site_url_5 = 'secondary'
then c.collection_contract_uid
when a.site_url_4 = 'profile'
then 'None'
when a.site_url_4 = 'collections'
then 'None'
end
else 'None' end as collection_contract_uid
, case when a.site = 'nifty'
then case when a.site_url_4 = 'itemdetail' and a.site_url_5 = 'primary'
then b.collection_item_id
when a.site_url_4 = 'itemdetail' and a.site_url_5 = 'secondary'
then 'None'
when a.site_url_4 = 'profile'
then 'None'
when a.site_url_4 = 'collections'
then 'None'
end
else 'None' end as collection_item_id
, case when a.site = 'nifty'
then case when a.site_url_4 = 'itemdetail' and a.site_url_5 = 'primary'
then 'None'
when a.site_url_4 = 'itemdetail' and a.site_url_5 = 'secondary'
then a.site_url_7
when a.site_url_4 = 'profile'
then 'None'
when a.site_url_4 = 'collections'
then 'None'
end
else 'None' end as token_id
from split_url a
inner join ranked_tweet_associations a1
on a.tweet_id = a1.tweet_id
left join warehouse.art_dim b
on a.site_url_6 = b.collection_contract_uid
and a.site_url_7 = b.collection_item_id
left join ( select collection_contract_uid, artist_name
from warehouse.art_dim
group by collection_contract_uid, artist_name) c
on a.site_url_6 = c.collection_contract_uid
left join (select artist_name from warehouse.art_dim
group by artist_name) d
on a.site_url_5 = d.artist_name
)
select
a.tweet_id
,a.collection_contract_uid
,a.collection_item_id
,a.token_id
,a.tweet_artist_name
,a.twitter_handle as tweet_twitter_handle
,a.art_artist_name
,NVL(b.art_name,'None') as art_name
,a.tweet_text
,a.tweet_conversation_id
,a.twitter_author_id
,a.tweet_date
,a.tweet_public_retweet_count
,a.tweet_public_reply_count
,a.tweet_public_like_count
,a.tweet_public_quote_count
,a.tweet_url
,a.tweet_expanded_url
,a.tweet_display_url
,a.tweet_unwound_url
,a.tweet_url_status
,a.tweet_url_title
,a.tweet_url_description
,a.as_of_datetime
,current_timestamp
from identify_art_or_artist a
left join warehouse.art_dim b
on a.collection_contract_uid = b.collection_contract_uid
and a.collection_item_id = b.collection_item_id
) s
ON s.tweet_id = t.tweet_id
AND s.collection_contract_uid = t.collection_contract_uid
AND s.collection_item_id = t.collection_item_id
AND s.token_id = t.token_id
AND s.tweet_artist_name = t.tweet_artist_name
AND s.tweet_twitter_handle = t.tweet_twitter_handle
AND s.art_artist_name = t.art_artist_name
AND NVL(s.art_name,'None') = NVL(t.art_name,'None')
AND s.tweet_text = t.tweet_text
AND s.tweet_conversation_id = t.tweet_conversation_id
AND s.twitter_author_id = t.twitter_author_id
AND s.tweet_date = t.tweet_date
AND NVL(s.tweet_url,'x') = NVL(t.tweet_url,'x')
AND NVL(s.tweet_expanded_url,'x') = NVL(t.tweet_expanded_url,'x')
AND NVL(s.tweet_display_url,'x') = NVL(t.tweet_display_url,'x')
AND NVL(s.tweet_unwound_url,'x') = NVL(t.tweet_unwound_url,'x')
AND NVL(s.tweet_url_status,'x') = NVL(t.tweet_url_status,'x')
AND NVL(s.tweet_url_title,'x') = NVL(t.tweet_url_title,'x')
AND NVL(s.tweet_url_description,'x') = NVL(t.tweet_url_description,'x')
AND s.tweet_public_retweet_count = t.tweet_public_retweet_count
AND s.tweet_public_reply_count = t.tweet_public_reply_count
AND s.tweet_public_like_count = t.tweet_public_like_count
AND s.tweet_public_quote_count = t.tweet_public_quote_count
WHEN NOT MATCHED THEN INSERT
(
tweet_id
,collection_contract_uid
,collection_item_id
,token_id
,tweet_artist_name
,tweet_twitter_handle
,art_artist_name
,art_name
,tweet_text
,tweet_conversation_id
,twitter_author_id
,tweet_date
,tweet_public_retweet_count
,tweet_public_reply_count
,tweet_public_like_count
,tweet_public_quote_count
,tweet_url
,tweet_expanded_url
,tweet_display_url
,tweet_unwound_url
,tweet_url_status
,tweet_url_title
,tweet_url_description
,as_of_datetime
,insert_date
)
VALUES
(s.tweet_id
,s.collection_contract_uid
,s.collection_item_id
,s.token_id
,s.tweet_artist_name
,s.tweet_twitter_handle
,s.art_artist_name
,s.art_name
,s.tweet_text
,s.tweet_conversation_id
,s.twitter_author_id
,s.tweet_date
,s.tweet_public_retweet_count
,s.tweet_public_reply_count
,s.tweet_public_like_count
,s.tweet_public_quote_count
,s.tweet_url
,s.tweet_expanded_url
,s.tweet_display_url
,s.tweet_unwound_url
,s.tweet_url_status
,s.tweet_url_title
,s.tweet_url_description
,s.as_of_datetime
,current_timestamp)
;
`
var stmt = snowflake.createStatement(
{
sqlText: sql_command
}
);
try {
var result1 = stmt.execute();
result1.next();
return "Succeeded."; // Return a success/error indicator.
}
catch (err) {
return "Failed: " + err; // Return a success/error indicator.
}
$$
;
我以前运行过这个过程的另一个版本(类似的代码只是由于数据和字段的更改而进行了一些代码更改(，它运行得很好。我可能会遇到性格限制吗？我看不出这次失败的任何其他原因。
这是我收到的错误：
失败：SQL编译错误：错误行11位于位置12标识符"TWITTER_HANDLE"无效
所以我的解决方案是将所有CTE代码提取到一个视图中，并在USING之后引用该视图。这似乎有效，所以我想知道Snowflake是否对程序有某种性格限制。到目前为止，我在互联网上的搜索中还没有看到任何提及。所以，我想这个问题现在已经解决了。