Skip to content

Custom Views

Hydrolix separates the write schema (storage) from the read schema. Views are the read schema for accessing Hydrolix tables.

Views fall into two categories:

  • Automatic view - Every Hydrolix table has a single autoview combining all columns that transforms define.
  • Custom views - Users can create additional views that limit the client to a subset of columns. Queries against a specific view can only see the columns that the view defines.

Use a view⚓︎

To refer to a view schema in a query, append it to the table name after a #. When using this syntax, enclose the selection in backticks in the FROM clause.

1
2
3
SELECT …….
  FROM project_name.`table_name#view_name`
  WHERE …….

This example shows a query using a view.

1
2
3
4
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’)

Define a view⚓︎

View schemas associate with tables. A view schema can contain any columns that the ingest transforms define.

Tip

Hydrolix can ingest data from different sources into a single table. Compare data sources without complex JOIN statements.

View definitions are nearly identical to transform definitions. View definitions don't support the attributes compression, wurfl, format_details, sql_transform, and sample_data, though they are optional for transforms. Columns in a view don't include a source attribute on each datatype.

The structure of a view schema is similar to a transform.

These examples show a view and corresponding transform side by side. Toggle between the tabs to see the attributes that view definitions don't need.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
{
  "name": "view_example",
  "description": null,
  "settings": {
    "is_default": true,



    "output_columns": [
      {
        "name": "timestamp",
        "datatype": {
          "type": "datetime",
          "index": false,
          "primary": true,



          "format": "2006-01-02 15:04:05 MST",
          "resolution": "seconds",
          "default": null,
          "script": null
        }
      },
      {
        "name": "the_data",
        "datatype": {
          "type": "uint64",
          "index": true,



          "format": null,
          "resolution": "seconds",
          "default": null,
          "script": null
        }
      }
    ]
  },












  "table": "18376f54-0fbc-4a0b-ab9f-b2430ae433cc"
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
{
  "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"
}

To create a view, make a POST call to the /v1/orgs/{org_id}/projects/{project_id}/tables/{table_id}/views/ endpoint with the view in the request body.

is_default setting⚓︎

This setting designates a view as the default view. Only one view on a table can be the default at one time. If a custom view schema is the default, the #view part of the SELECT statement isn't needed.

Without custom views, the query system defaults to the automatic view.

output_columns setting⚓︎

The output_columns attribute in a view defines an existing or calculated column. Each column definition must specify at least name and datatype.

It's an error to specify an output_column that doesn't exist in the underlying table.

Warning

Views don't support all data types that transforms support.

Column types and formats⚓︎

Views preserve the column's original type.

For example, a datetime type must maintain the same resolution in the view. SQL functions like formatDateTime can return the date in the desired format.

To return a column as a different data type at query time, use calculated columns.

Views and denullify⚓︎

Mark a column in a transform with the denullify attribute. Only the view schema uses this attribute.

When a column has denullify, the query system returns type-appropriate blank values for rows containing NULL values.

How denullify works⚓︎

This feature works like the SQL COALESCE function.

Marking a column with denullify doesn't change ingestion behavior. Hydrolix still stores NULL values into table partitions.

However, the returned data type depends on the column data type.

1
2
3
4
5
6
7
8
/*
 * Traditional SQL
 */
SELECT COALESCE(int_column, 0) FROM table;
/*
 * Same result, if `int_column` is declared with `denullify=True`
 */
SELECT int_column FROM table;

Denullify example⚓︎

Denullification happens after the system retrieves data from the underlying partitions.

This sequence demonstrates denullify behavior:

  • Setup: the column int_column of integer data type has "denullify": true
  • Insert: Insert a new row with NULL value in this column
  • Query: one row appears with a 0 value in the int_column because denullify is true
  • Insert: Insert a second row with an actual 0 value
  • Query: now, without filter criteria on the int_column, both rows appear with value 0
  • Query: with the filter WHERE column = 0, only one row appears, the one with the actual 0

To see both rows, use this query:

1
WHERE column = 0 OR column IS NULL

Limitations⚓︎

Views preserve the data type of stored columns. Use a SQL function in the query or define a permanent calculated column instead.

Define calculated columns from SQL expressions. Define calculated columns in a transform, and the view computes the values during query execution.

Views don't offer any data access control mechanism. For that, see the Row-level Access Control feature.