基于Hadoop的数据仓库Hive 学习指南

  
   

    
cd ~/下载
    
sudo tar -zxvf ./apache-hive-1.2.1-bin.tar.gz -C /usr/local # 解压到/usr/local中
    
cd /usr/local/
    
sudo mv ./apache-hive-1.2.1-bin/ ./hive # 将文件夹名改为hive
    
sudo chown -R hadoop:hadoop ./hive # 修改文件权限
    
sudo mkdir -p /usr/local/hive/warehouse # 创建元数据存储文件夹
    
sudo chmod a+rwx /usr/local/hive/warehouse # 修改文件权限 
   

  
   

    
vim ~/.bashrc # 设置环境变量
   

  
   

    
source ~/.bashrc # 使配置立即生效
   

  
   

    
hive # 启动Hive
   

  
   

    
vim /usr/local/hive/conf/hive-site.xml 
   

  
   

    
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
    
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
    
<configuration>
    
<property>
    
 <name>hive.metastore.warehouse.dir</name>
    
 <value>/usr/local/hive/warehouse</value> 
    
 <description>location of default database for the warehouse</description>
    
 </property>
    
<property>
    
 <name>javax.jdo.option.ConnectionURL</name>
    
 <value>jdbc:derby:;databaseName=/usr/local/hive/metastore_db;create=true</value> 
    
 <description>JDBC connect string for a JDBC metastore</description>
    
 </property>
    
</configuration>
   

  
   

    
rpm -qa|grep mysql
   

  
   

    
sudo rpm -e --nodeps mysql-libs-xxxxxx 
   

  
   

    
sudo apt-get install mysql-server 
   

  
   

    
sudo service mysql start #启动mysql服务
    
sudo chkconfig mysql on #设置为开机自启动
    
sudo /usr/bin/mysqladmin -u root password '123' #设置root用户登录密码
    
mysql -u root -p #登录mysql
   

  
   

    
insert into mysql.user (Host,User,Password) values ("localhost","hive",password (" hive")); # 创建hive用户
    
create database hive; # 创建hive数据库
    
grant all on hive.* to hive@'%' identified by 'hive';
    
grant all on hive.* to hive@'localhost' identified by 'hive'; 
    
flush privileges;
    
exit #退出mysql
    
 
    
mysql -u hive -p hive #验证hive用户
    
 
    
show databases; 
   

  
   

    
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
    
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
    
<configuration>
    
<property>
    
 <name>hive.metastore.warehouse.dir</name>
    
 <value>/usr/local/hive/warehouse</value> 
    
 <description>location of default database for the warehouse</description>
    
 </property>
    
<property>
    
 <name>javax.jdo.option.ConnectionURL</name>
    
 <value>jdbc:mysql://localhost:3306/hive;createDatebaseIfNotExist=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.ConnectionPassword </name> 
    
 <value>hive </value> 
    
</property> 
    
 <property>
    
 <name>javax.jdo.option.ConnectionUserName</name>
    
 <value>hive</value>
    
 <description>Username to use against metastore database</description>
    
 </property>
    
</configuration>
   

  
   

    
mv ~/下载/mysql-connector-java-5.1.30-bin.jar /usr/local/hive /lib/ #拷贝mysql-connector-java-5.1.30-bin.jar 到hive 的lib下
    
cp /usr/local/hive/lib/jline-2.12.jar /usr/local/hadoop /share/ hadoop/yarn/lib # 把jline-2.12.jar拷贝到hadoop相应目录下
    
mkdir -p /usr/local/hive/tmp #创建hive临时文件夹
    
sudo chmod a+rwx /usr/local/hive/tmp # 修改文件权限 
   

    

     
show tables; #查看所有表名
     
show tables 'ad*' ; #查看以'ad'开头的表名
     
set 命令 #设置变量与查看变量
     
set –v; #查看所有的变量
     
set hive.stats.atomic; #查看hive.stats.atomic变量
     
set hive.stats.atomic=false; #设置hive.stats.atomic变量
     
dfs -ls ; #查看hadoop所有文件路径
     
dfs -ls /usr/local/hive/warehouse/; #查看hive所有文件
     
source file <filepath> #在client里执行一个hive脚本文件
     
quit; #退出交互式shell
     
exit #退出交互式shell 
     
reset #重置配置为默认值 
     
!ls #从Hive shell执行一个shell命令 
    

    

     
create database if not exists hive; #创建数据库
     
show databases; #查看Hive中包含数据库
     
show databases like 'h.*'; #查看Hive中以h开头数据库
     
describe databases; #查看hive数据库位置等信息
     
alter database hive set dbproperties; #为hive设置键值对属性
     
use hive; #切换到hive数据库下
     
drop database if exists hive; #删除不含表的数据库
     
drop database if exists hive cascade; #删除数据库和它中的表
    

    

     
#创建内部表（管理表）
     
create table if not exists hive.usr(
     
 name string comment 'username',
     
 pwd string comment 'password',
     
 address struct<street:string,city:string,state:string,zip:int>,
     
 comment 'home address',
     
 identify map<int,tinyint> comment 'number,sex') 
     
 comment 'description of the table' 
     
 tblproperties('creator'='me','time'='2016.1.1'); 
     
#创建外部表
     
create external table if not exists usr2(
     
 name string,
     
 pwd string,
     
 address struct<street:string,city:string,state:string,zip:int>,
     
 identify map<int,tinyint>) 
     
 row format delimited fields terminated by ','
     
 location '/usr/local/hive/warehouse/hive.db/usr'; 
     
#创建分区表
     
create table if not exists usr3(
     
 name string,
     
 pwd string,
     
 address struct<street:string,city:string,state:string,zip:int>,
     
 identify map<int,tinyint>) 
     
 partitioned by(city string,state string); 
     
#复制usr表的表模式 
     
create table if not exists hive.usr1 like hive.usr;
     
 
     
show tables in hive; 
     
show tables 'u.*'; #查看hive中以u开头的表
     
describe hive.usr; #查看usr表相关信息
     
alter table usr rename to custom; #重命名表
     
 
     
#为表增加一个分区
     
alter table usr2 add if not exists 
     
 partition(city=”beijing”,state=”China”) 
     
 location '/usr/local/hive/warehouse/usr2/China/beijing'; 
     
#修改分区路径
     
alter table usr2 partition(city=”beijing”,state=”China”)
     
 set location '/usr/local/hive/warehouse/usr2/CH/beijing';
     
#删除分区
     
alter table usr2 drop if exists partition(city=”beijing”,state=”China”)
     
#修改列信息
     
alter table usr change column pwd password string after address;
     
 
     
alter table usr add columns(hobby string); #增加列
     
alter table usr replace columns(uname string); #删除替换列
     
alter table usr set tblproperties('creator'='liming'); #修改表属性
     
alter table usr2 partition(city=”beijing”,state=”China”) #修改存储属性
     
set fileformat sequencefile; 
     
use hive; #切换到hive数据库下
     
drop table if exists usr1; #删除表
     
drop database if exists hive cascade; #删除数据库和它中的表
    

    

     
create view view_name as....; #创建视图
     
alter view view_name set tblproperties(…); #修改视图
    

    

     
#删除视图
     
drop view if exists view_name;
     
#创建索引
     
create index index_name on table table_name(partition_name/column_name) 
     
as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' with deferred rebuild....; 
    

    

     
alter index index_name on table table_name partition(...) rebulid; #重建索引
    

    

     
show formatted index on table_name; #显示索引
     
drop index if exists index_name on table table_name; #删除索引
    

    

     
add jar <jar文件的绝对路径>; #创建函数
     
create temporary function function_name;
     
drop temporary function if exists function_name; #删除函数
    

    

     
create table if not exists hive.stu(id int,name string) 
     
row format delimited fields terminated by '\t';
     
create table if not exists hive.course(cid int,sid int) 
     
row format delimited fields terminated by '\t';
    

    

     
load data local inpath '/usr/local/hadoop/examples/stu.txt' overwrite into table stu;
    

    

     
create table stu1 as select id,name from stu;
    

    

     
insert overwrite table stu1 select id,name from stu where（条件）;
    

  
   

    
hadoop fs -cp source_path target_path;
   

    

     
insert overwrite local directory '/usr/local/hadoop/tmp/stu' select id,name from stu;
    

    

     
select id,name,
     
 case 
     
 when id=1 then 'first' 
     
 when id=2 then 'second'
     
 else 'third'
    

    

     
select stu.*, course.* from stu join course on(stu .id=course .sid);
    

    

     
select stu.*, course.* from stu left outer join course on(stu .id=course .sid); 
    

    

     
select stu.*, course.* from stu right outer join course on(stu .id=course .sid); 
    

    

     
select stu.*, course.* from stu full outer join course on(stu .id=course .sid); 
    

    

     
select stu.* from stu left semi join course on(stu .id=course .sid); 
    

  
   

    
cd /usr/local/hadoop
    
mkdir input
   

  
   

    
cd /usr/local/hadoop/input
    
echo "hello world" > file1.txt
    
echo "hello hadoop" > file2.txt
   

  
   

    
cd ..
    
hadoop jar share/hadoop/mapreduce/hadoop-mapreduce-examples-2.4.1.jar wordcount input output
   

    

     
create table docs(line string);
     
load data inpath 'input' overwrite into table docs;
     
create table word_count as 
     
select word, count(1) as count from
     
(select explode(split(line,' '))as word from docs) w
     
group by word
     
order by word;