0

I have a table in SQL that I'm testing out dplyr. I have two inputs a date range and a text field which is a company

I run the following R code below

  # Filter on the Customer and the date range the user selects
  mydf <- db %>%
    tbl(table) %>%
    filter(TableDate >= start_date & TableDate <= end_date) %>% 
    filter(grepl('company', company_name)) %>% 
    collect()

So in theory this should select information from a single table between start_date and end_date for the company company like a fuzzy match as the company names contain things like X industries and Y incorporated

I get an error like follows

Error in .local(conn, statement, ...) : could not run statement: execute command denied to user 'shiny'@'%' for routine 'mydb.GREPL'

when i have a look at the sql

myDF <- tbl(db,table)
myDF1 <- filter(myDF, TableDate >= start_date & TableDate <= end_date)
myDF2 <- filter(myDF1, grepl('company', company_name))

explain(myDF2)

I get the output

<SQL>
SELECT *
FROM (SELECT *
FROM `table`
WHERE (`TableDate ` >= '2015-01-01' AND `TableDate ` <= '2015-06-01')) `lpknlorhcn`
WHERE (GREPL('company', `company_name`))

Error in .local(conn, statement, ...) : could not run statement: execute command denied to user 'shiny'@'%' for routine 'mydb.GREPL'

I have modified the code and output slightly for privacy reasons

Questions

  • Does anyone know how to set this up correctly (I know its a permissions thing on the mysql side but im not sure where as the user shiny has execute access

  • Why does the SQL in the explain plan look so funny (it has two where clauses for instance) and what is lpknlorhcn

Thank you kindly for your help

Community
  • 1
  • 1
John Smith
  • 2,448
  • 7
  • 54
  • 78

1 Answers1

1

MySQL doesn't support grepl(). You can either use %like% or collect the data first, then use grepl().

See this question about using grepl in dply/MySQL for more info and examples.

Community
  • 1
  • 1
crazybilly
  • 2,992
  • 1
  • 16
  • 42