Skip to content

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 as long as those transforms are all part of the same table.

To create a Query View, you can use 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 ingest data from different sources into a single table is a major benefit of Hydrolix. This way, you can easily compare different data sources without complex JOIN statements.

The structure of the view schema is similar to a transform. Let's take a look at a transform, then see how a corresponding view would look.

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

Then the view might look something like this:

 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
{
  "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,
          "format": null,
          "resolution": "seconds",
          "default": null,
          "script": null
        }
      }
    ]
  },
  "table": "18376f54-0fbc-4a0b-ab9f-b2430ae433cc"
}

Note that the fields are almost exactly the same. However, "from_input_index" should be omitted from the transform or there will be an error.

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

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

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.

Use a view in a query⚓︎

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

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

For example:

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