Hive的基本操作

一、Hive初识

　　1.Hive的安装，前面的博客中已经介绍过，这里就不再赘述了

　　2.Hive的基本语法

　　　　查看Hive中有多少数据库：show  databases;

　　　　创建一个数据库：create  database  数据库名；

　　　　创建一个数据表：create  table  表名（字段   数据类型，字段   数据类型........）row  format  delimited  fields  terminated  by  ' ';

　　　　查看某个数据库中有多少表：先进入该数据库下，命令是 use  数据库名，然后，show   tables;

　　　　查看某个表的信息：desc   表名；

　　　　查看某个表的详细信息：desc  extended  表名；

　　　　格式化查看某个表的详细信息：desc  formatted  表名；

　　　　查看数据库中有哪些函数：show   functions;

　　　　查看数据库中某个函数：desc  function  函数名；

　　　　详细查看数据库中某个函数：desc   function   extended  函数名；

　　　　模糊查找某个数据库：show  databases  like  'db_hive*';

　　　　查看某个数据库的信息：desc   database  数据库名;

　　　　查看某个数据库的详细信息: desc  database  extended  数据库名；

　　　　删除空的数据库（里面已经没有数据表了）：drop   database  数据库名；

　　　　删除数据库：drop   database  数据库名   cascade;

　　　    标准删除数据库：drop  database  if   exists  数据库名；

　　　　清空表中的数据：truncate   table   表名；

　　　　创建一个与已有表结构相同的表：create  table  if  not  exists  新表名   like   旧表名；

　　　　给表重命名：alter   table   旧表名   rename   to   新表名；

　　　　标准删除表：drop  table  if   exists  表名；

　　　　创建一张表的时候，就直接将已有表的某些字段信息加载进表中：create   table   if  not   exists  数据库名.新表名   AS   select   字段1，字段2 .... from   数据库名.旧表名；

　　3.Hive的高级语法

　　　　bin/hive   -e   SQL语句

　　　　　　例如：bin/hive  -e   "select  * from  数据库名.表名；"

　　　　bin/hive   -f   SQL脚本

　　　　　　例如：touch  hivef.sql

　　　　　　　　　　　select  *  from  数据库名.表名

　　　　　　　　　bin/hive  -f  /opt/datas/hivef.sql

　　　　　　　　　bin/hive  -f  /opt/datas/hivef.sql  > /opt/datas/hivef-res.txt

　　　　在hive  cli命令窗口中如何查看hdfs文件系统

　　　　　　hive  （default）> dfs   -ls  / ;

　　　　在hive  cli 命令窗口中如何查看本地文件系统

　　　　　　hive  (default) > !ls  /opt/datas;

二、Hive的深入

　　1.创建一张表（默认是管理表），默认存储，并将本地的数据加载进表

　　　　格式：create  table  if  not   exists  数据库名.表名(

　　　　　　　　字段    数据类型，

　　　　　　　　字段   数据类型　　　　　　　　

　　　　　　　)

　　　　　　　row  format  delimited  fields  terminated  by  ' ';

　　　　　　　load  data  local  inpath  '/opt/datas/data.txt'  into  table   数据库名.表名;

　　2.创建一张外部表（托管表），默认存储，并将本体的数据加载进表

　　　　格式：create  external   table  if  not   exists  数据库名.表名(

　　　　　　　　字段    数据类型，

　　　　　　　　字段   数据类型　　　　　　　　

　　　　　　　)

　　　　　　　row  format  delimited  fields  terminated  by  ' '

　　　　　　　location   '/user/xunzhang/hive/warehouse/biaoming';

　　　　　　　dfs  -put   /opt/datas/data.txt    /user/xunzhang/hive/warehouse/;

　　　　注意：这里的location 后面的hdfs路径，一定要先创建出来，数据可以先上传，也可以后上传

　　3.创建一张分区表，默认存储，并将本地的数据加载进表

　　　　格式：create  external   table  if  not   exists  数据库名.表名(

　　　　　　　　字段    数据类型，

　　　　　　　　字段   数据类型　　　　　　　　

　　　　　　　)

　　　　　　　partitioned  by  ( mouth (字段一)   数据类型，day (字段二)   数据类型)

　　　　　　　row   format   delimited  fields  terminated   by  ' ';

　　　　　　　load   data  local  inpath  '/opt/datas/data.txt'  into  table   数据库名.表名  partition (字段一=...  ，字段二 =....  )；

　　　　注意：

　　　　　　查表之前：msck   repair  table  数据库名.表名  或者   alter   table  数据库名.表名   add   partition  (字段一=...  ，字段二 =....  ) ;

　　　　　　这时候查找就变成这样：

　　　　　　　　select   *   from   数据库名.表名   where   字段一  =  ...   and   字段二  =  ....;

　　　4、总结

　　　　　　将文件数据加载进Hive表中的几种方法　　

　　　　　　　　1>加载本地文件到hive表

　　　　　　　　　　load  data  local  inpath  '/opt/datas/data.txt'  (overwrite)  into  table  数据库名.表名；

　　　　　　　　2>加载hdfs文件到hive表中　　

　　　　　　　　　　load  data  inpath  '/opt/datas/data.txt'  (overwrite)  into  table  数据库名.表名；

　　　　　　　　　　或者

　　　　　　　　　　import   table   数据库名.表名    from   '/user/xunzhang/hive/data.txt';

　　　　　　　　3>创建表时，通过insert 加载

　　　　　　　　　　create   table  数据库名.新表名  like   数据库名.旧表名；

　　　　　　　　　　insert  into  table  数据库名.新表名   select  *  from   数据库名.旧表名；

　　　　　　　　4>如果是外部表，创建表的时候通过location指定加载

　　　　将hive表中的数据导出的几种方法

　　　　　　　　1>将hive表中的数据导到本地

　　　　　　　　　　insert  overwrite   local   directory  '/opt/datas/data.txt'

　　　　　　　　　　row  format  delimited  fields  terminated  by  ' '   collection  items  terminated  by  ' '

　　　　　　　　　　select   *   from   数据库名.表名；

　　　　　　　　　　或者

　　　　　　　　　　bin/hive   -e   "select  *  from   数据库名.表名；"   >  /opt/datas/data.txt

　　　　　　　　2>将hive表中的数据导到hdfs上　　　　　　　　

　　　　　　　　　　insert  overwrite    directory  '/user/xunzhang/hive/data.txt'

　　　　　　　　　　row  format  delimited  fields  terminated  by  ' '   collection  items  terminated  by  ' '

　　　　　　　　　　select   *   from   数据库名.表名；

　　　　　　　　　　或者

　　　　　　　　　　export   table   数据库名.表名   to   '/user/xunzhang/hive/data.txt';

三、Hive高级

　　1.hive表中数据的存储方式

　　　　常见有三种，分别是：orc  ,  parquet  ,  textfile  ,存储相同的数据，orc占的空间最少，但查询最慢，textfile占的空间最大，但查询最快

　　　　1>  textfile方式

　　　　　　create table page_views(
　　　　　　　　track_time string,
　　　　　　　　url string,
　　　　　　　　session_id string,
　　　　　　　　referer string,
　　　　　　　　ip string,
　　　　　　　　end_user_id string,
　　　　　　　　city_id string
　　　　　　)
　　　　　　ROW FORMAT DELIMITED FIELDS TERMINATED BY ' '
　　　　　　STORED AS TEXTFILE ;

　　　　　　load data local inpath '/opt/datas/page_views.data' into table page_views ;
　　　　　　dfs -du -h /user/hive/warehouse/page_views/ ;
　　　　　　18.1 M /user/hive/warehouse/page_views/page_views.data

　　　　2>  orc方式　

　　　　　　create table page_views_orc(
　　　　　　　　track_time string,
　　　　　　　　url string,
　　　　　　　　session_id string,
　　　　　　　　referer string,
　　　　　　　　ip string,
　　　　　　　　end_user_id string,
　　　　　　　　city_id string
　　　　　　)
　　　　　　ROW FORMAT DELIMITED FIELDS TERMINATED BY ' '
　　　　　　STORED AS orc ;

　　　　　　insert into table page_views_orc select * from page_views ;
　　　　　　dfs -du -h /user/hive/warehouse/page_views_orc/ ;
　　　　　　2.6 M /user/hive/warehouse/page_views_orc/000000_0

　　　　3>parquet方式

　　　　　　create table page_views_parquet(
　　　　　　　　track_time string,
　　　　　　　　url string,
　　　　　　　　session_id string,
　　　　　　　　referer string,
　　　　　　　　ip string,
　　　　　　　　end_user_id string,
　　　　　　　　city_id string
　　　　　　)
　　　　　　ROW FORMAT DELIMITED FIELDS TERMINATED BY ' '
　　　　　　STORED AS PARQUET ;

　　　　　　insert into table page_views_parquet select * from page_views ;
　　　　　　dfs -du -h /user/hive/warehouse/page_views_parquet/ ;
　　　　　　13.1 M /user/hive/warehouse/page_views_parquet/000000_0

　　2.hive表中的数据压缩存储

　　　　最常见的压缩方式是：snappy压缩，hadoop本身是不支持压缩的，如果要想压缩存储，需要重新编译源码，这里我不做过多的介绍，这里面比较复杂

　　　　1>snappy 压缩的三种方式

　　　　　　create table page_views_orc_snappy(
　　　　　　　　track_time string,
　　　　　　　　url string,
　　　　　　　　session_id string,
　　　　　　　　referer string,
　　　　　　　　ip string,
　　　　　　　　end_user_id string,
　　　　　　　　city_id string
　　　　　　)
　　　　　　ROW FORMAT DELIMITED FIELDS TERMINATED BY ' '
　　　　　　STORED AS orc tblproperties ("orc.compress"="SNAPPY");

　　　　　　insert into table page_views_orc_snappy select * from page_views ;
　　　　　　dfs -du -h /user/hive/warehouse/page_views_orc_snappy/ ;

　　　　　　或者

　　　　　　set parquet.compression=SNAPPY ;
　　　　　　create table page_views_parquet_snappy(
　　　　　　　　track_time string,
　　　　　　　　url string,
　　　　　　　　session_id string,
　　　　　　　　referer string,
　　　　　　　　ip string,
　　　　　　　　end_user_id string,
　　　　　　　　city_id string
　　　　　　)
　　　　　　ROW FORMAT DELIMITED FIELDS TERMINATED BY ' '
　　　　　　STORED AS parquet;
　　　　　　insert into table page_views_parquet_snappy select * from page_views ;
　　　　　　dfs -du -h /user/hive/warehouse/page_views_parquet_snappy/ ;

　　　　　　或者

　　　　　　set parquet.compression=SNAPPY ;
　　　　　　create table page_views_par_snappy
　　　　　　ROW FORMAT DELIMITED FIELDS TERMINATED BY ' '
　　　　　　STORED AS parquet
　　　　　　AS select * from page_views ;

　　　　　　dfs -du -h /user/hive/warehouse/page_views_par_snappy/ ;

　　3. udf 编程

　　　　1>将编写好的udf程序打成一个jar包,比如jar包是：hiveudf.jar

　　　　2>在hive  cli 命令行：

　　　　　　add   jar  /opt/datas/hiveudf.jar;

　　　　3>在hive  cli 命令行，创建临时函数

　　　　　 命令：

　　　　　　 　create temporary function 函数名(自己定义)    as   "com.beifeng.senior.hive.udf.RemoveQuotesUDF"（jar包中class文件路径） ;　　

　　　　4>使用临时函数，将处理好的数据重新加载进hive表

　　　　　命令：

　　　　　　　insert overwrite table default.bf_log_comm select my_removequotes(remote_addr), my_removequotes(time_local), my_removequotes(request), my_removequotes(http_referer) from  default.bf_log_src ;

　　　　5>检查是否成功

　　　　　命令：

　　　　　　　select * from bf_log_comm limit 5 ;

四、实战

　　1、数据下载链接： 　　　　

　　　　http://files.grouplens.org/datasets/movielens/ml-100k.zip

　　　　或者
　　　　http://files.grouplens.org/datasets/movielens/ml-100k.zip
　2.代码展示

　　　　

CREATE TABLE u_data_new (
  userid INT,
  movieid INT,
  rating INT,
  weekday INT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '	';

add FILE /opt/datas/ml-100k/weekday_mapper.py;

INSERT OVERWRITE TABLE u_data_new
SELECT
  TRANSFORM (userid, movieid, rating, unixtime)
  USING 'python weekday_mapper.py'
  AS (userid, movieid, rating, weekday)
FROM u_data;

SELECT weekday, COUNT(*) FROM u_data_new GROUP BY weekday;

SELECT weekday, COUNT(1) cnt FROM u_data_new GROUP BY weekday order by cnt desc;

//  weekday_mapper.py里面的内容如下：
import sys
import datetime

for line in sys.stdin:
  line = line.strip()
  userid, movieid, rating, unixtime = line.split('	')
  weekday = datetime.datetime.fromtimestamp(float(unixtime)).isoweekday()
  print '	'.join([userid, movieid, rating, str(weekday)])