SQL Support for the Pentaho Data Service.
The Pentaho Data Service is designed to support a subset of SQL clauses and literals that are particularly useful for data blending, optimizations, and other scenarios. The following lists what we support. Limitations are noted near the end of this article.
Supported SQL Literals
The Pentaho Data Service supports the following literals.
- Strings have single quotes around them. The escape character for a single quote is another single quote, like this ''.
- Dates have square brackets around them. The following formats are supported: [yyyy/MM/dd HH:mm:ss.SSS], [yyyy/MM/dd HH:mm:ss] and [yyyy/MM/dd].
- For an IN list in a SQL statement, the date formats can have single quotes around them and dashes that replace slashes, like this: SELECT * FROM BUILDS WHERE BuildDate IN (‘2015-03-18’, ‘2015-03-22’). Note that you cannot surround a date format with a bracket date in an IN list.
- Number and BigNumber should have no grouping symbols. Use a period to represent a decimal, like this: 123.45.
- Integers should contain only digits.
- Boolean values can be TRUE or FALSE.
Supported SQL Clauses
The Pentaho Data Service supports the following clauses.
|Clause||What is Supported|
|GROUP BY|| |
|ORDER BY|| |
Other Development Considerations
There are a few considerations to keep in mind as you design your data service and transformation.
- You cannot JOIN one data service virtual table to another.
- Pentaho Data Services uses the Memory Group by step to group. This step keeps all the groups in memory to avoid sorts that can slow down the data service. But, if you plan to use many groups, watch your memory consumption on the server to make sure you don't exceed its limits.
- You can't specify the same field twice in the same SELECT clause.
- Calculations and functions like string concatenation are not supported. But, you can do these things in the data service transformation.