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

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

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.

1 Like

Hi @mike

Thanks for the help … :slight_smile:

Regards!
Deepak Bhatt