Query 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.

The structure of the view schema is similar, yet simpler than a transform. If a transform were defined like this:

{
  "name": "my_special_transform",
  "type": "csv",
	"table": "3fa85f64-5717-4562-b3fc-2c963f66afa6",
  "settings": {
    "output_columns": [{
      "position": 0,
      "name": "timestamp",
      "type": "datetime",
      "treatment": "primary",
      "format": "2006-01-02 15:04:05 MST",
      "default": ""
    }, {
      "position": 1,
      "name": "the_data",
      "type": "uint64",
      "treatment": "metric"
    }],
    "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.

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

The following table lists the currently supported data types in Hydrolix. If a data type is required that is not listed, contact support@hydrolix.io

Type Description
datetime Uses the same format as used in the transform. Currently supports second level granularity.
double Signed 64 bit floating point number

Supports NaN, +Inf, -Inf
string Variable length string

Use this for VARCHAR, BLOB, CLOB, and STRING from other data systems
uint64 Unsigned 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 BETWEEN1977-04-25 00:00:00AND2010-04-25 23:00:00)