AQL

AQL is a data browser for examination of the DB and a tool for lightweight administrative tasks for maintaining.

The aql command is complementary to asadm and asinfo, which are the primary administrative mechanisms for major, extensive DB needs or examination.

Some common uses of aql are discussed here, aql has many other commands. For details, see Getting help for AQL.

Case sensitivity

AQL commands (such as SELECT) and modifiers of commands (such as WHERE) can be either uppercase or lowercase. The formal command syntax here shows uppercase, while many examples show lowercase.

However, namespace names, bin names, and names of other objects are case-sensitive.

No relation to SQL, syntax differences

Although the name "AQL" resembles the well-known SQL, AQL is not SQL.

One syntactical difference between the two is that SQL statements must end with a semicolon (;). Although AQL statements can terminate with a semicolon, a semicolon is not required.

Getting help for aql

For extensive help with aql command-line options and commands, type aql --help.

Many of AQL's commands also have more detailed help. The following can be upper- or lowercase:

• HELP AGGREGATE
• HELP ASINFO
• HELP CREATE
• HELP DELETE
• HELP DESC
• HELP DROP
• HELP EXECUTE
• HELP EXPLAIN
• HELP GET
• HELP GRANT
• HELP INSERT
• HELP OPERATE
• HELP PRINT
• HELP REGISTER
• HELP REMOVE
• HELP RESET
• HELP RUN
• HELP SELECT
• HELP SET
• HELP SHOW
• HELP STAT
• HELP SYSTEM
• HELP TRUNCATE

Configuration file

aql can be configured with the standard Aerospike tools configuration file. See Aerospike Tools Configuration.

Starting aql and running commands

aql is started on the Linux command-line with the following command.

aql

This command displays the prompt aql> at which you enter AQL commands, modifiers, and arguments.

Looking at specific records

If for some reason you want to look at the details of a specific record, use EXPLAIN SELECT.

You must know the primary key of the record.

EXPLAIN SELECT * FROM namespaceName.setName WHERE PK=valueOfPrimaryKey

By default, results for the valueOfPrimaryKey object are displayed as a table.

To display results in JSON format, you must first set the output mode:

aql> SET OUTPUT JSON
OUTPUT = JSON

For other allowable values, enter HELP SET.

You can also specify outputmode = 'outputType' in your [configuration file](#configuration-file].

Working with secondary indexes

A secondary index augments the Aerospike primary index, the record's key. A secondary index is created for a specific bin. For more details about uses of secondary indexes, see Secondary Index.

Creating a secondary index

Syntax is as follow

A secondary index is not queryable until the system has finished creating it.

CREATE indexType INDEX indexName ON namespaceName.setName binName binType

where:

Variable	Description
secondaryIndexType	Is the optional specification of the type of secondary index. See description in Syntax for creating secondary indexes.
secondaryIndexName	Is the unique name of the secondary index to be created in the specified namespaceName, must be 20 characters or less, and must not contain a colon or semicolon.
namespaceName	Is the namespace where the secondary index is to be created and that contains the specified binName to be indexed.
setName	Is an optional name of a set.
binName	Is the name of the bin that is the secondary index key. Any record not containing binName is not indexed. Any record containing binName that does not match binType is not indexed.
binType	Is one of NUMERIC, STRING, or GEO2DSPHERE.

Syntax for creating secondary indexes

The syntax for creating secondary indexes is shown below:

CREATE LIST/MAPKEYS/MAPVALUES INDEX secondaryIndexName ON namespaceName[.setName] (binName) NUMERIC|STRING|GEO2DSPHERE

indexType is for indexing string or numeric values of the list or map collection data types (CDT).

• The specification is only one of LIST, MAPVALUES, or MAPKEYS
• LIST or MAPKEYS - string or numeric value of keys in lists or maps.
• MAPVALUES - string or numeric value of key-value pairs in maps. If not specified, only values in the specified bin of data type STRING or NUMERIC can be indexed.

Showing secondary index details

To show the names of secondary indexes, use SHOW INDEXES and the optional namespaceName:

SHOW INDEXES namespaceName

Seeing secondary index statistics

Use STAT INDEX namespaceName indexName.

Example

aql> STAT INDEX test NUMINDEX
+--------------------------+-------+
| name                     | value |
+--------------------------+-------+
| "keys"                   | 6     |
| "objects"                | 11    |
| "data_memory_used"       | 1320  |
| "load_pct"               | 100   |
| "loadtime"               | 6     |
| "stat_write_reqs"        | 11    |
| "stat_write_success"     | 11    |
| "stat_write_errs"        | 0     |
| "stat_delete_reqs"       | 0     |
| "stat_delete_success"    | 0     |
| "stat_delete_errs"       | 0     |
| "stat_defrag_recs"       | 0     |
| "stat_defrag_time"       | 0     |
| "n_query"                | 6     |
| "avg_selectivity"        | 2     |
| "avg_record_size"        | 42    |
| "n_aggregation"          | 5     |
| "agg_avg_selectivity"    | 2     |
| "agg_avg_record_size"    | 8     |
| "n_lookups"              | 1     |
| "lookup_avg_selectivity" | 6     |
| "lookup_avg_record_size" | 106   |
+--------------------------+-------+
22 rows in set (0.000 secs)

Dropping a secondary index

To completely remove a secondary index, use a command similar to the following:

DROP INDEX namespaceName indexName

Managing users

aql has straightforward syntax for managing user information.

Creating roles, users, and assigning roles

Before you can create a user and assign roles, you must create a role with CREATE ROLE.

Decide the name of the role and the permissions that role should have. A role relies on the following predefined permissions, also called "privileges":

• read: Read-only
• read-write: Read and write
• read-write-udf: Read and write only User-Defined Functions (UDFs)
• sys-admin: All of these permissions
• user-admin: Read and write user information

Example of creating a role

In this example, the role named superwoman :

• Is given user-admin privileges for all namespaces because a namespace was not specified.
• Is given the UDF management privilege for only a specific namespace and set of records.

aql> CREATE ROLE superwoman PRIVILEGES user-admin,read-write-udf.namespaceName.setName

Example of creating a user and assigning a role

To create a user, the associated password, and assign roles to that user, use CREATE USER:

CREATE USER userName PASSWORD userPassword ROLES roleName1,roleName2,...

For only a single role, use singular ROLE, not ROLES.

Changing user roles

Your can grant or revoke a user's roles.

GRANT ROLES roleName1,rolename2,roleName3... TO userName

REVOKE ROLES roleName1,rolename2,roleName3... FROM userName

For only a single role, use singular ROLE, not ROLES.

Changing user passwords

Use the following command:

SET PASSWORD userPassword FOR userName

Removing a user or role

To remove a user or role from the system, use DROP.

DROP USER userName

DROP ROLE roleName

Registering, showing, and removing User-Defined Functions (UDFs)

UDFs are programs you write in Lua that augment some of the basic features of Aerospike, such as to aggregate or modify data. For details, see the User-Defined Functions (UDF) Development Guide.

Registering a UDF

UDFs must be registered with the system. When you register a UDF module, the module is copied to all nodes in the cluster.

Assume you have already created a UDF. Now you want the system to know that it exists. Use REGISTER MODULE. The path to the module must be enclosed in double quotation marks:

REGISTER MODULE "pathToLuaFile"

Example

aql> REGISTER MODULE "udf/testudf.lua"
OK, 1 module added.

Showing UDFs already registered

Use SHOW MODULES.

The output shows the filename of the module, its type, which is lua, and a hash.

Example

aql> SHOW MODULES
+---------------------------+-------+------------------------+
| module                    | type  | hash                   |
+---------------------------+-------+------------------------+
| "example1.lua"            | "lua" | "033671e05067888fce09" |
| "example2.lua"            | "lua" | "07b42082cca8e73a96b2" |
+---------------------------+-------+------------------------+
2 rows in set (0.000 secs)

Removing registered UDFs

To completely remove a UDF module from the system, use REMOVE MODULE.

Double quotation marks around the module name are optional.

Example

aql> REMOVE MODULE example2.lua