clickhouse-elixir / clickhousex Goto Github PK

When I tried inserting multiple rows via Clickhousex.query/1
I get

%Clickhousex.Error{code: :unknown, constraint_violations: [], message: ""}}

I play around with the limit of data I am uploading at a time, it seem 20 is the max number of rows allowed.

When trying to connect to 21.7.4.18 version of CH, I get the following:

:erlang.apply("Code: 516, e.displayText() = DB::Exception: Invalid authentication: 'Basic:' HTTP Authorization scheme is not supported (version 21.7.4.18 (official build))\n", :reason, [])
(clickhousex 0.5.0) lib/clickhousex/http_client.ex:103: Clickhousex.HTTPClient.decode_response/3
(clickhousex 0.5.0) lib/clickhousex/protocol.ex:37: Clickhousex.Protocol.connect/1
(db_connection 2.4.0) lib/db_connection/connection.ex:82: DBConnection.Connection.connect/2
(connection 1.1.0) lib/connection.ex:622: Connection.enter_connect/5
(stdlib 3.15.1) proc_lib.erl:226: :proc_lib.init_p_do_apply/3

Judging from the implementation, it seems that the expected header is different from the one supplied:
https://clickhouse.tech/codebrowser/html_report/ClickHouse/src/Server/HTTPHandler.cpp.html

E.g. changing from auth_header = {"Authorization", "Basic: #{auth_hash}"} -> auth_header = {"Authorization", "Basic #{auth_hash}"} should fix this. I can submit a PR, if this change makes sense, but I suspect this would affect backwards compatibility.

Out of the box, Ecto supports arrays as a primitive type. However, Clickhousex converts lists to binary when decoding the results, and that gives me an error when trying to use a field with type {:array, :integer} in an Ecto schema.

  def decode(_query, result, _opts) do
    case result.command do
      :selected ->
        rows = result.rows
        new_rows = Enum.map(rows, fn el ->
          list1 = Tuple.to_list(el)
          Enum.map(list1, fn el1 -> # why is this necessary?
            cond do
              is_list(el1) ->
                to_string(el1)
              true ->
                el1
            end
          end)
        end)
        Map.put(result, :rows, new_rows)
      _ ->
        result
    end
  end

I ran the project locally without this conversion and it worked as expected, giving me a list as a result. Can this be removed?

cc @albertosca

Please, update clickhousex for db_connection ver. 2.2

Currently bulk insert is quite inconvenient: each value of each row must be encoded in the VALUES as ?, which is hardly the most optimized way to do so.

Inserted rows must be moved to the POST body.

As the check if across the whole query, if the column has a ? in it, it won't work.

Example: insert into ... from url('https://example.com/csv?t=token') -- this query fails.

Thoughts on supporting numeric binds like in postgrex, $0, $1, etc.?

👋

If this repo and org are no longer useful or used by the original authors, @plausible is interested in taking it over and carrying for it as one of its own :)

Sorry if I got the picture wrong and the repo is still being used and maintained.

👋

This happens because regex for select is checked first. I wonder if it makes sense to check for insert regex first? After all, select can be in an insert query, but insert can't be in an select query.

Hi .
Thanks for creating this module.

I have registered the module under the supervision tree like so :

def start(_type, _args) do
children = [
      {Clickhousex,
       [
         name: Clickhouse,
         scheme: :http,
         hostname: Application.get_env(:app, Clickhouse)[:host],
         port: Application.get_env(:app, Clickhouse)[:port],
         username: Application.get_env(:app, Clickhouse)[:username],
         password: Application.get_env(:app, Clickhouse)[:password],
         database: "bidder_logs"
       ]}
]
 opts = [strategy: :one_for_one, name: App.Supervisor]
 Supervisor.start_link(children, opts)
end

and I am querying like so :

query = "SELECT COUNT(*) from requests"
{:ok, _query, results} = Clickhousex.query(:"Elixir.Clickhouse", query, [])

This was working fine for some time. Due to increased load , I am seeing a lot of these error messages :

{:error,
 %DBConnection.ConnectionError{
   message: "connection not available and request was dropped from queue after 538ms. You can configure how long requests wait in the queue using :queue_target and :queue_interval. See DBConnection.start_link/2 for more information"
 }}

will be really helpful if you can share some resources on how this issue can be handled .

Thanks

when CH server is down, I have noticed in some cases that all outgoing HTTP requests from the elixir app will be halted.
it might be better to use a separate named HTTP Pool.

would like to know the maintainers thoughts on this..
Thanks !

While using json codec and clickhouse returns non-empty string everything is ok.

iex(1)> {:ok, pid} = Clickhousex.start_link(scheme: :http, hostname: ...)
{:ok, #PID<0.391.0>}
iex(2)> Clickhousex.query(pid, "SELECT 1", [])
{:ok,
 %Clickhousex.Query{
   columns: [],
   name: "",
   param_count: 0,
   params: [],
   statement: "SELECT 1",
   type: :select
 },
 %Clickhousex.Result{
   columns: ["1"],
   command: :selected,
   num_rows: 1,
   rows: [{1}]
 }}

But if clickhouse returns empty string error occurs.

iex(3)> Clickhousex.query(pid, "SHOW TABLES", [])
** (CaseClauseError) no case clause matching: {:error, %Jason.DecodeError{data: "", position: 0, token: nil}}
    (clickhousex 0.5.0) lib/clickhousex/codec/json.ex:38: Clickhousex.Codec.JSON.decode/1
    (clickhousex 0.5.0) lib/clickhousex/http_client.ex:112: Clickhousex.HTTPClient.decode_response/3
    (clickhousex 0.5.0) lib/clickhousex/protocol.ex:167: Clickhousex.Protocol.do_query/5
    (db_connection 2.4.0) lib/db_connection/holder.ex:325: DBConnection.Holder.holder_apply/4
    (db_connection 2.4.0) lib/db_connection.ex:1314: DBConnection.run_execute/5
    (db_connection 2.4.0) lib/db_connection.ex:1409: DBConnection.run/6
    (db_connection 2.4.0) lib/db_connection.ex:574: DBConnection.parsed_prepare_execute/5
    (db_connection 2.4.0) lib/db_connection.ex:566: DBConnection.prepare_execute/4

• Clickhousex.Codec.Binary.Extractor.build_extractor/5 (CC 13)
• Clickhousex.Protocol.connect/1 (CC 10)

Hi,
I have a fork of Clickhousex where I updated this project to use the latest Ecto / DBConnection. I also fixed some bugs and added more thorough type handling. I also added a lot of tests.

Additionally, I have a branch that uses different output formats from Clickhouse that should make for more efficient queries.

Would you be interested in a PR?

Create materialized view fails on execution

q  = """
    CREATE MATERIALIZED VIEW IF NOT EXISTS some_view
    ENGINE = MergeTree() ORDER BY number
    AS SELECT 
    number 
    FROM system.numbers
"""
Clickhousex.query(:clickhouse, q, [])

** (CaseClauseError) no case clause matching: {:error, %Jason.DecodeError{data: "", position: 0, token: nil}}
    (clickhousex) lib/clickhousex/codec/json.ex:18: Clickhousex.Codec.JSON.decode/1
    (clickhousex) lib/clickhousex/http_client.ex:39: Clickhousex.HTTPClient.send_p/5
    (clickhousex) lib/clickhousex/protocol.ex:134: Clickhousex.Protocol.do_query/4
    (db_connection) lib/db_connection/holder.ex:316: DBConnection.Holder.holder_apply/4
    (db_connection) lib/db_connection.ex:1255: DBConnection.run_execute/5
    (db_connection) lib/db_connection.ex:1342: DBConnection.run/6
    (db_connection) lib/db_connection.ex:539: DBConnection.parsed_prepare_execute/5
    (db_connection) lib/db_connection.ex:532: DBConnection.prepare_execute/4

Clickhousex version: 0.4.0
Elixir: 1.9.4

• Make configuration obligatory for inclusion
• Remove config/config.exs
• Remove configuration defaults, raise human-readable error when there is configuration specified

It might be convenient to have a way to do something like this:

defmodule MyApp.Clickhouse do
  use Clickhousex, hardcoded_opts...
end

And later use functions like Clickhousex.query from this module directly without explicit client atom name.

Currently it's only possible to change codec during compilation via configs in config/ directory like that:

config :clickhousex, codec: Clickhousex.Codec.Binary

I think it might be a good idea to be able to choose it on per-query basis, depending on the needs

The project README states one of the installation methods is via hex.pm.
The official hex.pm project page (https://hex.pm/packages/clickhousex) refers to this repository.
However, I failed to find source version in the repo that corresponds to the hex archive.
This prevents me from forking the project and making modifications properly.

Please advise.

Hello. I opened a related issue in clickhouse_ecto clickhouse-elixir/clickhouse_ecto#22

Clickhousex.Result.rows should return list of lists

But right now it returns a list of tuples.
For some reason clickhousex explicitly converts resulting row list to tuple

• Clickhousex.Codec.Binary
• Clickhousex.Codec.JSON
• Clickhousex.Codec.RowBinary.Utils
• Clickhousex.Codec.Values
• Clickhousex.Codec
• Clickhousex.HTTPClient.Response
• Clickhousex.HTTPRequest