Spark Cosmos db connector is dropping columns where majority of rows are null

user_8431097 0
Viewed 329

I am trying to read a 30K rows data from cosmos db using the spark cosmos connector using the following code

val readConfig = Config(Map(
  "Endpoint" -> "",
  "Masterkey" -> "",
  "Database" -> "",
  "Collection" -> "",
  "PreferredRegions" -> "",
   "query_custom" -> """SELECT t.id,t.gender,t.loc from Tab t"""
 ))

val df = spark.read.cosmosDB(readConfig)

In the 30k, only 2 rows have non null values for 'loc' column. But for some reason the connector is dropping the 'loc' column altogether in the final dataframe and the final dataframe is giving the following schema

df.printSchema
root
 |-- id: string (nullable = true)
 |-- gender: string (nullable = true)

Can someone please help me how to get the 'loc' column included in my final dataframe.

azure-cosmosdb azure-databricks apache-spark azure
1 Answers

When you don't specify the schema when reading, the Spark Connector needs to infer it. In order to infer it, it samples documents and based on them, creates the schema.

The problem might be that the sampled documents do not have this attribute (you said only 2 on 30K have it), so when the schema is generated to read the complete data, it obviously does not have it.

Providing the schema directly on the read call would solve this issue. Alternatively, you can customize the sampling size (reference https://github.com/Azure/azure-cosmosdb-spark/blob/19561f0d42eaa91f9e4793fbdf30b62b22829868/src/main/scala/com/microsoft/azure/cosmosdb/spark/config/CosmosDBConfig.scala#L47, default 1000) by increasing schema_samplesize.

user_56415980
Related