Skip to main content
We are excited to be a part of AWS re:Invent 2024. Visit us at booth #1844 in Las Vegas.More info
Loading

Record Operations

Insert a record​

The following command inserts a record:

INSERT INTO <ns>[.<set>] (PK, <bins>) VALUES (<key>, <values>)

Where

  • <ns> is the namespace for the record.
  • <set> is the set name for the record.
  • <key> is the record's primary key.
  • <bins> is a comma-separated list of bin names.
  • <values> is comma-separated list of bin values, which may include type cast expressions. Set to NULL (case insensitive & w/o quotes) to delete the bin.

Example:

aql> INSERT INTO test.testset (PK, a, b) VALUES ('xyz', 'abc', 123)

Delete a Record​

The following command deletes a record:

DELETE FROM <ns>[.<set>] WHERE PK=<key>

Where

  • <ns> is the namespace for the record.
  • <set> is the set name for the record.
  • <key> is the record's primary key.

Example:

aql> DELETE FROM test.testset WHERE PK='xyz'

Get a record using DIGEST​

For tools version 3.5.11 and above, use the following command to get a record using the DIGEST:

When providing the HEX representation of the digest (for example from the server logs), use DIGEST :

SELECT * FROM <ns>[.<set>] WHERE DIGEST='<DIGEST_HEX_STRING>'

When providing the Base64 representation of the digest (for example from asbackup file), use EDIGEST :

SELECT * FROM <ns>[.<set>] WHERE EDIGEST='<DIGEST_B64_STRING>'

Where

  • <ns> is the namespace for the record.
  • <set> is the set name for the record.
  • <DIGEST_HEX_STRING> is the hexadecimal representation of the record's digest.
  • <DIGEST_B64_STRING> is the Base64 representation of the record's digest.

Example:

aql> SELECT * FROM test.testset where DIGEST='139FE89822B63DFC173AEA51CCF2EF091AB3129F'
+---------+---------+-----------------------------------+---------------+-------+
| bin1    | bin2    | bin3                              | LDTCONTROLBIN | binl1 |
+---------+---------+-----------------------------------+---------------+-------+
| "val01" | "val02" | ["string1", "string2", "string3"] |               |       |
+---------+---------+-----------------------------------+---------------+-------+
1 row in set (0.000 secs)
aql> SELECT * FROM test.testset where EDIGEST='E5/omCK2PfwXOupRzPLvCRqzEp8='
+---------+---------+-----------------------------------+---------------+-------+
| bin1    | bin2    | bin3                              | LDTCONTROLBIN | binl1 |
+---------+---------+-----------------------------------+---------------+-------+
| "val01" | "val02" | ["string1", "string2", "string3"] |               |       |
+---------+---------+-----------------------------------+---------------+-------+
1 row in set (0.000 secs)

Define datatype​

You must specify the datatype of a bin value (e.g. MAP, LIST, GeoJSON etc) when you create the record. Once created, all the record operations such as INSERT, DELETE and queries are valid. The two options of specifying are:

1) Use the datatype by defining itself as is.

INSERT INTO test.testset(PK, a,b) VALUES ('xyz9', 'abc10', MAP('{"map":1, "of":2, "items":3}'))

INSERT INTO test.demo (PK, gj) VALUES ('key1', GEOJSON('{"type": "Point", "coordinates": [123.4, -456.7]}'))

1) Use the CAST expression to define the datatype.

INSERT INTO test.testset (PK, a, b) VALUES ('xyz11', 'abc11', CAST('{"map":100, "of":200, "items":300}' AS MAP))

INSERT INTO test.testset(PK, a,b) VALUES ('xyz12', 'abc12', CAST('{"type": "Point", "coordinates": [123.4, -456.7]}' as GEOJSON))

Truncate data​

danger

The TRUNCATE command is removed as of Database 7.0. Use asadm's manage truncate command instead.

Last updated on