[
https://issues.apache.org/jira/browse/CALCITE-115?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-115:
--------------------------------
Description:
Add user-defined table functions.
Table functions are defined using two methods in the Schema SPI:
getTableFunctionNames() and getTableFunctions(String name). (This request does
not provide a way to define them in a model.json file.)
One particular instance of the Schema SPI: ReflectiveSchema should use
reflection to find candidate methods and expose them as table functions.
Methods need to be public and return Table (or a sub-class). For example,
{code}
public static class HrSchema {
public final Employee[] emps = {
new Employee(100, 10, "Bill", 10000, 1000),
new Employee(200, 20, "Eric", 8000, 500),
new Employee(150, 10, "Sebastian", 7000, null),
new Employee(110, 10, "Theodore", 11500, 250),
};
public static Table generateStrings(final int count) {
return new AbstractQueryableTable(IntString.class) {
public RelDataType getRowType(RelDataTypeFactory typeFactory) {
return typeFactory.createJavaType(IntString.class);
}
public <T> Queryable<T> asQueryable(QueryProvider queryProvider,
SchemaPlus schema, String tableName) {
BaseQueryable<IntString> queryable =
new BaseQueryable<IntString>(null, IntString.class, null) {
public Enumerator<IntString> enumerator() {
return new Enumerator<IntString>() {
static final String z = "abcdefghijklm";
int i = -1;
IntString o;
public IntString current() {
return o;
}
public boolean moveNext() {
if (i < count - 1) {
o = new IntString(i, z.substring(0, i % z.length()));
++i;
return true;
} else {
return false;
}
}
public void reset() {
i = -1;
}
public void close() {
}
};
}
};
//noinspection unchecked
return (Queryable<T>) queryable;
}
};
}
}
public static class IntString {
public final int n;
public final String s;
public IntString(int n, String s) {
this.n = n;
this.s = s;
}
public String toString() {
return "{n=" + n + ", s=" + s + "}";
}
}
{code}
Then the user can write
{code}
select * from table(s.GenerateStrings(5))
{code}
and receive a data set with columns (n int, s string). The table-function is
implemented by generating a call to the method to get the `Table`, then calling
the table's `asQueryable` method, then calling the queryable's `enumerator()`
method.
Optiq invokes method that returns a Table at prepare time, with dummy argument
values (constants if the arguments are constant, null if the arguments are
nullable, otherwise the default value (0, false, etc.), to deduce the names and
types of the output columns. And of course it invokes it again at run-time with
the real values.
A table function may have parameters that are of type `Table`. For each such
parameter, the user must supply a query when writing SQL.
---------------- Imported from GitHub ----------------
Url: https://github.com/julianhyde/optiq/issues/115
Created by: [julianhyde|https://github.com/julianhyde]
Labels: enhancement,
Created at: Fri Jan 17 20:39:32 CET 2014
State: open
was:
Add user-defined table functions.
Table functions are defined using two methods in the Schema SPI:
getTableFunctionNames() and getTableFunctions(String name). (This request does
not provide a way to define them in a model.json file.)
One particular instance of the Schema SPI: ReflectiveSchema should use
reflection to find candidate methods and expose them as table functions.
Methods need to be public and return Table (or a sub-class). For example,
```java
public static class HrSchema {
public final Employee[] emps = {
new Employee(100, 10, "Bill", 10000, 1000),
new Employee(200, 20, "Eric", 8000, 500),
new Employee(150, 10, "Sebastian", 7000, null),
new Employee(110, 10, "Theodore", 11500, 250),
};
public static Table generateStrings(final int count) {
return new AbstractQueryableTable(IntString.class) {
public RelDataType getRowType(RelDataTypeFactory typeFactory) {
return typeFactory.createJavaType(IntString.class);
}
public <T> Queryable<T> asQueryable(QueryProvider queryProvider,
SchemaPlus schema, String tableName) {
BaseQueryable<IntString> queryable =
new BaseQueryable<IntString>(null, IntString.class, null) {
public Enumerator<IntString> enumerator() {
return new Enumerator<IntString>() {
static final String z = "abcdefghijklm";
int i = -1;
IntString o;
public IntString current() {
return o;
}
public boolean moveNext() {
if (i < count - 1) {
o = new IntString(i, z.substring(0, i % z.length()));
++i;
return true;
} else {
return false;
}
}
public void reset() {
i = -1;
}
public void close() {
}
};
}
};
//noinspection unchecked
return (Queryable<T>) queryable;
}
};
}
}
public static class IntString {
public final int n;
public final String s;
public IntString(int n, String s) {
this.n = n;
this.s = s;
}
public String toString() {
return "{n=" + n + ", s=" + s + "}";
}
}
```
Then the user can write
```sql
select * from table(s.GenerateStrings(5))
```
and receive a data set with columns (n int, s string). The table-function is
implemented by generating a call to the method to get the `Table`, then calling
the table's `asQueryable` method, then calling the queryable's `enumerator()`
method.
Optiq invokes method that returns a Table at prepare time, with dummy argument
values (constants if the arguments are constant, null if the arguments are
nullable, otherwise the default value (0, false, etc.), to deduce the names and
types of the output columns. And of course it invokes it again at run-time with
the real values.
A table function may have parameters that are of type `Table`. For each such
parameter, the user must supply a query when writing SQL.
---------------- Imported from GitHub ----------------
Url: https://github.com/julianhyde/optiq/issues/115
Created by: [julianhyde|https://github.com/julianhyde]
Labels: enhancement,
Created at: Fri Jan 17 20:39:32 CET 2014
State: open
> User-defined table functions
> ----------------------------
>
> Key: CALCITE-115
> URL: https://issues.apache.org/jira/browse/CALCITE-115
> Project: Calcite
> Issue Type: Improvement
> Reporter: GitHub Import
> Labels: github-import
>
> Add user-defined table functions.
> Table functions are defined using two methods in the Schema SPI:
> getTableFunctionNames() and getTableFunctions(String name). (This request
> does not provide a way to define them in a model.json file.)
> One particular instance of the Schema SPI: ReflectiveSchema should use
> reflection to find candidate methods and expose them as table functions.
> Methods need to be public and return Table (or a sub-class). For example,
> {code}
> public static class HrSchema {
> public final Employee[] emps = {
> new Employee(100, 10, "Bill", 10000, 1000),
> new Employee(200, 20, "Eric", 8000, 500),
> new Employee(150, 10, "Sebastian", 7000, null),
> new Employee(110, 10, "Theodore", 11500, 250),
> };
> public static Table generateStrings(final int count) {
> return new AbstractQueryableTable(IntString.class) {
> public RelDataType getRowType(RelDataTypeFactory typeFactory) {
> return typeFactory.createJavaType(IntString.class);
> }
> public <T> Queryable<T> asQueryable(QueryProvider queryProvider,
> SchemaPlus schema, String tableName) {
> BaseQueryable<IntString> queryable =
> new BaseQueryable<IntString>(null, IntString.class, null) {
> public Enumerator<IntString> enumerator() {
> return new Enumerator<IntString>() {
> static final String z = "abcdefghijklm";
> int i = -1;
> IntString o;
> public IntString current() {
> return o;
> }
> public boolean moveNext() {
> if (i < count - 1) {
> o = new IntString(i, z.substring(0, i % z.length()));
> ++i;
> return true;
> } else {
> return false;
> }
> }
> public void reset() {
> i = -1;
> }
> public void close() {
> }
> };
> }
> };
> //noinspection unchecked
> return (Queryable<T>) queryable;
> }
> };
> }
> }
> public static class IntString {
> public final int n;
> public final String s;
> public IntString(int n, String s) {
> this.n = n;
> this.s = s;
> }
> public String toString() {
> return "{n=" + n + ", s=" + s + "}";
> }
> }
> {code}
> Then the user can write
> {code}
> select * from table(s.GenerateStrings(5))
> {code}
> and receive a data set with columns (n int, s string). The table-function is
> implemented by generating a call to the method to get the `Table`, then
> calling the table's `asQueryable` method, then calling the queryable's
> `enumerator()` method.
> Optiq invokes method that returns a Table at prepare time, with dummy
> argument values (constants if the arguments are constant, null if the
> arguments are nullable, otherwise the default value (0, false, etc.), to
> deduce the names and types of the output columns. And of course it invokes it
> again at run-time with the real values.
> A table function may have parameters that are of type `Table`. For each such
> parameter, the user must supply a query when writing SQL.
> ---------------- Imported from GitHub ----------------
> Url: https://github.com/julianhyde/optiq/issues/115
> Created by: [julianhyde|https://github.com/julianhyde]
> Labels: enhancement,
> Created at: Fri Jan 17 20:39:32 CET 2014
> State: open
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
