HIVE实例

1、配置hive-site.xml文件，使用mysql作为metestore

mv hive-default.xml.template hive-site.xml

       <property>
          <name>javax.jdo.option.ConnectionURL</name>
          <value>jdbc:mysql://localhost:3306/hive?createDatabaseIfNotExist=true</value>
          <description>JDBC connect string for a JDBC metastore</description>
        </property>

        <property>
          <name>javax.jdo.option.ConnectionDriverName</name>
          <value>com.mysql.jdbc.Driver</value>
          <description>Driver class name for a JDBC metastore</description>
        </property>

        <property>
          <name>javax.jdo.option.ConnectionUserName</name>
          <value>root</value>
          <description>username to use against metastore database</description>
        </property>

        <property>
          <name>javax.jdo.option.ConnectionPassword</name>
          <value>1</value>
          <description>password to use against metastore database</description>
        </property>

2、Hive操作实例

###创建内部表
create table user_info(id bigint, account string, name string, age int) row format delimited fields terminated by '\t';
create table trade_detail(id bigint, account string, income double, expenses double, time string) row format delimited fields terminated by '\t';

#导入数据
load data local inpath '/home/jzk/tmp/user_info' into table user_info;#从本地追加数据到表
load data inpath '/trade_detail' overwrite into table trade_detail;#从hdfs上导入数据到表（覆盖原先的表），并将hdfs上的数据删除

select t1.name,t1.account,t2.income,t2.expenses,t2.surplus from user_info t1 join (select sum(income) as income,sum(expenses) as expenses,sum(income-expenses) as surplus,account from trade_detail group by account) t2 on t1.account = t2.account;
该语句将转换成mapreduce过程

#创建外部表
create external table t_ext(id int,name string) row format delimited fields terminated by '\t' location '\ext';

#复杂类型的数据表
 create table complex(name string,work_locations array<string>) row format  delimited fields terminated by '\t' collection items terminated by ',';

数据内容
ngfengxiao      beijing,shanghai,tianjin,hangzhou
linan   changchu,chengdu,wuhan

select * from complex;
OK
ngfengxiao      ["beijing","shanghai","tianjin","hangzhou"]
linan   ["changchu","chengdu","wuhan"]

select name,work_locations[0] from complex;

###分区
create table classmem(student string,age int) partitioned by(teacher string);
分区文件
classmem_Misli
huangfengxiao   20
lijie   21
dongdong        21
liqiang 21
hemeng  21

classmem_MrXu
dingding        19
wangqiang       19
lidong  19
hexing  19

load data local inpath '/home/jzk/tmp/classmem_Misli' into table classmem  partition (teacher='Mis.li');
load data local inpath '/home/jzk/tmp/classmem_MrXu' into table classmem  partition (teacher='Mr.xu');