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);
        }
コード例 #2
0
        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);
        }
コード例 #3
0
        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);
        }
コード例 #4
0
ファイル: ManifestHandler.cs プロジェクト: negudava/Analytics
        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);
        }