GridDB WebAPI | GridDB: Open Source Time Series Database for IoT

The GridDB WebAPI makes it simple to access GridDB data across the internet or from a programming language/envrionment where a GridDB connection does not exist or isn’t feasible to use. In this blog post, we’re going to showcase installation, some brief API examples with CURL, Postman, and R as well as how to setup the WebAPI behind a reverse proxy to enable SSL or other HTTP security features not directly supported by the WebAPI server.

Install

We’ll assume that you have already installed and setup GridDB. To download and untar the binary tarball from GitHub: https://github.com/griddb/webapi/releases/download/2.2.0/griddb_webapi-2.2.0-bin.tar.gz

$ sudo su - gsadm
$ cd /var/lib/gridstore
$ curl -o https://github.com/griddb/webapi/releases/download/2.2.0/griddb_webapi-2.2.0-bin.tar.gz
$ tar zxvf griddb_webapi-2.2.0-bin.tar.gz

Now edit webapi/conf/repository.json, changing the name parameter to your cluster name, usually “defaultCluster”, and making any other changes as required.

Now you can start the WebAPI:

$ nohup java -jar lib/griddb-webapi-ce-2.2.0.jar &

By using nohup, the WebAPI processs won’t die when your terminal exits for any reason.

Inserting Data

First, we create the container schema:

$ curl -X POST --basic -u admin:admin -H "Content-type:application/json"  http://127.0.0.1:8080/griddb/v2/defaultCluster/dbs/public/containers -d \
'{
    "columns": [
        {
            "name": "timestamp",
            "type": "TIMESTAMP"
        },
        {
            "name": "name",
            "type": "STRING"
        },
        {
            "name": "value",
            "type": "FLOAT"
        }
    ],
    "container_name": "test",
    "container_type": "TIME_SERIES",
    "rowkey": true
}'

Now we can PUT our data rows:

$ curl -X PUT --basic -u admin:admin -H "Content-type:application/json"  http://127.0.0.1:8080/griddb/v2/defaultCluster/dbs/public/containers/test/rows -d \
'[
    ["2021-01-16T10:25:00.253Z", "foo", 100.5 ],
    ["2021-01-16T10:35:00.691Z", "foo", 173.9 ],
    ["2021-01-16T10:45:00.032Z", "bar", 123.9 ]
]'
{"count":3}

Fetching Data

We can now directly fetch or query data; direct fetches are the fastest method to retrieve data:

$ curl -X POST --basic -u admin:admin -H "Content-type:application/json" http://127.0.0.1:8080/griddb/v2/defaultCluster/dbs/public/containers/test/rows -d  '{"limit":1000}'  
{"columns":[{"name":"timestamp","type":"TIMESTAMP"},{"name":"name","type":"STRING"},{"name":"value","type":"FLOAT"}],"rows":[["2021-01-16T10:25:00.253Z","foo",100.5],["2021-01-16T10:35:00.691Z","foo",173.9],["2021-01-16T10:45:00.032Z","bar",123.9]],"offset":0,"limit":1000,"total":3}[owen@hpelnxdev webapi_blog]$

Using TQL allows you to use all of GridDB’s time series or aggregation functions:

$ curl -X POST --basic -u admin:admin -H "Content-type:application/json" http://127.0.0.1:8080/griddb/v2/defaultCluster/dbs/public/tql -d \
 '[{"name":"test", "stmt":"select max(value)", "columns":[]}]' 

[{"columns":[{"name":"aggregationResult","type":"DOUBLE"}],"results":[[173.89999389648438]]}]

The WebAPI also supports querying with more familiar SQL statements:

$ curl -X POST --basic -u admin:admin -H "Content-type:application/json" http://127.0.0.1:8080/griddb/v2/defaultCluster/dbs/public/sql -d \
'[{"type":"sql-select","stmt":"select * from test"}]' 
[{"columns":[{"name":"timestamp","type":"TIMESTAMP"},{"name":"name","type":"STRING"},{"name":"value","type":"FLOAT"}],"results":[["2021-01-16T10:25:00.253Z","foo",100.5],["2021-01-16T10:35:00.691Z","foo",173.9],["2021-01-16T10:45:00.032Z","bar",123.9]]}]

Deleting Data

You can delete both entire containers or individual rows. To delete individual or multiple rows:

$ curl -X DELETE --basic -u admin:admin -H "Content-type:application/json" http://127.0.0.1:8080/griddb/v2/defaultCluster/dbs/public/containers/test/rows -d \
'[
    "2021-01-16T10:25:00.253Z",
    "2021-01-16T10:35:00.691Z"
]'

$ curl -X DELETE --basic -u admin:admin -H "Content-type:application/json"  http://127.0.0.1:8080/griddb/v2/defaultCluster/dbs/public/containers -d \
'[
    "test"
]'

R

R is a popular programming language for data analysis and the WebAPI makes it easy use data stored in GridDB into your R program. Using the HTTR library, we can use the same endpoints we used with curl and then we can convert the returned JSON into a R data frame for calculation:

library('httr')

r <- POST(
    url = "http://127.0.0.1:8080/griddb/v2/defaultCluster/dbs/public/containers/test/rows",
    config = authenticate("admin", "admin"),
    encode = "json",
    body = '{"limit" : 1000 }',
    add_headers("Content-Type" = "application/json")

)
df <- as.data.frame(do.call(rbind, content(r)$rows))
colnames(df) <- c("timestamp", "name", "value")
df$value <- as.numeric(as.character(df$value))

print(df)
print(mean(df$value))

The above R script outputs:

timestamp name value
1 2021-01-16T10:25:00.253Z  foo 100.5
2 2021-01-16T10:35:00.691Z  foo 173.9
3 2021-01-16T10:45:00.032Z  bar 123.9
[1] 132.7667

Conclusion

The GridDB WebAPI makes it simple to access data stored in GridDB from remote networks without complicated VPNs or from environments where it is not possible to use one of the many programming language specific GridDB clients. All scripts used in this post can be downloaded from the GridDB.net GitHub Repo.

If you have any questions about the blog, please create a Stack Overflow post here https://stackoverflow.com/questions/ask?tags=griddb .
Make sure that you use the “griddb” tag so our engineers can quickly reply to your questions.