public async Task <IActionResult> Index(CBoxList cblist, string name)
{
try
{
StringBuilder _schema = new StringBuilder();
foreach (var item in cblist.cboxlist)
{
if (item.isChecked)
{
_schema.Append(item.boxName + ", ");
}
}
var client = await BigQueryClient.CreateAsync("iloyalty");
string mlConfig = "model_type = 'LOGISTIC_REG', auto_class_weights = TRUE, input_label_cols =['churn'], max_iterations = 50";
string sql = @"CREATE OR REPLACE MODEL `iloyalty.telco_db." + name + "` OPTIONS (" + mlConfig + ") AS SELECT " + _schema + "churn FROM `iloyalty.telco_db.test_view3` WHERE dataframe = 'training'";
//string sql = @"CREATE MODEL `iloyalty.telco_db." + modelName + "` OPTIONS ("+mlConfig+") AS SELECT " +_schema+ " churn FROM `iloyalty.telco_db.test_view3` WHERE dataframe = 'training'";
await client.ExecuteQueryAsync(sql, parameters : null);
}
catch (Exception e)
{
ViewBag.msg = e.Message.ToString();
return(View(cblist));
}
ViewBag.msg = name + "model created!";
return(View(cblist));
}
/// <summary>
/// Handle the intent.
/// </summary>
/// <param name="req">Webhook request</param>
/// <returns>Webhook response</returns>
public override async Task <WebhookResponse> HandleAsync(WebhookRequest req)
{
var errorMessage = ExtractAndValidateParameters(req, out string hottestOrColdest, out string year,
out string countryName, out string fipsCountry);
if (errorMessage != null)
{
return(new WebhookResponse
{
FulfillmentText = errorMessage
});
}
var bigQueryClient = await BigQueryClient.CreateAsync(Program.AppSettings.GoogleCloudSettings.ProjectId);
// Build the parameterized SQL query
var table = bigQueryClient.GetTable("bigquery-public-data", "noaa_gsod", $"gsod*");
var tableStations = bigQueryClient.GetTable("bigquery-public-data", "noaa_gsod", "stations");
var sql = $"SELECT (gsod.temp - 32)*5/8 AS celcius, stations.name AS name, gsod.year AS y, gsod.mo AS m, gsod.da AS d \n" +
$"FROM {table} AS gsod \n" +
$"JOIN {tableStations} AS stations ON gsod.stn = stations.usaf AND gsod.wban = stations.wban \n" +
$"WHERE stations.country=@fipsCountry and gsod.year=@year \n" +
$"ORDER BY gsod.temp{(hottestOrColdest == "hottest" ? " DESC" : "")} \n" +
"limit 10";
// Create the BigQuery parameters
var parameters = new[]
{
new BigQueryParameter("fipsCountry", BigQueryDbType.String, fipsCountry),
new BigQueryParameter("year", BigQueryDbType.String, year)
};
var(resultList, processedMb, secs) = await RunQueryAsync(bigQueryClient, sql, parameters);
// Check if there's data.
if (resultList.Count == 0)
{
return(new WebhookResponse
{
FulfillmentText = "Sorry, there is no data."
});
}
// Show SQL query and query results in browser
var temperatures = resultList
.Select(x => $"{(double)x["celcius"]:0.0}°C at {x["name"]} on {x["y"]}-{x["m"]}-{x["d"]}")
.ToList();
ShowQuery(sql, parameters, (processedMb, secs, temperatures));
// Send spoken response to DialogFlow
var top = resultList[0];
return(new WebhookResponse
{
FulfillmentText = $"Scanned {processedMb} mega-bytes in {secs:0.0} seconds. " +
$"The {hottestOrColdest} temperature in {countryName} in the year {year} was " +
$"{(double)top["celcius"]:0.0} degrees celcius, at the {top["name"]} monitoring station."
});
}
public void Configure(IApplicationBuilder app, IWebHostEnvironment env, ILogger <Startup> logger)
{
if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
}
logger.LogInformation("Service is starting...");
app.UseRouting();
var eventReader = new CloudEventReader(logger);
var configReader = new ConfigReader(logger, CloudEventSource, CloudEventType);
var projectId = configReader.Read("PROJECT_ID");
var eventWriter = configReader.ReadEventWriter();
app.UseEndpoints(endpoints =>
{
endpoints.MapPost("/", async context =>
{
var client = await BigQueryClient.CreateAsync(projectId);
var country = await eventReader.ReadCloudSchedulerData(context);
_tableId = country.Replace(" ", "").ToLowerInvariant();
var results = await RunQuery(client, country, logger);
logger.LogInformation("Executed query");
var replyData = new { datasetId = DatasetId, tableId = _tableId, country = country };
await eventWriter.Write(replyData, context);
});
});
}
public async Task <string> ProcessAsync(string s3BucketName, string s3ObjectKey)
{
var apiServerLogKeyword = Environment.GetEnvironmentVariable("S3_API_SERVER_LOG_KEYWORD");
var dedicatedServerLogKeyword = Environment.GetEnvironmentVariable("S3_DEDICATED_SERVER_LOG_KEYWORD");
if (!s3ObjectKey.ToLower().Contains(apiServerLogKeyword) &&
!s3ObjectKey.ToLower().Contains(dedicatedServerLogKeyword))
{
return(string.Empty);
}
var readCount = 0;
var processCount = 0;
var insertCount = 0;
var projectId = Environment.GetEnvironmentVariable("BQ_PROJECT_ID");
var datasetId = Environment.GetEnvironmentVariable("BQ_DATASET_ID");
var sw = Stopwatch.StartNew();
// https://cloud.google.com/docs/authentication/getting-started
// environment variable GOOGLE_APPLICATION_CREDENTIALS
using (var client = await BigQueryClient.CreateAsync(projectId))
{
var request = new GetObjectRequest
{
BucketName = s3BucketName,
Key = s3ObjectKey,
};
using (var getResponse = await S3Client.GetObjectAsync(request))
using (var streamReader = new StreamReader(getResponse.ResponseStream))
{
try
{
if (s3ObjectKey.ToLower().Contains(apiServerLogKeyword))
{
(readCount, processCount, insertCount) = await new ApiServerLogProcessor(client, datasetId).ProcessAsync(streamReader);
}
else if (s3ObjectKey.ToLower().Contains(dedicatedServerLogKeyword))
{
(readCount, processCount, insertCount) = await new DedicatedServerLogProcessor(client, datasetId).ProcessAsync(streamReader);
}
}
catch
{
// todo: BigQueryにtransactionの概念はあるか？
// todo: ログ出力は呼び出し元に任せるから、transactionの概念がないならここでのtry-catchは不要
//context.Logger.LogLine("rollback");
throw;
}
}
}
return($"{sw.Elapsed.TotalMilliseconds:N0} msec. read: {readCount:N0} lines. process: {processCount:N0} lines. insert: {insertCount:N0} rows.");
}
static async Task MainAsync(string[] args)
{
GoogleCredential googleCredential = GoogleCredential.FromFile(_googleCredentialPath);
BigQueryClient bigQueryClient = await BigQueryClient.CreateAsync(_bigQueryProjectId, googleCredential);
string query = $"select * from `{_bigQueryProjectId}.{_bigQueryDataSetId}.{_bigQueryTableId}`";
BigQueryResults results = await RunBigQueryAsync(bigQueryClient, query);
await ExportBigQueryTableToStorageAsync(bigQueryClient, _cloudStorageDestinationUri, results);
}
public async Task <BigQueryClient> GetClient(string projectId, string credsPath)
{
GoogleCredential creds;
using (var stream = new FileStream(credsPath, FileMode.Open, FileAccess.Read)) {
creds = GoogleCredential.FromStream(stream);
}
var client = await BigQueryClient.CreateAsync(projectId, creds);
return(client);
}
private async Task <string> ProcessAsync(MasterData masterData)
{
var projectId = Environment.GetEnvironmentVariable("BQ_PROJECT_ID");
var datasetId = Environment.GetEnvironmentVariable("BQ_DATASET_ID");
var sw = Stopwatch.StartNew();
// https://cloud.google.com/docs/authentication/getting-started
// environment variable GOOGLE_APPLICATION_CREDENTIALS
using (var client = await BigQueryClient.CreateAsync(projectId))
{
await new MasterDataProcessor(client, datasetId, masterData).ProcessAsync();
}
return($"[INFO ] {sw.Elapsed.TotalMilliseconds:N0} msec.");
}
/// <summary>
/// Handle the intent.
/// </summary>
/// <param name="req">Webhook request</param>
/// <returns>Webhook response</returns>
public override async Task <WebhookResponse> HandleAsync(WebhookRequest req)
{
// Extract the DialogFlow date without the time
var date = req.QueryResult.Parameters.Fields["date"].StringValue;
var dateTime = DateTime.Parse(date);
date = dateTime.ToString("yyyy-MM-dd");
// Create the BigQuery client with default credentials
var bigQueryClient = await BigQueryClient.CreateAsync(Program.AppSettings.GoogleCloudSettings.ProjectId);
// Build the parameterized SQL query
var table = bigQueryClient.GetTable("bigquery-public-data", "hacker_news", "full");
var sql = $"SELECT title, url \nFROM {table} \n" +
"WHERE STARTS_WITH(CAST(timestamp AS STRING), @date) AND type=\"story\" \n" +
"ORDER BY score DESC \n" +
"LIMIT 10";
// Create the BigQuery parameters
var parameters = new[]
{
new BigQueryParameter("date", BigQueryDbType.String, date)
};
var(resultList, processedMb, secs) = await RunQueryAsync(bigQueryClient, sql, parameters);
// Check if there's data.
if (resultList.Count == 0)
{
return(new WebhookResponse
{
FulfillmentText = "Sorry, there is no data."
});
}
// Show SQL query and query results in browser
var titles = resultList.Select(x => x["title"].ToString()).ToList();
ShowQuery(sql, parameters, (processedMb, secs, titles));
// Send spoken response to DialogFlow
return(new WebhookResponse
{
FulfillmentText = $"Scanned {processedMb} mega-bytes in {secs:0.0} seconds. " +
$"The top title on hacker news was titled: {titles.First()}"
});
}
public void Configure(IApplicationBuilder app, IWebHostEnvironment env, ILogger <Startup> logger)
{
if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
}
logger.LogInformation("Service is starting...");
app.UseRouting();
app.UseEndpoints(endpoints =>
{
endpoints.MapPost("/", async context =>
{
var projectId = Environment.GetEnvironmentVariable("PROJECT_ID");
var client = await BigQueryClient.CreateAsync(projectId);
var cloudEvent = await context.Request.ReadCloudEventAsync();
logger.LogInformation("Received CloudEvent\n" + GetEventLog(cloudEvent));
var country = (string)cloudEvent.Data;
_tableId = country.Replace(" ", "").ToLowerInvariant();
var results = await RunQuery(client, country, logger);
logger.LogInformation("Executed query");
var replyData = JsonConvert.SerializeObject(new { datasetId = DatasetId, tableId = _tableId, country = country });
var replyEvent = GetEventReply(replyData);
logger.LogInformation("Replying with CloudEvent\n" + GetEventLog(replyEvent));
// Binary format
//TODO: There must be a better way to convert CloudEvent to HTTP response
context.Response.Headers.Add("Ce-Id", replyEvent.Id);
context.Response.Headers.Add("Ce-Specversion", "1.0");
context.Response.Headers.Add("Ce-Type", replyEvent.Type);
context.Response.Headers.Add("Ce-Source", replyEvent.Source.ToString());
context.Response.ContentType = "application/json;charset=utf-8";
await context.Response.WriteAsync(replyEvent.Data.ToString());
});
});
}
public async Task Initialize()
{
this.Client = await BigQueryClient.CreateAsync(ProjectId);
this.Table = await this.Client.GetTableAsync(DatasetId, TableId);
}
/// <summary>
/// Handle the intent.
/// </summary>
/// <param name="req">Webhook request</param>
/// <returns>Webhook response</returns>
public override async Task <WebhookResponse> HandleAsync(WebhookRequest req)
{
// Extract the DialogFlow date, without the time, that has been requested
// Format is "yyyy-mm-dd"
var date = req.QueryResult.Parameters.Fields["date"].StringValue;
date = date.Substring(0, Math.Min(10, date.Length));
// Create the BigQuery client with default credentials
var bigQueryClient = await BigQueryClient.CreateAsync(Program.AppSettings.GoogleCloudSettings.ProjectId);
// Build the parameterized SQL query
var table = bigQueryClient.GetTable("bigquery-public-data", "hacker_news", "full");
var sql = $"SELECT title, url \nFROM {table} \n" +
"WHERE STARTS_WITH(CAST(timestamp AS STRING), @date) AND type=\"story\" \n" +
"ORDER BY score DESC \n" +
"LIMIT 10";
// Create the BigQuery parameters
var parameters = new[]
{
new BigQueryParameter("date", BigQueryDbType.String, date)
};
// Show SQL query in browser
ShowQuery(sql, parameters);
// Time the BigQuery execution with a StopWatch
var stopwatch = new Stopwatch();
stopwatch.Start();
// Execute BigQuery SQL query. This can take time
var result = await bigQueryClient.ExecuteQueryAsync(sql, parameters);
// Query finished, stop the StopWatch
stopwatch.Stop();
// Get a job reference, for statistics
var job = await bigQueryClient.GetJobAsync(result.JobReference);
// Get result list, and check that there are some results
var resultList = result.ToList();
if (resultList.Count == 0)
{
return(new WebhookResponse
{
FulfillmentText = "Sorry, there is no data for that date."
});
}
// Time and data statistics
long processedMb = job.Statistics.TotalBytesProcessed.Value / (1024 * 1024);
double secs = stopwatch.Elapsed.TotalSeconds;
var titles = resultList.Select(x => x["title"].ToString()).ToList();
// Show SQL query and query results in browser
ShowQuery(sql, parameters, (processedMb, secs, titles));
// Send spoken response to DialogFlow
return(new WebhookResponse
{
FulfillmentText = $"Scanned {processedMb} mega-bytes in {secs:0.0} seconds. " +
$"The top title on hacker news was titled: {titles.First()}"
});
}
/// <summary>
/// Handle the intent.
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
public override async Task <string> HandleAsync(ConvRequest req)
{
var errorMessage = ExtractAndValidateParameters(req, out string temp, out string year, out string countryCode2,
out string countryName, out string fipsCountry);
if (errorMessage != null)
{
return(DialogflowApp.Tell(errorMessage));
}
var bigQueryClient = await BigQueryClient.CreateAsync(Program.AppSettings.GoogleCloudSettings.ProjectId);
// Build the parameterized SQL query
var tableGsod = bigQueryClient.GetTable("bigquery-public-data", "noaa_gsod", $"gsod*");
var tableStations = bigQueryClient.GetTable("bigquery-public-data", "noaa_gsod", "stations");
var sql = $"SELECT (gsod.temp - 32)*5/8 AS celcius, stations.name AS name, gsod.year AS y, gsod.mo AS m, gsod.da AS d \n" +
$"FROM {tableGsod} AS gsod \n" +
$"JOIN {tableStations} AS stations ON gsod.stn = stations.usaf AND gsod.wban = stations.wban \n" +
$"WHERE stations.country=@fipsCountry and gsod.year=@year \n" +
$"ORDER BY gsod.temp{(temp == "hottest" ? " DESC" : "")} \n" +
"limit 10";
// Create the BigQuery parameters
var parameters = new[]
{
new BigQueryParameter("fipsCountry", BigQueryDbType.String, fipsCountry),
new BigQueryParameter("year", BigQueryDbType.String, year)
};
// Show SQL query in browser
ShowQuery(sql, parameters);
// Time the BigQuery execution with a StopWatch
var stopwatch = new Stopwatch();
stopwatch.Start();
// Execute BigQuery SQL query. This can take time
var result = await bigQueryClient.ExecuteQueryAsync(sql, parameters);
// Query finished, stop the StopWatch
stopwatch.Stop();
// Get a job reference, for statistics
var job = await bigQueryClient.GetJobAsync(result.JobReference);
// Get result list, and check that there are some results
var resultList = result.ToList();
if (resultList.Count == 0)
{
return(DialogflowApp.Tell($"Sorry, there is no data for country '{countryName}'"));
}
// Time and data statistics
long processedMb = job.Statistics.TotalBytesProcessed.Value / (1024 * 1024);
double secs = stopwatch.Elapsed.TotalSeconds;
var temperatures = resultList
.Select(x => $"{(double)x["celcius"]:0.0}°C at {x["name"]} on {x["y"]}-{x["m"]}-{x["d"]}")
.ToList();
// Show SQL query and query results in browser
ShowQuery(sql, parameters, (processedMb, secs, temperatures));
// Send spoken response to DialogFlow
var top = resultList[0];
return(DialogflowApp.Tell($"Scanned {processedMb} mega-bytes in {secs:0.0} seconds. " +
$"The {temp} temperature in {countryName} in the year {year} was " +
$"{(double)top["celcius"]:0.0} degrees celcius, at the {top["name"]} monitoring station."));
}
