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-onlyread-write
: Read and writeread-write-udf
: Read and write only User-Defined Functions (UDFs)sys-admin
: All of these permissionsuser-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