PageRenderTime 52ms CodeModel.GetById 25ms RepoModel.GetById 0ms app.codeStats 0ms

/Mosaic-Catalogue-Controllers/src/main/java/org/mosaic/catalogue/controllers/presto/PrestoController.java

https://bitbucket.org/gireeshp/data-catalog-team
Java | 160 lines | 117 code | 26 blank | 17 comment | 11 complexity | ffa26e7385cba7b125b43d834f8754c5 MD5 | raw file
  1. package org.mosaic.catalogue.controllers.presto;
  2. import java.sql.Connection;
  3. import java.sql.DriverManager;
  4. import java.sql.ResultSet;
  5. import java.sql.SQLException;
  6. import java.sql.Statement;
  7. import java.util.ArrayList;
  8. import java.util.HashMap;
  9. import java.util.HashSet;
  10. import java.util.List;
  11. import java.util.Map;
  12. import java.util.regex.Matcher;
  13. import java.util.regex.Pattern;
  14. import org.apache.commons.lang.StringUtils;
  15. import org.mosaic.catalogue.controllers.errors.AjaxErrorHandler;
  16. import org.springframework.beans.factory.annotation.Autowired;
  17. import org.springframework.stereotype.Controller;
  18. import org.springframework.web.bind.annotation.RequestBody;
  19. import org.springframework.web.bind.annotation.RequestMapping;
  20. import org.springframework.web.bind.annotation.RequestMethod;
  21. import org.springframework.web.bind.annotation.ResponseBody;
  22. import com.augmentiq.maxiq.base.dao.sql.operation.configuration.ObjectSerializationHandler;
  23. import com.augmentiq.maxiq.base.dao.sql.operation.configuration.exceptions.SystemException;
  24. import com.augmentiq.maxiq.cache.support.Cache;
  25. import com.augmentiq.maxiq.constant.cache.CacheConstants;
  26. import com.augmentiq.maxiq.core.dao.configuration.generic.genericdao.GenericStoreDao;
  27. import com.augmentiq.maxiq.entity.model.configuration.bean.DataSource;
  28. import com.augmentiq.maxiq.entity.model.configuration.bean.FieldMapping;
  29. import com.augmentiq.maxiq.model.connector.datanode.DataNode;
  30. import com.augmentiq.maxiq.services.presto.PrestoServices;
  31. import com.google.gson.Gson;
  32. @Controller
  33. public class PrestoController extends AjaxErrorHandler {
  34. @Autowired
  35. private PrestoServices prestoServices;
  36. @RequestMapping(value = "/runPrestoQuery", method = { RequestMethod.POST })
  37. public @ResponseBody List<FieldMapping> runPrestoQuery(@RequestBody String query) throws SystemException {
  38. HashSet<String> tableSet = new HashSet<String>();
  39. String from = "from\\s+(?:\\w+\\.)*(\\w+)($|\\s+[WHERE,JOIN,START\\s+WITH,ORDER\\s+BY,GROUP\\s+BY])";
  40. String join = "join\\s+(?:\\w+\\.)*(\\w+)($|\\s+[WHERE,JOIN,START\\s+WITH,ORDER\\s+BY,GROUP\\s+BY])";
  41. ArrayList<String> patternlist = new ArrayList<>();
  42. patternlist.add(from);
  43. patternlist.add(join);
  44. for (String pattern : patternlist) {
  45. Pattern p = Pattern.compile(pattern, Pattern.CASE_INSENSITIVE);
  46. Matcher m = p.matcher(query);
  47. while (m.find()) {
  48. tableSet.add(m.group(1));
  49. }
  50. }
  51. if (!tableSet.isEmpty()) {
  52. for (String tableName : tableSet) {
  53. String json = GenericStoreDao.getConfigurationByName(tableName);
  54. if (json != null) {
  55. String prestoTableAlias = null;
  56. DataSource dataSource = (DataSource) ObjectSerializationHandler
  57. .toObject(StringUtils.replace(json, "\\", "\\\\"), DataSource.class);
  58. prestoTableAlias = dataSource.getPrestoTableAlias();
  59. query = query.replaceAll(tableName, prestoTableAlias);
  60. query = query.replaceAll(prestoTableAlias + "_", tableName + "_");
  61. query = query.replaceAll("_" + prestoTableAlias, "_" + tableName);
  62. }
  63. }
  64. }
  65. Connection connection = null;
  66. List<FieldMapping> fieldMappingList = new ArrayList<FieldMapping>();
  67. // String query = "select
  68. // emp.employee_id,emp.dept_id,employee_name,dept.dept_name,dept.dept_head
  69. // from mysql.unitrax.employee emp INNER JOIN postgres.public.department
  70. // dept ON emp.dept_id = dept.dept_id";
  71. // query with mongo db
  72. // String query = "select
  73. // emp.employee_id,emp.dept_id,employee_name,dept.dept_name,dept.dept_head,sal.emp_salary
  74. // from mysql.unitrax.employee emp INNER JOIN postgres.public.department
  75. // dept ON emp.dept_id = dept.dept_id INNER JOIN
  76. // mongodb.datacatalog.salary sal ON sal.emp_id = emp.employee_id";
  77. Statement statement = null;
  78. try {
  79. Class.forName("com.facebook.presto.jdbc.PrestoDriver");
  80. connection = DriverManager.getConnection("jdbc:presto://localhost:9090/", "root", "");
  81. // "jdbc:presto://localhost:8080/mysql/tutorials", "tutorials", “");
  82. // connect mysql server tutorials database here
  83. statement = connection.createStatement();
  84. // String sql="select
  85. // emp.employee_id,emp.dept_id,employee_name,dept.dept_name,dept.dept_role
  86. // from mysql.unitrax.employee emp INNER JOIN
  87. // postgres.public.department dept ON emp.dept_id = dept.dept_id";
  88. // select mysql table author table two columns
  89. ResultSet columnresultSet = statement.executeQuery(query);
  90. ResultSet dataresultSet = statement.executeQuery(query);
  91. while (columnresultSet.next()) {
  92. int columnCount = columnresultSet.getMetaData().getColumnCount();
  93. for (int i = 1; i <= columnCount; i++) {
  94. FieldMapping feildmapping = new FieldMapping();
  95. feildmapping.setFieldName(columnresultSet.getMetaData().getColumnLabel(i));
  96. feildmapping.setPosition(Long.valueOf(i));
  97. fieldMappingList.add(feildmapping);
  98. }
  99. break;
  100. }
  101. while (dataresultSet.next()) {
  102. int count = 0;
  103. int columnCount = dataresultSet.getMetaData().getColumnCount();
  104. for (int i = 1; i <= columnCount; i++) {
  105. String rowLevelColumnValue = null;
  106. try {
  107. rowLevelColumnValue = dataresultSet.getString(i);
  108. if (rowLevelColumnValue.contains("\"")) {
  109. rowLevelColumnValue = rowLevelColumnValue.replaceAll("\"", "");
  110. }
  111. } catch (Exception e) {
  112. rowLevelColumnValue = null;
  113. }
  114. FieldMapping fieldMapping = fieldMappingList.get(i - 1);
  115. fieldMapping.getSampleRecords().add(count, rowLevelColumnValue);
  116. }
  117. count++;
  118. }
  119. columnresultSet.close();
  120. columnresultSet.close();
  121. statement.close();
  122. connection.close();
  123. } catch (SQLException sqlException) {
  124. sqlException.printStackTrace();
  125. } catch (Exception exception) {
  126. exception.printStackTrace();
  127. }
  128. return fieldMappingList;
  129. }
  130. @RequestMapping(value = "/getPublishedDS", method = { RequestMethod.GET, RequestMethod.POST })
  131. public @ResponseBody DataNode getPublishedDataSource() throws SystemException {
  132. return prestoServices.getPublishedDataSource();
  133. }
  134. }