Custom 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": "transform_example",
"description": null,
"settings": {
"is_default": false,
"sql_transform": null,
"null_values": [],
"sample_data": [],
"output_columns": [
{
"name": "timestamp",
"datatype": {
"type": "datetime",
"index": false,
"primary": true,
"source": {
"from_input_index": 0
},
"format": "2006-01-02 15:04:05 MST",
"resolution": "seconds",
"default": null,
"script": null
}
},
{
"name": "the_data",
"datatype": {
"type": "uint64",
"index": true,
"source": {
"from_input_index": 1
},
"format": null,
"resolution": "seconds",
"default": null,
"script": null
}
}
],
"compression": "none",
"format_details": {
"delimiter": ",",
"skip_head": 1,
"quote": "\"",
"comment": "#",
"skip_comments": false,
"escape": "/",
"windows_ending": false,
"buffer_size": 4096
}
},
"type": "csv",
"table": "18376f54-0fbc-4a0b-ab9f-b2430ae433cc"
}
Then the view might look something like this:
{
"name": "view_example",
"description": null,
"settings": {
"is_default": true,
"output_columns": [
{
"name": "timestamp",
"datatype": {
"type": "datetime",
"index": false,
"primary": true,
"source": {
"from_input_index": 0
},
"format": "2006-01-02 15:04:05 MST",
"resolution": "seconds",
"default": null,
"script": null
}
},
{
"name": "the_data",
"datatype": {
"type": "uint64",
"index": true,
"source": {
"from_input_index": 1
},
"format": null,
"resolution": "seconds",
"default": null,
"script": null
}
}
]
},
"table": "18376f54-0fbc-4a0b-ab9f-b2430ae433cc"
}
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 two elements, name
and datatype
.
If an output_column
is specified that does not exist in the table, an error will occur.
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.
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 7 months ago