I provided instructions for my colleague on how to update hive partitions using an odbc connection.
The set up works for me. We are both using the same instance of hosted rstudio, just with different user names.
The set up:
r script:
pacman::p_load(tidyverse, lubridate, scales, dbplyr, DBI, odbc, rlang, glue, aws.s3)
cy="2020"
cm="06"
cd="13"
hive_conn <- dbConnect(odbc(),
dsn = "Hive")
# update partitions in hive ----
update_partitions_query <- read_lines("/home/colleague_name/test/q.sql") %>%
glue_collapse(sep = "\n") %>%
glue_sql(.con = hive_conn)
dbExecute(conn = hive_conn, statement = update_partitions_query)
Here is q.sql which uses variables from the r script:
ALTER TABLE {rlang::parse_exprs(glue('adhoc.daily_session_parameters'))} ADD IF NOT EXISTS
PARTITION (year={cy}, month={cm}, day={cd})
location '{rlang::parse_exprs(glue("'s3://company_name-emr/tables/adhoc.db/daily_session_parameters/year={cy}/month={cm}/day={cd}'"))}'
If I run this code, all works as expected. The partitions are updated in hive.
However, when my colleague runs the exact same code, we get:
Warning message:
In is.na(x) : is.na() applied to non-(list or vector) of type 'symbol'
> dbExecute(conn = hive_conn, statement = update_partitions_query)
Error: nanodbc/nanodbc.cpp:1617: 00000: [Amazon][Hardy] (80) Syntax or semantic analysis error thrown in server while executing query. Error message from server: Error while compiling statement: FAILED: ParseException line 3:11 missing EOF at 's3' near ''''
<SQL> '
ALTER TABLE adhoc.daily_session_parameters ADD IF NOT EXISTS
PARTITION (year='2020', month='06', day='13')
location ''s3://company_name-emr/tables/adhoc.db/daily_session_parameters/year=2020/month=06/day=13'''
We tried creating afresh new project with only these two files, the r script and the sql file. We checked we are using the same libraries and that we have nothing else in our workspace. But the result is the same. I am unable to recreate the issue on my login, but with theirs, this error message is given and we cannot update the partitions.
Any idea what this might be? If there is more information that could be helpful let me know and I can share, I do not know what other info to provide.