hive中pivot函数 hive pivot

一、hive数据库操作

1，新建表

基本格式：

create table IF NOT EXISTS test.test_mx
(
 activity_code string COMMENT '活动编码',
 activity_name string COMMENT '活动名称',
 request_url string COMMENT 'client request url',
)
COMMENT 'Test access log'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ' '
location '/user/hadoop/warehouse/test';
STORED AS TEXTFILE ;

参数解释：

1，IF NOT EXISTS：判定表是否存在
2，COMMENT：给字段和表增加注释
3，ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'：定义文件中的每一行之间的分隔符，分隔符可以是其他字符，比如",","#","|"等，一般只要用数据文件中可以区分每一行中的不同数据即可。
4，LOCATION hdfs_path：指定该表映射到到hdfs的文件路径，默认是映射到/user/hive/warehouse目录下。
5，STORED AS TEXTFILE：指定文件的类型，保存在hive中的文件的类型有多种，一般简单就保存为TEXTFILE文本格式，企业中一般不使用这种格式来保存数据，主要是因为文本格式占的空间比较大，不利于大数据分析。企业中一般使用ORC和PARQUET两种文件类型来保存。
5，COLLECTION ITEMS TERMINATED BY '\n'：列与列直接的分隔符，通常是以换行符来区分，一般不需要写。

2，插入数据

（1）加载数据

load data local inpath '/home/hadoop/test/test_mx.txt' into table test.test_mx;

（2）insert插入数据

insert into test.test_mx(activity_code ,activity_name,request_url  ) values(6222345,'百度文选','www.baidu.com');

（3）insert–select

insert into test.test_mx
select activity_code,activity_name,request_url
from  test.test_mx_01

3，删除表与删除数据

（1）truncate

truncate table 表名

注意：truncate可删除所有的行，但是不能删除外部表

（2）shell命令

hdfs -dfs -rm -r 外部表路径

注意：shell命令删除外部表

（3）drop

drop table 表名

注意：drop 可删除整个表

二、sqoop工具的使用

1，使用sqoop新建表

sqoop create-hive-table \
--connect jdbc:mysql://IP地址:端口/数据库实例 \
--username 用户名 \
--password 密码 \
--table 数据库表 \
--hive-database hive数据库
--hive-table hive表名

主要参数解释：

create-hive-table ：创建表关键字。

2，sqoop数据导入

sqoop import -Dmapreduce.map.memory.mb=6000 -Dmapreduce.map.java.opts=-Xmx1600m -Dmapreduce.task.io.sort.mb=4800 \
--hive-import \
--connect jdbc:mysql://IP地址:端口/数据库实例?dontTrackOpenResources=true\&defaultFetchSize=10000\&useCursorFetch=true \
--driver com.mysql.jdbc.Driver \
--table 表名 \
--username name \
--password password  \
--hive-import \
--hive-database test \
--hive-table MscActivity \
--hive-overwrite -m 1 \
--compression-codec org.apache.hadoop.io.compress.SnappyCodec --null-string '\\N' --null-non-string '\\N'

主要参数解释：

1，import ：数据导入关键字
2，connect 数据库连接信息
3，driver 数据库驱动
4，table 数据库表
5，username 数据库用户名
6，password 数据库密码
7，hive-database hive数据库实例
8，hive-table hive数据表
9，hive-overwrite -m 任务个数
10，--null-string '\\N' --null-non-string '\\N'  ：sqoop遇到"\N"后按照null进行插入处理

3，sqoop数据导出

示例代码：

sqoop export \
--connect jdbc:mysql://IP地址:端口/数据库实例 \
--username 用户名 \
--password password  \
--table test_mx_0511 \
--export-dir /user/hive/warehouse/test.db/test_mx_0511/ \
--fields-terminated-by '\t' --input-null-string '\\N' --input-null-non-string '\\N'

主要参数解释：

1，export：数据导出关键字
2，export-dir 指定hive表在hdfs上面的路径
3，fields-terminated-by '\t' 指定分隔符

4，常见问题处理

（1）MySQL导入hive时，没有成功，但是文件已经存在

错误信息：Output directory hdfs://node01:8020/user/root/MscActivity already exists
错误描述：如果sqoop导入不成功的时候，每次MR都会生成一个output目录但是不能自动删除，所以我们就把HDFS的输出目录删除：
问题解决：
			hadoop fs -ls -R:查看目录
			hadoop fs -rmr 路径:删除输出目录

（2）数据导入发生错位

数据导入发生错位
错误描述：导入表的数据与源数据不一致，发生数据错位情况
问题解决：查看源数据与目标数据的分隔符与换行符是否一致。

（3）GC overhead limit exceeded

第一种：
错误信息：GC overhead limit exceeded
错误描述：代码的问题，和内存无关，代码中出现了大量占用内存的对象
解决思路：设置mysql的读取数据的方式，不要一次性将所有数据都fetch到内存
实施方案：在配置connect参数的时候加上 ?dontTrackOpenResources=true&defaultFetchSize=10000&useCursorFetch=true
案例：--connect jdbc:mysql://IP地址:3306/数据库实例?dontTrackOpenResources=true\&defaultFetchSize=10000\&useCursorFetch=true

第二种：
错误信息：GC overhead limit exceeded
错误描述：代码的问题，和内存无关，代码中出现了大量占用内存的对象
解决思路：增加mapreduce的内存配置
实施方案：sqoop import -Dmapreduce.map.memory.mb=6000 -Dmapreduce.map.java.opts=-Xmx1600m -Dmapreduce.task.io.sort.mb=4800 \

（4）sqoop数据导出时，格式不符

报错信息：ERROR tool.ExportTool: Error during export: 
		Export job failed!
问题描述：sqoop数据导入时，格式不符
解决方案：检查hive表与MySQL数据表的字段格式是否一致
		加上两条参数–input-null-string和–input-null-non-string，在sqoop遇到"\N"后按照null进行插入处理