hive和mysql查询性能优化_hive的配置和HQL的查询优化

hive属于一种类SQL数据库

配置信息

pwd =>/opt/bigdata/hadoop/hive110/conf

hive110/conf/hive-site.xml配置

hive.metastore.warehouse.dir

/opt/bigdata/hadoop/hive110/warehouse

javax.jdo.option.ConnectionURLmysql

jdbc:mysql://20.0.0.100:3306/hive110?createDatabaseIfNotExist=true

javax.jdo.option.ConnectionDriverName

com.mysql.jdbc.Driver

javax.jdo.option.ConnectionUserName

root

javax.jdo.option.ConnectionPassword

code

hive.server2.thrift.client.user

root

hive.server2.thrift.client.password

root

将mysql-java驱动拷贝到 hive110/lib中

cp /opt/install/hadoop/mysql-connector-java-5.1.38.jar ./

# hive环境变量

export HIVE_HOME=/opt/bigdata/hive110

export PATH=$PATH:$HIVE_HOME/bin:$PATH

# 初始化hive

./schematool -dbType mysql -initSchema

# 启动hive访问hiveserver(hive110/bin)

./hive

nohup hiveserver2 >/dev/null 2>&1 & #启动server2服务

beeline -u jdbc:hive2://200.0.0.100:10000

取消繁琐的日志信息

临时beeline　　set hive.server2.logging.operation.level=NONE;

永久配置

hive.server2.logging.operation.enabled

true

hive.server2.logging.operation.level

NONE

hive.server2.logging.operation.log.location

/opt/bigdata/hadoop/hive110/oprlog

查询优化(原理：减少mapreduce)

数据倾斜(key 不均，业务数据本身特性，建表设计，SQL命令)

1、小表join大表——mapjoin map端完成join

select /*+MAPJOIN(smalltalbe)*/ key,...from smalltable join bigtable on ...

--hive0.11版本之后默认开启

hive.auto.convert.join=true默认

hive.mapjoin.smalltable.filesize=2500000(25Mb) 默认(可以改变小表的上限值 最多为一个文件片(128Mb)大小)

2、大表join大表单key值0或null居多(改null/0键值)

解决方案：--将key值为0或者NULL的key修改为自定义的key或者通过系统自动生成的时间戳或随机数

select if(field is NULL,'null',field)

select if(field is NULL,unix_timestamp(),field)

select if(field)is NULL,rand(),field)

3、group by 维度过小 ，某分组数据过多

hive.map.aggr=truemap端完成conbiner

hive.groupby.skewindata=true　　会再开一个mapreduce 第一个mapreduce做conbiner 然后作为第二个mapreduce的入口

4、count distinct(sum(1) group by... 来替换) null值单独处理后union

sum(1) group by... 来替换