public async Task LoadAnnouncementsHousesAsync(CallsInfo[] calls)
{
_logger.LogInformation("Загрузка данных по домам объявлений из Presto");
IPrestoClient client = new PrestodbClient(Config);
var query = @"
select * from apoturaev.hackaton_dataset2_stage8 where calltrackingid in (ID)
";
var batch = 500;
var batches = Math.Ceiling((decimal)calls.Length / batch);
for (int i = 0; i < batches; i++)
{
var loadCount = Math.Min(batch, calls.Length - i * batch);
var ids = calls.Skip(i * batch).Take(loadCount).Select(x => x.CalltrackingId);
var stringIds = String.Join(',', ids.Select(x => x.ToString()).ToArray());
var parsedQuery = query.Replace("ID", stringIds);
var request = new ExecuteQueryV1Request(parsedQuery);
var response = await client.ExecuteQueryV1(request);
foreach (var data in response.Data)
{
var call = calls.First(x => x.CalltrackingId == Int32.Parse(data[1].ToString()));
var announcementId = Int32.Parse(data[0].ToString());
var announcement = call.Announcements.FirstOrDefault(x => x.Id == announcementId);
if (announcement == default)
{
announcement = new AnnouncementInfo
{
Id = announcementId
};
call.Announcements.Add(announcement);
}
announcement.House = data[3].ToString();
}
if (response.LastError != null)
{
_logger.LogError("Error while loading texts");
}
_logger.LogInformation($"Batch {i} loaded");
}
_logger.LogInformation("Закончили");
}
public async System.Threading.Tasks.Task LoadHouseInfoAsync()
{
IPrestoClient Client = new PrestodbClient(Config);
var day = new DateTime(2019, 8, 26);
while (day < DateTime.Now)
{
var dateString = day.ToString("yyyy-MM-dd");
var query = @"
insert into apoturaev.hackaton_dataset2_stage8
select
ac.id,
cr.calltrackingid,
cr.calldate,
json_extract_scalar(json_parse(geo.part), '$.name') as house_name
from
apoturaev.hackaton_dataset2_stage1 cr
join
lst.announcement_change_v2 ac
on
ac.userid = cr.realtyuserid
and
ac.ptn_dadd = date 'DATE'
cross join
unnest(ac.geo_address) geo (part)
cross join
unnest(ac.phones) ph (phone)
where
ac.wasactive = 1
and
json_extract_scalar(json_parse(geo.part), '$.type') = 'house'
and
cr.calldate between date 'DATE' and date 'DATE' + interval '1' day
and
cr.phone = json_extract_scalar(json_parse(ph.phone), '$.number')
";
var parsed_query = query.Replace("DATE", dateString);
var request = new ExecuteQueryV1Request(parsed_query);
var response = await Client.ExecuteQueryV1(request);
if (response.LastError == null)
{
_logger.LogInformation($"Date {dateString} loaded");
}
day = day + TimeSpan.FromDays(1);
}
}
/// <summary>
/// Submits a statement to Presto for execution. The Presto client
/// executes queries on behalf of a user against a catalog and a schema.
/// </summary>
/// <param name="request">The query execution request.</param>
/// <returns>The resulting response object from the query.</returns>
public async Task <ExecuteQueryV1Response> ExecuteQueryV1(ExecuteQueryV1Request request)
{
// Check the required configuration items before running the query
if (!CheckConfiguration(out Exception Ex))
{
throw Ex;
}
// Track all of the incremental results as they are returned
List <QueryResultsV1> Results = new List <QueryResultsV1>();
// Choose the correct client to use for ssl errors
HttpClient LocalClient = (this.Configuration.IgnoreSslErrors) ? this.IgnoreSslErrorClient : this.NormalClient;
// Build the url path
Uri Path = this.BuildUri("/statement");
// Create a new request to post with the query
HttpRequestMessage Request = this.BuildRequest(Path, HttpMethod.Post, new StringContent(request.Query));
// Add all of the configured headers to the request
this.BuildQueryHeaders(ref Request, request.Options);
// Use the stopwatch to measure if we've exceeded the specified timeout
Stopwatch SW = new Stopwatch();
if (this.Configuration.ClientRequestTimeout > 0)
{
SW.Start();
}
// This is the original submission result, will contain the nextUri
// property to follow in order to get the results
HttpResponseMessage ResponseMessage = await this.MakeHttpRequest(LocalClient, Request);
// This doesn't really do anything but evaluate the headers right now
this.ProcessResponseHeaders(ResponseMessage);
string Content = await ResponseMessage.Content.ReadAsStringAsync();
// If parsing the submission response fails, return and exit
if (!QueryResultsV1.TryParse(Content, out QueryResultsV1 Response, out Exception ParseEx))
{
throw new PrestoException($"The query submission response could not be parsed.", Content, ParseEx);
}
public async Task TestExecuteStatement()
{
//ARRANGE
PrestoClientSessionConfig Config = new PrestoClientSessionConfig("hive", Table)
{
Host = "localhost",
Port = 8080
};
IPrestoClient Client = new PrestodbClient(Config);
ExecuteQueryV1Request Req = new ExecuteQueryV1Request("select * from tracklets limit 5;");
// ACT
ExecuteQueryV1Response Res = await Client.ExecuteQueryV1(Req);
// ASSERT
Assert.True(Res.QueryClosed == true);
}
public async Task TestExecuteStatementWhere()
{
//ARRANGE
PrestoClientSessionConfig Config = new PrestoClientSessionConfig("hive", Schema)
{
Host = "localhost",
Port = 8080
};
IPrestoClient Client = new PrestodbClient(Config);
ExecuteQueryV1Request Req = new ExecuteQueryV1Request("select id,length,objectclass from tracklets WHERE length > 1000 LIMIT 5;");
// ACT
ExecuteQueryV1Response Res = await Client.ExecuteQueryV1(Req);
// ASSERT
Assert.True(Res.QueryClosed == true);
}
public async Task CreateTable()
{
// ARRANGE
PrestoClientSessionConfig Config = new PrestoClientSessionConfig("hive", Schema)
{
Host = "localhost",
Port = 8080
};
IPrestoClient Client = new PrestodbClient(Config);
ExecuteQueryV1Request Req = new ExecuteQueryV1Request($"CREATE TABLE IF NOT EXISTS tracklets (id bigint, objectclass varchar, length double, trackdata array(varchar), platform varchar,spectrum varchar, timestamp bigint) WITH (format = 'AVRO', external_location = '{S3_Location}');");
// ACT
ExecuteQueryV1Response Res = await Client.ExecuteQueryV1(Req);
// ASSERT
Assert.True(Res.QueryClosed == true);
}
public async Task CreateSchema()
{
// ARRANGE
PrestoClientSessionConfig Config = new PrestoClientSessionConfig()
{
Host = "localhost",
Port = 8080
};
IPrestoClient Client = new PrestodbClient(Config);
ExecuteQueryV1Request Req = new ExecuteQueryV1Request($"CREATE SCHEMA IF NOT EXISTS hive.{Schema}");
// ACT
ExecuteQueryV1Response Res = await Client.ExecuteQueryV1(Req);
// ASSERT
Assert.True(Res.QueryClosed == true);
}
public async Task TestQueryResultDataToCsv()
{
//ARRANGE
PrestoClientSessionConfig Config = new PrestoClientSessionConfig("hive", Table)
{
Host = "localhost",
Port = 8080
};
IPrestoClient Client = new PrestodbClient(Config);
ExecuteQueryV1Request Req = new ExecuteQueryV1Request("select * from tracklets limit 5;");
// ACT
ExecuteQueryV1Response Res = await Client.ExecuteQueryV1(Req);
string Csv = String.Join("\n", Res.DataToCsv());
// ASSERT
Assert.True(Res.QueryClosed == true && !String.IsNullOrEmpty(Csv));
}
