Autolodaer schemaHints convert valid values to null

_databreaks — Tue, 12 Mar 2024 06:07:31 GMT

I am ingesting json files from S3 using Autoloader and would like to use schemaHints to define the datatype of one of the fields, that is, I wanted the field id to be of integer type.

The DLT code below infers the the id as string, with correct values.

create streaming table raw as select * from cloud_files('/path', 'json', map("cloudFiles.inferColumnTypes", "true") );

But I would like the id column to be integer type, hence wanted to use schemaHints like below :

create streaming table raw as select * from cloud_files('/path', 'json', map("cloudFiles.inferColumnTypes", "true", "cloudFiles.schemaHints", "id LONG" ) )

The resulting table will have the correct data type but all the values of the column/field id are now null.

I've done some checking on the data such as isnotnull(int(id)) to see if there is any record in not convertible to int but surprise surprise, all ids are castable to int.

I already have a work-around in mind. I just want to understand why would autoloader do that?

Is this a known issue/bug?

Regards.

topic Autolodaer schemaHints convert valid values to null in Data Engineering

Autolodaer schemaHints convert valid values to null