Skip to main content

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].

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 aggregating or modifying 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. Use REGISTER MODULE to let the system know that it exists. 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

Managing users​

deprecated

Managing users and roles is deprecated as of aql 7.0. Use asadm instead.

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