数据仓库Hive从0到1

数据仓库Hive

一、Hive部署

• 下载地址：hive

1.1、上传至服务器、解压、配置环境变量

• 解压

  tar -zxvf apache-hive-1.1.0-bin.tar.gz -C /home/app/
  

• 配置环境变量

  vi ~/.bash_profile
  

  export HIVE_HOME=/home/app/apache-flume-1.7.0-bin
  export PATH=$HIVE_HOME/bin:$PATH
  

  source ~/.bash_profile
  

1.2、修改配置

• 进入conf目录

  cp hive-env.sh.template hive-env.sh
  

  vi hive-env.sh
  

  HADOOP_HOME=/home/app/hadoop-2.7.3/
  

1.3、创建hive-site-.xml

• 进入conf目录

  vi hive-site.xml
  

  <?xml version="1.0"?>
  <?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
  <configuration>
      <property>
          <name>javax.jdo.option.ConnectionURL</name>
          <value>jdbc:mysql://192.168.64.147:3306/hive?createDatabaseIfNotExsit=true</value>
      </property>
      <property>
          <name>javax.jdo.option.ConnectionDriverName</name>
          <value>com.mysql.jdbc.Driver</value>
      </property>
      <property>
          <name>javax.jdo.option.ConnectionUserName</name>
          <value>root</value>
      </property>
      <property>
          <name>javax.jdo.option.ConnectionPassword</name>
          <value>123456</value>
      </property>
  </configuration>
  

1.4、上传mysql的jar

• 上传mysql的jar到hive的lib目录下

1.5、启动hive

• 进入bin目录

  ./hive
  

二、hive快速入门

• hive创建数据库

  create database test_db;
  

• hive查看数据库列表

  show databases;
  

• 进入数据库

  use test_db;
  

• 查看表

  show tables;
  

• 创建表

  create table hello(id int, name string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
  

  ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'：表示读取文件以\t方式

• 读取文本数据保存数据到数据库表中

  load data local inpath '/home/app/hello.txt' overwrite into table hello;
  

  • hello.txt

    1	zhangsan
    2	lisi
    3	wangwu
    

三、Hive DDL之数据库操作

• 创建数据库

  create database hive;
  

• 创建数据库(存在就忽略不存在就创建)

  create database if not exists hive;
  

• 创建数据（指定位置）

  create database hive LOCATION '/test/location';
  

  /test/location是hdfs目录

• 查看数据库详细信息

  desc database test_db;
  desc database extended test_db;
  

• 删除空数据库

  drop database test_db;
  

• 删除数据库和库里面的所有表

  drop database test_db CASCADE;
  

• 模糊查询数据库

  show databases like 'hive*';
  

• 显示当前使用哪个数据库

  set hive.cli.print.current.db=true;
  

四、Hive DDL之表操作

• 创建表

  create table emp(
  empno int,
  ename string,
  job string,
  mgr int,
  hiredate string,
  sal double,
  comm double,
  deptno int
  ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
  

• 创建表（外部表）

  create external table emp_external (
  empno int,
  ename string,
  job string,
  mgr int,
  hiredate string,
  sal double,
  comm double,
  deptno int
  ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' location '/external/emp';
  

• 查询表数据

  select *from emp;
  

• 查看表详情

  desc formatted emp;
  

• 读取文本数据到表中

  load data local inpath '/home/app/emp.txt' overwrite into table emp;
  

• 更改表名

  alter table emp rename to emp2;
  

五、Hive DML之加载和导出数据

• 从本地写入数据到表中（先删掉表中已有数据）

  load data local inpath '/home/app/emp.txt' overwrite into table emp;
  

• 从hdfs中追加数据到表中

  load data inpath 'hdfs://192.168.64.147:9000/data/emp.txt' into table emp;
  

• 从sql中查询数据创建表

  create table emp1 as select * from emp;
  

• 将hive中数据写到本地

  insert overwrite local directory '/home/app/tmp/hive/' row format delimited fields terminated by '\t' select *from emp;
  

六、Hive QL基本统计

• 指定字段查询

  select ename,empno from emp;
  

• 指定查询条数

  select ename,empno from emp limit 3;
  

基本的查询语句和关系型数据库(Mysql )基本一样

七、Hive QL之聚合函数

• 求部门编号为10的个数

  slect count(1) from emp where deptno=10;
  

• 求员工最大工资，最小工资，平均工资和总工资为多少

  select max(sal), min(sal), sum(sal), avg(sal) from emp;
  

八、Hive QL之分组函数

• 求每个部分的平均工资

  select deptno, avg(sal) from emp group by deptno;
  

• 求每个部门、工作岗位的平均工资

select deptno, job, avg(sal) from emp group by deptno,job;

• 求每个部分的平均薪水大于2000的部门

select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal>900;

九、Hive QL之join的使用

• 创建表

  create table dept(
  deptno int,
  dname string,
  loc string
  ) row format delimited fields terminated by '\t'
  

• 将本地数据插入到表中

  load data local inpath '/home/app/dept.txt' overwrite into table dept;
  

• 表与表关联查询

  select *from emp e join dept d on e.deptno=d.deptno;
  

十、Hive QL执行计划

explain select *from emp e join dept d on e.deptno=d.deptno;