GridDB Developer
JP
Download

GridDB Community Edition v4.6 New Features

By IsraelIn BlogPosted

Introduction

With the fresh release of the GridDB Community Edition version 4.6, we would like to share with our readers the new features that we are most excited about. We will focus our efforts in highlighting the new SQL aggregate functions as well as the new command line interface (CLI) tool.

As usual, the new release is available for free on GitHub.com . To install, simply download and follow standard procedures found here: Getting Started: Using RPM/YUM .

To take full advantage of v4.6’s new features, you will also need to install the GridDB CLI tool. The command line tool is a cluster operation control command interpreter, but we will simply refer to it as gs_sh. The main purpose of this tool is to provide developers a means to manage their GridDB’s cluster and data operations.

There have also been new SQL Aggregation functions added. They won’t be covered here, but you can read about them here in our docs: https://docs.griddb.net/sqlreference/sql-commands-supported/#aggregate-functions

Installation

Installing gs_sh is extremely easy as it comes packaged with an RPM. 

$ wget https://github.com/griddb/cli/releases/download/v4.6.0/griddb-ce-cli-4.6.0-linux.x86_64.rpm
$ sudo rpm -ivh griddb-ce-cli-X.X.X-linux.x86_64.rpm

You will also need to have a griddb-jdbc.jar set up in your /usr/share/java/ directory. You can find info about that here: https://griddb.net/en/blog/connecting-to-griddb-via-jdbc-with-sqlworkbench-j/.

Here is a quick summary though: 

$ wget https://repo1.maven.org/maven2/com/github/griddb/gridstore-jdbc/4.5.0/gridstore-jdbc-4.5.0.jar

You can make sure you’re using the latest version of the jdbc file by checking GitHub.

That should be about it on the installation side.

Cluster Operation Control Command Interpreter (gs_sh)

Features

As stated earlier, the tool exists to provide developers a means to manage their GridDB cluster and data operations. This means that using this tool will allow

  • Displaying cluster status
  • Database and user management
  • Displaying collections and triggers
  • Index setting, deleting index
  • Search using TQL/SQL

Usage

Basics

Let’s just dive right in. To do so, just type directly into your shell: 

$ gs_sh
gs> version

gs_sh-ce version 4.6.0

Now that you’ve confirmed it works, let us first tackle some prerequisite steps to using this feature. For this section you will need to create a new gs user, one other than the default gsadm user and communicate to your tool your GridDB cluster definitions.

Defining GridDB Cluster

Method One: Sync

There are a couple of ways to define your nodes and cluster. If you are coming into v4.6 with a previously used GridDB instance, this first method will be easier as it uses your already defined GridDB Cluster. This method will have you simply sync over your conf/gs_cluster.json configuration. So, drop into the shell, and after you set your username and password, you can sync your config:

sync <IP Address> <Port Number> <Cluster var name> <node variables> 

gs> setuser admin admin
gs> sync 127.0.0.1 10040 defaultCluster node0

And then you can run the show command to make sure the details are correct: 

gs> show
Node variable:
  node01=Node[10.0.1.6:10040,ssh=22]
Cluster variable:
  defaultCluster=Cluster[name=defaultCluster,mode=MULTICAST,transaction=239.0.0.1:31999,sql=239.0.0.1:41999,nodes=($node01)]
Other variables:
  user=admin
  password=*****
  ospassword=

Note: these above settings are the “default” settings indicated by the guides over at https://docs.griddb.net.

Method Two: Start from Scratch

The 2nd method is more involved as it allows you to set all configurations prior to starting up your cluster. Instructions to follow:

First we will need to explicitly define our current cluster’s configuration. So after dropping into the gs shell, define your nodes like so:

setnode <Node variable> <IP address> <Port no.> [<SSH port no.>]

And a specific example:

gs> setnode node0 192.168.0.1 10000
gs> setnode node1 192.168.0.2 10000

Once done there, we can set any of the following cluster config: multicast, fixed list, or provider method. In this example, we will set it to multicast.

The full command looks like this:

setcluster <Cluster variable> <Cluster name> <Multicast address> <Port no.> [<Node variable> ...]

Full example: 

gs> setcluster cluster0 name 200.0.0.1 1000 $node0

If you want to add/remove nodes: 

gs> modcluster cluster0 add $node1
gs> modcluster cluster0 remove $node1

You need a new user to be able to connect to each GridDB node environment (explained below) as a user other than the admin user gsadm . To do so:

setuser <Username> <Password> 

gs> setuser admin admin

And please note, only one user can be set at a time. If you wish to change the user/password, simply rerun the above command.

Other Commands/Uses

You can set your own variables to keep valuable information handy, such as declaring your GS_PORT

gs> set GS_PORT 10000

And then you can show all variables and other useful items with show

gs> show
Node variable:
  node0=Node[192.168.0.1:10000,ssh=22]
  node1=Node[192.168.0.2:10000,ssh=22]
Cluster variable:
  cluster0=Cluster[name=name,mode=MULTICAST,transaction=200.0.0.1:1000,nodes=($node0)]
Other variables:
  user=admin
  password=*****
  ospassword=
  GS_PORT=10000

Once you are happy with your cluster definition, you can save your settings into a script: 

gs> save test.gsh

And then of course, to load: 

gs> load test.gsh

And as a small note, make sure when you run the gs_sh command, you have proper admin rights. If you get privilege errors about not being able to write to the log, the saving and loading of .gsh files will not work.

Once you’ve got your script file saved, the next time you launch into the shell, you can simply add it as one of the options to run when beginning the process: 

$ gs_sh test.gsh

Cluster Operation Controls

Here is a generalized list of what can be done with these controls:

  • Displaying SQL processing under execution (showsql)
  • Displaying executing event (showevent)
  • Displaying connection (showconnection)
  • SQL cancellation (killsql <query ID> )
Basics

For example, if you’ve got a query currently being executed, you can show it by simply typing: 

gs> showsql

an example output: 

  =======================================================================
  query id: e6bf24f5-d811-4b45-95cb-ecc643922149:3
  start time: 2019-04-02T06:02:36.93900
  elapsed time: 53
  database name: public
  application name: gs_admin
  node: 192.168.56.101:10040
  sql: INSERT INTO TAB_711_0101 SELECT a.id, b.longval FROM TAB_711_0001 a LEFT OU
    job id: e6bf24f5-d811-4b45-95cb-ecc643922149:3:5:0
    node: 192.168.56.101:10040
  #---------------------------

Data Operation in a Database

For the purposes of this blog, I am using the dataset from this other blog.

To begin, you will need to connect to the cluster which you intend to operate.

gs> connect $cluster0

Note here that the cluster name does matter, so in my specific case (following the sync instructions above), I ran : 

gs> connect $defaultCluster
The connection attempt was successful(NoSQL).
The connection attempt was successful(NewSQL).

Now that we are connected, we can try running some queries.

To run a TQL Command via shell: 

gs[public]> tql LosAngelesNO2 select *;

4,344 results. (0 ms)

gs[public]> get 1

timestamp,notwo
2019-01-01T00:00:00.000Z,1.4
The 1 results had been acquired.

And for SQL: 

gs[public]> sql select * from LosAngelesNO2;

4,344 results. (1 ms)

gs[public]> get 10

timestamp,notwo
2019-01-01T00:00:00.000Z,1.4
2019-01-01T01:00:00.000Z,1.6
2019-01-01T02:00:00.000Z,3.5
2019-01-01T03:00:00.000Z,1.3
2019-01-01T04:00:00.000Z,1.3
2019-01-01T05:00:00.000Z,1.0
2019-01-01T06:00:00.000Z,1.9
2019-01-01T07:00:00.000Z,3.0
2019-01-01T08:00:00.000Z,3.4
2019-01-01T09:00:00.000Z,1.5
The 10 results had been acquired.

The first command is a search, and the second actually produces the results.

You can also save your queries into a CSV file: 

gs[public]> getcsv test.csv 1000

The 1,000 results had been acquired.
The 1,000 results had been acquired.

And when finished, you can just close all dangling connections: 

gs[public]> tqlclose
gs[public]> queryclose
gs[public]> disconnect
Disconnected connection attempt was successful.
Container Management

You can also create/delete/update your containers via the shell.

To create a collection container:

createcollection <Container name> <Column name> <Column type> [<Column name> <Column type> ...]

gs[public]> createcollection test col01 string

And a timeseries collection:

createtimeseries <Container name> <Compression method> <Column name> <Column type> [<Column name> <Column type> ...]

gs[public]> createtimeseries testTS NO colTS timestamp

and then to drop a container: 

gs[public]> dropcontainer col01

Another useful container feature is the showcontainer command

gs[public]> showcontainer

        Database    : public
        Name        : LosAngelesNO2
        Type        : TIME_SERIES
        Partition ID: 24
        DataAffinity: -
        
        Compression Method : NO
        Compression Window : -
        Row Expiration Time: -
        Row Expiration Division Count: -
        
        Columns:
        No  Name                  Type            CSTR  RowKey   Compression
        ------------------------------------------------------------------------------
         0  timestamp             TIMESTAMP       NN    [RowKey]
         1  notwo                 DOUBLE

To search through your containers and find them: 

gs[public]> searchcontainer LosAngelesNO2

        LosAngelesNO2

        gs[public]> searchcontainer LosAngeles%

        LosAngelesNO2
        LosAngelesCO

Note: similar commands exist for rows: putrow and removerow.

Indexes

Creating indexes is simple:

createindex <Container name> <Column name> <Index type> ...

gs[public]> createindex LosAngelesNO2 notwo tree
        gs[public]> showcontainer LosAngelesNO2
        Database    : public
        Name        : LosAngelesNO2
        Type        : TIME_SERIES
        Partition ID: 24
        DataAffinity: -
        
        Compression Method : NO
        Compression Window : -
        Row Expiration Time: -
        Row Expiration Division Count: -
        
        Columns:
        No  Name                  Type            CSTR  RowKey   Compression
        ------------------------------------------------------------------------------
         0  timestamp             TIMESTAMP       NN    [RowKey]
         1  notwo                 DOUBLE
        
        Indexes:
        Name        :
        Type        : TREE
        Columns:
        No  Name
        --------------------------
         0  notwo

And then to delete an index: 

gs[public]> dropindex LosAngelesNO2 notwo tree
        gs[public]> showcontainer LosAngelesNO2
        Database    : public
        Name        : LosAngelesNO2
        Type        : TIME_SERIES
        Partition ID: 24
        DataAffinity: -
        
        Compression Method : NO
        Compression Window : -
        Row Expiration Time: -
        Row Expiration Division Count: -
        
        Columns:
        No  Name                  Type            CSTR  RowKey   Compression
        ------------------------------------------------------------------------------
         0  timestamp             TIMESTAMP       NN    [RowKey]
         1  notwo                 DOUBLE

Conclusion

Though there are a few more features which did not make it to this blog, this will give you enough information to be excited to use all of the new features included with the latest release. To read more about the full feature list, you can check out the full specification list on GitHub.

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.