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

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

SQL connection strings

--driver example --connection-string Additional 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@(10.0.0.1:3306)/mydb'
OR
'server=sqldb.myserver.com;userid=mysqlusername;password=secret;database=mydbname'		 MySQL connection strings
SQL-Server --driver mssql --connection-string 'server=sqldb.myserver.com;userid=mysqlusername;password=secret;database=mydbname' SQL-Server connection strings

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 Additional information
boolean bool bool  
integer int int  
decimal decimal decimal  
not applicable id mutex Table primary key
not applicable * idset
* idsetq		 set Used to reduce table rows and make queries more efficient.
string string keyed mutex  
not applicable * stringset
* stringsetq		 keyed set Used to reduce table rows and make queries more efficient.
timestamp timestamp timestamp  

Examples

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%

Next step

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