KV存储-Aerospike之AQL的命令(COMMANDS)

Aerospike(以下简称Spike）
Spike的AQL工具为DB、UDF和index管理提供了类似SQL命令行的接口。但Spike不支持标准SQL作为查询或管理语言。
如下是个人基于Spike4.5.X版本对AQL命令(COMMANDS)的一些梳理总结。

1.DDL

CREATE INDEX <index> ON <ns>[.<set>] (<bin>) NUMERIC|STRING|GEO2DSPHERE
CREATE LIST/MAPKEYS/MAPVALUES INDEX <index> ON <ns>[.<set>] (<bin>) NUMERIC|STRING|GEO2DSPHERE
DROP INDEX <ns>[.<set>] <index>
Examples:
    CREATE INDEX idx_foo ON test.demo (foo) NUMERIC
    DROP INDEX test.demo idx_foo

2.MANAGE UDFS

REGISTER MODULE '<filepath>'
REMOVE MODULE <filename>
    <filepath> is file path to the UDF module(in single quotes).
    <filename> is file name of the UDF module.

Examples:
    REGISTER MODULE '~/test.lua'
    REMOVE MODULE test.lua

3.USER ADMINISTRATION

CREATE USER <user> PASSWORD <password> ROLE[S] <role1>,<role2>...
    pre-defined roles: read|read-write|read-write-udf|sys-admin|user-admin
DROP USER <user>
SET PASSWORD <password> [FOR <user>]
GRANT ROLE[S] <role1>,<role2>... TO <user>
REVOKE ROLE[S] <role1>,<role2>... FROM <user>
CREATE ROLE <role> PRIVILEGE[S] <priv1[.ns1[.set1]]>,<priv2[.ns2[.set2]]>...
    priv: read|read-write|read-write-udf|sys-admin|user-admin|data-admin
    ns:   namespace.  Applies to all namespaces if not set.
    set:  set name.  Applie to all sets within namespace if not set.
          sys-admin, user-admin and data-admin can't be qualified with namespace or set.
DROP ROLE <role>
GRANT PRIVILEGE[S] <priv1[.ns1[.set1]]>,<priv2[.ns2[.set2]]>... TO <role>
REVOKE PRIVILEGE[S] <priv1[.ns1[.set1]]>,<priv2[.ns2[.set2]]>... FROM <role>

4.DML

INSERT INTO <ns>[.<set>] (PK, <bins>) VALUES (<key>, <values>)
DELETE FROM <ns>[.<set>] WHERE PK = <key>
TRUNCATE <ns>[.<set>] [upto <LUT>]
    <ns> is the namespace for the record.
    <set> is the set name for the record.
    <key> is the record's primary key.
    <bins> is a comma-separated list of bin names.
    <values> is comma-separated list of bin values, which may include type cast expressions. Set to NULL (case insensitive & w/o quotes) to delete the bin.
    <LUT> is last update time upto which set or namespace needs to be truncated. LUT is either nanosecond since Unix epoch like 1513687224599000000 or in date string in format like "Dec 19 2017 12:40:00".

  Type Cast Expression Formats:
      CAST(<Value> AS <TypeName>)
      <TypeName>(<Value>)

  Supported AQL Types:
        Bin Value Type                    Equivalent Type Name(s)
     ===============================================================
      Integer                           DECIMAL, INT, NUMERIC
      Floating Point                    FLOAT, REAL
      Aerospike CDT (List, Map, etc.)   JSON
      Aerospike List                    LIST
      Aerospike Map                     MAP
      GeoJSON                           GEOJSON
      String                            CHAR, STRING, TEXT, VARCHAR
     ===============================================================
  [Note:  Type names and keywords are case insensitive.]

Examples:
    INSERT INTO test.demo (PK, foo, bar) VALUES ('key1', 123, 'abc')
    INSERT INTO test.demo (PK, foo, bar) VALUES ('key1', CAST('123' AS INT), JSON('{"a": 1.2, "b": [1, 2, 3]}'))
    INSERT INTO test.demo (PK, foo, bar) VALUES ('key1', LIST('[1, 2, 3]'), MAP('{"a": 1, "b": 2}'))
    INSERT INTO test.demo (PK, gj) VALUES ('key1', GEOJSON('{"type": "Point", "coordinates": [124, -456.7]}'))
    DELETE FROM test.demo WHERE PK = 'key1'

5.INVOKING UDFS

INVOKING UDFS
    EXECUTE <module>.<function>(<args>) ON <ns>[.<set>]
    EXECUTE <module>.<function>(<args>) ON <ns>[.<set>] WHERE PK = <key>
    EXECUTE <module>.<function>(<args>) ON <ns>[.<set>] WHERE <bin> = <value>
    EXECUTE <module>.<function>(<args>) ON <ns>[.<set>] WHERE <bin> BETWEEN <lower> AND <upper>
        <module> is UDF module containing the function to invoke.
        <function> is UDF to invoke.
        <args> is a comma-separated list of argument values for the UDF.
        <ns> is the namespace for the records to be queried.
        <set> is the set name for the record to be queried.
        <key> is the record's primary key.
        <bin> is the name of a bin.
        <value> is the value of a bin.
        <lower> is the lower bound for a numeric range query.
        <upper> is the lower bound for a numeric range query.

    Examples:
        EXECUTE myudfs.udf1(2) ON test.demo
        EXECUTE myudfs.udf1(2) ON test.demo WHERE PK = 'key1'

6.OPERATE

6.1.OPERATE

OPERATE <op(<bin>, params...)>[with_policy(<map policy>),] [<op(<bin>, params...)> with_policy (<map policy>) ...] ON <ns>[.<set>] where PK=<key>
    <op> name of operation to perform.
    <bin> is the name of a bin.
    <params> parameters for operation.
    <map policy> map operation policy.
    <ns> is the namespace for the records to be queried.
    <set> is the set name for the record to be queried.
    <key> is the record's primary key.

6.2.OP

    LIST_APPEND (<bin>, <val>)
    LIST_INSERT (<bin>, <index>, <val>)
    LIST_SET    (<bin>, <index>, <val>)
    LIST_GET    (<bin>, <index>)
    LIST_POP    (<bin>, <index>)
    LIST_REMOVE (<bin>, <index>)
    LIST_APPEND_ITEMS (<bin>, <list of vals>)
    LIST_INSERT_ITEMS (<bin>, <index>, <list of vals>)
    LIST_GET_RANGE    (<bin>, <startindex>[, <count>])
    LIST_POP_RANGE    (<bin>, <startindex>[, <count>])
    LIST_REMOVE_RANGE (<bin>, <startindex>[, <count>])
    LIST_TRIM         (<bin>, <startindex>[, <count>])
    LIST_INCREMENT    (<bin>, <index>, <numeric val>)
    LIST_CLEAR        (<bin>)
    LIST_SIZE         (<bin>)
    MAP_PUT             (<bin>, <key>, <val>) [with_policy (<map policy>)]
    MAP_PUT_ITEMS       (<bin>, <map>)  [with_policy (<map policy>)]
    MAP_INCREMENT       (<bin>, <key>, <numeric val>) [with_policy (<map policy>)]
    MAP_DECREMENT       (<bin>, <key>, <numeric val>) [with_policy (<map policy>)]
    MAP_GET_BY_KEY      (<bin>, <key>)
    MAP_REMOVE_BY_KEY   (<bin>, <key>)
    MAP_GET_BY_VALUE    (<bin>, <value>)
    MAP_REMOVE_BY_VALUE (<bin>, <value>)
    MAP_GET_BY_INDEX    (<bin>, <index>)
    MAP_REMOVE_BY_INDEX (<bin>, <index>)
    MAP_GET_BY_RANK     (<bin>, <rank>)
    MAP_REMOVE_BY_RANK  (<bin>, <rank>)
    MAP_REMOVE_BY_KEY_LIST    (<bin>, <list of keys>)
    MAP_REMOVE_BY_VALUE_LIST  (<bin>, <list of vals>)
    MAP_GET_BY_KEY_RANGE      (<bin>, <startkey>, <endkey>)
    MAP_REMOVEBY_RANGE        (<bin>, <startkey>, <endkey>)
    MAP_GET_BY_VALUE_RANGE    (<bin>, <startval>, <endval>)
    MAP_REMOVE_BY_VALUE_RANGE (<bin>, <startval>, <endval>)
    MAP_GET_BY_INDEX_RANGE    (<bin>, <startindex>[, <count>])
    MAP_REMOVE_BY_INDEX_RANGE (<bin>, <startindex>[, <count>])
    MAP_GET_BY_RANK_RANGE     (<bin>, <startrank> [, <count>])
    MAP_REMOVE_BY_RANK_RANGE  (<bin>, <startrank> [, <count>])
    MAP_CLEAR     (<bin>)
    MAP_SET_TYPE  (<bin>, <map type>)
    MAP_SIZE      (<bin>)
    TOUCH   ()
    READ    (<bin>)
    WRITE   (<bin>, <val>)
    PREPEND (<bin>, <val>)
    APPEND  (<bin>, <val>)
    INCR    (<bin>, <numeric val>)

6.3.MAP_POLICY

    AS_MAP_UNORDERED
    AS_MAP_KEY_ORDERED
    AS_MAP_KEY_VALUE_ORDERED
    AS_MAP_UPDATE
    AS_MAP_UPDATE_ONLY
    AS_MAP_CREATE_ONLY

6.4.Examples

    OPERATE LIST_APPEND(listbin, 1), LIST_APPEND(listbin2, 10) ON test.demo where PK = 'key1'
    OPERATE LIST_POP_RANGE(listbin, 1, 10) ON test.demo where PK = 'key1'

7.QUERY

    SELECT <bins> FROM <ns>[.<set>]
    SELECT <bins> FROM <ns>[.<set>] WHERE <bin> = <value>
    SELECT <bins> FROM <ns>[.<set>] WHERE <bin> BETWEEN <lower> AND <upper>
    SELECT <bins> FROM <ns>[.<set>] WHERE PK = <key>
    SELECT <bins> FROM <ns>[.<set>] IN <indextype> WHERE <bin> = <value>
    SELECT <bins> FROM <ns>[.<set>] IN <indextype> WHERE <bin> BETWEEN <lower> AND <upper>
    SELECT <bins> FROM <ns>[.<set>] IN <indextype> WHERE <bin> CONTAINS <GeoJSONPoint>
    SELECT <bins> FROM <ns>[.<set>] IN <indextype> WHERE <bin> WITHIN <GeoJSONPolygon>

        <ns> is the namespace for the records to be queried.
        <set> is the set name for the record to be queried.
        <key> is the record's primary key.
        <bin> is the name of a bin.
        <value> is the value of a bin.
        <indextype> is the type of a index user wants to query. (LIST/MAPKEYS/MAPVALUES)
        <bins> can be either a wildcard (*) or a comma-separated list of bin names.
        <lower> is the lower bound for a numeric range query.
        <upper> is the lower bound for a numeric range query.

    Examples:

        SELECT * FROM test.demo
        SELECT * FROM test.demo WHERE PK = 'key1'
        SELECT foo, bar FROM test.demo WHERE PK = 'key1'
        SELECT foo, bar FROM test.demo WHERE foo = 123
        SELECT foo, bar FROM test.demo WHERE foo BETWEEN 0 AND 999
        SELECT * FROM test.demo WHERE gj CONTAINS CAST('{"type": "Point", "coordinates": [0.0, 0.0]}' AS GEOJSON)

8.AGGREGATION

    AGGREGATE <module>.<function>(<args>) ON <ns>[.<set>]
    AGGREGATE <module>.<function>(<args>) ON <ns>[.<set>] WHERE <bin> = <value>
    AGGREGATE <module>.<function>(<args>) ON <ns>[.<set>] WHERE <bin> BETWEEN <lower> AND <upper>

        <module> is UDF module containing the function to invoke.
        <function> is UDF to invoke.
        <args> is a comma-separated list of argument values for the UDF.
        <ns> is the namespace for the records to be queried.
        <set> is the set name for the record to be queried.
        <key> is the record's primary key.
        <bin> is the name of a bin.
        <value> is the value of a bin.
        <lower> is the lower bound for a numeric range query.
        <upper> is the lower bound for a numeric range query.

    Examples:

        AGGREGATE myudfs.udf2(2) ON test.demo WHERE foo = 123
        AGGREGATE myudfs.udf2(2) ON test.demo WHERE foo BETWEEN 0 AND 999

9.EXPLAIN

    EXPLAIN SELECT * FROM <ns>[.<set>] WHERE PK = <key>
        <ns> is the namespace for the records to be queried.
        <set> is the set name for the record to be queried.
        <key> is the record's primary key.

    Examples:
        EXPLAIN SELECT * FROM test.demo WHERE PK = 'key1'

10.info

    SHOW NAMESPACES | SETS | BINS | INDEXES
    SHOW SCANS | QUERIES
    STAT NAMESPACE <ns> | INDEX <ns> <indexname>
    STAT SYSTEM
    ASINFO <ASInfoCommand>

10.1.Show namespaces

10.2.Show sets

10.3.Show bins

10.4.Show indexs

10.5.show queries

10.6.show scans

10.7.stat namespace

10.8.stat system

11.Job

JOB MANAGEMENT
    KILL_QUERY <transaction_id>
    KILL_SCAN <scan_id>

12.User

USER ADMINISTRATION
    SHOW USER [<user>]
    SHOW USERS
    SHOW ROLE <role>
    SHOW ROLES

13.udf

MANAGE UDFS
    SHOW MODULES
    DESC MODULE <filename>
        <filepath> is file path to the UDF module(in single quotes).
        <filename> is file name of the UDF module.

    Examples:
        SHOW MODULES
        DESC MODULE test.lua

RUN <filepath>

SYSTEM <bash command>