Unable to use Glue table EDIT SCHEMA AS JSON option

I understand you're facing challenges with AWS Glue crawlers not correctly identifying the schema of nested JSON files in S3, and you're looking for a way to manually specify the schema. While the "EDIT SCHEMA AS JSON" option in the AWS Glue console seems like a potential solution, it appears to be not working as expected in your case.

For nested JSON files, crawlers can sometimes struggle to infer the correct schema. Here are a few alternative approaches you can consider:

1. Use a custom classifier: You can create a custom JSON classifier that defines the structure of your nested JSON. This classifier can then be associated with your crawler to help it correctly interpret the schema.

2. Manually create the table: Instead of relying on the crawler, you can manually create the table in the AWS Glue Data Catalog with the correct schema. Here's how:

   • In the AWS Glue console, go to Tables and choose "Add table"
   • Set the table properties, including the database and S3 data location
   • For the data format, choose JSON
   • When defining the schema, you can add columns that represent your nested structure
   • You can use dot notation to represent nested fields (e.g., "parent.child.grandchild")

3. Use AWS Glue ETL jobs: If you've had success with ETL jobs, you might want to consider using them in combination with crawlers. You could use an ETL job to flatten or transform your nested JSON into a format that's easier for the crawler to interpret.

4. Optimize JSON reading: If you're using AWS Glue version 3.0 or later, you can try using the optimized JSON reader. This can be done by setting "optimizePerformance" to True in the format_options when creating a dynamic frame. However, note that this requires providing a schema and has some limitations with nested structures.

5. Use the AWS Glue Schema Registry: You can create a schema for your JSON data using the AWS Glue Schema Registry. This allows you to define and evolve schemas over time, which can be particularly useful for complex, nested JSON structures.

Remember, when dealing with nested JSON, it's crucial to have a clear understanding of your data structure. You might need to experiment with different approaches to find the one that works best for your specific use case.
Sources
Using the JSON format in AWS Glue - AWS Glue
Creating a schema - AWS Glue
Getting started with the AWS Glue Data Catalog - AWS Glue