Multiple rows delete: how to write a delete query

Thank you for your answer. After query, I have a RecordSet about 2M records, now I want to remove all these records . Can you tell me how I can do this with best performance ?

This is my code to do this but it’s very slow (200s to remove 2M record).

    AerospikeClient mAsClient = new AerospikeClient(Const.AEROSPIKE_HOST, Const.AEROSPIKE_PORT);
    mAsClient.register(null, "udf/aggregate.lua", "aggregate.lua", Language.LUA);
    Statement stmt1 = new Statement();
    stmt1.setNamespace("test");
    stmt1.setSetName("network");
    stmt1.setBinNames("id");

    RecordSet resultSet = mAsClient.query(null, stmt1);
    while (resultSet.next()) {
        String id = resultSet.getRecord().getString("id");
        Statement stmt = new Statement();
        stmt1.setNamespace("test");
        stmt1.setSetName("network");
        stmt.setFilters(Filter.equal("id", id));
        ExecuteTask task = mAsClient.execute(null, stmt, "aggregate", "deleteRecord");
        System.out.println("Status = " + task.queryStatus());
    }

and my lua :

function deleteRecord(r)
    aerospike:remove(r)
end

As I pointed out, you do not want to get a RecordSet, you want to apply a record UDF to each that matches your criteria. The UDF has a single line removing the record it’s being applied to.

Here’s a similar example of modifying the TTL of all records that match a specific criteria. You would change the Lua code from trying to modify the TTL and update the record to an aerospike:remove(rec) call.

Thank you @zbotzer , I will try it.

I tried your example in my issue and it’s working perfect. Thank you so much .

Hello zbotzer, I removed records from aerospike by update TTL = 1 to record. But the records still on Ram and after few seconds( ~ 10s ) , the records have gone from Ram. I want after update TTL then record has gone immediate so how I can do that ?

If you set the TTL to 1s, it will expire one second after being written. The record cannot be accessed by a get once it is expired, but it still needs the nsup thread to clean it up from the primary index. It’ll consume the 64B of DRAM in the primary index until it’s removed by the namespace supervisor.

Instead of changing the TTL to 1 second in order to remove a record, your code should call aerospike:remove(rec). See the aerospike module of the Lua UDF API. That will immediately remove the record’s metadata from the primary index.

What you were doing before (rather than updating the TTL) should work.

Thanks rbotzer , After changed aerospike:update() to aerospike:remove() , it’s working perfect as I want .

Thanks so much.

hi ronen, is the queryapply on secondary index already in the release?

Long time ago

Check out the latest PHP client, and generate the PHPDoc for yourself.

Hi, don’t know if it is the case to open a new thread?

I need to know the number of record deleted (removed) from the Set. I’m using this registered UDF:

function delete(r)
    aerospike: remove(r)
end

In C# I call it using:

public void DeleteItems(IEnumerable<string> itemIds)
{
    foreach (var itemId in itemIds)
    {
        var statement = new Statement
        {
            Namespace = configuration.Namespace,
            SetName = configuration.ItemsSetName,
            Filter = Filter.Equal("Id", itemId)
        };

        Client.Execute(null, statement, removeRecordFileName, removeRecordFunctionName);
    }
}

My initial idea was to count the record before and after but seems like a SQL “select count(*)” is not so simple to do in Aerospike.

Any suggestion?

You can just do a statement with includebindata=false and then invoke the execution.

var statement = new Statement
    {
        Namespace = configuration.Namespace,
        SetName = configuration.ItemsSetName,
        Filter = Filter.Equal("Id", itemId)
    };
    QueryPolicy qp = new QueryPolicy();
    qp.includeBinData=false;
    rs=Client.query(qp, statement,);
while(rs.next()){
count++;
}

or something similar.

1 Like

How to delete a single record from AQL? Found below query from aerospike documentation but unable to get the PK of a record to delete. DELETE FROM [.] WHERE PK=

PK is what is your key - when you created a record. Aerospike (at the client library) takes your key, set name (defualt null) and you key type inferred, and hashes to a digest in the client library. The client library sends only the digest to the Aerospike server by default. So all the server has is your record’s digest against which it looks up the record’s value or data. You have to know your key.

So - DELETE FROM mynamespace.myset WHERE PK = “mykey”

Thanks Piyush.

We are trying to delete a record where we don’t know PK of it. Tried as below but getting an exception - “AEROSPIKE_ERR_FAIL_FORBIDDEN”

So, how to get the PK of existing (previously inserted) record?

aql> set record_print_metadata TRUE
RECORD_PRINT_METADATA = true

aql>

aql> set output table
OUTPUT = TABLE

aql>
aql> select * from ovn_test.testset
+---------+-----+--------------------------------+-----------+-------+-------+
| name    | age | {edigest}                      | {set}     | {ttl} | {gen} |
+---------+-----+--------------------------------+-----------+-------+-------+
| "name2" | 38  | "rBD160onhKhHpbXrAWfOHmm1xsM=" | "testset" | -1    | 1     |
| "name"  | 36  | "v2wdE+fNEMW9Ai0n598XDAvM1uE=" | "testset" | -1    | 2     |
+---------+-----+--------------------------------+-----------+-------+-------+
2 rows in set (0.282 secs)

OK

aql> select * from ovn_test.testset where edigest="v2wdE+fNEMW9Ai0n598XDAvM1uE="
+--------+-----+-------+-------+
| name   | age | {ttl} | {gen} |
+--------+-----+-------+-------+
| "name" | 36  | -1    | 2     |
+--------+-----+-------+-------+
1 row in set (0.001 secs)

OK

aql> delete from ovn_test.testset where edigest="v2wdE+fNEMW9Ai0n598XDAvM1uE="
Error: (22) AEROSPIKE_ERR_FAIL_FORBIDDEN
aql>
Aerospike Query Client
Version 3.29.0
C Client Version 4.6.17
Copyright 2012-2020 Aerospike. All rights reserved.
aql> show namespaces
+------------+
| namespaces |
+------------+
| "ovn_test" |
+------------+
[127.0.0.1:3000] 1 row in set (0.002 secs)

OK

aql> select * from ovn_test
0 rows in set (0.121 secs)

OK

aql> insert into ovn_test.testset (pk, bin1, bin2) values ("k1", 'abc', 123)
OK, 1 record affected.

aql> select * from ovn_test
+-------+------+
| bin1  | bin2 |
+-------+------+
| "abc" | 123  |
+-------+------+
1 row in set (0.118 secs)

OK

aql> set record_print_metadata true
RECORD_PRINT_METADATA = true
aql> select * from ovn_test
+-------+------+--------------------------------+-----------+-------+-------+
| bin1  | bin2 | {edigest}                      | {set}     | {ttl} | {gen} |
+-------+------+--------------------------------+-----------+-------+-------+
| "abc" | 123  | "E9xE66jt4u+dwdx5jkQjvh5trO4=" | "testset" | -1    | 1     |
+-------+------+--------------------------------+-----------+-------+-------+
1 row in set (0.117 secs)

OK

aql> delete from ovn_test.testset where edigest="E9xE66jt4u+dwdx5jkQjvh5trO4="
OK, 1 record affected.

aql> select * from ovn_test
0 rows in set (0.115 secs)

OK

aql>

I assume the user has write access? (If you are using Enterprise Edition, security is enabled.) See for details: Documentation | Aerospike

That’s interesting, thanks for testing at your end Piyush. We are using “admin” account in our case and it has read-write role (I believe it is super set to write role).

==>tls_aql
Seed:        
User:         admin
Config File:  /etc/aerospike/astools.conf /home/aerospike/.aerospike/astools.conf
Enter Password:
Aerospike Query Client
Version 3.23.0
C Client Version 4.6.9
Copyright 2012-2019 Aerospike. All rights reserved.
aql>
aql> show users
+---------+-----------------------------------------------------+
| user    | roles                                               |
+---------+-----------------------------------------------------+
| "admin" | "read-write, read-write-udf, sys-admin, user-admin" |
| "test"  | "read-write"                                        |
| "test2" | "user-admin"                                        |
+---------+-----------------------------------------------------+
3 rows in set (0.001 secs)

Sequence of events that were performed,

  • Initially Inserted a record in set - testset
  • Truncated the testset
  • Re -inserted the the same record in same set.
  • Restored namespace from a backup using asrestore

Do you think any of above is preventing us to delete the record? and any relation?

Granted user “write” role, still throwing an exception

aql> delete from ovn_test.testset where edigest="v2wdE+fNEMW9Ai0n598XDAvM1uE="
Error: (22) AEROSPIKE_ERR_FAIL_FORBIDDEN

aql> show users
+---------+------------------------------------------------------------+
| user    | roles                                                      |
+---------+------------------------------------------------------------+
| "admin" | "read-write, read-write-udf, sys-admin, user-admin, write" |
| "test"  | "read-write"                                               |
| "test2" | "user-admin"                                               |
+---------+------------------------------------------------------------+
3 rows in set (0.001 secs)

Try logging in as user “test” and then running this exercise instead of trying to do as “admin”. I tested with security enabled and was able to do with a user different from admin.

[training@ip-172-31-47-105 bin]$ aql -Upg -Ppg
Seed:         127.0.0.1
User:         pg
Config File:  /etc/aerospike/astools.conf /home/training/.aerospike/astools.conf
Aerospike Query Client
Version 3.29.0
C Client Version 4.6.17
Copyright 2012-2020 Aerospike. All rights reserved.
aql> insert into ovn_test.testset (PK, bin1, bin2) values (1,2,3)
OK, 1 record affected.

aql> set record_print_metadata true
RECORD_PRINT_METADATA = true
aql> select * from ovn_test
+------+------+--------------------------------+-----------+-------+-------+
| bin1 | bin2 | {edigest}                      | {set}     | {ttl} | {gen} |
+------+------+--------------------------------+-----------+-------+-------+
| 2    | 3    | "9ZEkmG6WrRdbN0yUh5RbvK1Te3Q=" | "testset" | -1    | 1     |
+------+------+--------------------------------+-----------+-------+-------+
1 row in set (0.129 secs)

OK

aql> delete from ovn_test.testset where edigest="9ZEkmG6WrRdbN0yUh5RbvK1Te3Q="
OK, 1 record affected.

aql> select * from ovn_test
0 rows in set (0.112 secs)

OK

aql>

Unable to delete with app user (Other than admin) too

aql> delete from xxxx.yyyy where edigest=“Et4wiC2A+JBWEnPbu9dniMPbe84=” Error: (22) AEROSPIKE_ERR_FAIL_FORBIDDEN

© 2021 Copyright Aerospike, Inc. | All rights reserved. Creators of the Aerospike Database.