How to create a record with array type

[DavertMik]

I have column tags:

    t.string "tags", array: true, null: false

I created it as array of strings:

  def up
    execute "ALTER TABLE analytics_tests ADD COLUMN tags Array(String)"
   # ...

However I can't insert data into Clickhouse as array:

irb(main):216:0> MyRecord.create tags: ['a', 'b']
        1: from (irb):216
TypeError (can't quote Array)

I thought array type is supported, what should be changed to insert arrays into clickhouse?

[trumenov]

I can offer working patch:

# file config/initializers/active_record/clickhouse_string_array.rb
require 'delegate'

class ClickhouseStringArray < SimpleDelegator
  def initialize(arr_in)
    raise("ClickhouseStringArray require Array, but received[#{ arr_in.class }]=[#{ arr_in }]") unless arr_in.is_a?(Array)
    super(arr_in)
  end
end

class ClickhouseStringArrayType < ActiveRecord::Type::Value
  def cast(value)
    value.is_a?(ClickhouseStringArray) ? value : deserialize(value)
  end

  # Casts the value from the ruby type to a type that the database knows how to understand.
  def serialize(value)
    return nil if value.nil?
    unless value.is_a?(Array)
      raise("ClickhouseStringArrayType require Array, but received[#{ value.class }]=[#{ value }]")
    end
    value.any? ? ClickhouseStringArray.new(value) : nil
  end

  # Casts the value from an array
  def deserialize(value)
    case value
    when Array
      ClickhouseStringArray.new(value)
    when ClickhouseStringArray
      value
    when String
      deserialize(JSON.parse(value).to_a) # recursion!
    else
      nil
    end
  end
end

ActiveRecord::Type.register(:clickhouse_string_array, ClickhouseStringArrayType)

# file config/initializers/active_record/connection_adapters/quoting.rb

module ActiveRecord
  module ConnectionAdapters
    class ClickhouseAdapter < AbstractAdapter
      def quote(value)
        case value
        when ClickhouseStringArray
          '[' + value.to_a.map { |x| super(x) }.join(',') + ']'
        else
          super(value)
        end
      end
    end
  end
end

# file app/models/analytics_test.rb
...
  attribute :tags, :clickhouse_string_array
  attribute :environments, :clickhouse_string_array
...

AnalyticsTest.insert({ tags: ["asdasd"] })

[PNixx]

Fixed in v0.5.14

[DavertMik]

Thank you!

[spyderdfx]

Looks like issue is still here. https://github.com/PNixx/clickhouse-activerecord/blob/c91066a817782c430a7909b9af043dcfb206b647/lib/active_record/connection_adapters/clickhouse/oid/array.rb#L46 Maybe serialization should look something like "[" + value.map { |item| serialize(item) }.join(", ") + "]" Otherwise, here in Rails array is still array and serialized using YAML

[PNixx]

I will check

[PNixx]

@spyderdfx can you give an example? https://github.com/PNixx/clickhouse-activerecord/blob/master/spec/cases/model_spec.rb#L197-L224 These tests work.

[spyderdfx]

Table:

execute <<~SQL
  CREATE TABLE test
  (
    id Int32,
    array_of_ints Array(Int32) DEFAULT []
  )
  ENGINE = MergeTree()
  PRIMARY KEY id
SQL

Simple model:

class Test < ApplicationRecord
  self.table_name = 'test'
end

Trying to insert array:

> Test.insert_all([{id: 1, array_of_ints: [1, 2]}])
  Clickhouse Test Insert (3.4ms)  INSERT INTO test (id,array_of_ints) VALUES (1, '---
- 1                                                                                                                         
- 2
')
/bundle/3.1/ruby/3.1.0/gems/clickhouse-activerecord-0.5.14/lib/active_record/connection_adapters/clickhouse/schema_statements.rb:110:in `process_response': ActiveRecord::ActiveRecordError: Response code: 500: (ActiveRecord::StatementInvalid)
Code: 130. DB::Exception: Array does not start with '[' character: while executing 'FUNCTION if(isNull(_dummy_0) : 3, defaultValueOfTypeName('Array(Int32)') :: 2, _CAST(_dummy_0, 'Array(Int32)') :: 4) -> if(isNull(_dummy_0), defaultValueOfTypeName('Array(Int32)'), _CAST(_dummy_0, 'Array(Int32)')) Array(Int32) : 1': While executing ValuesBlockInputFormat. (CANNOT_READ_ARRAY_FROM_TEXT) (version 22.1.3.7 (official build))

[PNixx]

Ruby on Rails version?

[spyderdfx]

7.0.4

[PNixx]

Can you check with 0.6.2 version?

[spyderdfx]

Tried 0.6.2, same behavior with fallback to YAML serialization:

irb(main):001:0> Test.insert_all([{id: 1, array_of_ints: [1, 2]}])
  Clickhouse test (system) (3.4ms)  DESCRIBE TABLE `test`
  Clickhouse Test Insert (5.6ms)  INSERT INTO test (id,array_of_ints) VALUES (1, '---
- 1
- 2
')
/bundle/3.1/ruby/3.1.0/gems/clickhouse-activerecord-0.6.2/lib/active_record/connection_adapters/clickhouse/schema_statements.rb:120:in `process_response': ActiveRecord::ActiveRecordError: Response code: 500: (ActiveRecord::StatementInvalid)
Code: 130. DB::Exception: Array does not start with '[' character: while executing 'FUNCTION if(isNull(_dummy_0) : 3, defaultValueOfTypeName('Array(Int32)') :: 2, _CAST(_dummy_0, 'Array(Int32)') :: 4) -> if(isNull(_dummy_0), defaultValueOfTypeName('Array(Int32)'), _CAST(_dummy_0, 'Array(Int32)')) Array(Int32) : 1': While executing ValuesBlockInputFormat. (CANNOT_READ_ARRAY_FROM_TEXT) (version 22.1.3.7 (official build))
/bundle/3.1/ruby/3.1.0/gems/clickhouse-activerecord-0.6.2/lib/active_record/connection_adapters/clickhouse/schema_statements.rb:120:in `process_response': Response code: 500: (ActiveRecord::ActiveRecordError)
Code: 130. DB::Exception: Array does not start with '[' character: while executing 'FUNCTION if(isNull(_dummy_0) : 3, defaultValueOfTypeName('Array(Int32)') :: 2, _CAST(_dummy_0, 'Array(Int32)') :: 4) -> if(isNull(_dummy_0), defaultValueOfTypeName('Array(Int32)'), _CAST(_dummy_0, 'Array(Int32)')) Array(Int32) : 1': While executing ValuesBlockInputFormat. (CANNOT_READ_ARRAY_FROM_TEXT) (version 22.1.3.7 (official build))
irb(main):002:0> ClickhouseActiverecord::VERSION
=> "0.6.2"

[PNixx]

Yes, insert_all incorrect working. I will try fix it.

[PNixx]

Fixed in v1.0.4