Skip to main content Link Menu Expand (external link) Document Search Copy Copied

SQL ingest source reference

FeatureBase can import data from MySQL, Postgres and MySQL databases using SQL queries executed at the command line.

Table of contents

Before you begin

You will need the following privileges on the database:

Connect string (merge into the include file, below)

The SQL ingest tool connects to the source database using your login credentials before executing SQL queries.

SQL connection strings

--driver example --connection-string Further information
Postgres 'postgresql://postgres:password@localhost:5432/molecula?sslmode=disable'
OR</br>'user=postgres password=password host=localhost port=5432 dbname=molecula sslmode=disable'
postgres connection string
MySql 'myusername:password@('
MySQL connection strings
SQL-Server --driver mssql --connection-string ';userid=mysqlusername;password=secret;database=mydbname' | [SQL-Server` connection strings]({:target=”_blank”}  

Command-line SQL statements

Once connected to the source database, SQL queries copy data which is then batched, converted to Roaring Bitmap format then saved to a target FeatureBase index.

Mapping data types

This table provides mapping between FeatureBase SQL data types and internal data types used by the application for configuring ingestion, API calls, etc.

General data type FeatureBase SQL data type Internal data type Further information
boolean bool bool  
integer int int  
decimal decimal decimal  
not applicable id mutex Table primary key
not applicable idset set Used to reduce table rows and make queries more efficient.
string string keyed mutex  
not applicable stringset keyed set Used to reduce table rows and make queries more efficient.
timestamp timestamp timestamp  


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

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%

Next step

Refer to SQL ingest reference to learn how to use the command-line SQL ingest tool