Processing data in database tables

Another way to get data in and out is through "importing" from or "exporting" to the underlying database. If you run Rax in the same database (but not necessarily in the same schema) as the tables that contain your data, you can let Rax read data directly from these tables. The only thing you need to do, is tell Rax how the data in the SQL tables maps onto Rax data types. This is achieved by using the import operator. The name 'import' is somewhat misleading, since the data is not actually copied into Rax data structures. Rax reads the data directly from the original tables. The example below shows how to import three columns from a table named BUILDINGS from the underlying database:

    {[#:id, $:name, &:imperial_height]}: T;
    T := import [(#)"IDNUM", ($)"NAME", (&)"HEIGHT"] "BUILDINGS";

After importing a table, some analysis and/or translations can be performed in Rax and the results can be written back to the database using the `export output procedure:

    {[$:NAME, #:ID, &:M_HEIGHT]}: T';
    T' := project[ name, id, imperial_height / 3.2808399] T;
    `export T', "METRIC_BUILDINGS";

The above example transforms table T into T' and exports the result to table "METRIC_BUILDINGS" in the underlying database. The columns will have the same name as their corresponding fields. The `export procedure does perform a copy of the data. If the table "METRIC_BUILDINGS" already exists, use !"METRIC_BUILDINGS" to overwrite. Without the exclamation mark, it would refuse to overwrite the table.

The mapping between Rax types and SQL types is pretty straightforward, though back end specific. The table below, shows the mapping for SQLServer:

Table 11.1. Mapping between Rax and SQL Server types for import and `export

Rax typeSQL type

Note that a single value of the | type (interval) is mapped onto two values of type DATETIME. This is because an interval type does not exist in SQL. When importing, two columns are mapped onto a single tuple field like this:

           {[|:interval]}: intervals := import [(|)[(@)"BEGIN", (@)"END"]] "T_INTERVALS";

When exporting, two columns are created out of a single tuple field.

Note that the SQL expressions in double quotes do not have to be necessarily plain table and column names. You can also use more complex expressions, like in this example:

    {[#:RespondentId, #:MediaUniverseId, |:Period, &:Weight]}:
      RespondentWeights :=
        import [
          (|)[(@)"CAST(StartDate AS DATETIME)", (@)"CAST(EndDate AS DATETIME)"],
        "(SELECT TOP 1000 * FROM RespondentWeights ORDER BY RespondentID)";

The SQL CAST function was used to cast columns of type DATE to type DATETIME needed by Rax. Instead of a table name, a sub-query was used to select only a part of the data.