Can I create a Hangouts Chat bot to (get and) display results from a SQL database?

Question

I want to build a custom chat bot (using Google Hangouts Chat) that will answer requests like "Sales 08-03-2018 in $" -> "$500000". I have the data in AWS Redshift. I can use psycopg2 that connects Redshift using Python. Are there any APIs that will directly connect to Redshift or MySQL and send these aggregation results back to my bot giving an answer to the required question? If not, what are the alternate ways to do this? Also, can anyone tell me where do we run the Flask code? I am new to this, and it will be really helpful if someone could just brief me about where to code and run. I have a G Suite account that has enabled the Hangouts Chat API. Please help.

Thanks.

Answer 1

You pretty much have everything you need to build this solution and don't need any specific APIs to do the work (unless you want to build this one solution to serve other users).

• Write your bot in Python (obviously since you're already doing that)
• Use psycopg2 to access Redshift as you've already stated
• Your bot app aggregates the results from psycopg2+Redshift
• Format your output in JSON and return it to Hangouts Chat
• Use Google App Engine to host your app; it supports Flask
• Start with this simple, synchronous Flask App Engine sample bot
• If your bot can't respond immediately, make it asynchronous; check out this equivalent async Flask bot
• If you want your data/results to look good in Chat, check out this 3rd Flask sample bot featuring interactive cards which allow for formatted visual output
• Once your app is running on App Engine, register its YOUR_APP.appspot.com (or custom domain) endpoint in the Hangouts Chat "Configuration" tab under "Connection settings" -> "Bot URL"
• Check out all the Hangouts Chat developer docs here
• You can also run Flask on any other PaaS or IaaS instances that supports it.
• Also read this for more about the platform itself as well as other ways to create+host bots
• Similar idea from Motorola Solutions CIO per his Mar 2018 post... fetch & tie together data then present results in a chat room