Delimiter not found / String length exceeds DDL length

Question

I am using Amazon kinesis firehose delivery stream to copy my data to redshift. I have used dynamoDB stream as data source, used lambda-streams-to-firehose lambda function to copy data to delivery stream. After execution of this lambda I am getting following output.

{ invocationId: '0b214ec1-6b67-4c78-8881-9b3998555205', deliveryStreamArn: 'arn:aws:firehose:us-east-1:xxxxxxxx:deliverystream/<streamName>', region: 'us-east-1', records: [ { recordId: '49575469680524135041586805764649280618633657491608567810', approximateArrivalTimestamp: 1501146562192, data: 'eyJLZXlzIjp7IkRldmljZUlEIjp7IlMiOiJEQVZJUy1NLTIwLVcifSwiVGltZXN0YW1wIjp7IlMiOiIxNTAxMTQ2NTYwODI5In19LCJOZXdJbWFnZSI6eyJUZW1wZXJhdHVyZSI6eyJTIjoiNjMuMTM5OTk5OTk5OTk5OTkifSwiRGV2aWNlSUQiOnsiUyI6IkRBVklTLU0tMjAtVyJ9LCJQcmVzc3VyZSI6eyJTIjoiMTMyLjg0In0sIlRpbWVzdGFtcCI6eyJTIjoiMTUwMTE0NjU2MDgyOSJ9fSwiU2VxdWVuY2VOdW1iZXIiOiI0MDIxMTAyMDAwMDAwMDAwMDI0MDI0MDA1NzgiLCJTaXplQnl0ZXMiOjEyNiwiQXBwcm94aW1hdGVDcmVhdGlvbkRhdGVUaW1lIjoxNTAxMTQ2NTQwLCJldmVudE5hbWETUiOiJJTlNFUlQifQo=' }, { recordId: '49575469680524135041586805770929650251531656329085059074', approximateArrivalTimestamp: 1501146564204, data: 'eyJLZXlzIjp7IkRldmljZUlEIjp7IlMiOiJCSVo0SU5URUxMSUEtTElCMDIifSwiVGltZXN0YW1wIjp7IlMiOiIxNTAxMTQ2NTYzMTg4In19LCJOZXdJbWFnZSI6eyJDb2xpZm9ybUJhY3RlcmlhIjp7IlMiOiIzNiJ9LCJDeWFub0JhY3RlcmlhIjp7IlMiOiIyMDg0MSJ9LCJUZW1wZXJhdHVyZSI6eyJTIjoiODAifSwiRGV2aWNlSUQiOnsiUyI6IkJJWjRJTlRFTExJQS1MSUIwMiJ9LCJBbGthbGluaXR5Ijp7IlMiOiIyMzUifSwiVGltZXN0YW1wIjp7IlMiOiIxNTAxMTQ2NTYzMTg4In0sIkRlcHRoIjp7IlMiOiIyMCJ9LCJFQyI6eyJTIjoiMCJ9fSwiU2VxdWVuY2VOdW1iZXIiOiI0MDIxMTAzMDAwMDAwMDAwMDI0MDI0MDE1ODciLCJTaXplQnl0ZXMiOjE2OCwiQXBwcm94aW1hdGVDcmVhdGlvbkRhdGVUaW1lIjoxNTAxMTQ2NTQwLCJldmVudE5hbWUiOiJJTlNFUlQifQo=' } ] }

I have configure another lambda for data transformation before storing data to S3 which gives me following output.

[ { recordId: '49575469680524135041586805764649280618633657491608567810', result: 'Ok', data: 'eyJLZXlzIjp7IkRldmljZUlEIjp7IlMiOiJEQVZJUy1NLTIwLVcifSwiVGltZXN0YW1wIjoiMDcuMjcuMjAxNyAwOTowOToyMCJ9LCJOZXdJbWFnZSI6eyJUZW1wZXJhdHVyZSI6eyJTIjoiNjMuMTM5OTk5OTk5OTk5OTkifSwiRGV2aWNlSUQiOnsiUyI6IkRBVklTLU0tMjAtVyJ9LCJQcmVzc3VyZSI6eyJTIjoiMTMyLjg0In0sIlRpbWVzdGFtcCI6IjA3LjI3LjIwMTcgMDk6MDk6MjAifSwiU2VxdWVuY2VOdW1iZXIiOiI0MDIxMTAyMDAwMDAwMDAwMDI0MDI0MDA1NzgiLCJTaXplQnl0ZXMiOjEyNiwiQXBwcm94aW1hdGVDcmVhdGlvbkRhdGVUaW1lIjoxNTAxMTQ2NTQwLCJldmVudE5hbWUiOiJJTlNFUlQifQ==' }, { recordId: '49575469680524135041586805770929650251531656329085059074', result: 'Ok', data: 'eyJLZXlzIjp7IkRldmljZUlEIjp7IlMiOiJCSVo0SU5URUxMSUEtTElCMDIifSwiVGltZXN0YW1wIjoiMDcuMjcuMjAxNyAwOTowOToyMyJ9LCJOZXdJbWFnZSI6eyJDb2xpZm9ybUJhY3RlcmlhIjp7IlMiOiIzNiJ9LCJDeWFub0JhY3RlcmlhIjp7IlMiOiIyMDg0MSJ9LCJUZW1wZXJhdHVyZSI6eyJTIjoiODAifSwiRGV2aWNlSUQiOnsiUyI6IkJJWjRJTlRFTExJQS1MSUIwMiJ9LCJBbGthbGluaXR5Ijp7IlMiOiIyMzUifSwiVGltZXN0YW1wIjoiMDcuMjcuMjAxNyAwOTowOToyMyIsIkRlcHRoIjp7IlMiOiIyMCJ9LCJFQyI6eyJTIjoiMCJ9fSwiU2VxdWVuY2VOdW1iZXIiOiI0MDIxMTAzMDAwMDAwMDAwMDI0MDI0MDE1ODciLCJTaXplQnl0ZXMiOjE2OCwiQXBwcm94aW1hdGVDcmVhdGlvbkRhdGVUaW1lIjoxNTAxMTQ2NTQwLCJldmVudE5hbWUiOiJJTlNFUlQifQ==' } ]

Now when I look at above output I can see the comma is available between two records but when it stored to S3 as object there are comma between the objects. May be that's why I am getting error in redshift. Can anyone tell me what am I missing in lambda or in configuration of delivery stream.

here is how the firehose settings look like.

Answer 1

In your firehose delivery stream, have you added Redshift COPY options - DELIMITER ',' ?
You get the above-mentioned error ( Delimiter not found / String length exceeds DDL length ) when your redshift COPY option is missing the DELIMITER parameter.

If I’ve made a bad assumption please comment and I’ll refocus my answer.