ClickHouse SQL

The Hydrolix query language is an ANSI-compliant SQL, based on the Yandex ClickHouse SQL language. Queries are passed via the Hydrolix Query API which offers programmatic access to your data.

To query data,

  1. Ingest some data
  2. Construct the WHERE clause
  3. Define the query conditions
  4. Execute the query against the Query API

Build a SQL Query

When querying Hydrolix, it is important to follow these steps:

  1. Specify the project name and the table name within the FROM portion of the SELECT statement separated by a period (.).
  FROM project_name.table_name  
  WHERE ……
  1. Specify a view schema

Hydrolix has the notion that a single data set can have different query data structures. The query data structure, or view schema allows for a user's access to a data set to be restricted to a set of columns, but can also be used to set the data type for a column. For example, something that is stored as a number may also need to be operated on as a string sometimes.

If no view is specified and a default view has been defined, the view schema is not required as part of the SQL.

  1. A where clause

When referencing data in tables, a time specification within a WHERE predicate is required. This predicate should reference the primary datetime column that was specified in the transform schema when the data was ingested.

SELECT count(timestamp) AS count
  FROM my_first_project.the_table
  WHERE (timestamp BETWEEN ‘1977-04-25 00:00:00’ AND  ‘2010-04-25 23:00:00’)
  1. Define the rest of the query.

Refer to the Hydrolix tutorials and SQL Reference to understand query syntax and see running examples.

  1. Execute the query

Queries are sent to Hydrolix via the Query API. The only required parameter is "query" to pass in the query. Query results are returned as JSON.