Working with Queries in the RAI Console

A short guide to working with queries in the RAI Console.

The RAI Console features a Query Editor that you can use to view and manage the contents of your database.

Using the Query Editor

With the Query Editor, you can experiment with issuing query requests. The output panel will display the results in the same way as you would see them in a RAI Notebook.

initial view of the Query Editor

Queries are not persisted in the database. Instead, queries are saved in your browser’s local storage, and are available until you delete them, as long as you log into the Console using the same browser and same account.

Viewing Query Results

To run a query:

  1. Enter a query into the Query Editor.
  2. Click the Run button.

Query results appear in the Query Output panel. The query remains active until you switch to another query. So if you make changes to data, the query automatically updates.

For example, let’s say the data in a model reads as follows:

update
module store
def office = {
"Los Angeles";
"Chicago"}
def product = {
(1, "Laptops");
(2, "Desktops");
(3, "Phones")
}
end

Running a query that reads def output = store:product produces the following result:

Int64String
1“Laptops”
2“Desktops”
3“Phones”

While you have a query open, its results will update automatically when data is changed.

If, in the example above, you modify the definition of store:product to add (4, "Keyboards") while the query is running, the results will update as follows:

Int64String
1“Laptops”
2“Desktops”
3“Phones”
4“Keyboards”

Including Definitions

You can also include definitions in a query in order to simulate the results of making changes to data.

Using the example above, if you enter the following into the Query Editor:

query
def store:product = (5, "Memory")
def output = store:product

the results will read:

Int64String
1“Laptops”
2“Desktops”
3“Phones”
4“Keyboards”
5“Memory”

In this example 5, "Memory" is included in the output even though the installed model does not include this entry.

Commenting Out Query Segments

To comment out part of a query, you can add // to the beginning of a line. You can also comment out a section of a query by adding /* to the beginning of a section and */ to the end of the section.

Naming a Query

By default, queries are unnamed. When you create a new query, the query is called Untitled.

You can rename the query by double-clicking the query’s tab and entering a new name.

Hiding the Query Editor

You can show or hide the Query Editor by clicking the Show/Hide Queries button on the right-hand side of the Console:

Show or Hide the Query Editor

Working with EDB Data

The Query Editor lets you insert and delete records from an EDB relation by turning off the Read-Only switch. When the Read-Only switch is turned off, code entered into the Query Editor runs directly against stored EDB data. For more information on working with EDB relations, see Updating Data: Working with EDB Relations

You can, however, use the Query Editor to simulate changes to an EDB relation by leaving the Read-Only switch on.

For example, let’s say you have an EDB relation with the following data on yearly rainfall in California cities (from the US National Centers for Environmental Information):

cityinches
Antioch12.6
Berkeley26.1
Clearlake29.9
Davis19.2
Eureka40.4
Fairfield24.5
Fremont15.9
Fresno11.0

You can create this relation in the Query Editor by turning the Read-Only switch off and entering the following code. This code uses the Rel function load_csv to simulate loading the contents of a CSV file into an EDB relation called rainfall. The code creates a schema with two columns: :city, "string" and :inches, "float". For more information about specifying schemas when importing CSV data, see Specifying Column Types (File Schema) in the CSV Import guide.

update
def config:data = """
city,inches
Antioch,12.6
Berkeley,26.1
Clearlake,29.9
Davis,19.2
Eureka,40.4
Fairfield,24.4
Fremont,15.9
Fresno,11.0
"""
def config:schema=(:city, "string") ; (:inches, "float") //creates a schema for the data

def csv = load_csv[config] //uses the Rel function \stdlib{load_csv} to simulate loading a csv file
def insert[:rainfall](city,inches) = csv(:city, pos, city)
and csv(:inches, pos, inches) from pos //creates the relation "rainfall" and inserts into it from the relation "csv"

When you click Run, the code creates the relation rainfall, which appears in the object tree under Data.

An EDB relation created

Note that nothing appears in the Output window. That’s because this code does not return a result.

Updating EDB Data

When you run queries with the Read-Only switch turned off, you can use the Query Editor to update EDB relations: you can insert rows into and delete rows from EDB relations.

For example, to insert a new record into the rainfall relation defined above, you would turn off the Read-Only switch and enter code along the following lines:

update
def insert[:rainfall] = ("Napa", 26.7)

If you now query rainfall, you’ll see that the data for Napa has been added:

query
def output = rainfall

Relation: output

"Antioch"12.6
"Berkeley"26.1
"Clearlake"29.9
"Davis"19.2
"Eureka"40.4
"Fairfield"24.4
"Fremont"15.9
"Fresno"11.0
"Napa"26.7
The Query Editor updated with added EDB data

Testing Changes to EDB Data

You can also use the Query Editor to simulate changes to an EDB relation without changing data. To do so, incorporate an insert or delete statement into the Query Editor and query the data with the Read-Only switch turned on.

We can test this functionality by setting up a simple calculation on the rainfall relation. The following code determines the average rainfall across cities:

query
def output:avg = average[rainfall]

Relation: output

:avg22.91111111111111

Given the data above, this calculation initially returns an average of 22.91111111111111.

You can modify this code block to include a simulated addition to the rainfall relation. We’ll use the yearly rainfall for Santa Cruz, a city whose yearly rainfall is slightly higher than the average we just calculated.

query
def insert[:rainfall] = ("Santa Cruz", 30.6)
def output:avg = average[rainfall]

Relation: output

:avg23.68

When you run this query, the RKGMS calculates average[rainfall] with the new row included, returning an average of 23.68.

Shortcuts for Using the Query Editor

We created some shortcuts for using the Query Editor:

  • Cmd-S or Ctrl-S saves the query
  • Shift-Enter saves and runs the query
  • Ctrl-Enter creates a new query