The error we get in the Snowflake is this:
SQL execution internal error: Processing aborted due to error 300010:3898809911; incident 7639893.
Perhaps there is error with one of the columns. Is there any way to recreate temporary table that is generated by snowplow loading process. This tmp table is requested by Snowflake supporting team, so they can look into the data.
What was the query / queries being executed at the time that this was being executed? Hopefully it’s an ephemeral issue on the Snowflake side of things but unfortunately the error message doesn’t give a lot away.
Hi @vladimir.ilic,
It might be either a transient error and a retry should help, or it might be related to a length of particular fields and associated columns.
Thanks,
It’s happening every run, and this is the query:
INSERT INTO atomic.events
(app_id, platform, etl_tstamp, collector_tstamp, dvce_created_tstamp, event, event_id, txn_id, name_tracker, v_tracker,
v_collector, v_etl, user_id, user_ipaddress, user_fingerprint, domain_userid, domain_sessionidx, network_userid,
geo_country, geo_region, geo_city, geo_zipcode, geo_latitude, geo_longitude, geo_region_name, ip_isp, ip_organization,
ip_domain, ip_netspeed, page_url, page_title, page_referrer, page_urlscheme, page_urlhost, page_urlport, page_urlpath,
page_urlquery, page_urlfragment, refr_urlscheme, refr_urlhost, refr_urlport, refr_urlpath, refr_urlquery,
refr_urlfragment, refr_medium, refr_source, refr_term, mkt_medium, mkt_source, mkt_term, mkt_content, mkt_campaign,
se_category, se_action, se_label, se_property, se_value, tr_orderid, tr_affiliation, tr_total, tr_tax, tr_shipping,
tr_city, tr_state, tr_country, ti_orderid, ti_sku, ti_name, ti_category, ti_price, ti_quantity, pp_xoffset_min,
pp_xoffset_max, pp_yoffset_min, pp_yoffset_max, useragent, br_name, br_family, br_version, br_type, br_renderengine,
br_lang, br_features_pdf, br_features_flash, br_features_java, br_features_director, br_features_quicktime,
br_features_realplayer, br_features_windowsmedia, br_features_gears, br_features_silverlight, br_cookies,
br_colordepth, br_viewwidth, br_viewheight, os_name, os_family, os_manufacturer, os_timezone, dvce_type, dvce_ismobile,
dvce_screenwidth, dvce_screenheight, doc_charset, doc_width, doc_height, tr_currency, tr_total_base, tr_tax_base,
tr_shipping_base, ti_currency, ti_price_base, base_currency, geo_timezone, mkt_clickid, mkt_network, etl_tags,
dvce_sent_tstamp, refr_domain_userid, refr_dvce_tstamp, domain_sessionid, derived_tstamp, event_vendor, event_name,
event_format, event_version, event_fingerprint, true_tstamp,
unstruct_event_com_snowplowanalytics_snowplow_add_to_cart_1,
unstruct_event_com_snowplowanalytics_snowplow_application_error_1, contexts_com_quantdeck_ttc_voucher_code_context_1,
unstruct_event_com_snowplowanalytics_snowplow_link_click_1, contexts_com_quantdeck_promocode_cookie_pool_context_1,
contexts_com_quantdeck_ttc_affiliate_window_context_2, contexts_com_quantdeck_ttc_affiliate_basket_reached_1,
contexts_com_snowplowanalytics_snowplow_ua_parser_context_1, contexts_com_google_analytics_cookies_1,
contexts_com_snowplowanalytics_snowplow_web_page_1)
SELECT enriched_data:app_id::VARCHAR(255),
enriched_data:platform::VARCHAR(255),
enriched_data:etl_tstamp::TIMESTAMP,
enriched_data:collector_tstamp::TIMESTAMP,
enriched_data:dvce_created_tstamp::TIMESTAMP,
enriched_data:event::VARCHAR(128),
enriched_data:event_id::CHAR(36),
enriched_data:txn_id::INTEGER,
enriched_data:name_tracker::VARCHAR(128),
enriched_data:v_tracker::VARCHAR(100),
enriched_data:v_collector::VARCHAR(100),
enriched_data:v_etl::VARCHAR(100),
enriched_data:user_id::VARCHAR(255),
enriched_data:user_ipaddress::VARCHAR(45),
enriched_data:user_fingerprint::VARCHAR(50),
enriched_data:domain_userid::VARCHAR(36),
enriched_data:domain_sessionidx::SMALLINT,
enriched_data:network_userid::VARCHAR(38),
enriched_data:geo_country::CHAR(2),
enriched_data:geo_region::CHAR(2),
enriched_data:geo_city::VARCHAR(75),
enriched_data:geo_zipcode::VARCHAR(15),
enriched_data:geo_latitude::DOUBLE PRECISION,
enriched_data:geo_longitude::DOUBLE PRECISION,
enriched_data:geo_region_name::VARCHAR(100),
enriched_data:ip_isp::VARCHAR(100),
enriched_data:ip_organization::VARCHAR(100),
enriched_data:ip_domain::VARCHAR(100),
enriched_data:ip_netspeed::VARCHAR(100),
enriched_data:page_url::VARCHAR(4096),
enriched_data:page_title::VARCHAR(2000),
enriched_data:page_referrer::VARCHAR(4096),
enriched_data:page_urlscheme::VARCHAR(16),
enriched_data:page_urlhost::VARCHAR(255),
enriched_data:page_urlport::INTEGER,
enriched_data:page_urlpath::VARCHAR(3000),
enriched_data:page_urlquery::VARCHAR(6000),
enriched_data:page_urlfragment::VARCHAR(3000),
enriched_data:refr_urlscheme::VARCHAR(16),
enriched_data:refr_urlhost::VARCHAR(255),
enriched_data:refr_urlport::INTEGER,
enriched_data:refr_urlpath::VARCHAR(6000),
enriched_data:refr_urlquery::VARCHAR(6000),
enriched_data:refr_urlfragment::VARCHAR(3000),
enriched_data:refr_medium::VARCHAR(25),
enriched_data:refr_source::VARCHAR(50),
enriched_data:refr_term::VARCHAR(255),
enriched_data:mkt_medium::VARCHAR(255),
enriched_data:mkt_source::VARCHAR(255),
enriched_data:mkt_term::VARCHAR(255),
enriched_data:mkt_content::VARCHAR(500),
enriched_data:mkt_campaign::VARCHAR(255),
enriched_data:se_category::VARCHAR(1000),
enriched_data:se_action::VARCHAR(1000),
enriched_data:se_label::VARCHAR(1000),
enriched_data:se_property::VARCHAR(1000),
enriched_data:se_value::DOUBLE PRECISION,
enriched_data:tr_orderid::VARCHAR(255),
enriched_data:tr_affiliation::VARCHAR(255),
enriched_data:tr_total::NUMBER(18, 2),
enriched_data:tr_tax::NUMBER(18, 2),
enriched_data:tr_shipping::NUMBER(18, 2),
enriched_data:tr_city::VARCHAR(255),
enriched_data:tr_state::VARCHAR(255),
enriched_data:tr_country::VARCHAR(255),
enriched_data:ti_orderid::VARCHAR(255),
enriched_data:ti_sku::VARCHAR(255),
enriched_data:ti_name::VARCHAR(255),
enriched_data:ti_category::VARCHAR(255),
enriched_data:ti_price::NUMBER(18, 2),
enriched_data:ti_quantity::INTEGER,
enriched_data:pp_xoffset_min::INTEGER,
enriched_data:pp_xoffset_max::INTEGER,
enriched_data:pp_yoffset_min::INTEGER,
enriched_data:pp_yoffset_max::INTEGER,
enriched_data:useragent::VARCHAR(1000),
enriched_data:br_name::VARCHAR(50),
enriched_data:br_family::VARCHAR(50),
enriched_data:br_version::VARCHAR(50),
enriched_data:br_type::VARCHAR(50),
enriched_data:br_renderengine::VARCHAR(50),
enriched_data:br_lang::VARCHAR(255),
enriched_data:br_features_pdf::BOOLEAN,
enriched_data:br_features_flash::BOOLEAN,
enriched_data:br_features_java::BOOLEAN,
enriched_data:br_features_director::BOOLEAN,
enriched_data:br_features_quicktime::BOOLEAN,
enriched_data:br_features_realplayer::BOOLEAN,
enriched_data:br_features_windowsmedia::BOOLEAN,
enriched_data:br_features_gears::BOOLEAN,
enriched_data:br_features_silverlight::BOOLEAN,
enriched_data:br_cookies::BOOLEAN,
enriched_data:br_colordepth::VARCHAR(12),
enriched_data:br_viewwidth::INTEGER,
enriched_data:br_viewheight::INTEGER,
enriched_data:os_name::VARCHAR(50),
enriched_data:os_family::VARCHAR(50),
enriched_data:os_manufacturer::VARCHAR(50),
enriched_data:os_timezone::VARCHAR(255),
enriched_data:dvce_type::VARCHAR(50),
enriched_data:dvce_ismobile::BOOLEAN,
enriched_data:dvce_screenwidth::INTEGER,
enriched_data:dvce_screenheight::INTEGER,
enriched_data:doc_charset::VARCHAR(128),
enriched_data:doc_width::INTEGER,
enriched_data:doc_height::INTEGER,
enriched_data:tr_currency::CHAR(3),
enriched_data:tr_total_base::NUMBER(18, 2),
enriched_data:tr_tax_base::NUMBER(18, 2),
enriched_data:tr_shipping_base::NUMBER(18, 2),
enriched_data:ti_currency::CHAR(3),
enriched_data:ti_price_base::NUMBER(18, 2),
enriched_data:base_currency::CHAR(3),
enriched_data:geo_timezone::VARCHAR(64),
enriched_data:mkt_clickid::VARCHAR(128),
enriched_data:mkt_network::VARCHAR(64),
enriched_data:etl_tags::VARCHAR(500),
enriched_data:dvce_sent_tstamp::TIMESTAMP,
enriched_data:refr_domain_userid::VARCHAR(36),
enriched_data:refr_dvce_tstamp::TIMESTAMP,
enriched_data:domain_sessionid::CHAR(36),
enriched_data:derived_tstamp::TIMESTAMP,
enriched_data:event_vendor::VARCHAR(1000),
enriched_data:event_name::VARCHAR(1000),
enriched_data:event_format::VARCHAR(128),
enriched_data:event_version::VARCHAR(128),
enriched_data:event_fingerprint::VARCHAR(128),
enriched_data:true_tstamp::TIMESTAMP,
enriched_data:unstruct_event_com_snowplowanalytics_snowplow_add_to_cart_1::OBJECT,
enriched_data:unstruct_event_com_snowplowanalytics_snowplow_application_error_1::OBJECT,
enriched_data:contexts_com_quantdeck_ttc_voucher_code_context_1::ARRAY,
enriched_data:unstruct_event_com_snowplowanalytics_snowplow_link_click_1::OBJECT,
enriched_data:contexts_com_quantdeck_promocode_cookie_pool_context_1::ARRAY,
enriched_data:contexts_com_quantdeck_ttc_affiliate_window_context_2::ARRAY,
enriched_data:contexts_com_quantdeck_ttc_affiliate_basket_reached_1::ARRAY,
enriched_data:contexts_com_snowplowanalytics_snowplow_ua_parser_context_1::ARRAY,
enriched_data:contexts_com_google_analytics_cookies_1::ARRAY,
enriched_data:contexts_com_snowplowanalytics_snowplow_web_page_1::ARRAY
FROM atomic.snowplow_tmp_run_2021_03_12_00_30_34
Every time is the same table: atomic.snowplow_tmp_run_2021_03_12_00_30_34
Thanks for the details @vladimir.ilic.
It’s most likely the latter scenario then. I would advise that you:
se_label column up to 4096 singlebyte characters by executingALTER TABLE atomic.events ALTER COLUMN se_label TYPE VARCHAR(4096);
Note: we have seen in the past that there might be issues with this column. You might need to update schema’s name according to your configuration.
Issue is:
enriched_data:os_timezone::VARCHAR(255)
This is the value I get for that one
EuropeVmtkWEJsVW5WdU9rRmpkR2x2YmlCdWIzUWdabTkxYm1RdUlpd2ljM1JoWTJ0VWNtRmpaU0k2SWtWeWNtOXlPaUJCWTNScGIyNGdibTkwSUdadmRXNWtMbHh1SUNBZ0lHRjBJR0lnS0doMGRIQnpPaTh2WkROeVkzRnFOVEpvWTIxNGVua3VZMnh2ZFdSbWNtOXVkQzV1WlhRdmNtSm1hSFY2Ym1sb1lTOXBiblJsY21ac2J5MXBaVzF2WWk1eWRXNHVhbk02TkRjNk16QXpLVnh1SUNBZ0lHRjBJRTlpYW1WamRDNXdjbTlqWlhOelZHRm5jeUFvYUhSMGNITTZMeTlrTTNKamNXbzFNbWhqYlhoNmVTNWpiRzkxWkdaeWIyNTBMbTVsZEM5eVltWm9kWHB1YVdoaEwybHVkR1Z5Wm14dkxXbGxiVzlpTG5KMWJpNXFjem8xT1RvMU1EQXBYRzRnSUNBZ1lYUWdUMkpxWldOMExtRXVTVzUwWlhKbWJHOHVjblZ1VkdGbmN5QW9hSFIwY0hNNkx5OWtNM0pqY1dvMU1taGpiWGg2ZVM1amJHOTFaR1p5YjI1MExtNWxkQzl5WW1ab2RYcHVhV2hoTDJsdWRHVnlabXh2TFdsbGJXOWlMbkoxYmk1cWN6bzVNVG94TURFcFhHNGdJQ0FnWVhRZ2FIUjBjSE02THk5a00zSmpjV28xTW1oamJYaDZlUzVqYkc5MVpHWnliMjUwTG01bGRDOXlZbVpvZFhwdWFXaGhMMmx1ZEdWeVpteHZMV2xsYlc5aUxuSjFiaTVxY3pvNU1Ub3lNRFZjYmlBZ0lDQmhkQ0JvZEhSd2N6b3ZMMlF6Y21OeGFqVXlhR050ZUhwNUxtTnNiM1ZrWm5KdmJuUXVibVYwTDNKaVptaDFlbTVwYUdFdmFXNTBaWEptYkc4dGFXVnRiMkl1Y25WdUxtcHpPamt4T2pJeE5pSXNJbXhwYm1WT2RXMWlaWElpT201MWJHd3NJbXhwYm1WRGIyeDFiVzRpT201MWJHd3NJbVpwYkdWT1lXMWxJam9pZEdGbmJXRnVZV2RsY2k1cWN5SjlmWDAiLCJ0diI6ImpzLTIuMTIuMCIsInRuYSI6InFkY2xqMSIsImFpZCI6ImludGVyZmxvLWllLW1vYiIsInAiOiJ3ZWIiLCJ0eiI6IkV1cm9wZS9Mb25kb24iLCJsYW5nIjoiZW4tR0IiLCJjcyI6IlVURi04IiwicmVzIjoiMzYweDc4MCIsImNkIjoiMjQiLCJjb29raWUiOiIxIiwiZWlkIjoiZTM4ZTI3MDEtMGFhZS00MTBlLTlhY2QtOGJkNmM3YjNlYjU2IiwiZHRtIjoiMTYxNTQ2MDQ3ODQ4MSIsImN4IjoiZXlKelkyaGxiV0VpT2lKcFoyeDFPbU52YlM1emJtOTNjR3h2ZDJGdVlXeDVkR2xqY3k1emJtOTNjR3h2ZHk5amIyNTBaWGgwY3k5cWMyOXVjMk5vWlcxaEx6RXRNQzB3SWl3aVpHRjBZU0k2VzNzaWMyTm9aVzFoSWpvaWFXZHNkVHBqYjIwdVoyOXZaMnhsTG1GdVlXeDVkR2xqY3k5amIyOXJhV1Z6TDJwemIyNXpZMmhsYldFdk1TMHdMVEFpTENKa1lYUmhJanA3SWw5bllTSTZJa2RCTVM0eUxqRTRNek01TmpJMk1UUXVNVFl4TlRRMk1ESTJPQ0o5ZlN4N0luTmphR1Z0WVNJNkltbG5iSFU2WTI5dExuTnViM2R3Ykc5M1lXNWhiSGwwYVdOekxuTnViM2R3Ykc5M0wzZGxZbDl3WVdkbEwycHpiMjV6WTJobGJXRXZNUzB3TFRBaUxDSmtZWFJoSWpwN0ltbGtJam9pTmpNeFl6STVNMk10TnpSa05pMDBaRFV3TFdJd00yWXRZak15WWpOaFpqQmxZV1l4SW4xOVhYMCIsInZwIjoiMzYweDY1OSIsImRzIjoiMzYweDU1OTkiLCJ2aWQiOiIxIiwic2lkIjoiNmUyODQ3MjUtY2I1Yy00OWM3LTk0NWUtZTE4NGY1ZDM5NDRkIiwiZHVpZCI6IjBiN2NjNmY3LTFjMjYtNDM5Ny05ZWJhLTY1YWZiY2UzM2YyYyIsImZwIjoiMzU2MTkzNTYyNCIsInJlZnIiOiJodHRwczovL3d3dy5pbnRlcmZsb3JhLmllL2NhdGVnb3J5L2hwLWNhdGVnb3J5Lz9zb3J0PXByaWNlK2FzYyZjYWNoZV9rZXk9MjAyMSUyRjAzJTJGMTFfaW5jaF93ZXhmb3JkXzE2MTU0NjAzNzg0NDUmZnE9Zl9vY2Nhc2lvbiUzQSUyMk1vdGhlciIsInVybCI6Imh0dHBzOi8vd3d3LmludGVyZmxvcmEuaWUvY2F0ZWdvcnkvYmlydGhkYXktZmxvd2Vycy8iLCJzdG0iOiIxNjE1NDYwNDc4ODE0In1dfQ
Any idea how to fix it? Doesn’t help resizing the os_timezone column in atomic.events, because enriched_data varchar size remains 255.
Thanks
Hi @vladimir.ilic,
The value looks strange, but, despite this, I expect that it should be truncated at data loading step. As stated earlier, you need to use the latest Snowflake Loader version where a similar issue has been addressed.
Hope this helps.