AWS Glue crawling JSON lines data in S3

user_5572411 0
Viewed 1485

I have this type of data in my S3:

{"version":"0","id":"c1d9e9a4-25a2-a0d8-2fa4-b062efec98c4","detail-type":"OneTypeee","source":"OneSource","account":"123456789","time":"2021-01-17T12:35:17Z","region":"eu-central-1","resources":[],"detail":{"Key1":"Value1"}}
{"version":"0","id":"c13879a4-2h32-a0d8-9m33-b03jsh3cxxj4","detail-type":"OtherType","source":"SomeMagicSource","account":"123456789","time":"2021-01-17T12:36:17Z","region":"eu-central-1","resources":[],"detail":{"Key2":"Value2", "Key22":"Value22"}}
{"version":"0","id":"gi442233-3y44a0d8-9m33-937rjd74jdddj","detail-type":"MoreTypes","source":"SomeMagicSource2","account":"123456789","time":"2021-01-17T12:45:17Z","region":"eu-central-1","resources":[],"detail":{"MagicKey":"MagicValue", "Foo":"Bar"}}

Please note, I have added new lines to make it more readable. In reality, Kinesis Firehose produces these batches with no newlines.

When I try to run an AWS Glue crawler on this type of data, it only crawls the first JSON line and that's it. I know this because when I run Athena SQL queries, I always get only one (first) result.

How do I make a glue crawler correctly crawl through this data and make a correct schema so I could query all of that data?

amazon-kinesis-firehose amazon-athena aws-glue amazon-web-services amazon-s3
2 Answers

I wasn't able to run a crawler through JSON lines data, but simply specifying in the Glue Table Serde properties that the data is JSON worked for me. Glue automatically splits the JSON by newline and I can query the data in my Glue Jobs.

Here's what my table's properties look like. Additionally, my json lines data was compressed, so here you can ignore the compressionType property. glue table configuration

user_96100720

I had the same issue and for me the reason was that json records were being written to S3 bucket without next line character: \n.

Make sure your json records are written with \n appended at the end. In case of java, something like this:

PutRecordRequest request = new PutRecordRequest()
    .withRecord(new Record().withData(ByteBuffer.wrap((json + "\n").getBytes())))
    .withDeliveryStreamName(streamName);
amazonKinesis.putRecordAsync(request);
user_17209380
Related