常见hive语句

1、创建hive库

1 create database hive_oracle;

2、选择库

1use hive_oracle

3、创建数据库

1CREATE TABLE `hive_oracle.xcb_oracle_hive_end_dt`(
2  `xcb_id` bigint, 
3  `xcb_number` int, 
4  `xcb_timestamp` timestamp, 
5  `xcb_varchar2` string)
6PARTITIONED BY ( 
7  `ds` string)

4、简单查询

1select * from xcb_oracle_hive_base_dt;（原来有数据的一张表）

image-20211214154046316

5、时间函数

createTime: 2021-12-13T20:52:41+08:00
updateTime: 2021-12-13T20:52:41+08:00

1createTime: 2021-12-13T20:52:41+08:00
2updateTime: 2021-12-13T20:52:41+08:00

image-20211214154725488

  • from_unixtime (这个不行) ---- select from_unixtime(1323308943,’yyyyMMdd’);
1select xcb_id,from_unixtime(xcb_timestamp,'yyyyMMdd') from xcb_oracle_hive_base_dt;

  • unix_timestamp

    1select xcb_id,unix_timestamp(xcb_timestamp,'yyyyMMdd') from xcb_oracle_hive_base_dt;

    image-20211214155415436

  • year 返回日期中的年

    1select xcb_id,year(xcb_timestamp) from xcb_oracle_hive_base_dt;

    image-20211214155950842

  • month 返回日期中的月份

    1select xcb_id,month(xcb_timestamp) from xcb_oracle_hive_base_dt;

    image-20211214160221219

  • day：返回日期中的天

    1select xcb_id,day(xcb_timestamp) from xcb_oracle_hive_base_dt;

    image-20211214161011441

  • hour：返回日期中的小时

    1select xcb_id,hour(xcb_timestamp) from xcb_oracle_hive_base_dt;

    image-20211214161124084

  • minute：返回日期中的分钟

    1select xcb_id,minute(xcb_timestamp) from xcb_oracle_hive_base_dt;

    image-20211214161259839

  • second：返回日期中的秒

    1select xcb_id,second(xcb_timestamp) from xcb_oracle_hive_base_dt;

    image-20211214161401332

  • weekofyear：返回日期在当前周数

    1select xcb_id,weekofyear(xcb_timestamp) from xcb_oracle_hive_base_dt;

    image-20211214161525157

createTime: 2021-12-13T20:52:41+08:00
updateTime: 2021-12-13T20:52:41+08:00

1createTime: 2021-12-13T20:52:41+08:00
2updateTime: 2021-12-13T20:52:41+08:00

image-20211214161822514

createTime: 2021-12-13T20:52:41+08:00
updateTime: 2021-12-13T20:52:41+08:00

1createTime: 2021-12-13T20:52:41+08:00
2updateTime: 2021-12-13T20:52:41+08:00

image-20211214163739034

createTime: 2021-12-13T20:52:41+08:00
updateTime: 2021-12-13T20:52:41+08:00

1createTime: 2021-12-13T20:52:41+08:00
2updateTime: 2021-12-13T20:52:41+08:00

image-20211214170616062

  • 特殊组合

    1select xcb_id,from_unixtime(unix_timestamp(xcb_timestamp),'yyyy-MM-dd hh:mm:ss') from xcb_oracle_hive_base_dt;

    image-20211214171058188

6、特殊起飞

  • 复制一张表

    1INSERT INTO xcb_oracle_hive_end_dt partition(ds=2012) 
2select 
3t.xcb_id,t.xcb_number,from_unixtime(unix_timestamp(t.xcb_timestamp),'yyyy-MM-dd hh:mm:ss'),t.xcb_varchar2
4from xcb_oracle_hive_base_dt t;