Loading SQL enriched data in Redshift fails

For data modelers & consumers Redshift
1

Hi All,

I am trying to load SQL enriched data into Redshift. When I disable my SQL enrichment loading goes fine, when it is enabled I get the following error:

Data loading error [Amazon](500310) Invalid operation: Load into table 'com_deaflion_matched_company_1' failed.  Check 'stl_load_errors' system table for details.;
ERROR: Data loading error [Amazon](500310) Invalid operation: Load into table 'com_deaflion_matched_company_1' failed.  Check 'stl_load_errors' system table for details.;
Following steps completed: [Discover]

The stl_load_errors says:

Invalid JSONPath format: Member is not an object.

This is at my JSONPath file:

{
    "jsonpaths": [
        "$.schema.vendor",
        "$.schema.name",
        "$.schema.format",
        "$.schema.version",
        "$.hierarchy.rootId",
        "$.hierarchy.rootTstamp",
        "$.hierarchy.refRoot",
        "$.hierarchy.refTree",
        "$.hierarchy.refParent",
        "$.data.city",
        "$.data.company_data_id",
        "$.data.company_data_table",
        "$.data.country",
        "$.data.id",
        "$.data.industry_id",
        "$.data.name",
        "$.data.source"
    ]
}

And here two rows/variations from the data it is trying to load:

{"schema":{"vendor":"com.deaflion","name":"matched_company","format":"jsonschema","version":"1-0-0"},"data":[{"name":"company","id":35484,"industry_id":null,"country":"nl","city":"city","company_data_id":"50153935","company_data_table":"company_country_nl","source":16,"found":1}],"hierarchy":{"rootId":"fbe923cb-2b4b-4a3d-a228-280d9c1c5f1a","rootTstamp":"2017-09-04 14:44:39.484","refRoot":"events","refTree":["events","matched_company"],"refParent":"events"}}
{"schema":{"vendor":"com.deaflion","name":"matched_company","format":"jsonschema","version":"1-0-0"},"data":[{"name":null,"id":null,"industry_id":null,"country":null,"city":null,"company_data_id":null,"company_data_table":null,"source":null,"found":0}],"hierarchy":{"rootId":"94f123a0-2e8c-4124-bd47-9c16959436b7","rootTstamp":"2017-09-05 07:24:45.504","refRoot":"events","refTree":["events","matched_company"],"refParent":"events"}}

Any help is welcome!

Thanks in advance.

1 Like
2

The data element of your event payload is a list with one item; it should be an object.

2 Likes
3

This worked! Thanks @acgray!

I had to changed the SQL enrichment from AT_LEAST_ZERO to AT_MOST_ONE

"output": {
  "expectedRows": "AT_MOST_ONE",

When you expect more than 1 result the enrichment will create an array with objects :slight_smile: Thanks for helping me out!