12. Tables

Note: Tables are an experimental feature in RAW. Although the format is considered stable, features are missing and the public API is still subject to changes.

Tables are used in RAW to permanently store data.

Tables are designed for “tabular” structure, similarly to SQL tables in relational databases.

[7]:
%load_ext raw_magic
The raw_magic extension is already loaded. To reload it, use:
  %reload_ext raw_magic

To create a table in RAW we use the %%table magic. The type must be passed as an argument.

The type is described in RQL syntax and must consist of a tabular structure, i.e. a collection of records of primitive types.

To create a table called table1.

[14]:
%%table table1

type: 'collection(record(column_1: int, column_2: string))'
table table1 replaced

This table is now empty.

To insert data into the table, we use the tables_insert magic. This requires the table name and the contents to be inserted into the table.

The contents are an RQL query.

This allows users to load data into a RAW table from another location as in:

[16]:
%%tables_insert table1

SELECT Altitude AS column_1, City AS column_2
FROM READ("https://raw-tutorial.s3.amazonaws.com/airports.csv")

The table contents can now be queried:

[17]:
%%rql

SELECT *
FROM table1
LIMIT 10
[17]:
column_1column_2
5282Goroka
20Madang
5388Mount Hagen
239Nadzab
146Port Moresby
19Wewak
112Narssarssuaq
283Godthaab
165Sondrestrom
251Thule

It is also possible to insert data that does not originate from another location.

This can be done by creating a RAW query that includes the data to be inserted.

Let’s create a new table, and insert data into it:

[23]:
%%table table2

type: 'collection(record(column_1: int, column_2: string))'
table table2 replaced

To insert data, we write an RQL query using RAW’s syntax to create collections and records.

To create a collection in RAW we use [..., ...].

To create records in RAW we use (<field>: <value>, ...).

Therefore, we can insert data manually as in:

[24]:
%%tables_insert table2

[
 (column_1: 1, column_2: "Row 1"),
 (column_1: 2, column_2: "Row 2"),
 (column_1: 3, column_2: "Row 3")
]
[25]:
%%rql

SELECT * FROM table2
[25]:
column_1column_2
1Row 1
2Row 2
3Row 3

Tables use multiversion concurrency control.