public async static Task <bool> manifestToSQL(AdlsContext adlsContext, string manifestName, string localRoot, List <SQLStatement> statemensList, string datasourceName = "")
{
ManifestHandler manifestHandler = new ManifestHandler(adlsContext, localRoot);
CdmManifestDefinition manifest = await manifestHandler.cdmCorpus.FetchObjectAsync <CdmManifestDefinition>(manifestName + ".manifest.cdm.json");
if (manifest == null)
{
Console.WriteLine($"Manifest: {manifestName } at Location {localRoot} is invalid");
return(false);
}
foreach (var submanifest in manifest.SubManifests)
{
string subManifestName = submanifest.ManifestName;
await manifestToSQL(adlsContext, subManifestName, localRoot + '/' + subManifestName, statemensList, datasourceName);
}
foreach (CdmEntityDeclarationDefinition eDef in manifest.Entities)
{
string entityName = eDef.EntityName;
string dataLocation;
if (eDef.DataPartitions.Count > 0)
{
dataLocation = eDef.DataPartitions[0].Location;
}
else
{
dataLocation = $"{localRoot}/{entityName}/*.*";
}
string fileName = dataLocation.Substring(dataLocation.LastIndexOf("/") + 1);
string ext = fileName.Substring(fileName.LastIndexOf("."));
dataLocation = dataLocation.Replace(fileName, "*" + ext);
string dataSource = "";
if (datasourceName == "")
{
localRoot = $"https://{adlsContext.StorageAccount}{adlsContext.FileSytemName}{localRoot}";
}
else
{
dataSource = $", DATA_SOURCE = '{datasourceName}'";
}
dataLocation = dataLocation.Replace("adls:", localRoot);
var entSelected = await manifestHandler.cdmCorpus.FetchObjectAsync <CdmEntityDefinition>(eDef.EntityPath, manifest);
string columnDef = string.Join(", ", entSelected.Attributes.Select(i => CdmTypeToSQl((CdmTypeAttributeDefinition)i)));;
var sql = $"CREATE OR ALTER VIEW {entityName} AS SELECT * FROM OPENROWSET(BULK '{dataLocation}', FORMAT = 'CSV', Parser_Version = '2.0' {dataSource}) WITH({columnDef}) as r ";
statemensList.Add(new SQLStatement()
{
Statement = sql
});
}
return(true);
}
public async static Task <SQLStatements> CDMToSQL(AdlsContext adlsContext, string storageAccount, string rootFolder, string localFolder, string manifestName, string SAS, string pass, bool createDS)
{
SQLStatements statements = new SQLStatements();
List <SQLStatement> statementsList = new List <SQLStatement>();
var SQLHandler = new SQLHandler(System.Environment.GetEnvironmentVariable("SQL-On-Demand"));
var adlsURI = "https://" + storageAccount;
var sqlOnDemand = SQLHandler.createCredentialsOrDS(createDS, adlsURI, rootFolder, SAS, pass, dataSourceName);
await ManifestHandler.manifestToSQL(adlsContext, manifestName, localFolder, statementsList, createDS);
statements.Statements = statementsList;
SQLHandler.executeStatements(statements);
return(statements);
}
public async static Task <bool> manifestToSQLMetadata(AdlsContext adlsContext, string manifestName, string localRoot, List <SQLMetadata> metadataList)
{
ManifestHandler manifestHandler = new ManifestHandler(adlsContext, localRoot);
CdmManifestDefinition manifest = await manifestHandler.cdmCorpus.FetchObjectAsync <CdmManifestDefinition>(manifestName + ".manifest.cdm.json");
if (manifest == null)
{
Console.WriteLine($"Manifest: {manifestName } at Location {localRoot} is invalid");
return(false);
}
foreach (var submanifest in manifest.SubManifests)
{
string subManifestName = submanifest.ManifestName;
await manifestToSQLMetadata(adlsContext, subManifestName, localRoot + '/' + subManifestName, metadataList);
}
foreach (CdmEntityDeclarationDefinition eDef in manifest.Entities)
{
string entityName = eDef.EntityName;
string dataLocation;
if (eDef.DataPartitionPatterns.Count > 0)
{
var pattern = eDef.DataPartitionPatterns.First();
dataLocation = localRoot + "/" + pattern.RootLocation + pattern.GlobPattern;
}
else if (eDef.DataPartitions.Count > 0)
{
dataLocation = eDef.DataPartitions[0].Location;
string nameSpace = dataLocation.Substring(0, dataLocation.IndexOf(":") + 1);
if (nameSpace != "")
{
dataLocation = dataLocation.Replace(nameSpace, localRoot);
}
else
{
if (dataLocation.StartsWith('/') || localRoot.EndsWith('/'))
{
dataLocation = localRoot + dataLocation;
}
else
{
dataLocation = localRoot + "/" + dataLocation;
}
}
}
else
{
dataLocation = $"{localRoot}/{entityName}/*.*";
}
string fileName = dataLocation.Substring(dataLocation.LastIndexOf("/") + 1);
string ext = fileName.Substring(fileName.LastIndexOf("."));
dataLocation = dataLocation.Replace(fileName, "*" + ext);
var entSelected = await manifestHandler.cdmCorpus.FetchObjectAsync <CdmEntityDefinition>(eDef.EntityPath, manifest);
string columnDef = string.Join(", ", entSelected.Attributes.Select(i => CdmTypeToSQl((CdmTypeAttributeDefinition)i)));;
metadataList.Add(new SQLMetadata()
{
entityName = entityName, columnDefinition = columnDef, dataLocation = dataLocation
});
}
return(true);
}
public async Task <bool> manifestToModelJson(AdlsContext adlsContext, string manifestName, string localRoot, CdmManifestDefinition modelJson = null, bool root = true)
{
ManifestHandler manifestHandler = new ManifestHandler(adlsContext, localRoot);
CdmManifestDefinition manifest;
if (root)
{
// Add to root folder.
var cdmFolderDefinition = manifestHandler.cdmCorpus.Storage.FetchRootFolder("adls");
// Read if model.json exists.
modelJson = await manifestHandler.cdmCorpus.FetchObjectAsync <CdmManifestDefinition>("model.json");
if (modelJson == null)
{
// Make the temp manifest and add it to the root of the local documents in the corpus
modelJson = manifestHandler.cdmCorpus.MakeObject <CdmManifestDefinition>(CdmObjectType.ManifestDef, "model.json");
// Add an import to the foundations doc so the traits about partitons will resolve nicely
modelJson.Imports.Add(FoundationJsonPath);
// Add to root folder.
cdmFolderDefinition.Documents.Add(modelJson, $"model.json");
}
}
manifest = await manifestHandler.cdmCorpus.FetchObjectAsync <CdmManifestDefinition>(manifestName + ".manifest.cdm.json");
Console.WriteLine($"Reading Manifest : {manifest.Name}");
foreach (var submanifest in manifest.SubManifests)
{
string subManifestName = submanifest.ManifestName;
await this.manifestToModelJson(adlsContext, subManifestName, localRoot + '/' + subManifestName, modelJson, false);
}
foreach (CdmEntityDeclarationDefinition eDef in manifest.Entities.ToList())
{
Console.WriteLine($"Adding Entity : {eDef.EntityName}");
var cdmEntityDefinition = this.CreateCdmEntityDefinition(eDef);
var cdmEntityDocument = this.CreateDocumentDefinition(cdmEntityDefinition);
// Add Imports to the entity document.
cdmEntityDocument.Imports.Add(FoundationJsonPath);
// Add the document to the root of the local documents in the corpus.
var cdmFolderDefinition = modelJson.Ctx.Corpus.Storage.FetchRootFolder("adls");
cdmFolderDefinition.Documents.Add(cdmEntityDocument, cdmEntityDocument.Name);
// Add the entity to the manifest.
modelJson.Entities.Add(cdmEntityDefinition);
CdmEntityDeclarationDefinition modelJsonEdef = modelJson.Entities.Item(eDef.EntityName);
if (eDef.DataPartitions.Count > 0)
{
var dataPartition = eDef.DataPartitions.First();
dataPartition.Location = manifestName + "/" + dataPartition.Location;
modelJsonEdef.DataPartitions.Add(dataPartition);
}
if (eDef.DataPartitionPatterns.Count > 0)
{
var DataPartitionPatterns = eDef.DataPartitionPatterns.First();
DataPartitionPatterns.RootLocation = manifestName + "/" + DataPartitionPatterns.RootLocation;
modelJsonEdef.DataPartitionPatterns.Add(DataPartitionPatterns);
}
}
bool created = false;
if (root)
{
await modelJson.FileStatusCheckAsync();
created = await modelJson.SaveAsAsync("model.json", true);
}
return(created);
}
