SELECT now()  as date_DES

 UNION ALL

 SELECT trunc(now(), 'month')

 union all

 select  date_sub(trunc(now(), 'month'), 1)

 union all

 select add_months( date_sub(trunc(now(), 'month'), 1),-12)

 union all

 select ''

  union all

  select add_months( date_sub(trunc(now(), 'month'), 1), 0)

     date_des

1    2023-01-11 09:04:53.304718000    now()

2    2023-01-01 00:00:00    trunc(now(), 'month') -- 获得月份首日

3    2022-12-31 00:00:00    date_sub(trunc(now(), 'month'), 1) --日期减1天

4    2021-12-31 00:00:00    add_months( date_sub(trunc(now(), 'month'), 1),-12) --R12,上个月的最后一天，减去12个月

5    NULL

6    2022-12-31 00:00:00    add_months( date_sub(trunc(now(), 'month'), 1), 0) --上个月的最后一天

从impala 3.0 开始，在group by、having和order by子句中的别名替换逻辑变得更加符合标准的行为，如下所示。
别名现在只在顶层合法，而不是在subexpressions中。

SELECT int_col / 2 AS x  FROM t  GROUP BY x;

SELECT int_col / 2 AS x  FROM t  ORDER BY x;

SELECT NOT bool_col AS nb  FROM t  GROUP BY nb  HAVING nb;

和以下语句是不允许的。

SELECT int_col / 2 AS x  FROM t  GROUP BY x / 2;

SELECT int_col / 2 AS x  FROM t  ORDER BY -x;

SELECT int_col / 2 AS x  FROM t  GROUP BY x HAVING x > 3

-----------------------------------

https://blog.51cto.com/u_15278282/2932154

> SELECT * FROM (VALUES(4,5,6),(7,8,9)) AS t;

+---+---+---+

| 4 | 5 | 6 |

+---+---+---+

| 4 | 5 | 6 |

| 7 | 8 | 9 |

+---+---+---+

> SELECT * FROM (VALUES(1 AS c1, true AS c2, 'abc' AS c3),(100,false,'xyz')) AS t;

+-----+-------+-----+

| c1  | c2    | c3  |

+-----+-------+-----+

| 1   | true  | abc |

| 100 | false | xyz |

+-----+-------+-----+

> VALUES (CAST('2019-01-01' AS TIMESTAMP)), ('2019-02-02');

+---------------------------------+

| cast('2019-01-01' as timestamp) |

+---------------------------------+

| 2019-01-01 00:00:00             |

| 2019-02-02 00:00:00             |

+------------------

From <https://docs.cloudera.com/documentation/enterprise/6/6.3/topics/impala_values.html#values>

select 'FOOBAR' ilike 'f%';

+---------------------+

| 'foobar' ilike 'f%' |

+---------------------+

| true                |

+---------------------+

select 'FOOBAR' like 'f%';

+--------------------+

| 'foobar' like 'f%' |

+--------------------+

| false              |

+--------------------+

select 'ABCXYZ' not ilike 'ab_xyz';

+-----------------------------+

| not 'abcxyz' ilike 'ab_xyz' |

+-----------------------------+

| false   

From <https://docs.cloudera.com/documentation/enterprise/6/6.3/topics/impala_operators.html#ilike> 

Solution 1

impala-shell> set var:id=123;select * from users where id=${VAR:id};

This variable can also be passed from command-line using --var

impala-shell --var id=123

impala-shell> select * from users where id=${VAR:id};

Solution 2

There's an open feature request for adding variable substitution support to impala-shell: IMPALA-1067, to mimic Hive's similar feature 
(hive --hivevar param=60 substitutes ${hivevar:param} inside a query with 60).

Variables that you can use in other SQL contexts (e.g. from a JDBC client) are not supported either, 

and I couldn't even find an open request for it... You might want to open a request for it: https://issues.cloudera.org/browse/IMPALA