UA Parser is not getting into Snowflake dabase

For engineers Enrichment
1

I recently configured and started using Snowplow at my new company. I have several enrichments running but I can’t seem to get the UA Parser (newest version) to generate data into the table columns. The JSON is inserted into the proper column but it is not getting shredded into the columns. Can some one point me in the right direction? I used the JSON definition for the enrichment specified on the wiki (see below). Campaign attribution, ip_lookups, and referrals are working properly. Thanks in advance!

{
"schema": "iglu:com.snowplowanalytics.snowplow/ua_parser_config/jsonschema/1-0-1",

"data": {

    "vendor": "com.snowplowanalytics.snowplow",
    "name": "ua_parser_config",
    "enabled": true,
    "parameters": {
        "database": "regexes-latest.yaml",
        "uri": "s3://snowplow-hosted-assets/third-party/ua-parser/"
    }
}

}

I’m getting enriched events like the below

{
  "schema": {
    "vendor": "com.snowplowanalytics.snowplow",
    "name": "ua_parser_context",
    "format": "jsonschema",
    "version": "1-0-0"
  },
  "data": {
    "useragentFamily": "Chrome",
    "useragentMajor": "75",
    "useragentMinor": "0",
    "useragentPatch": "3770",
    "useragentVersion": "Chrome 75.0.3770",
    "osFamily": "Windows",
    "osMajor": "10",
    "osMinor": null,
    "osPatch": null,
    "osPatchMinor": null,
    "osVersion": "Windows 10",
    "deviceFamily": "Other"
  },
  "hierarchy": {
    "rootId": "33ca2e51-1651-4f80-8aad-4ee5f6faee1a",
    "rootTstamp": "2019-07-13 18:57:32.000",
    "refRoot": "events",
    "refTree": [
      "events",
      "ua_parser_context"
    ],
    "refParent": "events"
  }
}
2

@sonnypolaris, have you checked “shredded/bad” bucket? The evaluation of the derived contexts is executed during shredding. If something is off, the event is rejected and placed in “shredded/bad” bucket.

3

I’m getting the shredded events in ‘Good’ folder
/my-snowplow-data/events/shredded/archive/run=2019-07-14-23-23-52/shredded-types/ (see above sample)

It just looks like the snowflake data loader does not insert the data into the column. I can run an update statement and get the data into the columns

update atomic.events e
set e.dvce_type = u.dvce_type,
    e.os_Family = u.os_Family,
    e.os_Name = u.os_Name,
    e.br_name = u.br_name, 
    e.br_family = u.br_family,
    e.br_version = u.br_version
from
  (select 
         domain_userid,
         collector_tstamp,
         e.event_id,
         CONTEXTS_COM_SNOWPLOWANALYTICS_SNOWPLOW_UA_PARSER_CONTEXT_1[0] ua_data,
         CONTEXTS_COM_SNOWPLOWANALYTICS_SNOWPLOW_UA_PARSER_CONTEXT_1[0].deviceFamily as dvce_type,
         CONTEXTS_COM_SNOWPLOWANALYTICS_SNOWPLOW_UA_PARSER_CONTEXT_1[0].osFamily as os_Family,
         CONTEXTS_COM_SNOWPLOWANALYTICS_SNOWPLOW_UA_PARSER_CONTEXT_1[0].osVersion as os_name,
         CONTEXTS_COM_SNOWPLOWANALYTICS_SNOWPLOW_UA_PARSER_CONTEXT_1[0].useragentVersion as br_name,
         CONTEXTS_COM_SNOWPLOWANALYTICS_SNOWPLOW_UA_PARSER_CONTEXT_1[0].useragentFamily as br_family,
         CONTEXTS_COM_SNOWPLOWANALYTICS_SNOWPLOW_UA_PARSER_CONTEXT_1[0].useragentMajor || '.' || CONTEXTS_COM_SNOWPLOWANALYTICS_SNOWPLOW_UA_PARSER_CONTEXT_1[0].useragentMinor as br_version
   from atomic.events e
   where e.CONTEXTS_COM_SNOWPLOWANALYTICS_SNOWPLOW_UA_PARSER_CONTEXT_1 is not null
     and e.br_name is null ) u
where e.event_id = u.event_id;
4

@sonnypolaris, this is a different story. The shredder is not used in Snowflake DB loading processes as the enriched and transformed data is loaded as part of events payload. Why are you shredding in the first place? Are you also loading the data into Redshift? Is it loaded to Redshift OK if so?

The issue is rather with the enrichment. I think I understand now what you meant by “not getting shredded into the columns”. It looks like there’s no data in that column. If it’s mobile data then there won’t be useragent to apply the enrichment to. I’m not sure what else could be wrong.

You can ensure that the bucket is accessible from where you run the pipeline by using region-related bucket: https://github.com/snowplow/snowplow/wiki/Hosted-assets#7-s3-hosted-asset-bucket-per-region.

5

I understand the part where Snowflake is not using the shredder. Got it!

In my enrichment configuration, I just used the default value which my not be correct. I corrected the value to be
s3://snowplow-hosted-assets-us-east-1/third-party/ua-parser/

This should work. I’ll do another run and verify.

Thanks