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)); }