Read and Write Tables From Hive With R

library(DBI)

con <- dbConnect(odbc::odbc(),
                      Driver   = "Cloudera ODBC Driver for Apache Hive 64-bit",
                      Host     = "nn1.pX.company.prod.saagie.io",
                      Port     = 10000,
                      Schema   = "default",
                      AuthMech = 3,
                      UseSASL  = 1,
                      UID      = "user",
                      PWD      = "password")

library(DBI)
library(getPass)

# Method 1 (interactive) : Use in RStudio. Enter your password in an interactive pop-up window.
system('kinit user',input=getPass('Enter your password: '))

# Method 2 (scripts) : Use outside of RStudio.
# Write the password in the command line or store it in an environment variable.
# Uncomment the following line to use it.
# system('echo password | kinit user')

con <- dbConnect(odbc::odbc(),
                 Driver = "Cloudera ODBC Driver for Apache Hive 64-bit",
                 Host = "nn1.pX.company.prod.saagie.io",
                 Port = 10000,
                 Schema = "default",
                 AuthMech = 1)

# In this example of the Linux Hive ODBC driver, the dbListTables method returns only the first letter of each schema and table.
# A workaround is to use "show schemas" and "show tables" in plain SQL.

# To list all tables of all schemas.
dbListTables(con)
dbGetQuery(con, 'show schemas')
dbGetQuery(con, 'show tables')

sample <- DBI::dbReadTable(con, "sample") # Retrieve all data from a table

# To create a lazy "tbl" from an Impala table.
forecast_db <- tbl(con, in_schema('forecast', 'forecast_weekly'))
forecast_db # Print columns and column types

# The query is written in dplyr syntax, but is executed on a remote SQL database.
query <- forecast_db %>%
    summarise(mean_forecast = forecast %>% as.numeric() %>% mean)

show_query(query) # To show the query that will be executed.
query # To execute the lazy query when the result is printed.

# Example of verbs used by dplyr.
forecast_db %>%
    filter(prediction_date == max(prediction_date)) %>%
    group_by(reference) %>%
    summarise(forecast_mean = mean(forecast),
              forecast_max = max(forecast),
              nb_forecast_total = n())

sales_db <- tbl(con, in_schema('forecast', 'sales_weekly'))
sales_db

forecast_db <- forecast_db %>%
    mutate(reference = as.character(reference))

diff_db <- inner_join(forecast_db, sales_db, by=c('reference', 'year', 'week'))

diff_by_ref <- diff_db %>%
    group_by(reference, year, week) %>%
    summarise(diff_by_week = abs(forecast - quantity)) %>% # To differentiate between forecast and reality for each prediction.
    group_by(reference) %>%
    summarise(diff_by_ref = sum(diff_by_week)) # Sum of all differences for each reference.

diff_db # To execute all the lazy queries above.

# To list all databases.
dbListTables(conn)
# To list the "default" database.
dbGetQuery(conn, "show tables")

# To get all the elements of the "default" database.
d <- dbReadTable(conn, "table_name")
# OR
d <- dbGetQuery(conn, "select * from table_name")
# To get all the other elements that are not in the "default" database.
d <- dbReadTable(conn, nameBDD.table_name)

dbSendUpdate(conn, "CREATE TABLE table_name (attribute1 string, attribute2 int) STORED AS PARQUET")

dbGetQuery(conn, "INSERT INTO table_name VALUES ('test', 1)")