搭建Hive

简介

hive是构建在hadoop hdfs上的一个数据仓库。

安装

安装hive之前需要先安装jdk和Hadoop。
hive有三种安装模式：内嵌模式、本地模式、远程模式。
内嵌模式 - 使用derby存储方式。
本地模式 - 在本地运行一个mysql服务器。
远程模式 - 在远端服务器运行一个mysql服务器。

• 内嵌模式

cd /opt
sudo wget http://mirrors.estointernet.in/apache/hive/hive-2.3.5/apache-hive-2.3.5-bin.tar.gz
sudo tar -zxvf apache-hive-2.3.5-bin.tar.gz
cd apache-hive-2.3.5-bin
sudo vim /etc/profile
#添加如下内容
export HIVE_HOME=/opt/apache-hive-2.3.5-bin
export PATH=$HIVE_HOME/bin:$PATH
#保存退出
source /etc/profile
schematool -dbType derby -initSchema
hadoop fs -mkdir       /tmp
hadoop fs -mkdir       /user/hive/warehouse
hadoop fs -chmod g+w   /tmp
hadoop fs -chmod g+w   /user/hive/warehouse

• 本地模式
• 远程模式

使用方式

hive三种管理方式：CLI，web界面，远程服务。

• CLI: bin/hive --service cli #进入命令行模式

show tables; #查看表列表
show functions; #查看内置函数列表
desc table_name;#查看表结构
dfs -ls dir_path; #查看hdfs上的文件
！linux_command;#在cli模式下执行linux命令
select *** from ***; #执行HQL语句
source sql_script_file_path; #执行sql脚本
hive -e 'hql_command'; #不进入命令行模式直接运行执行命令
hive -S;  #进入静默模式。就是没有MapReduce调试信息输出，直接输出结果

• web模式：hive --service hwi #开启web界面，通过http://ip:9999/hwi/进入界面
• hive --service hiveserver2 #开启远程模式

数据类型

• 基本类型：
  tinyint、smallint、int、bigint #整数类型
  float、double #浮点类型
  boolean #布尔类型
  string #字符串类型
• 复杂数据类型：
  array #数组类型，有一系列相同数据类型的元素组成的集合
  map #集合类型，key-value对，通过key访问value
  struct #结构类型，可以包含不同数据类型的元素，这些元素可以通过'点语法'的方式来得到所需要的元素
  date、timestamp #时间类型

表类型

table #内部表
partition #分区表
external table #外部表
bucket table #桶表

• 内部表

create table t1 
(id int, name string) 
location '/user/hive/warehouse/t2' #指定表存放在hdfs的目录
row format delimited fields terminated by ','; #定义字段分隔符为，

create table t2 
as
select * from copy_table; 使用子查询创建新表

• 分区表

create table partition_t1
(id int, name string)
partitioned by (dt string);
#查看分区
show partitions db_name.table_name;
分区表使用的分区字段不是在数据中存在的（比如创建了一个国家分区，但是数据中并没有这个字段），分区字段只是为了在HDFS上产生了对应的子目录。

• 外部表

create external table external_t1
(id int, name string)
row format delimited fields terminated by ','
location '/user/root/intput';#读取hdfs中input目录下所有文件(文件类型相同)并放到external_t1表中

• 桶表

create table bucket_t1
(id int, name string)
clustered by (name) into 5 buckets;#根据name哈希分桶，一共分五个桶

• 视图
  是一个逻辑概念

create view view_t1
...

数据导出

方式一：导出数据到本地
insert overwrite local directory '/you/path/dir'
row format delimited
fields terminated by '\t'
select * from test;
方式二：导出数据到hdfs
insert overwrite directory 'hdfs://host:8020/user/hive/warehouse/tableName/'
select * from dbName.tableName;
方式三：非交互模式
hive -e "select * from dbName.tableName;" > /home/caoxianle/tableName/
方式四：使用sqoop

数据导入

• load 命令 #把文件导入hive中

load data [local] inpath 'file_path' [overwrite] into table table_name [partition (partcol1=val1, partcol2=val2, ...)]

local：有local关键字表示从本地目录导入，没有就表示从hdfs目录导入
overwrite：表示覆盖该表中原有的数据
方式一：本地导入
load data local inpath '/you/local/path/' overwrite into table tableName partition (dt='yyyy-mm-dd')
方式二：hdfs导入
load data inpath 'hdfs/path/' overwrite into table tableName;
方式三：查询到入
create table tableName as select * from dbName.tableName;
方式四：insert查询导入
insert into/overwrite table tableName partition (dt='') (c1, c2,...) select c1, c2,... from tableName where p1='';

• sqoop #从jdbc/odbc导入数据到hive或者hdfs中

使用sqoop导入oracle数据到hdfs中
$SQOOP_HOME/bin/sqoop import --connect jdbc:oracle:thin:@ip:port:db_name --username user_name --password password --table table_name --columns 'col1,col2,...' -m 1 --target-dir '/user/sqoop/data'

-m 使用的MapReduce数
--target-dir 数据存放在hdfs中的目录
使用sqoop导入Oracle数据到hive中
$SQOOP_HOME/bin/sqoop import --hive-import --connect jdbc:oracle:thin:@ip:port:db_name --username user_name --password password --table table_name --columns 'col1,col2,...' -m 1 --hive-table hive_table_name --where '条件' --query '查询语句'

--hive-table 把数据导入到hive中的那张表中，没有该表则自动创建，不写该选项，会在hive中创建与jdbc中同名得表
--where 指定导入数据的条件，筛选数据。
--query 把查询语句得出的结果导入表中

• 使用sqoop把hive中的数据导出到Oracle中

$SQOOP_HOME/bin/sqoop export --connect jdbc:oracle:thin:@ip:port:db_name --username user_name --password password --table oracle_table_name --export-dir 'dir'

oracle数据表必须先创建，并且列数目和类型与hive中一致

数据查询

select [all | distinct] col1,col2,... from table_name [where where_condition] [group by col_name1,col_name2,...] [cluster by col_list | [distribute by col_list] [sort by col_list] | [order by col_list]] [limit number]

distribute by 指定分发器(partitioner)，多reducer可用

常用函数

• transform函数用法：
  transform中的值作为输入， 然后传递给python脚本，最后经过python的处理后，输出想要得到的字符串格式。

select transform(intput columns)
using 'python *.py'
as (output columns)

• nvl函数用法：
  NVL函数的格式如下：NVL(expr1,expr2)
  含义是：如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。

• lateral view用法：
  lateral view用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

一个简单的例子，假设我们有一张表pageAds，它有两列数据，第一列是pageid string，第二列是adid_list，即用逗号分隔的广告ID集合：

string pageid   Array<int> adid_list
"front_page"    [1, 2, 3]
"contact_page"  [3, 4, 5]
要统计所有广告ID在所有页面中出现的次数。

首先分拆广告ID：
SELECT pageid, adid 
    FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid;
执行结果如下：

string pageid   int adid
"front_page"    1
"front_page"    2
"front_page"    3
"contact_page"  3
"contact_page"  4
"contact_page"  5

默认记录和字段分隔符：

\n 每行一条记录

^A 分隔列（八进制 \001）

^B 分隔ARRAY或者STRUCT中的元素，或者MAP中多个键值对之间分隔（八进制 \002）

^C 分隔MAP中键值对的“键”和“值”（八进制 \003）

定义分隔符：
CREATE TABLE test(

    ……

)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY '\001'

COLLECTION ITEMS TERMINATED BY '\002'

MAP KEYS TERMINATED BY '\003'

LINES TERMINATED BY '\n'

• 常用函数：
  get_json_object() #一次只能解析一个字段
  regexp_extract()
  regexp_replace()
  json_tuple() #一次可以解析多个字段
  val()
  row_number() over()函数
  ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)

e.g. 
xxx = '[{"bssid":"6C:59:40:21:05:C4","ssid":"MERCURY_05C4"},{"bssid":"AC:9C:E4:04:EE:52","appid":"10003","ssid":"and-Business"}]'
regexp_extract('xxx','^\\[(.+)\\]$',1) 这里是把需要解析的json数组去除左右中括号，需要注意的是这里的中括号需要两个转义字符\\[。
regexp_replace('xxx','\\}\\,\\{', '\\}\\|\\|\\{') 把json数组的逗号分隔符变成两根竖线||，可以自定义分隔符只要不在json数组项出现就可以。
split(regexp_replace(regexp_extract('[{"bssid":"6C:59:40:21:05:C4","ssid":"MERCURY_05C4"},{"bssid":"AC:9C:E4:04:EE:52","appid":"10003","ssid":"and-Business"}]','^\\[(.+)\\]$',1),'\\}\\,\\{', '\\}\\|\\|\\{'),'\\|\\|') as str
json_tuple可以一次性解析多个字段，而get_json_object一次只能解析一个字段。
lateral view json_tuple(ss.col,'appid','ssid','bssid') rr as appid,ssid,bssid