static async Task queryAirportsByTimezoneOffset(string tzOffset)
{
// SELECT * FROM c where c.timezone_num = "-5"
// SELECT c.pk FROM c where c.timezone_num = "-5" offset 1 limit 2
// SELECT COUNT(1) FROM c <-- 1459
// SELECT COUNT(1) FROM c where c.timezone_num = "-5" <-- 522
// https://docs.microsoft.com/en-us/azure/cosmos-db/sql-query-offset-limit
log("queryAirportsByTimezoneOffset");
CosmosUtil cu = new CosmosUtil();
string dbname = dbNameEnvVar();
string cname = "airports";
await cu.setCurrentDatabase(dbname);
await cu.setCurrentContainer(cname);
string sql = $"select c.name, c.city, c.longitude, c.timezone_num from c where c.timezone_num = '{tzOffset}'";
List <dynamic> items = await cu.queryDocuments(sql);
for (int i = 0; i < items.Count; i++)
{
Console.WriteLine(items[i]);
}
return;
}
static async Task deleteDocuments(string dbname, string cname, int maxCount)
{
log($"deleteDocuments; db: {dbname}, container: {cname}, maxCount: {maxCount}");
CosmosUtil cu = new CosmosUtil();
await cu.setCurrentDatabase(dbname);
await cu.setCurrentContainer(cname);
string sql = $"select * from c";
List <dynamic> items = await cu.queryDocuments(sql, maxCount);
log($"deleteDocuments, count: {items.Count}");
for (int i = 0; i < items.Count; i++)
{
Console.WriteLine(items[i]);
log($"deleting item: {items[i].id}, {items[i].pk}");
string id = items[i].id;
string pk = items[i].pk;
GenericDocument doc = new GenericDocument();
doc.id = items[i].id;
doc.pk = items[i].pk;
ItemResponse <GenericDocument> response = await cu.deleteGenericDocument(doc);
log($"status code: {response.StatusCode}");
log($"request charge: {response.RequestCharge}");
}
}
static async Task upsertAirports(int maxCount)
{
log($"upsertAirports: {maxCount}");
FileUtil fsu = new FileUtil();
List <Airport> airports = new FileUtil().ReadAirportsCsv();
log($"airports read from csv file: {airports.Count}");
CosmosUtil cu = new CosmosUtil();
string dbname = dbNameEnvVar();
string cname = "airports";
await cu.setCurrentDatabase(dbname);
await cu.setCurrentContainer(cname);
for (int i = 0; i < airports.Count; i++)
{
if (i < maxCount)
{
Airport a = airports[i];
Console.WriteLine(a.ToJson());
ItemResponse <Airport> response = await cu.upsertAirportDocument(a);
log($"status code: {response.StatusCode}");
log($"request charge: {response.RequestCharge}");
log($"diagnostics: {response.Diagnostics}");
log($"resource: {response.Resource}");
}
}
return;
}
static async Task queryAirportsPaginated()
{
// SELECT COUNT(1) FROM c <-- 1459
// SELECT COUNT(1) FROM c where c.timezone_num = "-5" <-- 522
// SELECT c.pk FROM c where c.timezone_num = '-5' order by c.pk offset 140 limit 20
// https://docs.microsoft.com/en-us/azure/cosmos-db/sql-query-offset-limit
// https://www.postgresql.org/docs/current/queries-limit.html
// https://github.com/azure/azure-documentdb-datamigrationtool
//
// "The rows skipped by an OFFSET clause still have to be computed inside the server;
// therefore a large OFFSET can be inefficient."
log("queryAirportsPaginated");
CosmosUtil cu = new CosmosUtil();
string dbname = dbNameEnvVar();
string cname = "airports";
await cu.setCurrentDatabase(dbname);
await cu.setCurrentContainer(cname);
string predicate = "where c.timezone_num = '-5'";
int itemCount = await cu.count(predicate);
int offset = 0;
int itemsPerPage = 20;
log("itemCount: " + itemCount);
log("itemsPerPage: " + itemsPerPage);
double pagesDouble = ((double)itemCount / (double)itemsPerPage);
double pagesCount = Math.Ceiling(pagesDouble);
log("pagesCount: " + pagesCount);
for (int p = 0; p < pagesCount; p++)
{
offset = offset + itemsPerPage;
string sql = $"SELECT c.pk FROM c {predicate} order by c.pk offset {offset} limit {itemsPerPage}";
log("===");
log($"page: {p} sql: {sql}");
List <dynamic> items = await cu.queryDocuments(sql, itemsPerPage, true);
for (int i = 0; i < items.Count; i++)
{
Console.WriteLine(items[i]);
}
}
return;
}
static async Task queryAirportsByPkId(string pk, string id)
{
log("queryAirportsByPkId");
CosmosUtil cu = new CosmosUtil();
string dbname = Environment.GetEnvironmentVariable("AZURE_IOT_COSMOSDB_SQLDB_DBNAME");
string cname = "airports";
await cu.setCurrentDatabase(dbname);
await cu.setCurrentContainer(cname);
string sql = $"select * from c where c.pk = '{pk}' and c.id = '{id}'";
List <dynamic> items = await cu.queryDocuments(sql);
for (int i = 0; i < items.Count; i++)
{
Console.WriteLine(items[i]);
}
return;
}
static async Task queryAirportsByPk(string pk)
{
log("queryAirportsByPk");
CosmosUtil cu = new CosmosUtil();
string dbname = dbNameEnvVar();
string cname = "airports";
await cu.setCurrentDatabase(dbname);
await cu.setCurrentContainer(cname);
string sql = $"select * from c where c.pk = '{pk}'";
List <dynamic> items = await cu.queryDocuments(sql);
for (int i = 0; i < items.Count; i++)
{
Console.WriteLine(items[i]);
}
return;
}
static async Task queryAirportsByTimezoneCode(string tzCode)
{
log("queryAirportsByTimezoneCode");
CosmosUtil cu = new CosmosUtil();
string dbname = dbNameEnvVar();
string cname = "airports";
await cu.setCurrentDatabase(dbname);
await cu.setCurrentContainer(cname);
string sql = $"select c.name, c.city, c.longitude, c.timezone_num from c where c.TimezoneCode = '{tzCode}'";
List <dynamic> items = await cu.queryDocuments(sql);
for (int i = 0; i < items.Count; i++)
{
Console.WriteLine(items[i]);
}
return;
}
static async Task queryLatestEvents(int limit)
{
log("queryLatestEvents");
CosmosUtil cu = new CosmosUtil();
string dbname = Environment.GetEnvironmentVariable("AZURE_IOT_COSMOSDB_SQLDB_DBNAME");
string cname = "events";
await cu.setCurrentDatabase(dbname);
await cu.setCurrentContainer(cname);
string sql = $"select * from c order by c.epoch desc offset 0 limit {limit}";
List <dynamic> items = await cu.queryDocuments(sql);
for (int i = 0; i < items.Count; i++)
{
Console.WriteLine(items[i]);
}
return;
}
static async Task queryEvents(double startEpoch, double endEpoch)
{
log($"queryEvents: {startEpoch} to {endEpoch}");
CosmosUtil cu = new CosmosUtil();
string dbname = Environment.GetEnvironmentVariable("AZURE_IOT_COSMOSDB_SQLDB_DBNAME");
string cname = "events";
await cu.setCurrentDatabase(dbname);
await cu.setCurrentContainer(cname);
string sql = $"select * from c where c.epoch >= {startEpoch} and c.epoch < {endEpoch}";
List <dynamic> items = await cu.queryDocuments(sql);
for (int i = 0; i < items.Count; i++)
{
Console.WriteLine(items[i]);
}
return;
}
static async Task queryAirportsByGPS(double lng, double lat, double meters)
{
log("queryAirportsByGPS");
CosmosUtil cu = new CosmosUtil();
string dbname = Environment.GetEnvironmentVariable("AZURE_IOT_COSMOSDB_SQLDB_DBNAME");
string cname = "airports";
await cu.setCurrentDatabase(dbname);
await cu.setCurrentContainer(cname);
string location = $"[{lng}, {lat}]";
string sql = "select c.pk, c.Name, c.City, c.location.coordinates from c " +
"where ST_DISTANCE(c.location, { \"type\": \"Point\", \"coordinates\":" +
location + "}) < " + meters;
List <dynamic> items = await cu.queryDocuments(sql);
for (int i = 0; i < items.Count; i++)
{
Console.WriteLine(items[i]);
}
return;
}
static async Task upsertAirports(int maxCount, int pauseMs)
{
log($"upsertAirports: {maxCount} pauseMs: {pauseMs}");
FileUtil fsu = new FileUtil();
List <Airport> airports = new FileUtil().ReadAirportsCsv();
log($"airports read from csv file: {airports.Count}");
CosmosUtil cu = new CosmosUtil();
string dbname = Environment.GetEnvironmentVariable("AZURE_IOT_COSMOSDB_SQLDB_DBNAME");
string cname = Environment.GetEnvironmentVariable("AZURE_IOT_COSMOSDB_SQLDB_COLLNAME");
await cu.setCurrentDatabase(dbname);
await cu.setCurrentContainer(cname);
for (int i = 0; i < airports.Count; i++)
{
if (i < maxCount)
{
Airport a = airports[i];
a.UpdateEpoch();
log("===");
Console.WriteLine(a.ToJson());
ItemResponse <Airport> response = await cu.upsertAirportDocument(a);
log("---");
log($"status code: {response.StatusCode}");
log($"request charge: {response.RequestCharge}");
//log($"diagnostics: {response.Diagnostics}");
//log($"resource: {response.Resource}");
Thread.Sleep(pauseMs);
}
}
log($"airports read from csv file: {airports.Count}");
return;
}