public async Task TruncateAndInsertAsync()
{
var sw = Stopwatch.StartNew();
var countToInsert = 0;
var query = BuildTruncateQuery() + BuildInsertQuery(ref countToInsert);
if (countToInsert == 0)
{
// todo: error message
LambdaLogger.Log($"[ERROR] [{TableName}] countToInsert: 0");
}
await Client.ExecuteQueryAsync(query, null, CommonQueryOptions);
var insertedCount = await GetInsertedCount();
if (countToInsert != insertedCount)
{
// todo: error message
LambdaLogger.Log($"[ERROR] [{TableName}] countToInsert: {countToInsert}, insertedCount: {insertedCount}");
}
LambdaLogger.Log($"[INFO ] [{TableName}] {sw.Elapsed.TotalMilliseconds:N0} msec. {countToInsert} rows inserted.");
}
protected async Task <(List <BigQueryRow>, long, double)> RunQueryAsync(BigQueryClient bigQueryClient, string sql, BigQueryParameter[] parameters)
{
// 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.
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
var resultList = result.ToList();
// Time and data statistics
long processedMb = job.Statistics.TotalBytesProcessed.Value / (1024 * 1024);
double secs = stopwatch.Elapsed.TotalSeconds;
return(resultList, processedMb, secs);
}
private async Task <BigQueryResults> RunQuery(BigQueryClient client, string country, ILogger <Startup> logger)
{
var sql = $@"SELECT date, cumulative_confirmed as num_reports
FROM `bigquery-public-data.covid19_open_data.covid19_open_data`
WHERE cumulative_confirmed > 0 and country_name = '{country}' and subregion1_code is NULL";
var table = await GetOrCreateTable(client, logger);
logger.LogInformation($"Executing query: \n{sql}");
return(await client.ExecuteQueryAsync(sql, null, new QueryOptions
{
DestinationTable = table.Reference
}));
}
public async Task <bool> selectExceptUsers()
{
BigQueryClient client = BigQueryClient.Create(projectId);
BigQueryTable table = client.GetTable(datasetId, tableId);
string sql = $"SELECT * EXCEPT (SecondNumber) FROM {table}";
BigQueryResults results = await client.ExecuteQueryAsync(sql, parameters : null);
/*foreach (BigQueryRow row in results)
* {
* Console.WriteLine($"Name: {row["player"]}; Score: {row["score"]}; Level: {row["level"]}");
* }*/
Console.WriteLine(results.ToList().Count);
return(true);
}
private async void CreateExportQuery(SENSOR_DATA_TYPE type)
{
try
{
var credential = GoogleCredential.FromFile(" path to credentials");
BigQueryClient client = BigQueryClient.Create(_projectId, credential);
string table_name = "", destination_uri = " ";
switch (type)
{
case SENSOR_DATA_TYPE.SOIL_MOISTURE:
table_name = "`smartgarden-iot.sm_gardenplant_data.plant_Soil_Moisture`";
destination_uri = "gs://bq-query-export-data/sm-data-*.csv";
break;
case SENSOR_DATA_TYPE.LIGHT:
table_name = "`smartgarden-iot.sm_gardenplant_data.plant_light_resistance`";
destination_uri = "gs://bq-query-export-data/lr-data-*.csv";
break;
case SENSOR_DATA_TYPE.TEMPERATURE:
table_name = "`smartgarden-iot.sm_gardenplant_data.plant_temperature`";
destination_uri = "gs://bq-query-export-data/tmp-data-*.csv";
break;
case SENSOR_DATA_TYPE.HUMIDITY:
table_name = "`smartgarden-iot.sm_gardenplant_data.plant_humidity`";
destination_uri = "gs://bq-query-export-data/hm-data-*.csv";
break;
default:
break;
}
string sql = string.Format(@"EXPORT DATA OPTIONS(uri='{0}', format='CSV', overwrite=true)
AS SELECT * FROM {1} WHERE TIME_DIFF(time_received, CURRENT_TIME('UTC-5'),HOUR) < 12
LIMIT 70", destination_uri, table_name);
BigQueryParameter[] parameters = null;
BigQueryResults _ = await client.ExecuteQueryAsync(sql, parameters);
}
catch (Exception ex)
{
_logger.LogError(@" \n --------------\n
Somthing went wrong in {0}. \n Message: {1} \n \n Stack trace: {3} \n Inner Exception {4}", ex.Source, ex.Message, ex.StackTrace, ex.StackTrace);
}
}
private async Task <BigQueryResults> RunQuery(BigQueryClient client, string country, ILogger <Startup> logger)
{
var sql = $@"SELECT date, SUM(confirmed) num_reports
FROM `bigquery-public-data.covid19_jhu_csse.summary`
WHERE country_region = '{country}'
GROUP BY date
ORDER BY date ASC";
logger.LogInformation($"Executing query: \n{sql}");
var table = await GetOrCreateTable(client);
return(await client.ExecuteQueryAsync(sql, null, new QueryOptions {
DestinationTable = table.Reference
}));
}
public async Task <bool> selectReplaceUsers()
{
BigQueryClient client = BigQueryClient.Create(projectId);
BigQueryTable table = client.GetTable(datasetId, tableId);
string sql = $"SELECT * REPLACE (FirstNumber / @divider AS FirstNumber) FROM {table}";
BigQueryParameter[] parameters = new[]
{
new BigQueryParameter("divider", BigQueryDbType.Int64, 2)
};
BigQueryResults results = await client.ExecuteQueryAsync(sql, parameters);
/*foreach (BigQueryRow row in results)
* {
* Console.WriteLine($"Name: {row["player"]}; Score: {row["score"]}; Level: {row["level"]}");
* }*/
Console.WriteLine(results.ToList().Count);
return(true);
}
public async Task <bool> selectNotEqualUsers()
{
BigQueryClient client = BigQueryClient.Create(projectId);
BigQueryTable table = client.GetTable(datasetId, tableId);
string sql = $"SELECT FullName, Country ,CreatedAt" +
$"FROM {table}" +
"WHERE Country != 'American Samoa'" +
"ORDER BY Country ASC; ";
BigQueryResults results = await client.ExecuteQueryAsync(sql, parameters : null);
/*foreach (BigQueryRow row in results)
* {
* Console.WriteLine($"Name: {row["player"]}; Score: {row["score"]}; Level: {row["level"]}");
* }*/
Console.WriteLine(results.ToList().Count);
return(true);
}
public async Task <bool> selectQualifyUsers()
{
BigQueryClient client = BigQueryClient.Create(projectId);
BigQueryTable table = client.GetTable(datasetId, tableId);
string sql = $"SELECT FullName, Country, CreatedAt, AvgFirstNumber, MinFirstNumber, TotalFirstNumber" +
"FROM(" +
"SELECT FullName, Country, CreatedAt," +
"AVG(FirstNumber) OVER(PARTITION BY Country) AS AvgFirstNumber," +
"MIN(FirstNumber) OVER(PARTITION BY Country) AS MinFirstNumber," +
"SUM(FirstNumber) OVER(PARTITION BY Country) AS TotalFirstNumber" +
$"FROM {table})" +
"WHERE AvgFirstNumber > 500;";
BigQueryResults results = await client.ExecuteQueryAsync(sql, parameters : null);
/*foreach (BigQueryRow row in results)
* {
* Console.WriteLine($"Name: {row["player"]}; Score: {row["score"]}; Level: {row["level"]}");
* }*/
Console.WriteLine(results.ToList().Count);
return(true);
}
public async Task <BigQueryResults> ExecuteReaderAsync(string query)
{
return(await _client.ExecuteQueryAsync(query, parameters : null));
}
