@Mahipal Ramidi I assume you mean functions missing or having a different behavior documented here: https://github.com/Esri/spatial-framework-for-hadoop/wiki/ST_Geometry-in-Hive-versus-SQL. Their number is not that high. I think that is more pragmatic to either contribute to ESRI open source project (https://github.com/Esri/spatial-framework-for-hadoop) and maybe others will contribute too as such that everybody wins. Other option, is to write yourself the a few functions missing, or convince ESRI to add the missing functions. Adding another framework into the mix can complicate your implementation. You could have a small SQL Server database where these functions are available, pre-process the data before bring it to Hive and denormalize your tables to add those columns needed for missing functions. ... View more

@Mahipal Ramidi Best is to write a Java UDF for Hive, however, you can actually write WIDTH_BUCKET in SQL if you know your number of buckets and assume that is static for your histogram. Her is an example: SELECT whatever, CASE WHEN(whatever)>=${hiveconf:mymin} AND (t.tiv)<=${hiveconf:mymax} THEN CASEWHENfloor((whatever)/((${hiveconf:mymax}– ${hiveconf:mymin})/${ hiveconf:mybuckets} )+ 1)>${ hiveconf:mybuckets} THEN floor((t.tiv)/((${hiveconf:mymax}-${ hiveconf:mymin})/${ hiveconf:mybuckets})) ELSE floor((whatever)/((${hiveconf:mymax}- ${hiveconf:mymin})/${hiveconf:mybuckets})+1) END ELSE(${hiveconf:mybuckets})+1 END AS whateverlabel FROM(whatever table or sql) ... View more

Gopal and me gave a couple of tips in here to increase the parallelity ( since Hive is normally not tuned for cartesian joins and creates too few mappers ). https://community.hortonworks.com/questions/44749/hive-query-running-on-tez-contains-a-mapper-that-h.html#comment-45388 Apart from that my second point still holds you should create some pre-filtering to reduce the amount of points you need to compare. There are a ton of different ways to do this: https://en.wikipedia.org/wiki/Spatial_database#Spatial_index You can put points in grids and make sure that a data point in one grid entry cannot be closer to any point of the other grid entry than your max distance for example. ... View more

@Mahipal Ramidi As Sindhu suggested, you can write your UDF, specifically leveraging Java math library. NTILE divides ordered data set into number of buckets and assigns appropriate bucket number to each row. It can be used to divide rows into equal sets and assign a number to each row. WIDTH_BUCKET, while not far off from NTILE, here we can actually supply the range of values (start and end values), it takes the ranges and splits it into N groups. You can actually write WIDTH_BUCKET in SQL if you know your number of buckets and assume that is static for your histogram. Her is an example: SELECT whatever, CASE WHEN(whatever)>=${hiveconf:mymin} AND (t.tiv)<=${hiveconf:mymax} THEN CASEWHENfloor((whatever)/((${hiveconf:mymax}– ${hiveconf:mymin})/${ hiveconf:mybuckets} )+ 1)>${ hiveconf:mybuckets} THEN floor((t.tiv)/((${hiveconf:mymax}-${ hiveconf:mymin})/${ hiveconf:mybuckets})) ELSE floor((whatever)/((${hiveconf:mymax}- ${hiveconf:mymin})/${hiveconf:mybuckets})+1) END ELSE(${hiveconf:mybuckets})+1 END AS whateverlabel FROM(whatever table or sql) Even is not exactly WIDTH_BUCKET, https://developers.google.com/chart/interactive/docs/gallery/histogram provides a bucketing javascript function useful for histograms. Check the histograms section. Another good resource: https://developers.google.com/api-client-library/java/apis/analytics/v3 I believe that I knew a good Java library that had WIDTH_BUCKET among other analytical functions, but look at Google resources mentioned above. Most likely, you could leverage those and add yours, custom UDFs. ... View more

@Mahipal Ramidi Taking in account how ST_GeomFromText or St_GeomFromJson functions are used to convert text or json to geometry and then geometry is used in various functions, they provide the same functionality. If the value as text or json had to be parsed with some special conditions, then JSON would have been a better choice for processing, but that is not a known use case. As such, overall, used as explained above, text makes more sense, taking even less space (no structure included). If there will be a case where the above assumption is not true, add another column to your table, shape_json and convert text to json, or apply a text to json function for the specific scenario if performance is not impacted. If performance is impacted, denormalize by adding shape_json column. ... View more