Supported Data Formats for RAQL
RAQL can perform queries on datasets in the following formats:
CSV (comma-separated values):
The first line in a CSV dataset must identify column names for the data.
RAQL replaces any white space character in column names with an underscore (_). For example, "First Name" becomes First_Name.
Column names that are numeric have "column_" prepended to the name. For example, "2010" becomes "column_2010".
JDBC Result Sets: returned from databases when SQL queries or stored procedures are invoked.
XML: data must be well-formed. In addition,
RAQL has the following limitations for XML data:
XML namespaces are ignored.
The structure of the XML should be flat, with a single set of repeating nodes (the rows) that contain a single level of elements (the columns) with simple content (text only). Data in any nodes that are ancestors of the repeating 'rows' is not accessible.
For example:
Data in attributes may not be accessible in some situations.
JSON: data must be well-formed. The structure of the JSON should be flat, with a single array of objects (the rows) that contain name/value pairs (the columns) with simple content (number, string, boolean). Data in any objects that are ancestors of the repeating 'rows' is not accessible.
For example:
{
"records": {
"record": [
{
"itemId": "N2390",
"price": 145.2,
...
},
{
"itemId": "G88",
"price": 16.95,
...
},
...
]
}
}
The following EMML sample does a directinvoke for getting JSON content and executes RAQL on it .
<mashup xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'
xsi:schemaLocation='http://www.openmashup.org/schemas/v1.0/EMML/../schemas/EMMLPrestoSpec.xsd'
xmlns='http://www.openmashup.org/schemas/v1.0/EMML'
xmlns:macro='http://www.openmashup.org/schemas/v1.0/EMMLMacro'
xmlns:presto='http://www.jackbe.com/v1.0/EMMLPrestoExtensions' name='newMashup'>
<variable name='data' type='document' subtype='json' />
<directinvoke endpoint='http://api.openweathermap.org/data/2.5/box/city?bbox=12,32,15,37,10&cluster=yes'
outputvariable='data' stream='true' />
<output name='result' type='document' />
<raql query='SELECT id, name FROM data/list' outputvariable='result' stream='true' />
</mashup>
Java Objects: loaded in
In-Memory Stores by external systems. Java objects must:
Be plain Java objects or beans with properties for each column of data in the dataset.
Be serializable. This is required when
In-Memory Stores use both local memory for the
Presto Server and memory from additional
BigMemory hosts. See
In-Memory Dataset Management for more information.
Have search attributes defined in the configuration for the declared
In-Memory Store where they will be stored. Search attributes provide the extraction class and other information that maps Java object properties to dataset columns and allows
RAQL to access and work with the data.
