Hive SQL学习整理

=Start=

缘由:

最近用Hive SQL查数据比较多,虽然HQL大体上和常用的SQL没什么区别,但是也有一些特别的地方需要注意,在此整理一下,方便要用的时候参考。

正文:

参考解答:
1、不等于

2、对于NULL列的处理

3、两表联合查询

4、多表联合查询

5、模糊匹配

6、当前时间&将秒格式的时间戳按特定格式展示

7、将毫秒格式的时间戳转换成可读的日期格式

8、如何检查逗号分隔的字符串中是否包含某个字符串?

9、如何判断某个元素是否在一个array中?

10、字符串连接函数

 

参考链接:

=END=

声明: 除非注明,ixyzero.com文章均为原创,转载请以链接形式标明本文地址,谢谢!
https://ixyzero.com/blog/archives/4245.html

《Hive SQL学习整理》上有13条评论

  1. SQL中的Case When Else End的用法
    https://blog.csdn.net/ruidongliu/article/details/11735507

    update操作:
    update table_name
    set 字段1=case
    when 条件1 then 值1
    when 条件2 then 值2
    else 值3
    end
    where ...

    select操作:
    select 字段1, 字段2,
    case 字段3
    when 值1 then 新值
    when 值2 then 新值
    end as 重新命名字段3的名字
    from table_name
    where ...
    order by ...

    how to write case and group by in hive query
    https://stackoverflow.com/questions/37440828/how-to-write-case-and-group-by-in-hive-query

  2. hive 正则表达式详解
    https://blog.csdn.net/bitcarmanlee/article/details/51106726

    regular expression in hive
    https://stackoverflow.com/questions/46060475/regular-expression-in-hive
    https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-RelationalOperators

    Hive SQL中regexp/regexp_extract的简单使用样例(主要是为了说明需要用「\\」表示常规的「\」)

    select 13112349876 regexp '\\d{11}'
    -- true

    select 13112349876 regexp '\d{11}'
    -- false

    select 'big-corporation' regexp '^big-', 'big-corporation' regexp '^big-inc', 'big-corporation' regexp '(.+?)corp(.+?)'
    -- true false true

    select 'big corporation' regexp '^abc|^btw|corp$'
    -- false

    select 'big corp' regexp '^abc|^btw|corp$'
    -- true

    select regexp_extract('{"bssid":"12:34:56:78:c4:90","ssid":"wifi-guest"}', 'bssid\\":\\"(.*?)\\"')
    -- 12:34:56:78:c4:90

  3. Hive 临时表
    https://blog.csdn.net/opensure/article/details/51378754

    Hive从0.14.0开始提供创建临时表的功能,表只对当前session有效,session退出后,表自动删除。

    语法:
    CREATE TEMPORARY TABLE TABLE_NAME_HERE (key string, value string)

    注意点:
    1、如果创建的临时表表名已存在,那么当前session引用到该表名时实际用的是临时表,只有drop或rename临时表名才能使用原始表;
    2、临时表限制:不支持分区字段和创建索引。

    Create temporary table in Hive?
    https://stackoverflow.com/questions/5385163/create-temporary-table-in-hive
    https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-TemporaryTables

    hive 学习系列三(表格的创建create-table)
    https://blog.csdn.net/eases_stone/article/details/80607109

  4. hive:条件判断函数
    https://www.cnblogs.com/kxdblog/p/4034243.html

    • If 函数 : if
    语法: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
    返回值: T
    说明: 当条件testCondition为TRUE时,返回valueTrue;否则返回valueFalseOrNull

    hive> select if(1=2,100,200);
    200
    hive> select if(1=1,100,200);
    100

    • 非空查找函数 : COALESCE
    语法: COALESCE(T v1, T v2, …)
    返回值: T
    说明: 返回参数中的第一个非空值;如果所有值都为NULL,那么返回NULL

    hive> select COALESCE(null,'100','50');
    100

    • 条件判断函数: CASE
    语法 : CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
    返回值 : T
    说明:如果 a 等于 b ,那么返回 c ;如果 a 等于 d ,那么返回 e ;否则返回 f

    hive> select case 100
    when 50 then 'tom'
    when 100 then 'mary'
    else 'tim'
    end;
    mary

  5. Hive SQL中如何获取当前时间?
    https://stackoverflow.com/questions/17905873/how-to-select-current-date-in-hive-sql

    select unix_timestamp(), -- 1550666245
    from_unixtime(unix_timestamp()), -- 2019-02-20 20:37:25
    to_date(from_unixtime(unix_timestamp())), -- 2019-02-20
    CURRENT_DATE, -- 2019-02-20
    YEAR(CURRENT_DATE()), -- 2019
    CURRENT_TIMESTAMP -- 2019-02-20 20:37:24.641
    ;
    -- 1550666245 2019-02-20 20:37:25 2019-02-20 2019-02-20 2019 2019-02-20 20:37:24.641

    select from_unixtime(1550666245,'yyyyMMdd'); -- 20190220

  6. CONCAT_WS()函数对于参数类型的限制
    Argument 2 of function CONCAT_WS must be “string or array”, but “bigint” was found.

    # 解决办法一,使用 concat 一点点拼接
    hive> select concat('www','.ixyzero','.com');
    www.ixyzero.com

    # 解决办法二,强制类型转换
    暂略

    http://www.thelandbeyondspreadsheets.com/how-to-join-strings-in-sql-for-hive/
    http://hadooptutorial.info/string-functions-in-hive/
    https://stackoverflow.com/questions/16009608/combine-columns-from-multiple-columns-into-one-in-hive
    https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-StringFunctions

  7. Hive数据类型转换
    https://www.iteblog.com/archives/892.html

    任何整数类型都可以隐式地转换成一个范围更大的类型。TINYINT,SMALLINT,INT,BIGINT,FLOAT和STRING都可以隐式地转换成DOUBLE;是的你没看出,STRING也可以隐式地转换成DOUBLE!但是你要记住,BOOLEAN类型不能转换为其他任何数据类型!

    Hive – Converting a string to bigint
    https://stackoverflow.com/questions/28867438/hive-converting-a-string-to-bigint

    SELECT CAST('00321' AS BIGINT);
    -- '321'

    Hive Converting from Double to String Not in Scientific
    https://stackoverflow.com/questions/32576187/hive-converting-from-double-to-string-not-in-scientific

    select 9999999902.0, cast(9999999902.0 as BIGINT), cast(cast(9999999902.0 as BIGINT) as string);
    -- 9.999999902E9 9999999902 9999999902

  8. https://stackoverflow.com/questions/21088865/error-message-tok-allcolref-is-not-supported-in-current-context-while-using-d

    报错信息:
    FAILED: SemanticException TOK_ALLCOLREF is not supported in current context

    Hive doesn't support DISTINCT * syntax. You can manually specify every field of the table to get the same result.
    错误原因:执行distinct *时报错,hive不支持。
    解决办法:手动写明每一个字段即可解决。

    https://blog.csdn.net/ltliyue/article/details/52292819
    https://stanzhai.site/blog/post/stanzhai/Hive%E6%89%A7%E8%A1%8CSQL%E6%8F%90%E7%A4%BASemanticException-TOK_ALLCOLREF-is-not-supported-in-current-context%E7%9A%84%E9%97%AE%E9%A2%98-2

  9. HiveSQL中的类型转换
    HiveSQL中如何将 字符串数组 转换成 整型数组 ? (Hive : casting array to array in query)
    https://stackoverflow.com/questions/32871399/hive-casting-arraystring-to-arrayint-in-query

    hive> select array('1','2','3') string_array;
    ["1","2","3"]

    hive> select explode(string_array) array_element --对字符串数组进行切分
    from (select array('1','2','3') string_array --初始化字符串数组
    )s
    1
    2
    3

    hive> select collect_list(cast(array_element as int)) int_array --cast and collect array
    from( select explode(string_array) array_element --explode array
    from (select array('1','2','3') string_array --initial array
    )s
    )s;
    [1,2,3]

    How to convert an array to array in Hive
    https://stackoverflow.com/questions/43462561/how-to-convert-an-arraydate-to-arraystring-in-hive

    select concat_ws(',',collect_set(date)) from table;

    Hive: Convert String to Integer
    https://stackoverflow.com/questions/12346750/hive-convert-string-to-integer
    https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-TypeConversionFunctions

    cast(str_column as int)

发表评论

电子邮件地址不会被公开。 必填项已用*标注