Presto Analytics : RAQL Queries : Migrate RAQL Queries from Version 3.7 to 3.8
Migrate RAQL Queries from Version 3.7 to 3.8
With this release, there are several significant changes to RAQL that require changes to existing RAQL queries from 3.7 or earlier. Edit mashups to:
*Update <storeto> Syntax to store datasets in separate, named In-Memory Stores. The original syntax which store multiple datasets in the same store is no longer supported.
*Add Schemas or Update Datatypes for CSV or XML datasets. Changes in implicit casting may cause existing queries to break without this additional meta-data.
*Update Built-In Plain Functions for 3.8 for queries that use deprecated functions or functions that are no longer supported.
Note:  
In a few cases, the functions listed are supported but the parameters or the semantics of the function have changed sufficiently that queries may need updates to perform as expected.
*Update Built-In Aggregate or Window Analytic Functions for 3.8 for queries that use deprecated functions or functions that are no longer supported.
Note:  
In a few cases, the functions listed are supported but the parameters or the semantics of the function have changed sufficiently that queries may need updates to perform as expected.
*Implement and Replace User-Defined Functions based on the new RAQL User Defined Functions API. This API has changed completely, so user-defined functions from previous versions must be updated.
Update <storeto> Syntax
The original syntax for storing datasets in an In-Memory Store allowed you to store multiple datasets in the same store with each dataset as one entry. This syntax and behavior is no longer supported in 3.8.
You must update any RAQL query that uses this syntax to store the dataset in a separate, named In-Memory Store. See <storeto> for details on the new syntax and links to examples.
In addition, the underlying mechanism used for storing datasets in an In-Memory Store has been extended. Therefore, search attributes must not be configured explicitly. Instead, the cache must be configured as being searchable and dynamic indexing must be enabled (use <searchable allowDynamicIndexing="true" /> in cache configuration file) and the search attributes must be specified in the <storeto> statement using the searchattributes attribute.
Add Schemas or Update Datatypes
With version 3.8, RAQL has added, and in some cases, changed functionality to more closely follow SQL. As a result, queries on datasets that do not have datatype and schema information may break as some of the implicit casting from previous releases is no longer supported.
With CSV or XML datasets, you may need to provide datatype and schema information to permit existing queries to work correctly. You can add this meta-data to mashups directly, or add them as Presto global attributes that you can refer to in many mashups. See Providing Dataset Path and Datatype Information in a Schema for instructions.
In addition, the decimal datatype from 3.7 and earlier is no longer valid. Update any existing schemas with double or bigdecimal instead. See Valid RAQL Datatypes for a complete list of supported datatypes.
Update Built-In Plain Functions for 3.8
Deprecated or Unsupported 3.7 Functions
Replace with 3.8 Function
concat(String column-or-literal, String column-or-literal)
Use the || operator
"date"(String column)
"date"(String column, String format)
to_date
"day"(Date column)
"day"(String column)
"day"(Long column)
extract_day
decimal(Double column) (Unsupported Function)
decimal(Long column)
decimal(String column)
cast(columnas datatype)
"hour"(Date column)
"hour"(String column)
"hour"(Long column)
extract_hour
length(String column) (Unsupported Function)
char_length or character_length
"minute"(Date column)
"minute"(String column)
"minute"(Long column)
extract_minute
"month"(Date column)
"month"(String column)
"month"(Long column)
"month"(Object column)
extract_month
number(String column) (Unsupported Function)
cast(columnas datatype)
round(Object column)
This function no longer accepts objects and implicitly casts them to numeric values.
Mashups that relied on this implicit casting must update queries to use the following:
round(CAST(Object column AS double))
If no number of decimal places are specified, this function now rounds to zero decimals which may also affect mashup results. Mashups that require two decimal places must update queries to use the following:
round(Number column, "2")
"second"(Date column)
"second"(String column)
"second"(Long column)
extract_second
"string"(String column)
cast(columnas datatype)
substr(String column, Integer begin, Integer end)
substring(String column, Integer start-position, Integer length
Note:  
This is not a simple substitution as the semantics of the parameters that identify the characters to extract have different meanings in these functions.
In addition to different parameters, character positions in substr use zero-based indexes while substring uses 1-based indexes.
to_long(String column)
cast(columnas datatype)
"week"(Date column)
"week"(String column)
"week"(Long column)
extract_week
"year"(Date column)
"year"(String column)
"year"(Long column)
"year"(Object column)
extract_year
For more information on any built-in plain function, see Built-In RAQL Functions
Update Built-In Aggregate or Window Analytic Functions for 3.8
Deprecated or Unsupported 3.7 Functions
Replace with 3.8 Function
correlation(Number column, Number column)
corr(Number column, Number column)
covariance(Number column, Number column)
covar(Number column, Number column)
denserank()
dense_rank()
firstvalue(Object column)
first_value(Object column)
analytics.kmean_clusters(String column-list, Integer k, Integer iterations, String measure)
analytics.kmeans_clusters(String column1[,String column2,...String columnN]; Integer k, Integer iterations, String measure)
This changes the function name (for consistency) and changes the signature of the function. The syntax change supports a variable number of columns as parameters to identify the features for clustering.
analytics.kmeans_observations(String column-list, Integer k, Integer iterations, String measure)
analytics.kmeans_observations(String column1[,String column2,...String columnN]; Integer k, Integer iterations, String measure)
This changes the signature of the function to support a variable number of columns as parameters to identify the features for clustering.
lastvalue(Object column)
last_value(Object column)
mean(Number column) (Unsupported Function)
avg(Number column)
rownumber()
row_number()
For more information on any built-in analytic function, see Built-In RAQL Functions
Implement and Replace User-Defined Functions
The RAQL User-Defined Function API has changed completely for version 3.8. See the Presto RAQL User-Defined Function API for reference information on this API in version 3.8.
The previous API is no longer supported. If you have implemented and deployed user-defined functions for RAQL in version 3.7 or earlier, you must re-implement these functions using the new API and redeploy them in version 3.8. See Create and Add User-Defined Functions for RAQL Queries for instructions and examples of functions based on this new API.
Copyright © 2013-2015 Software AG, Darmstadt, Germany.

Product LogoContact Support   |   Community   |   Feedback