How to create Delta Live table from Json files using Custom schema? I am getting the below error for the attached code # Error org.apache.spark.sql.AnalysisException: Table has a user-specified schema that is incompatible with the schema

[Abhradwip]

#### Code

# CodeImport DataType

from pyspark.sql.types import StructType, StructField, TimestampType, IntegerType, StringType, FloatType, BooleanType, LongType

# Define Custom Schema

call_schema = StructType(

  [

    StructField("RecordType", StringType(), True),

    StructField("SystemIdentity", StringType(), True),

    StructField("FileNum", StringType(), True),

    StructField("SwitchNum", StringType(), True),

    StructField("CallingNum", StringType(), True),

    StructField("CallingIMSI", StringType(), True),

    StructField("CalledNum", StringType(), True),

    StructField("CalledIMSI", StringType(), True),

    StructField("DateS", StringType(), True),

    StructField("TimeS", StringType(), True),

    StructField("TimeType", LongType(), True),

    StructField("CallPeriod", LongType(), True),

    StructField("CallingCellID", StringType(), True),

    StructField("CalledCellID", StringType(), True),

    StructField("ServiceType", StringType(), True),

    StructField("Transfer", LongType(), True),

    StructField("IncomingTrunk", StringType(), True),

    StructField("OutgoingTrunk", StringType(), True),

    StructField("MSRN", StringType(), True),

    StructField("CalledNum2", StringType(), True),

    StructField("FCIFlag", StringType(), True),

    StructField("callrecTime", TimestampType(), True),

    StructField("EventProcessedUtcTime", TimestampType(), True),

    StructField("PartitionId", LongType(), True),

    StructField("EventEnqueuedUtcTime", TimestampType(), True),

  ]

)

# Define Delta Live Table

import dlt

from pyspark.sql.functions import *

from pyspark.sql.types import *

json_path = "/mnt/adlspoc23/stream/"

@dlt.table(

 comment="The raw Telecom callstream dataset, ingested from ADLS.",

 schema=call_schema

)

def callstream_raw():

 return (spark.read.format("json").load(json_path))

# Error

org.apache.spark.sql.AnalysisException: Table 'callstream_raw' has a user-specified schema that is incompatible with the schema inferred from its query.

[Anonymous]

@Abhradwip Mukherjee​ ; The error you're seeing occurs because the schema you're specifying for your Delta Live table is not compatible with the schema of the data being read from the JSON files.

You should check that the schema you have specified matches the schema of the data you are trying to load. You can also try loading the data without specifying a schema, allowing Spark to infer the schema automatically

You can try to update the code to include an explicit schema definition when reading the JSON files so that it matches the schema you've specified for the Delta Live table.

Hope this helps you to debug!

Asides, the code you presented seems okay.

[Abhradwip]

Thanks Suteja, but the issue has been resolved. We can actually use custom schema in delta live.

[Anonymous]

Hi @Abhradwip Mukherjee​ 

Hope all is well! Just wanted to check in if you were able to resolve your issue and would you be happy to share the solution or mark an answer as best? Else please let us know if you need more help. 

We'd love to hear from you.

Thanks!