Table com snowplowanalytics snowplow web page 1 does not exist

For data modelers & consumers
1

Hello,

I’m trying to use sql-runner to execute the playbook and sql defined in 5-data-modeling/web-model/sql-runner/ and I get the following error:

$ ./sql-runner -playbook XXX-snowplow-sql/playbook/web-model.yml.tmpl
2017/01/23 10:10:09 EXECUTING 00-web-page-context (in step 00-web-page-context @ XXX-snowplow): /home/ubuntu/XXX-snowplow-sql/playbook/web-model/01-page-views/00-web-page-context.sql
2017/01/23 10:10:09 FAILURE: 00-web-page-context (step 00-web-page-context @ target XXX-snowplow), ERROR: ERROR #42P01 relation "atomic.com_snowplowanalytics_snowplow_web_page_1" does not exist (addr="XXX:5439")
2017/01/23 10:10:09

TARGET INITIALIZATION FAILURES:
QUERY FAILURES:
* Query 00-web-page-context /home/ubuntu/XXX-snowplow-sql/playbook/web-model/01-page-views/00-web-page-context.sql (in step 00-web-page-context @ target XXX-snowplow), ERROR:
  - ERROR #42P01 relation "atomic.com_snowplowanalytics_snowplow_web_page_1" does not exist (addr="XXX:5439")
  1. Why this table doesn’t exist? Is there a pre-setup step that is not defined in the Wiki?
  2. The only reference I found about this table is here snowplow/iglu-central - what are those SQL’s for, I can’t find any documentation on their intended usage?
2

Okay so I actually ran all of the .sql files from iglu-central repository dependent on the web-model modeling. It doesn’t say explicitly in the documentation that if you want to use the web-model then you need some extra tables (or at least I didn’t find it).

Can someone with more experience confirm this?

3

Hi @radubogdan,

For events that use self-describing schemas (as in the case of web_page_context) you’ll need to create the tables in Redshift using the DDLs provided in that iglu-central repository. Each self-describing schema consists of three main components:

  1. JSON schema - for defining the event structure and aiding in the creation of the DDL
  2. JSON path - for loading data into Redshift
  3. Redshift DDL - defining the table structure

There’s some more information describing the processes involved in working with self-describing schemas in the example repository here.

Many of the web models rely on having these tables present so you’ll need to make sure they’ve been created - and have some data in them to get the most useful results out.

2 Likes
4

Thank you for jumping in @mike!

I had no problems whatsoever with the whole setup until I reached the data modeling which was unclear at the beginning. I smashed my head two days ago, but after a while I finally got ahead with those three components you’re talking about and understood what they do. Also the igluctl and schemaguru are super handy but unfortunately a bit hidden in the documentation.

Thanks for making it loud and clear!

5

@mike Thanks a lot for the info here.

I’m having trouble activating pre-defined contexts with trackers 2.7 to 2.9.

The web page context is never added to the events (no &cx= query param, no difference in query params with webPageContext on/off)

This is the script I used:

     <!--Snowplow starts plowing -->
<script type="text/javascript">
  ;(function(p,l,o,w,i,n,g){if(!p[i]){p.GlobalSnowplowNamespace=p.GlobalSnowplowNamespace||[];
      p.GlobalSnowplowNamespace.push(i);p[i]=function(){(p[i].q=p[i].q||[]).push(arguments)
      };p[i].q=p[i].q||[];n=l.createElement(o);g=l.getElementsByTagName(o)[0];n.async=1;
      n.src=w;g.parentNode.insertBefore(n,g)}}(window,document,"script","//d1fc8wv8zag5ca.cloudfront.net/2.9.0/sp.js","snowplow", {
          app_id: 'test',
          contexts: {
              webPage: true,
              performanceTiming: true,
              gaCookies: false,
              geolocation: false
          }
  }))
  window.snowplow('newTracker', 'cf', 'my_cf_collector_here')
  window.snowplow('enableActivityTracking', 30, 10)
  window.snowplow('enableLinkClickTracking')
  window.snowplow('trackPageView')
  // window.snowplow('setUserIdFromLocation', 'mac')
</script>

How do I activate the web page context?

–

Edit: I just found I need to pass the JSON contexts object to the newTracker call…

6

Hi @li0nel,

The web page context data will land in its own table, which is joinable to the atomic table via event_id = root_id AND collector_tstamp = root_tstamp.

Have you checked the atomic.com_snowplowanalytics_snowplow_web_page_1 table?

7

Hi @Colm

Thanks a lot for your help.

The mistake was with where I inserted the tracker options, everything worked great once I moved the options as a parameter to the newTracker call!

window.snowplow(‘newTracker’, ‘cf’, ‘my_cf_collector_here’, {
app_id: ‘test’,
contexts: {
webPage: true,
performanceTiming: true,
gaCookies: false,
geolocation: false
})

1 Like
8

Ah - I should have caught that. Thanks for updating us on the solution!