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're 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"
}

Use the Create view endpoint to add a view to a table.

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. Use the Update default view API endpoint to change the default 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.