Background filter query

A common use case for a background query is to perform maintenance or cleanup operations on a database. Unlike production queries, which are highly optimized, maintenance operations may involve scans of the entire database for records which meet some criteria.

Create a filter expression which selectively retrieves records. You can filter by:
- Record data, using a record expression.
- Record metadata, including record size, time-to-live (TTL), last update time, and others, using a metadata expression.
Process the query in the background.
Perform one or more operations on the returned records.

Record operations include:
- Modify a list or map.
- Perform math operations on a bin.
- Delete a bin from a record or add a bin to a record.
- Delete an entire record.

Scans which go through an entire dataset can take a long time. Aerospike provides tools for monitoring long-running queries. See Query Management for more information.

The following example creates a filter expression which identifies all records which do not have a TTL (time to live) value set. The TTL value specifies a duration in seconds after which the record expires and is removed from the set.

private static void touchByTTL(AerospikeClient client) {

        Statement stmt = new Statement();

        stmt.setNamespace(namespace);

        stmt.setSetName(mySet);

        WritePolicy policy = new WritePolicy();

        // Create the expression
        policy.filterExp = Exp.build(Exp.eq(Exp.ttl(), Exp.val(-1)));

        // Touch each record found by the expression
        ExecuteTask task = client.execute(policy, stmt, Operation.touch());

        System.out.println("Touched records with TTL equals to -1");

}

For monitoring purposes, you must have the task ID of a particular query. The following example displays the task ID of the current job:

System.out.println("task.id: " + task.getTaskId());

Output:

-> task.id: 5234562535299509836

Create a filter expression which selectively retrieves records. You can filter by:
- Record data, using a record expression.
- Record metadata, including record size, time-to-live (TTL), last update time, and others, using a metadata expression.
Process the query in the background.
Perform one or more operations on the returned records.

Record operations include:
- Modify a list or map.
- Perform math operations on a bin.
- Delete a bin from a record or add a bin to a record.
- Delete an entire record.

Scans which go through an entire dataset can take a long time. Aerospike provides tools for monitoring long-running queries. See Query Management for more information.

package main

import (
    "fmt"
    as "github.com/aerospike/aerospike-client-go/v7"
    "github.com/aerospike/aerospike-client-go/v7/expr"
)

func touchByTTL(client *as.Client, namespace string, mySet string) {
    stmt := as.NewStatement(namespace, mySet)

    policy := as.NewWritePolicy(0, 0)

    // Set filter expression to match TTL == -1
    policy.FilterExpression = expr.ExpEq(
        expr.ExpTTL(),
        expr.ExpIntVal(-1),
    )

    // Perform touch operation on all matching records
    task, err := client.Execute(policy, stmt, as.NewOperation(as.TouchOp()))
    if err != nil {
        fmt.Println("Error executing touch operation:", err)
        return
    }

    // Optionally wait for completion
    err = <-task.OnComplete()
    if err != nil {
        fmt.Println("Error waiting for task completion:", err)
    } else {
        fmt.Println("Touched records with TTL equals to -1")
    }
}

For monitoring purposes, you must have the task ID of a particular query. The following example displays the task ID of the current job:

func (etsk *ExecuteTask) TaskId() uint64

Create a filter expression which selectively retrieves records. You can filter by:
- Record data, using a record expression.
- Record metadata, including record size, time-to-live (TTL), last update time, and others, using a metadata expression.
Process the query in the background.
Perform one or more operations on the returned records.

Record operations include:
- Modify a list or map.
- Perform math operations on a bin.
- Delete a bin from a record or add a bin to a record.
- Delete an entire record.

Scans which go through an entire dataset can take a long time. Aerospike provides tools for monitoring long-running queries. See Query Management for more information.

import aerospike
from aerospike_helpers import expressions as exp
from aerospike_helpers.operations import operations
# Define host configuration
config = {
    'hosts': [ ('127.0.0.1', 3000) ]
}
# Establishes a connection to the server
client = aerospike.client(config).connect()
query = client.query('namespace', 'my_set')

#the filter policy to check if there is no ttl
noTtlSet = exp.Eq(exp.TTL(), -1).compile()
policy = {
    "expressions": noTtlSet

}

# built in operation to touch the record
ops = [
    operations.touch()
]

# execute the background operation
query.add_ops(ops)

id = query.execute_background(policy=policy)

print("Job submitted:", id)

For monitoring purposes, you must have the task ID of a particular query. The following example displays the status the task:

job_info = client.job_info(id, aerospike.JOB_QUERY)

def query_status(x):
    return{
        0: 'Query not found',
        1: 'Query in progress, ' + str(job_info['progress_pct']) + '% complete',
        2: 'Query complete, ' + str(job_info['records_read']) + ' records read'
    }[x]

print(id, " ", query_status(job_info['status']))

Query management

Use task ID of your background query with the Aerospike admin tool to manage the job, or terminate it.

Admin+> show jobs queries trid 5234562535299509836
~~~~~~~~~~~~~Query Jobs (2024-11-30 23:21:42 UTC)~~~~~~~~~~~~~
Node              |mycluster-1:3000    |172.17.0.2:3000
Namespace         |test                |test
Module            |query               |query
Type              |basic               |basic
Progress %        |100.0               |100.0
Transaction ID    |5234562535299509836 |5234562535299509836
Time Since Done   |00:12:51            |00:12:51
active-threads    |0                   |0
from              |127.0.0.1+60036     |172.17.0.3+55372
n-pids-requested  |2.048 K             |2.048 K
net-io-bytes      |30.000 B            |149.000 B
net-io-time       |00:00:00            |00:00:00
recs-failed       |0.000               |0.000
recs-filtered-bins|0.000               |0.000
recs-filtered-meta|0.000               |0.000
recs-succeeded    |0.000               |1.000
recs-throttled    |0.000               |0.000
rps               |0.000               |0.000
run-time          |00:00:00            |00:00:00
set               |testset             |testset
sindex-name       |mysindex            |mysindex
socket-timeout    |00:00:30            |00:00:30
status            |done(ok)            |done(ok)
Number of rows: 23

Admin+> manage jobs kill trids 1343444200604843206 9156474088110606100
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Kill Jobs (2024-10-20 23:57:22 UTC)~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
         Node|     Transaction ID|Namespace|Module| Type|                            Response
10.0.0.1:3000|9156474088110606100|      bar|scan  |basic|ok
10.0.0.2:3000|1343444200604843206|      bar|query |basic|Failed to kill job : job not active.
Number of rows: 1