3

How to perform (sql like) IN queries in aerospike. Do we need an UDF for this?

Something like this: Select * from ns.set where PK in (1,2,3)

If this requires a UDF how to go about it as the UDF is executed on a key:

EXECUTE <module>.<function>(<args>) ON <ns>[.<set>] WHERE PK = <key>
Sandeep B
  • 765
  • 1
  • 6
  • 19

2 Answers2

2

In Ver 3.12.1+, you can run such queries if you store your Primary Key in a bin and then run predicate filtering on that bin. See http://www.aerospike.com/docs/guide/predicate.html

Aerospike by default does not store the PK in its raw string or numeric form as you assign it. It stores a RIPEMD160 hash of the PK+Set name.

pgupta
  • 5,130
  • 11
  • 8
2

You're basically looking at retrieving records by a list of keys. This is a batch-read operation in Aerospike. Every language client for Aerospike should have this capability.

For example, in the Python client this is the Client.get_many method:

from __future__ import print_function
import aerospike
from aerospike.exception import AerospikeError
import sys

config = { 'hosts': [('127.0.0.1', 3000)] }
client = aerospike.client(config).connect()

try:
    # assume the fourth key has no matching record
    keys = [
      ('test', 'demo', '1'),
      ('test', 'demo', '2'),
      ('test', 'demo', '3'),
      ('test', 'demo', '4')
    ]
    records = client.get_many(keys)
    print records
except AerospikeError as e:
    print("Error: {0} [{1}]".format(e.msg, e.code))
    sys.exit(1)
finally:
    client.close()

Similarly, in the Java client the AerospikeClient.get() method can take a list of keys.

Ronen Botzer
  • 6,951
  • 22
  • 41
  • I totally misunderstood the question! However, as a separate problem, lets say one has composite keys such as abc:123, abc:456, abc:234...xyz:123, xyz:456 .. etc and I wanted all records that are abc:* key, you can now use predicate filtering with ver 3.12.1+ if you stored the key in a bin. Right? – pgupta May 17 '17 at 06:07
  • Predicate filtering would be a great way to introspect bin information and quickly finding the matching records. – Ronen Botzer May 17 '17 at 18:28