Creating external tables

The Kognitio Retail Demo is used throughout this section. Before building external tables you need to make the retail data available in your MapR-FS file system

Create all the external tables you’ll need for the retail demo

1. Create the first external table.

You can access data in MapR-FS using Kognitio external tables. The retail demo has six tables. You can create a table to access the main EPOS sales data using

-- Main fact table EPOS sales
CREATE EXTERNAL TABLE demo_ret.t_ret_sale(
    saledate DATE NOT NULL,
    saletime TIME NOT NULL,
    basketno BIGINT NOT NULL,
    prodno SMALLINT NOT NULL,
    price SMALLINT NOT NULL,
    storeno SMALLINT NOT NULL,
    tillno TINYINT NOT NULL,
    saleweek TINYINT NOT NULL)
FROM maprfs TARGET 'file /kogvol01/demos/retail/eposdata/*.csv.gz';

Notes:

  • The path in the TARGET string is the location of the data in the MapR-FS file system. It isn’t a path in your Linux file system. If you chose a different location for the demo data in MapR-FS, modify the path before you run this script.

  • We’ve used a wildcard *.csv.gz in the file declaration in the TARGET string. This allows you to access any number of files in the same location (with the same format) via a single Kognitio external table.

2. Test your external table.

To check that the data is accessible, execute a query against your new externaltable:

SELECT TOP 10 * FROM demo_ret.t_ret_sale;

This returns 10 parsed rows from the mapR-FS files in the TARGET string.

3. Create the other five external tables.

-- Store dimension table
CREATE EXTERNAL TABLE demo_ret.t_ret_store(
        storeno SMALLINT NOT NULL,
        storename VARCHAR(30) NOT NULL,
        storeregion VARCHAR(30) NOT NULL,
        storenigrid CHAR(6) NULL,
        storelat DECIMAL(10,6) NULL,
        storelong DECIMAL(10,6) NULL)
FROM maprfs TARGET 'file /kogvol01/demos/retail/dimdata/t_ret_store.csv.gz';

-- Product dimension table
CREATE EXTERNAL TABLE demo_ret.t_ret_product(
        prodno SMALLINT NOT NULL,
        section_no TINYINT NOT NULL,
        group_no TINYINT NOT NULL,
        dept_no TINYINT NOT NULL,
        product_name CHAR(30) NOT NULL)
FROM maprfs TARGET 'file /kogvol01/demos/retail/dimdata/t_ret_product.csv.gz';

-- Product Department dimension table
CREATE EXTERNAL TABLE demo_ret.t_ret_prod_dept(
        dept_no INT NOT NULL,
        dept_name CHAR(25) NULL)
FROM maprfs TARGET 'file /kogvol01/demos/retail/dimdata/t_ret_prod_dept.csv.gz';

-- Product Group dimension table
CREATE EXTERNAL TABLE demo_ret.t_ret_prod_group(
        group_no TINYINT NOT NULL,
        group_name CHAR(20) NOT NULL,
        dept_no TINYINT NULL)
FROM maprfs TARGET 'file /kogvol01/demos/retail/dimdata/t_ret_prod_group.csv.gz';

-- Product Section dimension table
CREATE EXTERNAL TABLE demo_ret.t_ret_prod_section(
        section_no INT NOT NULL,
        section_name CHAR(35) NULL,
        group_no INT NULL)
FROM maprfs TARGET 'file /kogvol01/demos/retail/dimdata/t_ret_prod_section.csv.gz';

Next: Creating views and images