Impala 学习笔记

VALUES Statement | 6.3.x | Cloudera Documentation 

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