Getting data in the web schema


#1

We have some trouble getting data into the web schema (and two tables in atomic too)

  • We noticed that when running these queries, they constantly outputs: ROWS AFFECTED: -1 with not data going into any of the tables, even though we have plenty of events in the atomic.events table. Why is this?

  • I also noticed that the two tables atomic.com_snowplowanalytics_snowplow_web_page_1 and atomic.org_w3_performance_timing_1 (which we had to create to make the queries run in the first place) are both empty - I wonder if that has to do with why the above mentioned queries are not populating the web schema? If that’s the case, then how do I get data into those two tables? NB! We have not added any enrichment jsons for those two tables, as we could not find anywhere in the documentation if that were needed or if they are run by standard…


#2

Hi @radubogdan,

Some of the tables used in the playbooks reference things like the web_page and performance_timing contexts. By default these aren’t turned on - this tends to be a sane default as they can be quite noisy - but they can be switched on easily using whatever tracker library you’re using.

In the case of the Javascript tracker you’d set performanceTiming and webPage to true as in the example under ‘2. The page view ID’ here. For more documentation on the contexts that you can enable the Javascript tracker documentation provides a comprehensive list.


#3

Thank you @mike!

Looks like the problem is in the JS Tracker.


#4

UPDATE

I now added the:

 contexts: {
    webPage: true,
    performanceTiming: true,
  }

To the JS tracker, I now have data in the atomic.com_snowplowanalytics_snowplow_web_page_1 and atomic.org_w3_performance_timing_1 tables - great!

However, no data is coming into the tables in web schema still. What could I be lacking/doing wrong. This is the output then I run the sql scripts using sql_runner ./sql-runner -playbook au-snowplow-sql/playbooks/web-model.yml.tmpl:

2017/01/26 14:25:28 EXECUTING 00-web-page-context (in step 00-web-page-context @ au-snowplow): /home/ubuntu/au-snowplow-sql/playbooks/web-model/01-page-views/00-web-page-context.sql
2017/01/26 14:25:38 SUCCESS: 00-web-page-context (step 00-web-page-context @ target au-snowplow), ROWS AFFECTED: -1
2017/01/26 14:25:38 EXECUTING 01-events (in step 01-events @ au-snowplow): /home/ubuntu/au-snowplow-sql/playbooks/web-model/01-page-views/01-events.sql
2017/01/26 14:25:55 SUCCESS: 01-events (step 01-events @ target au-snowplow), ROWS AFFECTED: -1
2017/01/26 14:25:55 EXECUTING 02-events-time (in step 02-events-time @ au-snowplow): /home/ubuntu/au-snowplow-sql/playbooks/web-model/01-page-views/02-events-time.sql
2017/01/26 14:26:05 SUCCESS: 02-events-time (step 02-events-time @ target au-snowplow), ROWS AFFECTED: -1
2017/01/26 14:26:05 EXECUTING 03-events-scroll-depth (in step 03-events-scroll-depth @ au-snowplow): /home/ubuntu/au-snowplow-sql/playbooks/web-model/01-page-views/03-events-scroll-depth.sql
2017/01/26 14:26:13 SUCCESS: 03-events-scroll-depth (step 03-events-scroll-depth @ target au-snowplow), ROWS AFFECTED: -1
2017/01/26 14:26:13 EXECUTING 04-ua-parser-context (in step 04-ua-parser-context @ au-snowplow): /home/ubuntu/au-snowplow-sql/playbooks/web-model/01-page-views/04-ua-parser-context.sql
2017/01/26 14:26:22 SUCCESS: 04-ua-parser-context (step 04-ua-parser-context @ target au-snowplow), ROWS AFFECTED: -1
2017/01/26 14:26:22 EXECUTING 05-timing-context (in step 05-timing-context @ au-snowplow): /home/ubuntu/au-snowplow-sql/playbooks/web-model/01-page-views/05-timing-context.sql
2017/01/26 14:26:29 SUCCESS: 05-timing-context (step 05-timing-context @ target au-snowplow), ROWS AFFECTED: -1
2017/01/26 14:26:29 EXECUTING 06-page-views (in step 06-page-views @ au-snowplow): /home/ubuntu/au-snowplow-sql/playbooks/web-model/01-page-views/06-page-views.sql
2017/01/26 14:26:46 SUCCESS: 06-page-views (step 06-page-views @ target au-snowplow), ROWS AFFECTED: -1
2017/01/26 14:26:46 EXECUTING 00-sessions (in step 00-sessions @ au-snowplow): /home/ubuntu/au-snowplow-sql/playbooks/web-model/02-sessions/00-sessions.sql
2017/01/26 14:26:56 SUCCESS: 00-sessions (step 00-sessions @ target au-snowplow), ROWS AFFECTED: -1
2017/01/26 14:26:56 EXECUTING 00-users (in step 00-users @ au-snowplow): /home/ubuntu/au-snowplow-sql/playbooks/web-model/03-users/00-users.sql
2017/01/26 14:27:04 SUCCESS: 00-users (step 00-users @ target au-snowplow), ROWS AFFECTED: -1
2017/01/26 14:27:04 EXECUTING 00-commit (in step 00-commit @ au-snowplow): /home/ubuntu/au-snowplow-sql/playbooks/web-model/04-commit/00-commit.sql
2017/01/26 14:27:05 SUCCESS: 00-commit (step 00-commit @ target au-snowplow), ROWS AFFECTED: -1
2017/01/26 14:27:05 SUCCESS: 10 queries executed against 1 targets


#5

SOLVED And another update. Turned out, that due to a missing enrichment config for the ua_parser, the com_snowplowanalytics_snowplow_ua_parser_context_1 table was not populated, causing nothing to end up in the web schema. Now it all works. Great :slight_smile: