Github project : example-R-Query-Insert-From-Hive
rjdbc : https://cran.r-project.org/web/packages/RJDBC/index.html
This allows R to connect to any DBMS that has a JDBC driver.
Download the Hive JDBC drivers
drv <- JDBC(driverClass = "org.apache.hive.jdbc.HiveDriver", classPath = list.files("Hive_Jdbc_Folder_Path",pattern="jar$",full.names=T), identifier.quote="`") hiveConnectionUrl <- "jdbc:hive2://IP:Port/;ssl=false" conn <- dbConnect(drv, hiveConnectionUrl, "User_HDFS", "Password_HDFS") |
# All databases dbListTables(conn) # The database "default" dbGetQuery(conn, "show tables") |
# In database "default" d <- dbReadTable(conn, "table_name") # OR d <- dbGetQuery(conn, "select * from table_name") # Other that the database "default" 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)") |
# Retrieve Hive drivers for Debianoids from Cloudera if (!dir.exists("driver-hive")) dir.create("driver-hive") if (!length(Sys.glob("driver-hive/clouderahiveodbc_*.deb"))) download.file(paste0("https://downloads.cloudera.com/connectors/hive-2.5.25.1020/", "Debian/clouderahiveodbc_2.5.25.1020-2_amd64.deb"), destfile = "driver-hive/clouderahiveodbc_2.5.25.1020-2_amd64.deb") # Install driver system("sudo dpkg -i driver-hive/clouderahiveodbc_*.deb", intern = TRUE) # Setup driver and /etc/odbcinst.ini file system("sudo apt-get install -y odbcinst", intern = TRUE) system("sudo odbcinst -i -d -f /opt/cloudera/hiveodbc/Setup/odbcinst.ini", intern = TRUE) # Install ODBC system("sudo apt-get install -y unixodbc-dev", intern = TRUE) # Install R package for ODBC pkg <- "odbc" if (!length(find.package(pkg, quiet = TRUE))) install.packages(pkg, repos = "https://cloud.r-project.org") con <- DBI::dbConnect(odbc::odbc(), Driver = "Cloudera ODBC Driver for Apache Hive 64-bit", Host = "nn1", Port = "10000", Schema = "default", AuthMech = 3, UseSASL = 1, UID = "mysaagieidasfirstname.lastname", PWD = URLencode(Sys.getenv("mysaagiepassword"))) # Check connection (list tables in schema) DBI::dbGetQuery(con, "show tables") # Example data frame dat <- head(iris) # Make syntactically valid names (remove dots) names(dat) <- make.names(gsub("[._]", replacement = "", names(dat))) # Write to Hive (and possibly overwrite) DBI::dbSendQuery(con, "drop table if exists iris") DBI::dbWriteTable(con, "iris", dat) # Read back dat2 <- DBI::dbReadTable(con, "iris") # Or: dat2 <- DBI::dbGetQuery(con, "select * from iris") |