Forum Discussion

JonBFabric's avatar
JonBFabric
Helper I
8 months ago
Solved

Lakehouse Table Generate Create Table

Hi,   I maintain server lakehouses, and due to issues with the deployment pipelines tend to apply schema changes through script, and to retain the data in the table use the following process:   ...
data engineering
Lakehouse
  • JonBFabric's avatar
    JonBFabric
    8 months ago

    One final update on this.

     

    The logfile containing the schema is not necessarily the most recent, and there is not necessarily only one version of the schema. There is a schema associated with every modification made to the table structure, be that the original creation or subsequent alterations. Consequently, the logfile we need is the most recent with a schema.

     

    from delta.tables import DeltaTable
    import json
     
    # Note that the table name must be lowercase
    table_path = '<Path_To_Table>'
     
    # Identify log files
    log_dir = f"{table_path}/_delta_log"
    files = [f.path for f in notebookutils.fs.ls(log_dir) if f.name.endswith(".json")]
     
    # Identify log files with a schema
    filesWithSchema = []

    for file in sorted(files, reverse=True) :
        content = notebookutils.fs.head(file, 5000000)
        JSONdocs = content.split('\n')

        for doc in JSONdocs:
            if 'schemaString' in doc:
                filesWithSchema.append(file)
     
     
    # Load the header for the latest log file containing a schema
    latest = sorted(filesWithSchema, reverse=True)[0]
    content = notebookutils.fs.head(latest, 5000000)
     
    # Extract the schema
    JSONdocs = content.split('\n')

    for doc in JSONdocs:
        if 'schemaString' in doc:
            schemaString = json.loads(doc).get("metaData", {}).get("schemaString")

    # Extract Field Metadata
    FieldList = []
    OrdinalPosition = 0

    for field in json.loads(schemaString).get("fields") :
        OrdinalPosition += 1
        FieldDetails = {}
        FieldDetails['FieldName'] = field.get("name")
        FieldDetails['Nullable'] = field.get("nullable")
        FieldDetails['OrdinalPosition'] = OrdinalPosition

        match field.get("type").split('(')[0]:
            case 'string':
                FieldDetails['SQLType'] = field.get("metadata").get("__CHAR_VARCHAR_TYPE_STRING")
            case 'timestamp':
                FieldDetails['SQLType'] = 'timestamp'
            case 'date':
                FieldDetails['SQLType'] = 'date'
            case 'integer':
                FieldDetails['SQLType'] = 'int'
            case 'short':
                FieldDetails['SQLType'] = 'smallint'
            case 'long':
                FieldDetails['SQLType'] = 'bigint'
            case 'decimal':
                FieldDetails['SQLType'] = field.get("type")
            case 'boolean':
                FieldDetails['SQLType'] = 'boolean'

        FieldList.append(FieldDetails)


    display(FieldList)
JonBFabric's avatar
JonBFabric
Helper I
8 months ago

Whilst I understand everything that you are saying, I would like to describe another scenario which suggests that some of the above is not actually correct. 

 

Using only SparkSQL in a notebook I have created a lakehouse table with a single varchar(10) field. If I try to insert any value with more than 10 characters I get the following error:

 

[DELTA_EXCEED_CHAR_VARCHAR_LIMIT] Exceeds char/varchar type length limitation. Failed check: (isnull('String) OR (length('String) <= 10)).

 

Obviously something in the spark engine or the delta table metadata ia storing the size restriction

v-hashadapu's avatar
v-hashadapu
Community Support
8 months ago

Hi JonBFabric , Thank you for reaching out to the Microsoft Community Forum.

 

Yes, Spark/Delta can record and enforce CHAR(n) / VARCHAR(n) when a table is created through Spark or other Delta-aware APIs; the engine stores that constraint in the Delta metadata and will reject writes that exceed the declared width (hence the DELTA_EXCEED_CHAR_VARCHAR_LIMIT error). The authoritative place to get that declaration is the Spark/Delta surface (for example, run SHOW CREATE TABLE or DESCRIBE TABLE EXTENDED in a Spark notebook or read the Delta transaction log/Delta Table API). Those commands return the DDL/metadata that Spark/Delta actually enforces.

 

Do not rely on the Fabric SQL analytics endpoint or INFORMATION_SCHEMA alone to recover declared widths. Those surfaces present a T-SQL compatibility projection that can inflate, cap or otherwise transform reported lengths (the 4×/8000 behaviour you saw) and therefore are not a trustworthy source of the original Spark declared sizes. If you cannot run Spark against the table, your fallback is to inspect the _delta_log or compute observed max character/byte lengths and reconstruct conservative VARCHAR widths and for long term safety you must version-control the DDL or persist it as table metadata at creation time.