Databases

RIOT database import and export

Databases

RIOT can import from and export to databases.

Drivers

RIOT relies on JDBC to interact with databases. It includes JDBC drivers for the most common database systems:

• Oracle

  jdbc:oracle:thin:@myhost:1521:orcl

• IBM Db2

  jdbc:db2://host:port/database

• MS SQL Server

  jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]]

• MySQL

  jdbc:mysql://[host]:[port][/database][?properties]

• PostgreSQL

  jdbc:postgresql://host:port/database

• SQLite

  jdbc:sqlite:sqlite_database_file_path

Database Import

The db-import command imports data from a relational database into Redis.

riot -h <redis host> -p <redis port> db-import --url <jdbc url> SQL [REDIS COMMAND...]

To show the full usage, run:

riot db-import --help

Examples

PostgreSQL Example

riot db-import "SELECT * FROM orders" --url "jdbc:postgresql://host:port/database" --username appuser --password passwd hset --keyspace order --keys order_id

Import from PostgreSQL to JSON strings

riot db-import "SELECT * FROM orders" --url "jdbc:postgresql://host:port/database" --username appuser --password passwd set --keyspace order --keys order_id

This will produce Redis strings that look like this:

{
  "order_id": 10248,
  "customer_id": "VINET",
  "employee_id": 5,
  "order_date": "1996-07-04",
  "required_date": "1996-08-01",
  "shipped_date": "1996-07-16",
  "ship_via": 3,
  "freight": 32.38,
  "ship_name": "Vins et alcools Chevalier",
  "ship_address": "59 rue de l'Abbaye",
  "ship_city": "Reims",
  "ship_postal_code": "51100",
  "ship_country": "France"
}

Database Export

Use the db-export command to read from a Redis database and writes to a SQL database.

The general usage is:

riot -h <redis host> -p <redis port> db-export --url <jdbc url> SQL

To show the full usage, run:

riot db-export --help

Redis reader options

• --scan-count
  How many keys to read at once on each call to SCAN
• --scan-match
  Pattern of keys to scan for (default: * i.e. all keys)
• --scan-type
  Type of keys to scan for (default: all types)
• --key-include
  Regular expressions for keys to whitelist. For example mykey:.* will only consider keys starting with mykey:.
• --key-exclude
  Regular expressions for keys to blacklist. For example mykey:.* will not consider keys starting with mykey:.
• --key-slots
  Ranges of key slots to consider for processing. For example 0:8000 will only consider keys that fall within the range 0 to 8000.
• --read-threads
  How many value reader threads to use in parallel
• --read-batch
  Number of values each reader thread should read in a pipelined call
• --read-queue
  Max number of items that reader threads can put in the shared queue. When the queue is full, reader threads wait for space to become available. Queue size should be at least # threads * batch, e.g., --read-threads 4 --read-batch 500 => --read-queue 2000
• --read-pool
  Size of the connection pool shared by reader threads. Can be smaller than the number of threads
• --read-from
  Which Redis cluster nodes to read from: master, master_preferred, upstream, upstream_preferred, replica_preferred, replica, lowest_latency, any, any_replica. See Read-From Settings for more details.
• --mem-limit
  Maximum memory usage in megabytes for a key to be read (default: 0). Use 0 to disable memory usage checks.
• --mem-samples
  Number of memory usage samples for a key (default: 5).

Example

Export to PostgreSQL

riot db-export "INSERT INTO mytable (id, field1, field2) VALUES (CAST(:id AS SMALLINT), :field1, :field2)" --url "jdbc:postgresql://host:port/database" --username appuser --password passwd --scan-match "gen:*" --key-regex "gen:(?<id>.*)"