HDInsight Ad-Hoc Query Solution
Is there an Azure data lake ad-hoc query story?
I work for a mid-sized federal agency. A year ago we were comparing the various data lake solutions provided by Azure and AWS. At the time our conclusion was Azure is great because of ease of use and general enterprise integration, but when it came to data lake/big data use cases it didn't have a good ad-hoc query solution. Hive on Tez is still Hive - it may be faster, but it's still quite slow relative to Impala and Presto. At the time I was dismayed by the lack of a valid solution. AWS has Athena that kicks HDInsight's tail. I'm querying billions of records in seconds. If that won't work then I can spin up an EMR cluster with Presto already installed. On the Azure side apparently I can hack Presto onto an HDInsight cluster, but I'm on my own if it breaks.
I'm revisiting this today because there's a large push from the infrastructure folks to implement on Azure. I can't help but think that I'm missing something despite a whole lot of looking. What's the story with Azure not having an ad-hoc SQL solution?
Thanks in advance for any help or pointers.
Posted May 31, 2018, 7:38 PM UTC
HDInsight ad-hoc query solution is "HDInsight Interactive Query" which is built on Hive LLAP (Low Latency and Analytical Processing).
More details: https://azure.microsoft.com/en-us/blog/general-availability-of-hdinsight-interactive-query-blazing-fast-data-warehouse-style-queries-on-hyper-scale-data-2/
How to use: https://docs.microsoft.com/en-us/azure/hdinsight/interactive-query/apache-interactive-query-get-started
Perfromance: https://azure.microsoft.com/en-us/blog/hdinsight-interactive-query-performance-benchmarks-and-integration-with-power-bi-direct-query/
Recent session at Microsoft BUILD https://blogs.msdn.microsoft.com/ashish/2018/05/11/interactive-ad-hoc-analysis-at-petabyte-scale-with-hdinsight-interactive-query/
You know, this was an excellent response. Thank you! It prodded me to go back and examine my configuration.
During previous tests I was running CSVs. Performance was atrocious. I even sat down with some MS folks and asked them why it was so bad. We chatted for a while, but they couldn't offer me much. In their defense, I'm pretty sure that I was talking with a new guy.
This time I moved some ORC files over from AWS and performance is much improved. Thanks for the kick in the pants.