public static CreateDatabaseResponse Unmarshall(UnmarshallerContext context)
{
CreateDatabaseResponse createDatabaseResponse = new CreateDatabaseResponse();
createDatabaseResponse.HttpResponse = context.HttpResponse;
createDatabaseResponse.RequestId = context.StringValue("CreateDatabase.RequestId");
return createDatabaseResponse;
}
public static CreateDatabaseResponse Unmarshall(UnmarshallerContext context)
{
CreateDatabaseResponse createDatabaseResponse = new CreateDatabaseResponse();
createDatabaseResponse.HttpResponse = context.HttpResponse;
createDatabaseResponse.RequestId = context.StringValue("CreateDatabase.RequestId");
return(createDatabaseResponse);
}
/// <summary>
/// Unmarshaller the response from the service to the response class.
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
public override AmazonWebServiceResponse Unmarshall(JsonUnmarshallerContext context)
{
CreateDatabaseResponse response = new CreateDatabaseResponse();
context.Read();
int targetDepth = context.CurrentDepth;
while (context.ReadAtDepth(targetDepth))
{
if (context.TestExpression("Database", targetDepth))
{
var unmarshaller = DatabaseUnmarshaller.Instance;
response.Database = unmarshaller.Unmarshall(context);
continue;
}
}
return(response);
}
public async Task CreateDatabase()
{
Console.WriteLine("Creating Database");
try
{
var createDatabaseRequest = new CreateDatabaseRequest
{
DatabaseName = Constants.DATABASE_NAME
};
CreateDatabaseResponse response = await writeClient.CreateDatabaseAsync(createDatabaseRequest);
Console.WriteLine($"Database {Constants.DATABASE_NAME} created");
}
catch (ConflictException)
{
Console.WriteLine("Database already exists.");
}
catch (Exception e)
{
Console.WriteLine("Create database failed:" + e.ToString());
}
}
public async Task DoWorkAsync()
{
_context.Database.OpenConnection();
_context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.S3Buckets ON");
ListBucketsResponse listBucketResponse = await _S3Client.ListBucketsAsync(new ListBucketsRequest());
bool sageMakerBucketFound = false, apacheWebLogBucketFound = false, SSHLogBucketFound = false, windowsSecurityLogBucketFound = false, squidProxyLogBucketFound = false;
foreach (var bucket in listBucketResponse.Buckets)
{
if (bucket.BucketName.Equals("master-sagemaker-data"))
{
sageMakerBucketFound = true;
if (_context.S3Buckets.Find(1) == null)
{
_context.S3Buckets.Add(new Models.S3Bucket
{
ID = 1,
Name = "master-sagemaker-data"
});
}
}
else if (bucket.BucketName.Equals("smartinsights-test-website"))
{
apacheWebLogBucketFound = true;
if (_context.S3Buckets.Find(2) == null)
{
_context.S3Buckets.Add(new Models.S3Bucket
{
ID = 2,
Name = "smartinsights-test-website"
});
}
}
else if (bucket.BucketName.Equals("smartinsights-ssh-logs"))
{
SSHLogBucketFound = true;
if (_context.S3Buckets.Find(3) == null)
{
_context.S3Buckets.Add(new Models.S3Bucket
{
ID = 3,
Name = "smartinsights-ssh-logs"
});
}
}
else if (bucket.BucketName.Equals("smartinsights-windows-security-logs"))
{
windowsSecurityLogBucketFound = true;
if (_context.S3Buckets.Find(4) == null)
{
_context.S3Buckets.Add(new Models.S3Bucket
{
ID = 4,
Name = "smartinsights-windows-security-logs"
});
}
}
else if (bucket.BucketName.Equals("smartinsights-squid-proxy-logs"))
{
squidProxyLogBucketFound = true;
if (_context.S3Buckets.Find(5) == null)
{
_context.S3Buckets.Add(new Models.S3Bucket
{
ID = 5,
Name = "smartinsights-squid-proxy-logs"
});
}
}
if (sageMakerBucketFound && apacheWebLogBucketFound && SSHLogBucketFound && windowsSecurityLogBucketFound && squidProxyLogBucketFound)
{
break;
}
}
if (!sageMakerBucketFound && _context.S3Buckets.Find(2) == null)
{
PutBucketResponse putBucketResponse2 = await _S3Client.PutBucketAsync(new PutBucketRequest
{
BucketName = "master-sagemaker-data",
UseClientRegion = true,
CannedACL = S3CannedACL.Private
});
PutBucketTaggingResponse putBucketTaggingResponse2 = await _S3Client.PutBucketTaggingAsync(new PutBucketTaggingRequest
{
BucketName = "master-sagemaker-data",
TagSet = new List <Tag>
{
new Tag
{
Key = "Project",
Value = "OSPJ"
}
}
});
PutPublicAccessBlockResponse putPublicAccessBlockResponse2 = await _S3Client.PutPublicAccessBlockAsync(new PutPublicAccessBlockRequest
{
BucketName = "master-sagemaker-data",
PublicAccessBlockConfiguration = new PublicAccessBlockConfiguration
{
BlockPublicAcls = true,
BlockPublicPolicy = true,
IgnorePublicAcls = true,
RestrictPublicBuckets = true
}
});
if (putBucketResponse2.HttpStatusCode.Equals(HttpStatusCode.OK) && putPublicAccessBlockResponse2.HttpStatusCode.Equals(HttpStatusCode.OK))
{
_context.S3Buckets.Add(new Models.S3Bucket
{
ID = 1,
Name = "master-sagemaker-data"
});
}
}
if (!apacheWebLogBucketFound && _context.S3Buckets.Find(3) == null)
{
PutBucketResponse putBucketResponse3 = await _S3Client.PutBucketAsync(new PutBucketRequest
{
BucketName = "smartinsights-test-website",
UseClientRegion = true,
CannedACL = S3CannedACL.Private
});
PutBucketTaggingResponse putBucketTaggingResponse3 = await _S3Client.PutBucketTaggingAsync(new PutBucketTaggingRequest
{
BucketName = "smartinsights-test-website",
TagSet = new List <Tag>
{
new Tag
{
Key = "Project",
Value = "OSPJ"
}
}
});
PutPublicAccessBlockResponse putPublicAccessBlockResponse3 = await _S3Client.PutPublicAccessBlockAsync(new PutPublicAccessBlockRequest
{
BucketName = "smartinsights-test-website",
PublicAccessBlockConfiguration = new PublicAccessBlockConfiguration
{
BlockPublicAcls = true,
BlockPublicPolicy = true,
IgnorePublicAcls = true,
RestrictPublicBuckets = true
}
});
if (putBucketResponse3.HttpStatusCode.Equals(HttpStatusCode.OK) && putPublicAccessBlockResponse3.HttpStatusCode.Equals(HttpStatusCode.OK))
{
_context.S3Buckets.Add(new Models.S3Bucket
{
ID = 2,
Name = "smartinsights-apache-web-logs"
});
}
}
if (!SSHLogBucketFound && _context.S3Buckets.Find(4) == null)
{
PutBucketResponse putBucketResponse4 = await _S3Client.PutBucketAsync(new PutBucketRequest
{
BucketName = "smartinsights-ssh-logs",
UseClientRegion = true,
CannedACL = S3CannedACL.Private
});
PutBucketTaggingResponse putBucketTaggingResponse4 = await _S3Client.PutBucketTaggingAsync(new PutBucketTaggingRequest
{
BucketName = "smartinsights-ssh-logs",
TagSet = new List <Tag>
{
new Tag
{
Key = "Project",
Value = "OSPJ"
}
}
});
PutPublicAccessBlockResponse putPublicAccessBlockResponse4 = await _S3Client.PutPublicAccessBlockAsync(new PutPublicAccessBlockRequest
{
BucketName = "smartinsights-ssh-logs",
PublicAccessBlockConfiguration = new PublicAccessBlockConfiguration
{
BlockPublicAcls = true,
BlockPublicPolicy = true,
IgnorePublicAcls = true,
RestrictPublicBuckets = true
}
});
if (putBucketResponse4.HttpStatusCode.Equals(HttpStatusCode.OK) && putPublicAccessBlockResponse4.HttpStatusCode.Equals(HttpStatusCode.OK))
{
_context.S3Buckets.Add(new Models.S3Bucket
{
ID = 3,
Name = "smartinsights-ssh-logs"
});
}
}
if (!windowsSecurityLogBucketFound && _context.S3Buckets.Find(5) == null)
{
PutBucketResponse putBucketResponse5 = await _S3Client.PutBucketAsync(new PutBucketRequest
{
BucketName = "smartinsights-windows-security-logs",
UseClientRegion = true,
CannedACL = S3CannedACL.Private
});
PutBucketTaggingResponse putBucketTaggingResponse5 = await _S3Client.PutBucketTaggingAsync(new PutBucketTaggingRequest
{
BucketName = "smartinsights-windows-security-logs",
TagSet = new List <Tag>
{
new Tag
{
Key = "Project",
Value = "OSPJ"
}
}
});
PutPublicAccessBlockResponse putPublicAccessBlockResponse5 = await _S3Client.PutPublicAccessBlockAsync(new PutPublicAccessBlockRequest
{
BucketName = "smartinsights-windows-security-logs",
PublicAccessBlockConfiguration = new PublicAccessBlockConfiguration
{
BlockPublicAcls = true,
BlockPublicPolicy = true,
IgnorePublicAcls = true,
RestrictPublicBuckets = true
}
});
if (putBucketResponse5.HttpStatusCode.Equals(HttpStatusCode.OK) && putPublicAccessBlockResponse5.HttpStatusCode.Equals(HttpStatusCode.OK))
{
_context.S3Buckets.Add(new Models.S3Bucket
{
ID = 4,
Name = "smartinsights-windows-security-logs"
});
}
}
if (!squidProxyLogBucketFound && _context.S3Buckets.Find(6) == null)
{
PutBucketResponse putBucketResponse6 = await _S3Client.PutBucketAsync(new PutBucketRequest
{
BucketName = "smartinsights-squid-proxy-logs",
UseClientRegion = true,
CannedACL = S3CannedACL.Private
});
PutBucketTaggingResponse putBucketTaggingResponse6 = await _S3Client.PutBucketTaggingAsync(new PutBucketTaggingRequest
{
BucketName = "smartinsights-squid-proxy-logs",
TagSet = new List <Tag>
{
new Tag
{
Key = "Project",
Value = "OSPJ"
}
}
});
PutPublicAccessBlockResponse putPublicAccessBlockResponse6 = await _S3Client.PutPublicAccessBlockAsync(new PutPublicAccessBlockRequest
{
BucketName = "smartinsights-squid-proxy-logs",
PublicAccessBlockConfiguration = new PublicAccessBlockConfiguration
{
BlockPublicAcls = true,
BlockPublicPolicy = true,
IgnorePublicAcls = true,
RestrictPublicBuckets = true
}
});
if (putBucketResponse6.HttpStatusCode.Equals(HttpStatusCode.OK) && putPublicAccessBlockResponse6.HttpStatusCode.Equals(HttpStatusCode.OK))
{
_context.S3Buckets.Add(new Models.S3Bucket
{
ID = 5,
Name = "smartinsights-squid-proxy-logs"
});
}
}
await _context.SaveChangesAsync();
_context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.S3Buckets OFF");
_context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.GlueDatabases ON");
try
{
GetDatabaseResponse getDatabaseResponse = await _GlueClient.GetDatabaseAsync(new GetDatabaseRequest
{
Name = "master-database"
});
if (_context.GlueDatabases.Find(1) == null)
{
_context.GlueDatabases.Add(new Models.GlueDatabase
{
ID = 1,
Name = "master-database"
});
}
}
catch (EntityNotFoundException)
{
CreateDatabaseResponse createDatabaseResponse = await _GlueClient.CreateDatabaseAsync(new CreateDatabaseRequest
{
DatabaseInput = new DatabaseInput
{
Name = "master-database"
}
});
if (createDatabaseResponse.HttpStatusCode.Equals(HttpStatusCode.OK))
{
_context.GlueDatabases.Add(new Models.GlueDatabase
{
ID = 1,
Name = "master-database"
});
}
}
finally
{
await _context.SaveChangesAsync();
_context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.GlueDatabases OFF");
}
GetConnectionsResponse getConnectionsResponse = await _GlueClient.GetConnectionsAsync(new GetConnectionsRequest
{
HidePassword = false
});
bool GlueDBConnectionNameMatch = false, GlueDBConnectionParamtetersMatch = false;
foreach (Connection c in getConnectionsResponse.ConnectionList)
{
if (c.Name.Equals(Environment.GetEnvironmentVariable("GLUE_DB-CONNECTION_NAME")))
{
GlueDBConnectionNameMatch = true;
}
c.ConnectionProperties.TryGetValue("JDBC_CONNECTION_URL", out string DBHost);
c.ConnectionProperties.TryGetValue("USERNAME", out string DBUserName);
c.ConnectionProperties.TryGetValue("PASSWORD", out string DBPassword);
if (DBHost.Contains(Environment.GetEnvironmentVariable("RDS_HOSTNAME")) && DBUserName.Equals(Environment.GetEnvironmentVariable("RDS_USERNAME")) && DBPassword.Equals(Environment.GetEnvironmentVariable("RDS_PASSWORD")))
{
GlueDBConnectionParamtetersMatch = true;
}
if (GlueDBConnectionNameMatch && GlueDBConnectionParamtetersMatch)
{
break;
}
}
if (!GlueDBConnectionParamtetersMatch)
{
if (GlueDBConnectionNameMatch)
{
DeleteConnectionResponse deleteConnectionResponse = await _GlueClient.DeleteConnectionAsync(new DeleteConnectionRequest
{
ConnectionName = Environment.GetEnvironmentVariable("GLUE_DB-CONNECTION_NAME")
});
if (deleteConnectionResponse.HttpStatusCode.Equals(HttpStatusCode.OK))
{
await _GlueClient.CreateConnectionAsync(new CreateConnectionRequest
{
ConnectionInput = new ConnectionInput
{
ConnectionProperties = new Dictionary <string, string>()
{
{ "JDBC_CONNECTION_URL", "jdbc:sqlserver://" + Environment.GetEnvironmentVariable("RDS_HOSTNAME") + ":" + Environment.GetEnvironmentVariable("RDS_PORT") + ";databaseName=" + Environment.GetEnvironmentVariable("GLUE_INGESTION-DB_NAME") },
{ "JDBC_ENFORCE_SSL", "false" },
{ "USERNAME", Environment.GetEnvironmentVariable("RDS_USERNAME") },
{ "PASSWORD", Environment.GetEnvironmentVariable("RDS_PASSWORD") },
},
ConnectionType = ConnectionType.JDBC,
Name = Environment.GetEnvironmentVariable("GLUE_DB-CONNECTION_NAME"),
PhysicalConnectionRequirements = new PhysicalConnectionRequirements
{
AvailabilityZone = "ap-southeast-1c",
SubnetId = "subnet-0daa6ec8e25a13077",
SecurityGroupIdList = new List <string>
{
"sg-0e1e79f6d49b3ed11"
}
}
}
});
}
}
else
{
await _GlueClient.CreateConnectionAsync(new CreateConnectionRequest
{
ConnectionInput = new ConnectionInput
{
ConnectionProperties = new Dictionary <string, string>()
{
{ "JDBC_CONNECTION_URL", "jdbc:sqlserver://" + Environment.GetEnvironmentVariable("RDS_HOSTNAME") + ":" + Environment.GetEnvironmentVariable("RDS_PORT") + ";databaseName=" + Environment.GetEnvironmentVariable("GLUE_INGESTION-DB_NAME") },
{ "JDBC_ENFORCE_SSL", "false" },
{ "USERNAME", Environment.GetEnvironmentVariable("RDS_USERNAME") },
{ "PASSWORD", Environment.GetEnvironmentVariable("RDS_PASSWORD") },
},
ConnectionType = ConnectionType.JDBC,
Name = Environment.GetEnvironmentVariable("GLUE_DB-CONNECTION_NAME"),
PhysicalConnectionRequirements = new PhysicalConnectionRequirements
{
AvailabilityZone = "ap-southeast-1c",
SubnetId = "subnet-0daa6ec8e25a13077",
SecurityGroupIdList = new List <string>
{
"sg-0e1e79f6d49b3ed11"
}
}
}
});
}
}
if (!_context.LogInputs.Any())
{
_context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.LogInputs ON");
_context.LogInputs.Add(new Models.LogInput
{
ID = 1,
Name = "Test Website",
FirehoseStreamName = "SmartInsights-Test-Website",
ConfigurationJSON = "{\r\n \"cloudwatch.emitMetrics\": false,\r\n \"awsSecretAccessKey\": \"XW2HNGQnW9ygpvPDzQQemY0AhsFlUGwiKnVpZGbO\",\r\n \"firehose.endpoint\": \"firehose.ap-southeast-1.amazonaws.com\",\r\n \"awsAccessKeyId\": \"AKIASXW25GZQH5IABE4P\",\r\n \"flows\": [\r\n {\r\n \"filePattern\": \"/var/www/example.hansen-lim.me/log/access.log\",\r\n \"deliveryStream\": \"SmartInsights-Test-Website\",\r\n \"dataProcessingOptions\": [\r\n {\r\n \"optionName\": \"LOGTOJSON\",\r\n \"logFormat\": \"COMBINEDAPACHELOG\"\r\n }\r\n ]\r\n}",
LogInputCategory = Models.LogInputCategory.ApacheWebServer,
LinkedUserID = 1,
LinkedS3BucketID = _context.S3Buckets.Find(2).ID,
LinkedS3Bucket = _context.S3Buckets.Find(2),
InitialIngest = true
});
_context.LogInputs.Add(new Models.LogInput
{
ID = 2,
Name = "Linux SSH Server Logs",
FirehoseStreamName = "SmartInsights-SSH-Login-Logs",
ConfigurationJSON = "{\r\n \"cloudwatch.emitMetrics\": false,\r\n \"awsSecretAccessKey\": \"XW2HNGQnW9ygpvPDzQQemY0AhsFlUGwiKnVpZGbO\",\r\n \"firehose.endpoint\": \"firehose.ap-southeast-1.amazonaws.com\",\r\n \"awsAccessKeyId\": \"AKIASXW25GZQH5IABE4P\",\r\n \"flows\": [\r\n {\r\n \"filePattern\": \"/opt/log/www1/secure.log\",\r\n \"deliveryStream\": \"SmartInsights-SSH-Login-Logs\",\r\n \"dataProcessingOptions\": [\r\n {\r\n \"optionName\": \"LOGTOJSON\",\r\n \"logFormat\": \"SYSLOG\",\r\n \"matchPattern\": \"^([\\\\w]+) ([\\\\w]+) ([\\\\d]+) ([\\\\d]+) ([\\\\w:]+) ([\\\\w]+) ([\\\\w]+)\\\\[([\\\\d]+)\\\\]\\\\: ([\\\\w\\\\s.\\\\:=]+)$\",\r\n \"customFieldNames\": [\"weekday\", \"month\", \"day\", \"year\", \"time\", \"host\", \"process\", \"identifer\",\"message\"]\r\n }\r\n ]\r\n}",
LogInputCategory = Models.LogInputCategory.SSH,
LinkedUserID = 1,
LinkedS3BucketID = _context.S3Buckets.Find(3).ID,
LinkedS3Bucket = _context.S3Buckets.Find(3),
InitialIngest = true
});
_context.LogInputs.Add(new Models.LogInput
{
ID = 3,
Name = "Windows Security Events",
FirehoseStreamName = "SmartInsights-Windows-Security-Logs",
ConfigurationJSON = "{ \r\n \"Sources\":[ \r\n { \r\n \"Id\":\"" + "WinSecurityLog" + "\",\r\n \"SourceType\":\"WindowsEventLogSource\",\r\n \"LogName\":\" " + "Security" + " \"\r\n \"IncludeEventData\" : true\r\n }\r\n ],\r\n \"Sinks\":[ \r\n { \r\n \"Id\":\"WinSecurityKinesisFirehose\",\r\n \"SinkType\":\"KinesisFirehose\",\r\n \"AccessKey\":\"" + "AKIASXW25GZQH5IABE4P" + "\",\r\n \"SecretKey\":\"" + "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" + "\",\r\n \"Region\":\"ap-southeast-1\",\r\n \"StreamName\":\"" + "SmartInsights-Windows-Security-Logs" + "\"\r\n \"Format\": \"json\"\r\n }\r\n ],\r\n \"Pipes\":[ \r\n { \r\n \"Id\":\"WinSecurityPipe\",\r\n \"SourceRef\":\"WinSecurityLog\",\r\n \"SinkRef\":\"WinSecurityKinesisFirehose\"\r\n }\r\n ],\r\n \"SelfUpdate\":0\r\n}",
LogInputCategory = Models.LogInputCategory.WindowsEventLogs,
LinkedUserID = 1,
LinkedS3BucketID = _context.S3Buckets.Find(4).ID,
LinkedS3Bucket = _context.S3Buckets.Find(4),
InitialIngest = true
});
_context.LogInputs.Add(new Models.LogInput
{
ID = 4,
Name = "Squid Proxy Server",
FirehoseStreamName = "SmartInsights-Cisco-Squid-Proxy-Logs",
ConfigurationJSON = "{\r\n \"cloudwatch.emitMetrics\": false,\r\n \"awsSecretAccessKey\": \"XW2HNGQnW9ygpvPDzQQemY0AhsFlUGwiKnVpZGbO\",\r\n \"firehose.endpoint\": \"firehose.ap-southeast-1.amazonaws.com\",\r\n \"awsAccessKeyId\": \"AKIASXW25GZQH5IABE4P\",\r\n \"flows\": [\r\n {\r\n \"filePattern\": \"/opt/log/cisco_router1/cisco_ironport_web.log\",\r\n \"deliveryStream\": \"SmartInsights-Cisco-Squid-Proxy-Logs\",\r\n \"dataProcessingOptions\": [\r\n {\r\n \"optionName\": \"LOGTOJSON\",\r\n \"logFormat\": \"SYSLOG\",\r\n \"matchPattern\": \"^([\\\\w.]+) (?:[\\\\d]+) ([\\\\d.]+) ([\\\\w]+)\\\\/([\\\\d]+) ([\\\\d]+) ([\\\\w.]+) ([\\\\S]+) ([\\\\S]+) (?:[\\\\w]+)\\\\/([\\\\S]+) ([\\\\S]+) (?:[\\\\S\\\\s]+)$\",\r\n \"customFieldNames\": [\"timestamp\",\"destination_ip_address\",\"action\",\"http_status_code\",\"bytes_in\",\"http_method\",\"requested_url\",\"user\",\"requested_url_domain\",\"content_type\"]\r\n }\r\n ]\r\n }\r\n ]\r\n}",
LogInputCategory = Models.LogInputCategory.SquidProxy,
LinkedUserID = 1,
LinkedS3BucketID = _context.S3Buckets.Find(5).ID,
LinkedS3Bucket = _context.S3Buckets.Find(5),
InitialIngest = true
});
await _context.SaveChangesAsync();
_context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.LogInputs OFF");
_context.GlueConsolidatedEntities.Add(new Models.GlueConsolidatedEntity
{
CrawlerName = "Test Website",
LinkedLogInputID = _context.LogInputs.Find(1).ID,
JobName = "Test Website"
});
_context.GlueConsolidatedEntities.Add(new Models.GlueConsolidatedEntity
{
CrawlerName = "SSH Logs",
LinkedLogInputID = _context.LogInputs.Find(2).ID,
JobName = "SSH Logs"
});
_context.GlueConsolidatedEntities.Add(new Models.GlueConsolidatedEntity
{
CrawlerName = "Windows Security Logs",
LinkedLogInputID = _context.LogInputs.Find(3).ID,
JobName = "Windows Security Logs"
});
_context.GlueConsolidatedEntities.Add(new Models.GlueConsolidatedEntity
{
CrawlerName = "Squid Proxy Logs",
LinkedLogInputID = _context.LogInputs.Find(4).ID,
JobName = "Squid Proxy Logs"
});
_context.AlertTriggers.Add(new Models.Trigger
{
Name = "Logins",
AlertTriggerType = Models.AlertTriggerType.IPInsights,
CondtionalField = "request",
CondtionType = "Equal",
Condtion = "GET /staging HTTP/1.1",
IPAddressField = "host",
UserField = "authuser",
CurrentInputDataKey = "Test-Website/Input/ipinsights/data-2020-02-11-03-15-42.csv",
CurrentModelFileKey = "Test-Website/Model",
CheckpointKey = "Test-Website/Checkpoint",
SagemakerStatus = Models.SagemakerStatus.Trained,
SagemakerErrorStage = Models.SagemakerErrorStage.None,
CurrentModelName = "Test-WebsiteModel-2020-02-11-03-21-38",
TrainingJobName = "Test-Website-IPInsights-Training-2020-02-11-03-15-42",
TrainingJobARN = "arn:aws:sagemaker:ap-southeast-1:188363912800:training-job/test-website-ipinsights-training-2020-02-11-03-15-42",
EndpointConfigurationName = "Test-WebsiteEndpointConfig-2020-02-11-03-21-39",
EndpointConfigurationARN = "arn:aws:sagemaker:ap-southeast-1:188363912800:endpoint-config/test-websiteendpointconfig-2020-02-11-03-21-39",
InferenceBookmark = 13,
TrainingBookmark = 13,
LinkedLogInputID = _context.LogInputs.Find(1).ID
});
await _context.SaveChangesAsync();
}
_context.Database.CloseConnection();
}
/// <summary>
/// Creates or updates a glue table for the new CUR files. This makes sure any changes in the columns are captured
/// and applied to the table. This will end up creating a new table for each billing period.
/// </summary>
/// <param name="manifest"></param>
/// <param name="context"></param>
/// <returns>The table name</returns>
private static async Task <string> CreateOrUpdateGlueTable(Manifest manifest, ILambdaContext context)
{
if (String.IsNullOrEmpty(_GlueDatabaseName))
{
string Message = "No Glue database name defined, cannot create a table.";
context.LogWarning(Message);
await SNSNotify(Message, context);
return(String.Empty);
}
string Date = manifest.BillingPeriod.Start.ToString("yyyy-MM-dd");
string Format = manifest.ContentType.ToLower().Substring(manifest.ContentType.LastIndexOf("/") + 1);
Dictionary <string, string> Parameters;
StorageDescriptor Descriptor;
switch (Format)
{
case "csv":
{
Parameters = new Dictionary <string, string>()
{
{ "EXTERNAL", "TRUE" },
{ "skip.header.line.count", "1" },
{ "columnsOrdered", "true" },
{ "compressionType", manifest.Compression.ToString().ToLower() },
{ "classification", manifest.ContentType.ToLower().Substring(manifest.ContentType.LastIndexOf("/") + 1) }
};
Descriptor = new StorageDescriptor()
{
Columns = manifest.Columns.Select(x => new Amazon.Glue.Model.Column()
{
Name = $"{x.Category}/{x.Name}", Type = "string"
}).ToList(),
InputFormat = "org.apache.hadoop.mapred.TextInputFormat",
OutputFormat = "org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat",
Location = $"s3://{_DestinationBucket}/{GetDestinationPrefix(manifest)}",
SerdeInfo = new SerDeInfo()
{
Name = "OpenCSVSerde",
SerializationLibrary = "org.apache.hadoop.hive.serde2.OpenCSVSerde",
Parameters = new Dictionary <string, string>()
{
{ "escapeChar", "\\" },
{ "quoteChar", "\"" },
{ "separatorChar", "," }
}
}
};
break;
}
case "parquet":
{
Parameters = new Dictionary <string, string>()
{
{ "EXTERNAL", "TRUE" },
{ "compressionType", manifest.Compression.ToString().ToLower() },
{ "classification", manifest.ContentType.ToLower().Substring(manifest.ContentType.LastIndexOf("/") + 1) }
};
Descriptor = new StorageDescriptor()
{
Columns = manifest.Columns.Select(x => new Amazon.Glue.Model.Column()
{
Name = x.Name, Type = (!String.IsNullOrEmpty(x.Type) ? x.Type.ToLower() : "string")
}).ToList(),
InputFormat = "org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat",
OutputFormat = "org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat",
Location = $"s3://{_DestinationBucket}/{GetDestinationPrefix(manifest)}",
SerdeInfo = new SerDeInfo()
{
Name = "ParquetHiveSerDe",
SerializationLibrary = "org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe",
Parameters = new Dictionary <string, string>()
{
{ "serialization.format", "1" }
}
}
};
break;
}
default:
{
string Message = $"Failed to create or update the database {_GlueDatabaseName} table. Unknown format type ${manifest.ContentType}.";
await SNSNotify(Message, context);
return(String.Empty);
}
}
// The updated table input for this particular CUR
TableInput TblInput = new TableInput()
{
Description = Date,
Name = Date,
TableType = "EXTERNAL_TABLE",
Parameters = Parameters,
StorageDescriptor = Descriptor
};
// Make sure the database exists
GetDatabaseRequest GetDb = new GetDatabaseRequest()
{
Name = _GlueDatabaseName
};
try
{
await _GlueClient.GetDatabaseAsync(GetDb);
context.LogInfo($"Database {_GlueDatabaseName} already exists.");
}
catch (EntityNotFoundException)
{
try
{
CreateDatabaseRequest DbRequest = new CreateDatabaseRequest()
{
DatabaseInput = new DatabaseInput()
{
Name = _GlueDatabaseName
}
};
CreateDatabaseResponse Response = await _GlueClient.CreateDatabaseAsync(DbRequest);
if (Response.HttpStatusCode == HttpStatusCode.OK)
{
context.LogInfo($"Successfully CREATED database {_GlueDatabaseName}.");
}
else
{
context.LogError($"Failed to CREATE database with status code {(int)Response.HttpStatusCode}.");
}
}
catch (Exception ex)
{
string Message = $"Failed to create the database {_GlueDatabaseName}.";
context.LogError(Message, ex);
await SNSNotify(Message + $" {ex.Message}", context);
return(String.Empty);
}
}
// Make sure the table exists
GetTableRequest GetTable = new GetTableRequest()
{
DatabaseName = _GlueDatabaseName,
Name = Date
};
try
{
GetTableResponse TableResponse = await _GlueClient.GetTableAsync(GetTable);
UpdateTableRequest UpdateReq = new UpdateTableRequest()
{
TableInput = TblInput,
DatabaseName = _GlueDatabaseName
};
UpdateTableResponse Response = await _GlueClient.UpdateTableAsync(UpdateReq);
if (Response.HttpStatusCode == HttpStatusCode.OK)
{
context.LogInfo($"Successfully UPDATED table {TblInput.Name} in database {_GlueDatabaseName}.");
return(TblInput.Name);
}
else
{
string Message = $"Failed to UPDATE table with status code {(int)Response.HttpStatusCode}.";
context.LogError(Message);
await SNSNotify(Message, context);
return(String.Empty);
}
}
catch (EntityNotFoundException) // This means the table does not exist
{
CreateTableRequest CreateReq = new CreateTableRequest()
{
TableInput = TblInput,
DatabaseName = _GlueDatabaseName
};
CreateTableResponse Response = await _GlueClient.CreateTableAsync(CreateReq);
if (Response.HttpStatusCode == HttpStatusCode.OK)
{
context.LogInfo($"Successfully CREATED table {TblInput.Name} in database {_GlueDatabaseName}.");
return(TblInput.Name);
}
else
{
string Message = $"Failed to CREATE table with status code {(int)Response.HttpStatusCode}.";
context.LogError(Message);
await SNSNotify(Message, context);
return(String.Empty);
}
}
}