Storage Loader Failure table

sachinsingh10 · November 24, 2016, 10:49pm

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

alex · November 24, 2016, 11:16pm

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

github.com

snowplow/iglu-central/blob/master/sql/com.snowplowanalytics.snowplow/timing_1.sql

-- Copyright (c) 2014 Snowplow Analytics Ltd. All rights reserved.
--
-- This program is licensed to you under the Apache License Version 2.0,
-- and you may not use this file except in compliance with the Apache License Version 2.0.
-- You may obtain a copy of the Apache License Version 2.0 at http://www.apache.org/licenses/LICENSE-2.0.
--
-- Unless required by applicable law or agreed to in writing,
-- software distributed under the Apache License Version 2.0 is distributed on an
-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the Apache License Version 2.0 for the specific language governing permissions and limitations there under.
--
-- Authors:       Alex Dean
-- Copyright:     Copyright (c) 2014 Snowplow Analytics Ltd
-- License:       Apache License Version 2.0
--
-- Compatibility: iglu:com.snowplowanalytics.snowplow/timing/jsonschema/1-0-0

CREATE TABLE atomic.com_snowplowanalytics_snowplow_timing_1 (
	-- Schema of this type
	schema_vendor  varchar(128)  encode runlength not null,

This file has been truncated. show original

sachinsingh10 · November 24, 2016, 11:57pm

@alex Thanks appreciate it.

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

Regards
SS

Topic		Replies	Views
Loading Redshift - can't find missing tables in snowplow/snowplow repo Storage targets	3	2250	November 10, 2016
Storage loader error For engineers	10	2402	October 17, 2016
Snowplow StorageLoader fails with message: Cannot COPY into nonexistent table "snowplow_duplicate_1" For engineers	4	2128	May 20, 2017
Redshift load error: Number of jsonpaths and the number of columns should match Storage targets	2	2915	November 30, 2016
Step [rdb_load] stdout: Configuration error Attempt to decode value on failed cursor: DownField(sslMode) Troubleshooting	10	2285	November 6, 2019

Storage Loader Failure table

Related topics