I am creating a shiny app which is connected to hive tables in HDFS.

Below is the working app created on r dataframe. Calculation are done on the fly and columns will appear only if they are in the filter in the dashboard i.e. if state selected then only state wise will be shown

library(shiny)
library(dplyr)

table_a <- data.frame(id =c(1,1,1,2,2,2,3,4,4),
                  state = c("Texas","Texas","Texas","Alaska","Alaska","Alaska","Nevada","Texas","Texas"),
                  gender = c("M","M","M","F","F","F","F","F","F"),
                  channel = c("Y","Y","H","Y","D","D","H","Y","Y"),
                  spend = c(10,10,5,20,20,5,7,1,2))

u <- unique(table_a$state)
v <- unique(table_a$gender)

ui <- fluidPage(
titlePanel(" APP"),
sidebarLayout(
sidebarPanel(
  selectInput("x", "Select state", choices = u , multiple = TRUE),
  selectInput("y", "Select gender", choices = v, multiple = TRUE),
  submitButton("Submit")
),
mainPanel(
  uiOutput("t"),
  tableOutput("m")
)))

server <- function(input, output) {
check <- reactive({
state_1 <- input$x
gender_1 <- input$y

grps <- c("state", "gender")[c(!is.null(state_1), !is.null(gender_1))]

if (is.null(state_1)) state_1 <- u
if (is.null(gender_1)) gender_1 <- v

print(input$x)
table_a %>%
  filter(state %in% state_1, gender %in% gender_1) %>%
  group_by(across(all_of(grps))) %>%
  summarise(Total_spend = sum(spend))
})

output$m <- renderTable(
check()
)

output$t <- renderUI({input$x
})

##  But My data is in hive application on top of HDFS.
##  So I want to construct SQL query dynamically using the user input in r shiny
##  and then building the ‘where’ clause and 'group by clause' dynamically
##  and aggregating the spend at last.

##  The query will be passed through r as application’s backend code something like as below

# library(odbc)
# library(shiny)
#  con <- dbConnect(odbc::odbc(),"hive_connection")

# query_1 <-  "select a.state,a.gender,sum(a.spend) as total_spend
# from table_a a
# where a.state in ('Texas') and a.gender in ('F','M')
# group by a.state,a.gender"
#
# df <- dbGetQuery(con,query_1)
}
shinyApp(ui = ui, server = server)

But My data is in hive tables on top of HDFS and I want to construct the SQL query dynamically using the user input in r shiny and then building the ‘where’ clause and group by clause dynamically and the aggregating the spend.

The query will be passed through r as application’s backend code something like as below

 library(odbc)
 library(shiny)

 con <- dbConnect(odbc::odbc(),"hive_connection")
 
  query_1 <-  "select a.state,a.gender,sum(a.spend) as total_spend
  from table_a a
  where a.state in ('Texas') and a.gender in ('F','M')
  group by a.state,a.gender"

 df <- dbGetQuery(con,query_1)