in templates/aws-cloudfront-monitoring/source/lambda.d/shared_lib/python/metric_helper.py [0:0]
def construct_query_string(db_name, domain, start_time, end_time, metric, table_name):
# Dynamically build query string using partition
if metric == 'request':
query_string = 'SELECT count(timestamp) FROM "' + db_name + '"."' + table_name + '" WHERE '
query_string = assemble_query(start_time, end_time, query_string)
query_string += ' AND "cs-host" = \'' + domain + '\' AND timestamp <= ' + str(
format_date_time(end_time)) + ' AND timestamp > ' + str(
format_date_time(start_time)) + ';'
elif metric == 'requestOrigin':
query_string = 'SELECT count(timestamp) FROM "' + db_name + '"."' + table_name + '" WHERE '
query_string = assemble_query(start_time, end_time, query_string)
query_string += ' AND "cs-host" = \'' + domain + '\' AND timestamp <= ' + str(
format_date_time(end_time)) + ' AND timestamp > ' + str(
format_date_time(start_time)
) + ' AND "x-edge-response-result-type"=\'Miss\';'
elif metric == 'statusCode':
query_string = 'SELECT "sc-status", count(timestamp) FROM "' + db_name + '"."' + table_name + '" WHERE '
query_string = assemble_query(start_time, end_time, query_string)
query_string += ' AND "cs-host" = \'' + domain + '\' AND timestamp <= ' + str(
format_date_time(end_time)) + ' AND timestamp > ' + str(
format_date_time(start_time)) + ' GROUP BY "sc-status";'
elif metric == 'statusCodeOrigin':
query_string = 'SELECT "sc-status", count(timestamp) FROM "' + db_name + '"."' + table_name + '" WHERE '
query_string = assemble_query(start_time, end_time, query_string)
query_string += ' AND "cs-host" = \'' + domain + '\' AND timestamp <= ' + str(
format_date_time(end_time)
) + ' AND timestamp > ' + str(
format_date_time(start_time)
) + ' AND "x-edge-response-result-type"=\'Miss\' GROUP BY "sc-status";'
elif metric == 'bandwidth':
query_string = 'SELECT sum("sc-bytes") FROM "' + db_name + '"."' + table_name + '" WHERE '
query_string = assemble_query(start_time, end_time, query_string)
query_string += ' AND "cs-host" = \'' + domain + '\' AND timestamp <= ' + str(
format_date_time(end_time)) + ' AND timestamp > ' + str(
format_date_time(start_time)) + ';'
elif metric == 'bandwidthOrigin':
query_string = 'SELECT sum("sc-bytes") FROM "' + db_name + '"."' + table_name + '" WHERE '
query_string = assemble_query(start_time, end_time, query_string)
query_string += ' AND "cs-host" = \'' + domain + '\' AND timestamp <= ' + str(
format_date_time(end_time)) + ' AND timestamp > ' + str(
format_date_time(start_time)
) + ' AND "x-edge-response-result-type"=\'Miss\';'
elif metric == 'chr':
query_string = 'SELECT cast(A.hitCount as decimal(38,2)) * 100 / cast(B.all as decimal(38,2)) as CHR FROM '
molecule_query_string = '(SELECT count(timestamp) as hitCount FROM "' + db_name + '"."' + table_name + '" WHERE '
molecule_query_string = assemble_query(start_time, end_time,
molecule_query_string)
molecule_query_string += ' AND "cs-host" = \'' + domain + '\' AND timestamp <= ' + str(
format_date_time(end_time)
) + ' AND timestamp > ' + str(
format_date_time(start_time)
) + ' AND ("x-edge-response-result-type" = \'Hit\' OR "x-edge-response-result-type" = \'RefreshHit\')) A, '
denom_query_string = '(SELECT count(timestamp) as all FROM "' + db_name + '"."' + table_name + '" WHERE '
denom_query_string = assemble_query(start_time, end_time,
denom_query_string)
denom_query_string += ' AND "cs-host" = \'' + domain + '\' AND timestamp <= ' + str(
format_date_time(end_time)
) + ' AND timestamp > ' + str(
format_date_time(start_time)
) + ' AND "x-edge-response-result-type" <> \'LimitExceeded\' AND "x-edge-response-result-type" <> \'CapacityExceeded\' AND "x-edge-response-result-type" <> \'Error\' AND "x-edge-response-result-type" <> \'Redirect\') B;'
query_string = query_string + molecule_query_string + denom_query_string
elif metric == 'chrBandWith':
query_string = 'SELECT cast(A.hitCount as decimal(38,2)) * 100 / cast(B.all as decimal(38,2)) as CHR FROM '
molecule_query_string = '(SELECT sum("cs-bytes") as hitCount FROM "' + db_name + '"."' + table_name + '" WHERE '
molecule_query_string = assemble_query(start_time, end_time,
molecule_query_string)
molecule_query_string += ' AND "cs-host" = \'' + domain + '\' AND timestamp <= ' + str(
format_date_time(end_time)
) + ' AND timestamp > ' + str(
format_date_time(start_time)
) + ' AND ("x-edge-response-result-type" = \'Hit\' OR "x-edge-response-result-type" = \'RefreshHit\')) A, '
denom_query_string = '(SELECT sum("cs-bytes") as all FROM "' + db_name + '"."' + table_name + '" WHERE '
denom_query_string = assemble_query(start_time, end_time,
denom_query_string)
denom_query_string += ' AND "cs-host" = \'' + domain + '\' AND timestamp <= ' + str(
format_date_time(end_time)
) + ' AND timestamp > ' + str(
format_date_time(start_time)
) + ' AND "x-edge-response-result-type" <> \'LimitExceeded\' AND "x-edge-response-result-type" <> \'CapacityExceeded\' AND "x-edge-response-result-type" <> \'Error\' AND "x-edge-response-result-type" <> \'Redirect\') B;'
query_string = query_string + molecule_query_string + denom_query_string
else:
raise Exception('[schedule_athena_query] Invalid metric ' + metric)
return query_string