On this page

Create table

Create table – Table creation requires to define the properties of table hence we need to send a json doc specifying the details.
Please see more about the specific of table that could be defined in this documentation

Method : POST
URI : /db/<dbname>/table
Body :

{
"table_name": "< table name >",
"table_type": <table_type>, // WIDE_TABLE (1), NORMAL_TABLE (0) etc...
"key_type": <key_type>, // string or long (for key type)
"key_sz": <key_size_bytes>, // if key_type is string then size of key, dfault is 24 in this case
"allow_duplicate": <0 or 1>,
"allow_rev_idx": <0 or 1>,
"version_type": <0 or 1>
}

Note: Not all params are required for table creation and there could be many more attributes here as we create table , please see the detail documentations for the same

Here is some information regarding these parameters.

• You don’t need to provide all these info unless you are sure, otherwise default is good
• The required (must) attributes are.

◦ table name : alphanumeric and “_”(underscore) is allowed. Should be less than 64 bytes (ideally)
◦ table type:

▪ NORMAL_TABLE (0) : for key/val scenario where val is opaque
▪ WIDE_TABLE (1) : when you wish to store json document. Secondary, nested, composite, geo, reverse indexing can be created for only WIDE_TABLE
▪ INDEX_TABLE (2) : only for db internal use, don’t create this
▪ PRIMITIVE_INT (3) : when key and value are integer types
▪ PRIMITIVE_TABLE_LONG (4) : when key and value are long types
▪ PRIMITIVE_TABLE_STRING (5) : when key is string type and value is long type
▪ LARGE_TABLE (6) : Don’t use directly, create bucket instead. Bucket uses this table type

◦ key type:

▪ NORMAL_KEY (1) : when key is of string type (variable size)
▪ COMPOSITE_KEY (3) : when key is composite, for ex; “city:area” etc. (variable size)
▪ NORMAL_KEY_LONG (10) : when key is of long type (fixed size)

Note: for variable key type, we may also provide key size, default is 24 bytes.
Please note that it’s important to keep key size as low as possible as it has direct impact on db performance in many ways. ideal size is 8 bytes to 64 bytes (or in some case upto 128 bytes). Please consider structuring your table such that its key size is minimum

◦ allow duplicate: set it as 1 if you wish to allow duplicate PKs
◦ allow reverse index : set it as 1 if you wish to enable reverse indexing

There are more attributes that can be set, please se table_env documentation

Example

A. let’s create WIDE_TABLE , with key size 24 (key type as NORMAL_KEY) etc.

curl -H "Content-Type: application/json" -d '{"table_name":"my_table_test","table_type":1,"key_type":1,"key_sz":24,"allow_duplicate":1,"allow_rev_idx":0,"version_type":1}' -X POST http://192.168.1.105:18080/db/mydb/table

Response

{
"msg": "successfully created the table"
}

To insert data into WIDE_TABLE we can use the following API

API : POST /db/<dbname>/<table_name>/doc
Body :

{"doc":{"name":"john"}, "_pk":"p1"} // _pk outside doc, DB will take this as _pk
{"doc":{"name":"john", "_pk":"p1"}} // _pk inside doc, DB will take this as _pk
{"doc":{"name":"john"}} // _pk is missing, DB will create one (timestamp in microsec)

Note:
> If key_type = 1 (NORMAL_KEY) or 3 (COMPOSITE_KEY) then pass “_pk” value as string, else pass it
as long (for NORMAL_KEY_LONG)
> Also note that the value of key “doc” is going as object (not string) this is because we must insert document (json) for WIDE_TABLE

B. Let’s create NORMAL_TABLE now, with long key type

curl -H "Content-Type: application/json" -d '{"table_name":"my_table_test2","table_type":0,"key_type":10,"allow_duplicate":1,"allow_rev_idx":0,"version_type":1}' -X POST http://192.168.1.105:18080/db/mydb/table

To insert data into NORMAL_TABLE we can use following

API : POST /db/<dbname>/<table_name>/doc
Body:

{"doc":"this is a random opaque value", "_pk":1}

Note: > we must provide “_pk” else call will fail. And note that value of key “doc” is string, we can pass anything here

C. There is another way, which is unique for all table types and it’s using sql like statement

API : /db/<dbname>/query
Body :

{"sql":"insert statement ..."}

To insert into WIDE_TABLE, we can use following as body

{"sql" : "insert into my_table_test values \"p1\" {\"name\":\"john\"}"}

To insert into NORMAL_TABLE

{"sql" : "insert into my_table_test2 values 1 \" this is a random opaque value \""}

Response

{
"retval": 0
}

This returns 0 or positive number for success else negative value for error

Note: using the same API , you can run select query to retrieve the data
ex.

API : /db/<dbname>/query
Body :

{"sql":"insert statement ..."}

Example.

{"sql" : "select * from my_table_test"}
{"sql" : "select * from my_table_test2"}

etc.

Was this article helpful to you? Yes No