Revision: 3885

1 Web API

1.1 Overview

GridDB provides a Web API to register rows, acquire rows and execute a TQL and a SQL statement for GridDB cluster. The Web API is configured as a web application.

The functions of GridDB Web API are shown below.

• Row acquisition
  • Acquires rows from the container.
• Row registration
  • Registers rows to the container.
• Database connection confirmation
  • Checks connection to a database.
• Container list acquisition
  • Acquires a container list from a database.
• Container information acquisition
  • Acquires container information (column etc.) from a container
• TQL statement execution
  • Executes multiple SQL statements and acquires the results.
• Row deletion
  • Deletes a row from a container.
• Creating a container
  • Creates a new container for a database.
• Deleting container
  • Deletes a container from a database.
• SQL SELECT execution
  • Executes a SQL SELECT statement

1.2 Using Web API

To use gs_admin, Java has to be installed beforehand. The compatible versions are as follows.

• Oracle Java 8
• RedHat OpenJDK 8

The procedure to install and configure Web API is as follows.

1. Installation of a library package.
2. Installation of Web API package.
3. Setting of the destination cluster
4. Setting of the Web API operation (optional)
5. Setting of the log output destination (optional)

1.2.1 Installation of library packages

Install the following two packages.

• Java library package (griddb-xx-java_lib-XX.X-linux.x86_64.rpm)
• In the case of package (griddb-xx-newsql-X.X.X-linux.x86_64.rpm) *GridDB AE of a NewSQL interface

Log into a machine installed with the Web API as a root user, and install the package using the command below.

# rpm -Uvh griddb-xx-java_lib-X.X.X-linux.x86_64.rpm
# rpm -Uvh griddb-xx-newsql-X.X.X.linux.x86_64.rpm

* xx indicates the GridDB edition (se, ae)

*X.X.X indicates the GridDB version.

1.2.2 Installation of a Web API package

Install the Web API package (griddb-xx-webapi-XX.X-linux.x86_64.rpm).

Log into a machine installed with the Web API as a root user, and install the package using the command below.

# rpm -Uvh griddb-xx-webapi-X.X.X-linux.x86_64.rpm

* xx indicates the GridDB edition (se, ae)

*X.X.X indicates the GridDB version.

After installing, Web API .jar file and configuration files are installed as follows:

/usr/griddb-webapi/griddb-webapi.jar                     : Web API jar file

/var/lib/gridstore/webapi/conf/griddb_webapi.properties  : Configuration file
                              /repository.json           : Cluster information definition file
                         /log                            : Log output directory

1.2.3 Specifying cluster configuration

Specify the information of the cluster to be connected from the Web API in the cluster information definition file ( /var/lib/gridstore/webapi/conf/repository.json ).

Based on the value of the cluster definition file (gs_cluster.json) of the cluster to be connected, specify the cluster configuration method to "mode", and the address information corresponding to the method.

Example: In case of the multicast method

{
  "clusters" : [
    {
      "name" : "myCluster",
      "mode" : "MULTICAST",
      "address" : "239.0.0.111",
      "port" : 31999
    }
  ]
}

[Memo]

• If the contents of repository.json are invalid (format error, mandatory parameter not defined, etc.), Web API startup will result in an error.
• The GridDB AE cluster can be connected from the GridDB SE Web API, and vice versa. Provided that, to execute the SQL SELECT statement, which is an API limited to GridDB AE, connect to the GridDB AE cluster from the GridDB AE Web API.

1.2.4 Setting of the Web API operation (optional)

Configure Web API behaviors by setting the configuration file (/var/lib/gridstore/webapi/conf/griddb_webapi.properties).

If not setting it will work with the default values. Configure the values according to the system.

1.2.4.1 GridDB configuration

1.2.4.2 Web API configuration

[Memo]

• The Web API needs to be restarted to reflect the environment settings.

1.2.5 Setting of the log output destination (optional)

The Web API logs are output to the following directory by default.

/var/lib/gridstore/webapi/log

To change the output directory, edit the following file:

/var/lib/gridstore/webapi/conf/logback.xml

1.2.6 Starting and Stopping

Web API applications can be started and stopped using the service command.

# service griddb-webapi [start|stop|status|restart]

1.3 Common functions (HTTP request/response)

This chapter describes the HTTP request /response part common to each function.

1.3.1 URI

The URI to be accessed when using the Web API.

http://(Web API server IP) :(port)/griddb/v2/(command path)

[Memo]

• Specify path of each function as ":path".
• It is not allowed in this version's GridDB WEB APIs that clusters, databases and containers which name contains any special characters('-', '.', '/', '=') are specified in the path.

1.3.2 Request header

Specify the following headers to the request header. (Common to all APIs)

1.3.3 Request body

Specify the request body as JSON format. Please refer to the JSON format of each functions.

[Memo]

• Write JSON format data in UTF-8.
• Write the date in UTC and in the following format:
  • YYYY-MM-DDThh:mm:ss.SSSZ
• Otherwise, an error will occur.
  • Example:
    • 2016/01/17T14: 32: 33.888Z ... Error due to year, month and date separator error
    • 2016-01-18 ... Error because time is not specified

1.3.4 Response code

Refer to the paragraph of each function for the response code.

1.3.5 Response body

Refer to the section on each function for the response body when the processing succeeds.

If processing fails, an error message is returned in the following format.

{
  "version":"v2",
  "errorCode":"Error code",
  "errorMessage":"Error message"
}

1.4 Row acquisition

This function acquire the rows from the container (table). It is also possible to narrow down the rows to be acquired by specifying conditions.

Path

/:cluster/dbs/:database/containers/:container/rows

HTTP method

POST

Request header

Refer to the request header.

Request body

[Memo]

• If the value specified by limit is greater than maxLimit in the configuration file, the value of maxLimit is used in the limit clause.

Example: Acquires row data with a column "id" value of 50 or more, sorts it in descending order by the value of "id", and acquires 100 values from the 11th

{
  "offset" : 10,
  "limit"  : 100,
  "condition" : "id >= 50",
  "sort" : "id desc"
}

Response code

Response body

Acquired rows will be returned as the following JSON data.

Example:

{
  "columns" : [
    {"name": "date", "type": "TIMESTAMP" },
    {"name": "value", "type": "DOUBLE" },
    {"name": "str", "type": "STRING" }
  ],
  "rows" : [
    ["2016-01-16T10:25:00.253Z", 100.5, "normal" ],
    ["2016-01-16T10:35:00.691Z", 173.9, "normal" ],
    ["2016-01-16T10:45:00.032Z", 173.9, null ]
  ],
  "total" : 100000,
  "offset" : 0,
  "limit" : 3
}

Depending on the column data type, column values with the following JSON data type will be returned.

[Memo]

• BLOB type is unsupported. An empty string will be returned when specifying the container that has the BLOB column.
• In case that column value is NULL, null will be returned for the column in JSON data.

1.5 Row registration

This function registers the rows to the container.

Specify the rows to be registered in JSON format. Multiple rows can be registered in one container.

[Memo]

• The data of one row / multiple rows can be specified and registered into one container.
• The container to be registered must exist.
• When the container has a row key, if a row has the row key that already exists in the container will be updated, otherwise a row will be newly created.
• When the container has no row key, a row will be newly created.
• When an exception occurs during row registration processing, only some rows may be registered. Therefore, when retrying with an HTTP client when an exception occurs, the same row data may be registered duplicately in a container has no row key.

Path

/:cluster/dbs/:database/containers/:container/rows

HTTP method

PUT

Request header

Refer to the request header.

Request body

Specify the rows as following JSON format.

Example:

[
  ["2016-01-16T10:25:00.253Z", 100.5, "normal"],
  ["2016-01-16T10:35:00.691Z", 173.9, "normal"],
  ["2016-01-16T10:45:00.032Z", 173.9, null]
]

Depending on the column data type, describe a column value with the following JSON data type.

[Memo]

• BLOB type is unsupported. An error occurs when specifying the container that has the BLOB column.
• If a NULL value (null of JSON data type) is specified as column value, it operates as follows:
  • When the NOT NULL constraint is specified for the column : Registration error will occur.
  • Otherwise : A NULL value will be registered.

Response code

Response body

If the process is successful, nothing is returned.

Please refer to response body in case of failure.

1.6 Database connection checking

Check the connection to the specified database.

Path

/:cluster/dbs/:database/checkConnection

HTTP method

GET

Request header

Refer to the request header.

Request parameter

Response code

Response body

If the process is successful, nothing is returned.

Please refer to response body in case of failure.

1.7 Container list acquisition

Get a list of containers and tables. It is also possible to narrow down the containers to be acquired by specifying conditions.

Path

/:cluster/dbs/:database/containers

HTTP method

GET

Request header

Refer to the request header.

Request parameter

[Memo]

• If "limit" is set to 0 (zero), all rows matching the condition can be acquired.
• offset can be used together with "limit".

Response code

Response body

Acquired rows will be returned as the following JSON data.

Example:

{
  "names" : [
    "container1",
    "container2",
    "timeseries1"
  ],
  "total" : 100000,
  "offset" : 0,
  "limit" : 3
}

1.8 Container information acquisition

Get the information on a container or a table.

Path

/:cluster/dbs/:database/containers/:container/info

HTTP method

GET

Request header

Refer to the request header.

Request body

-

Response code

Response body

Example:

{
  "container_name" : "container1",
  "container_type" : "collection",
  "rowkey" : true,
  "columns" : [
    {"name": "date", "type": "TIMESTAMP", "index": ["tree"]},
    {"name": "value", "type": "DOUBLE", "index": []},
    {"name": "str", "type": "STRING", "index": []}
  ]
}

[Memo]

• Containers with composite row keys and composite indexes are not supported. When the command is executed, IllegalStateException occurs.

1.9 TQL command execution

Execute a TQL statement. Multiple TQL statements can be sent at once. The value of specific columns can be set to acquire in the execution result.

Path

/:cluster/dbs/:database/tql

HTTP method

POST

Request header

Refer to the request header.

Request body

[Memo]

• If the value specified by limit in TQL is greater than maxLimit in the configuration file, the value of maxLimit is used in the limit clause.

Example:

[
  {"name" : "container1", "stmt" : "select * limit 100", "columns" : null},
  {"name" : "container2", "stmt" : "select * where column1>=0", "columns" : ["column1"]},
  {"name" : "container3", "stmt" : "select SUM(*) order by column1 desc", "columns" : null}
]

Response code

Response body

[Memo]

• If the SQL statement is an aggregate operation, total, offset, and limit are not included.

Example:

[
  {
    "columns" : [
      {"name": "date", "type": "TIMESTAMP"},
      {"name": "value", "type": "DOUBLE"},
      {"name": "str", "type": "STRING"}
    ],
    "results" : [
      ["2016-01-16T10:25:00.253Z", 100.5, "normal" ],
      ["2016-01-16T10:35:00.691Z", 173.9, "normal" ],
      ["2016-01-16T10:45:00.032Z", 173.9, null]
    ],
    "total" : 1000125,
    "offset" : 0,
    "limit" : 3
  },
  {
    "columns" : [
      {"name": "aggregationResult", "type": "LONG"}
    ],
    "results" : [
      [55]
    ]
  }
]

1.10 Row deletion

This function deletes the rows from the container (table).

Path

/:cluster/dbs/:database/containers/:container/rows

HTTP method

DELETE

Request header

Refer to the request header.

Request body

Example:

[
  "key1",
  "key2",
  "key3"
]

Response code

Response body

If the process is successful, nothing is returned.

Please refer to response body in case of failure.

[Memo]

• Containers with composite row keys are not supported. When the command is executed, IllegalStateException occurs.

1.11 Container creation

Create a container.

Path

/:cluster/dbs/:database/containers

HTTP method

POST

Request header

Refer to the request header.

Request body

Example:

{
  "container_name" : "container1",
  "container_type" : "collection",
  "rowkey" : true,
  "columns" : [
    {"name": "date", "type": "TIMESTAMP", "index": ["tree"]},
    {"name": "value", "type": "DOUBLE", "index": []},
    {"name": "str", "type": "STRING", "index": []}
  ]
}

Response code

Response body

If the process is successful, nothing is returned.

Please refer to response body in case of failure.

[Memo]

• Composite row keys and composite indexes are not supported.

1.12 Container deletion

Delete a container

Path

/:cluster/dbs/:database/containers

HTTP method

DELETE

Request header

Refer to the request header.

Request body

Example:

[
  "container1",
  "container2",
  "timeseries1"
]

Response code

Response body

If the process is successful, nothing is returned.

Please refer to response body in case of failure.

1.13 SQL SELECT execution

This function executes a SQL SELECT statement in a GridDB database. It can be used only for GridDB Advanced Edition.

Path

/:cluster/dbs/:database/sql

HTTP method

POST

Request header

Refer to the request header.

Request body

Specify a SQL SELECT statement as following JSON format.

Example:

[
  {"type" : "sql-select", "stmt" : "select * from container1"},
  {"type" : "sql-select", "stmt" : "select column1 from container 2 where column1>=0"},
  {"type" : "sql-select", "stmt" : "select column2, column3 from container3 order by column1 desc"}
]

Response code

Response body

Example:

[
  {
    "columns" : [
      {"name": "date", "type": "TIMESTAMP"},
      {"name": "value", "type": "DOUBLE"},
      {"name": "str", "type": "STRING"}
    ],
    "rows" : [
      ["2016-01-16T10:25:00.253Z", 100.5, "normal" ],
      ["2016-01-16T10:35:00.691Z", 173.9, "normal" ],
      ["2016-01-16T10:45:00.032Z", 173.9, null]
    ]
  },
  {
    "columns" : [
      {"name": "date", "type": "TIMESTAMP"},
      {"name": "value", "type": "DOUBLE"},
      {"name": "str", "type": "STRING"}
    ],
    "results" : [
      ["2016-01-16T10:25:00.253Z", 100.5, "normal" ],
      ["2016-01-16T10:35:00.691Z", 173.9, "normal" ],
      ["2016-01-16T10:45:00.032Z", 173.9, null]
    ]
  }
]

Depending on the column data type, column values with the following JSON data type will be returned.

[Memo]

• In case that column value is NULL, null will be returned for the column in JSON data.
• BLOB, GEOMETRY and array types are not supported in this function.
• For BLOB columns, "BLOB" will be returned as data type, and an empty string will be returned as data value in case that the column value is not NULL.
• For GEOMETRY and array columns, "UNKNOWN" will be returned as data type, and null will be returned as data value.

1.14 Checking of operation

To check the operation of the Web API, use curl command in linux or another command.

• Example) Row acquisition

  curl -X POST -u "user:password" \\
  -H "Content-type:application/json; charset=UTF-8" -d '{"limit":5,"sort":"id asc"}' \\
  http://host:port/griddb/v2/cluster/dbs/public/containers/test/rows
  

• Example) Row registration

  curl -X PUT -u "user:password" \\
  -H "Content-type:application/json; charset=UTF-8" -d '[[1,"value"],[2,"value"]]' \\
  http://host:port/griddb/v2/cluster/dbs/public/containers/test/rows
  

• Example) SQL SELECT execution

  curl -X POST -u "user:password" \\
  -H "Content-type:application/json; charset=UTF-8" -d '{"type":"sql-select","stmt":"select * from test"}' \\
  http://host:port/griddb/v2/cluster/dbs/public/sql
  

1.15 Uninstallation

Delete the directory and the distributed files in the following procedures after stopping the Web API.

# rpm -e griddb-xx-webapi

* xx indicates the GridDB edition (se, ae)