cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
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.
cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

Auto Loader changes the order of columns when infering JSON schema (sorted lexicographically)

arthurburkhardt
New Contributor

We are using Auto Loader to read json files from S3 and ingest data into the bronze layer. But it seems auto loader struggles with schema inference and instead of preserving the order of columns from the JSON files, it sorts them lexicographically.

For example, if we have the following json

{"colB": 1, "colC": 2, "colA": 3}
{"colB": 1, "colC": 2, "colA": 3}
{"colB": 1, "colC": 2, "colA": 3}

And the following code

import dlt

@dlt.table(table_properties={'quality': 'bronze'})
def my_table():
  return (
     spark.readStream.format('cloudFiles')
     .option('cloudFiles.format', 'json')
     .load('s3://my_bucket/my_table/')

)

It will create the following table:

colAcolBcolC
312
312
312

This is a really bad behavior, and it seems there is no option to preserve the order of columns. The only way to preserve the order of tables is to explicitly specify the schema, which defeats the purpose of schema inference.

Looking at Databricks demos, this seems to be the default behavior: https://notebooks.databricks.com/demos/auto-loader/01-Auto-loader-schema-evolution-Ingestion.html

Is there any way in Auto Loader to preserve the order of columns, like in most json-to-dataframe libraries?

0 REPLIES 0

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you wonโ€™t want to miss the chance to attend and share knowledge.

If there isnโ€™t a group near you, start one and help create a community that brings people together.

Request a New Group