Module: ClickHouse::SQL

Defined in:
lib/clickhouse/sql.rb,
lib/clickhouse/sql/raw.rb,
lib/clickhouse/sql/join.rb,
lib/clickhouse/sql/part.rb,
lib/clickhouse/sql/error.rb,
lib/clickhouse/sql/query.rb,
lib/clickhouse/sql/table.rb,
lib/clickhouse/sql/setting.rb,
lib/clickhouse/sql/version.rb,
lib/clickhouse/sql/fragment.rb,
lib/clickhouse/sql/placeholder.rb,
lib/clickhouse/sql/select_item.rb,
lib/clickhouse/sql/select_query.rb,
lib/clickhouse/sql/placeholder_type.rb,
lib/clickhouse/sql/bind_index_manager.rb

Overview

Builds composable ClickHouse SELECT queries for ClickHouse::HTTPClient.

Keep ClickHouse SQL visible, but pass runtime values through named typed placeholders ({name:Type}). Untyped placeholders are fragment slots for trusted SQL structure such as column references or subqueries: anonymous {} slots are filled by positional arguments in source order, and named {name} slots by keyword arguments.

Table handles (ClickHouse::SQL.table) build validated, qualified column references so shared fragments stay unambiguous when queries join tables with overlapping column names.

Examples:

Build and execute a typed SELECT query joining two tables

CH = ClickHouse::SQL

events = CH.table("events")
accounts = CH.table("accounts", as: "a")

query = CH
  .select(events.columns(:id, :timestamp), accounts[:status])
  .from(events)
  .left_join(accounts, final: true, on: [
    CH.fragment("{} = {}", accounts[:id], events[:account_id]),
  ])
  .where("{} = {account_id:UUID}", events[:account_id], account_id: )
  .where("{}.{tag_key:Identifier} = {value:String}", events[:tags], tag_key: "geo.country", value: "AU")

ClickHouse::HTTPClient.select(query.to_query, :analytics)

Defined Under Namespace

Modules: Part Classes: BindIndexManager, Error, Fragment, Join, Placeholder, PlaceholderType, Query, Raw, SelectItem, SelectQuery, Setting, Table

Constant Summary collapse

UUID_REGEX = 
/\A[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}\z/i
IDENTIFIER_REGEX =

Validates {x:Identifier} placeholder values, which ClickHouse substitutes server-side with proper identifier quoting; permissive enough for hyphenated tag keys.

/\A[A-Za-z_][0-9A-Za-z_.-]*\z/
QUALIFIED_IDENTIFIER_REGEX =

Validates identifiers that Ruby interpolates into raw SQL with no quoting (table handles). Stricter than IDENTIFIER_REGEX: each dot-separated segment must be a simple identifier, so - (a SQL comment starter or subtraction) and stray dots never reach SQL text.

/\A[A-Za-z_]\w*(?:\.[A-Za-z_]\w*)*\z/
GENERATED_BIND_PREFIX_REGEX = 
/\A[A-Za-z_]\w*\z/
GENERATED_BIND_TYPE_REGEX =

Braces would terminate a generated placeholder early (or misalign the parser), smuggling the remainder of the type into the SQL as raw text, so a generated bind type must not contain them. Anything else stays inside the placeholder and is validated downstream.

/\A[^{}]+\z/
SQL_ALIAS_REGEX = 
/\A[A-Za-z_]\w*\z/
SETTING_NAME_REGEX = 
/\A[A-Za-z_]\w*\z/
VERSION = 
"0.1.0"

Class Method Summary collapse

Class Method Details

.and(fragments) ⇒ ClickHouse::SQL::Fragment?

Joins present fragments with AND, parenthesizing each fragment so a fragment containing a top-level OR cannot change the clause's meaning.

Parameters:

Returns:



181
182
183
 
# File 'lib/clickhouse/sql.rb', line 181

def and(fragments)
  join_fragments(fragments, " AND ", wrap: true)
end

.bind_list(prefix, values, type:, compact: false) ⇒ ClickHouse::SQL::Fragment

Builds a comma-separated list of generated typed placeholders.

Parameters:

  • prefix (String, Symbol) —

    Safe prefix for generated placeholder names.

  • values (Array, Object) —

    Values to bind; non-arrays are wrapped.

  • type (String) —

    ClickHouse placeholder type for every value.

  • compact (Boolean) (defaults to: false) —

    Whether to use shorter base-36 placeholder suffixes.

Returns:

Raises:



217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
 
# File 'lib/clickhouse/sql.rb', line 217

def bind_list(prefix, values, type:, compact: false)
  unless prefix.to_s.match?(GENERATED_BIND_PREFIX_REGEX)
    raise Error, "Generated bind prefix must be a simple identifier: #{prefix.inspect}"
  end

  unless type.to_s.match?(GENERATED_BIND_TYPE_REGEX)
    raise Error, "Generated bind type must not be blank or contain braces: #{type.inspect}"
  end

  values = Array(values)
  raise Error, "bind_list requires at least one value" if values.empty?

  bindings = {}
  placeholders = values.each_with_index.map do |value, index|
    name = generated_bind_name(prefix, index, compact:)
    bindings[name.to_sym] = value
    "{#{name}:#{type}}"
  end

  Fragment.new(placeholders.join(", "), bindings)
end

.coerce_expression(value) ⇒ ClickHouse::SQL::Part?

Coerces a value into a SQL expression that may contain placeholders.

Parameters:

Returns:



343
344
345
346
347
348
349
350
351
352
353
354
355
356
 
# File 'lib/clickhouse/sql.rb', line 343

def coerce_expression(value)
  case value
  when nil
    nil
  when ClickHouse::SQL::Part
    value
  when String
    Fragment.new(value)
  when Symbol
    Raw.new(value.to_s)
  else
    raise Error, "Expected a SQL fragment or trusted SQL string, got #{value.class}"
  end
end

.coerce_raw_expression(value) ⇒ ClickHouse::SQL::Part?

Coerces a value into trusted raw SQL expression context.

Parameters:

Returns:



362
363
364
365
366
367
368
369
370
371
372
373
374
375
 
# File 'lib/clickhouse/sql.rb', line 362

def coerce_raw_expression(value)
  case value
  when nil
    nil
  when ClickHouse::SQL::Part
    value
  when String
    Raw.new(value)
  when Symbol
    Raw.new(value.to_s)
  else
    raise Error, "Expected a trusted SQL string, got #{value.class}"
  end
end

.collect_placeholder_types(parts) ⇒ Hash{Symbol=>String}

Collects placeholder types from query parts.

Parameters:

  • parts (Array<#placeholder_types, nil>) —

    Query parts to inspect.

Returns:

  • (Hash{Symbol=>String}) —

    Merged placeholder type names.



466
467
468
469
470
471
472
 
# File 'lib/clickhouse/sql.rb', line 466

def collect_placeholder_types(parts)
  parts.compact.each_with_object({}) do |part, placeholder_types|
    next unless part.respond_to?(:placeholder_types)

    merge_placeholder_types!(placeholder_types, part.placeholder_types)
  end
end

.collect_placeholders(parts) ⇒ Hash{Symbol=>Object}

Collects placeholder values from query parts.

Parameters:

  • parts (Array<#placeholders, nil>) —

    Query parts to inspect.

Returns:

  • (Hash{Symbol=>Object}) —

    Merged placeholder values.



454
455
456
457
458
459
460
 
# File 'lib/clickhouse/sql.rb', line 454

def collect_placeholders(parts)
  parts.compact.each_with_object({}) do |part, placeholders|
    next unless part.respond_to?(:placeholders)

    merge_placeholders!(placeholders, part.placeholders)
  end
end

.csv(fragments) ⇒ ClickHouse::SQL::Fragment?

Joins present fragments with , .

Parameters:

Returns:



198
199
200
 
# File 'lib/clickhouse/sql.rb', line 198

def csv(fragments)
  join_fragments(fragments, ", ")
end

.fragment(sql, *args, **keyword_bindings) ⇒ ClickHouse::SQL::Fragment

Builds a SQL expression with typed placeholder values and fragment slots.

Anonymous {} slots are filled in source order by positional SQL parts. Named {name} slots and {name:Type} values take keyword bindings.

Parameters:

  • sql (String, #to_s) —

    SQL containing {name:Type} value placeholders, {name} fragment slots, and/or anonymous {} fragment slots.

  • args (Array<ClickHouse::SQL::Part, Hash>) —

    Positional fills for {} slots in source order; a Hash argument is treated as named bindings.

  • keyword_bindings (Hash) —

    Additional placeholder values keyed by name.

Returns:



98
99
100
101
102
 
# File 'lib/clickhouse/sql.rb', line 98

def fragment(sql, *args, **keyword_bindings)
  hashes, fills = args.partition { |arg| arg.is_a?(Hash) }
  bindings = hashes.reduce({}, :merge)
  Fragment.new(sql, merge_bindings(bindings, keyword_bindings), fills:)
end

.generated_bind_name(prefix, index, compact:) ⇒ String

Builds a generated placeholder name.

Parameters:

  • prefix (String, Symbol, #to_s) —

    Placeholder name prefix.

  • index (Integer) —

    Zero-based generated placeholder index.

  • compact (Boolean) —

    Whether to use base-36 suffixes.

Returns:

  • (String) —

    Generated placeholder name.



534
535
536
537
538
 
# File 'lib/clickhouse/sql.rb', line 534

def generated_bind_name(prefix, index, compact:)
  return "#{prefix}_#{index}" unless compact

  "#{prefix}#{index.to_s(36)}"
end

.join(table, type: "INNER", as: nil, final: false, on:) ⇒ ClickHouse::SQL::Join

Builds a standalone JOIN clause for SelectQuery#joins.

Useful for join specs shared between queries, so the joined table, its alias, FINAL, and the ON conditions travel together.

Parameters:

  • table (String, Symbol, ClickHouse::SQL::Part) —

    Trusted table expression.

  • type (String) (defaults to: "INNER") —

    JOIN type, for example INNER or LEFT.

  • as (String, Symbol, nil) (defaults to: nil) —

    Optional table alias; only valid for string/symbol table names — a ClickHouse::SQL::Table handle owns its alias.

  • final (Boolean) (defaults to: false) —

    Whether to append ClickHouse FINAL.

  • on (String, ClickHouse::SQL::Part, Array) —

    ON conditions.

Returns:



144
145
146
 
# File 'lib/clickhouse/sql.rb', line 144

def join(table, type: "INNER", as: nil, final: false, on:)
  Join.new(table, type:, as:, final:, on:)
end

.join_fragments(fragments, separator, wrap: false) ⇒ ClickHouse::SQL::Fragment?

Joins fragments with a separator while preserving nested placeholders.

Parameters:

  • fragments (Array<String, Symbol, ClickHouse::SQL::Part, nil>) —

    Fragments to join.

  • separator (String) —

    SQL separator to place between fragments.

  • wrap (Boolean) (defaults to: false) —

    Whether to parenthesize each fragment, for boolean separators.

Returns:



554
555
556
557
558
559
560
 
# File 'lib/clickhouse/sql.rb', line 554

def join_fragments(fragments, separator, wrap: false)
  fills = Array(fragments).flatten.compact.map { |fragment| coerce_expression(fragment) }
  return nil if fills.empty?

  slots = fills.map { wrap ? "({})" : "{}" }
  Fragment.new(slots.join(separator), fills:)
end

.left_join(table, as: nil, final: false, on:) ⇒ ClickHouse::SQL::Join

Builds a standalone LEFT JOIN clause for SelectQuery#joins.

Parameters:

  • table (String, Symbol, ClickHouse::SQL::Part) —

    Trusted table expression.

  • as (String, Symbol, nil) (defaults to: nil) —

    Optional table alias; only valid for string/symbol table names — a ClickHouse::SQL::Table handle owns its alias.

  • final (Boolean) (defaults to: false) —

    Whether to append ClickHouse FINAL.

  • on (String, ClickHouse::SQL::Part, Array) —

    ON conditions.

Returns:



156
157
158
 
# File 'lib/clickhouse/sql.rb', line 156

def left_join(table, as: nil, final: false, on:)
  join(table, type: "LEFT", as:, final:, on:)
end

.merge_bindings(bindings, keyword_bindings) ⇒ Hash{Symbol=>Object}

Merges positional and keyword bindings for a fragment.

Parameters:

  • bindings (Hash, nil) —

    Explicit bindings hash.

  • keyword_bindings (Hash) —

    Keyword bindings hash.

Returns:

  • (Hash{Symbol=>Object}) —

    Symbol-keyed merged bindings.



524
525
526
 
# File 'lib/clickhouse/sql.rb', line 524

def merge_bindings(bindings, keyword_bindings)
  ValueNormalization.symbolize_keys((bindings || {}).merge(keyword_bindings))
end

.merge_placeholder_types!(target, source) ⇒ Hash{Symbol=>String}

Merges placeholder types, rejecting duplicate names with different types.

Parameters:

  • target (Hash{Symbol=>String}) —

    Placeholder types to mutate.

  • source (Hash{Symbol,String=>String}) —

    Placeholder types to merge in.

Returns:

  • (Hash{Symbol=>String}) —

    The mutated target hash.



415
416
417
418
419
420
421
422
423
424
425
426
 
# File 'lib/clickhouse/sql.rb', line 415

def merge_placeholder_types!(target, source)
  source.each do |key, type|
    key = key.to_sym
    if target.key?(key) && target[key] != type
      raise Error, "mismatching types for the '#{key}' placeholder: #{target[key]} vs #{type}"
    end

    target[key] = type
  end

  target
end

.merge_placeholders!(target, source) ⇒ Hash{Symbol=>Object}

Merges placeholder values, rejecting duplicate names with different values.

Parameters:

  • target (Hash{Symbol=>Object}) —

    Placeholder values to mutate.

  • source (Hash{Symbol,String=>Object}) —

    Placeholder values to merge in.

Returns:

  • (Hash{Symbol=>Object}) —

    The mutated target hash.



396
397
398
399
400
401
402
403
404
405
406
407
408
 
# File 'lib/clickhouse/sql.rb', line 396

def merge_placeholders!(target, source)
  source.each do |key, value|
    key = key.to_sym
    if target.key?(key) && target[key] != value
      raise Error, "mismatching values for the '#{key}' placeholder: " \
        "#{truncated_inspect(target[key])} vs #{truncated_inspect(value)}"
    end

    target[key] = value
  end

  target
end

.normalize_placeholder_type(type) ⇒ String

Normalizes placeholder type spelling for comparison.

Parameters:

  • type (String, #to_s) —

    ClickHouse placeholder type.

Returns:

  • (String) —

    Type with whitespace removed.



432
433
434
 
# File 'lib/clickhouse/sql.rb', line 432

def normalize_placeholder_type(type)
  type.to_s.gsub(/\s+/, "")
end

.or(fragments) ⇒ ClickHouse::SQL::Fragment?

Joins present fragments with OR, parenthesizing each fragment so a fragment containing a top-level AND cannot change the clause's meaning.

Parameters:

Returns:



190
191
192
 
# File 'lib/clickhouse/sql.rb', line 190

def or(fragments)
  join_fragments(fragments, " OR ", wrap: true)
end

.parens(fragment) ⇒ ClickHouse::SQL::Fragment

Wraps a fragment in parentheses.

Parameters:

Returns:



206
207
208
 
# File 'lib/clickhouse/sql.rb', line 206

def parens(fragment)
  Fragment.new("({})", fills: [coerce_expression(fragment)])
end

.parse_placeholders(sql) ⇒ Array<ClickHouse::SQL::Placeholder>

Parses ClickHouse placeholder markers from SQL text.

Parameters:

  • sql (String, #to_s) —

    SQL text containing {name} or {name:Type} markers.

Returns:



252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
 
# File 'lib/clickhouse/sql.rb', line 252

def parse_placeholders(sql)
  sql = sql.to_s
  placeholders = []
  index = 0

  while (open_pos = sql.index("{", index))
    close_pos = sql.index("}", open_pos + 1)
    raise Error, "Unclosed ClickHouse placeholder in SQL fragment" unless close_pos

    raw = sql[open_pos..close_pos]
    content = raw[1...-1]
    if ValueNormalization.blank_string?(content)
      # `{}` is an anonymous fragment slot, filled positionally.
      placeholders << Placeholder.new(
        raw:,
        name: nil,
        type: nil,
        begin_pos: open_pos,
        end_pos: close_pos + 1
      )
      index = close_pos + 1
      next
    end

    # A colon must be followed by a non-empty type; `{name:}` is invalid
    # rather than silently becoming an untyped fragment slot.
    match = content.match(/\A\s*([A-Za-z_]\w*)\s*(?::\s*(\S.*?)\s*)?\z/m)
    raise Error, "Invalid ClickHouse placeholder #{raw.inspect}" unless match

    placeholders << Placeholder.new(
      raw:,
      name: match[1],
      type: match[2]&.strip,
      begin_pos: open_pos,
      end_pos: close_pos + 1
    )

    index = close_pos + 1
  end

  placeholders
end

.part?(value) ⇒ Boolean

Returns whether a value is a renderable SQL builder part.

Parameters:

  • value (Object) —

    Value to check.

Returns:

  • (Boolean) —

    True when value implements ClickHouse::SQL's internal part protocol.



544
545
546
 
# File 'lib/clickhouse/sql.rb', line 544

def part?(value)
  value.is_a?(ClickHouse::SQL::Part)
end

.raw_static(sql) ⇒ ClickHouse::SQL::Raw

Wraps trusted static SQL that has no placeholder values.

Parameters:

  • sql (String, #to_s) —

    Trusted SQL structure or expression.

Returns:



108
109
110
 
# File 'lib/clickhouse/sql.rb', line 108

def raw_static(sql)
  Raw.new(sql)
end

.render_part(part, redacted:, bind_index_manager:) ⇒ String

Renders a query part in normal or redacted mode.

Parameters:

Returns:

  • (String) —

    Rendered SQL.



442
443
444
445
446
447
448
 
# File 'lib/clickhouse/sql.rb', line 442

def render_part(part, redacted:, bind_index_manager:)
  if redacted
    part.to_redacted_sql(bind_index_manager)
  else
    part.to_sql
  end
end

.resolve_table_alias(table, as) ⇒ String, ...

Resolves a clause alias against a table expression's own alias.

A ClickHouse::SQL::Table handle owns its alias so qualified column references built from it always match the rendered FROM/JOIN clause. An explicit clause alias alongside a handle — even one matching the handle's alias — would create a second source of truth that can drift, so it raises instead.

Parameters:

  • table (Object) —

    Table expression, possibly a ClickHouse::SQL::Table.

  • as (String, Symbol, nil) —

    Explicit clause alias, if any.

Returns:

  • (String, Symbol, nil) —

    The alias to render, if any.



317
318
319
320
321
322
323
324
325
326
 
# File 'lib/clickhouse/sql.rb', line 317

def resolve_table_alias(table, as)
  return as unless table.is_a?(ClickHouse::SQL::Table)

  unless as.nil?
    raise Error, "A table handle owns its alias; pass the alias when creating the handle: " \
      "ClickHouse::SQL.table(#{table.name.inspect}, as: #{as.to_s.inspect})"
  end

  table.alias_name
end

.select(*columns, **aliases) ⇒ ClickHouse::SQL::SelectQuery

Starts a mutable SELECT query.

Parameters:

Returns:



172
173
174
 
# File 'lib/clickhouse/sql.rb', line 172

def select(*columns, **aliases)
  SelectQuery.new.select(*columns, **aliases)
end

.setting(name, value) ⇒ ClickHouse::SQL::Setting

Builds a validated ClickHouse SETTINGS assignment.

Parameters:

  • name (String, Symbol) —

    ClickHouse setting name.

  • value (String, Integer, Float, Boolean) —

    Inline setting value.

Returns:



244
245
246
 
# File 'lib/clickhouse/sql.rb', line 244

def setting(name, value)
  Setting.new(name, value)
end

.split_top_level_arguments(input) ⇒ Array<String>

Splits a ClickHouse type argument list on top-level commas.

Parameters:

  • input (String, #to_s) —

    Argument list without the outer type name.

Returns:

  • (Array<String>) —

    Top-level argument strings.



478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
 
# File 'lib/clickhouse/sql.rb', line 478

def split_top_level_arguments(input)
  input = input.to_s
  arguments = []
  start = 0
  depth = 0
  quote = nil
  escaped = false

  input.each_char.with_index do |char, index|
    if quote
      # An escaped character never closes the string; a backslash that is
      # not itself escaped escapes the character after it.
      if escaped
        escaped = false
      elsif char == "\\"
        escaped = true
      elsif char == quote
        quote = nil
      end
      next
    end

    case char
    when "'", '"'
      quote = char
    when "("
      depth += 1
    when ")"
      depth -= 1 if depth.positive?
    when ","
      next unless depth.zero?

      arguments << input[start...index].strip
      start = index + 1
    end
  end

  arguments << input[start..].to_s.strip
  arguments
end

.star ⇒ ClickHouse::SQL::Raw

Returns a trusted * select expression.

Returns:



163
164
165
 
# File 'lib/clickhouse/sql.rb', line 163

def star
  raw_static("*")
end

.table(name, as: nil) ⇒ ClickHouse::SQL::Table

Builds a validated table handle that qualifies column references.

Parameters:

  • name (String, Symbol, #to_s) —

    Trusted table name.

  • as (String, Symbol, nil) (defaults to: nil) —

    Optional table alias.

Returns:



128
129
130
 
# File 'lib/clickhouse/sql.rb', line 128

def table(name, as: nil)
  Table.new(name, as:)
end

.truncated_inspect(value, limit: 32) ⇒ String

Inspects a value for an error message without embedding whole user-supplied values, which would otherwise travel into error trackers when a caller bug raises.

Parameters:

  • value (Object) —

    Value to render into an error message.

  • limit (Integer) (defaults to: 32) —

    Maximum inspected length kept verbatim.

Returns:

  • (String) —

    Inspect output, truncated when oversized.



384
385
386
387
388
389
 
# File 'lib/clickhouse/sql.rb', line 384

def truncated_inspect(value, limit: 32)
  inspected = value.inspect
  return inspected if inspected.length <= limit

  "#{inspected[0, limit]}… (truncated #{value.class}, #{inspected.length} chars)"
end

.unsafe_raw(sql, reason:) ⇒ ClickHouse::SQL::Raw

Wraps an explicitly reviewed dynamic SQL escape hatch.

Parameters:

  • sql (String, #to_s) —

    SQL that cannot be represented with placeholders.

  • reason (String) —

    Durable explanation for why raw SQL is safe here.

Returns:

Raises:



117
118
119
120
121
 
# File 'lib/clickhouse/sql.rb', line 117

def unsafe_raw(sql, reason:)
  raise Error, "unsafe_raw requires a reason" if ValueNormalization.blank_string?(reason)

  Raw.new(sql, unsafe_reason: reason)
end

.validate_alias!(alias_name) ⇒ String

Validates a simple SQL alias.

Parameters:

  • alias_name (String, Symbol, #to_s) —

    Alias to validate.

Returns:

  • (String) —

    The validated alias string.

Raises:



299
300
301
302
303
304
 
# File 'lib/clickhouse/sql.rb', line 299

def validate_alias!(alias_name)
  alias_name = alias_name.to_s
  return alias_name if alias_name.match?(SQL_ALIAS_REGEX)

  raise Error, "SQL alias must be a simple identifier: #{alias_name.inspect}"
end

.validate_setting_name!(name) ⇒ String

Validates a ClickHouse setting name.

Parameters:

  • name (String, Symbol, #to_s) —

    Setting name to validate.

Returns:

  • (String) —

    The validated setting name.

Raises:



332
333
334
335
336
337
 
# File 'lib/clickhouse/sql.rb', line 332

def validate_setting_name!(name)
  name = name.to_s
  return name if name.match?(SETTING_NAME_REGEX)

  raise Error, "ClickHouse setting name must be a simple identifier: #{name.inspect}"
end