Kueri, a natural language database tool - Part Three

_________________________

Kueri installation - Windows 7 PC
_________________________

installed Kueri on a Windows 7 PC. In the on-line Kueri referencethis page shows how to do it. Everything went well and I'll share my experiences here. Kueri also works in a Linux environment; this page has further details.

First, be sure to first install at least Java 1.7 before you start the Kueri installation. Kueri relies on Java for its own system management. Kueri works perfectly with a locally installed MySQL / SQL Server / etc. database, but it does not need these resources for its own operation. Because the install package downloads in a tar.gz archive file, the suggested 7-Zip (a free download) really helped. The Windows environment instructions worked well and I quickly installed Kueri SDK Enterprise. Now, Kueri will not show in the Windows Control Panel. If you need to uninstall Kueri for some reason, first drill down to the folder / directory location where you installed it and delete it. Next, drill down to

     C:\Users\{user name}

as in this example


1. Look for the .kueri folder / directory in C:\Users\{user name}
and delete the

     .kueri

folder.

Kueri will work perfectly with a MySQL database stored anywherelocally or in the cloud somewhere. For MySQL, however, Kueri will need a MySQL Java connector, available here from the MySQL website. Copy the connector JAR file, named

     mysql-connector-java-5.1.39-bin

here, from the package, and place it in the

     kueri\tomcat\jdbc

directory as shown in this


2. Place the MySQL Java Connector in the
kueri\tomcat\jdbc
directory
example. 

To run Kueri, first launch the

     startup.bat

file as step 5 explains. To make things easier, I created a desktop shortcut out to this batch file and in its Advanced . . . (AKA properties)

3. Shortcut Advanced properties

I picked

4. Run the batch file as an administrator
"Run as administrator". When startup.bat launches, a DOS command prompt (DOS box) will open; at first, "a lot" of "stuff" will happen inside that box but soon enough, all that will ease off and it will look something roughly like

5. Running startup.bat
this. Keep this box open every moment Kueri runs. Minimize it if you want, but don't close it.

We're almost there. Point a new browser tab to

     127.0.0.1/admin

and the Kueri admin login

6. Kueri admin login
will open. Use

     admin / admin


as the first email / password for the first login, but that, Kueri will want a new password. After a little more configuration in step 8 here, you can use Kueri in production. Next, I'll set up a Kueri connection to the Google Cloud SQL resource I built in Part Two.
_______________

Part three here showed how to set up Kueri in a Windows environment. Part four will show how to configure a Kueri Data Source.

Kueri, a natural language database tool - Part Four

_________________________

Configure a Kueri Data Source
_________________________

After the Kueri login, I clicked Data Sources at the bottom right and on the Data Sources page, I clicked

1. Set up a New Connection to a data source
New Connection. In the MySQL Workbench

2. Find the Cloud SQL IP address
I copied the Cloud SQL IP address from the kueri_demo connection, and back at the Kueri Admin page, I drilled down to open the New Database connection

3. Build a new database connection
page. I named the new Database Connection "kueri_demo"; for the Host, I used the Cloud SQL resource IP address I copied as shown in Figure 2 above. Note that I could have found the IP address at the Google Cloud SQL console as well. I used the Google Cloud SQL credentials for the user name / password, I tested it, and I clicked Next to open this

4. Choose tables
page. Although this page shows a "test_demo_table", the create database script in the GitHub repository file will not create this table and this article will not use this table in any way. It shows on this page only for my own testing and development work. Click Next to open this

5. Initial configuration - Data Source tables
page for initial configuration of the chosen tables. To get the most out of Kueri, table configuration matters the most. The folks at Kueri themselves publish great documentation here and especially here - more than enough to get going. In the GitHub repository, I included the latest schema, or database configuration, for the kueri_demo datasource. Of course, Kueri allows and encourages further datasource data table configuration after the initial work phase. Drill down

6. Configure existing tables
to

     Configure -> Edit

tables to open the table configuration page, and proceed.

A configured Kueri Data Source becomes a valuable resource, and resource owners need a way to backup and restore it. For Kueri, this means "Export / Import a Semantic Map". To do this, drill into the Data Source options

7. Export/Import Semantic Map
of the relevant data source (kueri_demo in this case) and click Export/Import Semantic Map. This dialog box

8. Export / Import dialog box
will open. In the GitHub repository, file

     KUERI_ARTICLE_RESOURCES.zip

 has an exported backup file called

     kueri_demo_30.1

for the kueri_demo Data Source described in this article. To use it, build a basic bare-bones Kueri Data Source called "kueri_demo", and then import kueri_demo_30.1 with the feature described here.

I found that I could handle the table configuration more easily with the Workspace open in one browser tab and the Table Configuration page open in another tab.

Note that web developers can embed Kueri in the web software they build. Although I have not yet explored that feature, it clearly has even more potential.

As I worked with Kueri to configure the

     kueri_demo

Data Source, I realized that the more effort one puts into a Kueri configuration, the more value Kueri will return. The

     kueri_demo_30.1

file reflects my latest configuration work, and because this involved experimentation, a description of this work does not fit well in a review article. I admit: I am still learning how to configure - and more importantly optimize - Data Source tables in and through Kueri. I believe that like so many other products, Kueri will start as a skill set, then grow into a specialty, and finally become an industry. Right now, I am at the Kueri skill set building stage.
_______________

As we've seen, Kueri integrates natural language with relational database resources. It solves a difficult problem and it solves that problem well. Although it has a large configuration space, anyone with a basic understanding of that space can use it. More understanding and skill means more value returned. Kueri has unlimited potential.

Kueri, a natural language database tool - Part Two

_________________________

The Google Cloud SQL database for this article
_________________________

For this article, Kueri points to a Google Cloud SQL database called

     kueri_demo

as the featured demo data source. This database has two tables. Starting from this U.S. Census Bureau page, the Complete Zip Code Totals file (zbp11totals) became the source for the

     kueri_demo.zbp11totals

table. At this IRS page, the 13zpallnoagi file (the second file in the third ZIP Code Data block) became the source for the second

     kueri_demo.taxstat

table, which I will explain here.

For the 13zpallnoagi file import, I first scrapped all columns starting at column "AD" (column 30). Next, I deleted the

     STATEFIPS (column "A" <=> column 1)

and the

     AGI_STUB (column "D" <=> column 4)

columns. This left 27 columns. This file (the file in the second Documentation block) at the IRS page maps the 13zpallnoagi file layout, and I renamed most of the remaining columns


ORIGINAL COLUMN NAME    NEW COLUMN NAME
STATE<>    STATE
ZIPCODE<>    ZIP
N1<>    OVERALL_RETURN_COUNT
MARS1<>    SINGLE_RETURN_COUNT
MARS2<>    JOINT_RETURN_COUNT
MARS4<>    HOH_RETURN_COUNT
PREP<>    PAID_PREP_RETURN_COUNT
N2<>    EXMPTN_COUNT
NUMDEP<>    DEPNDT_COUNT
A00100<>    AGI
N02650<>    RTN_COUNT_WITH_TOTAL_INCOME
A02650<>    TOTAL_INC_AMT
N00200<>    RTN_COUNT_WITH_SAL_AND_WAGES
A00200<>    SAL_AND_WAGE_AMT
N00300<>    RTN_COUNT_WITH_TXBL_INT
A00300<>    TXBL_INT_AMT
N00600<>    RTN_COUNT_WITH_ORDY_DVDS
A00600<>    ORDY_DVD_AMT
N00650<>    RTN_COUNT_WITH_QLFD_DVDS
A00650<>    QLFD_DVD_AMT
N00700<>    RTN_COUNT_WITH_ST_LCL_RFDS
A00700<>    ST_LCL_RFD_AMT

to make things easier.

Next, I set up a Google Cloud SQL (AKA MySQL hosted by Google) resource. Google Cloud Platform offers a free trial product - $300.00 of resources for sixty days, and I used it here. A Cloud SQL resource requires a locally installed MySQL Workbench 6.3, and using steps I described in an earlier Bit Vectors article, I built a connection between the MySQL Workbench and Cloud SQL. At that point, I began to build the kueri_demo database. After I built this database, I rolled it up in a complete database creation script, which I placed in a 3.3‑meg ZIP file named

     KUERI_ARTICLE_RESOURCES

in the GitHub repository. This file will unzip into a 61‑meg MySQL script file; pick the MySQL workbench connection to the Cloud SQL resource, drop the script file text into the MySQL Workbench tab, and run it. With one click, the script should build the kueri_demo database. Note that each table has a ZIP column - each five characters long - but the tables do not have a formal

     parent -> child

relation between them.
_______________

Part two here showed how to set up a Google Cloud SQL demo database which Kueri will front-end. Part three will describe how to install and configure the Kueri product.

Kueri, a natural language database tool - Part One

_________________________

The Kueri user experience
_________________________

Front-end application software sits between the users and the database layer. That front-end software evolved from text-based applications, moving to point and click GUI technologies that query the database and show the result set(s). Users can certainly input text through GUI text controls, but the words have no natural language structure. A natural language technology, where a user can type query sentences with standard grammar, is the next step. For many years, this seemed like science fiction, but Kueri - a natural language interface for databases - has arrived.

Kueri works as a front-end layer for many SQL Server compatible databases and common format spreadsheets. After installation and configuration, the user types a natural language query and Kueri returns a result set. Developers can even embed Kueri in the web and mobile software they build.

In this four-part article, I'll review Kueri and describe my experiences with it. As an early reviewer, the folks at Kueri provided me with the Kueri SDK Enterprise edition, which they will release shortly. The Kueri Lite (Beta) SDK is available now. I built the review around a demo Kueri SDK Enterprise installation that assumes

  • the Kueri SDK Enterprise product
  • a Windows 7 Intel PC
  • a Google Cloud SQL database

as prerequisitesDownload the Google Cloud SQL demo database creation script and the demo Kueri configuration file at this GitHub repositoryNote that a Google Cloud SQL database is a MySQL database, so the database creation script should work in any MySQL environment with little or no modification. 

Part one here will describe the Kueri user experience. Part two will describe the Google Cloud database that Kueri will front-end for this article. Part three will show how to install Kueri. Part four will explain how to configure a Kueri Data Source.
____________________

We'll first look at the Kueri user experience. In the main Kueri page


1. Open the Kueri Workspace
click "Workspace" to switch into the Workspace mode. Then, type a natural language query in the textbox

2. Try a query!
and hit "Enter". Kueri shows the result set. At the right, click "Switch to Guided mode" and below the textbox, Kueri will offer suggestions as the user types the query. Note that Guided mode is more restrictive as the user types the query. Click "Show the SQL" at the right to see the actual MySQL query

3. Kueri built this MySQL query
that Kueri built.
_______________

Part one here described the Kueri user experience. Part two will describe the Google Cloud database that Kueri will front-end for this article.