def refresh_intent_handler(intent_request, session_attributes):
athena = boto3.client('athena')
session_attributes['lastIntent'] = None
# Build and execute query
logger.debug('<<BIBot>> Athena Query String = ' + REFRESH_QUERY)
st_values = []
response = helpers.execute_athena_query(REFRESH_QUERY)
logger.debug('<<BIBot>> query response = ' + json.dumps(response))
while len(response['ResultSet']['Rows']) > 0:
for item in response['ResultSet']['Rows']:
st_values.append({'value': item['Data'][0]['VarCharValue']})
logger.debug('<<BIBot>> appending: ' +
item['Data'][0]['VarCharValue'])
try:
next_token = response['NextToken']
response = athena.get_query_results(
QueryExecutionId=query_execution_id,
NextToken=next_token,
MaxResults=100)
logger.debug('<<BIBot>> additional query response = ' +
json.dumps(response))
except KeyError:
break
logger.debug('<<BIBot>> "st_values = ' + pprint.pformat(st_values))
lex_models = boto3.client('lex-models')
response = lex_models.get_slot_type(name=REFRESH_SLOT, version='$LATEST')
logger.debug('<<BIBot>> "boto3 version = ' + boto3.__version__)
logger.debug('<<BIBot>> "Lex slot event_name = ' +
pprint.pformat(response, indent=4))
logger.debug('<<BIBot>> "Lex slot event_name checksum = ' +
response['checksum'])
logger.debug('<<BIBot>> "Lex slot event_name valueSelectionStrategy = ' +
response['valueSelectionStrategy'])
try:
logger.debug('<<BIBot>> "st_values = ' + pprint.pformat(st_values))
st_checksum = response['checksum']
response = lex_models.put_slot_type(
name=response['name'],
description=response['description'],
enumerationValues=st_values,
checksum=response['checksum'],
valueSelectionStrategy=response['valueSelectionStrategy'])
except KeyError:
pass
response = lex_models.get_intent(name=REFRESH_INTENT, version='$LATEST')
logger.debug('<<BIBot>> Lex get-intent = ' +
pprint.pformat(response, indent=4))
logger.debug('<<BIBot>> Lex get-intent keys = ' +
pprint.pformat(response.keys()))
response = lex_models.put_intent(
name=response['name'],
description=response['description'],
slots=response['slots'],
sampleUtterances=response['sampleUtterances'],
conclusionStatement=response['conclusionStatement'],
fulfillmentActivity=response['fulfillmentActivity'],
checksum=response['checksum'])
response = lex_models.get_bot(name=REFRESH_BOT, versionOrAlias='$LATEST')
logger.debug('<<BIBot>> Lex bot = ' + pprint.pformat(response, indent=4))
response = lex_models.put_bot(
name=REFRESH_BOT,
description='none',
intents=response['intents'],
clarificationPrompt=response['clarificationPrompt'],
abortStatement=response['abortStatement'],
idleSessionTTLInSeconds=response['idleSessionTTLInSeconds'],
voiceId=response['voiceId'],
processBehavior='SAVE',
locale=response['locale'],
checksum=response['checksum'],
childDirected=response['childDirected'])
logger.debug('<<BIBot>> Lex put bot = ' +
pprint.pformat(response, indent=4))
response_string = "I've refreshed the events dimension from the database. Please rebuild me."
return helpers.close(session_attributes, 'Fulfilled', {
'contentType': 'PlainText',
'content': response_string
})
def count_intent_handler(intent_request, session_attributes):
method_start = time.perf_counter()
logger.debug('<<BIBot>> count_intent_handler: intent_request = ' +
json.dumps(intent_request))
logger.debug('<<BIBot>> count_intent_handler: session_attributes = ' +
json.dumps(session_attributes))
session_attributes['greetingCount'] = '1'
session_attributes['resetCount'] = '0'
session_attributes['finishedCount'] = '0'
session_attributes['lastIntent'] = 'Count_Intent'
# # Retrieve slot values from the current request
# slot_values = session_attributes.get('slot_values')
#
# try:
# slot_values = helpers.get_slot_values(slot_values, intent_request)
# except bibot.SlotError as err:
# return helpers.close(session_attributes, 'Fulfilled', {'contentType': 'PlainText','content': str(err)})
#
# logger.debug('<<BIBot>> "count_intent_handler(): slot_values: %s', slot_values)
#
# # Retrieve "remembered" slot values from session attributes
# slot_values = helpers.get_remembered_slot_values(slot_values, session_attributes)
# logger.debug('<<BIBot>> "count_intent_handler(): slot_values after get_remembered_slot_values: %s', slot_values)
# # Remember updated slot values
# helpers.remember_slot_values(slot_values, session_attributes)
#
# # build and execute query
# select_clause = COUNT_SELECT
# where_clause = COUNT_JOIN
# for dimension in bibot.DIMENSIONS:
# slot_key = bibot.DIMENSIONS.get(dimension).get('slot')
# if slot_values[slot_key] is not None:
# where_clause += bibot.DIMENSIONS.get(dimension).get('column')
query_string = "Select supplier_name from contracttable where contract_id = '3210004304'"
logger.debug('the query string is = ' + query_string)
response = helpers.execute_athena_query(query_string)
result = response['ResultSet']['Rows'][1]['Data'][0]
if result:
count = result['VarCharValue']
else:
count = 0
logger.debug('<<BIBot>> "Count value is: %s' % count)
# build response string
if count == 0:
response_string = 'There were no {}'.format(COUNT_PHRASE)
else:
response_string = 'There were {} {}'.format(count, COUNT_PHRASE)
# add the English versions of the WHERE clauses
for dimension in bibot.DIMENSIONS:
slot_key = bibot.DIMENSIONS[dimension].get('slot')
logger.debug('<<BIBot>> pre top5_formatter[%s] = %s', slot_key,
slot_values.get(slot_key))
if slot_values.get(slot_key) is not None:
# the DIMENSION_FORMATTERS perform a post-process functions and then format the output
# Example: {... 'venue_state': {'format': ' in the state of {}', 'function': get_state_name}, ...}
if userexits.DIMENSION_FORMATTERS.get(slot_key) is not None:
output_text = userexits.DIMENSION_FORMATTERS[slot_key][
'function'](slot_values.get(slot_key))
response_string += ' ' + userexits.DIMENSION_FORMATTERS[
slot_key]['format'].lower().format(output_text)
logger.debug('<<BIBot>> dimension_formatter[%s] = %s',
slot_key, output_text)
response_string += '.'
return helpers.close(session_attributes, 'Fulfilled', {
'contentType': 'PlainText',
'content': response_string
})
def top_intent_handler(intent_request, session_attributes):
method_start = time.perf_counter()
logger.debug('<<BIBot>> top_intent_handler: session_attributes = ' +
json.dumps(session_attributes))
session_attributes['greetingCount'] = '1'
session_attributes['resetCount'] = '0'
session_attributes['finishedCount'] = '0'
session_attributes['lastIntent'] = 'Top_Intent'
# Retrieve slot values from the current request
slot_values = session_attributes.get('slot_values')
try:
slot_values = helpers.get_slot_values(slot_values, intent_request)
except bibot.SlotError as err:
return helpers.close(session_attributes, 'Fulfilled', {
'contentType': 'PlainText',
'content': str(err)
})
logger.debug('<<BIBot>> "top_intent_handler(): slot_values: %s',
slot_values)
# Retrieve "remembered" slot values from session attributes
slot_values = helpers.get_remembered_slot_values(slot_values,
session_attributes)
logger.debug(
'<<BIBot>> "top_intent_handler(): slot_values afer get_remembered_slot_values: %s',
slot_values)
if slot_values.get('count') is None:
slot_values['count'] = TOP_DEFAULT_COUNT
if slot_values.get('dimension') is None:
if len(bibot.DIMENSIONS.keys()) > 0:
response_string = 'Please tell me a dimension, for example, "top five '
for counter, item in enumerate(bibot.DIMENSIONS.keys()):
if counter == 0:
response_string += item + '".'
elif counter == 1:
response_string += ' I can also report on ' + item
if len(bibot.DIMENSIONS.keys()) == 2:
response_string += '.'
elif counter < (len(bibot.DIMENSIONS.keys()) - 1):
response_string += ', ' + item
else:
if len(bibot.DIMENSIONS.keys()) == 3:
response_string += ' and ' + item + '.'
else:
response_string += ', and ' + item + '.'
else:
response_string = 'Please tell me a dimension, for example, "top five months".'
return helpers.close(session_attributes, 'Fulfilled', {
'contentType': 'PlainText',
'content': response_string
})
# If switching dimension, forget the prior remembered value for that dimension
dimension_slot = bibot.DIMENSIONS.get(
slot_values.get('dimension')).get('slot')
if dimension_slot is not None:
slot_values[dimension_slot] = None
logger.debug(
'<<BIBot>> "top_intent_handler(): cleared dimension slot: %s',
dimension_slot)
# store updated slot values
logger.debug(
'<<BIBot>> "top_intent_handler(): calling remember_slot_values_NEW: %s',
slot_values)
helpers.remember_slot_values(slot_values, session_attributes)
# Check for minimum required slot values
if slot_values.get('dimension') is None:
return helpers.close(
session_attributes, 'Fulfilled', {
'contentType':
'PlainText',
'content':
"Sorry, I didn't understand that. Try \"Top 5 venues for all rock and pop\"."
})
# Build and execute query
try:
# the SELECT clause is for a particular dimension e.g., top 5 {states}...
# Example: "SELECT {}, SUM(s.amount) ticket_sales FROM sales s, event e, venue v, category c, date_dim ed "
select_clause = TOP_SELECT.format(
bibot.DIMENSIONS.get(slot_values.get('dimension')).get('column'))
except KeyError:
return helpers.close(
session_attributes, 'Fulfilled', {
'contentType':
'PlainText',
'content':
"Sorry, I don't know what you mean by " +
slot_values['dimension']
})
# add JOIN clauses
where_clause = TOP_JOIN
# add WHERE clause for each non empty slot
for dimension in bibot.DIMENSIONS:
slot_key = bibot.DIMENSIONS.get(dimension).get('slot')
if slot_values[slot_key] is not None:
value = userexits.pre_process_query_value(slot_key,
slot_values[slot_key])
where_clause += TOP_WHERE.format(
bibot.DIMENSIONS.get(dimension).get('column'), value)
try:
# the GROUP BY is by dimension, and the ORDER by is the aggregated fact
# Example: " GROUP BY {} ORDER BY ticket_sales desc"
order_by_group_by = TOP_ORDERBY.format(
bibot.DIMENSIONS.get(slot_values.get('dimension')).get('column'))
order_by_group_by += " LIMIT {}".format(slot_values.get('count'))
except KeyError:
return helpers.close(
session_attributes, 'Fulfilled', {
'contentType': 'PlainText',
'content': "Sorry, I don't know what you mean by " + dimension
})
query_string = select_clause + where_clause + order_by_group_by
logger.debug('<<BIBot>> Athena Query String = ' + query_string)
# execute Athena query
response = helpers.execute_athena_query(query_string)
# Build response text for Lex
response_string = ''
result_count = len(response['ResultSet']['Rows']) - 1
if result_count < int(slot_values.get('count', 0)):
if result_count == 0:
response_string += "There weren't any " + slot_values.get(
'dimension') + " "
elif result_count == 1:
response_string += "There was only 1. "
else:
response_string += "There were only " + str(result_count) + ". "
if result_count == 0:
pass
elif result_count == 1:
try:
response_string += 'The top ' + bibot.DIMENSIONS.get(
slot_values.get('dimension')).get('singular')
except KeyError:
response_string += 'The top ' + slot_values.get('dimension')
else:
response_string += 'The top ' + str(
result_count) + ' ' + slot_values.get('dimension')
# add the English versions of the WHERE clauses
for dimension in bibot.DIMENSIONS:
slot_key = bibot.DIMENSIONS[dimension].get('slot')
logger.debug('<<BIBot>> pre top5_formatter[%s] = %s', slot_key,
slot_values.get(slot_key))
if slot_values.get(slot_key) is not None:
# the DIMENSION_FORMATTERS perform a post-process functions and then format the output
# Example: {... 'venue_state': {'format': ' in the state of {}', 'function': get_state_name}, ...}
if userexits.DIMENSION_FORMATTERS.get(slot_key) is not None:
output_text = userexits.DIMENSION_FORMATTERS[slot_key][
'function'](slot_values.get(slot_key))
output_text = userexits.DIMENSION_FORMATTERS[slot_key][
'format'].lower().format(output_text)
response_string += ' ' + output_text
logger.debug('<<BIBot>> top5_formatter[%s] = %s', slot_key,
output_text)
if result_count == 0:
pass
elif result_count == 1:
response_string += ' was '
else:
response_string += ' were '
# add the list of top X dimension values to the response text
if result_count > 0:
remembered_value = None
for counter, item in enumerate(response['ResultSet']['Rows']):
if counter > 0:
if counter > 1:
response_string += '; and ' if counter == result_count else '; '
if result_count > 1:
response_string += str(counter) + ', '
value = userexits.post_process_dimension_output(
slot_values.get('dimension'),
item['Data'][0]['VarCharValue'])
response_string += value
remembered_value = item['Data'][0]['VarCharValue']
response_string += '.'
logger.debug('<<BIBot>> response_string = ' + response_string)
# If result count = 1, remember the value for future questions
if result_count == 1:
slot_name = bibot.DIMENSIONS.get(
slot_values.get('dimension')).get('slot')
slot_values[slot_name] = remembered_value
# store updated query attributes
helpers.remember_slot_values(slot_values, session_attributes)
method_duration = time.perf_counter() - method_start
method_duration_string = 'method time = %.0f' % (method_duration *
1000) + ' ms'
logger.debug('<<BIBot>> "Method duration is: ' + method_duration_string)
logger.debug(
'<<BIBot>> top_intent_handler() - sessions_attributes = %s, response = %s',
session_attributes, {
'contentType': 'PlainText',
'content': response_string
})
return helpers.close(session_attributes, 'Fulfilled', {
'contentType': 'PlainText',
'content': response_string
})
def compare_intent_handler(intent_request, session_attributes):
method_start = time.perf_counter()
logger.debug('<<BIBot>> compare_intent_handler: session_attributes = ' +
json.dumps(session_attributes))
session_attributes['greetingCount'] = '1'
session_attributes['resetCount'] = '0'
session_attributes['finishedCount'] = '0'
session_attributes[
'lastIntent'] = None # "switch" handling done in Compare_Intent
# Retrieve slot values from the current request
slot_values = session_attributes.get('slot_values')
try:
slot_values = helpers.get_slot_values(slot_values, intent_request)
except bibot.SlotError as err:
return helpers.close(session_attributes, 'Fulfilled', {
'contentType': 'PlainText',
'content': str(err)
})
logger.debug('<<BIBot>> "count_intent_handler(): slot_values: %s',
slot_values)
# Retrieve "remembered" slot values from session attributes
slot_values = helpers.get_remembered_slot_values(slot_values,
session_attributes)
logger.debug(
'<<BIBot>> "count_intent_handler(): slot_values afer get_remembered_slot_values: %s',
slot_values)
# Remember updated slot values
helpers.remember_slot_values(slot_values, session_attributes)
for key, config in COMPARE_CONFIG.items():
if slot_values.get(config['1st']):
if slot_values.get(config['2nd']) is None:
return helpers.close(session_attributes, 'Fulfilled', {
'contentType': 'PlainText',
'content': config['error']
})
slot_values['dimension'] = key
slot_values[bibot.DIMENSIONS[key]['slot']] = None
the_1st_dimension_value = slot_values[config['1st']].lower()
the_2nd_dimension_value = slot_values[config['2nd']].lower()
break
# Build and execute query
select_clause = COMPARE_SELECT.format(
bibot.DIMENSIONS[slot_values['dimension']]['column'])
where_clause = COMPARE_JOIN
the_1st_dimension_value = userexits.pre_process_query_value(
bibot.DIMENSIONS[key]['slot'], the_1st_dimension_value)
the_2nd_dimension_value = userexits.pre_process_query_value(
bibot.DIMENSIONS[key]['slot'], the_2nd_dimension_value)
where_clause += " AND (LOWER(" + bibot.DIMENSIONS[
slot_values['dimension']][
'column'] + ") LIKE LOWER('%" + the_1st_dimension_value + "%') OR "
where_clause += "LOWER(" + bibot.DIMENSIONS[slot_values['dimension']][
'column'] + ") LIKE LOWER('%" + the_2nd_dimension_value + "%')) "
logger.debug(
'<<BIBot>> compare_sales_intent_request - building WHERE clause')
for dimension in bibot.DIMENSIONS:
slot_key = bibot.DIMENSIONS.get(dimension).get('slot')
if slot_values[slot_key] is not None:
logger.debug(
'<<BIBot>> compare_sales_intent_request - calling userexits.pre_process_query_value(%s, %s)',
slot_key, slot_values[slot_key])
value = userexits.pre_process_query_value(slot_key,
slot_values[slot_key])
where_clause += COMPARE_WHERE.format(
bibot.DIMENSIONS.get(dimension).get('column'), value)
order_by_group_by = COMPARE_ORDERBY.format(
bibot.DIMENSIONS[slot_values['dimension']]['column'])
query_string = select_clause + where_clause + order_by_group_by
logger.debug('<<BIBot>> Athena Query String = ' + query_string)
response = helpers.execute_athena_query(query_string)
# Build response string
response_string = ''
result_count = len(response['ResultSet']['Rows']) - 1
# add the English versions of the WHERE clauses
counter = 0
for dimension in bibot.DIMENSIONS:
slot_key = bibot.DIMENSIONS[dimension].get('slot')
logger.debug('<<BIBot>> pre compare_sale_formatter[%s] = %s', slot_key,
slot_values.get(slot_key))
if slot_values.get(slot_key) is not None:
# the DIMENSION_FORMATTERS perform a post-process function and then format the output
# Example: {... 'venue_state': {'format': ' in the state of {}', 'function': get_state_name}, ...}
if userexits.DIMENSION_FORMATTERS.get(slot_key) is not None:
output_text = userexits.DIMENSION_FORMATTERS[slot_key][
'function'](slot_values.get(slot_key))
if counter == 0:
response_string += userexits.DIMENSION_FORMATTERS[
slot_key]['format'].format(output_text)
else:
response_string += ', ' + userexits.DIMENSION_FORMATTERS[
slot_key]['format'].lower().format(output_text)
counter += 1
logger.debug('<<BIBot>> compare_sales_formatter[%s] = %s',
slot_key, output_text)
if (result_count == 0):
if len(response_string) > 0:
response_string += ', '
response_string += "I didn't find any results for the " + slot_values[
'dimension']
response_string += " " + userexits.post_process_dimension_output(
key, the_1st_dimension_value)
response_string += " and " + userexits.post_process_dimension_output(
key, the_2nd_dimension_value) + "."
elif (result_count == 1):
if len(response_string) > 0:
response_string += ', there '
else:
response_string += 'There '
response_string += 'is only one ' + bibot.DIMENSIONS[
slot_values['dimension']]['singular'] + '.'
elif (result_count == 2):
# put the results into a dict for easier reference by name
result_set = {}
result_set.update({
response['ResultSet']['Rows'][1]['Data'][0]['VarCharValue'].lower(
): [
response['ResultSet']['Rows'][1]['Data'][0]['VarCharValue'],
float(response['ResultSet']['Rows'][1]['Data'][1]
['VarCharValue'])
]
})
result_set.update({
response['ResultSet']['Rows'][2]['Data'][0]['VarCharValue'].lower(
): [
response['ResultSet']['Rows'][2]['Data'][0]['VarCharValue'],
float(response['ResultSet']['Rows'][2]['Data'][1]
['VarCharValue'])
]
})
logger.debug('<<BIBot>> compare_intent_handler - result_set = %s',
result_set)
the_1st_dimension_string = result_set[
the_1st_dimension_value.lower()][0]
the_1st_dimension_string = userexits.post_process_dimension_output(
key, the_1st_dimension_string)
the_2nd_dimension_string = result_set[
the_2nd_dimension_value.lower()][0]
the_2nd_dimension_string = userexits.post_process_dimension_output(
key, the_2nd_dimension_string)
if len(response_string) == 0:
response_string = 'Sales for ' + the_1st_dimension_string + ' were '
else:
response_string += ', sales for ' + the_1st_dimension_string + ' were '
the_1st_amount = result_set[the_1st_dimension_value.lower()][1]
the_2nd_amount = result_set[the_2nd_dimension_value.lower()][1]
the_1st_amount_formatted = '{:,.0f}'.format(the_1st_amount)
the_2nd_amount_formatted = '{:,.0f}'.format(the_2nd_amount)
if (the_1st_amount == the_2nd_amount):
response_string += 'the same as for ' + the_2nd_dimension_string + ', $' + the_2nd_amount_formatted
else:
if (the_1st_amount < the_2nd_amount):
percent_different = (the_1st_amount -
the_2nd_amount) / the_2nd_amount * -1
higher_or_lower = 'lower'
else:
percent_different = (the_1st_amount -
the_2nd_amount) / the_2nd_amount
higher_or_lower = 'higher'
response_string += '{:.0%}'.format(
percent_different
) + ' ' + higher_or_lower + ' than for ' + the_2nd_dimension_string
response_string += ': $' + the_1st_amount_formatted + ' as opposed to $' + the_2nd_amount_formatted + '.'
else: # >2, should not occur
response_string = 'I seem to have a problem, I got back ' + str(
result_count) + ' ' + dimension + '.'
logger.debug('<<BIBot>> response_string = ' + response_string)
method_duration = time.perf_counter() - method_start
method_duration_string = 'method time = %.0f' % (method_duration *
1000) + ' ms'
logger.debug('<<BIBot>> "Method duration is: ' + method_duration_string)
return helpers.close(session_attributes, 'Fulfilled', {
'contentType': 'PlainText',
'content': response_string
})