How to fix the unknown schema datatype of AWS Glue Table?

Question

There was a data source (JSON files) in S3. The JSON structure is as follows.
I used AWS Glue Crawler to build the Glue table based on this S3 data source.
I think the "data" column should be "Struct" type. 
It seems that this may have to do with the fact that the key contains commas in the "data" column.
So how can I fix the unknown schema datatype caused by the JSON key containing commas?

![Glue table schema](/media/postImages/original/IMhxcZpurfQdupfwmxYtC3nA)

```
{
  "meta": {
    "request": {
      "domains": "test.com,test.org",
      "format": "json",
      "start_date": "2023-10-01",
      "end_date": "2023-10-31",
      "country": "world"
    },
    "status": "Success",
    "last_updated": "2023-10-31"
  },
  "data": {
    "test.com": 6161.0497,
    "test.org": 3129.9708,
    "test.org, test.com": 1127.10123112
  },
  "total": 10161.9689
}
```

Accepted Answer

You might define the data column in a more structured way:

```json
"data": {
  "domains": [
    {
      "name": "test.com",
      "value": 6161.0497
    },
    {
      "name": "test.org",
      "value": 3129.9708
    },
    {
      "name": "test.org, test.com",
      "value": 1127.10123112
    }
  ]
}
```
This format removes the commas from the keys and presents the data in a way that should be more straightforward for AWS Glue to parse.

If changing the JSON structure is not an option, then writing a custom ETL script would be the best way to ensure the data types are correctly identified.

If this has resolved your issue or was helpful, accepting the answer would be greatly appreciated. Thank you!

Answer

Hi there, 
Using space in name of field can cause some bug with AWS Glue’s JSON classifier because it doesn’t handle nested properties that have spaces in them well.

If you want the field data should be struct, you can use any other character but space to separate words.

![Enter image description here](/media/postImages/original/IM_TTTjVgaQyq78KyzIDdWpQ)

Here is some ref for you:
https://stackoverflow.com/questions/54203398/aws-glue-ignoring-spaces-in-json-properties

How to fix the unknown schema datatype of AWS Glue Table?

相关内容