//public static string GenerateADFPipelineSQLQuery(string tableName, // List<DataElement> inOutParams, string dateField, bool isDataQuery, CopyOnPremSQLToADLAType copyDataType) //{ // string sqlQuery = "$$Text.Format('select "; // int itemIteration = 0; // foreach (var columnName in inOutParams) // { // if (itemIteration < inOutParams.Count - 1) // { // sqlQuery = sqlQuery + "[" + columnName.Name + "],"; // itemIteration = itemIteration + 1; // } // else // { // sqlQuery = sqlQuery + "[" + columnName.Name + "]"; // } // } // if(!(isDataQuery && copyDataType.ToString() == CopyOnPremSQLToADLAType.All.ToString())) // { // sqlQuery = sqlQuery + " from " + tableName + " where [" + dateField + "] >= \\'{0:yyyy-MM-dd HH:mm}\\' AND " + // " [" + dateField + "] < \\'{1:yyyy-MM-dd HH:mm}\\'', " + // "WindowStart, WindowEnd)"; // return sqlQuery; // } // switch (copyDataType) // { // case CopyOnPremSQLToADLAType.LastIteration: // { // break; // } // case CopyOnPremSQLToADLAType.All: // { // sqlQuery = sqlQuery + " from " + tableName + "')"; // break; // } // case CopyOnPremSQLToADLAType.Distinct: // { // break; // } // case CopyOnPremSQLToADLAType.Transactional: // { // sqlQuery = sqlQuery + " from " + tableName + " where [" + dateField + "] >= \\'{0:yyyy-MM-dd HH:mm}\\' AND " + // " [" + dateField + "] < \\'{1:yyyy-MM-dd HH:mm}\\'', " + // "WindowStart, WindowEnd)"; // break; // } // } // return sqlQuery; //} public static string GenerateADFPipelineSQLQuery(List <DataElement> inOutParams, string dateField, bool isDataQuery, CopyOnPremSQLToADLAType copyDataType) { string sqlQuery = "$$Text.Format('select "; string tableName = InitialParams.TableName; string paramsQuery = String.Empty; int itemIteration = 0; foreach (var columnName in inOutParams) { if (itemIteration < inOutParams.Count - 1) { paramsQuery = paramsQuery + "[" + columnName.Name + "],"; itemIteration = itemIteration + 1; } else { paramsQuery = paramsQuery + "[" + columnName.Name + "]"; } } if (!System.String.IsNullOrEmpty(paramsQuery)) { sqlQuery = sqlQuery + paramsQuery; } //if (!(isDataQuery && copyDataType.ToString() == CopyOnPremSQLToADLAType.All.ToString())) if (!(isDataQuery)) { sqlQuery = sqlQuery + " from " + tableName + " where [" + dateField + "] >= \\'{0:yyyy-MM-dd HH:mm}\\' AND " + " [" + dateField + "] < \\'{1:yyyy-MM-dd HH:mm}\\'', " + "WindowStart, WindowEnd)"; return(sqlQuery); } switch (copyDataType) { case CopyOnPremSQLToADLAType.LastIteration: { sqlQuery = sqlQuery + " from " + tableName + " where " + " CONVERT(varchar, RECORDDATEUTC, 101) = (select max(CONVERT(varchar, RECORDDATEUTC, 101)) from " + tableName + ")')"; break; } case CopyOnPremSQLToADLAType.All: { sqlQuery = sqlQuery + " from " + tableName + "')"; break; } case CopyOnPremSQLToADLAType.Distinct: { if (!String.IsNullOrEmpty(InitialParams.PrimaryKey)) { sqlQuery = sqlQuery + " from (Select " + paramsQuery + ",ROW_NUMBER() " + " over(partition by " + InitialParams.PrimaryKey + " order by " + dateField + " desc) pk from " + tableName + ") dat where pk = 1')"; } break; } case CopyOnPremSQLToADLAType.Transactional: { sqlQuery = sqlQuery + " from " + tableName + " where [" + dateField + "] >= \\'{0:yyyy-MM-dd HH:mm}\\' AND " + " [" + dateField + "] < \\'{1:yyyy-MM-dd HH:mm}\\'', " + "WindowStart, WindowEnd)"; break; } } //if(tableName.Contains("DSServiceNowCMNLocations")) //{ // sqlQuery = "$$Text.Format('SELECT [RECORDDATEUTC],[city]," + // "(select top 1 name from Fact_DSServiceNowCompaniesmetricdetails where sys_id = [company]) as [company]," + // "(select top 1 name from Fact_DSServiceNowUsersmetricdetails where sys_id = [contact] and name is not null) as [contact]," + // "[country],[fax_phone],[full_name],[lat_long_error],[latitude],[longitude],[name],[parent],[phone]," + // "[phone_territory],[state],[stock_room],[street],[sys_created_by],[sys_created_on],[sys_created_onId],[sys_id]," + // "[sys_mod_count],[sys_tags],[sys_updated_by],[sys_updated_on],[sys_updated_onId],[u_boolean_1]," + // "(select top 1 name from FACT_DSServiceNowUsersmetricdetails where sys_id = [u_chief_operating_officer]) as [u_chief_operating_officer]," + // "(select top 1 name from FACT_DSServiceNowUsersmetricdetails where sys_id = [u_contracts_manager]) as [u_contracts_manager], " + // "(select top 1 name from FACT_DSServiceNowUsersmetricdetails where sys_id = [u_head_of_delivery]) as [u_head_of_delivery], " + // "(select top 1 name from FACT_DSServiceNowUsersmetricdetails where sys_id = [u_project_manager]) as [u_project_manager], " + // "(select top 1 name from [FACT_DSServiceNowUsersmetricdetails] where sys_id = [u_reference_1]) as [u_reference_1]," + // "(select top 1 name from [FACT_DSServiceNowUsersmetricdetails] where sys_id = [u_reference_2]) as [u_reference_2]," + // "(select top 1 name from [FACT_DSServiceNowUsersmetricdetails] where sys_id = [u_reference_4]) as [u_reference_4], " + // "(select top 1 name from [FACT_DSServiceNowUsersmetricdetails] where sys_id = [u_reference_5]) as [u_reference_5], " + // "(select top 1 name from [FACT_DSServiceNowUsersmetricdetails] where sys_id = [u_regionalmanager_office]) as [u_regionalmanager_office]," + // "[u_string_1],[zip] from " + // "(Select[RECORDDATEUTC], [city], [company], [contact], [country], [fax_phone], [full_name], [lat_long_error], [latitude],[longitude]," + // " [name], [parent], [phone], [phone_territory], [state], [stock_room], [street], [sys_created_by], [sys_created_on]," + // "[sys_created_onId], [sys_id], [sys_mod_count], [sys_tags], [sys_updated_by], [sys_updated_on], [sys_updated_onId]," + // " [u_boolean_1],[u_chief_operating_officer], [u_contracts_manager], [u_head_of_delivery], [u_project_manager], " + // "[u_reference_1],[u_reference_2],[u_reference_4],[u_reference_5], " + // "[u_regionalmanager_office], [u_string_1], " + // "[zip], ROW_NUMBER() over(partition by sys_id order by recorddateutc desc) pk from FACT_DSServiceNowCMNLocationsmetricdetails) " + // "dat where pk = 1')"; //} return(sqlQuery); }
public static void DeployDatasetAndPipelines(string pipelineName, string activityName, string inDataSetName, string outDataSetName, string tableName, List <DataElement> lstElements, string fileName, string folderpath, string sqlQuery, DateTime recorddateUTC, bool IsDataDeploy, CopyOnPremSQLToADLAType cpType) { Dataset dsInput = null, dsOutput = null; try { DatasetGetResponse respGetInDatasets = client.Datasets.Get(resourceGroupName, dataFactoryName, inDataSetName); dsInput = respGetInDatasets.Dataset; } catch (Exception ex) { } if (dsInput == null) { //Create Input DataSet CreateOrUpdateInputDataSet(client, resourceGroupName, dataFactoryName, linkedServiceNameSource, inDataSetName, tableName, lstElements, IsDataDeploy); } try { DatasetGetResponse respGetOutDatasets = client.Datasets.Get(resourceGroupName, dataFactoryName, outDataSetName); dsOutput = respGetOutDatasets.Dataset; } catch (Exception ex) { } if (dsOutput == null) { //Create Output DataSet CreateOrUpdateOutputDataSet(client, resourceGroupName, dataFactoryName, linkedServiceNameDestination, outDataSetName, fileName, folderpath, lstElements, IsDataDeploy); } //Create Or Update Pipeline CreateOrUpdatePipeline(client, resourceGroupName, dataFactoryName, linkedServiceNameDestination, pipelineName, inDataSetName, outDataSetName, sqlQuery, recorddateUTC, activityName, IsDataDeploy, cpType); }
public static void CreateOrUpdatePipeline(DataFactoryManagementClient client, string resourceGroupName, string dataFactoryName, string linkedServiceName, string pipelineName, string dsInput, string dsOutput, string sqlQuery, DateTime recordDateUTC, string activityName, bool isDataDeploy, CopyOnPremSQLToADLAType cpType) { if (String.IsNullOrEmpty(sqlQuery)) { Console.WriteLine("Cannot create pipeline. Empty SQL Query"); return; } try { DateTime PipelineActivePeriodStartTime = DateTime.Now.Subtract(TimeSpan.FromDays(1000.00)); DateTime PipelineActivePeriodEndTime = PipelineActivePeriodStartTime; TimeSpan objActivityDelayPolicyAttr = TimeSpan.FromMinutes(Convert.ToDouble(InitialParams.DelayIntervalOfActivity)); string mode = PipelineMode.OneTime; if ((isDataDeploy))// && (!cpType.ToString().Equals(CopyOnPremSQLToADLAType.All.ToString()))) { recordDateUTC.AddHours(3); PipelineActivePeriodStartTime = recordDateUTC; PipelineActivePeriodEndTime = recordDateUTC.AddYears(100); mode = PipelineMode.Scheduled; } Scheduler objActivityScheduler = new Scheduler(); objActivityScheduler.Interval = Convert.ToUInt16(InitialParams.ActivityFrequencyInterval); if (isDataDeploy) { objActivityScheduler.Offset = TimeSpan.FromMinutes(Convert.ToDouble(InitialParams.OffsetIntervalOfDataSlice)); } if (InitialParams.SliceType == SliceType.Start && isDataDeploy) { objActivityScheduler.Style = SchedulerStyle.StartOfInterval; } switch (InitialParams.ActivityFrequencyType) { case Frequency.Month: { objActivityScheduler.Frequency = SchedulePeriod.Month; break; } case Frequency.Day: { objActivityScheduler.Frequency = SchedulePeriod.Day; break; } case Frequency.Hour: { objActivityScheduler.Frequency = SchedulePeriod.Hour; break; } case Frequency.Minute: { objActivityScheduler.Frequency = SchedulePeriod.Minute; break; } default: { objActivityScheduler.Frequency = SchedulePeriod.Day; break; } } if (client == null) { client = CreateManagementClientInstance(); } Activity activityInPipeline = new Activity() { Name = activityName, Inputs = new List <ActivityInput>() { new ActivityInput() { Name = dsInput } }, Outputs = new List <ActivityOutput>() { new ActivityOutput() { Name = dsOutput } }, TypeProperties = new CopyActivity() { Source = new SqlSource() { SqlReaderQuery = sqlQuery }, Sink = new AzureDataLakeStoreSink() { WriteBatchSize = 0, WriteBatchTimeout = TimeSpan.FromMinutes(0) } }, Policy = new ActivityPolicy() { Timeout = TimeSpan.FromMinutes(3.0), Delay = objActivityDelayPolicyAttr, Concurrency = 1, ExecutionPriorityOrder = ExecutionPriorityOrder.NewestFirst, LongRetry = 0, LongRetryInterval = TimeSpan.FromMinutes(0.0), Retry = 3 }, }; Pipeline pl = null; try { Console.WriteLine("Finding existing pipeline: " + pipelineName + " ... " + activityInPipeline.Name); PipelineGetResponse respPipelines = client.Pipelines.Get(resourceGroupName, dataFactoryName, pipelineName); pl = respPipelines.Pipeline; Console.WriteLine("FIND SUCCESS STATUS: Now updating existing pipeline " + pipelineName + " ... " + activityInPipeline.Name); pl.Properties.Activities.Add(activityInPipeline); PipelineCreateOrUpdateParameters plParameters = new PipelineCreateOrUpdateParameters() { Pipeline = pl }; client.Pipelines.CreateOrUpdate(resourceGroupName, dataFactoryName, plParameters); Console.WriteLine("updated successfully existing pipeline: " + pipelineName + " ... " + activityInPipeline.Name); } catch (Exception ex) { Console.WriteLine("Could not found any existing pipeline: " + ex.Message); } if (pl == null) { // Create a pipeline with a copy activity Console.WriteLine("Creating new pipeline " + pipelineName + " ... " + activityInPipeline.Name); client.Pipelines.CreateOrUpdate(resourceGroupName, dataFactoryName, new PipelineCreateOrUpdateParameters() { Pipeline = new Pipeline() { Name = pipelineName, Properties = new PipelineProperties() { Description = "Pipeline for data transfer from on-premise SC - SQL Server Datamart to Lake Store", Activities = new List <Activity>() { activityInPipeline }, // Initial value for pipeline's active period. With this, you won't need to set slice status Start = PipelineActivePeriodStartTime, End = PipelineActivePeriodEndTime, IsPaused = false, PipelineMode = mode, HubName = cgsHubName, ExpirationTime = TimeSpan.FromMinutes(0) }, } }); // Create a pipeline with a copy activity Console.WriteLine("created new pipeline " + pipelineName + " ... " + activityInPipeline.Name); } } catch (Exception ex) { Console.WriteLine("pipeline " + ex.Message); } }
//Create Data Sets and Pipelines - Deploy these in Azure Data Factory for a single Structure. public static void DeployADFDataSetsAndPipelines(CopyOnPremSQLToADLAType cpType) { //check if there is any data in the source structure. DateTime firstDateTimeRecordInTable; string tableName = InitialParams.TableName; if (System.String.IsNullOrEmpty(InitialParams.TableName)) { Console.WriteLine("source structure(SC table name) cannot be empty. Execution does not proceed further"); return; } //List the attribute, data type of each column List <DataElement> lstElements = ADFOperations.GenerateStructure(InitialParams.TableName); //if (System.String.IsNullOrEmpty(InitialParams.DataSourcePathInADLS)) //{ // InitialParams.DataSourcePathInADLS = Utils.GetdataSourceType(tableName); //} string dateTimeField = (String.IsNullOrEmpty(InitialParams.FilterDateTimeField) ? "recorddateutc" : InitialParams.FilterDateTimeField); folderPath = (String.IsNullOrEmpty(folderPath) ? ConfigurationSettings.AppSettings["folderPath"] : folderPath); firstDateTimeRecordInTable = ADFOperations.FetchFirstRowRecordDate(InitialParams.TableName, InitialParams.FilterDateTimeField); if (firstDateTimeRecordInTable <= DateTime.Now.Subtract(TimeSpan.FromHours(1))) { //re: INPUT DATASET - Prepare the SQL query required for pipeline to execute on Source System firstDateTimeRecordInTable = firstDateTimeRecordInTable.AddHours(3); string sqlQuery = ADFOperations.GenerateADFPipelineSQLQuery(lstElements, dateTimeField, false, cpType); string InOutDataSetNameRef = InitialParams.TablePathInADLS + "_" + cpType.ToString(); Console.WriteLine("Deploying data sets and pipelines for Headers"); string inDataSetName = String.Format("SC-{2}_DSI_H_{0}_{1}", InitialParams.Environment, InOutDataSetNameRef, InitialParams.TempCompPrefix); string outDataSetName = String.Format("SC-{2}_DSO_H_{0}_{1}", InitialParams.Environment, InOutDataSetNameRef, InitialParams.TempCompPrefix); string pipelineName = String.Format("SC-{2}_PL01_H_{0}_{1}", InitialParams.Environment, InOutDataSetNameRef, InitialParams.TempCompPrefix); string activityName = String.Format("Act-{2}_H_{0}_{1}", InitialParams.Environment, InOutDataSetNameRef, InitialParams.TempCompPrefix); string fileName = "Header_" + InOutDataSetNameRef; string folderpath = String.Format("{0}/{5}/DL-{1}/{4}/{2}/{3}", InitialParams.FolderPath, InitialParams.DataSourcePathInADLS, InitialParams.TablePathInADLS, InOutDataSetNameRef, InitialParams.TempCompPrefix, InitialParams.TempPathDeviation); DeployDatasetAndPipelines(pipelineName, activityName, inDataSetName, outDataSetName, tableName, lstElements, fileName, folderpath, sqlQuery, firstDateTimeRecordInTable, false, cpType); Console.WriteLine("Deployed data sets and pipelines for headers"); //re: OUTPUT DATASET - Prepare the SQL query required for pipeline to execute on Source System sqlQuery = ADFOperations.GenerateADFPipelineSQLQuery(lstElements, dateTimeField, true, cpType); Console.WriteLine("Deploying data sets and pipelines for data"); inDataSetName = String.Format("SC-{2}_DSI_D_{0}_{1}", InitialParams.Environment, InOutDataSetNameRef, InitialParams.TempCompPrefix); outDataSetName = String.Format("SC-{2}_DSO_D_{0}_{1}", InitialParams.Environment, InOutDataSetNameRef, InitialParams.TempCompPrefix); pipelineName = String.Format("SC-{2}_PL01_D_{0}_{1}", InitialParams.Environment, InOutDataSetNameRef, InitialParams.TempCompPrefix); activityName = String.Format("Act-{2}_D_{0}_{1}", InitialParams.Environment, InOutDataSetNameRef, InitialParams.TempCompPrefix); if (cpType.Equals(CopyOnPremSQLToADLAType.Distinct) || cpType.Equals(CopyOnPremSQLToADLAType.All) || cpType.Equals(CopyOnPremSQLToADLAType.Flattened) || cpType.Equals(CopyOnPremSQLToADLAType.LastIteration)) { fileName = "Data_" + InOutDataSetNameRef; } else { fileName = "Data_" + InOutDataSetNameRef + "-{year}-{month}-{day}-{hour}-{minute}"; } DeployDatasetAndPipelines(pipelineName, activityName, inDataSetName, outDataSetName, tableName, lstElements, fileName, folderpath, sqlQuery, firstDateTimeRecordInTable, true, cpType); Console.WriteLine("Deployed data sets and pipelines for data"); } else { Console.WriteLine(" Empty record date UTC for table : " + tableName); } }