clickhouse-elixir / clickhouse_ecto Goto Github PK
View Code? Open in Web Editor NEWEcto adapter for ClickHouse database
License: Apache License 2.0
Ecto adapter for ClickHouse database
License: Apache License 2.0
Hey! ๐
I have following ecto query:
views = View |> select([v], %{offset: v.offset})
It generates valid sql SELECT s0."offset" FROM "views"
When i'm trying to use it as subquery, for example:
from v in subquery(views), select: v.offset
then the resulting SQL looks somehow wrong:
SELECT s0."offset" FROM (SELECT 'TRUE' FROM "views" AS s0) AS s0 []
After some investigation i've found that problem is likely here
https://github.com/appodeal/clickhouse_ecto/blob/master/lib/clickhouse_ecto/query_string.ex#L202
because of views.fields
field is nil after building initial query.
Hi!
When i try insert records throught Ecto.Multi
i have a error:
** (FunctionClauseError) no function clause matching in Ecto.Schema.safe_load_zip/4
The following arguments were given to Ecto.Schema.safe_load_zip/4:
# 1
[]
# 2
[1]
# 3
%Schema.Coins{
__meta__: #Ecto.Schema.Metadata<:built, "coins">,
block: nil,
symbol: nil,
timestamp: nil,
txn: nil,
type: nil,
volume: nil
}
# 4
#Function<23.31199312/2 in Ecto.Repo.Schema.postprocess/5>
Attempted function clauses (showing 2 out of 2):
defp safe_load_zip([{field, type} | fields], [value | values], struct, loader)
defp safe_load_zip([], [], _struct, _loader)
(ecto) lib/ecto/schema.ex:1442: Ecto.Schema.safe_load_zip/4
(ecto) lib/ecto/schema.ex:1431: Ecto.Schema.__safe_load__/6
(ecto) lib/ecto/repo/schema.ex:65: anonymous fn/7 in Ecto.Repo.Schema.postprocess/5
(elixir) lib/enum.ex:1948: Enum."-reduce/3-lists^foldl/2-0-"/3
(ecto) lib/ecto/repo/schema.ex:64: Ecto.Repo.Schema.postprocess/5
(ecto) lib/ecto/repo/schema.ex:53: Ecto.Repo.Schema.do_insert_all/7
(ecto) lib/ecto/multi.ex:441: Ecto.Multi.apply_operation/4
(ecto) lib/ecto/multi.ex:421: Ecto.Multi.apply_operation/5
Records sucessfuly added to the table, but after that - raise error.
Simple code snippet
list =
[
%{
block: 1194660,
symbol: "PCOALERT",
timestamp: ~U[2019-07-24 11:51:34Z],
txn: 963116,
type: 1,
volume: #Decimal<3698.562602227655288279>
},
%{
block: 1194660,
symbol: "POPE",
timestamp: ~U[2019-07-24 11:51:34Z],
txn: 963116,
type: 2,
volume: #Decimal<0.380456477496956849>
},
%{
block: 1194657,
symbol: "BIP",
timestamp: ~U[2019-07-24 11:51:19Z],
txn: 963114,
type: 1,
volume: #Decimal<13.871965040627080734>
}
]
Ecto.Multi.new()
|> Ecto.Multi.insert_all(:insert_all, Coins, list)
|> Repo.transaction()
My use case is that I use the elixir client with a read-only CH user that caches the result, so here I would trade some performance for cleaner code.
I see that you removed the ?<number>
in #7
My queries require a lot of repeating parameters, so I forked the repo and fixed some issues santiment#1.
Are you planing to go forward without supporting positional parameters at all? Or maybe implement both? In case you are interested I would try to find some free time to work in that direction and try to improve the performance as well.
It appears that the code at Hex.pm (https://hex.pm/packages/clickhouse_ecto) is outdated; are there any plans to reupload it?
Thanks!
Hey
I have such group_by in my query:
group_by(query, [s], fragment("?, ? WITH TOTALS", s.param, s.param2))
query is correct and it works, but WITH TOTALS add key "totals" in result map which is not handled by clickhousex I guess and query return results without totals at all.
It will be great if you can fix it, thanks.
How to reproduce:
Create any migration for the new DB, call mix ecto.migrate
Expected result: migration successfully executed.
Actual result:
warning: :pool_timeout option no longer has an effect and has been replaced with an improved queuing system.
See "Queue config" in DBConnection.start_link/2 documentation for more information.
(ecto_sql) lib/ecto/adapters/sql.ex:469: Ecto.Adapters.SQL.adapter_config/1
(ecto_sql) lib/ecto/adapters/sql.ex:456: Ecto.Adapters.SQL.init/3
(ecto) lib/ecto/repo/supervisor.ex:172: Ecto.Repo.Supervisor.init/1
(stdlib) supervisor.erl:295: :supervisor.init/1
(stdlib) gen_server.erl:374: :gen_server.init_it/2
** (FunctionClauseError) no function clause matching in String.Unicode.codepoints/1
The following arguments were given to String.Unicode.codepoints/1:
# 1
["CREATE TABLE IF NOT EXISTS ", [34, "schema_migrations", 34], 32, 40, [[[], [[34, "version", 34], 32, "Int64", [[], []]], ", "], [34, "inserted_at", 34], 32, "DateTime", [[], []]], 41, [], " ENGINE = TinyLog "]
Attempted function clauses (showing 1 out of 1):
def codepoints(binary) when is_binary(binary)
(elixir) lib/elixir/unicode/unicode.ex:300: String.Unicode.codepoints/1
(clickhousex) lib/clickhousex/query.ex:39: DBConnection.Query.Clickhousex.Query.parse/2
(db_connection) lib/db_connection.ex:1122: DBConnection.parse/3
(db_connection) lib/db_connection.ex:531: DBConnection.prepare_execute/4
(clickhousex) lib/clickhousex.ex:59: Clickhousex.query/4
(ecto_sql) lib/ecto/adapters/sql.ex:291: Ecto.Adapters.SQL.query!/4
(elixir) lib/enum.ex:1336: Enum."-map/2-lists^map/1-0-"/2
(ecto_sql) lib/ecto/adapters/sql.ex:699: Ecto.Adapters.SQL.execute_ddl/4
Hello. Latest clickhouse_ecto
version doesn't work with ecto 3
** (FunctionClauseError) no function clause matching in Ecto.Repo.Queryable.struct_load!/6
The following arguments were given to Ecto.Repo.Queryable.struct_load!/6:
# 1
[timestamp: :utc_datetime, device_id: :integer, message: :string]
# 2
{~N[2020-11-02 18:27:28], 2, "2020/11/02 21:27:28 Random test info"}
# 3
[]
# 4
false
# 5
%Click.Log{
__meta__: #Ecto.Schema.Metadata<:loaded, "logs">,
device_id: nil,
message: nil,
timestamp: nil
}
# 6
ClickhouseEcto
Attempted function clauses (showing 3 out of 3):
def struct_load!([{field, type} | types], [value | values], acc, all_nil?, struct, adapter)
def struct_load!([], values, _acc, true, _struct, _adapter)
def struct_load!([], values, acc, false, struct, _adapter)
(ecto 3.5.4) lib/ecto/repo/queryable.ex:186: Ecto.Repo.Queryable.struct_load!/6
(ecto 3.5.4) lib/ecto/repo/queryable.ex:245: anonymous fn/5 in Ecto.Repo.Queryable.preprocessor/3
(elixir 1.11.2) lib/enum.ex:1399: Enum."-map/2-lists^map/1-0-"/2
(ecto 3.5.4) lib/ecto/repo/queryable.ex:236: Ecto.Repo.Queryable.execute/4
(ecto 3.5.4) lib/ecto/repo/queryable.ex:17: Ecto.Repo.Queryable.all/3
As you can see Ecto
expected single row to be a List
but Clickhousex
returns a tuple
.
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.
Hey. First of all thank you for building this library.
I'm moving to Clickhouse and would love to use it, but my app is running Ecto 3 and it runs into conflicts with this library. Are you planning to add support for Ecto 3? If you can give me some direction, I can take a look and see if I can help make it happen as well.
Hi, I found that limit
and offset
queries don't work properly with parameters because of different order in parameters list and ClickHouse LIMIT ?, ?
query.
Simple example:
Such query
Schema |> limit(^limit) |> offset(^offset) |> Repo.all()
generates such sql:
SELECT ... FROM "schema" LIMIT ?, ?
with parameters: [limit, offset]
and final query will be like this SELECT ... FROM "schema" LIMIT #{limit}, #{offset}
, but should be SELECT ... FROM "schema" LIMIT #{offset}, #{limit}
I think this is regression of #7 PR.
It seems that I found typo in lib/clickhouse_ecto/storage.ex
.
There is a call HTTPoison.start/0
on 70 line. HTTPoison
is not listed in deps. Is that call necessary?
Currently there's no automatic (that is, via ecto) way to rollback a migration.
This is due to a fact that there are no DELETE
s in Clickhouse per se: only mutations via ALTER
.
Wonder if there's anything that can be done for rollbacks only. Maybe even support DELETE
s via ALTER
as a general case (that may issue a Logger.warn
ing if used for anything else but rollbacks)?
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.