Hi all,
I am new in aerospike . I have create a namespace say “Company” with in that i have set “Products”
and bins are (data,k, product_id, product_type)
I want to write the delete query
a) delete all product of particular type. b) delete all product of particular id
How should i write these query .
i am using php client . thanks,
You would use a record UDF, written in Lua, to inspect whether the criteria are met for deletion, and if they are you’d call the aerospike.remove() function.
Currently you have to use scanApply() to attach the record UDF to a scan of the set Company.Products. Be aware that we are working on secondary index record UDFs, meaning there will soon be a queryApply() that will let you run a record UDF against the records matched by a secondary index query. This should fit your use case better, as the query itself can be targeted at an index over product type or ID, rather than scanning all the records in the set.
Do you have an example for this topic ?
I answered a similar question on stackoverflow.
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.
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: https://www.aerospike.com/docs/operations/configure/security/access-control/index.html#users