Hive

hive命令行操作

1.创建hive的数据库

$hive>hive --version // $hive>hive --help // $hive>create database mydb2 ; // $hive>show databases ; $hive>use mydb2 ; $hive>create table mydb2.t(id int,name string,age int); $hive>drop table t ; $hive>drop table mydb2.t ; $hive>select * from mydb2.t ; //查看指定库的表 $hive>exit ; //退出 $>hive //hive --service cli $>hive //hive --service cli

通过远程jdbc方式连接到hive数据仓库

1.启动hiveserver2服务器，监听端口10000 $>hive --service hiveserver2 & 2.通过beeline命令行连接到hiveserver2 $>beeline //进入beeline命令行(于hive --service beeline) $beeline>!help //查看帮助 $beeline>!quit //退出 $beeline>!connect jdbc:hive2://localhost:10000/mydb2//连接到hibve数据 $beeline>show databases ; $beeline>use mydb2 ; $beeline>show tables; //显式表

使用Hive-jdbc驱动程序采用jdbc方式访问远程数据仓库

1.创建java模块 2.引入maven 3.添加hive-jdbc依赖

<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.it18zhang</groupId> <artifactId>HiveDemo</artifactId> <version>1.0-SNAPSHOT</version> <dependencies> <dependency> <groupId>org.apache.hive</groupId> <artifactId>hive-jdbc</artifactId> <version>2.1.0</version> </dependency> </dependencies> </project> 4.App package com.hivedemo; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; /** * 使用jdbc方式连接到hive数据仓库，数据仓库需要开启hiveserver2服务。 */ public class App { public static void main(String[] args) throws Exception { Class.forName("org.apache.hive.jdbc.HiveDriver"); Connection conn = DriverManager.getConnection("jdbc:hive2://192.168.231.201:10000/mydb2"); Statement st = conn.createStatement(); ResultSet rs = st.executeQuery("select id , name ,age from t"); while(rs.next()){ System.out.println(rs.getInt(1) + "," + rs.getString(2)) ; } rs.close(); st.close(); conn.close(); } }

hive中表

1.managed table 托管表。 删除表时，数据也删除了。 2.external table 外部表。 删除表时，数据不删。 hive命令

//创建表,external 外部表 $hive>CREATE external TABLE IF NOT EXISTS t2(id int,name string,age int) COMMENT ‘xx’ ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’ STORED AS TEXTFILE ; //查看表数据 $hive>desc t2 ; $hive>desc formatted t2 ; //加载数据到hive表 $hive>load data local inpath ‘/home/centos/customers.txt’ into table t2 ; //local上传文件 $hive>load data inpath ‘/user/centos/customers.txt’ [overwrite] into table t2 ; //移动文件 //复制表 mysql>create table tt as select * from users ; //携带数据和表结构 mysql>create table tt like users ; //不带数据，只有表结构 hive>create table tt as select * from users ; hive>create table tt like users ;

//count()查询要转成mr

$hive>select count(*) from t2 ; $hive>select id,name from t2 ;

// $hive>select * from t2 order by id desc ; //MR

//启用/禁用表 $hive>ALTER TABLE t2 ENABLE NO_DROP; //不允许删除 $hive>ALTER TABLE t2 DISABLE NO_DROP; //允许删除

//分区表,优化手段之一，从目录的层面控制搜索数据的范围。 //创建分区表. $hive>CREATE TABLE t3(id int,name string,age int) PARTITIONED BY (Year INT, Month INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’ ; //显式表的分区信息 $hive>SHOW PARTITIONS t3; //添加分区,创建目录 $hive>alter table t3 add partition add PARTITION (year=2014, month=11) PARTITION (year=2014, month=12);

//删除分区 hive>ALTER TABLE employee_partitioned DROP IF EXISTS PARTITION (year=2014, month=11); //分区结构 hive>/user/hive/warehouse/mydb2.db/t3/year=2014/month=11 hive>/user/hive/warehouse/mydb2.db/t3/year=2014/month=12

//加载数据到分区表 hive>load data local inpath ‘/home/centos/customers.txt’ into table t3 partition(year=2014,month=11); //创建桶表 $hive>CREATE TABLE t4(id int,name string,age int) CLUSTERED BY (id) INTO 3 BUCKETS ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’ ;

//加载数据不会进行分桶操作 $hive>load data local inpath ‘/home/centos/customers.txt’ into table t4 ;

//查询t3表数据插入到t4中。 $hive>insert into t4 select id,name,age from t3 ; //桶表的数量如何设置? //评估数据量，保证每个桶的数据量block的2倍大小。

//连接查询 $hive>CREATE TABLE customers(id int,name string,age int) ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’ ; $hive>CREATE TABLE orders(id int,orderno string,price float,cid int) ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’ ; //加载数据到表 //内连接查询 hive>select a.,b. from customers a , orders b where a.id = b.cid ; //左外 hive>select a.,b. from customers a left outer join orders b on a.id = b.cid ; hive>select a.,b. from customers a right outer join orders b on a.id = b.cid ; hive>select a.,b. from customers a full outer join orders b on a.id = b.cid ; //explode,炸裂,表生成函数。 //使用hive实现单词统计 //1.建表 $hive>CREATE TABLE doc(line string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’ ;