Glue crawler json parsing

Question

I am currently trying to parse a nested json file that looks like this:

{"A":1,"B":{"B1":"test1","B2":"test1"}},
{"A":2,"B":{"B1":"test2","B2":"test2"}}

I specified classifier as: $.B

This results in the following table:

CREATE EXTERNAL TABLE `testing`(
  `B1` string COMMENT 'from deserializer',
  `B2` string COMMENT 'from deserializer')
ROW FORMAT SERDE 
  'org.openx.data.jsonserde.JsonSerDe' 
WITH SERDEPROPERTIES ( 
'paths'='B1,B2') 
STORED AS INPUTFORMAT ...

But when I query the data I get 2 records with null values for all fields.
Can someone please explain what is going on?
Why isn't the data showing up when the structure for the table seems to be ok.

Answer

Hello,

Looks like the issue is with the property **jsonPath** which gets added by the AWS glue crawler to the table properties when you attach a custom **JSON classifier**. When you query this table using AWS Athena with the JSON serde **org.openx.data.jsonserde.JsonSerDe** , it is not able to understand this property and hence it might not be able to parse the JSON data resulting in empty rows.

I would suggest you to check on this [blog](https://aws.amazon.com/blogs/big-data/create-tables-in-amazon-athena-from-nested-json-and-mappings-using-jsonserde/) for querying nested JSON data using AWS Athena.

However, a **Glue ETL job** is able to read the table and display the output successfully !!

Ref:

AWS Athena JSON serde [reference](https://docs.aws.amazon.com/athena/latest/ug/json-serde.html)

Glue crawler json parsing

Relevant questions

Converting JSON string to table/ojbect in Lua

AWS Glue crawler exclude patterns not working

Loading json string data as super from Glue job in to Redshift

flatten deeply nested JSON with Crawler

JSON Custom Classifier not parsing data