imsmwu / rclickhouse Goto Github PK
View Code? Open in Web Editor NEWA 'DBI' Interface to the Yandex Clickhouse Database Providing Basic 'dplyr' Support
License: GNU General Public License v2.0
A 'DBI' Interface to the Yandex Clickhouse Database Providing Basic 'dplyr' Support
License: GNU General Public License v2.0
When compiled with flags like -march=native
/ -mavx
/ -mavx2
/ -msse4.2
compilation fails:
#include <citycrc.h>
^~~~~~~~~~~
"citycrc.h"
In file included from vendor/clickhouse-cpp/contrib/cityhash/city.cc:357:
vendor/clickhouse-cpp/contrib/cityhash/citycrc.h:31:10: error: 'city.h' file not found with include; use "quotes" instead
#include <city.h>
^~~~~~~~
"city.h"
2 errors generated.
make: *** [vendor/clickhouse-cpp/contrib/cityhash/city.o] Error 1
ERROR: compilation failed for package ‘RClickhouse’
- removing ‘/usr/local/lib/R/3.4/site-library/RClickhouse’
Warning in install.packages :
installation of package ‘RClickhouse’ had non-zero exit status
Hi,
The following hangs at the insert statment
dbExecute(con,"CREATE TABLE tag_join(`id` UInt32) ENGINE = Log()")
dbExecute(con,"INSERT INTO tag_join VALUES (1);")
Hello.
Thanks for the long-anticipated update of your package, but I can't get it working on Windows:
The following code is working fine on Linux with RClickhouse 0.5.1:
conn <- DBI::dbConnect(RClickhouse::clickhouse(), host = "10.0.0.219")
but on Windows I get:
Error in connect(config[["host"]], strtoi(config[["port"]]), config[["db"]], :
fail to connect: No error
Clickhouse client has a progress bar. Would be awesome in R.
Love Clickhouse. When inserting, you get a cryptic error like:
Error in insert(conn@ptr, qname, value) :
cannot write R type 16 to column of type Int32
In this example, it'd be great to know (1) which column and (2) what is R type 16.
Hi, Clickhouse now supports nullable columns. Is there an elegant way to ask dbWriteTable to make columns nullable by default? If sample data isn't nullable, it's currently an error. This breaks a lot of code. A global option would be excellent.
Empty response from CH is captured in unpredictable way. I suppose that this can happened in case of 'Division by Zero' under uniq(account_id) / (SELECT uniq(account_id) ...
. But native CH client didn't print any error. Simply "Ok. 0 rows".
Browse[1]> dbGetQuery(conn, r)
Error in select(conn@ptr, statement) : unsupported column type: Null
Browse[1]> dbGetQuery(conn, r)
data frame with 0 columns and 0 rows
Browse[1]> dbGetQuery(conn, r)
Error in select(conn@ptr, statement) : unsupported column type: Null
Browse[1]> dbGetQuery(conn, r)
data frame with 0 columns and 0 rows
Browse[1]> dbGetQuery(conn, r)
Error in select(conn@ptr, statement) : unsupported column type: Null
SQL request in native CH console is evaluated in correct way.
SELECT
date,
switchevent,
min(duration_minutes),
uniq(epgId),
uniq(account_id) /
(
SELECT uniq(account_id)
FROM view_states
WHERE ((date >= '2018-01-19') AND (date <= '2018-01-20')) AND ((duration >= 60) AND (duration <= 18000)) AND (account_id LIKE '%#34%')
) AS uniq_account_id_ratio
FROM view_states
WHERE ((date >= '2018-01-19') AND (date <= '2018-01-20')) AND ((duration >= 60) AND (duration <= 18000)) AND (account_id LIKE '%#34%')
GROUP BY
date,
switchevent
Ok.
0 rows in set. Elapsed: 0.269 sec. Processed 245.77 thousand rows, 8.82 MB (912.36 thousand rows/s., 32.73 MB/s.)
Session details:
sessionInfo()
R version 3.4.3 (2017-11-30)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows >= 8 x64 (build 9200)
Matrix products: default
locale:
[1] LC_COLLATE=Russian_Russia.1251 LC_CTYPE=Russian_Russia.1251 LC_MONETARY=Russian_Russia.1251 LC_NUMERIC=C
[5] LC_TIME=Russian_Russia.1251
attached base packages:
[1] stats graphics grDevices utils datasets methods base
other attached packages:
[1] dvtdspack_0.1.2 bindrcpp_0.2 checkmate_1.8.5 assertr_2.5 openxlsx_4.0.17 officer_0.2.1
[7] digest_0.6.15 tictoc_1.0 anytime_0.3.0 shinycssloaders_0.2.0 shinyWidgets_0.4.1 shinyjs_1.0
[13] shinyBS_0.61 shinythemes_1.1.1 shinyjqui_0.2.0 shiny_1.0.5 config_0.2 RPostgreSQL_0.6-2
[19] DBI_0.7 hrbrthemes_0.1.0 extrafont_0.17 RColorBrewer_1.1-2 Cairo_1.5-9 jsonlite_1.5
[25] futile.logger_1.4.3 stringi_1.1.6 magrittr_1.5 readxl_1.0.0 scales_0.5.0 glue_1.2.0
[31] lubridate_1.7.2 forcats_0.3.0 dplyr_0.7.4 purrr_0.2.4 readr_1.1.1 tidyr_0.8.0
[37] tibble_1.4.2 ggplot2_2.2.1 tidyverse_1.2.1 stringr_1.3.0
loaded via a namespace (and not attached):
[1] nlme_3.1-131 bitops_1.0-6 fs_1.1.0 httr_1.3.1 hunspell_2.9 backports_1.1.2
[7] tools_3.4.3 utf8_1.1.3 R6_2.2.2 DT_0.4 lazyeval_0.2.1 colorspace_1.3-2
[13] tidyselect_0.2.3 mnormt_1.5-5 chron_2.3-52 compiler_3.4.3 extrafontdb_1.0 cli_1.0.0
[19] rvest_0.3.2 xml2_1.2.0 qdapTools_1.3.3 psych_1.7.8 RApiDatetime_0.0.3 foreign_0.8-69
[25] R.utils_2.6.0 base64enc_0.1-3 pkgconfig_2.0.1 htmltools_0.3.6 dbplyr_1.2.1 htmlwidgets_1.0
[31] rlang_0.2.0 rstudioapi_0.7 bindr_0.1 crosstalk_1.0.0 zip_1.0.0 R.oo_1.21.0
[37] RCurl_1.95-4.10 Rcpp_0.12.15 munsell_0.4.3 RClickhouse_0.4.0 R.methodsS3_1.7.1 yaml_2.1.16
[43] plyr_1.8.4 grid_3.4.3 parallel_3.4.3 crayon_1.3.4 lattice_0.20-35 haven_1.1.1
[49] hms_0.4.1 pillar_1.2.0 uuid_0.1-2 reshape2_1.4.3 futile.options_1.0.0 data.table_1.10.4-3
[55] lambda.r_1.2 modelr_0.1.1 httpuv_1.3.5 Rttf2pt1_1.3.6 cellranger_1.1.0 gtable_0.2.0
[61] assertthat_0.2.0 mime_0.5 xtable_1.8-2 broom_0.4.3 rsconnect_0.8.5
implement copy_to
without transactions:
con <- DBI::dbConnect(clckhs::clickhouse(),host="xxx", password="xxx")
copy_to(con, mtcars, "mtcars")
Error in dbBegin(con) : Transactions are not supported.
11. stop("Transactions are not supported.") at ClickhouseConnection.R#166
10. dbBegin(con)
9. dbBegin(con)
8. db_begin.DBIConnection(con)
7. db_begin(con)
6. db_copy_to.DBIConnection(dest$con, name, df, overwrite = overwrite,
types = types, temporary = temporary, unique_indexes = unique_indexes,
indexes = indexes, analyze = analyze, ...)
5. db_copy_to(dest$con, name, df, overwrite = overwrite, types = types,
temporary = temporary, unique_indexes = unique_indexes, indexes = indexes,
analyze = analyze, ...)
4. copy_to.src_sql(dbplyr::src_dbi(dest), df = df, name = name,
overwrite = overwrite, ...)
3. copy_to(dbplyr::src_dbi(dest), df = df, name = name, overwrite = overwrite,
...)
2. copy_to.DBIConnection(con, mtcars, "mtcars")
- copy_to(con, mtcars, "mtcars")
Hello, and great thanks to provide nice package.
When I use dplyr::XXX_join, I got the following error.
Error: con must not be NULL
Probably 'sql_vector' needs to set "con" parameter in line.193 in 'R/dplyr.R'.
on <- sql_vector(paste0(sql_escape_ident(con, by$x), " = ", sql_escape_ident(con, by$y)), collapse = " AND ", parens = TRUE)
It seems to be need to append con = con
.
Hi
I cannot write tables with spaces (or any other special character) in column names.
The R code below shows an example.
library(RClickhouse)
library(DBI)
con <- dbConnect(clickhouse(), port=9000)
test1 <- data.frame(
"Col1"=c("b","b"),
"Col2_no_space"=1:2,
stringsAsFactors=FALSE,
check.names=FALSE
)
dbWriteTable(con, "test1", test1)
dbListTables(con)
#> [1] "test1"
test2 <- data.frame(
"Col1"=c("b","b"),
"Col2 with space"=1:2,
stringsAsFactors=FALSE,
check.names=FALSE
)
dbWriteTable(con, "test2", test2)
#> Error in select(conn@ptr, statement): DB::Exception: Syntax error: failed at position 46: space Int32) ENGINE=TinyLog. Expected one of: CODEC, ALIAS, TTL, ClosingRoundBracket, Comma, DEFAULT, MATERIALIZED, COMMENT, token
dbListTables(con)
#> [1] "test1"
sessionInfo()
#> R version 3.6.3 (2020-02-29)
#> Platform: x86_64-pc-linux-gnu (64-bit)
#> Running under: Ubuntu 18.04.4 LTS
#>
#> Matrix products: default
#> BLAS: /usr/lib/x86_64-linux-gnu/blas/libblas.so.3.7.1
#> LAPACK: /usr/lib/x86_64-linux-gnu/lapack/liblapack.so.3.7.1
#>
#> locale:
#> [1] LC_CTYPE=en_US.UTF-8 LC_NUMERIC=C
#> [3] LC_TIME=en_US.UTF-8 LC_COLLATE=en_US.UTF-8
#> [5] LC_MONETARY=en_US.UTF-8 LC_MESSAGES=en_US.UTF-8
#> [7] LC_PAPER=en_US.UTF-8 LC_NAME=C
#> [9] LC_ADDRESS=C LC_TELEPHONE=C
#> [11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C
#>
#> attached base packages:
#> [1] stats graphics grDevices utils datasets methods base
#>
#> other attached packages:
#> [1] DBI_1.1.0 RClickhouse_0.5.2
#>
#> loaded via a namespace (and not attached):
#> [1] Rcpp_1.0.3 knitr_1.28 magrittr_1.5 tidyselect_1.0.0
#> [5] bit_1.1-15.2 R6_2.4.1 rlang_0.4.5 stringr_1.4.0
#> [9] highr_0.8 dplyr_0.8.5 tools_3.6.3 xfun_0.12
#> [13] dbplyr_1.4.2 htmltools_0.4.0 yaml_2.2.1 bit64_0.9-7
#> [17] digest_0.6.25 assertthat_0.2.1 tibble_2.1.3 crayon_1.3.4
#> [21] purrr_0.3.3 glue_1.3.1 evaluate_0.14 rmarkdown_2.1
#> [25] stringi_1.4.6 compiler_3.6.3 pillar_1.4.3 pkgconfig_2.0.3
And here is information about ClickHouse version:
docker run -d \
--name ch_test \
--ulimit nofile=262144:262144 \
--publish=8123:8123 --publish=9000:9000 \
--restart=always \
yandex/clickhouse-server:20.1.6.3
Do you have an idea how I could solve this issue?
Thank you for your help and for this very useful package.
Best regards
Patrice
Imagine hte following:
res=dbSendQuery(con,queryText)
while(nrow(x<-dbFetch(res,50))){
........
}
Right now, this could complain about a memory error. However, having asked Clickhouse, it's not clear the results should be stored in memory? Not sure why we can't stream through the results sequentially in R.
cannot write unsupported type: FixedString(16)
a parallel execution of the same query does not work:
task 1 failed - "DB::Exception: Query with id = 1 is already running."
/etc/RClickhouse.ini
, ~/.R/Clickhouse.ini
or ./RClickhouse.ini
) and load username, hostname, password etc.Just wondering if you can add data importing function in this package.
use zzz.R to unload:
When reading Int64
columns RClickhouse
always convert them to numeric (with warning).
DBI spec supports integer64
and common approach is to use bit64
package. For example here is signature for dbConnect
in RPostgres
. Would be awesome to have something similar here (I think it is possible to leverage some inspiration from RPostgres
package).
Due to a recent change in dbplyr (tidyverse/dbplyr@b8d2d2e), the trunk version now generates CASE
statements which are incompatible with Clickhouse, where an ELSE
block is mandatory (see https://clickhouse.yandex/docs/en/operators/index.html#conditional-expression).
dbplyr 1.1.0:
SELECT CASE WHEN (`x` > 20.0) THEN (1) ELSE (0) END AS `isGt` FROM `test`
dbplyr trunk:
SELECT CASE WHEN (`x` > 20.0) THEN (1) WHEN NOT(`x` > 20.0) THEN (0) END AS `isGt` FROM `test`
task 1 failed - "DB::Exception: Query with id = 1 is already running."
)When trying a dyplr left_join()
, RClickhouse emits the standard SQL join syntax, including table prefixes, which are not supported in Clickhouse (I'm using ClickHouse server version 18.10.3 revision 54405).
There has been custom code for joins in dplyr.R (see #14), but this has since been removed?
Do I overlook something or are joins unsupported at the moment?
Many thanks in advance for your help in this matter.
Dates do not display correctly (seems like some form of escaping has been applied), and spaces turn into T with a final Z at the end, as shown here:
However, within R, the data.table object looks fine:
This DT::datatable problem does not happen when the exact same query is used on clickhouse-r (my only other basis of comparison)
many R users don't have the right permissions to install lz4, so we can ship it with the package.
All non-ASCII Unicode characters are garbled in dataframes obtained via SELECT queries. Data is stored in UTF-8 in Clickhouse, but native R encoding on my Windows machine is CP1251.
Is there a way to preserve character encoding in RClickhouse? For now, I can only correct it manually with set_chr_encoding
function.
For example, RMariaDB
package is working correctly with UTF-8 strings on Windows.
library(tidyverse)
library(DBI)
library(RClickhouse)
library(rlang)
ch_db <- DBI::dbConnect(RClickhouse::clickhouse())
test_df <- ch_db %>%
dbGetQuery("select * from test") %>%
as_tibble()
# View result with garbled cyrillic characters
test_df
#> # A tibble: 1 x 2
#> translit russian
#> * <chr> <chr>
#> 1 Moskva РњРѕСЃРєРІР°
# Check encoding
test_df %>%
purrr::map_dfc(Encoding)
#> # A tibble: 1 x 2
#> translit russian
#> <chr> <chr>
#> 1 unknown unknown
# Set UTF-8 encoding for correct display
test_df %>%
purrr::map_dfc(rlang::set_chr_encoding, encoding = "UTF-8")
#> # A tibble: 1 x 2
#> translit russian
#> <chr> <chr>
#> 1 Moskva Москва
devtools::session_info()
#> Session info -------------------------------------------------------------
#> setting value
#> version R version 3.4.3 (2017-11-30)
#> system x86_64, mingw32
#> ui RTerm
#> language (EN)
#> collate Russian_Russia.1251
#> tz Europe/Moscow
#> date 2018-02-01
#> Packages -----------------------------------------------------------------
#> package * version date source
#> assertthat 0.2.0 2017-04-11 CRAN (R 3.4.1)
#> backports 1.1.2 2017-12-13 CRAN (R 3.4.3)
#> base * 3.4.3 2017-12-06 local
#> bindr 0.1 2016-11-13 CRAN (R 3.4.1)
#> bindrcpp 0.2 2017-06-17 CRAN (R 3.4.1)
#> broom 0.4.3 2017-11-20 CRAN (R 3.4.3)
#> cellranger 1.1.0 2016-07-27 CRAN (R 3.4.1)
#> cli 1.0.0 2017-11-05 CRAN (R 3.4.2)
#> colorspace 1.3-2 2016-12-14 CRAN (R 3.4.1)
#> compiler 3.4.3 2017-12-06 local
#> crayon 1.3.4 2017-09-16 CRAN (R 3.4.2)
#> datasets * 3.4.3 2017-12-06 local
#> DBI * 0.7 2017-06-18 CRAN (R 3.4.1)
#> dbplyr 1.2.0 2018-01-03 CRAN (R 3.4.3)
#> devtools 1.13.4 2017-11-09 CRAN (R 3.4.3)
#> digest 0.6.15 2018-01-28 CRAN (R 3.4.3)
#> dplyr * 0.7.4 2017-09-28 CRAN (R 3.4.2)
#> evaluate 0.10.1 2017-06-24 CRAN (R 3.4.1)
#> forcats * 0.2.0 2017-01-23 CRAN (R 3.4.1)
#> foreign 0.8-69 2017-06-22 CRAN (R 3.4.3)
#> ggplot2 * 2.2.1 2016-12-30 CRAN (R 3.4.1)
#> glue 1.2.0 2017-10-29 CRAN (R 3.4.2)
#> graphics * 3.4.3 2017-12-06 local
#> grDevices * 3.4.3 2017-12-06 local
#> grid 3.4.3 2017-12-06 local
#> gtable 0.2.0 2016-02-26 CRAN (R 3.4.1)
#> haven 1.1.1 2018-01-18 CRAN (R 3.4.3)
#> hms 0.4.1 2018-01-24 CRAN (R 3.4.3)
#> htmltools 0.3.6 2017-04-28 CRAN (R 3.4.1)
#> httr 1.3.1 2017-08-20 CRAN (R 3.4.1)
#> jsonlite 1.5 2017-06-01 CRAN (R 3.4.1)
#> knitr 1.19 2018-01-29 CRAN (R 3.4.3)
#> lattice 0.20-35 2017-03-25 CRAN (R 3.4.3)
#> lazyeval 0.2.1 2017-10-29 CRAN (R 3.4.2)
#> lubridate 1.7.1 2017-11-03 CRAN (R 3.4.2)
#> magrittr 1.5 2014-11-22 CRAN (R 3.4.1)
#> memoise 1.1.0 2017-04-21 CRAN (R 3.4.2)
#> methods * 3.4.3 2017-12-06 local
#> mnormt 1.5-5 2016-10-15 CRAN (R 3.4.1)
#> modelr 0.1.1 2017-07-24 CRAN (R 3.4.1)
#> munsell 0.4.3 2016-02-13 CRAN (R 3.4.1)
#> nlme 3.1-131 2017-02-06 CRAN (R 3.4.3)
#> parallel 3.4.3 2017-12-06 local
#> pillar 1.1.0 2018-01-14 CRAN (R 3.4.3)
#> pkgconfig 2.0.1 2017-03-21 CRAN (R 3.4.1)
#> plyr 1.8.4 2016-06-08 CRAN (R 3.4.1)
#> psych 1.7.8 2017-09-09 CRAN (R 3.4.1)
#> purrr * 0.2.4 2017-10-18 CRAN (R 3.4.2)
#> R6 2.2.2 2017-06-17 CRAN (R 3.4.1)
#> RClickhouse * 0.4.0 2018-01-20 CRAN (R 3.4.3)
#> Rcpp 0.12.15 2018-01-20 CRAN (R 3.4.3)
#> readr * 1.1.1 2017-05-16 CRAN (R 3.4.1)
#> readxl 1.0.0 2017-04-18 CRAN (R 3.4.1)
#> reshape2 1.4.3 2017-12-11 CRAN (R 3.4.3)
#> rlang * 0.1.6 2017-12-21 CRAN (R 3.4.3)
#> rmarkdown 1.8 2017-11-17 CRAN (R 3.4.3)
#> rprojroot 1.3-2 2018-01-03 CRAN (R 3.4.3)
#> rstudioapi 0.7 2017-09-07 CRAN (R 3.4.2)
#> rvest 0.3.2 2016-06-17 CRAN (R 3.4.1)
#> scales 0.5.0 2017-08-24 CRAN (R 3.4.1)
#> stats * 3.4.3 2017-12-06 local
#> stringi 1.1.6 2017-11-17 CRAN (R 3.4.2)
#> stringr * 1.2.0 2017-02-18 CRAN (R 3.4.1)
#> tibble * 1.4.2 2018-01-22 CRAN (R 3.4.3)
#> tidyr * 0.8.0 2018-01-29 CRAN (R 3.4.3)
#> tidyverse * 1.2.1 2017-11-14 CRAN (R 3.4.3)
#> tools 3.4.3 2017-12-06 local
#> utf8 1.1.3 2018-01-03 CRAN (R 3.4.3)
#> utils * 3.4.3 2017-12-06 local
#> withr 2.1.1 2017-12-19 CRAN (R 3.4.3)
#> xml2 1.2.0 2018-01-24 CRAN (R 3.4.3)
#> yaml 2.1.16 2017-12-12 CRAN (R 3.4.3)
Hi,
I get the following error:
Error in insert(conn@ptr, qname, value) :
count of elements in nested and nulls should be the same
when inserting a data.table with 1 row with some NA values
id ds remote_id dt nrow type name lastmeasure_at firstmeasure_at
<dbl> <chr> <chr> <int> <int> <chr> <chr> <dttm> <dttm>
1 NA test 874957 NA 268576 <NA> <NA> 2019-08-29 22:00:00 NA
All columns that have NA's are Nullable in the table definition.
I do not understand what i need to do to solve this.
Regards
> devtools::install_github("inkrement/clckhs@cpp")
Downloading GitHub repo inkrement/clckhs@cpp
from URL https://api.github.com/repos/inkrement/clckhs/zipball/cpp
Installing clckhs
'/usr/local/lib/R/bin/R' --no-site-file --no-environ --no-save --no-restore --quiet CMD INSTALL \
'/tmp/Rtmp1ymmeI/devtools72551f52e321/inkrement-clckhs-714e4b3' \
--library='/home/chris/TwitterPCAprediction/packrat/lib/x86_64-pc-linux-gnu/3.3.3' --install-tests
* installing *source* package ‘clckhs’ ...
** libs
g++ -std=c++11 -I/usr/local/lib/R/include -DNDEBUG -I. -I./vendor/clickhouse-cpp -I./vendor/clickhouse-cpp/contrib -std=c++11 -I/usr/local/include -I"/home/chris/TwitterPCAprediction/packrat/lib/x86_64-pc-linux-gnu/3.3.3/Rcpp/include" -fpic -g -O2 -fstack-protector-strong -Wformat -Werror=format-security -Wdate-time -D_FORTIFY_SOURCE=2 -g -c RcppExports.cpp -o RcppExports.o
In file included from RcppExports.cpp:4:0:
../inst/include/clckhs.h:8:32: fatal error: clckhs_RcppExports.h: No such file or directory
#include "clckhs_RcppExports.h"
^
compilation terminated.
/usr/local/lib/R/etc/Makeconf:141: recipe for target 'RcppExports.o' failed
make: *** [RcppExports.o] Error 1
ERROR: compilation failed for package ‘clckhs’
* removing ‘/home/chris/TwitterPCAprediction/packrat/lib/x86_64-pc-linux-gnu/3.3.3/clckhs’
Error: Command failed (1)
Hey guys,
I am having an issue with reading tables that contain a Date type column. The date column appears to have a weird format when read into R, even though the dates are written properly on clickhouse when tested via clickhouse-client on the terminal.
Please take a look at this example:
library(RClickhouse)
library(DBI)
drv = RClickhouse::clickhouse()
conn = dbConnect(drv, host="localhost", port=9000, user="abhi", password="RBgCR9YS")
dbListTables(conn)
dbSendQuery(conn, "create database test_db")
dbDisconnect(conn)
conn = dbConnect(drv, db = "test_db", host="localhost", port=9000, user="default", password="")
data = data.frame(
date = seq(as.Date('2017-01-01'), as.Date('2017-12-31'), by = 1),
value = 1:365
)
dbWriteTable(conn, "test_tbl", data, append = TRUE, overwrite = FALSE)
dbGetQuery(conn, "describe table test_tbl")
ret_data = dbGetQuery(conn, "select * from test_tbl")
head(ret_data)
date value
1 4062912-07-11 1
2 4063149-01-30 2
3 4063385-08-20 3
4 4063622-03-11 4
5 4063858-09-30 5
6 4064095-04-20 6
As you can see the dates are in a non-sensical format. Is this a bug of some sort?
This issue has existed even in the clckhs package since the changes committed in October.
Crazy idea. With the advent of clickhouse-client, I'm wondering if it's plausible to embed Clickhouse like SQLite. Parquet backend?
Error in select(conn@ptr, statement) : unsupported column type: UUID
when trying to query table with UUID column.
I'm trying to use RClickhouse
to connect to a clickhouse-server
Docker container running on localhost. I've tried both the current version on CRAN and the latest development version from GitHub.
I can launch a clickhouse-server
and verify that it is listening and responding on port 8123.
But then my attempt to make a DBI connection hangs - it usually requires that I shut down R and RStudio to recover. I've not included that bit in the reprex below but include it afterwards.
library(httr)
library(magrittr)
library(DBI)
library(RClickhouse)
system("docker run -d --name clickhouse --ulimit nofile=262144:262144 -p 8123:8123 yandex/clickhouse-server")
Sys.sleep(3) # pause while the container starts
x <- GET("http://localhost:8123/") %>% print()
#> Response [http://localhost:8123/]
#> Date: 2019-02-05 16:16
#> Status: 200
#> Content-Type: text/html; charset=UTF-8
#> Size: 4 B
#> Ok.
standard output and standard error
5494a53b1eb8b101dacc3c32ff55f3644236a3a5cd80478390796543fcb66dfa
Created on 2019-02-05 by the reprex package (v0.2.1)
This next statement now hangs. Any help appreciated, Otherwise, is this a bug report?
con <- dbConnect(RClickhouse::clickhouse(),
host = "localhost",
port = 8123,
db = "default",
user = "default",
password = "")
devtools::session_info()
#> - Session info ----------------------------------------------------------
#> setting value
#> version R version 3.5.2 (2018-12-20)
#> os Windows 10 x64
#> system x86_64, mingw32
#> ui RTerm
#> language (EN)
#> collate English_United Kingdom.1252
#> ctype English_United Kingdom.1252
#> tz Europe/London
#> date 2019-02-05
#>
#> - Packages --------------------------------------------------------------
#> package * version date lib source
#> assertthat 0.2.0 2017-04-11 [1] CRAN (R 3.5.1)
#> backports 1.1.3 2018-12-14 [1] CRAN (R 3.5.2)
#> bindr 0.1.1 2018-03-13 [1] CRAN (R 3.5.1)
#> bindrcpp 0.2.2 2018-03-29 [1] CRAN (R 3.5.1)
#> bit 1.1-14 2018-05-29 [1] CRAN (R 3.5.0)
#> bit64 0.9-7 2017-05-08 [1] CRAN (R 3.5.0)
#> callr 3.1.1 2018-12-21 [1] CRAN (R 3.5.2)
#> cli 1.0.1 2018-09-25 [1] CRAN (R 3.5.2)
#> crayon 1.3.4 2017-09-16 [1] CRAN (R 3.5.1)
#> curl 3.3 2019-01-10 [1] CRAN (R 3.5.2)
#> DBI * 1.0.0 2018-05-02 [1] CRAN (R 3.5.1)
#> dbplyr 1.3.0 2019-01-09 [1] CRAN (R 3.5.2)
#> desc 1.2.0 2018-05-01 [1] CRAN (R 3.5.1)
#> devtools 2.0.1 2018-10-26 [1] CRAN (R 3.5.2)
#> digest 0.6.18 2018-10-10 [1] CRAN (R 3.5.1)
#> dplyr 0.7.8 2018-11-10 [1] CRAN (R 3.5.2)
#> evaluate 0.12 2018-10-09 [1] CRAN (R 3.5.2)
#> fs 1.2.6 2018-08-23 [1] CRAN (R 3.5.1)
#> glue 1.3.0 2018-07-17 [1] CRAN (R 3.5.1)
#> highr 0.7 2018-06-09 [1] CRAN (R 3.5.1)
#> htmltools 0.3.6 2017-04-28 [1] CRAN (R 3.5.1)
#> httr * 1.4.0 2018-12-11 [1] CRAN (R 3.5.2)
#> knitr 1.21 2018-12-10 [1] CRAN (R 3.5.2)
#> magrittr * 1.5 2014-11-22 [1] CRAN (R 3.5.1)
#> memoise 1.1.0 2017-04-21 [1] CRAN (R 3.5.1)
#> pillar 1.3.1 2018-12-15 [1] CRAN (R 3.5.2)
#> pkgbuild 1.0.2 2018-10-16 [1] CRAN (R 3.5.2)
#> pkgconfig 2.0.2 2018-08-16 [1] CRAN (R 3.5.1)
#> pkgload 1.0.2 2018-10-29 [1] CRAN (R 3.5.2)
#> prettyunits 1.0.2 2015-07-13 [1] CRAN (R 3.5.1)
#> processx 3.2.1 2018-12-05 [1] CRAN (R 3.5.2)
#> ps 1.3.0 2018-12-21 [1] CRAN (R 3.5.2)
#> purrr 0.3.0 2019-01-27 [1] CRAN (R 3.5.2)
#> R6 2.3.0 2018-10-04 [1] CRAN (R 3.5.2)
#> RClickhouse * 0.4.0 2019-02-05 [1] Github (IMSMWU/RClickhouse@783f4c3)
#> Rcpp 1.0.0 2018-11-07 [1] CRAN (R 3.5.1)
#> remotes 2.0.2 2018-10-30 [1] CRAN (R 3.5.2)
#> rlang 0.3.1 2019-01-08 [1] CRAN (R 3.5.1)
#> rmarkdown 1.11 2018-12-08 [1] CRAN (R 3.5.2)
#> rprojroot 1.3-2 2018-01-03 [1] CRAN (R 3.5.1)
#> sessioninfo 1.1.1 2018-11-05 [1] CRAN (R 3.5.2)
#> stringi 1.2.4 2018-07-20 [1] CRAN (R 3.5.2)
#> stringr 1.3.1 2018-05-10 [1] CRAN (R 3.5.1)
#> testthat 2.0.1 2018-10-13 [1] CRAN (R 3.5.2)
#> tibble 2.0.1 2019-01-12 [1] CRAN (R 3.5.2)
#> tidyselect 0.2.5 2018-10-11 [1] CRAN (R 3.5.2)
#> usethis 1.4.0 2018-08-14 [1] CRAN (R 3.5.1)
#> withr 2.1.2 2018-03-15 [1] CRAN (R 3.5.1)
#> xfun 0.4 2018-10-23 [1] CRAN (R 3.5.1)
#> yaml 2.2.0 2018-07-25 [1] CRAN (R 3.5.1)
#>
#> [1] C:/Users/neilc/Documents/R/win-library/3.5
#> [2] C:/Program Files/R/R-3.5.2/library
First of all I am not sure if this is a problem with this library or the underlying c++ driver, which I suspect is the case.
I am creating temporary tables during the execution of a job, writing data and then copying it to the final table once I make sure that everything is fine.
The whole process is fine but the temporary tables are supposed to be deleted once I close the connection but the tables stay there, in the database I have specified with the connection, which is also odd as temporary tables aren´t supposed to have a database.
The remaining temporary tables are empty if I execute a count...
The query I use is:
CREATE TEMPORARY TABLE test_table_f55a142e4e164fc18ccaf0e2adf04b28 (...)
Executed as:
DBI::dbGetQuery(conn, query)
> union_all(genres1, genres2, genres3) %>% group_by(channel_id, genre) %>%
+ summarize(c = sum(num)) %>% ungroup() %>% as.data.frame()
Error in clckhs::select(conn@ptr, statement) :
DB::Exception: Syntax error: failed at position 55 (line 2, col 7): (SELECT `channel_id` AS `channel_id`, `genre` AS `genre`, `num` AS `num`
FROM (SELECT `channel_id`, `genre`, COUNT() AS `num`
FROM (SELECT *
FROM `videos1`) `uv, expected SELECT
Clickhouse is meant to be low-latency query engine. Thus it would be useful to measure overhead related to different layers applied by the package. Measure speed of cli vs. DBI vs. RClickhouse.
Looking at the dbConnect, it seems there's basically no way to specify the max_memory_usage and max_bytes_before_external_group_by etc. for dbConnect. Would this be possible to change? I figure it's a good idea to be able to map all clickhouse--client params -- these are fairly important ones.
I tried to install it on win10 (Rtools + R 3.4), but the compilation fails.
src: clickhouse 1.1.54140uptime: 1075055 [NA@NA:NA/default]
should be
src: clickhouse 1.1.54140 [username@password:hostname/default; uptime: 1075055]
Seems like the build fails on solaris. Has to be fixed soon.
https://www.r-project.org/nosvn/R.check/r-patched-solaris-x86/RClickhouse-00install.html
Not sure if that issue has been raised yet, but the package is no longer part of CRAN, can this be fixed?
Package ‘RClickhouse’ was removed from the CRAN repository.
Formerly available versions can be obtained from the archive.
Archived on 2019-12-17 as check issues were not corrected in time.
Travis CI returns the following error:
W: The repository 'http://repo.yandex.ru/clickhouse/trusty stable Release' does not have a Release file.
W: http://ppa.launchpad.net/couchdb/stable/ubuntu/dists/trusty/Release.gpg: Signature by key 15866BAFD9BCC4F3C1E0DFC7D69548E1C17EAB57 uses weak digest algorithm (SHA1)
W: http://ppa.launchpad.net/kirillshkrogalev/ffmpeg-next/ubuntu/dists/trusty/Release.gpg: Signature by key 1EC6DBC9AA41BD34B32CC5A15C50E96D8EFE5982 uses weak digest algorithm (SHA1)
E: Failed to fetch http://repo.yandex.ru/clickhouse/trusty/dists/stable/main/binary-amd64/Packages 404 Not Found
E: Failed to fetch http://repo.yandex.ru/clickhouse/trusty/dists/stable/main/binary-i386/Packages 404 Not Found
E: Some index files failed to download. They have been ignored, or old ones used instead.
The command "sudo -E apt-get -yq --no-install-suggests --no-install-recommends --force-yes install clickhouse-server-base clickhouse-server-common" failed and exited with 100 during .
dbReadTable fails, but subquery works, which is quite strange...
For example dbGetQuery(conn, "SELECT * from iris_df")
works and also dbGetQuery(conn, paste0("SELECT * FROM ", "iris_df"))
works. But the same code wrapped within a method does not work:
#' @export
#' @rdname ClickhouseConnection-class
setMethod("dbReadTable", "ClickhouseConnection", function(conn, name, ...) {
dbGetQuery(conn, paste0("SELECT * FROM ", name))
})
> dbReadTable(conn, "iris_df")
Note: method with signature ‘DBIConnection#character’ chosen for function ‘dbReadTable’,
target signature ‘ClickhouseConnection#character’.
"ClickhouseConnection#ANY" would also be valid
Error in (function (classes, fdef, mtable) :
unable to find an inherited method for function ‘dbGetQuery’ for signature ‘"character", "missing"’
> dbReadTable(conn, "iris_df")
Error in (function (classes, fdef, mtable) :
unable to find an inherited method for function ‘dbGetQuery’ for signature ‘"character", "missing"’
``
Hi..
I use devtools to install RClickhouse and have an error message during installation.
`
g++ -std=gnu++11 -I"/usr/share/R/include" -DNDEBUG -I. -I../inst/include -I./vendor/clickhouse-cpp -I./vendor/clickhouse-cpp/contrib -I"/usr/local/lib/R/site-library/Rcpp/include" -fpic -g -O2 -fdebug-prefix-map=/build/r-base-t3diwe/r-base-3.6.2=. -fstack-protector-strong -Wformat -Werror=format-security -Wdate-time -D_FORTIFY_SOURCE=2 -g -c RcppExports.cpp -o RcppExports.o
In file included from ./vendor/clickhouse-cpp/clickhouse/columns/column.h:5:0,
from ./vendor/clickhouse-cpp/clickhouse/block.h:3,
from ./vendor/clickhouse-cpp/clickhouse/query.h:3,
from ./vendor/clickhouse-cpp/clickhouse/client.h:3,
from ../inst/include/RClickhouse.h:4,
from RcppExports.cpp:4:
./vendor/clickhouse-cpp/clickhouse/columns/../types/types.h:3:10: fatal error: BigInt.hpp: No such file or directory
#include <BigInt.hpp>
^~~~~~~~~~~~
compilation terminated.
/usr/lib/R/etc/Makeconf:177: recipe for target 'RcppExports.o' failed
make: *** [RcppExports.o] Error 1
ERROR: compilation failed for package ‘RClickhouse’`
Does there have any suggestion for this? thanks to all...^^
dplyr and clickhouse support joins, but click house does not use the standard SQL syntax. Therefore, it might be the case, that we have to fix this.
Seems like logical values are not correctly translated.
cleaned <- tbl(con, "panel") %>% group_by(channel_id, date) %>%
summarize(
isRed = ifelse(isRed == 1, TRUE, FALSE),
isPaid = ifelse(isPaid == 1, TRUE, FALSE),
views = mean(views)
) %>% ungroup()
Error in clckhs::select(conn@ptr, statement) :
DB::Exception: Unknown identifier: TRUE
Greetings, I've been using clickhouse-r primarily but was excited to switch over to your package today in order to take advantage of dplyr's interactions with ggplot2. Initial tests performed well but when I tried to switch databases by setting the dbname parameter, calls to tbl() fail because it still thinks the database is "default"
When I try explicitly setting it, e.g. tbl(pool,"dbname.tablename") it reports this error:
Error in select(conn@ptr, statement) :
DB::Exception: Table default.dbname.tablename doesn't exist.
Methods:
CREATE TABLE test (
id UInt64 DEFAULT rand64()
) Engine=TinyLog;
the following query will return a column id containing positive and negative numbers, which should not be the case for a UInt64:
batch <- DBI::dbGetQuery(con, 'select * from test limit 100')
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.