Custom Read Views
Hydrolix has the notion that a single data set can have different query formats. The query data structure, or view allows for a user's access to a data set to be restricted to a set of columns.
A view can contain columns from more than one Ingest Transform.
To create a Query View, use the UI or call the View API.
View Schema Document
Query view schemas are associated with tables. A view schema can contain any of the columns the user has access to that have been defined by an ingest transform. A view can contain columns that are across any of the ingest transforms for a table.
Tip!
Being able to tie columns from different data sources is a major benefit of Hydrolix. No need to use Join's simple use a view!
The structure of the view schema is similar, to a transform. If a transform were defined like this:
{
"name": "my_special_transform",
"type": "csv",
"table": "3fa85f64-5717-4562-b3fc-2c963f66afa6",
"settings": {
"output_columns": [
{
"name": "timestamp",
"position": 0,
"datatype": {
"type": "datetime",
"primary": true,
"format": "2006-01-02 15:04:05 MST"
}
},
{
"name": "the_data",
"position": 1,
"datatype": {
"type": "uint64"
}
}
],
"compression": "gzip",
"format_details": {
"skip_head": 2,
"delimiter": 9
}
}
}
Then the view might look something like this:
{
"name": "my_view",
"description": "string",
"table": "3fa85f64-5717-4562-b3fc-2c963f66afa6",
"settings": {
"is_default": true,
"output_columns": [{
"name": "timestamp",
"type": "datetime",
"treatment": "primary"
},
{
"name": "the_data",
"type": "uint64",
"treatment": "metric"
}
]
}
}
is_default
is_default
This element is used to designate this view as the default. Only one view on a table may be set to default at one time. If a default view schema is defined on a table, the #view
part of the SELECT
statement is not required.
output_columns
output_columns
In a view, output_columns
is an array of elements. Each element is named for a column that should be part of the view. Each element contains at least three elements, name
, type
and treatment
.
If an output_column
is specified that does not exist in the table, an error will occur.
treatment
treatment
Every column requires a treatment. A treatment is used to specify how a column will be utilized in the query. A column can have only one treatment. The allowed treatments are similar to the Ingest Transform.
Treatment | Supported data types | Description |
---|---|---|
primary | datetime | The column to be used as the main index. It is used in determining the HDX partition. Each data set must have EXACTLY ONE Primary column. Primary may not have any Null values. |
tag | datetime, uint64, string | Indexed metadata about a metric. Used for filtering. |
metric | uint64, double | Non-indexed values. |
Note: virtual
and ignore
, which are valid in transforms are not valid treatments in views.
type
type
The following table lists the currently supported data types in Hydrolix. If a data type is required that is not listed, contact [email protected]
Type | Description |
---|---|
array | For array values |
datetime | Uses the same format as used in the transform. Currently supports second level granularity. |
bool/boolean | use this for boolean values |
double | Signed 64 bit floating point number Supports NaN, +Inf, -Inf |
int/8/32/64 | Signed 8/32/64 bit integer Range [0 : 18446744073709551615] |
string | Variable length string Use this for VARCHAR, BLOB, CLOB, and STRING from other data systems |
uint/8/32/64 | Unsigned 8/32/64 bit integer Range [0 : 18446744073709551615] |
Note:
Not all data types available in transforms are available in views. Data types available in transforms that are not available in views are transformed into one of the data types above.
Date Formats and Views
In a view a datetime
type cannot be given a different format than the one used at ingest time, however, the formatDateTime
function can be used to return the date in a different format.
Using a view in a query
To refer to a schema in a query, append it to the table name after a #
. When using this syntax ` are required around the selection in the FROM
clause
SELECT …….
FROM project_name.`table_name#view_name `
WHERE …….
For example:
SELECT count(timestamp)
AS count
FROM my_first_project.`the_table#my_restricted_view`
WHERE (timestamp BETWEEN ‘1977-04-25 00:00:00’ AND ‘2010-04-25 23:00:00’)
Updated about 1 month ago