3

I have a table in AWS Glue, and the crawler has defined one field as array. The content is in S3 files that have a json format. The table is TableA, and the field is members.

There are a lot of other fields such as strings, booleans, doubles, and even structs.

I am able to query them all using a simpel query such as:

SELECT
    content.my_boolean,
    content.my_string,
    content.my_struct.value
FROM schema.tableA;

The issue is when I add content.members into the query. The error I get is: [Amazon](500310) Invalid operation: schema "content" does not exist.

Content exists because i am able to select other fiels from the main key in the json (content). Probably is something related with how to perform the query agains array field in Spectrum.

Any idea?

mrc
  • 2,845
  • 8
  • 39
  • 73

2 Answers2

3

You have to rename the table to extract the fields from the external schema:

SELECT
    a.content.my_boolean,
    a.content.my_string,
    a.content.my_struct.value
FROM schema.tableA a;

I had the same issue on my data, I really don't know why it needs this cast but it works. If you need to access elements of an array you have to explod it like:

SELECT member.<your-field>,
FROM schema.tableA a, a.content.members as member;

Reference

Hyruma92
  • 836
  • 8
  • 24
0

You need to create a Glue Classifier.

Select JSON as Classifier type

and for the JSON Path input the following:

$[*]

then run your crawler. It will infer your schema and populate your table with the correct fields instead of just one big array. Not sure if this was what you were looking for but figured I'd drop this here just in case others had the same problem I had.

KoalaKey
  • 252
  • 3
  • 11
  • I am facing the same problem, and I used a JSON classifier to get the crawler to find the array fields instead of a single array. I entered the JSON path as $[*] and again crawled through the data. It still gives just a single array instead of individual columns. Any other suggestions? – Rishikesh Mar 30 '23 at 15:59