@Jame Sure. SET (without VAR[IABLE]) refers to Hive variables and configs. And ${var.xxxx} is used to reference hive variables/configs.
They difference between hive variables and session variables is two fold:
Session variables are explicitly declared, they do not appear just by setting them. That has the benefit that a silly typo won't go unnoticed. And you also get to define the type.
The ${..} notation results in a macro substitution. That is the "text" of the hive variable is substituted into the SQL query string prior to parsing. On one hand this allows great liberty on composing SQL statements (I'll be writing about EXECUTE IMMEDIATELY very shortly). On the other hand it really invites SQL injection attacks. A session variable cannot modify the SQL statement (outside of EXECUTE IMMEDIATE and IDENTIFIER()), it can only substitute in a value, similar to a parameter marker (`?` or `:parm`)
The TL;DR is that session variables should be better on all fronts.
I dream of a day where we can retire hive variable so we are not forced into the SET VAR syntax for variables....
