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,
    storeno SMALLINT NOT NULL,
    tillno TINYINT NOT NULL,
    saleweek TINYINT NOT NULL)
FROM maprfs TARGET 'file /kogvol01/demos/retail/eposdata/*.csv.gz';


  • 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