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 base relations. Base relations are stored on disk, and generally contain data from external sources; they are the “raw data” as far as the database is concerned. By contrast, derived relations are generally derived from logic applied to base relations.

For more details, see Working With Base Relations.

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

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

Listed base 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 base relations. Unlike with models, you cannot view substructures for base relations.

Viewing Data

You can view data through the Query Editor. To autogenerate a simple query, click the base relation in the Object Tree.

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

def output = my_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 Data in the Object Tree on the left-hand side of the Console and choose Import.

   OR

   • Click Upload Data 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.

4. Under Value, click Upload a file and browse to the location of the local file that you want to import. You can also upload multiple files at once into separate relations.
5. Click Upload.

The new base 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, they are 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]

4. Click Save or run the notebook cell.

To view the processed data:

1. Enter code along the following lines in the Query Editor:

// query
 
def output = table[my_data]

2. 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 data, which contain 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:

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

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

// read query
 
def output = my_data:Title

This code instructs the RKGS to output all of the fields called Title.

For details on querying imported data, 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 Symbols. JSON arrays turn into relations that use :[] as the relation name.

For example, imagine that you uploaded the following JSON data into a relation called my_uploaded_json_data. The JSON data contain 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 appear in the fully normalized Graph Normal Form:

// query
 
def output = my_uploaded_json_data

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

// read query
 
def title = i : my_uploaded_json_data[:[], i, :Title]
def output = title

The code block above uses a variable, i, which instructs the RKGS to output 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 them 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 a base relation:

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

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