Reading cdm format (model.json) to create External Tables in Azure Synapse Serverlees Pool from ADLS containing CRM tables. How can it be resolved?

Question

Reading cdm format (model.json) to create External Tables in Azure Synapse Serverlees Pool from ADLS containing CRM tables. How can it be resolved?

Elshan Chalabiyev 1

My ADLS Gen2 receives tables from CRM's Dataverse in CDM format (with model.json) using Azure Synapse Link which is cofigured to link just to Storage Account (without Synapse Anlytics).
Now I want to create External Tables in my Synapse Analytics Serverless SQL Pool out of the CRM tables in the Storage Account.

The CSV files of the tables are without column names, the metadata of the tables are in model.json.

How can I tell Synapse to use that model.json for creation of the External Tables.

MartinJaffer-MSFT 26,236 Reputation points

2022-06-28T19:22:07.507+00:00
Hello and welcome to Microsoft Q&A @Elshan Chalabiyev .

As I understand, you have CDM data in your Storage Account. A model.json, and CSV files. The CSV files have no header row (column names).
You want to access these in Synapse Serverless SQL to do analysis or other stuff. Your desired way of accessing is External Tables rather than openrowset.
Looking manually for the schema and manually creating the tables is tedious, so you want to have some automated way of generating the Create External Table SQL script.

Okay. This is a simple desire, but the task is complicated to implement. I don't have a ready-built option, so first I will talk about the high-level tasks involved.

I have found a sample model.json to work from, but it did not come with any data files. This will make table-> file relations difficult.

Read the model.json in PySpark notebook

identify each table mentioned in the model.json . These seem to be called "Entities".

extract the column names and data types from model.json. Preserve order, as your data files do not have column names. It must be ordinal.

Examine a "Create external table" script, and determine a pattern.

use the pattern to create a function/procedure to which I can feed the table name, file location, and data types and column names. This should output the SQL create external table.

Run this function/procedure on every table

Take the output and run it as SQL

Make sense so far?

1 answer

Your answer

MartinJaffer-MSFT 26,236 Reputation points

2022-06-28T19:22:07.507+00:00

Hello and welcome to Microsoft Q&A @Elshan Chalabiyev .

As I understand, you have CDM data in your Storage Account. A model.json, and CSV files. The CSV files have no header row (column names).
You want to access these in Synapse Serverless SQL to do analysis or other stuff. Your desired way of accessing is External Tables rather than openrowset.
Looking manually for the schema and manually creating the tables is tedious, so you want to have some automated way of generating the Create External Table SQL script.

Okay. This is a simple desire, but the task is complicated to implement. I don't have a ready-built option, so first I will talk about the high-level tasks involved.

I have found a sample model.json to work from, but it did not come with any data files. This will make table-> file relations difficult.

Read the model.json in PySpark notebook

identify each table mentioned in the model.json . These seem to be called "Entities".

extract the column names and data types from model.json. Preserve order, as your data files do not have column names. It must be ordinal.

Examine a "Create external table" script, and determine a pattern.

use the pattern to create a function/procedure to which I can feed the table name, file location, and data types and column names. This should output the SQL create external table.

Run this function/procedure on every table

Take the output and run it as SQL

Make sense so far?

Answer 1

For ease of rapid prototyping, I downloaded the model.json and wrote some Python on my local machine.
Below is not a final version, but it seems to work. Some changes would need to be made if we want to run it on Synapse. Namely, changing model.json location from local file to blob address. Or could mount it.

Take a look and tell me what you think. @Elshan Chalabiyev

import json  
  
model_location = 'C:\\Users\\XXXX\\Downloads\\model.json'  
  
#CREATE EXTERNAL FILE FORMAT and CREAT EXTERNAL DATA SOURCE are separate things, not covered here.  
#however the file format and external data source are used in the SQL expression, so I need names for them  
file_format = "myFileFormat"  
data_source = "myDataSource"  
      
# if data types in model.json are not good types in SQL, make substitutions with below  
typemapping = { 'string':'varchar(256)', 'int64':'int', 'decimal':'decimal'}  
  
#magic sql writer  
def writeSQL( tablename, filelocation, columns, datasource, fileformat):  
    statement = 'CREATE EXTERNAL TABLE ' + tablename + " (\n\t"  
      
    for col in columns:  
        statement += col['name'] + ' '  
        if col['dataType'] not in typemapping:  
            statement += col['dataType']  
        else:  
            statement += typemapping[col['dataType']]  
        statement += ", \n\t"  
    statement = statement.rstrip(", \n\t")  
    statement += ")\nWITH (\n Location = '" + filelocation + "',\n DATA_SOURCE = " + datasource + ",\n FILE_FORMAT = " + fileformat + ");\n"  
    print(statement)  
    return statement  
  
#do the stuff  
with open(model_location, 'r') as raw_model:  
    model = json.load(raw_model)  
    for table in model['entities']:  
        writeSQL(table['name'], table['partitions'][0]['name'], table['attributes'], data_source, file_format)

The model.json took the form of (see below). Is yours vaguely similar?

Share via

Reading cdm format (model.json) to create External Tables in Azure Synapse Serverlees Pool from ADLS containing CRM tables. How can it be resolved?

1 answer

Your answer