Hive / Impala best practice code structuring
The biggest help to me was learning about how MapReduce works on a distributed environment. I don't have exact rules of thumb for you, but I might be able to help.

In Hadoop/Hive, the hive.execution.engine definitely seems to matter. Make sure understand the differences and try changing it for certain problems.
Subqueries can actually HELP performance, especially with really wide tables, MapReduce as your execution engine, and Hive. For example, if you only want 2 or 3 columns from a 2nd table, its better to:

SELECT T1.*, T2.col1, T2.col2
FROM T1
LEFT OUTER JOIN (SELECT key, col1, col2 FROM T2) T2
ON T1.key = T2.key;

This is all due to how the execution engine works under-the-covers, so learning about that and the differences of the execution engines will really help.

Temp tables are your friend - prior to hadoop, I avoided staging data in temporary tables a lot and wrote more complex SQL to avoid it. But with Hadoop and big data, I've actually found it much faster to create tables and then subsequently joining to them helps in some cases with massive data.

Learn how the table's underlying data is stored. STORED AS TEXTFILE is a lot different than data stored as parquet

Impala vs Hive also has performance implications. I've found to find Hive more stable, but slower.

How the data is stored (skewed or not, bucketed or not, etc) makes a huge different. Keep an eye out for queries where you have 100 reducers and 99 of them finish super fast and 1 of them takes forever.


Like I mentioned, spend time learning how the execution engines do the work in a distributed environment. Once you understand how the execution happens behind the scenes, you can start to pick up on other subtleties like why this:
select count(distinct user)
from table;

can be a low slower than this:
select count(*) from
(select user
 from table
 group by user) q;