public async Task TestGetQuery()
{
//ARRANGE
PrestoClientSessionConfig Config = new PrestoClientSessionConfig("hive", Table)
{
Host = "localhost",
Port = 8080
};
IPrestoClient Client = new PrestodbClient(Config);
// ACT
ListQueriesV1Response Res = await Client.GetQueries();
List <GetQueryV1Response> Info = new List <GetQueryV1Response>();
foreach (BasicQueryInfo Item in Res.QueryInfo)
{
GetQueryV1Response QRes = await Client.GetQuery(Item.QueryId);
Info.Add(QRes);
}
// ASSERT
Assert.True(Res != null && Info.All(x => x.DeserializationSucceeded == true));
}
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);
}
}
public async Task TestListQueries()
{
//ARRANGE
PrestoClientSessionConfig Config = new PrestoClientSessionConfig("hive", Table)
{
Host = "localhost",
Port = 8080
};
IPrestoClient Client = new PrestodbClient(Config);
// ACT
ListQueriesV1Response Res = await Client.GetQueries();
// ASSERT
Assert.True(Res != null && Res.DeserializationSucceeded);
}
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));
}
