Redshift load error: Number of jsonpaths and the number of columns should match


#1

Hi Team,

I am getting error while running my storage loader job. Can you please help me with this. Below is the error I am getting:

Unexpected error: Java::Default::PSQLException error executing COPY statements: BEGIN;
COPY landing.events FROM ‘s3://*/udmd-a-enriched/shredded/good/run=2016-11-29-16-28-45/atomic-events’ CREDENTIALS ‘aws_access_key_id=xxxxxxxxxxxxxxxxxxxx;aws_secret_access_key=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx’ REGION AS ‘eu-west-1’ DELIMITER ‘\t’ MAXERROR 1 EMPTYASNULL FILLRECORD TRUNCATECOLUMNS TIMEFORMAT ‘auto’ ACCEPTINVCHARS ;
COPY landing.com_snowplowanalytics_snowplow_ua_parser_context_1 FROM 's3://
/udmd-a-enriched/shredded/good/run=2016-11-29-16-28-45/com.snowplowanalytics.snowplow/ua_parser_context/jsonschema/1-’ CREDENTIALS ‘aws_access_key_id=xxxxxxxxxxxxxxxxxxxx;aws_secret_access_key=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx’ JSON AS ‘s3://snowplow-hosted-assets/4-storage/redshift-storage/jsonpaths/com.snowplowanalytics.snowplow/ua_parser_context_1.json’ REGION AS ‘eu-west-1’ MAXERROR 1 TRUNCATECOLUMNS TIMEFORMAT ‘auto’ ACCEPTINVCHARS ;
COPY landing.udmd_unileversolutions_componentvideo_1 FROM ‘s3://******/udmd-a-enriched/shredded/good/run=2016-11-29-16-28-45/udmd.unileversolutions/componentvideo/jsonschema/1-’ CREDENTIALS ‘aws_access_key_id=xxxxxxxxxxxxxxxxxxxx;aws_secret_access_key=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx’ JSON AS ‘s3://*/udmd-a-jsonpathfiles/udmd.unileversolutions/componentvideo_1.json’ REGION AS ‘eu-west-1’ MAXERROR 1 TRUNCATECOLUMNS TIMEFORMAT ‘auto’ ACCEPTINVCHARS ;
COPY landing.udmd_unileversolutions_custom_data_1 FROM 's3:///udmd-a-enriched/shredded/good/run=2016-11-29-16-28-45/udmd.unileversolutions/customData/jsonschema/1-’ CREDENTIALS ‘aws_access_key_id=xxxxxxxxxxxxxxxxxxxx;aws_secret_access_key=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx’ JSON AS 's3://
/udmd-a-jsonpathfiles/udmd.unileversolutions/custom_data_1.json’ REGION AS ‘eu-west-1’ MAXERROR 1 TRUNCATECOLUMNS TIMEFORMAT ‘auto’ ACCEPTINVCHARS ;
COPY landing.udmd_unileversolutions_page_1 FROM 's3:///udmd-a-enriched/shredded/good/run=2016-11-29-16-28-45/udmd.unileversolutions/page/jsonschema/1-’ CREDENTIALS ‘aws_access_key_id=xxxxxxxxxxxxxxxxxxxx;aws_secret_access_key=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx’ JSON AS 's3:///udmd-a-jsonpathfiles/udmd.unileversolutions/page_1.json’ REGION AS ‘eu-west-1’ MAXERROR 1 TRUNCATECOLUMNS TIMEFORMAT ‘auto’ ACCEPTINVCHARS ;
COPY landing.udmd_unileversolutions_product_1 FROM 's3://
/udmd-a-enriched/shredded/good/run=2016-11-29-16-28-45/udmd.unileversolutions/product/jsonschema/1-’ CREDENTIALS ‘aws_access_key_id=xxxxxxxxxxxxxxxxxxxx;aws_secret_access_key=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx’ JSON AS ‘s3://******/udmd-a-jsonpathfiles/udmd.unileversolutions/product_1.json’ REGION AS ‘eu-west-1’ MAXERROR 1 TRUNCATECOLUMNS TIMEFORMAT ‘auto’ ACCEPTINVCHARS ;
COPY landing.udmd_unileversolutions_promotion_1 FROM ‘s3://**/udmd-a-enriched/shredded/good/run=2016-11-29-16-28-45/udmd.unileversolutions/promotion/jsonschema/1-’ CREDENTIALS ‘aws_access_key_id=xxxxxxxxxxxxxxxxxxxx;aws_secret_access_key=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx’ JSON AS 's3:///udmd-a-jsonpathfiles/udmd.unileversolutions/promotion_1.json’ REGION AS ‘eu-west-1’ MAXERROR 1 TRUNCATECOLUMNS TIMEFORMAT ‘auto’ ACCEPTINVCHARS ;
COMMIT;: ERROR: Number of jsonpaths and the number of columns should match. JSONPath size: 24, Number of columns in table or column list: 21
Detail:

error: Number of jsonpaths and the number of columns should match. JSONPath size: 24, Number of columns in table or column list: 21
code: 8001
context:
query: 164684
location: s3_utility.cpp:670
process: padbmaster [pid=14493]

/data/apps/SnowplowRealeases/r75/snowplow-storage-loader!/storage-loader/lib/snowplow-storage-loader/redshift_loader.rb:88:in load_events_and_shredded_types' file:/data/apps/SnowplowRealeases/r75/snowplow-storage-loader!/storage-loader/bin/snowplow-storage-loader:54:in(root)'
org/jruby/RubyArray.java:1613:in each' file:/data/apps/SnowplowRealeases/r75/snowplow-storage-loader!/storage-loader/bin/snowplow-storage-loader:51:in(root)'
org/jruby/RubyKernel.java:1091:in load' file:/data/apps/SnowplowRealeases/r75/snowplow-storage-loader!/META-INF/main.rb:1:in(root)‘
org/jruby/RubyKernel.java:1072:in require' file:/data/apps/SnowplowRealeases/r75/snowplow-storage-loader!/META-INF/main.rb:1:in(root)’
/tmp/jruby927117908431422619extract/jruby-stdlib-1.7.20.1.jar!/META-INF/jruby.home/lib/ruby/shared/rubygems/core_ext/kernel_require.rb:1:in `(root)'
error in running the Storage Loader, exiting with return code1.

Can you please tell me the table for which the jsonpath and columns are not matching.Thanks in advanced.

Regards!
Deepak


#2

It looks like there’s only 5 custom schemas there, I’d have a look at each one and find the one where the JSON path has 24 columns.


#3

Hi @mike

Thanks for the help … :slight_smile:

Regards!
Deepak Bhatt