hive笔记

HIVE笔记

启动hive和内置derby：hive --service hiveserver2

测试连接：

bin/beeline
!connect jdbc:hive2://192.168.70.128:10000/test

停止hive:hive>exit;

一些重要的配置

hive-site.xml

<property>
    <name>javax.jdo.option.ConnectionUserName</name>
    <value>root</value>
    <description>Username to use against metastore database</description>
</property>
<property>
    <name>javax.jdo.option.ConnectionPassword</name>
    <value>root</value>
    <description>password to use against metastore database</description>
</property>

数据库创建与删除

语法：CREATE TABLE IF NOT EXISTS user_db;
查看：SHOW DATABASES;
删除：DROP TABLE IF EXISTS user_db;

创建表

语法:

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]  
table_name (col_name data_type ['注释'],...)  
[COMMENT '表注释']  
[PARTITIONED BY(col_name,data_type,...)]
[ROW FORAT row_format DELIMITED FIELDS TERMINATED BY '\t'  LINES TERMINATED BY '\n']  
[STORED AS file_format];

示例

CREATE TABLE game  IF NOT EXISTS
(id int,game_name String)  
COMMENT '游戏表'  
ROW FORMAT DELIMITED  
FIELDS TERMINATED BY '\t'  
LINES TERMINATED BY '\n'  
STORED AS TEXTFILE;

修改表

修改表名：ALTER TABLE name RENAME TO new_name;
添加列：ALTER TABLE name ADD COLUMNS(col_name data_type,...);
删除列：ALTER TABLE name DROP COLUMN col_name;
修改列：ALTER TABLE CHANGE col_name new_name new_type;
替换列：ALTER TABLE REPLACE COLUMNS(col_name data_type new_col_name new_data_type,...);

删除表

语法：DROP TABLE [IF EXISTS] table_name

插入数据

insert：语法格式同传统数据库的格式一致
load data：一般用来存储大量数据，可用两种方式加载，一种是本地文件系统，另一种是从Hadoop文件系统
语法
1
2
LOAD DATA [LOCAL] INPATH 'file_path' [OVERWRITE] INTO TABLE table_name
[PARTITION(partcol1=val1, partcol2=val2 ...)];

注意：文件的存储格式需与表定义的格式保持一致

Hive 分区

添加分区

1 2	ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_sec [LOCATION 'location_path1'] partition_sec [LOCATION 'location_path2']

其中partition_sec为：(col_name=col_value,…)

也可以在创建表示添加PARTITIONED BY(col_name data_type,...)进行分区

示例

1	ALTER TABLE game ADD PARTITION (game_type='1') LOCATION '/game/types/1'

重命名分区

1	ALTER TABLE table_name PARTITION partition_sec RENAME TO PARTITION partition_sec2

示例

1	ALTER TABLE game PARTITION (game_type='1') RENAME TO (game_star='1')

删除分区

1	ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_sec,PARTITION partition_sec2,...

动态分区
需要提前设定是否支持动态分区，可以在配置文件中修改参数

hive> set hive.exec.dynamic.partition=true;
hive> set hive.exec.dynamic.partition.mode=nostrick; #允许全部动态分区,strick允许子分区动态，但主分区不允许
hive> set hive.exec.max.dynamic.partitions.pernode=100;#（缺省值100）每一个mapreducejob允许创建的分区的最大数量，如果超过了这个数量就会报错
hive> set hive.exec.max.dynamic.partitions=1000;#（缺省值1000）：一个dml语句允许创建的所有分区的最大数量
hive> set hive.exec.max.created.files=10000;#（缺省值100000）：所有的mapreduce job允许创建的文件的最大数量

示例

1 2	INSERT OVERWRITE TABLE partition_test PARTITION(stat_date,province) SELECT member_id,name,stat_date,province FROM partition_test_input DISTRIBUTE BY stat_date,province;

内置运算符

大部分同关系型数据库
A RLIKE B：有三个值。如果A或B为null,结果为null,如果A任何子字符串匹配到java正则表达式B，结果为true,否则为false。
A REGEXP B：等同RLIKE

NULL空值：不同字段类型对null的处理不一样，比如int通过is null，string通过 =‘’，使用时需要注意
A[n]:A是一个数组，n是一个整数，返回数组A的第n个元素，n从0开始
M[key]：M是一个Map并key的类型为K，返回对应于映射中关键字的值
S.x：S 是一个结构，返回S的s字段

内置函数

BIGINT round(double a)：返回BIGINT最近的double值
BIGINT floor(double a)：向下取整
BIGINT ceil(double a)：向上取整
double rand(),rand(int seed)：返回一个随机数，从行改变到行
sting concat(string a,stirng b)：返回字符串a+b
string substr(string a,int start)：返回一个字串，从start开始直到a结束
string substr(string a,int start,int length)：返回一个指定长度的字串，从start开始
string upper(string a)：返回大写
string ucase(string a)：同upper
string lower(stirng a)：返回小写
string lcase(string a)：同lower
string trim(string a)：去掉两端空格
string ltrim(string a)：去掉左侧空格
string rtrim(string a)：去掉右侧空格
string regexp_replace(string a,string reg,string replaceStr)：正则替换
int size(Map)：获取map的容量
int size(Array)：获取数组的长度
value cast(value as TypeB)：将value强制转换为类型TypeB,如果转换失败，返回null
string from_unixtime(int unixtime):返回指定unixtime对应的时间，格式”1970-01-01 00:00:00”
string to_date(string timestamp):返回日期部分
int year(string date)：年
int month(string date)：月，month(“1970-11-01 00:00:00”) = 11
int day(string date)：日，month(“1970-11-01 00:00:00”) = 1
string get_json_object(string json_string, string path)：从json字符串提取子json字符串
BIGINT count(*),count(expr):获取检索行数
double sum(col),sum(DISTINCT col)：列求和
double avg(col),avg(DISTINCT col)：列求平均值
double min(col)：取列最小值
double max(col)：取列最大值
视图

创建视图

1
2
3

CREATE VIEW [IF NOT EXISTS] view_name 
[(col_name [COMMENT '注释'],...)]
AS SELECT ...

示例

1	CREATE VIEW game_view AS SELECT * FROM game WHERE game_type='1';

删除视图：DROP VIEW view_name

整合hbase

版本： hadoop 2.7.3+hive 2.1.1+hbase 1.3.0+zookeeper 3.4.9

将${HIVE_HOME}/lib下hbase.jar用${HBASE_HOME}/lib同样的文件替换，删除低版本jar。替换zookeeper.jar为当前环境中安装的zookeeper版本

修改hive-site.xml

<property>
   <name>hive.aux.jars.path</name>
   <value>
   file:///usr/local/hadoop/thirdparty/hive/apache-hive-2.1.1/lib/hive-hbase-handler-2.1.1.jar,
   file:///usr/local/hadoop/thirdparty/hive/apache-hive-2.1.1/lib/guava-14.0.1.jar,
   file:///usr/local/hadoop/thirdparty/hbase-1.3.0/lib/hbase-common-1.1.9.jar,
   file:///usr/local/hadoop/thirdparty/hbase-1.3.0/lib/hbase-client-1.1.9.jar,
   file:///usr/local/hadoop/thirdparty/hbase-1.3.0/lib/hbase-server-1.1.9.jar,
   file:///usr/local/hadoop/thirdparty/hbase-1.3.0/lib/hbase-hadoop2-compat-1.1.9.jar,
   file:///usr/local/hadoop/thirdparty/hbase-1.3.0/lib/netty-all-4.0.23.Final.jar,
   file:///usr/local/hadoop/thirdparty/hbase-1.3.0/lib/hbase-protocol-1.1.9.jar,
   file:///usr/local/hadoop/thirdparty/zookeeper-3.4.9/zookeeper-3.4.9.jar
   </value>
   <description>The location of the plugin jars that contain implementations of user defined functions and serdes.</description>
</property>
<property>
    <name>hive.server2.enable.doAs</name>
    <value>false</value>
</property>

*value中的多个jar用,分隔，不要留空格和回车符

复制hadoop环境hosts文件中关于节点和zookeeper的相关内容映射到Java运行服务器的hosts文件中
在hbase中创建表
1
create 'game','info'

1、在hive中创建基于HBase的表

CREATE EXTERNAL table area2(rowkey string,info map<String,String>)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES("hbase.columns.mapping"=":key,info:")
TBLPROPERTIES("hbase.table.name" = "area2");

2、通过hive创建表

CREATE TABLE hive_hbase(key int, info map<String,String>)  
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'  
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,info:")  
TBLPROPERTIES ("hbase.table.name" = "hive_hbase");

使用外部表映射到HBase中的表，这样，在Hive中删除表，并不会删除HBase中的表，否则，就会删除。hbase删除，hive执行操作会报错。另外，除了rowkey，其他三个字段使用Map结构来保存HBase中的每一个列族。

hive 创建普通表会对应在hbase创建对应的表，如果hive删除表，hbase中的表也会删除，如果hbase删除，hive中表信息在，但执行操作时会报TableNotFoundException的错误

hive通过外部表的方式连接hbase表，hive删除表，hbase不会删除，hbase删除，hive执行操作会报错

两种方式创建的表，hive和hbase操作数据都能相互影响

在hive中，只能通过insert table select 原生表的方式插入数据，load data不行。如：
insert into hive_hbase select ‘1’ as rowkey,map(‘name’,’shot’)as info from temp limit 1;

忘语的个人博客