def queryKusto(query,client, database):
try:
response = client.execute_query(database, query)
logging.info("Query reponse: %s" % str(response))
dataframe_from_result_table(response.primary_results[0])
except Exception as e:
logging.error("Could not process query:%s"%e)
def query_kusto(query, db, client):
"""Query a kusto DB given client object, returns pandas dataframe"""
dataframe = pd.DataFrame([])
logging.info('Retry is set to 3 times.')
for i in range(4):
if i > 0:
logging.info('Retry {}'.format(i))
try:
# Execute query
response = client.execute(db, query)
# Convert to pandas dataframe
res = response.primary_results[0]
if res:
dataframe = dataframe_from_result_table(res)
if dataframe.empty:
time.sleep(10)
continue
return dataframe
except Exception as exp:
logging.error('Exception occured: {}'.format(exp))
# wait 10 seconds, then retry
time.sleep(10)
continue
return dataframe
async def test_sanity_data_frame(self):
with aioresponses() as aioresponses_mock:
self._mock_query(aioresponses_mock)
client = KustoClient(self.HOST)
response = await client.execute_query("PythonTest", "Deft")
data_frame = dataframe_from_result_table(response.primary_results[0])
self._assert_sanity_data_frame_response(data_frame)
def test_sanity_data_frame(self, mock_post, method):
"""Tests KustoResponse to pandas.DataFrame."""
client = KustoClient(self.HOST)
response = method.__call__(client, "PythonTest", "Deft")
data_frame = dataframe_from_result_table(
get_response_first_primary_result(response))
self._assert_sanity_data_frame_response(data_frame)
def failover_kusto_test(client, benchmark_run):
start_time_variable = CLperfDB.benchmark_start_time(benchmark_run)
end_time_variable = CLperfDB.benchmark_end_time(benchmark_run)
if start_time_variable is None or end_time_variable is None:
return (-1, -1)
query_ = re.sub("start_time_variable", start_time_variable,
hadr_replica_states_query)
query_ = re.sub("end_time_variable", end_time_variable, query_)
query_ = re.sub("server_name_variable",
benchmark_run.get_logical_server_name(), query_)
print("Querying kusto (failover info)")
print("Query text \n " + query_)
try:
failover_response = client.execute("sqlazure1", query_)
failover_df = dataframe_from_result_table(
failover_response.primary_results[0])
redo_stats_avg = failover_df['redo_stats_avg'].max()
failover_time_stats_max = failover_df['failover_time_stats_max'].max()
a = (redo_stats_avg, failover_time_stats_max)
return a
except:
return (-2, -2)
def backup_kusto_test(client, benchmark_run):
CLperfDB.benchmark_start_time(benchmark_run)
CLperfDB.benchmark_end_time(benchmark_run)
print(benchmark_run.start_time)
print(benchmark_run.end_time)
if benchmark_run.start_time is None or benchmark_run.end_time is None:
return -1
query_ = re.sub("start_time_variable", benchmark_run.start_time,
backup_query)
query_ = re.sub("end_time_variable", benchmark_run.end_time, query_)
query_ = re.sub("server_name_variable",
benchmark_run.get_logical_server_name(), query_)
print("Querying kusto (backup info)")
print("Query text \n " + query_)
try:
backup_response = client.execute("sqlazure1", query_)
backup_df = dataframe_from_result_table(
backup_response.primary_results[0])
return backup_df['backup_duration_min'].max()
except:
return -2
def get_page_views_10(client, area):
kusto_query_10 = open(path_mbr.query_path_product_10,
'r').read().format(area)
kusto_database_10 = 'DevRelWorkArea'
result_10 = query_kusto(client, kusto_database_10, kusto_query_10)
df_10 = dataframe_from_result_table(result_10.primary_results[0])
return df_10
def get_page_views_13(client, area):
kusto_query_13 = open(path_mbr.query_path_13_learn,
'r').read().format(area)
kusto_database_13 = 'DevRelWorkArea'
result_13 = query_kusto(client, kusto_database_13, kusto_query_13)
df_13 = dataframe_from_result_table(result_13.primary_results[0])
return df_13
def export(cluster, database, query_path, account, container, key, folder,
prefix, dest, gzip):
"""
Run QUERY_PATH on CLUSTER/DATABASE, export to blob ACCOUNT/CONTAINER/FOLDER
and download to local file DEST.
"""
logger = logging.getLogger(__name__)
with open(query_path, "r") as query_file:
query = query_file.read()
storage_uri = f"https://{account}.blob.core.windows.net/{container}/{folder}"
command = render_export_template(query, storage_uri, key, prefix, gzip)
client = get_kusto_client(cluster, database)
logger.info("Exporting query %s to %s...", query_path, storage_uri)
try:
res = client.execute_mgmt(database, command)
data = dataframe_from_result_table(res.primary_results[0])
storage_path = data["Path"].values[0]
logger.info("Successfully exported query %s to %s", query_path,
storage_path)
logger.info("Downloading file from %s to %s", storage_path, dest)
get_blob(storage_path, key, dest)
logger.info("Finished downloading file from %s to %s", storage_path,
dest)
except KustoClientError as e:
logger.error("KustoClientError", exc_info=True)
def get_distinct_obsid_version():
adx_query = """metrics
| where timestamp > ago(30d)
| distinct obsId, ['version']"""
query_response = client.execute(db, adx_query)
query_response_dataframe = (dataframe_from_result_table(
query_response.primary_results[0])).fillna(0)
return query_response_dataframe
def get_page_views_7(client, area):
kusto_query_7 = "database('DevRelWorkArea').['kpi-visitor-by-visitor-type'] " \
"| where Area == '" + area + "' " \
"| where Month >= startofmonth(now(),-14) and Month < startofmonth(now()) " \
"| summarize val = avg(mtdengaged_2days) by Month " \
"| order by Month desc "
kusto_database_7 = 'DevRelWorkArea'
result_7 = query_kusto(client, kusto_database_7, kusto_query_7)
df_7 = dataframe_from_result_table(result_7.primary_results[0])
return df_7
def get_page_views_5(client, area):
kusto_query_5 = "database('DevRelWorkArea').['kpi-learn-module-completion'] " \
"| where Area == '" + area + "' " \
"| where Month >= startofmonth(now(),-14) and Month < startofmonth(now()) " \
"| summarize val = avg(module_completion) by Month " \
"| order by Month desc "
kusto_database_5 = 'DevRelWorkArea'
result_5 = query_kusto(client, kusto_database_5, kusto_query_5)
df_5 = dataframe_from_result_table(result_5.primary_results[0])
return df_5
def get_page_views_4(client, area):
kusto_query_4 = "database('DevRelWorkArea').['kpi-pageview-visit'] " \
"| where Area == '" + area + "' " \
"| where Month >= startofmonth(now(),-14) and Month < startofmonth(now()) " \
"| summarize val = avg(visits_bounced/todouble(visits)) by Month " \
"| order by Month desc "
kusto_database_4 = 'DevRelWorkArea'
result_4 = query_kusto(client, kusto_database_4, kusto_query_4)
df_4 = dataframe_from_result_table(result_4.primary_results[0])
return df_4
def get_page_views_3(client, area):
kusto_query_3 = "database('DevRelWorkArea').['kpi-referrer-pageview-percentage'] " \
"| where Area == '" + area + "' " \
"| where Month >= startofmonth(now(),-14) and Month < startofmonth(now()) " \
"| summarize val = avg(rate_organic) by Month " \
"| order by Month desc "
kusto_database_3 = 'DevRelWorkArea'
result_3 = query_kusto(client, kusto_database_3, kusto_query_3)
df_3 = dataframe_from_result_table(result_3.primary_results[0])
return df_3
def get_page_views_2(client, area):
kusto_query_2 = "database('DevRelWorkArea').['kpi-pageview-visit'] " \
"| where Area == '" + area + "' " \
"| where Month >= startofmonth(now(),-14) and Month < startofmonth(now()) " \
"| summarize val = avg(pageviews) by Month " \
"| order by Month desc "
kusto_database_2 = 'DevRelWorkArea'
result_2 = query_kusto(client, kusto_database_2, kusto_query_2)
df_2 = dataframe_from_result_table(result_2.primary_results[0])
return df_2
def get_page_views_14(client, area):
kusto_query_14 = """database('DevRelWorkArea').['kpi-visitor-voted']
| where Area == '{}'
| where Month >= startofmonth(now(),-14) and Month < startofmonth(now())
| summarize val = avg(mtdCSAT) by Month
| order by Month desc""".format(area)
kusto_database_14 = 'DevRelWorkArea'
result_14 = query_kusto(client, kusto_database_14, kusto_query_14)
df_14 = dataframe_from_result_table(result_14.primary_results[0])
return df_14
def get_page_views_8(client, area):
kusto_query_8 = "database('DevRelWorkArea').['kpi-return-visitor'] " \
"| where Area == '" + area + "' " \
"| where Month >= startofmonth(now(),-14) and Month < startofmonth(now()) " \
"| summarize val = avg(visitors_returned_rate) by Month " \
"| order by Month desc "
kusto_database_8 = 'DevRelWorkArea'
result_8 = query_kusto(client, kusto_database_8, kusto_query_8)
df_8 = dataframe_from_result_table(result_8.primary_results[0])
return df_8
def get_observability_metrics(obsId, version):
adx_query = """set truncationmaxsize=67108864;
set truncationmaxrecords=500000;
metrics
| where obsId == '%s' and version== '%s';
""" % (obsId, version)
query_response = client.execute(db, adx_query)
query_response_dataframe = (dataframe_from_result_table(
query_response.primary_results[0]))
return query_response_dataframe
def get_page_views_10(client, area):
kusto_query_10 = "database('DevRelWorkArea').['kpi-viewed-article'] " \
"| where Locale == 'en-us' "\
"| where Area == '" + area + "' " \
"| where Month >= startofmonth(now(),-14) and Month < startofmonth(now()) " \
"| summarize val = avg(MTD) by Month " \
"| order by Month desc "
kusto_database_10 = 'DevRelWorkArea'
result_10 = query_kusto(client, kusto_database_10, kusto_query_10)
df_10 = dataframe_from_result_table(result_10.primary_results[0])
return df_10
def get_query_data_product_docs(client, area, database):
kusto_query_1 = open(path_mbr.query_path_product_1,
'r').read().format(area)
result_1 = query_kusto(client, database, kusto_query_1)
df_1 = dataframe_from_result_table(result_1.primary_results[0])
kusto_query_2 = open(path_mbr.query_path_product_2,
'r').read().format(area)
result_2 = query_kusto(client, database, kusto_query_2)
df_2 = dataframe_from_result_table(result_2.primary_results[0])
kusto_query_3 = open(path_mbr.query_path_product_3,
'r').read().format(area)
result_3 = query_kusto(client, database, kusto_query_3)
df_3 = dataframe_from_result_table(result_3.primary_results[0])
kusto_query_4 = open(path_mbr.query_path_product_4,
'r').read().format(area)
result_4 = query_kusto(client, database, kusto_query_4)
df_4 = dataframe_from_result_table(result_4.primary_results[0])
kusto_query_5 = open(path_mbr.query_path_product_5,
'r').read().format(area)
result_5 = query_kusto(client, database, kusto_query_5)
df_5 = dataframe_from_result_table(result_5.primary_results[0])
kusto_query_6 = open(path_mbr.query_path_product_6,
'r').read().format(area)
result_6 = query_kusto(client, database, kusto_query_6)
df_6 = dataframe_from_result_table(result_6.primary_results[0])
kusto_query_7 = open(path_mbr.query_path_product_7, 'r').read()
result_7 = query_kusto(client, database, kusto_query_7)
df_7 = dataframe_from_result_table(result_7.primary_results[0])
kusto_query_8 = open(path_mbr.query_path_product_8,
'r').read().format(area)
result_8 = query_kusto(client, database, kusto_query_8)
df_8 = dataframe_from_result_table(result_8.primary_results[0])
kusto_query_9 = open(path_mbr.query_path_product_9,
'r').read().format(area, area)
result_9 = query_kusto(client, database, kusto_query_9)
df_9 = dataframe_from_result_table(result_9.primary_results[0])
kusto_query_10 = open(path_mbr.query_path_product_10,
'r').read().format(area)
result_10 = query_kusto(client, database, kusto_query_10)
df_10 = dataframe_from_result_table(result_10.primary_results[0])
return df_1, df_2, df_3, df_4, df_5, df_6, df_7, df_8, df_9, df_10
def get_query_data_product_learn(client, area, database):
kusto_query_1 = open(path_mbr.query_path_product_1,
'r').read().format(area)
result_1 = query_kusto(client, database, kusto_query_1)
df_1 = dataframe_from_result_table(result_1.primary_results[0])
kusto_query_2 = open(path_mbr.query_path_product_2_learn,
'r').read().format(area)
result_2 = query_kusto(client, database, kusto_query_2)
df_2 = dataframe_from_result_table(result_2.primary_results[0])
kusto_query_3 = open(path_mbr.query_path_product_3_learn,
'r').read().format(area)
result_3 = query_kusto(client, database, kusto_query_3)
df_3 = dataframe_from_result_table(result_3.primary_results[0])
kusto_query_4 = open(path_mbr.query_path_product_4_learn,
'r').read().format(area)
result_4 = query_kusto(client, database, kusto_query_4)
df_4 = dataframe_from_result_table(result_4.primary_results[0])
kusto_query_5 = open(path_mbr.query_path_product_5_learn,
'r').read().format(area)
result_5 = query_kusto(client, database, kusto_query_5)
df_5 = dataframe_from_result_table(result_5.primary_results[0])
return df_1, df_2, df_3, df_4, df_5
def get_page_views_1(client):
kusto_query_1 = """
cluster('cgadataout').database('DevRelWorkArea').vwTopicMetadata()
| where Site =='docs.microsoft.com'
| where IsLive
| where Locale == "en-us"
| extend GitHubUrl = coalesce(OriginalContentGitUrl, GitUrl)
| where GitHubUrl startswith "https://github.com"
| extend OriginalContentRepo = tolower(extract("https://github.com/([^/]+/[^/]+)/",1,GitHubUrl))
| extend OriginalContentFile = tolower(extract("https://github.com/[^/]+/[^/]+/blob/[^/]+/(.+)",1,GitHubUrl))
| extend OriginalContentBranch = extract("https://github.com/[^/]+/[^/]+/blob/([^/]+)/",1,GitHubUrl)
| distinct OriginalContentRepo, OriginalContentBranch
"""
kusto_database_1 = 'DevRelWorkArea'
result_1 = query_kusto(client, kusto_database_1, kusto_query_1)
df_1 = dataframe_from_result_table(result_1.primary_results[0])
return df_1
def check_suitability_of_app(obsId, version):
adx_query = """let uuidMetrics = metrics
| where obsId == '%s' and version== '%s';
let ServiceMetrics = uuidMetrics
| where ['tags']['src.entry_point.resource'] == 'true';
let CallerResponseMetrics = uuidMetrics
| where ['tags']['action'] == 'respond' and ['tags']['connector_name'] == 'ballerina/http/Caller';
let RequestsTotal = ServiceMetrics
| where metricName == 'requests_total'
| project value, timestamp, timeWindow=(toint(['tags']['timeWindow']))
| summarize requests_total=sum(value) by timestamp, timeWindow;
let ResponseTimeTotal = ServiceMetrics
| where metricName == 'response_time_nanoseconds_total'
| summarize response_time_total=(sum(value)) by timestamp;
let AvgResponseTime = ResponseTimeTotal
| join kind=leftouter (RequestsTotal) on timestamp
| project timestamp, avg_response_time=((response_time_total / requests_total)/(1000*1000));
let Throughput = RequestsTotal
| project timestamp, throughput=((requests_total / timeWindow)*1000);
let httpErrors = CallerResponseMetrics
| summarize http_error_count=countif(isnotnull(['tags']['http.status_code_group']) and ['tags']['http.status_code_group'] != '2xx' and ['tags']['http.status_code_group'] != '1xx') by timestamp;
let ballerinaErrors = ServiceMetrics
| summarize ballerina_error_count=countif(['tags']['error'] == 'true') by timestamp;
uuidMetrics
| where metricName in ('requests_total', 'response_time_nanoseconds_total')
| distinct timestamp
| join kind=leftouter (AvgResponseTime) on timestamp
| project-away timestamp1
| join kind=leftouter (Throughput) on timestamp
| project-away timestamp1
| join kind=leftouter (httpErrors) on timestamp
| project-away timestamp1
| join kind=leftouter (ballerinaErrors) on timestamp
| project-away timestamp1
| order by timestamp asc;
""" % (obsId, version)
query_response = client.execute(db, adx_query)
query_response_dataframe = (dataframe_from_result_table(
query_response.primary_results[0]))
return query_response_dataframe
def execute_query(client, db_name, query):
query_result_df = None
try:
properties = ClientRequestProperties()
properties.set_option(properties.results_defer_partial_query_failures_option_name, True)
properties.set_option(properties.request_timeout_option_name, timedelta(seconds=1 * 30))
response = client.execute(db_name, query, properties=properties)
query_result_df = dataframe_from_result_table(response.primary_results[0])
except KustoServiceError as error:
print("2. Error:", error)
print("2. Is semantic error:", error.is_semantic_error())
print("2. Has partial results:", error.has_partial_results())
print("2. Result size:", len(error.get_partial_results()))
print("3. Response error count: ", response.errors_count)
print("3. Exceptions:", response.get_exceptions())
print("3. Result size:", len(response.primary_results))
print("4. Error:", error)
print("4. Is semantic error:", error.is_semantic_error())
print("4. Has partial results:", error.has_partial_results())
finally:
return query_result_df
def get_query_data_content_operations(client, database):
kusto_query_1 = open(path_mbr.query_path_content_operations_1, 'r').read()
result_1 = query_kusto(client, database, kusto_query_1)
df_1 = dataframe_from_result_table(result_1.primary_results[0])
kusto_query_2 = open(path_mbr.query_path_content_operations_2, 'r').read()
result_2 = query_kusto(client, database, kusto_query_2)
df_2 = dataframe_from_result_table(result_2.primary_results[0])
kusto_query_3 = open(path_mbr.query_path_content_operations_3, 'r').read()
result_3 = query_kusto(client, database, kusto_query_3)
df_3 = dataframe_from_result_table(result_3.primary_results[0])
kusto_query_4 = open(path_mbr.query_path_content_operations_4, 'r').read()
result_4 = query_kusto(client, database, kusto_query_4)
df_4 = dataframe_from_result_table(result_4.primary_results[0])
kusto_query_5 = open(path_mbr.query_path_content_operations_5, 'r').read()
result_5 = query_kusto(client, database, kusto_query_5)
df_5 = dataframe_from_result_table(result_5.primary_results[0])
kusto_query_6 = open(path_mbr.query_path_content_operations_6, 'r').read()
result_6 = query_kusto(client, database, kusto_query_6)
df_6 = dataframe_from_result_table(result_6.primary_results[0])
kusto_query_8 = open(path_mbr.query_path_content_operations_8, 'r').read()
result_8 = query_kusto(client, database, kusto_query_8)
df_8 = dataframe_from_result_table(result_8.primary_results[0])
return df_1, df_2, df_3, df_4, df_5, df_6, df_8
def test_dataframe_from_result_table(self):
"""Test conversion of KustoResultTable to pandas.DataFrame, including fixes for certain column types"""
with open(
os.path.join(os.path.dirname(__file__), "input",
"dataframe.json"), "r") as response_file:
data = response_file.read()
response = KustoResponseDataSetV2(json.loads(data))
df = dataframe_from_result_table(response.primary_results[0])
assert df.iloc[0].RecordName == "now"
assert type(
df.iloc[0].RecordTime) is pandas._libs.tslibs.timestamps.Timestamp
assert all(
getattr(df.iloc[0].RecordTime, k) == v for k, v in {
"year": 2021,
"month": 12,
"day": 22,
"hour": 11,
"minute": 43,
"second": 00
}.items())
assert type(df.iloc[0].RecordBool) is numpy.bool_
assert df.iloc[0].RecordBool == True
assert type(df.iloc[0].RecordInt) is numpy.int32
assert df.iloc[0].RecordInt == 5678
assert type(df.iloc[0].RecordReal) is numpy.float64
assert df.iloc[0].RecordReal == 3.14159
# Kusto datetime(0000-01-01T00:00:00Z), which Pandas can't represent.
assert df.iloc[1].RecordName == "earliest datetime"
assert type(
df.iloc[1].RecordTime) is pandas._libs.tslibs.nattype.NaTType
assert pandas.isnull(df.iloc[1].RecordReal)
# Kusto datetime(9999-12-31T23:59:59Z), which Pandas can't represent.
assert df.iloc[2].RecordName == "latest datetime"
assert type(
df.iloc[2].RecordTime) is pandas._libs.tslibs.nattype.NaTType
assert type(df.iloc[2].RecordReal) is numpy.float64
assert df.iloc[2].RecordReal == numpy.inf
# Pandas earliest datetime
assert df.iloc[3].RecordName == "earliest pandas datetime"
assert type(
df.iloc[3].RecordTime) is pandas._libs.tslibs.timestamps.Timestamp
assert type(df.iloc[3].RecordReal) is numpy.float64
assert df.iloc[3].RecordReal == -numpy.inf
# Pandas latest datetime
assert df.iloc[4].RecordName == "latest pandas datetime"
assert type(
df.iloc[4].RecordTime) is pandas._libs.tslibs.timestamps.Timestamp
# Kusto 600000000 ticks timedelta
assert df.iloc[5].RecordName == "timedelta ticks"
assert type(
df.iloc[5].RecordTime) is pandas._libs.tslibs.timestamps.Timestamp
assert type(df.iloc[5].RecordOffset
) is pandas._libs.tslibs.timestamps.Timedelta
assert df.iloc[5].RecordOffset == pandas.to_timedelta("00:01:00")
# Kusto timedelta(1.01:01:01.0) ==
assert df.iloc[6].RecordName == "timedelta string"
assert type(
df.iloc[6].RecordTime) is pandas._libs.tslibs.timestamps.Timestamp
assert type(df.iloc[6].RecordOffset
) is pandas._libs.tslibs.timestamps.Timedelta
assert df.iloc[6].RecordOffset == pandas.to_timedelta(
"1 days 01:01:01")
def get_observability_metrics():
adx_query = """let uuidMetrics = metrics
| where obsId == '%s' and version== '%s';
let inMetrics = uuidMetrics
| where ['tags']['src.entry_point.resource'] == 'true';
let outMetrics = uuidMetrics
| where ['tags']['connector_name'] == 'ballerina/http/Client';
let inRequestsTotal = inMetrics
| where metricName == 'requests_total'
| project value, timestamp, timeWindow=(toint(['tags']['timeWindow']))
| summarize in_requests_total=sum(value) by timestamp, timeWindow;
let inResponseTimeTotal = inMetrics
| where metricName == 'response_time_nanoseconds_total'
| summarize in_response_time_total=(sum(value)) by timestamp;
let inIPR = inMetrics
| where metricName == 'inprogress_requests'
| summarize in_ipr=sum(value) by timestamp;
let inAvgResponseTime = inResponseTimeTotal
| join kind=leftouter (inRequestsTotal) on timestamp
| project timestamp, in_avg_response_time=((in_response_time_total / in_requests_total)/(1000*1000));
let inThroughput = inRequestsTotal
| project timestamp, in_throughput=((in_requests_total / timeWindow)*1000);
let outRequestsTotal = outMetrics
| where metricName == 'requests_total'
| summarize out_requests_total=sum(value) by timestamp, timeWindow=(toint(['tags']['timeWindow'])), position=tostring(['tags']['src.position']);
let outResponseTimeTotal = outMetrics
| where metricName == 'response_time_nanoseconds_total'
| summarize out_response_time_total=(sum(value)) by timestamp, position=tostring(['tags']['src.position']);
let outIPR = outMetrics
| where metricName == 'inprogress_requests'
| summarize out_ipr=sum(value) by timestamp, position=strcat('out_inprogress_requests_', tostring(['tags']['src.position']))
| evaluate pivot(position, sum(out_ipr));
let outAvgResponseTime = outResponseTimeTotal
| join kind=leftouter (outRequestsTotal) on timestamp, position
| project timestamp, out_avg_response_time=((out_response_time_total / out_requests_total)/(1000*1000)), position=strcat('out_avg_response_time_', position)
| evaluate pivot(position, sum(out_avg_response_time));
let outThroughput = outRequestsTotal
| project timestamp, out_throughput=((out_requests_total / timeWindow)*1000), position=strcat('out_throughput_', position)
| evaluate pivot(position, sum(out_throughput));
uuidMetrics
| where metricName in ('inprogress_requests', 'requests_total', 'response_time_nanoseconds_total')
| distinct timestamp
| join kind=leftouter (inAvgResponseTime) on timestamp
| project-away timestamp1
| join kind=leftouter (inThroughput) on timestamp
| project-away timestamp1
| join kind=leftouter (inIPR) on timestamp
| project-away timestamp1
| join kind=leftouter (outAvgResponseTime) on timestamp
| project-away timestamp1
| join kind=leftouter (outThroughput) on timestamp
| project-away timestamp1
| join kind=leftouter (outIPR) on timestamp
| project-away timestamp1
| order by timestamp asc;
""" % (UUID.split('/')[0], UUID.split('/')[1])
query_response = client.execute(db, adx_query)
query_response_dataframe = (dataframe_from_result_table(
query_response.primary_results[0])).fillna(0)
return query_response_dataframe
def get_page_views_5(client, area):
kusto_query_5 = open(path_mbr.query_path_product_5_learn,'r').read().format(area)
kusto_database_5 = 'DevRelWorkArea'
result_5 = query_kusto(client, kusto_database_5, kusto_query_5)
df_5 = dataframe_from_result_table(result_5.primary_results[0])
return df_5
from azure.kusto.data import KustoClient, KustoConnectionStringBuilder
from azure.kusto.data.exceptions import KustoServiceError
from azure.kusto.data.helpers import dataframe_from_result_table
import pandas as pd
AAD_TENANT_ID = "72f988bf-86f1-41af-91ab-2d7cd011db47"
KUSTO_CLUSTER = "https://help.kusto.windows.net/"
KUSTO_DATABASE = "Samples"
KCSB = KustoConnectionStringBuilder.with_aad_device_authentication(
KUSTO_CLUSTER)
KCSB.authority_id = AAD_TENANT_ID
KUSTO_CLIENT = KustoClient(KCSB)
KUSTO_QUERY = "StormEvents | project StartTime,State, EventType| sort by StartTime desc | take 10"
try:
RESPONSE = KUSTO_CLIENT.execute(KUSTO_DATABASE, KUSTO_QUERY)
except KustoServiceError as error:
print("Error : ", error)
#print(RESPONSE.primary_results[0])
df = dataframe_from_result_table(RESPONSE.primary_results[0])
jsonData = df.to_json()
print(jsonData)
response = client.execute(db, query)
# iterating over rows is possible
for row in response.primary_results[0]:
# printing specific columns by index
print("value at 0 {}".format(row[0]))
print("\n")
# printing specific columns by name
print("EventType:{}".format(row["EventType"]))
# tables are serializeable, so:
with open("results.json", "w+") as f:
f.write(str(response.primary_results[0]))
# we also support dataframes:
dataframe = dataframe_from_result_table(response.primary_results[0])
print(dataframe)
##################
### EXCEPTIONS ###
##################
# Query is too big to be executed
query = "StormEvents"
try:
response = client.execute(db, query)
except KustoServiceError as error:
print("2. Error:", error)
print("2. Is semantic error:", error.is_semantic_error())
print("2. Has partial results:", error.has_partial_results())