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.