Schema supported by Autoloader

Chris_Konsur — Thu, 10 Nov 2022 23:20:52 GMT

We do not want to use schema inference with schema evolution in Autoloader. Instead, we want to apply our schema and use the merge option. Our schema is very complex, with multiple nested following levels.

When I apply this schema to Autoloader, it runs without errors, but it appears that Autoloader cannot parse this multiple-level nested schema. The Autoloader creates columns from the top level, but all the nested levels and values are NULL.

stream = spark.readStream.format("cloudFiles")\

.option("cloudFiles.format", al_in_file_format)\

.option("multiLine", True) \

.option('cloudFiles.allowOverwrites',True)\

.option("cloudFiles.useNotifications", True)\

.option("cloudFiles.subscriptionId", subscribtion)\

.option("cloudFiles.tenantId", tenant_id)\

.option("cloudFiles.clientId", client_id)\

.option("cloudFiles.clientSecret", client_secret)\

.option("cloudFiles.resourceGroup", "orcus-eastus")\

.schema(schema_from_data_catalog)\

.load(row["al_json_loc"])\

.withColumn("CurrTime",current_timestamp())\

.withColumn("filePath",input_file_name())\

.writeStream.format(al_out_format) \

.option('checkpointLocation', row["al_checkpoint_path"]) \

.option('mergeSchema', True)\

.outputMode("append")\

.table(databasetable)

Our schema is this format

{

"$schema": "http://json-schema.org/draft-07/schema#",

"type": "object",

"title": "LTE Insights",

"description": "LTE Insights",

"properties": {

"id": {

"type": "string",

"description": "Unique identifier"

"name": {

"type": "string",

"description": "Name"

}

But Autoloader expect a schema with this format. {

"fields": [

{

"metadata": {},

"name": "id",

"nullable": true,

"type": "string"

{

"metadata": {},

"name": "name",

"nullable": true,

"type": "string"

{

"metadata": {},

"name": "_rescued_data",

"nullable": true,

"type": "string"

{

"metadata": {},

"name": "CurrTime",

"nullable": true,

"type": "timestamp"

{

"metadata": {},

"name": "filePath",

"nullable": true,

"type": "string"

}

"type": "struct"

}

Is there a way to convert the schema to schema format that Autoloader expects?

topic Schema supported by Autoloader in Data Engineering

Schema supported by Autoloader