Working with Data in the RAI Console

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

You can use the RAI Console to import CSV or JSON data into EDB relations. EDB relations are stored on disk, and generally contain data from external sources; they are the “raw data” as far as the database is concerned. In contrast, IDB relations are generally derived from logic applied to EDB relations.

For more information on EDB relations, see Updating Data: Working with EDB Relations

Note that it is also possible to manage data through the RelationalAI SDKs.

When you open a database in the RAI Console, EDB relations appear under Data in the Object Tree on the left-hand side of the RAI Console.

Listed EDB relations include:

  1. Data that you or others have added to the database.
  2. Built-in data used by the system.

Using the Object Tree, you can view, upload, and delete EDB relations. Unlike with models, you cannot view substructures for EDB relations.

create data

Viewing Data

You can view data through the Query Editor. To auto-generate a simple query, click on the EDB relation in the Object Tree.

Clicking the EDB relation automatically generates a query in the following form:

def output = my_data
view data

You can also write a query by hand. For more details on writing queries, see Working with Queries in the RAI Console

Importing Data

In order to import data, you need to assign the data to a named relation. This is the name that appears in the Object Tree, and is also the name that you will use to reference the data when coding in Rel.

To upload data:

  1. Open the database into which you want to import data.

  2. Either:

    Right-click on Data in the Object Tree on the left-hand side of the Console and choose Import.

    OR

    Click the Upload Data button on the right side of the Console.

  3. In the dialog box that opens, provide a name for the relation that will be associated with the uploaded data, such as my_uploaded_csv_data. You will use this name to work with the data later.

upload data
  1. Under Value, click the Upload a file link and browse to the location of the local file that you wish to import. You can also upload multiple files at once into separate relations.
  2. Click Upload

The new EDB relation appears in the Object Tree.

You can preview the uploaded data by clicking View in the dialog box. Clicking View automatically generates a query along the following lines in the Query Editor:

def output = { (:my_uploaded_csv_data, my_uploaded_csv_data) }

To preview the data, click Run to run the query.

Processing CSV Data

When you upload CSV data, it is initially loaded as a string.

To process CSV data into a form that can be queried, you need to run some Rel code, using the load_csv function from the Rel Standard Library.

To process CSV data:

  1. Follow the steps above to load data into the Console.
  2. Create a model.
  3. Enter code along the following lines, where my_uploaded_csv_data is the name of the relation into which you loaded data:
def config:data = my_uploaded_csv_data
def my_data = load_csv[config]
  1. Click Save or run the notebook cell.

To view the processed data:

  1. Enter code along the following lines in the Query Editor:
def output = table[my_data]
  1. Click Run in the Query Editor.

You can also specify a schema for the CSV data, as well as other import options. For details on specifying import options, see Defining Import Options in the CSV Import guide.

For example, imagine that you uploaded the following CSV, which contains information on television shows watched through a streaming service:

Profile_Name,Start_Time,Title
Michael,2019-11-07,BoJack Horseman: Chickens
Tran,2020-03-24,Tiger King: The Secret
Marcela,2021-08-09,Community: Paranormal Parentage
Ling,2020-12-07,Cobra Kai: Cobra Kai Never Dies

After processing, queried data will appear as follows:

processed

Each relation is a separate “table” with the same data in the first column. Note that the RKGMS uses a file position for each row.

To query just one field from the processed CSV, you could use code along the following lines:

def output = my_data:Title

Relation: output

RelationalAITypes.FilePos(31)"BoJack Horseman: Chickens"
RelationalAITypes.FilePos(76)"Tiger King: The Secret"
RelationalAITypes.FilePos(115)"Community: Paranormal Parentage"
RelationalAITypes.FilePos(166)"Cobra Kai: Cobra Kai Never Dies"

This code tells the RKGMS “get me all of the fields called Title”.

For details on querying imported data, see see Querying CSV Data in the CSV Import guide.

Loading JSON Data

When you upload JSON data directly via the Console (that is, without using a notebook), the Console automatically converts JSON data to a form that is usable in queries.

After processing, JSON keys turn into relation names (starting with :). JSON arrays turn into relations that use :[] as the relation name.

For example, imagine that you uploaded the following JSON into a relation called my_uploaded_json_data. The JSON contains the same information on streaming television shows from the CSV example above:

[
 {
   "Profile_Name": "Michael",
   "Start_Time": "2019-11-07",
   "Title": "BoJack Horseman: Chickens"
 },
 {
   "Profile_Name": "Marcela",
   "Start_Time": "2021-08-09",
   "Title": "Community: Paranormal Parentage"
 },
 {
    "Profile_Name": "Ling",
    "Start_Time": "2020-12-07",
    "Title": "Cobra Kai: Cobra Kai Never Dies"
  },
  {
  "Profile_Name": "Tran",
  "Start_Time": "2020-03-24",
  "Title": "Tiger King: The Secret"
  }
]

When you query the relation, the imported data appears in the fully normalized Graph Normal Form:

def output = my_uploaded_json_data
JSON processed into Graph Normal Form

You can query just one of the fields by using code similar to the example below. Because the data above derives from a JSON array, you need to use the :[] symbol.

def title = i : my_uploaded_json_data[:[], i, :Title]
def output = title

Relation: output

1"BoJack Horseman: Chickens"
2"Community: Paranormal Parentage"
3"Cobra Kai: Cobra Kai Never Dies"
4"Tiger King: The Secret"

The code block above uses a variable, i, which tells the RKGMS “get me all of the fields called Title”.

For more information on working with JSON data, see JSON Import and Export.

Deleting Data

You can delete data from the Object Tree.

Before doing so, it’s best practice to:

  1. Export the data so that you can access it later if necessary.
  2. Check with other team members to make sure they are not using the data. The data will be unrecoverable once deleted.

To delete an EDB relation:

  1. Right-click the EDB relation you wish to delete and choose Delete.
  2. In the confirmation message that appears, click Delete.
delete data

The EDB relation disappears from the Object Tree and is removed from the database.