paint-brush
How to use NLP to SQL API?by@Chernenko
4,898 reads
4,898 reads

How to use NLP to SQL API?

by DenisOctober 4th, 2019
Read on Terminal Reader
Read this story w/o Javascript
tldt arrow

Too Long; Didn't Read

This article presents detail instructions on how technically skilled experts can implement cognitive automation use cases and provide non-technical employees intuitive access to enterprise data by Natural Language Interface. NLP to SQL API is used for unstructured Natural Language user request transformation into Structured Query Language. For the Python environment used for this tutorial, we use Asyncio library to write concurrent code using the Async/await syntax. For security reasons all requests should be encrypted with HTTPS protocol, which encrypts all requests, so anyone can access it.

Companies Mentioned

Mention Thumbnail
Mention Thumbnail
featured image - How to use NLP to SQL API?
Denis HackerNoon profile picture

Data is useless without the ability to easily get and act on it. The success of future enterprises will combine sophisticated information collection with better user experience, and the Natural Language User Interface comprises much of this user experience.

Intuitive Natural Language Interface allows users to find, compare, analyze, drill and manipulate existing information in the data warehouse to derive insights and act accordingly. This article presents detail instructions on how technically skilled experts can implement cognitive automation use cases and provide non-technical employees intuitive access to enterprise data by Natural Language Interface.

Before we start, it assumed that non-technical people have access to at least one of the following interfaces: Microsoft Teams, Skype for Business (Lync), Slack, Skype, Telegram, Email, Telegram, Messenger, Cortana, or web-browser. All the abovementioned Natural Language Interfaces included in Microsoft Azure Application. Other applications also possible, but not covered in this tutorial.


The basic tools you need to prepare are:
 
 1. Create MS Application. You can create by the link https://dev.botframework.com/bots/provision 
 After registration you should keep 2 parameters:
 APP_ID = ‘your-application-id’
 APP_PASSWORD = ‘your-application-password’
 
 2. MS Bot Framework SDK. https://dev.botframework.com/ Our tutorial in based on Python environment, so you need to import Python MS Bot Framework SDK https://github.com/microsoft/botbuilder-python

3. SSL certificates. For security reasons all requests should be encrypted with HTTPS protocol TLS 1.3, which encrypts all requests, so anyone can access it. You can buy SSL certificates or generate it with CertBot on your own.

4. Take care of Asynchronous processes. As you expect your application will have a lot of requests from lots of the users at the same time, you should pay your attention to implement asynchronous for all your processes in order to avoid the bottleneck. For the Python environment used for this tutorial, we use asyncio library. Asyncio is a python library to write concurrent code using the async/await syntax. In case you use an environment different from Python, you can feel free to search for other asynchronous libraries.

5. Asynchronous API. The same asynchronous pattern for API. In this tutorial, AIOHTTP is used.

6. URL and Secret Token for NLP to SQL API. NLP to SQL web service is used for unstructured Natural Language user request transformation into Structured Query Language. For this tutorial, we are using NLSQL. For NLSQL customization you should have database schema and Q&A samples you would like to use. For NLP to API webservice you should have your API web service documentation as well as Q&A dialog sample.

Well, after all, the above-mentioned tools are ready, we can simulate the software architecture (you can find the story picture above).

Below you can find Bot System sample for Python environment:

#!/usr/bin/python
import ssl
from aiohttp import web
import asyncio
import aiohttp
from botbuilder.core import (BotFrameworkAdapter, BotFrameworkAdapterSettings,
                             TurnContext, MemoryStorage)
from botbuilder.schema import (Attachment, Activity, ActivityTypes)


APP_ID = '' #1 add your APP ID
APP_PASSWORD = '' #2 add you APP Password
SSL_CONTEXT = ssl.SSLContext(ssl.PROTOCOL_SSLv23)
SSL_CONTEXT.load_cert_chain('', '') #3 add your SSL certificates
PORT = 8000
SETTINGS = BotFrameworkAdapterSettings(APP_ID, APP_PASSWORD)
ADAPTER = BotFrameworkAdapter(SETTINGS)

async def create_reply_activity(request_activity: Activity, text: str, attachment: Attachment = None) -> Activity:
    activity = Activity(
        type=ActivityTypes.message,
        channel_id=request_activity.channel_id,
        conversation=request_activity.conversation,
        recipient=request_activity.from_property,
        from_property=request_activity.recipient,
        attachment_layout='carousel',
        text=text,
        service_url=request_activity.service_url)
    if attachment:
        activity.attachments = attachment
    return activity


async def handle_conversation_update(context: TurnContext) -> web.Response:
    if context.activity.members_added[0].id != context.activity.recipient.id:
        response = await create_reply_activity(context.activity, "Hello world!")
        await context.send_activity(response)
    return web.Response(status=200)


async def unhandled_activity() -> web.Response:
    return web.Response(status=404)


async def api_connect(text):
    url = 'https://api.nlsql.com/sap' #4 add your API url here
    headers = {'Authorization': 'Token API KEY', 'Content-Type': 'application/json'} #5 add your API Key
    payload = {'message': text}
    async with aiohttp.ClientSession() as session:
        r = await session.post(url, headers=headers, json=payload)
        data = await r.json()

        return data


# MAIN function for bot
async def request_handler(context: TurnContext) -> web.Response:

    if context.activity.type == 'message':
        try:
            data = await api_connect(context.activity.text)
            # Make connection to own internal database and take data using received sql
            # Build separate logic for different API reply message types, if you need it.
            # data_type = data["data_type"]
            # if data_type == 'graph':
            #       ...
            # elif data_type == 'message':
            #       ...
            # elif data_type == 'buttons':
            #       ...
            # elif data_type == 'error':
            #       ...
            # etc.
            data_type = data["data_type"]
            if data_type == 'ytd':
                result = data['sql']['sql1'] + ' \n' + data['sql']['sql2']
            else:
                result = data['sql']
            response = await create_reply_activity(context.activity, result)
            await context.send_activity(response)
        except KeyError:
            pass

    elif context.activity.type == 'conversationUpdate':
        return await handle_conversation_update(context)

    elif context.activity.action == 'add':
        return await handle_conversation_update(context)

    else:
        return await unhandled_activity()


async def messages(request) -> web.Response:
    body = await request.json()
    # print("request: ", request.headers)
    # print("body: ", body)
    activity = Activity().deserialize(body)
    auth_header = request.headers['Authorization'] if 'Authorization' in request.headers else ''
    web.Response(status=200)
    try:
        return await ADAPTER.process_activity(activity, auth_header, request_handler)
    except Exception as exc:
        raise exc


async def init_app(loop):
    app = web.Application(loop=loop)
    app.add_routes([web.post('/api/messages', messages)])
    
    return app


try:
    loop = asyncio.get_event_loop()
    app = loop.run_until_complete(init_app(loop))
    web.run_app(app, host='127.0.0.1', port=PORT)
except Exception as e:
    raise e

The same logic should be applied in case of any other programming language usage. 

Data becomes the worldwide economy’s raw material. It pursuit drives antiquated sectors to reinvent themselves. Energy, production, healthcare, retail, and even government undergo a digital transformation.

However, information is useless without the ability to use it and act correspondingly. Companies that will survive the next decade will not only have superior information; they will have an intuitive and flexible user interface for everyone. The intuitive user interface is based on human objectives and behavior, which could be different. Cognitive Automation has behavioral impacts that drive further business decisions and improves enterprise financial results.

Different user experience changes how people make choices in subtle and unconscious ways. What is seen, where it is presented, and how it provides interactions, influences actions. The ability to query data easily will stimulate people for using the information and make more data-driven choices in order to drive the businesses forward faster.

I am CEO and founder of NLSQL, where we are designing the future of Cognitive Automation