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": "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

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

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

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.

Note: virtual and ignore, which are valid in transforms are not valid treatments in views.

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]

🚧

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’)