public static async Task <IActionResult> manifestToSQLDDL( [HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)] HttpRequest req, ILogger log, ExecutionContext context) { log.LogInformation("C# HTTP trigger function processed a request."); //get data from string tenantId = req.Headers["TenantId"]; string storageAccount = req.Headers["StorageAccount"]; string rootFolder = req.Headers["RootFolder"]; string localFolder = req.Headers["ManifestLocation"]; string manifestName = req.Headers["ManifestName"]; string dataSourceName = req.Headers["DataSourceName"]; string DDLType = req.Headers["DDLType"]; AdlsContext adlsContext = new AdlsContext() { StorageAccount = storageAccount, FileSytemName = rootFolder, MSIAuth = true, TenantId = tenantId }; // Read Manifest metadata log.Log(LogLevel.Information, "Reading Manifest metadata"); List <SQLMetadata> metadataList = new List <SQLMetadata>(); await ManifestHandler.manifestToSQLMetadata(adlsContext, manifestName, localFolder, metadataList); // convert metadata to DDL log.Log(LogLevel.Information, "Converting metadata to DDL"); var statementsList = await ManifestHandler.SQLMetadataToDDL(metadataList, DDLType, dataSourceName); return(new OkObjectResult(JsonConvert.SerializeObject(statementsList))); }
static void Main(string[] args) { //get data from string storageAccount = ConfigurationManager.AppSettings["StorageAccount"]; string rootFolder = ConfigurationManager.AppSettings["RootFolder"]; string localFolder = ConfigurationManager.AppSettings["ManifestLocation"]; string manifestName = ConfigurationManager.AppSettings["ManifestName"]; var TenantId = ConfigurationManager.AppSettings["TenantId"]; var AppId = ConfigurationManager.AppSettings["AppId"]; var AppSecret = ConfigurationManager.AppSettings["AppSecret"]; bool createDS = System.Convert.ToBoolean(ConfigurationManager.AppSettings["CreateDS"]); var SAS = ConfigurationManager.AppSettings["SAS"]; var pass = ConfigurationManager.AppSettings["Password"]; AdlsContext adlsContext = new AdlsContext() { StorageAccount = storageAccount, FileSytemName = rootFolder, TenantId = TenantId, ClientAppId = AppId, ClientSecret = AppSecret }; var statements = ManifestHandler.CDMToSQL(adlsContext, storageAccount, rootFolder, localFolder, manifestName, SAS, pass, createDS); Console.WriteLine(JsonConvert.SerializeObject(statements)); }
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 static async Task <IActionResult> manifestToModelJson( [HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)] HttpRequest req, ILogger log, ExecutionContext context) { log.LogInformation("C# HTTP trigger function processed a request."); //get data from string tenantId = req.Headers["TenantId"]; string storageAccount = req.Headers["StorageAccount"]; string rootFolder = req.Headers["RootFolder"]; string localFolder = req.Headers["ManifestLocation"]; string manifestName = req.Headers["ManifestName"]; AdlsContext adlsContext = new AdlsContext() { StorageAccount = storageAccount, FileSytemName = rootFolder, MSIAuth = true, TenantId = tenantId }; // Read Manifest metadata log.Log(LogLevel.Information, "Reading Manifest metadata"); ManifestHandler manifestHandler = new ManifestHandler(adlsContext, localFolder); bool created = await manifestHandler.manifestToModelJson(adlsContext, manifestName, localFolder); return(new OkObjectResult("{\"Status\":" + created + "}")); }
public static async Task <IActionResult> manifestToSynapseView( [HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)] HttpRequest req, ILogger log, ExecutionContext context) { log.LogInformation("C# HTTP trigger function processed a request."); //get data from string storageAccount = req.Headers["StorageAccount"]; string rootFolder = req.Headers["RootFolder"]; string localFolder = req.Headers["ManifestLocation"]; string manifestName = req.Headers["ManifestName"]; var TenantId = System.Environment.GetEnvironmentVariable("TenantId"); var AppId = System.Environment.GetEnvironmentVariable("AppId");; var AppSecret = System.Environment.GetEnvironmentVariable("AppSecret"); bool createDS = System.Convert.ToBoolean(System.Environment.GetEnvironmentVariable("CreateDS")); var SAS = System.Environment.GetEnvironmentVariable("SAS"); var pass = System.Environment.GetEnvironmentVariable("Password"); AdlsContext adlsContext = new AdlsContext() { StorageAccount = storageAccount, FileSytemName = rootFolder, TenantId = TenantId, ClientAppId = AppId, ClientSecret = AppSecret }; var statements = await ManifestHandler.CDMToSQL(adlsContext, storageAccount, rootFolder, localFolder, manifestName, SAS, pass, createDS); return(new OkObjectResult(JsonConvert.SerializeObject(statements))); }
private void mountStorage(AdlsContext adlsContext, string localFolder) { string firstChar; string rootFolder = adlsContext.FileSytemName; firstChar = rootFolder.Substring(0, 1); if (firstChar != "/") { rootFolder = "/" + rootFolder; } firstChar = localFolder.Substring(0, 1); if (firstChar != "/") { localFolder = "/" + localFolder; } if (rootFolder.EndsWith("/")) { rootFolder = rootFolder.Remove(rootFolder.Length - 1, 1); } if (localFolder.EndsWith("/")) { localFolder = localFolder.Remove(localFolder.Length - 1, 1); } if (adlsContext.MSIAuth == true) { MSITokenProvider MSITokenProvider = new MSITokenProvider($"https://{adlsContext.StorageAccount}/", adlsContext.TenantId); cdmCorpus.Storage.Mount("adls", new ADLSAdapter( adlsContext.StorageAccount, // Hostname. rootFolder + localFolder, // Root. MSITokenProvider )); } else if (adlsContext.ClientAppId != null && adlsContext.ClientSecret != null) { cdmCorpus.Storage.Mount("adls", new ADLSAdapter( adlsContext.StorageAccount, // Hostname. rootFolder + localFolder, // Root. adlsContext.TenantId, // Tenant ID. adlsContext.ClientAppId, // Client ID. adlsContext.ClientSecret // Client secret. )); } else if (adlsContext.SharedKey != null) { cdmCorpus.Storage.Mount("adls", new ADLSAdapter( adlsContext.StorageAccount, // Hostname. rootFolder + localFolder, // Root. adlsContext.SharedKey )); } cdmCorpus.Storage.DefaultNamespace = "adls"; // local is our default. so any paths that start out navigating without a device tag will assume local }
public static async Task <IActionResult> excecute( [HttpTrigger(AuthorizationLevel.Function, "post", Route = null)] HttpRequest req, ILogger log, ExecutionContext context) { log.LogInformation("C# HTTP trigger function processed a request."); //get data from string storageAccount = req.Headers["StorageAccount"]; string rootFolder = req.Headers["RootFolder"]; string localFolder = req.Headers["LocalFolder"]; string resolveReference = req.Headers["ResolveReference"]; string requestBody = await new StreamReader(req.Body).ReadToEndAsync(); EntityList entityList = JsonConvert.DeserializeObject <EntityList>(requestBody); var TenantId = System.Environment.GetEnvironmentVariable("TenantId"); var AppId = System.Environment.GetEnvironmentVariable("AppId");; var AppSecret = System.Environment.GetEnvironmentVariable("AppSecret"); AdlsContext adlsContext = new AdlsContext() { StorageAccount = storageAccount, FileSytemName = rootFolder, TenantId = TenantId, ClientAppId = AppId, ClientSecret = AppSecret }; ManifestHandler manifestHandler = new ManifestHandler(adlsContext, localFolder); bool resolveRef = false; if (resolveReference.Equals("true", StringComparison.OrdinalIgnoreCase)) { resolveRef = true; } bool ManifestCreated = await manifestHandler.createManifest(entityList, resolveRef); //Folder structure Tables/AccountReceivable/Group var subFolders = localFolder.Split('/'); string localFolderPath = ""; for (int i = 0; i < subFolders.Length - 1; i++) { var currentFolder = subFolders[i]; var nextFolder = subFolders[i + 1]; localFolderPath = $"{localFolderPath}/{currentFolder}"; ManifestHandler SubManifestHandler = new ManifestHandler(adlsContext, localFolderPath); await SubManifestHandler.createSubManifest(currentFolder, nextFolder); } var status = new ManifestStatus() { ManifestName = entityList.manifestName, IsManifestCreated = ManifestCreated }; return(new OkObjectResult(JsonConvert.SerializeObject(status))); }
static void Main(string[] args) { //get data from string tenantId = "979fd422-22c4-4a36-bea6-1cf87b6502dd"; string storageAccount = "ftanalyticsd365fo.dfs.core.windows.net"; string rootFolder = "/dynamics365-financeandoperations/analytics.sandbox.operations.dynamics.com/"; string localFolder = "Tables/Finance/Ledger/Main"; string manifestName = "Main"; var connectionString = "Server=ftsasynapseworkspace-ondemand.sql.azuresynapse.net;Database=AnalyticsAXDB"; string dataSourceName = "sqlOnDemandDS"; AdlsContext adlsContext = new AdlsContext() { StorageAccount = storageAccount, FileSytemName = rootFolder, MSIAuth = true, TenantId = tenantId }; // Read Manifest metadata Console.WriteLine("Reading Manifest metadata"); List <SQLMetadata> metadataList = new List <SQLMetadata>(); ManifestHandler.manifestToSQLMetadata(adlsContext, manifestName, localFolder, metadataList); // convert metadata to DDL Console.WriteLine("Converting metadata to DDL"); var statementsList = ManifestHandler.SQLMetadataToDDL(metadataList, "SynapseView", dataSourceName); // Execute DDL Console.WriteLine("Executing DDL"); SQLHandler sQLHandler = new SQLHandler(connectionString, tenantId); var statements = new SQLStatements { Statements = statementsList.Result }; sQLHandler.executeStatements(statements); Console.WriteLine(JsonConvert.SerializeObject(statements)); }
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); }
static void Main(string[] args) { //get data from config string tenantId = ConfigurationManager.AppSettings.Get("TenantId"); //"979fd422-22c4-4a36-bea6-1cf87b6502dd"; string storageAccount = ConfigurationManager.AppSettings.Get("StorageAccount"); //"ftfinanced365fo.dfs.core.windows.net"; string accessKey = ConfigurationManager.AppSettings.Get("AccessKey"); string rootFolder = ConfigurationManager.AppSettings.Get("RootFolder"); //"/dynamics365-financeandoperations/finance.sandbox.operations.dynamics.com/"; string manifestFilePath = ConfigurationManager.AppSettings.Get("ManifestFilePath"); var targetDbConnectionString = ConfigurationManager.AppSettings.Get("TargetDbConnectionString"); //"Server=ftsasynapseworkspace-ondemand.sql.azuresynapse.net;Database=Finance_AXDB"; string dataSourceName = ConfigurationManager.AppSettings.Get("DataSourceName"); //"finance" ; string DDLType = ConfigurationManager.AppSettings.Get("DDLType"); //"SynapseExternalTable"; string schema = ConfigurationManager.AppSettings.Get("Schema"); //"ChangeFeed"; string fileFormat = ConfigurationManager.AppSettings.Get("FileFormat"); //"CSV"; string convertToDateTimeStr = ConfigurationManager.AppSettings.Get("CovertDateTime"); //"CSV"; string TableNames = ConfigurationManager.AppSettings.Get("TableNames"); //"CSV"; NameValueCollection sAll = ConfigurationManager.AppSettings; foreach (string s in sAll.AllKeys) { Console.WriteLine("Key: " + s + " Value: " + sAll.Get(s)); } if (String.IsNullOrEmpty(manifestFilePath)) { Console.WriteLine("Enter Manifest file relative path:(/Tables/Tables.manifest.cdm.json or /Tables/model.json)"); manifestFilePath = Console.ReadLine(); } string manifestName = Path.GetFileName(manifestFilePath); string localFolder = manifestFilePath.Replace(manifestName, ""); bool MSIAuth; if (String.IsNullOrEmpty(accessKey)) { MSIAuth = true; } else { MSIAuth = false; } AdlsContext adlsContext = new AdlsContext() { StorageAccount = storageAccount, FileSytemName = rootFolder, MSIAuth = MSIAuth, TenantId = tenantId, SharedKey = accessKey }; // Read Manifest metadata Console.WriteLine($"Reading Manifest metadata https://{storageAccount}{rootFolder}{manifestFilePath}"); bool convertDateTime = false; if (convertToDateTimeStr.ToLower() == "true") { convertDateTime = true; } List <SQLMetadata> metadataList = new List <SQLMetadata>(); ManifestHandler.manifestToSQLMetadata(adlsContext, manifestName, localFolder, metadataList, convertDateTime); // convert metadata to DDL Console.WriteLine("Converting metadata to DDL"); var statementsList = ManifestHandler.SQLMetadataToDDL(metadataList, DDLType, schema, fileFormat, dataSourceName, TableNames); // Execute DDL Console.WriteLine("Executing DDL"); SQLHandler sQLHandler = new SQLHandler(targetDbConnectionString, tenantId); var statements = new SQLStatements { Statements = statementsList.Result }; try { sQLHandler.executeStatements(statements); foreach (var statement in statements.Statements) { Console.WriteLine(statement.Statement); Console.WriteLine("Status:" + statement.Created); Console.WriteLine("Detail:" + statement.Detail); } } catch (Exception e) { Console.WriteLine("ERROR executing SQL"); Console.WriteLine(e.Message); } Console.WriteLine("Press any key to exit"); Console.ReadLine(); }
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 ManifestHandler(AdlsContext adlsContext, string currentFolder) { cdmCorpus = new CdmCorpusDefinition(); this.mountStorage(adlsContext, currentFolder); }
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); }
static void Main(string[] args) { //get data from config string tenantId = ConfigurationManager.AppSettings.Get("TenantId"); //"979fd422-22c4-4a36-bea6-1cf87b6502dd"; string storageAccount = ConfigurationManager.AppSettings.Get("StorageAccount"); //"ftfinanced365fo.dfs.core.windows.net"; string rootFolder = ConfigurationManager.AppSettings.Get("RootFolder"); //"/dynamics365-financeandoperations/finance.sandbox.operations.dynamics.com/"; string localFolder = ConfigurationManager.AppSettings.Get("LocalFolder"); //"ChangeFeed"; string manifestName = ConfigurationManager.AppSettings.Get("ManifestName"); //"ChangeFeed"; var targetDbConnectionString = ConfigurationManager.AppSettings.Get("TargetDbConnectionString"); //"Server=ftsasynapseworkspace-ondemand.sql.azuresynapse.net;Database=Finance_AXDB"; string dataSourceName = ConfigurationManager.AppSettings.Get("DataSourceName"); //"finance" ; string DDLType = ConfigurationManager.AppSettings.Get("DDLType"); //"SynapseExternalTable"; string schema = ConfigurationManager.AppSettings.Get("Schema"); //"ChangeFeed"; string fileFormat = ConfigurationManager.AppSettings.Get("FileFormat"); //"CSV"; NameValueCollection sAll = ConfigurationManager.AppSettings; foreach (string s in sAll.AllKeys) { Console.WriteLine("Key: " + s + " Value: " + sAll.Get(s)); } AdlsContext adlsContext = new AdlsContext() { StorageAccount = storageAccount, FileSytemName = rootFolder, MSIAuth = true, TenantId = tenantId }; // Read Manifest metadata Console.WriteLine($"Reading Manifest metadata https://{storageAccount}{rootFolder}{localFolder}/{manifestName}.manifest.json"); List <SQLMetadata> metadataList = new List <SQLMetadata>(); ManifestHandler.manifestToSQLMetadata(adlsContext, manifestName, localFolder, metadataList); // convert metadata to DDL Console.WriteLine("Converting metadata to DDL"); var statementsList = ManifestHandler.SQLMetadataToDDL(metadataList, DDLType, schema, fileFormat, dataSourceName); // Execute DDL Console.WriteLine("Executing DDL"); SQLHandler sQLHandler = new SQLHandler(targetDbConnectionString, tenantId); var statements = new SQLStatements { Statements = statementsList.Result }; try { sQLHandler.executeStatements(statements); foreach (var statement in statements.Statements) { Console.WriteLine(statement.Statement); Console.WriteLine("Status:" + statement.Created); Console.WriteLine("Detail:" + statement.Detail); } } catch { Console.WriteLine("ERROR executing SQL"); } Console.WriteLine("Press any key to exit"); Console.ReadLine(); }