In my workplace, R is integrated with Dremio and I can access the tables in Dremio using RODBC::sqlQuery() function. Tables are having around hundred thousand entries. I am creating data-frame of SQL tables using this function but some rows are storing data in different format. I have read that sqlQuery function converts the binary SQL types to list of raw vectors, hence I am getting cell value like this:
as.raw(c(0x56, 0xfd, 0x36, 0xaf, 0x63, 0x18, 0x77, 0x2a, 0x09, 0x35, 0x9f, 0xe3))
dput(df)
structure(list(`_Id` = structure(list(as.raw(c(0x56, 0xfd, 0x36,
0xaf, 0x63, 0x18, 0x77, 0x2a, 0x09, 0x35, 0x9f, 0xe3)), as.raw(c(0x56,
0xfd, 0x37, 0x07, 0x63, 0x18, 0x77, 0x2a, 0x09, 0x35, 0x9f, 0xeb
)), as.raw(c(0x56, 0xfd, 0x38, 0x7a, 0x63, 0x18, 0x77, 0x2a,
0x09, 0x35, 0xa0, 0x07)), as.raw(c(0x56, 0xfd, 0x38, 0x8b, 0x63,
0x18, 0x77, 0x2a, 0x09, 0x35, 0xa0, 0x0e)), as.raw(c(0x56, 0xfd,
0x38, 0x9d, 0x63, 0x18, 0x77, 0x2a, 0x09, 0x35, 0xa0, 0x12))), class = "ODBC_binary")), class = "data.frame", row.names = c(NA,
5L))
I want cell value to be a single string id as
"56fd36af6318772a09359fe3" so that I can use it as matching criteria with other tables. Is there any way to do it?
You can use dplyr
library(dplyr)
df$`_Id` %>%
sapply(., function(x) { paste0(x, collapse = "") }) %>%
as.data.frame(string_id=as.character(.), stringsAsFactors=FALSE)
# 1 56fd36af6318772a09359fe3
# 2 56fd37076318772a09359feb
# 3 56fd387a6318772a0935a007
# 4 56fd388b6318772a0935a00e
# 5 56fd389d6318772a0935a012