Snowplow Postgres Loader Error

Hi!

I’m trying to run the snowplow postgres loader but I’m getting the following error:

{
  "schema": "iglu:com.snowplowanalytics.snowplow.badrows/loader_iglu_error/jsonschema/2-0-0",
  "data": {
    "processor": { "artifact": "snowplow-postgres-loader", "version": "0.1.0" },
    "failure": [
      {
        "schemaCriterion": "iglu:com.snowplowanalytics.snowplow/web_page/jsonschema/1-*-*",
        "error": {
          "error": "ResolutionError",
          "lookupHistory": [
            {
              "repository": "Iglu Central",
              "errors": [{ "error": "NotFound" }],
              "attempts": 2,
              "lastAttempt": "2020-10-27T14:33:07.242Z"
            },
            {
              "repository": "Iglu Central - Mirror 01",
              "errors": [{ "error": "NotFound" }],
              "attempts": 1,
              "lastAttempt": "2020-10-27T14:33:06.748Z"
            },
            {
              "repository": "Iglu Client Embedded",
              "errors": [{ "error": "NotFound" }],
              "attempts": 1,
              "lastAttempt": "2020-10-27T14:33:06.749Z"
            }
          ]
        }
      }
    ],
    "payload": {
      "app_id": "website",
      "platform": "web",
      "etl_tstamp": "2020-10-26T14:33:06.312Z",
      "collector_tstamp": "2020-10-26T14:33:04.610Z",
      "dvce_created_tstamp": "2020-10-26T14:33:00.909Z",
      "event": "page_ping",
      "event_id": "4f87b4c2-87df-417d-85d2-412a6cf80016",
      "txn_id": null,
      "name_tracker": "cf",
      "v_tracker": "js-2.10.2",
      "v_collector": "ssc-0.15.0-kinesis",
      "v_etl": "stream-enrich-0.21.0-common-0.37.0",
      "user_id": null,
      "user_ipaddress": "xxxxxxxxxxx",
      "user_fingerprint": "129911700",
      "domain_userid": "16d83422-4de2-4ba7-9a5e-54e0e018d71e",
      "domain_sessionidx": 2,
      "network_userid": "88fba23f-9ee2-4631-8cb8-278dc33e327d",
      "geo_country": null,
      "geo_region": null,
      "geo_city": null,
      "geo_zipcode": null,
      "geo_latitude": null,
      "geo_longitude": null,
      "geo_region_name": null,
      "ip_isp": null,
      "ip_organization": null,
      "ip_domain": null,
      "ip_netspeed": null,
      "page_url": "https://www.raizs.com.br/ovos-organicos-grande--alimentos-delivery-sp/p?idsku=463&gclid=Cj0KCQjwxNT8BRD9ARIsAJ8S5xbyHgYlSZdZnQu5viMMs3BcOKgzRe6jSN3v_klEyHZBvx-55CYZnr4aAveVEALw_wcB",
      "page_title": "Ovos Orgânicos 10 unidades - Fazenda da Toca - Raizs",
      "page_referrer": null,
      "page_urlscheme": "https",
      "page_urlhost": "www.raizs.com.br",
      "page_urlport": 443,
      "page_urlpath": "/",
      "page_urlquery": "idsku=463&gclid=Cj0KCQjwxNT8BRD9ARIsAJ8S5xbyHgYlSZdZnQu5viMMs3BcOKgzRe6jSN3v_klEyHZBvx-55CYZnr4aAveVEALw_wcB",
      "page_urlfragment": null,
      "refr_urlscheme": null,
      "refr_urlhost": null,
      "refr_urlport": null,
      "refr_urlpath": null,
      "refr_urlquery": null,
      "refr_urlfragment": null,
      "refr_medium": null,
      "refr_source": null,
      "refr_term": null,
      "mkt_medium": null,
      "mkt_source": null,
      "mkt_term": null,
      "mkt_content": null,
      "mkt_campaign": null,
      "contexts": {
        "schema": "iglu:com.snowplowanalytics.snowplow/contexts/jsonschema/1-0-0",
        "data": [
          {
            "schema": "iglu:com.snowplowanalytics.snowplow/web_page/jsonschema/1-0-0",
            "data": { "id": "3b2cdd37-fa52-4d0d-a71f-941ce6772283" }
          }
        ]
      },
      "se_category": null,
      "se_action": null,
      "se_label": null,
      "se_property": null,
      "se_value": null,
      "unstruct_event": null,
      "tr_orderid": null,
      "tr_affiliation": null,
      "tr_total": null,
      "tr_tax": null,
      "tr_shipping": null,
      "tr_city": null,
      "tr_state": null,
      "tr_country": null,
      "ti_orderid": null,
      "ti_sku": null,
      "ti_name": null,
      "ti_category": null,
      "ti_price": null,
      "ti_quantity": null,
      "pp_xoffset_min": 0,
      "pp_xoffset_max": 0,
      "pp_yoffset_min": 428,
      "pp_yoffset_max": 771,
      "useragent": "Mozilla/5.0 (Linux; Android 8.0.0; SM-G570M) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.110 Mobile Safari/537.36",
      "br_name": null,
      "br_family": null,
      "br_version": null,
      "br_type": null,
      "br_renderengine": null,
      "br_lang": "pt-BR",
      "br_features_pdf": null,
      "br_features_flash": null,
      "br_features_java": null,
      "br_features_director": null,
      "br_features_quicktime": null,
      "br_features_realplayer": null,
      "br_features_windowsmedia": null,
      "br_features_gears": null,
      "br_features_silverlight": null,
      "br_cookies": true,
      "br_colordepth": "24",
      "br_viewwidth": 360,
      "br_viewheight": 616,
      "os_name": null,
      "os_family": null,
      "os_manufacturer": null,
      "os_timezone": "America/Argentina/Buenos_Aires",
      "dvce_type": null,
      "dvce_ismobile": null,
      "dvce_screenwidth": 360,
      "dvce_screenheight": 640,
      "doc_charset": "UTF-8",
      "doc_width": 360,
      "doc_height": 4457,
      "tr_currency": null,
      "tr_total_base": null,
      "tr_tax_base": null,
      "tr_shipping_base": null,
      "ti_currency": null,
      "ti_price_base": null,
      "base_currency": null,
      "geo_timezone": null,
      "mkt_clickid": null,
      "mkt_network": null,
      "etl_tags": null,
      "dvce_sent_tstamp": "2020-10-26T14:33:00.933Z",
      "refr_domain_userid": null,
      "refr_dvce_tstamp": null,
      "derived_contexts": {},
      "domain_sessionid": "028a821f-1bf7-42f8-ad07-df02128607c1",
      "derived_tstamp": "2020-10-26T14:33:04.586Z",
      "event_vendor": "com.snowplowanalytics.snowplow",
      "event_name": "page_ping",
      "event_format": "jsonschema",
      "event_version": "1-0-0",
      "event_fingerprint": null,
      "true_tstamp": null
    }
  }
}

Does anyone knows what’s wrong?

resolver.json:

{
  "schema": "iglu:com.snowplowanalytics.iglu/resolver-config/jsonschema/1-0-2",
  "data": {
    "cacheSize": 500,
    "cacheTtl": 600,
    "repositories": [
      {
        "name": "Iglu Central",
        "priority": 1,
        "vendorPrefixes": ["com.snowplowanalytics"],
        "connection": {
          "http": {
            "uri": "http://iglucentral.com"
          }
        }
      },
      {
        "name": "Iglu Central - Mirror 01",
        "priority": 1,
        "vendorPrefixes": ["com.snowplowanalytics"],
        "connection": {
          "http": {
            "uri": "http://mirror01.iglucentral.com"
          }
        }
      }
    ]
  }
}

config.json

{
  "schema": "iglu:com.snowplowanalytics.snowplow.storage/postgresql_config/jsonschema/3-0-0",
  "data": {
    "name": "Acme Ltd. Snowplow Postgres",
    "id": "5c5e4353-4eeb-43da-98f8-2de6dc7fa947",
    "source": {
      "kinesis": {
        "appName": "xxxx",
        "streamName": "xxxx",
        "region": "xxxx",
        "initialPosition": "TRIM_HORIZON"
      }
    },
    "host": "xxxxx",
    "port": 5432,
    "database": "xxxxx",
    "username": "xxxx",
    "password": "xxxx",
    "schema": "atomic",
    "sslMode": "REQUIRE",
    "purpose": "ENRICHED_EVENTS"
  }
}

I think it’s got to be something to do with your network - you’re correctly pointing to Iglu Central (which is publicly available), but it’s returning not found. Iglu Central definitely isn’t down (we’d know about that kind of problem for sure!).

If I had to guess, the first things I’d look for are whether there’ either some IP blocklist or firewall in place somewhere that’s preventing the connection, or some broad rule for https-only.

I thought that too. But running wget http://iglucentral.com inside the container I got a 200 ok.
And I’m running in local machine with no network rules.

Hi @dehbmarques,

As it’s mentioned on Postgres Loader docs page, it is required that all schemas (not just custom ones) are hosted on Iglu Server 0.6.0+.

Your error indicates that it cannot find a mapping for iglu:com.snowplowanalytics.snowplow/web_page/jsonschema/1-*-* (not the actual schema) and these endpoints are available only on Iglu Servers.

Let us know how is it going with Postgres Loader.

So, this basically means that we have to run an instance of IgluServer. I did this and I have added the jsonschema for web_page but still I’m getting same error and no data is added to postgres.

{“schema”:“iglu:com.snowplowanalytics.snowplow.badrows/loader_iglu_error/jsonschema/2-0-0”,“data”:{“processor”:{“artifact”:“snowplow-postgres-loader”,“version”:“0.1.1”},“failure”:[{“schemaCriterion”:“iglu:com.snowplowanalytics.snowplow/web_page/jsonschema/1--”,“error”:{“error”:“ResolutionError”,“lookupHistory”:[{“repository”:“Iglu Central”,“errors”:[{“error”:“NotFound”}],“attempts”:1,“lastAttempt”:“2021-01-05T11:43:35.149Z”},{“repository”:“Iglu Central - Mirror 01”,“errors”:[{“error”:“NotFound”}],“attempts”:1,“lastAttempt”:“2021-01-05T11:43:35.237Z”},{“repository”:“Iglu Client Embedded”,“errors”:[{“error”:“NotFound”}],“attempts”:1,“lastAttempt”:“2021-01-05T11:43:35.237Z”}]}},{“schemaCriterion”:“iglu:org.w3/PerformanceTiming/jsonschema/1--”,“error”:{“error”:“ResolutionError”,“lookupHistory”:[{“repository”:“Iglu Central”,“errors”:[{“error”:“NotFound”}],“attempts”:1,“lastAttempt”:“2021-01-05T11:43:35.368Z”},{“repository”:“Iglu Central - Mirror 01”,“errors”:[{“error”:“NotFound”}],“attempts”:1,“lastAttempt”:“2021-01-05T11:43:35.426Z”},{“repository”:“Iglu Client Embedded”,“errors”:[{“error”:“NotFound”}],“attempts”:1,“lastAttempt”:“2021-01-05T11:43:35.426Z”}]}},{“schemaCriterion”:“iglu:nl.basjes/yauaa_context/jsonschema/1--”,“error”:{“error”:“ResolutionError”,“lookupHistory”:[{“repository”:“Iglu Central”,“errors”:[{“error”:“NotFound”}],“attempts”:1,“lastAttempt”:“2021-01-05T11:43:35.552Z”},{“repository”:“Iglu Central - Mirror 01”,“errors”:[{“error”:“NotFound”}],“attempts”:1,“lastAttempt”:“2021-01-05T11:43:35.621Z”},{“repository”:“Iglu Client Embedded”,“errors”:[{“error”:“NotFound”}],“attempts”:1,“lastAttempt”:“2021-01-05T11:43:35.621Z”}]}}],“payload”:{“app_id”:“mercurylabs1”,“platform”:“web”,“etl_tstamp”:“2021-01-05T11:43:10.491Z”,“collector_tstamp”:“2021-01-05T11:43:08.312Z”,“dvce_created_tstamp”:“2021-01-05T11:43:08.442Z”,“event”:“page_view”,“event_id”:“2a29a231-0741-491d-855b-2821d5b9f5f0”,“txn_id”:null,“name_tracker”:“sp”,“v_tracker”:“js-2.17.0”,“v_collector”:“ssc-1.0.1-kinesis”,“v_etl”:“stream-enrich-1.4.2-common-1.4.2”,“user_id”:null,“user_ipaddress”:“73e14ec09f8110c91c2139cf7793f631c136c3f6”,“user_fingerprint”:null,“domain_userid”:“b630e1c9-9f78-42b6-ae0f-766e231e00b7”,“domain_sessionidx”:7,“network_userid”:“8eb6c492-9b7d-43df-b1cb-5c55e3aa0de6”,“geo_country”:null,“geo_region”:null,“geo_city”:null,“geo_zipcode”:null,“geo_latitude”:null,“geo_longitude”:null,“geo_region_name”:null,“ip_isp”:null,“ip_organization”:null,“ip_domain”:null,“ip_netspeed”:null,“page_url”:“https://snowplow-test.s3.eu-west-2.amazonaws.com/index-test.html",“page_title”:"Small asynchronous website/webapp examples for snowplow.js”,“page_referrer”:“https://s3.console.aws.amazon.com/s3/object/snowplow-test?region=eu-west-2&prefix=index-test.html",“page_urlscheme”:“https”,“page_urlhost”:“snowplow-test.s3.eu-west-2.amazonaws.com”,“page_urlport”:443,“page_urlpath”:"/index-test.html",“page_urlquery”:null,“page_urlfragment”:null,“refr_urlscheme”:“https”,“refr_urlhost”:“s3.console.aws.amazon.com”,“refr_urlport”:443,“refr_urlpath”:"/s3/object/snowplow-test",“refr_urlquery”:“region=eu-west-2&prefix=index-test.html”,“refr_urlfragment”:null,“refr_medium”:“search”,“refr_source”:“Amazon”,“refr_term”:null,“mkt_medium”:null,“mkt_source”:null,“mkt_term”:null,“mkt_content”:null,“mkt_campaign”:null,“contexts”:{“schema”:“iglu:com.snowplowanalytics.snowplow/contexts/jsonschema/1-0-0”,“data”:[{“schema”:“iglu:com.snowplowanalytics.snowplow/web_page/jsonschema/1-0-0”,“data”:{“id”:“ed6cdb44-f8b9-4a4e-9ea7-149b256b2e63”}},{“schema”:“iglu:org.w3/PerformanceTiming/jsonschema/1-0-0”,“data”:{“navigationStart”:1609830810340,“unloadEventStart”:1609830810553,“unloadEventEnd”:1609830810553,“redirectStart”:0,“redirectEnd”:0,“fetchStart”:1609830810342,“domainLookupStart”:1609830810344,“domainLookupEnd”:1609830810344,“connectStart”:1609830810344,“connectEnd”:1609830810485,“secureConnectionStart”:1609830810390,“requestStart”:1609830810486,“responseStart”:1609830810543,“responseEnd”:1609830810586,“domLoading”:1609830810554,“domInteractive”:1609830810631,“domContentLoadedEventStart”:1609830810631,“domContentLoadedEventEnd”:1609830810632,“domComplete”:1609830810697,“loadEventStart”:1609830810697,“loadEventEnd”:1609830810697}}]},“se_category”:null,“se_action”:null,“se_label”:null,“se_property”:null,“se_value”:null,“unstruct_event”:null,“tr_orderid”:null,“tr_affiliation”:null,“tr_total”:null,“tr_tax”:null,“tr_shipping”:null,“tr_city”:null,“tr_state”:null,“tr_country”:null,“ti_orderid”:null,“ti_sku”:null,“ti_name”:null,“ti_category”:null,“ti_price”:null,“ti_quantity”:null,“pp_xoffset_min”:null,“pp_xoffset_max”:null,“pp_yoffset_min”:null,“pp_yoffset_max”:null,“useragent”:"Mozilla/5.0 (Macintosh; Intel Mac OS X 11_0_1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/87.0.4280.88 Safari/537.36”,“br_name”:null,“br_family”:null,“br_version”:null,“br_type”:null,“br_renderengine”:null,“br_lang”:“en-US”,“br_features_pdf”:true,“br_features_flash”:false,“br_features_java”:false,“br_features_director”:false,“br_features_quicktime”:false,“br_features_realplayer”:false,“br_features_windowsmedia”:false,“br_features_gears”:false,“br_features_silverlight”:false,“br_cookies”:true,“br_colordepth”:“30”,“br_viewwidth”:3200,“br_viewheight”:727,“os_name”:null,“os_family”:null,“os_manufacturer”:null,“os_timezone”:“Europe/Helsinki”,“dvce_type”:null,“dvce_ismobile”:null,“dvce_screenwidth”:3200,“dvce_screenheight”:1800,“doc_charset”:“UTF-8”,“doc_width”:3200,“doc_height”:2356,“tr_currency”:null,“tr_total_base”:null,“tr_tax_base”:null,“tr_shipping_base”:null,“ti_currency”:null,“ti_price_base”:null,“base_currency”:null,“geo_timezone”:null,“mkt_clickid”:null,“mkt_network”:null,“etl_tags”:null,“dvce_sent_tstamp”:“2021-01-05T11:43:08.447Z”,“refr_domain_userid”:null,“refr_dvce_tstamp”:null,“derived_contexts”:{“schema”:“iglu:com.snowplowanalytics.snowplow/contexts/jsonschema/1-0-0”,“data”:[{“schema”:“iglu:nl.basjes/yauaa_context/jsonschema/1-0-1”,“data”:{“deviceBrand”:“Apple”,“deviceName”:“Apple Macintosh”,“operatingSystemVersionMajor”:“11”,“layoutEngineNameVersion”:“Blink 87.0”,“operatingSystemNameVersion”:“Mac OS X 11.0.1”,“layoutEngineNameVersionMajor”:“Blink 87”,“operatingSystemName”:“Mac OS X”,“agentVersionMajor”:“87”,“layoutEngineVersionMajor”:“87”,“deviceClass”:“Desktop”,“agentNameVersionMajor”:“Chrome 87”,“operatingSystemNameVersionMajor”:“Mac OS X 11”,“deviceCpuBits”:“32”,“operatingSystemClass”:“Desktop”,“layoutEngineName”:“Blink”,“agentName”:“Chrome”,“agentVersion”:“87.0.4280.88”,“layoutEngineClass”:“Browser”,“agentNameVersion”:“Chrome 87.0.4280.88”,“operatingSystemVersion”:“11.0.1”,“deviceCpu”:“Intel”,“agentClass”:“Browser”,“layoutEngineVersion”:“87.0”}}]},“domain_sessionid”:“cba93600-8c98-40e2-b3b7-5ddc12febe94”,“derived_tstamp”:“2021-01-05T11:43:08.307Z”,“event_vendor”:“com.snowplowanalytics.snowplow”,“event_name”:“page_view”,“event_format”:“jsonschema”,“event_version”:“1-0-0”,“event_fingerprint”:“de5a0322c96b484ec6bd956b7bc4d461”,“true_tstamp”:null}}}

Actually, the issue was that Postgres Loader was not able to retrieve schemas from Iglu Server due to the fact that endpoint was not properly provided, and I did not found anywhere in the docs any reference about it. If you run an Iglu Server, for example on host acme.example.com on port 8080, then the config for Postgres Loader should be:

{
“name”: “Iglu Server Local”,
“priority”: 1,
“vendorPrefixes”: [
“com.snowplowanalytics”,
“com.snowplowanalytics.snowplow”,
“org.w3”,
“nl.basjes”
],
“connection”: {
“http”: {
“uri”: “http:/acme.example.com:8080/api/”
}
}
}

Notice api/ at the end of url.

1 Like

hi @Gabriel_Ciuloaica, I am very happy that it worked for you. I am also facing the same error since a long time now. Could you please point me to the documentation which you referred to host your own iglu server please? I will also try it out and see if that works for me as well.

Here is the documentation:

This is what I have followed to add schema to Iglue Server

Iglu Server image

docker pull snowplow-docker-registry.bintray.io/snowplow/iglu-server:0.6.1

Iglu Server

docker run
-v ${PWD}/iglu-server-config:/snowplow/config
snowplow/iglu-server:0.6.1 \
–config /snowplow/config/application.conf

And application.conf template is here: