Azure Synapse - How to read data from Azure Cosmos DB container containing multiple types in same collection?

Question

I have a container in Azure Cosmos DB that has multiple document types in the same container. So based on the type, the key pairs change. I'm trying to read the data from this container in Synapse using the following code:

cfg = {
"spark.cosmos.accountEndpoint": Endpoint,
"spark.cosmos.accountKey": accountKey,
"spark.cosmos.database": databaseName,
"spark.cosmos.container": containerName,
}

df = spark.read.format("cosmos.oltp").options(**cfg)\
    .option("spark.cosmos.read.inferSchema.enabled","true").load()

However, the schema that I'm getting through this dataframe is of the first row's type. How can I ensure that I read data for one particular type and the schema is inferred accordingly?

FabianMeiswinkel-MSFT · Accepted Answer · 2021-11-01 15:53:42Z

2

If you don't want to use the customQuery config option above (that would actually hard-code the query sent to Cosmos backend vs. relying on filter push-down in Spark) you can also use the config option "spark.cosmos.read.inferSchema.query" - this will just change the query used for schema inferrence - but otherwise rely on filter pushdown (which can result in better query performance in some cases)

cfg = {
"spark.cosmos.accountEndpoint": Endpoint,
"spark.cosmos.accountKey": accountKey,
"spark.cosmos.database": databaseName,
"spark.cosmos.container": containerName,
"spark.cosmos.read.inferSchema.query": "SELECT * FROM c WHERE c.typ = TYPE_NAME"
}

answered Nov 1, 2021 at 15:53

FabianMeiswinkel-MSFT

291 bronze badge

Sign up to request clarification or add additional context in comments.

Comments

jukebox · Accepted Answer · 2021-10-31 16:59:25Z

0

If you have multiple document types in one container, use the spark.cosmos.read.customQuery parameter in the configuration to get a particular document type as a spark dataframe:

cfg = {
"spark.cosmos.accountEndpoint": Endpoint,
"spark.cosmos.accountKey": accountKey,
"spark.cosmos.database": databaseName,
"spark.cosmos.container": containerName,
"spark.cosmos.read.customQuery": "SELECT * FROM c WHERE c.typ = TYPE_NAME"
}

df = spark.read.format("cosmos.oltp").options(**cfg)\
    .option("spark.cosmos.read.inferSchema.enabled","true").load()

Refer to this documentation for more information.

answered Oct 31, 2021 at 16:59

jukebox

4632 gold badges8 silver badges27 bronze badges

Collectives™ on Stack Overflow

Azure Synapse - How to read data from Azure Cosmos DB container containing multiple types in same collection?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related