Table Functions

A table function refers to a user-defined function that returns a table or a set of rows as its result. Table functions are used to encapsulate logic and calculations, allowing you to treat the result of the function as if it were a table in your SQL queries.

1. How to Develop and Deploy a Table Function

First you need to add the LeanXcale maven repository and the qe-tableFunctions dependency to your pom.xml file:

<repositories>
    <repository>
        <id>maven-releases</id>
        <url>https://nexus.leanxcale.com/repository/maven-releases</url>
    </repository>
</repositories>

<dependencies>
    <dependency>
        <groupId>com.leanxcale</groupId>
        <artifactId>qe-tableFunctions</artifactId>
        <version>1.7.6</version>
    </dependency>
</dependencies>

In order to create a custom table function, a Java class has to be written implementing the AutoloadTableFunction interface and defining the following methods:

Function Description Return

getFunctionName()

Define the custom table function name

String

getDescription()

Provides the custom table function description

String

eval(<table function arguments>)

Generate the collection of rows to be queried

ScannableTable

You can define as many eval functions as you need as long as their signatures are different. In order to write the eval functions returning ScannableTable, the following methods should be implemented:

  • getRowType

    • RelDataType getRowType(RelDataTypeFactory typeFactory)

    • Description:

      • Returns this table’s row type. This is a struct type whose fields describe the names and types of the columns in this table. The implementer must use the type factory provided. This ensures that the type is converted into a canonical form; other equal types in the same query will use the same object.

    • Parameters:

      • typeFactory: Type factory with which to create the type.

    • Returns:

      • Row type

  • getStatistic

    • Statistic getStatistic()

    • Description:

      • Statistic getStatistic()

    • Returns:

      • Statistic

  • getJdbcTableType

    • Description:

      • Returns the type of table.

    • Returns:

      • Table type.

  • sRolledUp

    • Description:

      • Determines whether the given column has been rolled up.

    • Returns:

      • true if the column is rolled up.

  • rolledUpColumnValidInsideAgg

    • Description:

      • Determines whether the given rolled up column can be used inside the given aggregate function. You can assume that isRolledUp(column) is true.

    • Parameters:

      • column - The column name for which isRolledUp is true.

      • call - The aggregate call.

      • parent - Parent node of call in the SqlNode tree.

      • config - Config settings. May be null.

    • Returns:

      • true if the given aggregate call is valid.

All the table function classes must belong to the package com.leanxcale in order to have them auto-loaded when LeanXcale starts.

Find below a complete example:

package com.leanxcale;

import com.leanxcale.QE.tableFunction.AutoloadTableFunction;
import org.apache.calcite.DataContext;
import org.apache.calcite.config.CalciteConnectionConfig;
import org.apache.calcite.linq4j.Enumerable;
import org.apache.calcite.linq4j.Linq4j;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeFactory;
import org.apache.calcite.schema.ScannableTable;
import org.apache.calcite.schema.Schema;
import org.apache.calcite.schema.Statistic;
import org.apache.calcite.schema.Statistics;
import org.apache.calcite.sql.SqlCall;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.calcite.util.ImmutableBitSet;
import java.util.Collections;

public class SeriesGeneratorTableFunction implements AutoloadTableFunction {

  public static String getFunctionName() {
    return "series_generator";
  }

  public ScannableTable eval(Integer start, Integer stop, Integer step) {
    int actualStep = (null == step) ? 1 : step;
    int numResults = (1 + (stop - start)/actualStep);
    return new ScannableTable() {
      @Override
      public Enumerable<Object[]> scan(DataContext dataContext) {
        Object[][] results = new Object[numResults][1];
        for (int inx = 0; inx < numResults; inx++) {
          results[inx][0] = start + inx*actualStep;
        }
        return Linq4j.asEnumerable(results);
      }

      @Override
      public RelDataType getRowType(RelDataTypeFactory typeFactory) {
        return typeFactory.builder()
                .add("SERIES", SqlTypeName.INTEGER)
                .build();
      }

      @Override
      public Statistic getStatistic() {
        return Statistics.of(numResults, Collections.singletonList(ImmutableBitSet.of(numResults)));
      }

      @Override
      public Schema.TableType getJdbcTableType() {
        return Schema.TableType.TABLE;
      }

      @Override
      public boolean isRolledUp(String s) {
        return false;
      }

      @Override
      public boolean rolledUpColumnValidInsideAgg(String s, SqlCall sqlCall, SqlNode sqlNode, CalciteConnectionConfig calciteConnectionConfig) {
        return false;
      }
    };
  }
}

Once you do mvn package, a .jar file gets created with the custom table functions you defined. This .jar should be included in the Query Engine’s classpath (lxs/LX-BIN/lib). The Query Engine should be restarted after adding the table function .jar file. For the cloud version, the leanxcale console will provide the means to add the table function code to your database.

2. Using Table Functions

You can use a table function in the FROM clause of a query as the first argument to the function table. In this example, series_generator is the table function:

SELECT SERIES FROM table(series_generator(1, 10, 1)) WHERE SERIES > 3;

+--------+
| SERIES |
+--------+
| 4      |
| 5      |
| 6      |
| 7      |
| 8      |
| 9      |
| 10     |
+--------+
7 rows selected (0.008 seconds)

Take into account that if the parameter you’re passing to your table function is a string, you’ll need to use single quotes. Double quotes are not allowed. This is an example:

SELECT * FROM table(my_new_tablefunction('test_string'));

But this does not work:

SELECT * FROM table(my_new_tablefunction("test_string"));

3. Table Function Utils

In order to ease the writing of table function, you can use the TableFunctionUtils class. You will be able to execute SQL queries within your Table Function.

ResultType Function Description

TableFunctionUtils

utils()

Static function to build an instance for the table function utils according to the current connection

Connection

getConnection()

Provide an connection to perfom SQL actions. This connection cannot be committed or rollbacked

long

localTimestamp()

Returns the current date and time in the session time zone

long

currentTimestamp()

Returns the current date and time in the session adjusted time zone

Long

getTid()

Return current transaction

String

getDbName()

Return connection’s database

String

getSchemaName()

Return connection’s schema

String

uuid()

Generate a UUID ////

Session

session(Settings Settings)

Creates a new session to the KiVi database. It shares the current KiVi connection.

Session

session()

Creates a new session to the KiVi database. It shares the current KiVi connection and transaction to connect through the direct API. ////

Table

getTable(String tableName)

Provide a table object that exist within current transaction context so you can perform basic operations on it

long

currVal(String sequence)

Get current sequence value or null if it is not set

long

nextVal(String sequence)

Get next sequence value and set current sequence value

String

geohash(List<Object> geohashSourceValues)

Calculate geohash from values. If there is one element, it expects it to be a WKT (well known text). If there is 2, the first one is the latitude and the second is the longitude.

Find below an example:

public class TableFunctionUtilsExample implements AutoloadTableFunction {
  public static final String functionName = "TableFunctionUtilsExample";

  public static String getFunctionName() {
    return functionName;
  }

  public static String getDescription() {
    return functionName;
  }

  public ScannableTable eval(Object parentTableName, Object sonTableName) {
    return new ScannableTable() {
      @Override
      public Enumerable<Object[]> scan(DataContext dataContext) {
        List<Object[]> result = new ArrayList<>();

        TableFunctionUtils utils = TableFunctionUtils.utils();
        Settings settings = new Settings();
        settings.setDatabase(utils.getDbName());
        settings.setSchema(utils.getSchemaName());
        settings.disableConflictChecking();
        settings.disableLogging();
        try (Session session = utils.session(settings)) {
          Table table = session.database().getTable((String) parentTableName);
          try (ResultSet rs = utils.getConnection().createStatement().executeQuery("select i_name, max_price from " + sonTableName)) {
            while (rs.next()) {
              Tuple tuple = table.find()
                  .filter(Filters.eq("i_name", rs.getString(1)).and(Filters.eq("i_price", rs.getInt(2))))
                  .iterator().next();
              Object[] row = new Object[3];
              row[0] = tuple.get("i_id");
              row[1] = tuple.get("i_name");
              row[2] = tuple.get("i_price");
              result.add(row);
            }
          }
        } catch (Exception exception) {
          throw new LeanxcaleRuntimeException(exception);
        }

        return Linq4j.asEnumerable(result);
      }

      @Override
      public RelDataType getRowType(RelDataTypeFactory typeFactory) {
        RelDataTypeFactory.Builder builder = typeFactory.builder();
        builder.add("ID", SqlTypeName.INTEGER);
        builder.add("NAME", SqlTypeName.VARCHAR);
        builder.add("PRICE", SqlTypeName.INTEGER);
        return builder.build();
      }

      @Override
      public Statistic getStatistic() {
        return Statistics.of(1, Collections.singletonList(ImmutableBitSet.of(1)));
      }

      @Override
      public Schema.TableType getJdbcTableType() {
        return Schema.TableType.TABLE;
      }

      @Override
      public boolean isRolledUp(String s) {
        return false;
      }

      @Override
      public boolean rolledUpColumnValidInsideAgg(String s, SqlCall sqlCall, SqlNode sqlNode, CalciteConnectionConfig calciteConnectionConfig) {
        return false;
      }
    };
  }
}