topic VIEW JSON result value in view which based on volume in Data Engineering

VIEW JSON result value in view which based on volume

Cami — Tue, 09 Apr 2024 15:06:52 GMT

Hello guys!

I have the following case:

It has been decided that the json file will be read from a following definition ( from volume) , which more or less looks like this:

CREATE OR REPLACE VIEW [catalog_name].[schema_name].v_[object_name] AS SELECT result, _metadata.file_path as etl_full_path, _metadata.file_modification_time as etl_inserted_at FROM json.`dbfs:/Volumes/[catalog_name]/[schema_name]/[volume_name]/object=[object_name]/`

I would like to read this view using SQL warehouse and I can not view result column value - there is a NULL value

despite the flag is turn on in view properties.

view.sqlConfig.spark.sql.legacy.json.allowEmptyString.enabled=true

Any tips?

Re: VIEW JSON result value in view which based on volume

Cami — Wed, 10 Apr 2024 05:23:12 GMT

Thank you for response.

But there no issue with NULL value when I query view created on my cluster which has set the flag

spark.sql.legacy.json.allowEmptyString.enabled = True

Without that flag on session or cluster I get NULL value querying json.
The issue with a NULL value occurs when I query view via SQL warehouse compute.

Re: VIEW JSON result value in view which based on volume

NandiniN — Thu, 09 Jan 2025 11:09:59 GMT

You must be getting the below error:

[CONFIG_NOT_AVAILABLE] Configuration spark.sql.legacy.json.allowEmptyString.enabled is not available.

that's because in a warehouse this config is not configurable. SQL editor won't be the best choice for this.

Not sure where you are setting view.sqlConfig.spark.sql.legacy.json.allowEmptyString.enabled. Is it in an interactive cluster?

The JSON data source parser in Spark 3.0 treats empty strings as null for certain data types and throws exceptions for others, which can be adjusted with spark.sql.legacy.json.allowEmptyString.enabled set to true. But this only is for a cluster and not warehouse or serverless.