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:

 

  1. Create a new table with the desired structure
  2. Insert all records from oringinal table in to new table
  3. After checking rounts drop the original table and rename the new table.

For Step 1, to be able base the new table on the existing table I need to be able to identify existing data types for all existing fields, and it appears there is no reliable way of doing this. The main issue relates to Char and Varchar fields, as there appears to be no current mothod for determining the appropriate character length.

 

I have tried various methods on the lakehouse, but those always show the fields to be String, with no maximum size.

 

I have also tried querying INFORMATION_SCHEMA COLUMNS through the sql endpoint, and the problem here is that value for CHARACTER_MAXIMUM_LENGTH appears to be 4 times the actual defined maximum number of characters, up to a maximum of 8000. I.e. A character length of 100 is shown as 400, 1000 is shown as 4000, 2000 and higher are always shown as 8000.

 

Does anyone know of a reliable way of generating a create statement for an existing lakehouse table?

 

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)

10 Replies

  • Zanqueta's avatar
    Zanqueta
    Super User
    8 months ago

    Hi JonBFabric,

     

    ✅ Why this happens

    • Delta tables in Fabric do not enforce fixed-length character types like CHAR(n) or VARCHAR(n); they store text as variable-length strings.
    • The SQL endpoint maps these to STRING for compatibility, so the original length constraint is not preserved.

    ✅ Why CHARACTER_MAXIMUM_LENGTH shows 4x

    • The SQL endpoint assumes UTF-16 encoding internally, so the reported length is multiplied by 4.
    • This is a known limitation and does not affect actual storage or query behaviour.

     

    Official References:

    What is a lakehouse? - Microsoft Fabric | Microsoft Learn

    Table utility commands | Delta Lake

     

    If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.

    Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.

    • JonBFabric's avatar
      JonBFabric
      Helper I
      8 months ago

      Thanks. Great to get the explanation as to what is happening and why. But going back to the original question...

       

      Is it possible to identify the SQL statement used to originally create a table? I'm getting the impression that the answer is no. And given that the maximum record length that can be handled by the SQL endpoint is 8060 bytes, those character limits are crucial and need to be tightly controlled.