LLAP has better SQL support than the older versions of Hive (the CLI is Hive 1.x and LLAP is Hive 2.x). ... View more

> I'd like to limit the allowed port range for LLAP. LLAP does not use the Slider port assignment scheme and instead pulls it from hive-site.xml (the interactive one) Here are the port numbers and configs you might want to whitelist. hive.llap.daemon.yarn.shuffle.port (15551) hive.llap.daemon.web.port (15002) hive.llap.daemon.rpc.port (0) The last rpc port is likely where you're seeing issues because it is unassigned by default. Also any tez changes you make need to go into tez_hive2/conf/tez-site.xml as well. ... View more

The cache, for sure (the math is the container * 0.8 == Xmx + Cache) I would recommend scaling down both Xmx and Cache by 0.8x, equally and picking 1 executor thread for your LLAP to avoid thrashing such a small cache. LLAP really shines when you give it (~4Gb per core) + enough cache to hold all the data needed for all executors in motion (not the total data size, but if you have 10 threads ... need at least 10 ORC stripes worth of cache). ... View more

@Peter Coates: There is no local download and upload (distcp does that, which is bad). This makes more sense if you think of S3 as a sharded key-value store (instead of a NAS). The filename is the key, so that whenever the key changes, the data moves from one shard to the other - the command will not return successfully until the KV store is done moving the data between those shards, which is a data operation and not a metadata operation - this can be pretty fast in some scenarios where the change of the key does not result in a shard change, In a FileSystem like HDFS, the block-ids of the data are independent of the name of the file - The name maps to an Inode and the Inode maps to the blocks. So the rename is entirely within metadata, due to the extra indirection of the Inode. ... View more

Your question seems to be that count(distinct Id) != count(id) ? ... View more

Can you do a "dfs -ls" on the output for Spark job? The total # of files might be very different between SparkSQL and Hive-Tez. ... View more

> A cartesian join. +1, right on. > still needs to do 2b computations but at least he doesn't need to shuffle 2b rows around. Even without any modifications, the shuffle won't move 2b rows, it will move exactly 3 aggregates per a.name because the map-side aggregation will fold that away into the sum(). > tez.grouping.max-size Rather than playing with the split-sizes which are fragile, you can however shuffle the 54,000 row set - the SORT BY can do that more predictably set hive.exec.reducers.bytes.per.reducer=4096; select sum() ... (select x.name, <gis-func>() from (select name, lon, lat from a sort by a.name) x, b) y; I tried it out with a sample query and it works more predictably this way. hive (tpcds_bin_partitioned_orc_200)> select count(1) from (select d_date, t_time_id from (select d_date from date_dim sort by d_date) d, time_dim) x; 6,311,433,600 Time taken: 20.412 seconds, Fetched: 1 row(s) hive (tpcds_bin_partitioned_orc_200)> ... View more

To add to @emaxwell > If I am only concerned with performance The bigger win is being able to skip decompressing blocks entirely - if you have hive.optimize.index.filter=true, that will kick in. > a few items in a where clause That's where the ORC indexes matter - if you have orc.create.index=true & orc.bloom.filter.columns contain those columns specifically (using "*" is easy, but slows down ETL when tables are wider and the measures are random) Clustering & Sorting on the most common column in the filter there can give you 2-3 magnitudes of performance (sorting specifically, because the min/max are stored at the footer of a file - this naturally happens for most ETL for date/timestamp columns, but for something randomly distributed like a location id, that is a big win). See for example, this ETL script https://github.com/t3rmin4t0r/all-airlines-data/blob/master/ddl/orc.sql#L78 ... View more

ORC is considering adding a faster decompression in 2016 - zstd (ZStandard). The enum values for that has already been reserved, but until we work through the trade-offs involved in ZStd - more on that sometime later this year. https://issues.apache.org/jira/browse/ORC-46 But bigger wins are in motion for ORC with LLAP, the in-memory format for LLAP isn't compressed at all - so it performs like ORC without compression overheads, while letting the cold data on disk sit around in Zlib. ... View more

floor(datediff(to_date(from_unixtime(unix_timestamp())), to_date(birthdate)) / 365.25 That unix_timestamp() could turn off a few optimizations in the planner, which might not be related to this issue. Start using CURRENT_DATE instead of the unix_timestamp() call, for faster queries. ... View more

Is there more information on this? There are 2 forms of logical predicate-pushdown and 2 forms of physical predicate-pushdown in Hive. ... View more

This looks awfully like an HDFS bug and entirely unrelated to Tez. The IndexOutOfBounds is thrown from HDFS block local readers. ... View more

Workarounds are specific to the actual problem, not a symptom like running out of memory. There are parts of the system which do use a lot of memory - the usual set of workarounds is to disable memory hungry features like map-joins, map-side hash aggregations. Alternatively, there are a few scalability features which reduce total memory required, but are disabled since they degrade performance on large RAM clusters (like dynamic partitioned insert optimizations). There are configuration issues which go unnoticed, like allocating 60% of a container as a single sort buffer. At the very least, I ask for people to submit a jmap hprof or a jmap -histo to be able to diagnose these. ... View more

The ideal rewrite for such a query is select * from latestposition where regionid='1d6a0be1-6366-4692-9597-ebd5cd0f01d1'and id=1422792010and deviceid='6c5d1a30-2331-448b-a726-a380d6b3a432' order by ts limit 1; is the table partitioned on ts? ... View more

Do you know which version you're running? Because the following configuration parameters don't seem to be related to hive+tez. set tez.task.resource.memory.mb=10000; Check your hive.tez.* parameters. ... View more

This is usually a symptom of the problem - general recommendation is to turn on hive.optimize.sort.dynamic.partition=true; to prevent partitioning+bucketing from blowing up HDFS file counts. ... View more