3

I'm considering aerospike for one of our projects. So I currently created a 3 node cluster and loaded some data on it.

Sample data

ns: imei

set: imei_data

+-------------------+-----------------------+-----------------------+----------------------------+--------------+--------------+
| imsi              | fcheck                | lcheck                | msc                        | fcheck_epoch | lcheck_epoch |
+-------------------+-----------------------+-----------------------+----------------------------+--------------+--------------+
| "413010324064956" | "2017-03-01 14:30:26" | "2017-03-01 14:35:30" | "13d20b080011044917004100" | 1488358826   | 1488359130   |
| "413012628090023" | "2016-09-21 10:06:49" | "2017-09-16 13:54:40" | "13dc0b080011044917006100" | 1474432609   | 1505550280   |
| "413010130130320" | "2016-12-29 22:05:07" | "2017-10-09 16:17:10" | "13d20b080011044917003100" | 1483029307   | 1507546030   |
| "413011330114274" | "2016-09-06 01:48:06" | "2017-10-09 11:53:41" | "13d20b080011044917003100" | 1473106686   | 1507530221   |
| "413012629781993" | "2017-08-16 16:03:01" | "2017-09-13 18:10:48" | "13dc0b080011044917004100" | 1502879581   | 1505306448   |

Then I created a secondary index on lcheck_epoch using AQL since I want to query based on date.

create index idx_lcheck on imei.imei_data (lcheck_epoch) NUMERIC

+--------+----------------+-----------+-------------+-------+--------------+----------------+-----------+
| ns     | bin            | indextype | set         | state | indexname    | path           | type      |
+--------+----------------+-----------+-------------+-------+--------------+----------------+-----------+
| "imei" | "lcheck_epoch" | "NONE"    | "imei_data" | "RW"  | "idx_lcheck" | "lcheck_epoch" | "NUMERIC" |
+--------+----------------+-----------+-------------+-------+--------------+----------------+-----------+

When I execute

select imsi from imei.imei_data where idx_lcheck=1476165806

I'm getting

Error: (204) AEROSPIKE_ERR_INDEX

Please explain.

ivcode
  • 235
  • 2
  • 14

1 Answers1

4

You're using the index name, not the bin name, in your query. Try this:

SELECT imsi FROM imei.imei_data WHERE lcheck_epoch=1476165806

Or

SELECT imsi FROM imei.imei_data WHERE lcheck_epoch BETWEEN 1490000000 AND 1510000000

Just a note, you can do much more complex queries using predicate filtering through several of the language clients (Java, C, C#, Go). For example the PredExp class of the Java client (see examples.)

Ronen Botzer
  • 6,951
  • 22
  • 41
  • 1
    Thank you so much. at first when I executed SELECT imsi FROM imei.imei_data WHERE lcheck_epoch='1476165806'; it gave me Error: (201) AEROSPIKE_ERR_INDEX_NOT_FOUND. Should be because i used quotes. – ivcode Oct 12 '17 at 03:28
  • 1
    Just two side questions. 1) does aerospike have an equivalent to mysql : select field1, field2 into out file '/tmp/file' from imei where lcheck_epoch>1476165806 since i have to send a csv dump of daily updated records to another system. and in above question is lcheck_epoch>1476165806 a valid filter? – ivcode Oct 12 '17 at 03:34
  • 2
    Yes, the quotes would turn it into a string, and you don't have a secondary index on that bin for STRING data, yours is NUMERIC. There's no automatic typecasting. – Ronen Botzer Oct 12 '17 at 04:44
  • 3
    Regarding a CSV dump, there was a similar question here: https://stackoverflow.com/questions/45819023/how-to-parse-aerospike-backup-file-to-regenerate-data - the short of it is that I suggest you use asbackup and asrestore, which will be faster, generate more compact data, than a homegrown CSV backup script. Between systems you should consider Kafka and the Aerospike Kafka connector. – Ronen Botzer Oct 12 '17 at 05:44