Objective :
Simple Service to store Hadoop MapReduce result into Google Spread Sheet.Purpose :
Dynamicaly Hadoop MapReduce result should reflect in Web Application chart which referes the Google spread sheet.At Run time my Hadoop Mapreduce program result will be uploaded into Google spread sheet using this service.
My Blog's Current chart will use the Spread Sheet Data and display the result in my Blog without any changes in blog.
Flow:
Hadoop MapReduce Result -> Google Spread Sheet -> Google Chart -> Blog
Google Service:
Using Maven repository add google core jar file . I Used com.google.gdata.core.1.47.1.jar for this service class.
GSpreadSheetService.java
import java.io.IOException;
import java.net.*; import java.util.*; import com.google.gdata.client.spreadsheet.SpreadsheetService; import com.google.gdata.data.Link; import com.google.gdata.data.PlainTextConstruct; import com.google.gdata.data.batch.*; import com.google.gdata.data.spreadsheet.*; import com.google.gdata.util.*; public class GSpreadSheetService { private String user; private String password; private String application; private SpreadsheetService spreadsheetService; private SpreadsheetFeed spreadsheetFeed; private static final String SHEET_URL = "https://spreadsheets.google.com/feeds/spreadsheets/private/full"; public GSpreadSheetService(String app, String us, String pwd, String proxyHost, String proxyPort) { this(app, us, pwd); System.setProperty("https.proxyHost", proxyHost); System.setProperty("https.proxyPort", proxyPort); } public GSpreadSheetService(String app, String us, String pwd) { this.application = app; this.user = us; this.password = pwd; } private void initiate() throws AuthenticationException, MalformedURLException, IOException, ServiceException { spreadsheetService = new SpreadsheetService(application); spreadsheetService.setProtocolVersion(SpreadsheetService.Versions.V3); spreadsheetService.setUserCredentials(user, password); URL feedURL = new URL(SHEET_URL); spreadsheetFeed = spreadsheetService.getFeed(feedURL, SpreadsheetFeed.class); } public List<String> getAllWorkSheetsNames() { List<String> names = new ArrayList<String>(); try { if (spreadsheetService == null || spreadsheetFeed == null) initiate(); List<SpreadsheetEntry> spreadsheets = spreadsheetFeed.getEntries(); for (SpreadsheetEntry spreadsheet : spreadsheets) { names.add(spreadsheet.getTitle().getPlainText()); } } catch (Exception e) { e.printStackTrace(); } return names; } public boolean deleteWorkSheetInSpreadSheet(String spreadSheetName, String workSheetName) { try { if (spreadsheetService == null || spreadsheetFeed == null) initiate(); WorksheetEntry worksheet = getProperWorkSheet(spreadSheetName, workSheetName); if (worksheet != null) { worksheet.delete(); } return true; } catch (Exception e) { e.printStackTrace(); } return false; } public boolean createWorkSheetWithDataInSpreadSheet(String spreadSheetName, String workSheetName, String[] headers, String[][] rows) { int rowCount = 5; int columnCount = 4; try { if (spreadsheetService == null || spreadsheetFeed == null) initiate(); System.out.println("Objec Initialized"); SpreadsheetEntry spreadSheet = getProperSpreadsheet(spreadSheetName); rowCount = rows.length; columnCount = headers.length; WorksheetEntry worksheet = new WorksheetEntry(); worksheet.setTitle(new PlainTextConstruct(workSheetName)); worksheet.setColCount(columnCount); worksheet.setRowCount(rowCount); WorksheetEntry createdWorkSheet = getProperWorkSheet( spreadSheetName, workSheetName); if (createdWorkSheet == null) { URL worksheetFeedUrl = spreadSheet.getWorksheetFeedUrl(); createdWorkSheet = spreadsheetService.insert(worksheetFeedUrl, worksheet); System.out.println("Work Sheet created"); } if (createdWorkSheet != null) { WorksheetEntry searchedWorksheet = getProperWorkSheet( spreadSheetName, workSheetName); URL cellFeedUrl = searchedWorksheet.getCellFeedUrl(); CellFeed cellFeed = spreadsheetService.getFeed(cellFeedUrl, CellFeed.class); List<CellAddress> cellAddrs = new ArrayList<CellAddress>(); for (int col = 0; col < headers.length; ++col) { cellAddrs.add(new CellAddress(1, (1 + col), headers[col])); } Map<String, CellEntry> cellEntries = getCellEntryMap( spreadsheetService, cellFeedUrl, cellAddrs); System.out.println("Map constructed"); CellFeed batchRequest = new CellFeed(); for (CellAddress cellAddr : cellAddrs) { CellEntry batchEntry = new CellEntry( cellEntries.get(cellAddr.idString)); batchEntry.changeInputValueLocal(cellAddr.value); batchEntry.setImmutable(true); BatchUtils.setBatchId(batchEntry, cellAddr.idString); BatchUtils.setBatchOperationType(batchEntry, BatchOperationType.UPDATE); batchRequest.getEntries().add(batchEntry); } // Submit the update Link batchLink = cellFeed.getLink(Link.Rel.FEED_BATCH, Link.Type.ATOM); CellFeed batchResponse = spreadsheetService.batch(new URL( batchLink.getHref()), batchRequest); System.out.println("batch Submitted"); // Check the results boolean isSuccess = true; for (CellEntry entry : batchResponse.getEntries()) { String batchId = BatchUtils.getBatchId(entry); if (!BatchUtils.isSuccess(entry)) { isSuccess = false; BatchStatus status = BatchUtils.getBatchStatus(entry); System.out.printf("%s failed (%s) %s", batchId, status.getReason(), status.getContent()); } } System.out.println("Header Cell Insertion Completed"); URL listFeedUrl = searchedWorksheet.getListFeedUrl(); ListFeed listFeed = spreadsheetService.getFeed(listFeedUrl, ListFeed.class); for (int i = 0; i < rows.length; i++) { ListEntry row = new ListEntry(); for (int j = 0; j < rows[i].length; j++) { row.getCustomElements().setValueLocal(headers[j], rows[i][j]); } row = spreadsheetService.insert(listFeedUrl, row); System.out.println("Row Inserted"); } } return true; } catch (Exception e) { e.printStackTrace(); } return false; } public static Map<String, CellEntry> getCellEntryMap( SpreadsheetService ssSvc, URL cellFeedUrl, List<CellAddress> cellAddrs) throws IOException, ServiceException { CellFeed batchRequest = new CellFeed(); for (CellAddress cellId : cellAddrs) { CellEntry batchEntry = new CellEntry(cellId.row, cellId.col, cellId.idString); batchEntry.setId(String.format("%s/%s", cellFeedUrl.toString(), cellId.idString)); BatchUtils.setBatchId(batchEntry, cellId.idString); BatchUtils.setBatchOperationType(batchEntry, BatchOperationType.QUERY); batchRequest.getEntries().add(batchEntry); } CellFeed cellFeed = ssSvc.getFeed(cellFeedUrl, CellFeed.class); CellFeed queryBatchResponse = ssSvc.batch( new URL(cellFeed.getLink(Link.Rel.FEED_BATCH, Link.Type.ATOM) .getHref()), batchRequest); Map<String, CellEntry> cellEntryMap = new HashMap<String, CellEntry>( cellAddrs.size()); for (CellEntry entry : queryBatchResponse.getEntries()) { cellEntryMap.put(BatchUtils.getBatchId(entry), entry); // System.out.printf( "batch %s {CellEntry: id=%s editLink=%s inputValue=%s\n", // BatchUtils.getBatchId(entry), entry.getId(), entry.getEditLink().getHref(), entry.getCell() // .getInputValue()); } return cellEntryMap; } private WorksheetEntry getProperWorkSheet(String spreadSheetName, String workSheetName) { try { if (spreadsheetService == null || spreadsheetFeed == null) initiate(); SpreadsheetEntry spreadSheet = getProperSpreadsheet(spreadSheetName); WorksheetFeed worksheetFeed = spreadsheetService.getFeed( spreadSheet.getWorksheetFeedUrl(), WorksheetFeed.class); List<WorksheetEntry> worksheets = worksheetFeed.getEntries(); for (WorksheetEntry workSheet : worksheets) { if (workSheetName.trim().equalsIgnoreCase( workSheet.getTitle().getPlainText())) { return workSheet; } } } catch (Exception e) { e.printStackTrace(); } return null; } private static class CellAddress { public final int row; public final int col; public final String idString; public final String value; public CellAddress(int row, int col, String v) { this.row = row; this.col = col; this.idString = String.format("R%sC%s", row, col); this.value = v; } } private SpreadsheetEntry getProperSpreadsheet(String spreadSheetName) { try { if (spreadsheetService == null || spreadsheetFeed == null) initiate(); List<SpreadsheetEntry> spreadsheets = spreadsheetFeed.getEntries(); for (SpreadsheetEntry spreadSheet : spreadsheets) { if (spreadSheetName.trim().equalsIgnoreCase( spreadSheet.getTitle().getPlainText())) { return spreadSheet; } } } catch (Exception e) { e.printStackTrace(); } return null; } } |
Google Chart [Using GoogleSpreadSheet]
