Example SQL tables and ingest tool flags

Tables

Assets table

CREATE TABLE `assets` (
    `asset_tag` char(4) NOT NULL DEFAULT '',
    `weight` int(8) DEFAULT 0,
    `warehouse` char(10),
    PRIMARY KEY (`asset_tag`)
);

Table: assets

asset_tag	weight	warehouse
ABCD	16	US-EAST
EFGH	9	US-WEST
IJKL	47	US-WEST
MNOP	30	US-EAST

Events table

CREATE TABLE `events` (
    `pk` char(10) NOT NULL DEFAULT '',
    `asset_tag` char(4) NOT NULL DEFAULT '',
    `fan_time` date NOT NULL,
    `fan_vol` char(10),
    PRIMARY KEY (`pk`)
);

Table: events

pk	asset_tag	fan_time	fan_vol
aus-14	ABCD	2021-06-21	90%
aus-15	EFGH	2021-06-19	10%
aus-16	ABCD	2021-06-20	60%
den-11	IJKL	2021-06-19	70%
den-12	MNOP	2021-06-20	90%
nyc-78	MNOP	2021-06-21	80%
den-13	MNOP	2021-06-21	80%
nyc-79	ABCD	2021-06-21	30%

SQL ingest examples with flags

SQL ingest flags to import from assets table

SELECT queries the MySQL table, then the ingester:

converts the records to Roaring Bitmap format
imports the records to the asset_list FeatureBase index.

molecula-consumer-sql \
    --driver mysql \
    --connection-string 'username:password@(127.0.0.1:3306)/dbname' \
    --featurebase-hosts localhost:10101 \
    --batch-size 10000 \
    --index=asset_list \
    --primary-key-fields 'asset_tag' \
    --row-expr 'SELECT asset_tag as asset_tag__String, weight as weight__Int, warehouse as warehouse__String FROM assets'

Join Assets and Events tables into a single FeatureBase index

The SELECT statement queries the MySQL events table to:

return event data along with weight of relative asset_tag
create locale field based on the first three characters from the events.pk field
join assets.asset_tag and events.asset_tag.

The ingester then:

converts the records to Roaring Bitmap format
imports the records to the events_plus_weight FeatureBase index.

molecula-consumer-sql \
    --driver mysql \
    --connection-string 'username:password@(127.0.0.1:3306)/dbname' \
    --featurebase-hosts localhost:10101 \
    --batch-size 10000 \
    --index=events_plus_weight \
    --primary-key-fields 'pk' \
    --row-expr 'SELECT events.pk as pk__String, events.asset_tag as asset_tag__String, assets.weight as weight__Int, SUBSTRING(events.pk, 1, 3) as locale__String FROM events INNER JOIN assets on assets.asset_tag = events.asset_tag'