Storage Loader Failure table

Hi All,

I am workign with the Android Tracker the ETL-EMR runs fine, the storage loader into Redshift caused “Table not found” error.

I can’t seem to find the schema for com_snowplowanalytics_snowplow_timing_1.

Loading Snowplow events and shredded types into launchpadredshift1 (Redshift cluster)…
Unexpected error: Java::Default::PSQLException error executing COPY statements: BEGIN;
COPY atomic.events FROM ‘s3://launchpad-etl-emr/shredded/good/run=2016-11-24-10-56-08/atomic-events’ CREDENTIALS ‘aws_access_key_id=xxxxxxxxxxxxxxxxxxxx;aws_ secret_access_key=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx’ REGION AS ‘ap-southeast-2’ DELIMITER ‘\t’ MAXERROR 1 EMPTYASNULL FILLRECORD TRUNCATECOLUMNS TIME FORMAT ‘auto’ ACCEPTINVCHARS ;
COPY atomic.com_snowplowanalytics_snowplow_application_background_1 FROM ‘s3://launchpad-etl-emr/shredded/good/run=2016-11-24-10-56-08/com.snowplowanalytics. snowplow/application_background/jsonschema/1-’ CREDENTIALS ‘aws_access_key_id=xxxxxxxxxxxxxxxxxxxx;aws_secret_access_key=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxx’ JSON AS ‘s3://snowplow-hosted-assets-ap-southeast-2/4-storage/redshift-storage/jsonpaths/com.snowplowanalytics.snowplow/application_background_1.json’ REGION AS ‘ap-southeast-2’ MAXERROR 1 TRUNCATECOLUMNS TIMEFORMAT ‘auto’ ACCEPTINVCHARS ;
COPY atomic.com_snowplowanalytics_snowplow_application_foreground_1 FROM ‘s3://launchpad-etl-emr/shredded/good/run=2016-11-24-10-56-08/com.snowplowanalytics. snowplow/application_foreground/jsonschema/1-’ CREDENTIALS ‘aws_access_key_id=xxxxxxxxxxxxxxxxxxxx;aws_secret_access_key=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxx’ JSON AS ‘s3://snowplow-hosted-assets-ap-southeast-2/4-storage/redshift-storage/jsonpaths/com.snowplowanalytics.snowplow/application_foreground_1.json’ REGION AS ‘ap-southeast-2’ MAXERROR 1 TRUNCATECOLUMNS TIMEFORMAT ‘auto’ ACCEPTINVCHARS ;
COPY atomic.com_snowplowanalytics_snowplow_client_session_1 FROM ‘s3://launchpad-etl-emr/shredded/good/run=2016-11-24-10-56-08/com.snowplowanalytics.snowplow /client_session/jsonschema/1-’ CREDENTIALS ‘aws_access_key_id=xxxxxxxxxxxxxxxxxxxx;aws_secret_access_key=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx’ JSON AS ‘s 3://snowplow-hosted-assets-ap-southeast-2/4-storage/redshift-storage/jsonpaths/com.snowplowanalytics.snowplow/client_session_1.json’ REGION AS ‘ap-southeast- 2’ MAXERROR 1 TRUNCATECOLUMNS TIMEFORMAT ‘auto’ ACCEPTINVCHARS ;
COPY atomic.com_snowplowanalytics_snowplow_geolocation_context_1 FROM ‘s3://launchpad-etl-emr/shredded/good/run=2016-11-24-10-56-08/com.snowplowanalytics.sno wplow/geolocation_context/jsonschema/1-’ CREDENTIALS ‘aws_access_key_id=xxxxxxxxxxxxxxxxxxxx;aws_secret_access_key=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx’ JSON AS ‘s3://snowplow-hosted-assets-ap-southeast-2/4-storage/redshift-storage/jsonpaths/com.snowplowanalytics.snowplow/geolocation_context_1.json’ REGION AS ‘ap-southeast-2’ MAXERROR 1 TRUNCATECOLUMNS TIMEFORMAT ‘auto’ ACCEPTINVCHARS ;
COPY atomic.com_snowplowanalytics_snowplow_link_click_1 FROM ‘s3://launchpad-etl-emr/shredded/good/run=2016-11-24-10-56-08/com.snowplowanalytics.snowplow/lin k_click/jsonschema/1-’ CREDENTIALS ‘aws_access_key_id=xxxxxxxxxxxxxxxxxxxx;aws_secret_access_key=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx’ JSON AS ‘s3://snow plow-hosted-assets-ap-southeast-2/4-storage/redshift-storage/jsonpaths/com.snowplowanalytics.snowplow/link_click_1.json’ REGION AS ‘ap-southeast-2’ MAXERROR 1 TRUNCATECOLUMNS TIMEFORMAT ‘auto’ ACCEPTINVCHARS ;
COPY atomic.com_snowplowanalytics_snowplow_mobile_context_1 FROM ‘s3://launchpad-etl-emr/shredded/good/run=2016-11-24-10-56-08/com.snowplowanalytics.snowplow /mobile_context/jsonschema/1-’ CREDENTIALS ‘aws_access_key_id=xxxxxxxxxxxxxxxxxxxx;aws_secret_access_key=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx’ JSON AS ‘s 3://snowplow-hosted-assets-ap-southeast-2/4-storage/redshift-storage/jsonpaths/com.snowplowanalytics.snowplow/mobile_context_1.json’ REGION AS ‘ap-southeast- 2’ MAXERROR 1 TRUNCATECOLUMNS TIMEFORMAT ‘auto’ ACCEPTINVCHARS ;
COPY atomic.com_snowplowanalytics_snowplow_screen_view_1 FROM ‘s3://launchpad-etl-emr/shredded/good/run=2016-11-24-10-56-08/com.snowplowanalytics.snowplow/sc reen_view/jsonschema/1-’ CREDENTIALS ‘aws_access_key_id=xxxxxxxxxxxxxxxxxxxx;aws_secret_access_key=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx’ JSON AS ‘s3://sn owplow-hosted-assets-ap-southeast-2/4-storage/redshift-storage/jsonpaths/com.snowplowanalytics.snowplow/screen_view_1.json’ REGION AS ‘ap-southeast-2’ MAXERR OR 1 TRUNCATECOLUMNS TIMEFORMAT ‘auto’ ACCEPTINVCHARS ;
COPY atomic.com_snowplowanalytics_snowplow_timing_1 FROM ‘s3://launchpad-etl-emr/shredded/good/run=2016-11-24-10-56-08/com.snowplowanalytics.snowplow/timing/ jsonschema/1-’ CREDENTIALS ‘aws_access_key_id=xxxxxxxxxxxxxxxxxxxx;aws_secret_access_key=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx’ JSON AS ‘s3://snowplow-hos ted-assets-ap-southeast-2/4-storage/redshift-storage/jsonpaths/com.snowplowanalytics.snowplow/timing_1.json’ REGION AS ‘ap-southeast-2’ MAXERROR 1 TRUNCATECO LUMNS TIMEFORMAT ‘auto’ ACCEPTINVCHARS ;
COPY atomic.com_snowplowanalytics_snowplow_ua_parser_context_1 FROM ‘s3://launchpad-etl-emr/shredded/good/run=2016-11-24-10-56-08/com.snowplowanalytics.snowp low/ua_parser_context/jsonschema/1-’ CREDENTIALS ‘aws_access_key_id=xxxxxxxxxxxxxxxxxxxx;aws_secret_access_key=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx’ JSON AS ‘s3://snowplow-hosted-assets-ap-southeast-2/4-storage/redshift-storage/jsonpaths/com.snowplowanalytics.snowplow/ua_parser_context_1.json’ REGION AS ‘ap-s outheast-2’ MAXERROR 1 TRUNCATECOLUMNS TIMEFORMAT ‘auto’ ACCEPTINVCHARS ;
COMMIT;: ERROR: Cannot COPY into nonexistent table com_snowplowanalytics_snowplow_timing_1
/home/ubuntu/emretlrunner/snowplow-storage-loader!/storage-loader/lib/snowplow-storage-loader/redshift_loader.rb:88:in load_events_and_shredded_types' file:/home/ubuntu/emretlrunner/snowplow-storage-loader!/storage-loader/bin/snowplow-storage-loader:54:in (root)’
org/jruby/RubyArray.java:1613:in each' file:/home/ubuntu/emretlrunner/snowplow-storage-loader!/storage-loader/bin/snowplow-storage-loader:51:in (root)’
org/jruby/RubyKernel.java:1091:in load' file:/home/ubuntu/emretlrunner/snowplow-storage-loader!/META-INF/main.rb:1:in (root)’
org/jruby/RubyKernel.java:1072:in require' file:/home/ubuntu/emretlrunner/snowplow-storage-loader!/META-INF/main.rb:1:in (root)’
/tmp/jruby4712887012122593396extract/jruby-stdlib-1.7.20.1.jar!/META-INF/jruby.home/lib/ruby/shared/rubygems/core_ext/kernel_require.rb:1:in `(root)’

Any pointers?

Regards
SS

Hey @sachinsingh10, the SQL file you are looking for is here:

1 Like

@alex Thanks appreciate it.

A Duh! moment fro me, maybe it was late when I was looking for it. :slight_smile:

Regards
SS