imsmwu / rclickhouse Goto Github PK

• dbQuoteString (vectorized!)
• dbQuoteIdentifer (already exists, but untested!)
• show
• sqlData (? default seems quite good...)

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")

1. 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."

• enhance sql-translation (add custom if null and is null checks; add var and sd functions)
• remove packrat
• uuid support #24
• search for config file (.e.g, /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:

• unload dynamic libraries (pthread, lz4?)
• force gc

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`

• Rename package according to R7DBI-naming convention
• Add support for new DataTypes (especially Enum, Array & FixedString)
• Extend documentation
• parallel execution of the same query does not work (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

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)

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.

RcppCore/Rcpp#69

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.

install_log.txt

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

I get two strange errors:

• Error in select(., mpg) : object 'mpg' not found
• Error in select(., units) : expecting an external pointer

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:

• dbBind
• dbColumnInfo
• dbGetStatement
• dbIsValid
• dbHasCompleted (implement batching first)
• dbGetRowCount
• dbGetRowsAffected
• dbGetRowCount

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')