Glue sql "Error in initialize(value, ...) : cannot use object of class “name” in new(): class “SQL” does not extend that class In addition: Warning message: In is.na(x) : is.na() applied to non-(list or vector) of type 'symbol'"

General
1

Till recently I have been using a .sql script in conjunction with a hive_conn to retrieve data from hive:

prediction_query <- read_lines(paste0(getwd(), '/sql_queries/test_prediction_data_query.sql')) %>%
  glue_collapse(sep = "\n") %>%
  glue_sql(.con = hive_conn)
my_df <- DBI::dbGetQuery(hive_conn, prediction_query)

test_prediction_data_query.sql:

with ...


select 
  i.s as s,
  i.install_dt as install_dt,
  i.platform as platform,
  i.usa as usa,
  i.publisher_name as publisher_name,
  coalesce(sn.sessions_day_from, 0) as {rlang::parse_exprs(glue('sessions_day_{day_from}'))},
  coalesce(sn.sum_session_time_day_from, 0) as {rlang::parse_exprs(glue('sum_session_time_day_{day_from}'))},
  coalesce(u.utility_day_from, 0) as {rlang::parse_exprs(glue('utility_day_{day_from}'))},
  round(coalesce(u.recent_utility_sum / u.utility_day_from, 0), 2) as recent_utility_ratio,
  coalesce(spn.revenue_day_from, 0) as {rlang::parse_exprs(glue('revenue_day_{day_from}'))}
from installs_base i 
left join sessions_day_from sn on sn.s = i.s 
left join utility_day_from u on u.s = i.s 
left join revenue_day_from spn on spn.s = i.s

I have been using this script for a few months with no issues, it worked.

Yesterday I made some changes in trying to install some other packages. The only significant change I can recall was updating packages with updates available using devtools.

This morning when I tried to run the script I get:

prediction_query <- read_lines(paste0(getwd(), '/sql_queries/test_prediction_data_query.sql')) %>%
+   glue_collapse(sep = "\n") %>%
+   glue_sql(.con = hive_conn)
Error in res[is_char] : object of type 'symbol' is not subsettable
In addition: Warning messages:
1: In is.na(res) :
  is.na() applied to non-(list or vector) of type 'symbol'
2: In is.na(x) : is.na() applied to non-(list or vector) of type 'symbol'

I did some Google searching but cannot find how to overcome this error message.

Those variables in my select statement do render the expected value in the console:

> rlang::parse_exprs(glue('sessions_day_{day_from}'))
[[1]]
sessions_day_7

> rlang::parse_exprs(glue('sum_session_time_day_{day_from}'))
[[1]]
sum_session_time_day_7

> rlang::parse_exprs(glue('utility_day_{day_from}'))
[[1]]
utility_day_7

> rlang::parse_exprs(glue('revenue_day_{day_from}'))
[[1]]
revenue_day_7

Any ideas on why my sql script stopped working with glue_sql?

2

Which version of glue did you have and which are you using now ?

It seems there was some changes in glue_sql that could have had an impact on your usage

You could try installed previous version to try if it is working again.

3

Hi, thanks for the suggestion and sorry for the slow response. I did try installing an older version of glue since I had recently updated packages. Even after installing an older version the problem persisted. Trouble is I'm not sure which version of glue I was using previously! I guess I'll need to figure out how to use the new glue to construct this query in the way that I need.

4

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.