//INIT PIPELINE ACTIVITIES
/**
* not used currently
*/
public Activity create_Activity_Init_1()
{
Console.WriteLine("Creating " + DualLoadConfig.ACTIVITY_INIT_1);
Activity activity = new Activity();
List <ActivityInput> activityInputs = new List <ActivityInput>();
ActivityInput activityInput = new ActivityInput();
activityInput.Name = DualLoadConfig.DATASET_ETL_Control;
activityInputs.Add(activityInput);
SqlSource source = new SqlSource();
source.SqlReaderQuery = DualLoadConfig.QUERY_INIT_1;
List <ActivityOutput> activityOutputs = new List <ActivityOutput>();
ActivityOutput activityOutput = new ActivityOutput();
activityOutput.Name = DualLoadConfig.DATASET_LOAD_1_SQLDUMMY;
activityOutputs.Add(activityOutput);
SqlSink sink = new SqlSink();
CopyActivity copyActivity = new CopyActivity();
copyActivity.Source = source;
copyActivity.Sink = sink;
activity.Name = DualLoadConfig.ACTIVITY_INIT_1;
activity.Inputs = activityInputs;
activity.Outputs = activityOutputs;
activity.TypeProperties = copyActivity;
return(activity);
}
private static void crearPipesSubidaNormal(DataFactoryManagementClient client)
{
var nombreTablas = DatosGrales.traerTablas(true);
var nombreSinSchema = DatosGrales.traerTablas(false);
List <Activity> la;
CopyActivity ca;
List <DatasetReference> inp;
DatasetReference dr;
List <DatasetReference> outp;
DatasetReference drO;
PipelineResource pipe;
string nombreTablaParaConsulta;
string consulta;
string nombreBD = DatosGrales.nombreBD;
for (int i = 0; i < nombreTablas.Length; i++)
{
if (esTablaEspecial(nombreTablas[i]))
{
//no creo nada porque es especial
}
else
{
nombreTablaParaConsulta = nombreTablas[i].Replace('-', '.');
consulta = DatosGrales.queryMagica(nombreTablaParaConsulta, 10000);
la = new List <Activity>();
ca = new CopyActivity();
ca.Name = "CopyPipeline-Sql-Lake-" + nombreTablas[i];
ca.Source = new SqlSource(null, 3, null, consulta);
ca.Sink = new SqlSink();
inp = new List <DatasetReference>();
dr = new DatasetReference("Dataset_" + nombreBD + "_" + nombreTablas[i]);
inp.Add(dr);
ca.Inputs = inp;
outp = new List <DatasetReference>();
drO = new DatasetReference("Dataset_" + nombreBD + "_DataLakeStore_" + nombreSinSchema[i]);
outp.Add(drO);
ca.Outputs = outp;
la.Add(ca);
pipe = new PipelineResource();
pipe.Activities = la;
client.Pipelines.CreateOrUpdate(DatosGrales.resourceGroup, DatosGrales.dataFactoryName, "Pipeline-Copy-" + nombreBD + "-" + nombreTablas[i], pipe);
Console.Write((i + 1) + ". Pipeline-Copy-" + nombreBD + "-" + nombreTablas[i] + " creado.\n");
}
}
}
//ARCHIVE PIPELINE ACTIVITIES
/**
* CopyActivity from Blob source to Blob sink from ToBeProcessed Folder to Archived Folder
*/
public Activity create_Activity_Archive_2(String toBeProcessedCompleteFolderDataset, String archiveFolderDataset, int i)
{
Console.WriteLine("Creating " + DualLoadConfig.ACTIVITY_ARCHIVE_2 + "_" + i);
Activity activity = new Activity();
List <ActivityInput> activityInputs = new List <ActivityInput>();
ActivityInput activityInput = new ActivityInput();
activityInput.Name = toBeProcessedCompleteFolderDataset;
activityInputs.Add(activityInput);
List <ActivityOutput> activityOutputs = new List <ActivityOutput>();
ActivityOutput activityOutput = new ActivityOutput();
activityOutput.Name = archiveFolderDataset + "_" + i;
activityOutputs.Add(activityOutput);
CopyActivity copyActivity = new CopyActivity();
BlobSource blobSource = new BlobSource();
copyActivity.Source = blobSource;
//blobSource.Recursive = true;
BlobSink sink = new BlobSink();
sink.CopyBehavior = "PreserveHierarchy";
//sink.WriteBatchSize = 10000;
//sink.WriteBatchTimeout = TimeSpan.FromMinutes(10);
copyActivity.Sink = sink;
//Scheduler scheduler = new Scheduler();
//scheduler.Frequency = SchedulePeriod.Hour;
//scheduler.Interval = 1;
activity.Name = DualLoadConfig.ACTIVITY_ARCHIVE_2 + "_" + i;
activity.Inputs = activityInputs;
activity.Outputs = activityOutputs;
activity.TypeProperties = copyActivity;
//activity.Scheduler = scheduler;
return(activity);
}
public override Activity ToSdkObject()
{
var activity = new CopyActivity(this.Name, this.Source?.ToSdkObject(), this.Sink?.ToSdkObject());
this.Inputs?.ForEach(item => activity.Inputs.Add(item));
this.Outputs?.ForEach(item => activity.Outputs.Add(item));
activity.Translator = this.Translator;
activity.EnableStaging = this.EnableStaging;
activity.StagingSettings = this.StagingSettings?.ToSdkObject();
activity.ParallelCopies = this.ParallelCopies;
activity.DataIntegrationUnits = this.DataIntegrationUnits;
activity.EnableSkipIncompatibleRow = this.EnableSkipIncompatibleRow;
activity.RedirectIncompatibleRowSettings = this.RedirectIncompatibleRowSettings?.ToSdkObject();
this.PreserveRules?.ForEach(item => activity.PreserveRules.Add(item));
this.Preserve?.ForEach(item => activity.Preserve.Add(item));
activity.LinkedServiceName = this.LinkedServiceName;
activity.Policy = this.Policy?.ToSdkObject();
SetProperties(activity);
return(activity);
}
private static void corregirAddressSinCompresion(DataFactoryManagementClient client)
{
string nombreTabla = "dbo-cc_address";
string nombreTablaSinEsquema = "cc_address";
List <Activity> la;
CopyActivity ca;
List <DatasetReference> inp;
DatasetReference dr;
List <DatasetReference> outp;
DatasetReference drO;
PipelineResource pipe;
string nuevaConsulta = "select top 1000 [LoadCommandID], [PublicID], [BatchGeocode], [CreateTime], [AddressLine1], [AddressLine2], [County], [AddressLine3], cast([SpatialPoint] as nvarchar(MAX)), [CityKanji], [AddressLine2Kanji], [Admin], [State], [AddressBookUID], [UpdateTime], [Country], [ID], [Ext_StreetType], [ExternalLinkID], [CreateUserID], [ValidUntil], [ArchivePartition], [BeanVersion], [CityDenorm], [Retired], [Ext_StreetNumber], [City], [AddressType], [AddressLine1Kanji], [UpdateUserID], [CEDEXBureau], [GeocodeStatus], [CEDEX], [PostalCodeDenorm], [PostalCode], [Subtype], [Description] from cc_address";
la = new List <Activity>();
ca = new CopyActivity();
ca.Name = "CopyPipeline-Sql-Lake-" + nombreTabla;
ca.Source = new SqlSource(null, 3, null, nuevaConsulta);
ca.Sink = new SqlSink();
inp = new List <DatasetReference>();
dr = new DatasetReference("Dataset_Claim_" + nombreTabla);
inp.Add(dr);
ca.Inputs = inp;
outp = new List <DatasetReference>();
drO = new DatasetReference("Dataset_Claim_DataLakeStore_" + nombreTablaSinEsquema);
outp.Add(drO);
ca.Outputs = outp;
la.Add(ca);
pipe = new PipelineResource();
pipe.Activities = la;
client.Pipelines.CreateOrUpdate(DatosGrales.resourceGroup, DatosGrales.dataFactoryName, "Pipeline-Copy-Claim-" + nombreTabla, pipe);
Console.Write("Pipeline-Copy-Claim-" + nombreTabla + " modificado.\n");
}
private static void corregirUserSinCompresion(DataFactoryManagementClient client)
{
string nombreTabla = "dbo-cc_user";
string nombreTablaSinEsquema = "cc_user";
List <Activity> la;
CopyActivity ca;
List <DatasetReference> inp;
DatasetReference dr;
List <DatasetReference> outp;
DatasetReference drO;
PipelineResource pipe;
string nuevaConsulta = "select top 1000 [LoadCommandID], [OffsetStatsUpdateTime], [PublicID], [CreateTime], [UserSettingsID], cast([SpatialPointDenorm] as nvarchar(MAX)), [SessionTimeoutSecs], [OrganizationID], [VacationStatus], [Department], [UpdateTime], [ExternalUser], [Language], [ExperienceLevel], [Locale], [ID], [LossType], [AuthorityProfileID], [CreateUserID], [BeanVersion], [NewlyAssignedActivities], [Retired], [DefaultPhoneCountry], [ValidationLevel], [PolicyType], [UpdateUserID], [QuickClaim], [CredentialID], [SystemUserType], [DefaultCountry], [TimeZone], [ContactID], [JobTitle] from cc_user";
la = new List <Activity>();
ca = new CopyActivity();
ca.Name = "CopyPipeline-Sql-Lake-" + nombreTabla;
ca.Source = new SqlSource(null, 3, null, nuevaConsulta);
ca.Sink = new SqlSink();
inp = new List <DatasetReference>();
dr = new DatasetReference("Dataset_Claim_" + nombreTabla);
inp.Add(dr);
ca.Inputs = inp;
outp = new List <DatasetReference>();
drO = new DatasetReference("Dataset_Claim_DataLakeStore_" + nombreTablaSinEsquema);
outp.Add(drO);
ca.Outputs = outp;
la.Add(ca);
pipe = new PipelineResource();
pipe.Activities = la;
client.Pipelines.CreateOrUpdate(DatosGrales.resourceGroup, DatosGrales.dataFactoryName, "Pipeline-Copy-Claim-" + nombreTabla, pipe);
Console.Write("Pipeline-Copy-Claim-" + nombreTabla + " modificado.\n");
}
private PipelineResource GetPipelineResource(string description)
{
PipelineResource resource = new PipelineResource
{
Description = description,
Parameters = new Dictionary <string, ParameterSpecification>
{
{ "OutputBlobNameList", new ParameterSpecification {
Type = ParameterType.Array
} }
},
Variables = new Dictionary <string, VariableSpecification>
{
{ "TestVariableArray", new VariableSpecification {
Type = VariableType.Array
} }
},
Activities = new List <Activity>()
};
CopyActivity copyActivity = new CopyActivity
{
Name = "ExampleCopyActivity",
DataIntegrationUnits = 32,
Inputs = new List <DatasetReference>
{
new DatasetReference
{
ReferenceName = datasetName,
Parameters = new Dictionary <string, object>()
{
{ "MyFolderPath", secrets.BlobContainerName },
{ "MyFileName", "entitylogs.csv" }
}
}
},
Outputs = new List <DatasetReference>
{
new DatasetReference
{
ReferenceName = datasetName,
Parameters = new Dictionary <string, object>()
{
{ "MyFolderPath", secrets.BlobContainerName },
{ "MyFileName", new Expression("@item()") }
}
}
},
Source = new BlobSource
{
},
Sink = new BlobSink
{
}
};
ForEachActivity forEachActivity = new ForEachActivity
{
Name = "ExampleForeachActivity",
IsSequential = true,
Items = new Expression("@pipeline().parameters.OutputBlobNameList"),
Activities = new List <Activity>()
{
copyActivity
}
};
resource.Activities.Add(forEachActivity);
return(resource);
}
static void Main(string[] args)
{
//Authentification auprès d'Azure avec l'application svc_adf
AuthenticationContext context = new AuthenticationContext("https://login.windows.net/" + _repertoireId);
ClientCredential cc = new ClientCredential(_applicationId, _applicationKey);
AuthenticationResult result = context.AcquireTokenAsync("https://management.azure.com/", cc).Result;
ServiceClientCredentials cred = new TokenCredentials(result.AccessToken);
DataFactoryManagementClient ADFclient = new DataFactoryManagementClient(cred)
{
SubscriptionId = _abonnementId
};
//Création d'une Azure Data Factory
Factory dataFactory = new Factory
{
Location = _region,
Identity = new FactoryIdentity()
};
ADFclient.Factories.CreateOrUpdate(_ressourceGroup, _dataFactoryName, dataFactory);
Console.WriteLine(SafeJsonConvert.SerializeObject(dataFactory, ADFclient.SerializationSettings));
var toto = ADFclient.Factories.Get(_ressourceGroup, _dataFactoryName).ProvisioningState;
while (ADFclient.Factories.Get(_ressourceGroup, _dataFactoryName).ProvisioningState == "PendingCreation")
{
System.Threading.Thread.Sleep(1000);
Console.WriteLine("*");
}
////Création d'un Integration Runtime Auto-Hébergé
//IntegrationRuntimeResource integrationRuntimeResource = new IntegrationRuntimeResource(
//new SelfHostedIntegrationRuntime
//{
// Description = "L'Integration Runtime du projet ..."
//}
//);
//ADFclient.IntegrationRuntimes.CreateOrUpdate(_ressourceGroup, _dataFactoryName, _IntegrationRuntimeName, integrationRuntimeResource);
//Console.WriteLine(SafeJsonConvert.SerializeObject(integrationRuntimeResource, ADFclient.SerializationSettings));
//Console.WriteLine("Authkey : " + ADFclient.IntegrationRuntimes.ListAuthKeys(_ressourceGroup, _dataFactoryName, _IntegrationRuntimeName).AuthKey1);
//Création service lié File System on premise
IntegrationRuntimeReference integrationRuntimeReference = new IntegrationRuntimeReference(_IntegrationRuntimeName);
SecureString secureString = new SecureString("MonPassword");
LinkedServiceResource FS_PartageOnPremise = new LinkedServiceResource(
new FileServerLinkedService
{
Description = "Service lié référençant un espace partagé dans le réseau privé de l'entreprise",
ConnectVia = integrationRuntimeReference,
Host = @"\\IRAutoHeberge\Dépôt",
UserId = "chsauget",
Password = secureString
}
);
ADFclient.LinkedServices.CreateOrUpdate(_ressourceGroup, _dataFactoryName, _FS_PartageOnPremiseName, FS_PartageOnPremise);
Console.WriteLine(SafeJsonConvert.SerializeObject(FS_PartageOnPremise, ADFclient.SerializationSettings));
//Création service lié Azure SQLDB
SecureString SQLsecureString = new SecureString("integrated security=False;encrypt=True;connection timeout=30;data source=adflivre.database.windows.net;initial catalog=advwrks;user id=chsauget;Password=toto");
LinkedServiceResource SQDB_AdventureWorks = new LinkedServiceResource(
new AzureSqlDatabaseLinkedService
{
Description = "Service lié référençant un espace partagé dans le réseau privé de l'entreprise",
ConnectionString = SQLsecureString,
}
);
ADFclient.LinkedServices.CreateOrUpdate(_ressourceGroup, _dataFactoryName, _SQDB_AdventureWorksName, SQDB_AdventureWorks);
Console.WriteLine(SafeJsonConvert.SerializeObject(SQDB_AdventureWorks, ADFclient.SerializationSettings));
//Création jeu de données FS_Customer
DatasetResource FS_Customer = new DatasetResource(
new FileShareDataset
{
LinkedServiceName = new LinkedServiceReference
{
ReferenceName = _FS_PartageOnPremiseName
}
, FolderPath = "AdventureWorks CSV"
, FileName = "Customer.csv"
, Format = new TextFormat
{
ColumnDelimiter = "\t",
RowDelimiter = "\n",
FirstRowAsHeader = false
}
, Structure = new List <DatasetDataElement>
{
new DatasetDataElement
{
Name = "CustomerID",
Type = "Int32"
},
new DatasetDataElement
{
Name = "PersonID",
Type = "Int32"
},
new DatasetDataElement
{
Name = "StoreID",
Type = "Int32"
},
new DatasetDataElement
{
Name = "TerritoryID",
Type = "Int32"
},
new DatasetDataElement
{
Name = "AccountNumber",
Type = "String"
},
new DatasetDataElement
{
Name = "rowguid",
Type = "String"
},
new DatasetDataElement
{
Name = "ModifiedDate",
Type = "DateTime"
}
}
}
, name: _FS_CustomerName
);
ADFclient.Datasets.CreateOrUpdate(_ressourceGroup, _dataFactoryName, _FS_CustomerName, FS_Customer);
Console.WriteLine(SafeJsonConvert.SerializeObject(FS_Customer, ADFclient.SerializationSettings));
//Création jeu de données SQDB_Col_Customer
DatasetResource SQDB_Col_Customer = new DatasetResource(
new AzureSqlTableDataset
{
LinkedServiceName = new LinkedServiceReference
{
ReferenceName = _SQDB_AdventureWorksName
},
TableName = "col.Customer"
}
, name: _SQDB_Col_CustomerName
);
ADFclient.Datasets.CreateOrUpdate(_ressourceGroup, _dataFactoryName, _SQDB_AdventureWorksName, SQDB_Col_Customer);
Console.WriteLine(SafeJsonConvert.SerializeObject(SQDB_Col_Customer, ADFclient.SerializationSettings));
//Création de l'activité de copie du fichier Customer
CopyActivity CustomerCopy = new CopyActivity
{
Name = "Copy - Customer"
,
Inputs = new List <DatasetReference> {
new DatasetReference()
{
ReferenceName = _FS_CustomerName
}
}
,
Outputs = new List <DatasetReference> {
new DatasetReference()
{
ReferenceName = _SQDB_Col_CustomerName
}
}
,
Source = new FileSystemSource
{
}
,
Sink = new AzureTableSink
{
}
};
//Création de l'activité de copie du fichier Customer
PipelineResource PipelineCustomer = new PipelineResource
{
Activities = new List <Activity> {
CustomerCopy
}
, Folder = new PipelineFolder {
Name = "AdventureWorks"
}
};
ADFclient.Pipelines.CreateOrUpdate(_ressourceGroup, _dataFactoryName, "Col_Customer", PipelineCustomer);
Console.WriteLine(SafeJsonConvert.SerializeObject(PipelineCustomer, ADFclient.SerializationSettings));
//Demander une execution du pipeline
CreateRunResponse runResponse = ADFclient.Pipelines.CreateRunWithHttpMessagesAsync(_ressourceGroup, _dataFactoryName, "Col_Customer").Result.Body;
//Contrôler l'execution du pipeline
PipelineRun run = ADFclient.PipelineRuns.Get(_ressourceGroup, _dataFactoryName, runResponse.RunId);
while (run.Status == "InProgress")
{
run = ADFclient.PipelineRuns.Get(_ressourceGroup, _dataFactoryName, runResponse.RunId);
Console.WriteLine("Status: " + run.Status);
}
//Déclencheur Quotidien
TriggerResource scheduleTrigger = new TriggerResource(
new ScheduleTrigger
{
Pipelines = new List <TriggerPipelineReference> {
new TriggerPipelineReference {
PipelineReference = new PipelineReference("Col_Customer")
}
}
,
Recurrence = new ScheduleTriggerRecurrence
{
StartTime = DateTime.Parse("2019-03-30T01:00:00Z")
,
Frequency = "Day"
,
Interval = 1
}
}
, name: "Daily_01h_Schedule"
);
ADFclient.Triggers.CreateOrUpdate(_ressourceGroup, _dataFactoryName, "Daily_01h_Schedule", scheduleTrigger);
Console.WriteLine(SafeJsonConvert.SerializeObject(scheduleTrigger, ADFclient.SerializationSettings));
ADFclient.Triggers.BeginStart(_ressourceGroup, _dataFactoryName, "Daily_01h_Schedule");
}
private static void corregirClaimSinCompresion(DataFactoryManagementClient client)
{
List <Activity> la;
CopyActivity ca;
List <DatasetReference> inp;
DatasetReference dr;
List <DatasetReference> outp;
DatasetReference drO;
PipelineResource pipe;
la = new List <Activity>();
ca = new CopyActivity();
ca.Name = "CopyPipeline-Lake-DW-" + "cc_history";
ca.Source = new AzureDataLakeStoreSource(recursive: false);
var ware = new SqlDWSink();
ware.AllowPolyBase = true;
ware.WriteBatchSize = 1000;
var poly = new PolybaseSettings();
poly.RejectValue = 0;
poly.RejectType = "percentage";
poly.RejectSampleValue = 0;
poly.UseTypeDefault = true;
ware.PolyBaseSettings = poly;
ca.Sink = ware;
ca.EnableStaging = true;
ca.CloudDataMovementUnits = 0;
ca.EnableSkipIncompatibleRow = true;
var stg = new StagingSettings();
stg.Path = "adfstagingcopydata";
LinkedServiceReference lsIntermedio = new LinkedServiceReference("temp_StagingStorage-c0p");
stg.LinkedServiceName = lsIntermedio;
ca.StagingSettings = stg;
var trans = new TabularTranslator();
//trans.ColumnMappings = DatosGrales.traerCamposPolybase("cc_history");
ca.Translator = trans;
inp = new List <DatasetReference>();
dr = new DatasetReference("Dataset_Descompresion_Claim_DataLakeStore_cc_history");
inp.Add(dr);
ca.Inputs = inp;
outp = new List <DatasetReference>();
drO = new DatasetReference("Dataset_Warehouse_landing-pruebaDFv2_cc_history");
outp.Add(drO);
ca.Outputs = outp;
la.Add(ca);
pipe = new PipelineResource();
pipe.Activities = la;
client.Pipelines.CreateOrUpdate(DatosGrales.resourceGroup, DatosGrales.dataFactoryName, "Pipeline-Copy-Lake-ADW-cc_history", pipe);
Console.Write("Pipeline-Copy-Lake-ADW-cc_history creado.\n");
}
private static void crearPipesDinamismoETL(DataFactoryManagementClient client)
{
// Generador de Scripts para claim center
string[] tablas = new[] { "cc_activity", "cc_check", "cc_checkpayee", "cc_claim", "cc_contact", "cc_coverage", "cc_exchangerate", "cc_exposure", "cc_exposurerpt", "cc_group", "cc_history", "cc_litstatustypeline", "cc_matter", "cc_policy", "cc_riskunit", "cc_transaction", "cc_transactionlineitem", "cc_transactionset", "cc_vehicle", "cctl_approvalstatus", "cctl_costtype", "cctl_coveragesubtype", "cctl_exposurestate", "cctl_exposuretype", "cctl_ext_casequestioned", "cctl_ext_courtvenue", "cctl_ext_damagetype", "cctl_ext_exposurestage", "cctl_historytype", "cctl_losscause", "cctl_matterstatus", "cctl_mattertype", "cctl_transactionstatus" };
PipelineResource pipe = new PipelineResource(name: "PipeForEachClaim");
List <Activity> la = new List <Activity>();
//Foreach activity
ForEachActivity fea = new ForEachActivity();
fea.IsSequential = false;
fea.Name = "ForEachActiv";
Expression ex = new Expression("@pipeline().parameters.TablasACopiar");
fea.Items = ex;
//Copy activity
List <Activity> la1 = new List <Activity>();
CopyActivity ca = new CopyActivity();
ca.EnableStaging = false;
ca.CloudDataMovementUnits = 5;
ca.Name = "CopyTabla";
List <DatasetReference> ldr = new List <DatasetReference>();
ldr.Add(new DatasetReference("Dataset_Dinamismo_Claim"));
ca.Inputs = ldr;
List <DatasetReference> ldo = new List <DatasetReference>();
ldo.Add(new DatasetReference("Dataset_WHDinamismo_Claim"));
ca.Outputs = ldo;
//string consulta = "declare @Tabla varchar(1000);select @Tabla = '@{item()}';declare @vsSQL varchar(8000);declare @vsTableName varchar(50);select @vsTableName = @Tabla;select @vsSQL = '; IF EXISTS (SELECT * FROM '+ @Tabla + ') '+' DROP TABLE '+ @Tabla + ';'+ ' CREATE TABLE ' + @vsTableName + char(10) + '(' + char(10);select @vsSQL = @vsSQL + ' ' + sc.Name + ' ' +st.Name +case when st.Name in ('varchar','varchar','char','nchar') then '(' + cast(sc.Length as varchar) + ') ' else ' ' end +case when sc.IsNullable = 1 then 'NULL' else 'NOT NULL' end + ',' + char(10) from sysobjects so join syscolumns sc on sc.id = so.id join systypes st on st.xusertype = sc.xusertype where so.name = @vsTableName order by sc.ColID; select substring(@vsSQL,1,len(@vsSQL) - 2) + char(10) + ') ' as QueryCreacion";
//string consulta = "declare @Tabla as varchar(1000);select @Tabla = '@{item()}';declare @vsSQL varchar(8000);declare @vsTableName varchar(50);select @vsTableName = @Tabla;select @vsSQL = '; IF EXISTS (SELECT * FROM '+ @Tabla + ') '+' DROP TABLE '+ @Tabla + ';'+ ' CREATE TABLE ' + @vsTableName + char(10) + '(' + char(10);select @vsSQL = @vsSQL + ' ' + column_name + ' ' +data_type +case when data_type in ('varchar','varchar','char','nchar') then '(' + cast(character_maximum_length as varchar) + ') ' else ' ' end +case when is_nullable = 'YES' then 'NULL' else 'NOT NULL' end + ',' + char(10) from information_schema.columns where table_name = @vsTableName order by ordinal_position; if len(@vsSQL) < 4000 select substring(@vsSQL,1,len(@vsSQL) - 2) + char(10) + ') ' as QueryCreacion1, null as QueryCreacion2;else select substring(@vsSQL,1,3999) as QueryCreacion1, SUBSTRING(@vsSQL, 4000, len(@vsSQL) - 2) as QueryCreacion2;";
string consulta = "declare @Tabla as varchar(1000);select @Tabla = '@{item()}';declare @Schema as varchar(1000);select @Schema = 'landing';declare @vsSQL nvarchar(MAX);declare @vsTableName varchar(50);select @vsTableName = @Tabla;select @vsSQL = '; IF EXISTS (SELECT * FROM information_schema.tables where table_name = '''+ @Tabla + ''' and table_schema = '''+ @Schema +''') '+' DROP TABLE '+ @Tabla + ';'+ ' CREATE TABLE ' + @vsTableName + char(10) + '(' + char(10);select @vsSQL = @vsSQL + ' ' + column_name + ' ' +data_type +case when data_type in ('varchar','varchar','char','nchar') then '(' + cast(character_maximum_length as varchar) + ') ' else ' ' end +case when is_nullable = 'YES' then 'NULL' else 'NOT NULL' end + ',' + char(10) from information_schema.columns where table_name = @vsTableName order by ordinal_position; set @vsSQL = LEFT(@vsSQL, len(@vsSQL) - 2);if len(@vsSQL) < 3999 select replace(replace(replace(substring(@vsSQL,1,len(@vsSQL)) + char(10) + ') ','-1', '8000'), 'geography', 'varchar(8000)'), 'nvarchar', 'varchar') as QueryCreacion1, null as QueryCreacion2;else select replace(replace(replace(LEFT(@vsSQL,3800),'-1', '8000'), 'geography', 'varchar(8000)'), 'nvarchar', 'varchar') as QueryCreacion1, replace(replace(replace(RIGHT(@vsSQL, Len(@vsSQL) - 3800) + ')','-1', '8000'), 'geography', 'varchar(8000)'), 'nvarchar', 'varchar') as QueryCreacion2;";
ca.Source = new SqlSource(null, 3, null, consulta, null, null);
ca.Sink = new SqlSink();
la1.Add(ca);
fea.Activities = la1;
la.Add(fea);
pipe.Activities = la;
IDictionary <string, ParameterSpecification> tablasACopiar = new Dictionary <string, ParameterSpecification>();
tablasACopiar.Add("tablasACopiar", new ParameterSpecification("Array", tablas));
pipe.Parameters = tablasACopiar;
client.Pipelines.CreateOrUpdate(DatosGrales.resourceGroup, DatosGrales.dataFactoryName, "PipeForEachClaim", pipe);
Console.WriteLine("Pipe creado, desea correrlo ahora? (s/n)");
if (Console.ReadLine() == "s")
{
client.Pipelines.CreateRun(DatosGrales.resourceGroup, DatosGrales.dataFactoryName, "PipeForEachClaim");
Console.WriteLine("Run creado! Seguimos creando los de Datastaging.");
}
crearPipesDinamismoDataStaging(client);
}
private static void corregirAddressConCompresion(DataFactoryManagementClient client)
{
string nombreTabla = "dbo-cc_address";
string nombreTablaParaCompresion = "cc_address";
List <Activity> la;
CopyActivity ca;
List <DatasetReference> inp;
DatasetReference dr;
List <DatasetReference> outp;
DatasetReference drO;
CopyActivity ca2;
List <ActivityDependency> dep;
List <DatasetReference> inp1;
DatasetReference dr1;
List <DatasetReference> outp1;
DatasetReference drO1;
PipelineResource pipe1;
string consultaNueva = "select top 10000 [LoadCommandID], [PublicID], [BatchGeocode], [CreateTime], [AddressLine1], [AddressLine2], [County], [AddressLine3], cast([SpatialPoint] as nvarchar(MAX)), [CityKanji], [AddressLine2Kanji], [Admin], [State], [AddressBookUID], [UpdateTime], [Country], [ID], [Ext_StreetType], [ExternalLinkID], [CreateUserID], [ValidUntil], [ArchivePartition], [BeanVersion], [CityDenorm], [Retired], [Ext_StreetNumber], [City], [AddressType], [AddressLine1Kanji], [UpdateUserID], [CEDEXBureau], [GeocodeStatus], [CEDEX], [PostalCodeDenorm], [PostalCode], [Subtype], [Description] from cc_address";
la = new List <Activity>();
ca = new CopyActivity();
ca.Name = "CA-Compresion-" + nombreTabla;
ca.Source = new SqlSource(null, 3, null, consultaNueva);
ca.Sink = new AzureDataLakeStoreSink();
inp = new List <DatasetReference>();
dr = new DatasetReference("Dataset_Claim_" + nombreTabla);
inp.Add(dr);
ca.Inputs = inp;
outp = new List <DatasetReference>();
drO = new DatasetReference("Dataset_Descompresion_Claim_DataLakeStore_" + nombreTablaParaCompresion);
outp.Add(drO);
ca.Outputs = outp;
la.Add(ca);
ca2 = new CopyActivity();
ca2.Name = "CA-Descompresion-" + nombreTabla;
ca2.Source = new SqlSource();
ca2.Sink = new AzureDataLakeStoreSink();
string[] condiciones = { "Succeeded" };
dep = new List <ActivityDependency>();
dep.Add(new ActivityDependency("CA-Compresion-" + nombreTabla, condiciones));
ca2.DependsOn = dep;
inp1 = new List <DatasetReference>();
dr1 = new DatasetReference("Dataset_Descompresion_Claim_DataLakeStore_" + nombreTablaParaCompresion);
inp1.Add(dr1);
ca2.Inputs = inp1;
outp1 = new List <DatasetReference>();
drO1 = new DatasetReference("Dataset_Claim_DataLakeStore_" + nombreTablaParaCompresion);
outp1.Add(drO1);
ca2.Outputs = outp1;
la.Add(ca2);
pipe1 = new PipelineResource();
pipe1.Activities = la;
client.Pipelines.CreateOrUpdate(DatosGrales.resourceGroup, DatosGrales.dataFactoryName, "Pipeline-Sql-DataLake-ConCompresion-Claim-" + nombreTabla, pipe1);
Console.Write("Pipeline-Sql-DataLake-ConCompresion-Claim-" + nombreTabla + " modificado.\n");
}
private static void crearPipesSubidaConCompresion(DataFactoryManagementClient client)
{
string nombreBD = DatosGrales.nombreBD;
string[] nombreTablas = DatosGrales.traerTablas(true);
string[] nombreTablasParaCompresion = DatosGrales.traerTablas(false);
List <Activity> la;
CopyActivity ca;
List <DatasetReference> inp;
DatasetReference dr;
List <DatasetReference> outp;
DatasetReference drO;
CopyActivity ca2;
List <ActivityDependency> dep;
string nombreTablaParaConsulta;
List <DatasetReference> inp1;
DatasetReference dr1;
List <DatasetReference> outp1;
DatasetReference drO1;
PipelineResource pipe1;
string consulta;
if (nombreBD == "ClaimCenter")
{
nombreBD = "Claim";
}
for (int i = 790; i < nombreTablas.Length; i++)
{
if (esTablaEspecial(nombreTablas[i]))
{
//no creo nada porque tiene un trato especial
}
else
{
nombreTablaParaConsulta = nombreTablas[i].Replace('-', '.');
consulta = DatosGrales.queryMagica(nombreTablaParaConsulta, 0);
la = new List <Activity>();
ca = new CopyActivity();
ca.Name = "CA-Compresion-" + nombreTablas[i];
ca.Source = new SqlSource(null, 3, null, consulta);
ca.Sink = new AzureDataLakeStoreSink();
inp = new List <DatasetReference>();
//dr = new DatasetReference("Dataset_" + nombreBD + "_" + nombreTablas[i]);
dr = new DatasetReference("Dataset_ClaimCenter_" + nombreTablas[i]);
inp.Add(dr);
ca.Inputs = inp;
outp = new List <DatasetReference>();
drO = new DatasetReference("Dataset_Descompresion_" + nombreBD + "_DataLakeStore_" + nombreTablasParaCompresion[i]);
outp.Add(drO);
ca.Outputs = outp;
la.Add(ca);
ca2 = new CopyActivity();
ca2.Name = "CA-Descompresion-" + nombreTablas[i];
ca2.Source = new SqlSource();
ca2.Sink = new AzureDataLakeStoreSink();
string[] condiciones = { "Succeeded" };
dep = new List <ActivityDependency>();
dep.Add(new ActivityDependency("CA-Compresion-" + nombreTablas[i], condiciones));
ca2.DependsOn = dep;
inp1 = new List <DatasetReference>();
dr1 = new DatasetReference("Dataset_Descompresion_" + nombreBD + "_DataLakeStore_" + nombreTablasParaCompresion[i]);
inp1.Add(dr1);
ca2.Inputs = inp1;
outp1 = new List <DatasetReference>();
drO1 = new DatasetReference("Dataset_" + nombreBD + "_DataLakeStore_" + nombreTablasParaCompresion[i]);
outp1.Add(drO1);
ca2.Outputs = outp1;
la.Add(ca2);
pipe1 = new PipelineResource();
pipe1.Activities = la;
client.Pipelines.CreateOrUpdate(DatosGrales.resourceGroup, DatosGrales.dataFactoryName, "Pipeline-Sql-DataLake-ConCompresion-" + nombreBD + "-" + nombreTablas[i], pipe1);
Console.Write((i + 1) + ". Pipeline-Sql-DataLake-ConCompresion-" + nombreBD + "-" + nombreTablas[i] + " creado.\n");
}
}
}
private static void crearPipesLakeaWarehouse(DataFactoryManagementClient client)
{
List <Activity> la;
CopyActivity ca;
List <DatasetReference> inp;
DatasetReference dr;
List <DatasetReference> outp;
DatasetReference drO;
PipelineResource pipe;
string[] tablasWarehouse = DatosGrales.tablasLakeaWarehouse;
string nombreTablaSinSchema;
string nombreSinPunto;
for (int i = 0; i < tablasWarehouse.Length; i++)
{
nombreTablaSinSchema = tablasWarehouse[i].Split('.')[1];
nombreSinPunto = tablasWarehouse[i].Replace('.', '-');
la = new List <Activity>();
ca = new CopyActivity();
ca.Name = "CopyPipeline-Lake-DW-" + nombreTablaSinSchema;
var sor = new AzureDataLakeStoreSource(recursive: false);
ca.Source = sor;
var ware = new SqlDWSink();
ware.AllowPolyBase = true;
ware.WriteBatchSize = 1000;
var poly = new PolybaseSettings();
poly.RejectValue = 1000;
poly.RejectType = "value";
//poly.RejectSampleValue = 0;
poly.UseTypeDefault = false;
ware.PolyBaseSettings = poly;
ware.PreCopyScript = "truncate table landing." + nombreTablaSinSchema;
ca.Sink = ware;
ca.EnableStaging = true;
ca.CloudDataMovementUnits = 0;
ca.EnableSkipIncompatibleRow = true;
var stg = new StagingSettings();
stg.Path = "adfstagingcopydata";
LinkedServiceReference lsIntermedio = new LinkedServiceReference("StagingStorageLakeToWarehouse");
stg.LinkedServiceName = lsIntermedio;
ca.StagingSettings = stg;
var trans = new TabularTranslator();
//trans.ColumnMappings = DatosGrales.traerCamposPolybase("cc_history");
ca.Translator = trans;
inp = new List <DatasetReference>();
dr = new DatasetReference("Dataset_Datastaging_DataLakeStore_" + nombreTablaSinSchema);
inp.Add(dr);
ca.Inputs = inp;
outp = new List <DatasetReference>();
drO = new DatasetReference("Dataset_Warehouse_" + nombreSinPunto);
outp.Add(drO);
ca.Outputs = outp;
la.Add(ca);
pipe = new PipelineResource();
pipe.Activities = la;
if (tablasWarehouse[i] == "landing.ccst_RAJ")
{
client.Pipelines.CreateOrUpdate(DatosGrales.resourceGroup, DatosGrales.dataFactoryName, "Pipeline-Copy-Lake-ADW-" + nombreTablaSinSchema, pipe);
Console.Write((i + 1) + ". Pipeline-Copy-Lake-ADW-" + nombreTablaSinSchema + " creado.\n");
}
}
}