I have the table in hive catalog of dremio where in one of my column it contains the json structure data so i want to make the query dynamic where it can create the table itself. I tried flattening it but it throws an error :
“You’re trying to flatten a field that is not a list. The offending field is expr000::struct<division_id::int64, division_name::varchar, location::varchar>.”
My Query:
SELECT
json_keys.key AS json_key
FROM (
SELECT
FLATTEN(CONVERT_FROM(data, ‘JSON’)) AS json_keys
FROM HiveCatalog.test.“emp”
) AS flattened_data
@Riya Flatten can only be applied to a LIST (ARRAY), the above column seems to be a struct
You can verify that by logging onto the UI, click on the dataset and if it is a list, there will be an unnest option next to the column name,
The other clue is to look at the data type icon next to the column name, a struct will be like the one I have for column “id” screenshot while list would for “fragmentProfile”
I am extracting the Data from Postgres into the Iceberg HiveCatalog via Airbyte. It shows me the datatype as varchar and storing as the json structure. Please see below:
To flatten it, I have used the below query :
select data.staff_id as id,data.initiative_id as in_id from ( select CONVERT_FROM (_airbyte_data,‘JSON’) as data from HiveCatalog.test.“airbyte_data”)
I am looking out for the way where I can do it dynamically.
