1、配置hive-site.xml文件,使用mysql作为metestore
2、Hive操作实例
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');