Hive基础操作

1.hive建表

1.1建内部表（文本）

create table if not exists test.imsiBackFill(
    s1apid string,
    enodebid string,
    xdrsize int,
    failed int,
    success int)
PARTITIONED BY(
    date_id string,
    hour string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|' 
LINES TERMINATED BY '\n' 
STORED AS TEXTFILE;

1.2 创建临时表

-- 按照enodebid聚合 生成临时表
CREATE TEMPORARY TABLE test.tmp_imsibackfill as 
    select a.enodebid as enodebid,
           sum(xdrsize) as totalXdrSize,
           sum(failed) as totalFailed,
           sum(success) as totalSuccess
      from test.imsiBackFill a
     where date_id = 20180920
       and hour = 10
     group by enodebid;

insert overwrite table test.tmp_imsibackfill
    select a.enodebid as enodebid,
           sum(xdrsize) as totalXdrSize,
           sum(failed) as totalFailed,
           sum(success) as totalSuccess
      from test.imsiBackFill a
     where date_id = 20180920
       and hour = 10
     group by enodebid;

 

2. 查

2.1 查看表结构

desc formatted test.imsibackfill;

2.2 查看建表语句

show create table test.imsiBackFill;

3.装载数据

3.1 从HDFS装载数据

load data inpath '/test/data/imsibackfill/*' overwrite into table test.imsiBackFill partition(date_id='20180920',hour='10');

4. 删除

4.1仅删除表中数据(会删除HDFS的文件)，保留表结构

truncate table test.imsiBackFill;

5.使用beeline 将sql结果导出为csv文件

beeline --outputformat=csv2 --showHeader=false --color=true -f "/resource/imsibackfill/select.hql" >/resource/imsibackfill/clusterEnode.txt

删除前面8行数据

sed -i '1,8d' clusterEnode.txt