SQL ingest tool reference

The SQL ingester tool selects data from a MSSQL, MySQL or Postgres database table.

Source column names are used to specify column names for each record.

Before you begin

• Learn how to manage ingestion
• Prepare the SQL

Syntax

molecula-consumer-sql        \
  <source-and-target-flags>  \
  <sql-flags>                \
  <id-flags>                 \
  <batch-flags>              \
  <error-flags>              \
  <log-stat-flags>           \
  <testing-flags>            \
  <auth-token-flags>         \
  <tls-authentication-flags> \

Common flags

SQL ingest flags

Generate ID flags

Batch processing flags

Error handling flags

Logging & statistics flags

Testing flags

Authentication token flags

TLS authentication flags

Additional information

batch additional

There is no default batch-size because memory usage per record varies between workloads.

During ingestion processing, there is a fixed overhead:

• from setting up an ingester transaction
• for each row

Setting large batch-size values will:

• average-out the overheads
• proportionally increase memory usage
• improve performance (in general terms)

For example:

concurrency additional

The concurrency ingest flag is used to run ingesters in parallel which can:

• improve utilization on multi-core systems
• allow for redundancy

Alternatively, ingest processes can be launched individually on different environments.

SQL connection strings

Missing value processing

Missing and empty string values are handled the same.

Quoting values

Use double quotes “…” to enclose fields containing:

• Line breaks (CRLF)
• Commas
• double quotes

Value Path Selection

The path argument is an array of JSON object keys which are applied in order.

For example:

Use allow-missing-fields to avoid path errors where source data is missing.

config options for data types

• Use the config flag when changing flags from default values.

Examples

Ingest data from SQL server database

SELECT queries the SQL-Server table then the ingester:

• converts the data to Roaring Bitmap format
• imports the records to the my_data FeatureBase index.

molecula-consumer-sql \
	--connection-string 'server=sqldb.myserver.com;userid=mysqlusername;password=secret;database=mydbname' \
	--featurebase-hosts 10.0.0.1:10101 \
	--batch-size 1000000 \
	--driver=mssql \
	--index=my_data \
	--id-field=id \
	--row-expr 'SELECT tableID as id__ID, zipcode as zipcode__String limit 10'

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'

Further information

• Example SQL and ingest tool flags