hive 建表, shell的例子

2019独角兽企业重金招聘Python工程师标准>>>

CREATE TABLE reqstat_gif_result(

plat string,

version  string,

net   string,

productid  string,

gbcode   string,

objtype    string,

count    BIGINT,

average   int

)

PARTITIONED BY( time_type string ,time_value string,query_type string)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY '\t'

COLLECTION ITEMS TERMINATED BY ':'

LINES TERMINATED BY '\n'

STORED AS RCFILE; 

#!/bin/bash

. ~/.bash_profile

if [ $# -eq 0 ]

   then

        time_type=hour

        time_value=`date -d "-1 hour" +%Y%m%d%H`

elif [ $# -eq 1 ]

   then

        time_type=hour

        time_value=$1

elif [ $# -eq 2 ]

   then 

        time_type=$1

        time_value=$2

else

    echo "args is error."

    exit 1

fi

echo "timetype:${time_type}, timevalue:${time_value}"

array="dns connect request response download"

for query_type in $array;

do

echo "querytype : ${query_type}"

$HIVE_HOME/bin/hive -e "\

 set hive.exec.dynamic.partition=true;

 set mapred.reduce.tasks=8;

 set mapred.job.name=reqstat_gif_result_${time_type}_${time_value};

 insert overwrite table mid.reqstat_gif_result partition(time_type='${time_type}',time_value='${time_value}',query_type='${query_type}') select p,v,net,u,gbcode,objtype, count(*),round(avg(get_json_object(a.req_para,'$.${query_type}')))  from  event.reqstat_gif  a where p_hour='${time_value}'  group by p,v,gbcode,net,u,objtype;

 "

done;

转载于:https://my.oschina.net/u/1388024/blog/314838