SQL

Receiver Sender

This service implements a SQL service. Supported RDBMS are Postgres, MySQL (MariaDB), MSSQL and Oracle Database.

docker pull registry.goingrid.io/services/postgres:v0.1.0
docker pull registry.goingrid.io/services/mysql:v0.1.0
docker pull registry.goingrid.io/services/mssql:v0.1.0
docker pull registry.goingrid.io/services/oracledb:v0.1.0

Table of contents

Service properties

This service implements the ingrid protocol message. Following properties are being used:

Property Usage
Class -
Operation -
Control some configuration properties can be overridden at runtime (containing suffix _CTRL)
Data accepts only properties if it has been specified in the configuration properties

Service capabilities

SELECT

The SELECT statement is used to select data from a database.

Example below will execute following query: SELECT * FROM user.

POST https://rest.app.goingrid.io/sql/select HTTP/1.1
Content-Type: application/json
Authorization: Basic admin nutz

{}

sql-select:
  image: ${ING_REGISTRY}/services/mysql:latest
  deploy:
    restart_policy:
      condition: on-failure
    placement:
      constraints:
        - node.role == manager
  environment:
    NAME: "sql-select"
    SERVERHOST: "hive"
    SERVERAUTH: "file:///run/secrets/ing-hive-key"
    INPUTCHANNEL: "sql_select"
    SQL_SERVER: "mariadb"
    SQL_PORT: "3308"
    SQL_DATABASE: "test"
    SQL_USERNAME: "root"
    SQL_PASSWORD: "file:///run/secrets/ing-mariadb-key"
    SQL_STATEMENT: "SELECT"
    #SQL_COLUMNS: "" if empty will use *
    SQL_TABLE: "user"
  depends_on:
    - hive
  secrets:
    - ing-hive-key
    - ing-mariadb-key
  networks:
    - ing-entry
    - ing-middle

JOIN

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Example below will execute following query: SELECT orders.id, customers.firstname, customers.lastname, orders.orderDate FROM orders INNER JOIN customers ON orders.id=customers.id

POST https://rest.app.goingrid.io/sql/select HTTP/1.1
Content-Type: application/json
Authorization: Basic admin nutz

{}

sql-select:
  image: ${ING_REGISTRY}/services/mysql:latest
  deploy:
    restart_policy:
      condition: on-failure
    placement:
      constraints:
        - node.role == manager
  environment:
    NAME: "sql-select"
    SERVERHOST: "hive"
    SERVERAUTH: "file:///run/secrets/ing-hive-key"
    INPUTCHANNEL: "sql_select"
    SQL_SERVER: "mariadb"
    SQL_PORT: "3308"
    SQL_DATABASE: "test"
    SQL_USERNAME: "root"
    SQL_PASSWORD: "file:///run/secrets/ing-mariadb-key"
    SQL_STATEMENT: "SELECT"
    SQL_COLUMNS: "orders.id, customers.firstname, customers.lastname, orders.orderDate"
    SQL_TABLE: "orders"
    SQL_JOIN: "INNER JOIN customers ON orders.id=customers.id"
  depends_on:
    - hive
  secrets:
    - ing-hive-key
    - ing-mariadb-key
  networks:
    - ing-entry
    - ing-middle

WHERE

The WHERE clause is used to filter records.

Example below will execute following query: SELECT * FROM user WHERE firstname LIKE "%T%" AND lastname LIKE "%S%"

POST https://rest.app.goingrid.io/sql/select HTTP/1.1
Content-Type: application/json
Authorization: Basic admin nutz

{
  "firstname": ["%T%"],
  "lastname": ["%S%"]
}

sql-select:
  image: ${ING_REGISTRY}/services/mysql:latest
  deploy:
    restart_policy:
      condition: on-failure
    placement:
      constraints:
        - node.role == manager
  environment:
    NAME: "sql-select"
    SERVERHOST: "hive"
    SERVERAUTH: "file:///run/secrets/ing-hive-key"
    INPUTCHANNEL: "sql_select"
    SQLSERVER: "mariadb"
    SQL_PORT: "3308"
    SQL_DATABASE: "test"
    SQL_USERNAME: "root"
    SQL_PASSWORD: "file:///run/secrets/ing-mariadb-key"
    SQL_STATEMENT: "SELECT"
    SQL_TABLE: "user"
    SQL_WHERE: "firstname LIKE {{ firstname }} AND lastname like {{ lastname }}"
  depends_on:
    - hive
  secrets:
    - ing-hive-key
    - ing-mariadb-key
  networks:
    - ing-entry
    - ing-middle

GroupBy

Not in scope at present, use Raw.

Having

Not in scope at present, use Raw.

OrderBy

Not in scope at present, use Raw.

Limit

Not in scope at present, use Raw.

Offset

Not in scope at present, use Raw.

INSERT

The INSERT statement is used to insert new records in a table.

Example below will execute following query: INSERT INTO user (firstname,lastname) VALUES ("Tony","Stark")

POST https://rest.app.goingrid.io/sql/insert HTTP/1.1
Content-Type: application/json
Authorization: Basic admin nutz

{
  "data_firstname": ["Tony"],
  "data_lastname": ["Stark"]
}

sql-insert:
  image: ${ING_REGISTRY}/services/mysql:latest
  deploy:
    restart_policy:
      condition: on-failure
    placement:
      constraints:
        - node.role == manager
  environment:
    NAME: "sql-insert"
    SERVERHOST: "hive"
    SERVERAUTH: "file:///run/secrets/ing-hive-key"
    INPUTCHANNEL: "sql_insert"
    SQL_SERVER: "mariadb"
    SQL_PORT: "3308"
    SQL_DATABASE: "test"
    SQL_USERNAME: "root"
    SQL_PASSWORD: "file:///run/secrets/ing-mariadb-key"
    SQL_STATEMENT: "INSERT"
    SQL_TABLE: "user"
    SQL_COLUMNS: "firstname,lastname"
    SQL_VALUEPARAMS: "data_firstname,data_lastname"
  depends_on:
    - hive
  secrets:
    - ing-hive-key
    - ing-mariadb-key
  networks:
    - ing-entry
    - ing-middle

UPDATE

The UPDATE statement is used to modify the existing records in a table.

Example below will execute following query: UPDATE user SET firstname = "Tony_updated", lastname = "Stark_updated" WHERE id = 123

POST https://rest.app.goingrid.io/sql/update HTTP/1.1
Content-Type: application/json
Authorization: Basic admin nutz

{
  "data_firstname": ["Tony_updated"],
  "data_lastname": ["Stark_updated"],
  "where_id": ["123"]
}

sql-update:
  image: ${ING_REGISTRY}/services/mysql:latest
  deploy:
    restart_policy:
      condition: on-failure
    placement:
      constraints:
        - node.role == manager
  environment:
    NAME: "sql-update"
    SERVERHOST: "hive"
    SERVERAUTH: "file:///run/secrets/ing-hive-key"
    INPUTCHANNEL: "sql_update"
    SQL_SERVER: "mariadb"
    SQL_PORT: "3308"
    SQL_DATABASE: "test"
    SQL_USERNAME: "root"
    SQL_PASSWORD: "file:///run/secrets/ing-mariadb-key"
    SQL_STATEMENT: "UPDATE"
    SQL_TABLE: "user"
    SQL_COLUMNS: "firstname,lastname"
    SQL_VALUEPARAMS: "data_firstname,data_lastname"
    SQL_WHERE: "id = {{where_id}}"
  depends_on:
    - hive
  secrets:
    - ing-hive-key
    - ing-mariadb-key
  networks:
    - ing-entry
    - ing-middle

DELETE

The UPDATE statement is used to modify the existing records in a table.

Example below will execute following query: DELETE FROM user WHERE id = 123

POST https://rest.app.goingrid.io/sql/delete HTTP/1.1
Content-Type: application/json
Authorization: Basic admin nutz

{
  "id": ["123"]
}

sql-delete:
  image: ${ING_REGISTRY}/services/mysql:latest
  deploy:
    restart_policy:
      condition: on-failure
    placement:
      constraints:
        - node.role == manager
  environment:
    NAME: "sql-delete"
    SERVERHOST: "hive"
    SERVERAUTH: "file:///run/secrets/ing-hive-key"
    INPUTCHANNEL: "sql_delete"
    SQLSERVER: "mariadb"
    SQL_PORT: "3308"
    SQL_DATABASE: "test"
    SQL_USERNAME: "root"
    SQL_PASSWORD: "file:///run/secrets/ing-mariadb-key"
    SQL_STATEMENT: "DELETE"
    SQL_TABLE: "user"
    SQL_WHERE: "id = {{id}}"
  depends_on:
    - hive
  secrets:
    - ing-hive-key
    - ing-mariadb-key
  networks:
    - ing-entry
    - ing-middle

Raw

If one of the statements above does suit your expectations, you still can just execute a raw query. Be careful with performing raw queries, you need a profound RDBMS security design.

Example below will execute following query: SELECT firstname, lastname FROM user WHERE id=123

POST https://rest.app.goingrid.io/sql/raw HTTP/1.1
Content-Type: application/json
Authorization: Basic admin nutz

{
  "id": ["123"]
}

sql-raw:
  image: ${ING_REGISTRY}/services/mysql:latest
  deploy:
    restart_policy:
      condition: on-failure
    placement:
      constraints:
        - node.role == manager
  environment:
    NAME: "sql-raw"
    SERVERHOST: "hive"
    SERVERAUTH: "file:///run/secrets/ing-hive-key"
    INPUTCHANNEL: "sql_raw"
    SQLSERVER: "mariadb"
    SQL_PORT: "3308"
    SQL_DATABASE: "test"
    SQL_USERNAME: "root"
    SQL_PASSWORD: "file:///run/secrets/ing-mariadb-key"
    SQL_RAWMODE: "TRUE"
    SQL_RAWACTION: "query"
    SQL_RAWVALUE: "SELECT firstname, lastname FROM user WHERE id={{ id }}"
  depends_on:
    - hive
  secrets:
    - ing-hive-key
    - ing-mariadb-key
  networks:
    - ing-entry
    - ing-middle

Poll

The SQL service is a trigger as well. Meaning you can poll and fetch for data and forward the data to the hive where a flow or a worker will process the request. In order to use polling, you have to enable it by setting SQL_POLLMODE and define a SELECT statement.

Example below will execute following query: SELECT * FROM user

sql-poll:
  image: ${ING_REGISTRY}/services/mysql:latest
  deploy:
    restart_policy:
      condition: on-failure
    placement:
      constraints:
        - node.role == manager
  environment:
    NAME: "sql-poll"
    SERVERHOST: "hive"
    SERVERAUTH: "file:///run/secrets/ing-hive-key"
    OUTPUTCHANNEL: "main"
    SQLSERVER: "mariadb"
    SQL_PORT: "3308"
    SQL_DATABASE: "test"
    SQL_USERNAME: "root"
    SQL_PASSWORD: "file:///run/secrets/ing-mariadb-key"
    SQL_POLLMODE: "true"
    SQL_POLLINTERVAL: "20s"
    SQL_STATEMENT: "SELECT"
    SQL_TABLE: "user"
  depends_on:
    - hive
  secrets:
    - ing-hive-key
    - ing-mariadb-key
  networks:
    - ing-entry
    - ing-middle

Service configuration

The service configuration has been split up for easier readability and use. For the sake of easier readability some parameters are being mentioned several times.

General configuration

These are the configuration parameters used to connect to the RDBMS and define which statement is being used.

Parameter Default Description
SQL_SERVER "" Server host or ip used to connect to the RDBMS
SQL_PORT 3306 Port used to connect to the RDBMS
SQL_PROTOCOL "tcp" Protocol used to connect to the RDBMS
SQL_DATABASE "" Existing database on the RDBMS
SQL_USERNAME "" Username used to authenticate to the RDBMS
SQL_PASSWORD "" Password belonging to the SQL_USERNAME
SQL_STATEMENT "" SQL statement used to perform an action (valid statements are SELECT, INSERT, UPDATE and DELET)
SQL_STATEMENT_CTRL "" Control attribute used to override SQL_STATEMENT at runtime

SELECT statement configuration

These are the configuration parameters used to execute a SELECT statement (set SQL_STATEMENT=SELECT to perform this action).

Parameter Default Description
SQL_TABLE "" Table used to perform against to
SQL_TABLE_CTRL "" Control attribute used to override SQL_TABLE at runtime
SQL_COLUMNS "" List of columns (separated by comma), * used if empty
SQL_COLUMNS_CTRL "" Control attribute used to override SQL_COLUMNS at runtime
SQL_JOIN "" Join clause used to combine rows from two or more tables
SQL_JOIN_CTRL "" Control attribute used to override SQL_JOIN at runtime
SQL_WHERE "" Where clause used to filter records
SQL_WHERE_CTRL "" Control attribute used to override SQL_WHERE at runtime
SQL_GROUPBY "" Not in scope at present, use Raw
SQL_GROUPBY_CTRL "" Not in scope at present, use Raw
SQL_HAVING "" Not in scope at present, use Raw
SQL_HAVING_CTRL "" Not in scope at present, use Raw
SQL_ORDERBY "" Not in scope at present, use Raw
SQL_ORDERBY_CTRL "" Not in scope at present, use Raw
SQL_LIMIT "" Not in scope at present, use Raw
SQL_LIMIT_CTRL "" Not in scope at present, use Raw
SQL_OFFSET "" Not in scope at present, use Raw
SQL_OFFSET_CTRL "" Not in scope at present, use Raw

INSERT statement configuration

These are the configuration parameters used to execute a INSERT statement (set SQL_STATEMENT=INSERT to perform this action).

Parameter Default Description
SQL_TABLE "" Table used to perform against to
SQL_TABLE_CTRL "" Control attribute used to override SQL_TABLE at runtime
SQL_COLUMNS "" List of columns (separated by comma), * used if empty
SQL_COLUMNS_CTRL "" Control attribute used to override SQL_COLUMNS at runtime
SQL_VALUEPARAMS "" List of parameters (separated by comma) containing values (parameters extracted from the data protocol message)
SQL_VALUEPARAMS_CTRL "" Control attribute used to override SQL_VALUEPARAMS at runtime

UPDATE statement configuration

These are the configuration parameters used to execute a UPDATE statement (set SQL_STATEMENT=UPDATE to perform this action).

Parameter Default Description
SQL_TABLE "" Table used to perform against to
SQL_TABLE_CTRL "" Control attribute used to override SQL_TABLE at runtime
SQL_COLUMNS "" List of columns (separated by comma), * used if empty
SQL_COLUMNS_CTRL "" Control attribute used to override SQL_COLUMNS at runtime
SQL_VALUEPARAMS "" List of parameters (separated by comma) containing values (parameters extracted from the data protocol message)
SQL_VALUEPARAMS_CTRL "" Control attribute used to override SQL_VALUEPARAMS at runtime
SQL_WHERE "" Where clause used to filter records
SQL_WHERE_CTRL "" Control attribute used to override SQL_WHERE at runtime

DELETE statement configuration

These are the configuration parameters used to execute a DELETE statement (set SQL_STATEMENT=DELETE to perform this action).

Parameter Default Description
SQL_TABLE "" Table used to perform against to
SQL_TABLE_CTRL "" Control attribute used to override SQL_TABLE at runtime
SQL_WHERE "" Where clause used to filter records
SQL_WHERE_CTRL "" Control attribute used to override SQL_WHERE at runtime

Raw configuration

These are the configuration parameters used to execute a raw SQL query.

Parameter Default Description
SQL_RAWMODE false Enabling raw queries only
SQL_RAWACTION "exec" Use either exec or query
SQL_RAWACTION_CTRL "" Control attribute used to override SQL_RAWACTION at runtime
SQL_RAWVALUE "" Raw query to be processed
SQL_RAWVALUE_CTRL "" Control attribute used to override SQL_RAWVALUE at runtime

Poll configuration

These are the configuration parameters used to start a poller. The poller will execute periodically a SELECT statement and provide the result data to the hive (use the SELECT configuration to describe what kind of data will be polled).

Parameter Default Description
SQL_POLLMODE false Enabling poll mode
SQL_POLLINTERVAL "5m" Poll interval
SQL_POLLCLASS "sql" The class being used in the ingrid protocol message
SQL_POLLOPERATION "poll" The operation being used in the ingrid protocol message
SQL_TABLE "" SELECT configuration
OUTPUTCHANNEL "main" Channel to be used as output.

Additionally the sql service includes all properties of the service configuration, the input configuration and the output configuration.

Known issues

Multi query statement within Oracle

Following multi query statement will not work within an oracle system:

alter session set "_oracle_script"=true; CREATE USER RENO;