exasol / r-exasol Goto Github PK
View Code? Open in Web Editor NEWThe EXASOL package for R provides an interface to the EXASOL database.
Home Page: http://www.exasol.com
License: BSD 3-Clause "New" or "Revised" License
The EXASOL package for R provides an interface to the EXASOL database.
Home Page: http://www.exasol.com
License: BSD 3-Clause "New" or "Revised" License
Something like the following call fails: dbListTables(exaconn, schema="RDEMO")
The reason is that readData is called with a SQL statement that filters for table_schema = "RDEMO" in this case, producing an error due to double quoted schema name.
Currently any users of r-exasol need to compile the package on their machines, which require additional dependencies (RTools on Windows for example). It would be much easier for the users of this package to install the binary package from CRAN.
Note: CRAN only provides binary packages for Windows and MacOsX. For Linux, still the compilation will be required.
r-exasol as binary package on CRAN.
r-exasol implements the DBI Interface, but not all of the methods are working right now.
(I checked the first 3 of this list and they are not working.
Implement all remaining methods which are not working.
Calling exa.writeData
multiple times in a loop cases socket error.
For example, the following code crashed at iteration 4065.
require(RODBC)
require(exasol)
C <- odbcConnect("exasolution")
for(i in 1:5000) {
df <- data.frame( ... single row of some data ... )
exa.writeData(C, df, "mytable")
}
$ uname -s -r -v -m -p -i -o
Linux 3.13.0-65-generic #106-Ubuntu SMP Fri Oct 2 22:08:27 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux
$ cat /proc/meminfo
MemTotal: 32896676 kB
MemFree: 14085916 kB
Buffers: 133672 kB
Cached: 8216236 kB
SwapCached: 1908 kB
Active: 13122696 kB
Inactive: 5180692 kB
Active(anon): 9936752 kB
Inactive(anon): 16780 kB
Active(file): 3185944 kB
Inactive(file): 5163912 kB
Unevictable: 0 kB
Mlocked: 0 kB
SwapTotal: 2093052 kB
SwapFree: 2075980 kB
Dirty: 0 kB
Writeback: 0 kB
AnonPages: 9951932 kB
Mapped: 32472 kB
Shmem: 52 kB
Slab: 269212 kB
SReclaimable: 248576 kB
SUnreclaim: 20636 kB
KernelStack: 1160 kB
PageTables: 23472 kB
NFS_Unstable: 0 kB
Bounce: 0 kB
WritebackTmp: 0 kB
CommitLimit: 18541388 kB
Committed_AS: 10122864 kB
VmallocTotal: 34359738367 kB
VmallocUsed: 57396 kB
VmallocChunk: 34359669400 kB
HardwareCorrupted: 0 kB
AnonHugePages: 0 kB
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 2048 kB
DirectMap4k: 33570816 kB
DirectMap2M: 0 kB
Some methods of DBI API are not yet implemented and marked with TODO
in the R code.
Fix all remaining TODO's.
Older versions of release-droid needed 3 workflows for the release process. In the latest release those workflows are optional.
Important: Need to remove both checksum workflows, because they depend each other.
Maybe a good idea would be to move the integration tests and upload into the upload_github_release workflow and acuatally upload the resulting tgz's to the release.
Clients can install then use the binary packages simply with install.packages
, and don't need to install all the development packages.
We want to move from Travis to GitHub Actions.
See #41
What was resolved in that ticket was adding some connection snippets to Rstudio.
What's not done yet is the ExaSol connection showing up in the connections pane when you connect to it via code or the wizard, for that the connections contract probably must be added to the library.
It's also possible this got fixed via the update of the DBI package for release 3.5.0
Are there any plans to update the package to work with R3.6?
There are many issues reported by the lintr
. They should be addressed so that the codebase keeps consistent style.
Hi,
I am trying to run the following code:
library(RODBC)
library(exasol)
drv <- dbDriver("exasol") **PASS**
con <- dbConnect(drv, exahost = "host", uid = "my_id", pwd ="my_pass", schema = "SCHEMA") . **PASS**
dbListTables(con) **Throws an error**
error:
Error in try(.Call(C_asyncRODBCQueryFinish, slot, 1)) :
Unknown ODBC error
Error in exa.readData(conn, qstr, ...) :
Could not allocate SQLAllocHandle
Error in exa.readData(conn, qstr, ...) : Unknown ODBC error
I am using mac, os 10, Exasol ver 5.17, and am able to access the DB from the Exaplus and see tables etc..
thanks!
Shahar
The nightly update of the docker images (the images are used to speed up the CI builds by caching the respective R environments) is not working correctly. The script which checks for available tags returns 1 if the tag does not exists.
Fix the script which checks for a specific tag of the docker image, and do not return 1 if the tag is not available.
We need to get more detailed information about errors, reported by customers.
Add logging at function call level for critical parts of the code.
Currently the implementation of r-exasol uses ODBC as the underlying communication protocol with the database. This however has several drawbacks:
Switching to Websockets could resolve some of the issues.
Hello, I am trying to install r-exasol
on my mac but the compilation fails. It installs fine on my windows machine.
Below is the error
> devtools::install_github("EXASOL/r-exasol")
Downloading GitHub repo EXASOL/r-exasol@HEAD
✓ checking for file ‘/private/var/folders/q7/lvjhd0sx3fd9mqs1h7mlt__40000gn/T/RtmpcQFvyB/remotes20c79c4fb7d/exasol-r-exasol-3784b3c/DESCRIPTION’ ...
─ preparing ‘exasol’:
✓ checking DESCRIPTION meta-information ...
─ cleaning src
─ installing the package to process help pages
-----------------------------------
─ installing *source* package ‘exasol’ ...
** using staged installation
CPPFLAGS:-I/usr/local/include
CPPFLAGS:-I/usr/local/include -I.
checking whether the C++ compiler works... yes
checking for C++ compiler default output file name... a.out
checking for suffix of executables...
checking whether we are cross compiling... no
checking for suffix of object files... o
checking whether we are using the GNU C++ compiler... yes
checking whether clang++ -mmacosx-version-min=10.13 -std=gnu++14 accepts -g... yes
checking how to run the C++ preprocessor... clang++ -mmacosx-version-min=10.13 -std=gnu++14 -E
checking whether we are using the GNU C++ compiler... (cached) yes
checking whether clang++ -mmacosx-version-min=10.13 -std=gnu++14 accepts -g... (cached) yes
configure: creating ./config.status
config.status: creating src/Makevars
** libs
clang++ -mmacosx-version-min=10.13 -std=gnu++14 -I"/Library/Frameworks/R.framework/Resources/include" -DNDEBUG -I/usr/local/include -I. -I'/Library/Frameworks/R.framework/Versions/4.0/Resources/library/testthat/include' -I/usr/local/include -fPIC -Wall -g -O2 -c r_exasol/connection_context.cpp -o r_exasol/connection_context.o
In file included from r_exasol/connection_context.cpp:1:
In file included from ./r_exasol/connection_context.h:5:
./r_exasol/external/sql.h:16:10: error: 'sql.h' file not found with <angled> include; use "quotes" instead
#include <sql.h>
^~~~~~~
"sql.h"
./r_exasol/external/sql.h:17:10: fatal error: 'sqlext.h' file not found
#include <sqlext.h>
^~~~~~~~~~
2 errors generated.
make: *** [r_exasol/connection_context.o] Error 1
ERROR: compilation failed for package ‘exasol’
─ removing ‘/private/var/folders/q7/lvjhd0sx3fd9mqs1h7mlt__40000gn/T/RtmpcjyrBJ/Rinst211d110fa934/exasol’
-----------------------------------
ERROR: package installation failed
Error: Failed to install 'exasol' from GitHub:
System command 'R' failed, exit status: 1, stdout + stderr (last 10 lines):
E> "sql.h"
E> ./r_exasol/external/sql.h:17:10: fatal error: 'sqlext.h' file not found
E> #include <sqlext.h>
E> ^~~~~~~~~~
E> 2 errors generated.
E> make: *** [r_exasol/connection_context.o] Error 1
E> ERROR: compilation failed for package ‘exasol’
E> * removing ‘/private/var/folders/q7/lvjhd0sx3fd9mqs1h7mlt__40000gn/T/RtmpcjyrBJ/Rinst211d110fa934/exasol’
E> -----------------------------------
E> ERROR: package installation failed
Is this a bug or Is there something I need to do additionally for mac?
Thanks
Hi all,
We're seeing a weird error, which happens quite frequently, however I cannot seem to find a pattern for it:
Error in odbcGetInfo(con) : argument is not an open RODBC channel
In the warnings (I guess coming from the Exasol RODBC library) I see the following:
Calls: myFunction ... <Anonymous> -> <Anonymous> -> EXANewConnection -> odbcGetInfo
In addition: Warning messages:
1: In odbcDriverConnect(con_str) :
[RODBC] ERROR: state 08S01, code -1, message [unixODBC][EXASOL][EXASolution driver]No server listening.
2: In odbcDriverConnect(con_str) :
[RODBC] ERROR: state 08S01, code -1, message [unixODBC][EXASOL][EXASolution driver]No server listening.
3: In odbcDriverConnect(con_str) : ODBC connection failed
Interestingly, this happens only when I'm trying to write data to the warehouse and only with the r-exasol package, whereas python package seems to be working fine.
Any help appreciated!
problem 1: if the first line is a comment, get error:
-- as;dlkaslkd
Error in if (toupper(regmatches(statement, gregexpr("^\w+", statement, :
argument is of length zero
problem 2:
if have with statement as first line, get no result (but no error)
Hi,
when I want to install from github R gives me this error message
devtools::install_github("EXASOL/r-exasol") Downloading GitHub repo EXASOL/r-exasol@HEAD Error in utils::download.file(url, path, method = method, quiet = quiet, : cannot open URL 'https://api.github.com/repos/EXASOL/r-exasol/tarball/HEAD'
Session Info:
R version 3.5.1 (--) Platform: x86_64-pc-linux-gnu (64-bit) Running under: Ubuntu 16.04.6 LTS Matrix products: default BLAS/LAPACK: /usr/lib/libopenblasp-r0.2.18.so
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] Rook_1.1-1 RJSONIO_1.3-1.2 Cairo_1.5-6
loaded via a namespace (and not attached): [1] Rcpp_1.0.1 magrittr_1.5 usethis_1.6.1 devtools_2.3.1 [5] pkgload_1.0.2 R6_2.4.0 brew_1.0-6 rlang_0.4.7 [9] fansi_0.4.0 tools_3.5.1 pkgbuild_1.1.0 sessioninfo_1.1.1 [13] cli_2.0.2 withr_2.1.2 ellipsis_0.3.1 remotes_2.2.0 [17] assertthat_0.2.1 digest_0.6.19 rprojroot_1.3-2 crayon_1.3.4 [21] processx_3.4.3 callr_3.4.3 fs_1.3.1 ps_1.3.0 [25] codetools_0.2-16 curl_3.3 testthat_2.3.2 memoise_1.1.0 [29] glue_1.4.1 compiler_3.5.1 desc_1.2.0 backports_1.1.4 [33] prettyunits_1.0.2
Any idea?
Best,
David
dbListTables(conn = db_con, schema = "SCHEMA_NAME") gives error:
Error in exa.readData(conn, qstr, ...) : Could not read header.
Error in exa.readData(conn, qstr, ...) :
42000 -6811776 [EXASOL][EXASolution driver]object "SCHEMA_NAME" not found [line 1, column 82]
Just a quick question.
Is the connection between the client and server encrypted when using this driver ?
Currently we use the forked DBITest for running the integration tests: marcelboldt/DBITest. However, they depend on a very old version of the DBI package (0.3.1.9008), also currently we use a forked version of DBI in the test setup: marcelboldt/DBI.
dpblyr for example depends on version 1.0.0 of the DBI package.
Verify what it takes to use the standard DBITest package (https://github.com/r-dbi/DBItest). If this is not feasible, use our own fork of DBITest and make it compatible with the official version of DBI (https://github.com/r-dbi/DBI).
Calling exa.write
or exa.read
from provided examples causes unknown error.
>require(RODBC)
>require(exasol)
>C <- odbcConnect("exasolution")
>odbcQuery(C, "CREATE SCHEMA test")
[1] 1
>odbcQuery(C, "CREATE TABLE test.twogroups (groupid INT, val DOUBLE)")
[1] 1
>exa.writeData(C, twogroups, tableName = "test.twogroups")
Fehler in exa.writeData(C, twogroups, tableName = "test.twogroups") :
Failed to receive proxy header (0 != 24)
odbcQuery
gets executed such that schema and table are created in the database.
I'm running on OS X El Capitan (10.11.2) using the following Driver EXASolution_ODBC-5.0.13-MacOS.dmg.
Currently, whenever any Roxygen2 documentation was changed, the developer must call manually devtools::document()
in order to update both the .Rd files as also the NAMESPACE file. It's easy to forget this!
Implement a GH Actions which calls devtools::document()
and verifies that the git workspace remains clean. If git detects any changes, it means that the submitter forgot to trigger the documentation update, and it must fail the GH Actions.
dbGetQuery and dbExecute cannot execute statements that start with a comment. For example:
dbExecute(con, statement = '/*comment*/drop table blah.CARS')
Error in if (stmt_cmd == "SELECT") { : argument is of length zero
dbGetQuery(con, statement = '/*comment*/select * from blah.CARS')
Error in if (toupper(regmatches(statement, gregexpr("^\w+", statement, :
argument is of length zero
In order to receive a resultset, the DB cluster currently needs to open a (HTTP) connection to the client. This is not possible e.g. if the client is behind NAT gateway or a restrictive firewall.
Solution: implement a fallback via EXPORT TO LOCAL CSV, which uses a proxy inside the cluster to which the client can connect.
R-Exasol communicates with an Exasol database via a TLS-secured connection.
Today the integration test are executed only under Linux, using the integration-test-docker-environment.
However, it be good to run the tests also under Windows and MacOsX.
For this purpose we need an external database instance available.
I need 3.3.3 to install devtools, but then I get
> devtools::install_github("EXASOL/r-exasol")
...
Installation failed: Command failed (1)
Any ideas?
> devtools::session_info()
Session info ----------------------------------------------------------------------------------------------------------------------------------
setting value
version R version 3.3.3 (2017-03-06)
system x86_64, mingw32
ui RStudio (1.1.383)
language (EN)
collate English_United States.1252
tz America/New_York
date 2017-12-07
Packages --------------------------------------------------------------------------------------------------------------------------------------
package * version date source
base * 3.3.3 2017-03-06 local
curl 3.0 2017-10-06 CRAN (R 3.3.3)
datasets * 3.3.3 2017-03-06 local
DBI * 0.7 2017-06-18 CRAN (R 3.3.3)
devtools 1.13.4 2017-11-09 CRAN (R 3.3.3)
digest 0.6.12 2017-01-27 CRAN (R 3.3.3)
git2r 0.19.0 2017-07-19 CRAN (R 3.3.3)
graphics * 3.3.3 2017-03-06 local
grDevices * 3.3.3 2017-03-06 local
httr 1.3.1 2017-08-20 CRAN (R 3.3.3)
memoise 1.1.0 2017-04-21 CRAN (R 3.3.3)
methods * 3.3.3 2017-03-06 local
R6 2.2.2 2017-06-17 CRAN (R 3.3.3)
stats * 3.3.3 2017-03-06 local
tools 3.3.3 2017-03-06 local
utils * 3.3.3 2017-03-06 local
withr 2.1.0 2017-11-01 CRAN (R 3.3.3)
Using Linux Mint 17. Installed unixodbc 2.2.14
Here is the problem when I try to compile the package in RStudio.
> install_github("EXASOL/r-exasol")
Downloading GitHub repo EXASOL/r-exasol@master
Installing exasol
'/usr/lib/R/bin/R' --no-site-file --no-environ --no-save --no-restore CMD INSTALL \
'/tmp/RtmpLklwnf/devtools316dfe767b/EXASOL-r-exasol-9d23594' --library='/usr/local/lib/R/site-library' --install-tests
* installing *source* package ‘exasol’ ...
** libs
gcc -std=gnu99 -I/usr/share/R/include -DNDEBUG -fpic -g -O2 -fstack-protector-strong -Wformat -Werror=format-security -D_FORTIFY_SOURCE=2 -g -c exasol.c -o exasol.o
gcc -std=gnu99 -shared -L/usr/lib/R/lib -Wl,-z,relro -o exasol.so exasol.o -L/usr/lib/R/lib -lR
installing to /usr/local/lib/R/site-library/exasol/libs
** R
** preparing package for lazy loading
** help
*** installing help indices
** building package indices
** testing if installed package can be loaded
Error in dyn.load(file, DLLpath = DLLpath, ...) :
unable to load shared object '/usr/local/lib/R/site-library/exasol/libs/exasol.so':
/usr/local/lib/R/site-library/exasol/libs/exasol.so: undefined symbol: SQLFreeHandle
Error: loading failed
Execution halted
ERROR: loading failed
* removing ‘/usr/local/lib/R/site-library/exasol’
Error: Command failed (1)
Hi, is it necessary to have R dependence of >= 3.3.0? thanks!
if (length(field_types != ncol(data)))
line 1376 in r-exasol/R/EXADBI.R will always be TRUE, should be if (length(field_types) != ncol(data))
.
see also tgouhier/biwavelet#22
I am trying to connect exasol with R with the following code. The problem occurs with Mac OS.
library(RODBC)
library(exasol)
con <- dbConnect("exasol", **)
# removed the connection credentials
df1 <- dbGetQuery(con,paste("query", sep=""))
# removed the query
Error in try(.Call(C_asyncRODBCQueryFinish, slot, 1)) :
Unknown ODBC error
Error in exa.readData(conn, statement, ...) :
Could not allocate SQLAllocHandle (-2)
Error in exa.readData(conn, statement, ...) : Unknown ODBC error`
My system is Mac OS "x86_64" . Please can you help resolving this. ?
With #79 we prepared the CRAN submission, and there is a Dockerfile and some script which facilitates the related CRAN compliance tests. It would be good to run those test with every submit in order to avoid adding new features which cause issues for new CRAN submissions.
Create a GH Actions (for each PR or when pushing to branch master) which build the Docker container under /tests/cran-submission and run the scripts on it. For that, the Dockerfile needs to be changed so that it runs the script automatically when starting the container.
Currently, we forward the password as is to the odbc connection string.
However, if the password contains a ";", this will break the syntax.
See this discussion how to fix this.
Change the encoding of the password so that it will not break the ODBC connection string syntax if the password contains a ";":
Is there an example on how to add an ExaSol connection to the connections pane?
We need a dummy EXASOL odbc driver that would simulate the ODBC communication.
I tried to use SQLITE odbc driver, but SQLITE does not support EXPORT SQL command.
Otherwise, it is hard to write proper unit tests.
I have a R table with an empty column of type character. When I try to write this table to Exasol with dbWriteTable
(to a non-existing table), an invalid CREATE TABLE
statement is generated:
[EXASOL][EXASolution driver]syntax error, unexpected '-', expecting UNSIGNED_INTEGER_ [line 1, column 815]
In addition: Warning message:
In max(nchar(as.character(x)), na.rm = TRUE) :
no non-missing arguments to max; returning -Inf
In these cases, the function max(nchar(as.character(x)), na.rm = TRUE)
returns -Inf
and the generated CREATE TABLE
statement tries to create a column of type VARCHAR(-Inf)
.
Workaround:
backup your R table, identify empty char columns, temporary fill them with something, execute dbWriteTable
, create and save DDL statement on exasol, drop table on exasol, create new table on exasol with saved DDL and fix varchar lenghts, write backup table to exasol.
On my Mac, the connection seems to work, but when I try to receive data (exa.readData) it fails to allocate an handle.
Invoking dbConnect(EXAConnection)
cannot connect to ODBC.
Most likely problem is here: All odbc keys and values are converted to upper case, including uid and password!
Invoking dbConnect() with existing connection must work.
In preparation of #56 we need to refactor the C code, and support C++ unit test.
The r-exasol library should be prepared to implement C++ unit tests and can be implemented in C++.
dbExecute fails at the dbGetRowsAffected step, because no function dbGetRowsAffected is registered for the returned EXAResult.
Workaround:
register dbGetRowsAffected for EXAResult:
setMethod("dbGetRowsAffected", signature("EXAResult"), function(res)
{
return(res$rows_affected)
}))
res$rows_affected seems to be always 0 though...
Project does not contain changelog/developer guide/...
We should add the versioning with changelog. I think simple git tag should suffice, since r-exasol
is installed using devtools::install_github
.
Later we can decide if releasing to CRAN required.
Basic Example to use dbplyr:
devtools::install_github("exasol/r-exasol")
install.packages("dbplyr")
install.packages("tidyverse")
install.packages("dplyr")
library(exasol)
library(dplyr)
library(dbplyr)
library(tidyverse)
con <- dbConnect("exa", exahost = "172.23.0.2:8888", uid = "sys", pwd = "exasol")
dbListTables(con)
mytable <- tbl(con,in_schema("TEST", "COMP1")) %>% filter(IDX<10) %>% select(IDX, CHAR1_1) %>% show_query() %>% collect()
Situation:
Fix:
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.