DB

Please note - the DB writer is currently being repackaged to a series of independent writers for the various databases we now support:
  • MySQL
  • Oracle
  • Redshift

This article will catch up eventually!




Description

Simple DB writer, takes data from Storage API and imports them into MySQL Database.

Usage

URL

DEVEL:

http://wr-db.kbc-devel-02.keboola.com/app_dev.php/wr-db/run

Configuration

Writer's configuration resides in Storage API in SYS bucket sys.c-wr-db.
Tables in this bucket represents connections to DBs, credentials for these connections are stored in table attributes:

  • accountId: Account ID
  • name: Account name
  • description: (optional) description of this table
  • db.host: hostname
  • db.user: username
  • db.password: users password
  • db.database: database name
  • db.port: (optional) port number

Table rows represents 1:1 mapping between SAPI tables and DB tables. Table has columns:

  • id: unique identifier
  • input: SAPI table ID
  • output: table name in DB
  • config: row configuration in JSON format

    Row configuration

    Is in json format. Currently has only one option - initTable, which can be used to perform a MySql query (or queries) before data are loaded from SAPI to DB table.
    Ideal for create/drop table queries.

    Example:

    {
    "initTable": "CREATE TABLE IF NOT EXISTS `newdummy` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT, `col1` text, `col2` text, `col3` text, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;"
    }

    The query must be without linebreaks.

API

Init new config

POST http://wr-db.kbc-devel-02.keboola.com/app_dev.php/wr-db/configs

{
    "name": "myNewAccount"
}

List available configs

GET http://wr-db.kbc-devel-02.keboola.com/app_dev.php/wr-db/configs

Provide credentials for the config

POST http://wr-db.kbc-devel-02.keboola.com/app_dev.php/wr-db/account/{accountId}

{
    "host": "...",
    "user": "...",
    "password": "...",
    "database": "..."
}

Add mapping

input - storage api table ID
output - database table name

POST http://wr-db.kbc-devel-02.keboola.com/app_dev.php/wr-db/rows/{accountId}

{
    "input": "in.c-ex-dummy.dummy",
    "output": "newdummy"
}

Run

POST http://wr-db.kbc-devel-02.keboola.com/app_dev.php/wr-db/run

Resources

Comments