Hive

上传安装包

cd /export/softwares/

tar -zxvf apache-hive-2.1.1-bin.tar.gz -C ../servers

在线安装mysql

yum install mysql mysql-server mysql-devel

#启动mysql服务
/etc/init.d/mysqld start

#通过mysql安装自带脚本进行设置
/usr/bin/mysql_secure_installation

#进入mysql的客户端然后进行授权
grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;
flush privileges;

修改hive-env.sh

cd /export/servers/hive/conf
cp hive-env.sh.template hive-env.sh

HADOOP_HOME=/export/servers/hadoop-2.7.5
export HIVE_CONF_DIR=/export/servers/hive/conf

修改hive-site.xml

cd /export/servers/hive/conf
vim hive-site.xml

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>





      javax.jdo.option.ConnectionUserName
      root
  
  
      javax.jdo.option.ConnectionPassword
      123456
  
  
      javax.jdo.option.ConnectionURL
      jdbc:mysql://node01:3306/hive?
createDatabaseIfNotExist=true&useSSL=false


      javax.jdo.option.ConnectionDriverName
      com.mysql.jdbc.Driver
  
  
      hive.metastore.schema.verification
      false
  
  
    datanucleus.schema.autoCreateAll
    true

 

 

        hive.server2.thrift.bind.host
        node03
   

添加mysql的连接驱动包到hive的lib目录下

配置hive的环境变量

sudo vim /etc/profile

export HIVE_HOME=/export/servers/hive
export PATH=:$HIVE_HOME/bin:$PATH

第一种交互方式

bin/hive

不进入hive的客户端直接执行hive的hql语句

bin/hive -e "create database if not exists mytest;"

bin/hive -f /export/servers/hive.sql

创建数据库

create database if not exists myhive;

use  myhive;

create database myhive2 location '/myhive2';

删除数据库

drop  database  myhive  cascade;   

建表入门(内部表)

use myhive;create table stu(id int,name string);

insert into stu values (1,"zhangsan");  #插入数据

select * from stu;

创建表并指定字段之间的分隔符

create  table if not exists stu2(id int ,name string) row format delimited fields terminated by '\t';

创建表并指定表文件的存放路径

create  table if not exists stu2(id int ,name string) row format delimited fields terminated by '\t' location '/user/stu2';

根据查询结果创建表

create table stu3 as select * from stu2; # 通过复制表结构和表内容创建新表

根据已经存在的表结构创建表

create table stu4 like stu;

查询表的详细信息

desc formatted  stu2;

删除表

drop table stu4;

创建老师表

create external table teacher (t_id string,t_name string) row format delimited fields terminated by '\t';

创建学生表

create external table student (s_id string,s_name string,s_birth string , s_sex string ) row format delimited fields terminated by '\t';

加载数据

load data local inpath '/export/servers/hivedatas/student.csv' into table student;

加载数据并覆盖已有数据

load data local inpath '/export/servers/hivedatas/student.csv' overwrite  into table student;

从hdfs文件系统向表中加载数据（需要提前将数据上传到hdfs文件系统）

cd /export/servers/hivedatas

hdfs dfs -mkdir -p /hivedatas

hdfs dfs -put techer.csv /hivedatas/

load data inpath '/hivedatas/techer.csv' into table teacher;

创建分区表语法

create table score(s_id string,c_id string, s_score int) partitioned by (month string) row format delimited fields terminated by '\t';

创建一个表带多个分区

create table score2 (s_id string,c_id string, s_score int) partitioned by (year string,month string,day string) row format delimited fields terminated by '\t';

加载数据到分区表中

load data local inpath '/export/servers/hivedatas/score.csv' into table score partition (month='201806');

加载数据到多分区表中

load data local inpath '/export/servers/hivedatas/score.csv' into table score2 partition(year='2018',month='06',day='01');

多分区表联合查询(使用 union all )

select * from score where month = '201806' union all select * from score where month = '201806';

查看分区

show  partitions  score;

添加一个分区

alter table score add partition(month='201805');

删除分区

alter table score drop partition(month = '201806');

分区表综合练习

hdfs dfs -mkdir -p /scoredatas/month=201806

hdfs dfs -put score.csv /scoredatas/month=201806/

create external table score4(s_id string, c_id string,s_score int) partitioned by (month string) row format delimited fields terminated by '\t' location '/scoredatas';

msck  repair   table  score4;

分桶表操作

set hive.enforce.bucketing=trueset mapreduce.job.reduces=3

创建分桶表

create table course (c_id string,c_name string,t_id string) clustered by(c_id) into 3 buckets row format delimited fields terminated by '\t';

创建普通表

create table course_common (c_id string,c_name string,t_id string) row format delimited fields terminated by '\t';

普通表中加载数据

load data local inpath '/export/servers/hivedatas/course.csv' into table course_common;

通过insert overwrite给桶表中加载数据

insert overwrite table course select * from course_common cluster by(c_id);

重命名

alter  table  old_table_name  rename  to  new_table_name;

查询表结构

desc score5;

添加列

alter table score5 add columns (mycol string, mysco int);

更新列

alter table score5 change column mysco mysconew int;

hive调优

set hive.fetch.task.conversion=more;

set hive.exec.mode.local.auto=true;

set hive.auto.convert.join = trueset hive.mapjoin.smalltable.filesize=25123456   #(25M)

set hive.map.aggr = true;set hive.groupby.mapaggr.checkinterval = 100000;

set hive.groupby.skewindata = true;set hive.exec.dynamic.partition=true;

set hive.exec.dynamic.partition.mode=nonstrict;

set  hive.exec.max.dynamic.partitions=1000;

set hive.exec.max.dynamic.partitions.pernode=100;

set hive.exec.max.created.files=100000

set hive.error.on.empty.partition=false;

set hive.exec.parallel = true;

set hive.mapred.mode = strict;

set  mapred.job.reuse.jvm.num.tasks=10;

set mapred.map.tasks.speculative.execution=true

set mapred.reduce.tasks.speculative.execution=true

set hive.mapred.reduce.tasks.speculative.execution=true