fbpx

Javascript Code (ETL) - Obsolete

We advise using the Java ETL, it's faster and more robust than the Javascript version that is kept for compatibility.

The Javascript view allows for defining your own transformation logic. It requires a JRE 1.8 as a minimum.

Each time icCube is reading a row from the underlying table/view it calls the Javascript code defined in the field "Row Processing Code". The "Init Code" is called once at the beginning and the "Completion Code" once at the end of the processing of the underlying table/view. The "Forwarded Columns" allows for defining the list of columns that will be sent without any change.

The "Table Row Ordering" can be used to keep the natural order (i.e., as returned for example by a SELECT * FROM ... for SQL tables) of the underlying table if required otherwise the Javascript callback cannot assume anything about the order of the input rows.

The following Javascript functions/variables are available to access the current row and generate new rows:

/**
 * Get a column's value from the current (or the specified) row.
 *
 * Possible signatures:
 *
 * get(col)
 * get(row, col)
 */
function get();

/**
 * Set a column's value to the current (or the specified) row.
 *
 * Possible signatures:
 *
 * set(col, value)
 * set(row, col, value)
 */
function set();

/**
 * Generate a new row using the content of the current (or the specified) row.
 *
 * Possible signatures:
 *
 * fire()
 * fire(rowNumber)
 *
 * fire(row)
 * fire(row, rowNumber)
 */
function fire();

/**
 * A copy of the current row that can be buffered.
 */
function copy();

/**
 * A new row with the forwarded columns initialized using the current row.
 */
function create();

/**
 * Array support : create an empty read/write array.
 */
function arr();

/**
 * Array support : length of the array.
 */
function arr_len(arr);

/**
 * Array support : add a value to the array.
 */
function arr_push(arr, value);

/**
 * Array support : get the value at the index.
 */
function arr_get(arr, idx);

/**
 * Returns a new Date (Joda LocalDate)
 *
 * Possible signatures:
 *
 * newDate()  -> now()
 * newDate(year,month,dayOfMonth)
 */
function newDate()

/**
 * Returns a new DateTime (Joda LocalDateTime)
 *
 * Possible signatures:
 *
 * newDateTime()  -> now()
 * newDateTime(year,month,dayOfMonth,hour[,minute][,second][,millis])
 */
function newDateTime()

/**
 * An object shared during the processing of the rows.
 */
var ctxt = {};

/**
 * Logger: icCube.builder.view.javascript
 */
function debug(msg);
function warn(msg);
function error(msg);

function isDebugEnabled(msg);

/**
 * @param tableName
 * @returns a java representation of the table (OlapBuilderCacheTableWrapper), exception if the table is missing
 */
function table(tableName);

/**
 * @param tableName
 * @returns a java8 stream on the rows of the table (IOlapBuilderCacheRowWrapper), exception if the table is missing
 */
function stream(tableName);

/**
 * Fires the row of the cached table
 */
function fireRow(tableRow)
{
    return ic3_view_proxy.fireRow(tableRow);
}

/**
 * Fires the full cached table
 */
function fireTable(table)
{
    return ic3_view_proxy.fireTable(table);
}
    

To better understand how this view is working here are several typical examples which are using the following tables:

#
# table: transports
#
DATE, BUS, PASSENGERS, KM, DESCRIPTION
2015-MAR-1,  large,  20,          10,  Train Station
2015-MAR-2,  small,  3,           50,  Airport
2015-MAR-3,  large,  25,          25,  Football Stadium
2015-MAR-4,  large,  22,          55,  Casino

#
# table: country_gdp
#
YEAR, CNAME_1, CGDP_1, CNAME_2, CGDP_2, CNAME_3, CGDP_3, CNAME_4, CGDP_4, CNAME_5, CGDP_5
2013, United States, 16768,  China,   16161,  India,   6776,   Japan,   4641,   Germany, 3585
2012, United States, 16163,  China,   14790,  India,   6357,   Japan,   4517,   Germany, 3472

#
# table: js_filter_cols
#
COUNTRY, PRODUCT, AMOUNT
Canada,  Bike,    10
Canada,  Bike,    20
Canada,  Car,     20
Canada,  Car,     40
USA,     Bike,    100
USA,     Bike,    200
USA,     Car,     500
USA,     Car,     1000
France,  Bike,    10
France,  Bike,    20
Spain,   Car,     100
Spain,   Car,     200
    

Java/Javascript Interoperability : Long Values

Javascript does not support "long" values . Instead long values when required (e.g., mathematical operations) are converted to double and depending on their values you might loose some precision. Nevertheless, passing around long values (i.e., set("column", get("column")) works fine. Doing so, you might need to compare them (e.g., to perform a group-by based on the column value). Comparing long values with Javascript operator does not work. For that purpose, you can instead use the static function equals (i.e., equals(v1, v2)). This function can be used with all types so better to use it instead of the operator == (or !=).

var current_value = get("column");

if( ! equals( current_value, prev_value ) )
{
    // ...

    prev_value = current_value;
}
    

Example: Adding New Column(s)

Based on the 'transports' table. The idea is to add a new column with the PRICE based on the content of the PASSENGERS and KM column content. The processing code is basically using the get/set function to access the current row; and once the new row is ready it calls the fire() function.

Generated Columns

PRICE : DOUBLE


Row Processing Code

//
// Generate the value of the PRICE column based on PASSENGERS and KM values
//
set("PRICE", get("PASSENGERS") * get("KM") * 0.2 );

//
// Fire the current generated row
//
fire();
    

Example: Filtering Rows

Based on the 'transports' table. The idea is to keep rows after a certain DATE. This example demonstrates how to read and generate dates (icCube is internally using JODA date and time).

Init Code

//
// Keep track of our actual row number as we're filtering out several rows
//
ctxt.row_nb = 1;

var jDateType = Java.type( "org.joda.time.LocalDate" );
var jDateRef = new jDateType( 2015, 3, 2 );

Row Processing Code

if( get( "DATE").isAfter( jDateRef ) )
{
   fire( ctxt.row_nb++ /* optional row number */);
}

Example: Filtering Columns

In order to filter columns (and keeping all the rows) simply define the list of "Forwarded Columns" and fire the row within the "Row Processing Code".

Row Processing Code

fire();

Example: Generating N Rows

Based on the 'country_gdp' table. The country_gdp table is showing on columns for each year the top 5 countries based on their GDP (billions USD). We'd like to perform a columns to rows transformation. The code is demonstrating how to generate more than one row for each underlying table row.

Generated Columns

COUNTRY : STRING
GDP     : LONG

Init Code

//
// We're generating more than one row for each underlying table row.
//
ctxt.row_nb = 1;

Row Processing Code

//
// Generates a new row for each Country/GDP columns pair.
//
for( var country_idx = 1; country_idx <= 5; country_idx++) {

  var cname = "CNAME_" + country_idx;
  var cgdp  = "CGDP_"  + country_idx;

  set( "COUNTRY", get( cname ));
  set( "GDP",     get( cgdp  ));

  fire(ctxt.row_nb++ /* optional row number */);
}

The resulting table contains the YEAR (forwarded) and the added COUNTRY and GDP columns:

DATE, COUNTRY, GDB
2013, United States, 16768
2013, China,         16161
2013, India,         6776
2013, Japan,         4641
2013, Germany,       3585
2012, United States, 16163
2012, China,         14790
2012, India,         6357
2012, Japan,         4517
2012, Germany,       3472

Example: Merging N Rows and using Arrays

Based on the 'js_filter_cols' table. The js_filter_cols table contains some AMOUNT per COUNTRY. In this example we are going to merge (group-by) rows per COUNTRY and SUM the AMOUNT. As an additional demonstration, we are going to show how to group the AMOUNT values into an array.

Generated Columns

AMOUNT     : DOUBLE
AMOUNT_ARR : DOUBLE : ARRAY

Init Code
//
// Grouping Logic Functions
//

function createBuffer() {
  ctxt.buffer = { country_id : -1, rows : [] /* our row buffer */ } ;
}

function addRowToBuffer( country_id ) {
  ctxt.buffer.country_id = country_id;
  ctxt.buffer.rows.push(  copy() /* a copy of the current row */ );
}

function processBuffer( ) {
  if(ctxt.buffer.rows.length <= 0 ) {
    return;
  }

  var g_amount = 0;
  var g_amount_arr = arr() /* a new empty array */;

  for( var ii = 0; ii < ctxt.buffer.rows.length; ii++ ) {

      var row = ctxt.buffer.rows[ii];
      var amount = get( row, "AMOUNT" );

      g_amount += amount /* group-by */;
      arr_push( g_amount_arr, amount ) /* array group-by */;
  }

  var row = create() /* a new empty row */;

  set( row, "COUNTRY", ctxt.buffer.country_id );
  set( row, "AMOUNT", g_amount );
  set( row, "AMOUNT_ARR", g_amount_arr );

  fire( row /* the generated row */, ctxt.row_nb++ );

  createBuffer();
}

//
// Actual initialization code
//

createBuffer();

ctxt.row_nb = 0;

Row Processing Code
//
// Grouping by COUNTRY the AMOUNT
//
var country_id = get( "COUNTRY" );

if( ctxt.buffer.rows.length > 0 && country_id != ctxt.buffer.country_id ) {
   processBuffer();
}

addRowToBuffer( country_id );

Completion Code
//
// Do not forget to flush the current buffer
//
processBuffer( ctxt.buffer );

The resulting table contains the COUNTRY and AMOUNT (and AMOUNT_ARR) columns; as you can see the AMOUNT column contains the SUM of the AMOUNT_ARR values:

COUNTRY, AMOUNT, AMOUNT_ARR
Canada,  90.0,   [10.0, 20.0, 20.0, 40.0]
USA,     1800.0, [100.0, 200.0, 500.0, 1000.0]
France,  30.0,   [10.0, 20.0]
Spain,   300.0,  [100.0, 200.0]

Working with Dates

icCube uses Joda Date and DateTime to manage dates and both are available in Javascript. If a data column defines a date type a Joda date object will be returned by the get("colName") method.

Creating a new date is easy, just type 'newDate(year,month,day)' or 'newDateTime(year,month,day,hour,minute,second)'. This will return a Javascript object wrapping the underlying Java class. You can use these dates with the available methods. For example :

        myDate.plusDays(2), myDate.plusMonths(2) and myDate.plusYears(10)
    

It is possible to do more advanced calculations using Joda library, let's calculate the difference in days between two days :

        var days = Java.type( "org.joda.time.Days" );
        var bucketSize = days.daysBetween(startDate, endDate).getDays();
    

Note, that as for other Java classes methods from both Java classes are available in Javascript (check documentation link for more information about LocalDate and LocalDateTime).

Walkthrough

The schema mentioned in this page can be downloaded here. It requires icCube v5.x onwards and a JRE 1.8 (for the Javascript code transformation).

Next chapter: ETL caching table describes how to use cached tables in the the Javascript view.