Autolodaer schemaHints convert valid values to null

Data Engineering

Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.

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.