-1

It would be great if some one could help on below requirement. Hive Database Table

My requirement is pull the data from Hive table based on "Fiscal Quarter" and load it to txt file. Process should be like loop, i would expect 3 txt files (FY19Q1_Txtfile1.txt/FY19Q2_Txtfile2.txt/FY19Q3_Txtfile3.txt) with 3 iterations.

Koppula
  • 95
  • 7
  • You could may be provide us some example data, or some code explaining what you are trying to achieve. You question is difficult to understand right now. – Gowachin Feb 17 '20 at 15:04
  • 2
    A better question might be "Should I use a loop to do this?", to which most people would say: "No". – Edward Feb 17 '20 at 15:15
  • @Gowachin:- I hope attached sample data helps to explain my requirement ! – Koppula Feb 17 '20 at 15:35
  • Well it has been worse, but take a look at this : https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example. The point isn't to make us look at the data, but provide us a way to copy it easily, for experimenting with it. – Gowachin Feb 17 '20 at 15:46
  • 1
    @Gowachin:- My bad, going forward will make sure to provide the details to copy easily. – Koppula Feb 18 '20 at 03:46

1 Answers1

3

Once your table is stored as a data.frame on R, named data for example, you can do that :

write.csv(data[data$Fiscal_Quarter == 'FY19Q1'], 'FY19Q1_Txtfile1.txt')
write.csv(data[data$Fiscal_Quarter == 'FY19Q2'], 'FY19Q2_Txtfile3.txt')
write.csv(data[data$Fiscal_Quarter == 'FY19Q3'], 'FY19Q3_Txtfile3.txt')

And if you want to use a loop instead : 

for (i in 1:3){
    file_name = paste('FY19Q', i, '_Txtfile', i, '.txt',sep="")
    FQ = paste('FY19Q', i, sep="")
    write.csv(data[data$Fiscal_Quarter == FQ], file_name)
}

I hope this answers the question.

yoshi8585
  • 113
  • 9
  • :- Thanks for your efforts, your code helps me a lot. – Koppula Feb 18 '20 at 03:48
  • With help of your code finally i have implemented my requirement and it's working well. Below is my working script to pull the data from Hive database table and load to txt file. library(odbc) library(data.table) #Connecte to Hive Database by using ODBC driver con <- dbConnect(odbc(), "HiveODBCDNSName") #Retrive unique months from Table UniqueMonths <- dbSendQuery(con, "select distinct fct.partition_year_month from schemaName.tablename fct where fct.record_source = 'Sales' ;") Df_UniqueMonths <- dbFetch(UniqueMonths) – Koppula Feb 19 '20 at 14:51
  • #Loop the Months to Hive SQL query and genrate the extract for Month sqlQry <- "select * as CountofRecords from schemaName.tablename fct where fct.record_source = 'Sales' and fct.partition_year_month = '" Location <- "E:\\Software Repository\\R\\rProjects\\Data\\chnl_Sales_" for (sMonth in Df_UniqueMonths$fct.partition_year_month) { qry <- paste0(sqlQry, sMonth, "' ;") ExtractLocation <- paste0(Location,sMonth,".txt") MonthWiseQuery <- dbSendQuery(con, qry) MonthWiseData <- big.matrix(dbFetch(MonthWiseQuery)) fwrite(MonthWiseData,ExtractLocation,sep = "|",quote ="auto")} – Koppula Feb 19 '20 at 14:54