EMR to Redshift Error

tjh34 · May 10, 2018, 3:18pm

I’m getting an interesting error on EMR-ETL-Runner on the RDB Loader (Redshift) stage:

Data loading error Amazon Invalid operation: Cannot COPY into nonexistent table com_snowplowanalytics_snowplow_duplicate_1;
ERROR: Data loading error Amazon Invalid operation: Cannot COPY into nonexistent table com_snowplowanalytics_snowplow_duplicate_1;
Following steps completed: [Discover]
INFO: Logs successfully dumped to S3 [s3://bucket/logs/rdb-loader/2018-05-10-10-49-44/b22169fd-58d5-48a1-9e63-ac313feb0a99]

I have no idea where this duplicate table is coming from. I have already created a database, ran the atomic-def.sql as referenced on this page, and all other steps:

Here is my config for the Redshift target as well:

{
“schema”: “iglu:com.snowplowanalytics.snowplow.storage/redshift_config/jsonschema/2-1-0”,
“data”: {
“name”: “Snowplow Redshift Storage”,
“host”: “IP”,
“database”: “Database”,
“port”: 5439,
“sslMode”: “REQUIRE”,
“username”: “username”,
“password”: “password”,
“roleArn”: “ARN”,
“schema”: “atomic”,
“maxError”: 1,
“compRows”: 1000,
“sshTunnel”: null,
“purpose”: “ENRICHED_EVENTS”
}

Any thoughts on why this is happening and ideas on how to fix are much appreciated.

mjensen · May 10, 2018, 3:29pm

should be able to just run this SQL to create table

github.com

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

-- AUTO-GENERATED BY igluctl DO NOT EDIT
-- Generator: igluctl 0.2.0
-- Generated: 2016-11-21 11:23

CREATE SCHEMA IF NOT EXISTS atomic;

CREATE TABLE IF NOT EXISTS atomic.com_snowplowanalytics_snowplow_duplicate_1 (
    "schema_vendor"     VARCHAR(128)  ENCODE RUNLENGTH NOT NULL,
    "schema_name"       VARCHAR(128)  ENCODE RUNLENGTH NOT NULL,
    "schema_format"     VARCHAR(128)  ENCODE RUNLENGTH NOT NULL,
    "schema_version"    VARCHAR(128)  ENCODE RUNLENGTH NOT NULL,
    "root_id"           CHAR(36)      ENCODE RAW       NOT NULL,
    "root_tstamp"       TIMESTAMP     ENCODE LZO       NOT NULL,
    "ref_root"          VARCHAR(255)  ENCODE RUNLENGTH NOT NULL,
    "ref_tree"          VARCHAR(1500) ENCODE RUNLENGTH NOT NULL,
    "ref_parent"        VARCHAR(255)  ENCODE RUNLENGTH NOT NULL,
    "original_event_id" CHAR(36)      ENCODE LZO       NOT NULL,
    FOREIGN KEY (root_id) REFERENCES atomic.events(event_id)
)
DISTSTYLE KEY

This file has been truncated. show original

tjh34 · May 11, 2018, 2:37pm

Thanks! That worked. I had to run manifest-def.sql as well. It wasn’t clear in the documentation that I had to run those as well.

Tejas_Behra · February 23, 2020, 9:57pm

Hi @mjensen & @tjh34, I am getting following logs -

Data loading error Amazon Invalid operation: Cannot COPY into nonexistent table com_snowplowanalytics_snowplow_ua_parser_context_1;
ERROR: Data loading error Amazon Invalid operation: Cannot COPY into nonexistent table com_snowplowanalytics_snowplow_ua_parser_context_1;
Following steps completed: [Discover]
INFO: Logs successfully dumped to S3 [s3://rr-snowplow-emr-logs/rdb-loader/2020-02-23-21-38-44/32fba851-e309-47f4-9f4c-fad68a4aa96e]

How can i solve this ? Thanks

mike · February 23, 2020, 10:15pm

Have you run the SQL to create the ua_parser_context table?

Tejas_Behra · February 23, 2020, 10:18pm

Yes its solved now -
Found the queries here -

Topic		Replies	Views
Load into table *_ua_parser_context_1 failed For engineers	3	674	August 30, 2023
Step [rdb_load] stdout: Configuration error Attempt to decode value on failed cursor: DownField(sslMode) Troubleshooting	10	2285	November 6, 2019
RDB-Loader Redshift - Invalid operation: column "filters" duplicated For engineers	5	1259	December 21, 2022
Snowplow StorageLoader fails with message: Cannot COPY into nonexistent table "snowplow_duplicate_1" For engineers	4	2127	May 20, 2017
Loading Redshift - can't find missing tables in snowplow/snowplow repo Storage targets	3	2250	November 10, 2016

EMR to Redshift Error

Related Topics