Background filter query

A common use case for a background query — see the guide for Java, Go, or Python — 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.

Production impact

Background filter queries can scan and mutate many records. Run against a test namespace or after a verified backup, use narrow filters, and monitor or cancel jobs with asadm. recordsPerSecond can limit the throughput on long running queries.

• Optionally narrow the scan before evaluating any filter expression:

  • Limit it to a single set, as the examples below do (stmt.setSetName(mySet) in Java, the mySet argument to as.NewStatement() in Go, or the my_set argument to client.query() in Python).
  • Filter on a secondary index, if a suitable index exists on the set — see the guide for Java, Go, or Python. The server applies the secondary index filter before the filter expression.

• Create a filter expression which selectively retrieves records. You can combine any of the following in a single filter expression:

  • 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 — see Java, Go, or Python.
  • Delete an entire record — see Java, Go, or Python.

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.

Java

private static void touchByTTL(AerospikeClient client) {

        Statement stmt = new Statement();

        stmt.setNamespace(namespace);
        stmt.setSetName(mySet);

        WritePolicy policy = client.copyWritePolicyDefault();

        // 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");
        System.out.println("task.id: " + task.getTaskId());

}

Go

package main

import (
    "fmt"
    as "github.com/aerospike/aerospike-client-go/v8"
    "github.com/aerospike/aerospike-client-go/v8/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")
    }
}

Python

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)

Note

execute_background() returns as soon as the job is submitted; it does not wait for the query to finish. Before assuming the touch operations are complete, poll job status with client.job_info() (see below) or check progress with asadm.

For monitoring purposes, you must have the task ID of a particular query.

Java

ExecuteTask.getTaskId() returns the task ID of the current job. The touchByTTL() method above already prints it right after client.execute(...):

Touched records with TTL equals to -1
task.id: 5234562535299509836

Go

The following example displays the task ID of the current job:

func (etsk *ExecuteTask) TaskId() uint64

Python

The following example displays the status of 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']))

Starting with Aerospike Database 8.2, the Developer SDK (Java and Python) accepts the same filter as AEL (Aerospike Expression Language) text passed to .where(), instead of a compiled expression. See the AEL overview and AEL reference for the full grammar.

Note

These snippets assume a Developer SDK session created separately, as described in the AEL overview. They are not standalone runnable examples.

Java

DataSet records = DataSet.of(namespace, mySet);
ExecuteTask task = session.backgroundTask()
    .touch(records)
    .where("$.ttl() == -1")
    .execute();

Go

Note

AEL text authoring is available for the Developer SDK (Java and Python) only. Go continues to use the expr.* builder shown above.

Python

records = DataSet.of('namespace', 'my_set')
task = await (
    session.background_task()
    .touch(records)
    .where("$.ttl() == -1")
    .execute()
)

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