hive命令输出对齐 hive export命令

转载

编程小匠人之魂 2024-06-11 01:32:31

文章标签 hive命令输出对齐 hive mysql sql 文章分类 Hive 大数据

文章目录

Hive

基本概念
架构图
HIVE 搭建模式

单用户模式
多用户模式

Hive SQL

TIPS
hive 优化

Hive

官网 http://hive.apache.org/

基本概念

数据仓库（历史数据，用于计算分析）
解释器、编译器、优化器等（将sql转换为MR，提交给Hadoop集群）。
hive运行时，元数据（表、库、字段、属性）存储在关系型数据库（在线数据，交互式）里面，数据存储在HDFS里面

架构图

hive命令输出对齐 hive export命令_hive命令输出对齐

hive命令输出对齐 hive export命令_sql_02

HIVE 搭建模式

local (测试/但不推荐)Derby数据库（自带）
单用户模式 Mysql
多用户/远程服务器模式（MetaStore Server）

单用户模式

安装mysql
yum install mysql-server -y
启动服务
service mysqld start
设置开机启动
chkconfig mysqld on
查看开机启动列表
chkconfig mysql --list
通过mysql命令连接数据库
mysql 连接成功
登录权限控制
show databases;
use mysql;
show tables;
desc user;
select host, user, password from user;
GRANT ALL PRIVILEGES ON \*.* TO 'root'@'%' IDENTIFIED BY '123' WITH GRANT OPTION;delete from user where host != '%'; flush privileges;
quit;
mysql -u root -p 123 登录验证

hive 安装
下载链接 apache-hive-2.1.1-bin.tar.gz 下载链接mysql-connector-java-5.1.6.jar

1.上传安装包
	sz -e 
2.解压
	tzr -zxvf apache-hive-2.1.1-bin.tar.gz /usr/local/software/
3.配置环境变量
	vim /etc/profile
	export HIVE_HOME=/usr/local/software/apache-hive-2.1.1
	PATH=$HIVE_HOME/bin
	:wq
	source /etc/profile
4.配置文件修改
	cp hive-default.xml.template hive-site.xml
	vim hive-site.xml
	//删除默认的配置
	:.,$-1d
	
	//更改配置hive-site.xml
	<property>
		<name>hive.metastore.warehouse.dir</name>
		<value>/user/hive_remote/warehouse</value>
	</property>
		<property>
		<name>hive.metastore.local</name>
		<value>true</value>
	</property>
	<property>
	  <name>javax.jdo.option.ConnectionURL</name>
	  <value>jdbc:mysql://node1/hive</value>
	</property>
	<property>
	  <name>javax.jdo.option.ConnectionDriverName</name>
	  <value>com.mysql.jdbc.Driver</value>
	</property>
	<property>
	  <name>javax.jdo.option.ConnectionUserName</name>
	  <value>username</value>
	</property>
	<property>
	  <name>javax.jdo.option.ConnectionPassword</name>
	  <value>pwd</value>
	</property>

	//上传jar包 mysql-connector-java-5.1.6.jar
	/usr/local/software/apache-hive-2.1.1/lib

	//jline 需要更改hadoop 中jline 低版本为高版本
	//路径
	/usr/local/software/hadoop-2.6.0/share/hadoop/yarn/lib
	

	//启动验证
	hive

多用户模式

1.分发服务端到其他节点，然后更改配置
	<property>
	    <name>hive.metastore.local</name>
	    <value>false</value>
	</property>
	<property>
	    <name>hive.metastore.warehouse.dir</name>
	    <value>/user/hive_remote/warehouse</value>
	</property>
	<property>
	    <name>hive.metastore.uris</name>
	    <value>thrift://node1:9083</value>
	</property>

	2. 启动服务端
		hive --service metasore
		后台启动
		hive --service metasore & > hive.log
		客户端直接
		hive
		
		ss -nal 
		查看9083

Hive SQL

DDL

官方文档 http://hive.apache.org/

1. DESC  TBNAME;
2. DESC FORMATTED + TBNAME;
3. SHOW CREATE TABLE + TBNAME ;
4. hive 建表外部指定 Location（EXTERNAL TABLE）,删除表 (DROP TABLE + TBNAME) 只是删除了元数据，但数据并不删除。
5. 分区：PARTITIONED BY (col_name data_type [COMMENT col_comment], ...) 根据col_name 建不通的子目录进行管理。
	- 分区字段不能在建表的字段里面;
	- 分区字段太多、粒度太细不易维护;
6. 修改增加分区
ALTER TABLE page_view ADD PARTITION (dt='2008-08-08', country='us') location '/path/to/us/part080808'
                          PARTITION (dt='2008-08-09', country='us') location '/path/to/us/part080809';

7.设置动态分区
set hive.exec.dynamic.partition.mode=nonstrict;	

beeline> set hive.exec.dynamic.partition.mode=nonstrict;
beeline> FROM page_view_stg pvs
      INSERT OVERWRITE TABLE page_view PARTITION(dt, country)
             SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null, null, pvs.ip,
                    from_unixtimestamp(pvs.viewTime, 'yyyy-MM-dd') ds, pvs.country
             DISTRIBUTE BY ds, country;



8.行转列 可以用case a when 'b' then c end as ...

9.改变文件格式
ALTER TABLE tmp_prgdb.tf_credit_multidim_crm_month  SET FILEFORMAT orc;

DML

1.LOAD DATA LOCAL INPATH 'filepath' INTO TABLE tab1 partition(month='10',....);

TIPS

1.hive后台运行-->并将结果写入到本地文件 
> vim hive_shell.sh 
`hive [-S] -e "select * from tb_name where col_name = 'x' " > ./localresult`

> sh hive_shell.sh

2.hive后台运行 -->并将结果写入到本地文件2
> vim exer.sql
`select * from student where sex = '男';
 select count(*) from student;`
> hive -f exer.sql  > ./output.txt

3.hive后台运行 -->并将结果写入到本地文件3
	将结果写入到hdfs 中
	-[-S] 静默输出
	-e exec
	-i 执行完进入hive
	[hvie> source ***.sql 在hive客户端执行linux上的sql文件]
4.explain + sql 

5.UDF 开发
 - 继承UDF
 - 开发程序，打包上传到指定机器
 - hive> add jar /usr/.../udf.jar
 - create tmporary tmp_fun as 'com.***.***'
 - hive> select tmp_fun(col_) from tb_name;

6.加载数据
	insert into 会执行mr操作，慢
	load data [local] inpath '' into table 快，但是会将数据加载(移动)到同一目录
	from tb_name insert into ... 快

7.创建表(表结构) create table tb_name1 like tb_name;

8.分区：提高查询效率，将不同的数据文件存放到不同的目录，检索查询时可以查询部分目录

9.select explode(split(word,'')) from wc

10.分桶（适用于抽样）

11.内部表和外部表的区别
1） 创建表的时候内部表直接存储在默认的hdfs中。外部表建表的时候可以指定路径，也可以不指定。
2） 删除表的时候，内部表将数据和元数据都删除，外部表只删除元数据。

12.load data： 类型不匹配，都是null 
1） 读时检查：hive 实现解耦，提高数据加载效率
2） 写时检查：mysql（关系型数据库）

13. 写文件到本地
insert overwrite local directory '/root/result'
注： 慎重操作，千万不要写在 '/' ,这样会把'/' 目录下面的内容全部清空。

word count

hive命令输出对齐 hive export命令_hive命令输出对齐_03

hive 优化

1.核心思想： 将Hive Sql 当成MR 程序进行优化
 	tips：select不会转换成MR 程序执行
 		 where 仅对本表字段做条件过滤

2.Explain [Extended]+ sql 显示执行计划
	
3.参数
set hive.exec.local.auto=true; --开启本地计算

set hive.exec.parallel=true; --开启并行计算

set hive.exec.parallel.thread.number=10; -- 并行计算的数量

set hive.mapred.mode=true; --严格模式（生产环境不开启）
hive> hive.mapred.mode=nonstrict
	-- 对于分区表，必须添加where 对于分区字段的限制
	-- order by 语句必须包含limit 限制输出条数
	-- 限制执行笛卡尔积的查询
4. hvie join 
	join 计算时，将小标放在join的左边
	Map join: 在Map端完成jion，
		- 1.Sql方式：在Sql语句中添加MapJoin标记（mapjoin hint）
		语法：参考文章 [https://www.cnblogs.com/raymoc/p/5323824.html](https://www.cnblogs.com/raymoc/p/5323824.html)
		- 2.开启自动的MapJoin

5. Map-Side聚合：
	set hive.map.aggr=true;
	相关参数配置：
		hive.groupby.mapaggr.checkinterval;
		-map端聚合时处理的数据行数（默认：100000）
		hive.map.aggr.hash.min.reduction;
		-进行聚合的最小比例，（预先对100000行数据做聚合，如果聚合之后的数据/100000大于0.5，则不会聚合）
		hive.groupby.skewindata;
		- 是否对group by之后的数据倾斜做优化，默认false;

6. 控制hive中的M & R 的数量
	Map数量相关参数
		mapred.max.split.size
		mapred.max.split.size=256000000
		-- 一个split的最大值，即每个map处理文件的最大值
		set mapred.min.split.size.per.node=1;
		-- 一个节点上split的最小值
		set mapred.min.split.size.per.rack=1;
		-- 一个机架上split的最小值
	Reduce数量相关参数
		mapred.reduce.tasks=-1;
		-- 强制指定reduce任务的数量
		
7. JVM重用
	1）小文件过多
	2）task过多

	通过设置 set mapred.job.reuse.jvm.num.tasks=n （taks的插槽个数） 来设置
	
	缺点：插槽个数设置后，会一直占用资源，知道所有的tasks都执行完即整个job执行完才会释放

8. HIVE 执行结果保存到.csv文件
	hive -e 'select * from dwa_db.dwa_mss_contract_small_ceo_day where day_id = 20190422;' | sed 's/\t/,/g' > /home/hxxt_yx/yh/temp.csv
	#为避免乱码，执行以下语句
	iconv -f UTF-8 -c  -t GBK temp.csv > testbb.csv