[Hadoop] Hive - dual表的使用

快来打我* 2022-05-21 01:08 201阅读 0赞

## 创建dual表 ##

hive> create table dual;
    FAILED: SemanticException [Error 10043]: Either list of columns or a custom serializer should be specified
    hive> create table dual(x string);
    OK
    Time taken: 0.456 seconds
    hive> insert into table dual values('');
    Query ID = hadoop_20180627172525_87716209-d742-4554-97ae-5b467aaeea53
    Total jobs = 3
    Launching Job 1 out of 3
    Number of reduce tasks is set to 0 since there's no reduce operator
    Starting Job = job_1528733327581_0044, Tracking URL = http://hadoop01:8088/proxy/application_1528733327581_0044/
    Kill Command = /home/hadoop/app/hadoop-2.6.0-cdh5.7.0/bin/hadoop job  -kill job_1528733327581_0044
    Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
    2018-06-27 17:26:01,772 Stage-1 map = 0%,  reduce = 0%
    2018-06-27 17:26:07,043 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.88 sec
    MapReduce Total cumulative CPU time: 1 seconds 880 msec
    Ended Job = job_1528733327581_0044
    Stage-4 is selected by condition resolver.
    Stage-3 is filtered out by condition resolver.
    Stage-5 is filtered out by condition resolver.
    Moving data to: hdfs://10.132.37.38:9000/user/hive/warehouse/hive.db/dual/.hive-staging_hive_2018-06-27_17-25-52_962_790287460738495144-1/-ext-10000
    Loading data to table hive.dual
    Table hive.dual stats: [numFiles=1, numRows=1, totalSize=1, rawDataSize=0]
    MapReduce Jobs Launched: 
    Stage-Stage-1: Map: 1   Cumulative CPU: 1.88 sec   HDFS Read: 2928 HDFS Write: 66 SUCCESS
    Total MapReduce CPU Time Spent: 1 seconds 880 msec
    OK
    Time taken: 15.575 seconds
    hive> select * from dual;
    OK
    
    Time taken: 0.113 seconds, Fetched: 1 row(s)
    hive>

## 时间戳查询 ##

hive> select unix_timestamp() from dual;
    OK
    1530091605
    Time taken: 0.109 seconds, Fetched: 1 row(s)
    hive>

hive> select current_date from dual;
    OK
    2018-06-27
    Time taken: 0.074 seconds, Fetched: 1 row(s)
    hive>

hive> select current_timestamp from dual;
    OK
    2018-06-27 17:27:07.299
    Time taken: 0.221 seconds, Fetched: 1 row(s)
    hive>

hive> select year('2018-06-27 10:23:00') from dual;
    OK
    2018
    Time taken: 0.099 seconds, Fetched: 1 row(s)
    hive> select month('2018-06-27 10:23:00') from dual;
    OK
    6
    Time taken: 0.082 seconds, Fetched: 1 row(s)
    hive> select day('2018-06-27 10:23:00') from dual;
    OK
    27
    Time taken: 0.097 seconds, Fetched: 1 row(s)
    hive> select hour('2018-06-27 10:23:00') from dual;
    OK
    10
    Time taken: 0.102 seconds, Fetched: 1 row(s)
    hive> select minute('2018-06-27 10:23:00') from dual;
    OK
    23
    Time taken: 0.072 seconds, Fetched: 1 row(s)
    hive> select second('2018-06-27 10:23:00') from dual;
    OK
    0
    Time taken: 0.081 seconds, Fetched: 1 row(s)
    hive>

## 数据类型/格式转换 ##

hive> select cast('5' as int) from dual;   # string转成int
    OK
    5
    Time taken: 0.087 seconds, Fetched: 1 row(s)
    hive>

hive> select cast(current_timestamp as date) from dual;    # 时间戳转换成日期
    OK
    2018-06-27
    Time taken: 0.082 seconds, Fetched: 1 row(s)
    hive>

## 字符串截取 ##

hive> select substr('Facebook',5) from dual;   # substr函数用法查询：desc function extended substr;
    OK
    book
    Time taken: 0.064 seconds, Fetched: 1 row(s)
    hive> select substr('Facebook',-5) from dual;
    OK
    ebook
    Time taken: 0.077 seconds, Fetched: 1 row(s)
    hive> select substr('Facebook',5,1) from dual;
    OK
    b
    Time taken: 0.058 seconds, Fetched: 1 row(s)
    hive>

## 字符串长度 ##

hive> select length('10.0.0.8') from dual;
    OK
    8
    Time taken: 0.127 seconds, Fetched: 1 row(s)
    hive>

## 字符串拆分 ##

hive> select split('10.0.0.8','\\.') from dual;    # 分隔符要转义
    OK
    ["10","0","0","8"]                                  # 结果是列表          
    Time taken: 0.094 seconds, Fetched: 1 row(s)
    hive>