Manage records

This page describes Aerospike Quick Look (AQL) commands to manage records in a database.

INSERT INTO

To insert a record, use the following command:

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

The command contains the following arguments:

<ns> : Namespace for the record.
<set> : Name of the set for the record.
<key> : Record’s primary key.
<bins> : Comma-separated list of bin names.
<values> : Comma-separated list of bin values, which may include type cast expressions. Set to NULL to delete the bin.

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

DELETE FROM

To delete a record, use the following command:

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

The command contains the following arguments:

<ns> : Namespace for the record.
<set> : Name of the set for the record.
<key> : Record’s primary key.

DELETE FROM test.testset WHERE PK='xyz'

EXPLAIN SELECT

To look at the details of a specific record, use the following command. You must use the primary key of the record:

EXPLAIN SELECT * FROM namespaceName.setName WHERE PK=valueOfPrimaryKey

Results for the valueOfPrimaryKey object are displayed as a table by default.

To display results in JSON format, you must set the output mode:

SET OUTPUT JSON
OUTPUT = JSON

For other allowable values, enter HELP SET.

You can also specify outputmode = 'outputType' in your configuration file.

SELECT * FROM

To query all records from a specific namespace and set, use the following primary index (PI) query:

SELECT * FROM <ns>[.<set>]

The command contains the following arguments:

<ns> : Namespace.
<set> : Name of the set.

SELECT * FROM users.profiles
+---------------------------+-----+--------+
| name                      | age | gender |
+---------------------------+-----+--------+
| "Bob White"               | 22  | "M"    |
| "Annie Black"             | 28  | "F"    |
| "Sally Green"             | 19  | "F"    |
| "Ricky Brown"             | 20  | "M"    |
| "Tammy Argent"            | 22  | "F"    |
+---------------------------+-----+--------+
5 rows in set (0.000 secs)

Starting with Tools 3.8.2, SELECT * prints the primary key of a record in a column ‘PK’ if it was sent to the server when the record was written.

SELECT < bin >

To query project-specific bins using a primary index query against all records in a specific namespace and set, enter the following:

SELECT <bin>[, <bin>[, ...]] FROM <ns>[.<set>]

The command contains the following arguments:

<ns> : Namespace.
<set> : Name of the set.
<bin> : Indicates whether one or more bins are returned from the records.

SELECT name, age FROM users.profiles
+---------------------------+-----+
| name                      | age |
+---------------------------+-----+
| "Bob White"               | 22  |
| "Annie Black"             | 28  |
| "Sally Green"             | 19  |
| "Ricky Brown"             | 20  |
| "Tammy Argent"            | 22  |
+---------------------------+-----+
5 rows in set (0.000 secs)

SELECT < bin > FROM < ns >

To filter all records in a specific namespace and specific set based on a predicate, enter the following command:

SELECT <bin>[, <bin>[, ...]] FROM <ns>[.<set>] [IN indextype] [WHERE <predicate>] [AND <predicate2>] [LIMIT <limit>]

To reduce the number of records returned, use the LIMIT <limit> specifier. Use this specifier when you want to return an entire namespace or set. For example:

SELECT * from users.profiles LIMIT 100

The index type specifier IN indextype is required in case the secondary index is on LIST, MAPKEYS, or MAPVALUES. If not specified, the specifier defaults to index on basic index on the bin itself.

When filtering records, consider the following:

The argument <predicate> must be one of the supported predicates for the secondary index.
For NUMERIC indexes, either a range predicate or equality predicate can be applied.
For STRING indexes, only equality predicates are supported.
When using a double where clause both predicates, <predicate> and <predicate2>, must be equality operations, and one of the bins in either predicate must have a sindex defined.

If age has a NUMERIC secondary index defined, this is a valid SELECT command:

SELECT name, age, city FROM users.profiles WHERE age 20 and city = "San Francisco"

Enter a predicate in the following forms, depending on the datatype:

For Numeric and String, this states that the bin < bin > must be equal to < value > : <bin> = <value>.
For Numeric, this states that the bin < bin > must fall with then range between < lower > and < upper > (inclusive): <bin> BETWEEN <lower> AND <upper>.
For GeoJSON, this returns value containing specific location points: <bin> CONTAINS <GeoJSONPoint>.
For GeoJSON, this returns values within a specified range of points: <bin> WITHIN <GeoJSONPolygon>.

The following is an example of a GeoJSON range SI query:

SELECT * FROM test.demo WHERE gj CONTAINS CAST('{"type": "Point", "coordinates": [0.0, 0.0]}' AS GEOJSON)

An equality predicate is in the form which states that the bin <bin> must equal <value> :

<bin> = <value>

To retrieve a record for a specific primary key:

PK = <key>

In this example, there is a secondary index on the age bin, and there is an SI query to identify all people between the age of 20 and 29:

SELECT name, age FROM users.profiles WHERE age BETWEEN 20 AND 29
+---------------------------+-----+
| name                      | age |
+---------------------------+-----+
| "Bob White"               | 22  |
| "Annie Black"             | 28  |
| "Ricky Brown"             | 20  |
| "Tammy Argent"            | 22  |
+---------------------------+-----+
4 rows in set (0.000 secs)

This SI query assumes a secondary index was created using the following command:

CREATE INDEX user_age_idx ON users.profiles (age) NUMERIC

AGGREGATE

To perform an aggregation on SI query results, enter the following command:

AGGREGATE <module>.<function>([<arg>[,...]]) ON <ns>[.<set>] [IN indextype] WHERE <predicate>

Remove WHERE <predicate> to run aggregation on Primary Index (PI) query results:

AGGREGATE <module>.<function>([<arg>[,...]]) ON <ns>[.<set>]

This query specifies a Stream UDF to execute against the results of an SI query.

The command contains the following arguments:

<module> is the UDF module name.
<function> is the Stream UDF function name.
<arg> are the arguments for the UDF function. Multiple arguments can be specified.
<predicate> is the same predicate used for querying.

In the following example, the SI query has an aggregation stream applied to it called avg_age. This has the appropriate sub-functions required to define the different stages of the stream (function female, name_age, and eldest). This set of functions are included in the UDF module named profile_aggregator :

AGGREGATE profile_aggregator.avg_age() ON users.profiles WHERE age BETWEEN 20 and 29
+--------------------------------------+
+ avg_age                        |
+--------------------------------------+
+ { "name": "Annie Black", "age": 28 } |
+--------------------------------------+

This is the output of a registered UDF module named profile_aggregator and which contains a avg_age function:

function avg_age(stream)

  local function female(rec)
    return rec.gender == "F"
  end

  local function name_age(rec)
    return map{ name=rec.name, age=rec.age }
  end

  local function eldest(p1, p2)
    if p1.age > p2.age then
      return p1
    else
      return p2
    end
  end

  return stream : filter(female) : map(name_age) : reduce(eldest)
end

The stream operations performs the following:

Filters female users.
Converts each profile record into a map containing only a name and age.
Reduces each profile to eldest female.

Get a record using 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>'

The DIGEST command provides the HEX representation of the digest (for example from the server logs).

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

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

The command contains the following arguments:

<ns> is the namespace for the record.
<set> : Name of the set 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.

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

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 when you create the record. Possible options are MAP, LIST, GeoJSON, and so on. Once you’ve created a record, you can begin to use record operations such as INSERT, DELETE, or SELECT.

You can specify the data type in two ways:

Define the datatype directly:

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]}'))

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))