13亿表的自关联优化

今天在做一个大表的自关联优化。这张表中共计有13亿的数据量,设表为A,具体字段只有三个

CUST_ID_A 客户A的ID
CUST_ID_B 客户B的ID
RELATION 关系

A.CUST_ID_A 其中一个值的最高重复条数有3万条,这样光这一个CUST_ID_A,如果自关联会预计会有3万*3万=9亿条。

原SQL如下:

select *
from A as left
inner join A as right on left.cust_id_b = right.cust_id_a

这个SQL会执行60分钟左右,无法满足我们的要求。

第一次优化:
我将A表复制成两张表,分别在CUST_ID_A和CUST_ID_B进行分桶,进而想通过分桶JOIN提供更高的JOIN速度。

create table bucket_a(
CUST_ID_A String,
CUST_ID_B String,
RELATION String
)
CLUSTERED BY (CUST_ID_A) SORTED BY(CUST_ID_A) INTO 4096 BUCKETS
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS PARQUET;

create table bucket_b(
CUST_ID_A String,
CUST_ID_B String,
RELATION String
)
CLUSTERED BY (CUST_ID_B) SORTED BY(CUST_ID_B) INTO 4096 BUCKETS
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS PARQUET;

select *
from bucket_b as left
inner join bucket_a as right on left.cust_id_b = right.cust_id_a

可惜效果依然很差,时间和之前一样,没有效果。通过这个,我觉得是因为我把握错关键点了。现在其实是数据倾斜,分桶解决不了数据倾斜,因为同一个ID还是会打入同一个通,依然是串行的。
而我的目标两个方向:
1、优化SQL计算逻辑,不再计算全量,剔除A表中倾斜数据。这个虽然逻辑上想通了可以做剔除出来单独计算,但是短期做不了。
2、目前明确倾斜的数据在执行的时候,是串行的。我们只需要将这个串行的改为并行的就行。

解决方法一:
主要逻辑是在A表增加一个字段part(int),然后将这13亿的数据打散成20份。然后使用union all 聚合。这个过程每个一个union里面的逻辑,都是并行的!整个过程只花了8分钟。

create table parted_a(
CUST_ID_A String,
CUST_ID_B String,
part int
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS PARQUET;

INSERT INTO TABLE parted_a
SELECT *,FLOOR(RAND() * 20) --给每一条数据赋予一个20范围内的随机值,这就类似分区了。
FROM A;

--执行手动UNION ALL 20个分片。。。。。。。。。。。。SQL好长好长,但是可以执行
--注意右表还是原来的全量表,这样才能不会造成数据丢失
INSERT INTO TABLE RESULT
SELECT *
FROM PARTED_A AS left
INNER JOIN A AS right ON left.CUSTOMER_ID_B = right.CUSTOMER_ID_A AND left.PART = 1

UNION ALL

SELECT *
FROM PARTED_A AS left
INNER JOIN A AS right ON left.CUSTOMER_ID_B = right.CUSTOMER_ID_A AND left.PART = 2

UNION ALL

SELECT *
FROM PARTED_A AS left
INNER JOIN A AS right ON left.CUSTOMER_ID_B = right.CUSTOMER_ID_A AND left.PART = 3
。。。。。。。


整个过程花了8分钟,最终生成了141亿的数据。。。时间优化成功了,但是数据量还是太大了,最终还是要去优化SQL逻辑

解决方法二:
进行动态分区。可惜没有效果,很奇怪。。哎,和我预计玩不一样。整个执行时间和原来一样。

set hive.exec.dynamic.partition.mode=nonstrict;
SET hive.exec.max.dynamic.partitions= 1000;
set hive.exec.max.dynamic.partitions.pernode=1000;
create table dynamic_a(
CUST_ID_A String,
CUST_ID_B String
)
partitioned by (part int)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS PARQUET;

INSERT INTO TABLE dynamic_a PARTITION(part)
SELECT *,FLOOR(RAND() * 500) --分区要在最后一个字段。
FROM A;

INSERT INTO TABLE RESULT
SELECT *
FROM dynamic_a AS left
INNER JOIN A AS right ON left.CUSTOMER_ID_B = right.CUSTOMER_ID_A

原以为能完美的解决,可惜还是最后几个task变得异常慢。看来还是数据倾斜了,得抽空好好研究下HIVE具体怎么执行的了。

CDH常用调优参数

–规划—减少yarn内存
–yarn占据146G,solr两个实例,各16GB;hbase40GB;

–每个节点上5个container

–yarn
yarn.nodemanager.resource.memory-mb=146GB
yarn.nodemanager.resource.cpu-vcores=32
yarn.scheduler.maximum-allocation-mb=20GB

yarn.nodemanager.resource.memory-mb=108GB
yarn.nodemanager.resource.cpu-vcores=24
yarn.scheduler.maximum-allocation-mb=20GB

–mapreduce
yarn.app.mapreduce.am.resource.mb=10GB
mapreduce.map.memory.mb=10GB
mapreduce.reduce.memory.mb=10GB

–hive
spark.executor.cores=4
spark.executor.memory=16 G
spark.executor.memoryOverhead=2 G
spark.driver.memory=10.5GB
spark.yarn.driver.memoryOverhead=1.5gb

–hive server2
hive metate server heap= 16gb
hiveserver2 heap =16gb

–zookeeper
maxClientCnxns=300
SERVER JAVA heapszie=8GB

–spark和spark2
spark.authentication = spark

–hdfs
dfs.namenode.handler.count=10 –等于数据盘的数量
dfs.datanode.sync.behind.writes=true
dfs.datanode.max.transfer.threads=8192
namenode heap size=16GB
–hbase
hbase.hstore.compactionThreshold=5
HBase RegionServer java heap = 32G

hive beeline 中文报错

今天在跑一个脚本的时候,死活报错:

我开始就认为就是SQL文件顶部的中文注释造成的问题,然后我删除了所有的中文注释依然报上面的错误。

原因:
SQL文件格式有问题,同事在编写SQL的时候是在WINDOWS电脑上编写的,所以文件是默认格式是WINDOWS。编码为UTF-8-BOM格式。

解决方法:
1、使用工具notepad++,将文件转换为unix文件

2、再将语言改为utf-8

cdh 启用kerberos后的各种问题!!真的好累

在CDH启用了kerberos认证后,各种组件不断各种错误。。。很多都是网上查不到。不知道是我们安装不对还是什么情况。
现在记录下来以备将来使用。

一、在从hive建立映射表到hbase的时候报错,执行的语句如下:
CREATE EXTERNAL TABLE hbase_family_base(rk string,
id string,
agent_code string,
cust_ecif_id string,
real_name string,
gender string,
birthday string,
age string,
certi_type string,
certi_code string,
job_id string,
job_zh string,
relatives_ecif_id string,
relatives_real_name string,
relatives_gender string,
relatives_birthday string,
relatives_age string,
relatives_certi_type string,
relatives_certi_code string,
relatives_job_id string,
relatives_job_zh string,
relation string,
policy_num string)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,family_base_test:id,
family_base_test:agent_code,
family_base_test:cust_ecif_id,
family_base_test:real_name,
family_base_test:gender,
family_base_test:birthday,
family_base_test:age,
family_base_test:certi_type,
family_base_test:certi_code,
family_base_test:job_id,
family_base_test:job_zh,
family_base_test:relatives_ecif_id,
family_base_test:relatives_real_name,
family_base_test:relatives_gender,
family_base_test:relatives_birthday,
family_base_test:relatives_age,
family_base_test:relatives_certi_type,
family_base_test:relatives_certi_code,
family_base_test:relatives_job_id,
family_base_test:relatives_job_zh,
family_base_test:relation,
family_base_test:policy_num string")
TBLPROPERTIES("hbase.table.name" = "hbase_family_base_test","hbase.mapred.output.outputtable" = "hbase_family_base_test");

然后报错信息如下:
INFO : Completed executing command(queryId=hive_20181124160404_28d8cae7-f4c3-46bc-ad48-5882f81289c0); Time taken: 48.329 seconds
Error: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:org.apache.hadoop.hbase.client.RetriesExhaustedException: Failed after attempts=36, exceptions:
Sat Nov 24 16:05:39 CST 2018, null, java.net.SocketTimeoutException: callTimeout=60000, callDuration=68267: row 'hbase_image,,' on table 'hbase:meta' at region=hbase:meta,,1.1588230740, hostname=node06,60020,1543042854540, seqNum=0

at org.apache.hadoop.hbase.client.RpcRetryingCallerWithReadReplicas.throwEnrichedException(RpcRetryingCallerWithReadReplicas.java:320)
at org.apache.hadoop.hbase.client.ScannerCallableWithReplicas.call(ScannerCallableWithReplicas.java:247)
at org.apache.hadoop.hbase.client.ScannerCallableWithReplicas.call(ScannerCallableWithReplicas.java:62)
at org.apache.hadoop.hbase.client.RpcRetryingCaller.callWithoutRetries(RpcRetryingCaller.java:210)
at org.apache.hadoop.hbase.client.ClientScanner.call(ClientScanner.java:327)
at org.apache.hadoop.hbase.client.ClientScanner.nextScanner(ClientScanner.java:302)
at org.apache.hadoop.hbase.client.ClientScanner.initializeScannerInConstruction(ClientScanner.java:167)
at org.apache.hadoop.hbase.client.ClientScanner.(ClientScanner.java:162)
at org.apache.hadoop.hbase.client.HTable.getScanner(HTable.java:862)
at org.apache.hadoop.hbase.MetaTableAccessor.fullScan(MetaTableAccessor.java:602)
at org.apache.hadoop.hbase.MetaTableAccessor.tableExists(MetaTableAccessor.java:366)
at org.apache.hadoop.hbase.client.HBaseAdmin.tableExists(HBaseAdmin.java:421)
at org.apache.hadoop.hbase.client.HBaseAdmin.tableExists(HBaseAdmin.java:431)
at org.apache.hadoop.hive.hbase.HBaseStorageHandler.preCreateTable(HBaseStorageHandler.java:195)
at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.createTable(HiveMetaStoreClient.java:735)
at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.createTable(HiveMetaStoreClient.java:728)
at sun.reflect.GeneratedMethodAccessor22.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.invoke(RetryingMetaStoreClient.java:105)
at com.sun.proxy.$Proxy20.createTable(Unknown Source)
at sun.reflect.GeneratedMethodAccessor22.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.hadoop.hive.metastore.HiveMetaStoreClient$SynchronizedHandler.invoke(HiveMetaStoreClient.java:2134)
at com.sun.proxy.$Proxy20.createTable(Unknown Source)
at org.apache.hadoop.hive.ql.metadata.Hive.createTable(Hive.java:784)
at org.apache.hadoop.hive.ql.exec.DDLTask.createTable(DDLTask.java:4177)
at org.apache.hadoop.hive.ql.exec.DDLTask.execute(DDLTask.java:311)
at org.apache.hadoop.hive.ql.exec.Task.executeTask(Task.java:214)
at org.apache.hadoop.hive.ql.exec.TaskRunner.runSequential(TaskRunner.java:99)
at org.apache.hadoop.hive.ql.Driver.launchTask(Driver.java:2052)
at org.apache.hadoop.hive.ql.Driver.execute(Driver.java:1748)
at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1501)
at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1285)
at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1280)
at org.apache.hive.service.cli.operation.SQLOperation.runQuery(SQLOperation.java:236)
at org.apache.hive.service.cli.operation.SQLOperation.access$300(SQLOperation.java:89)
at org.apache.hive.service.cli.operation.SQLOperation$3$1.run(SQLOperation.java:301)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:422)
at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1917)
at org.apache.hive.service.cli.operation.SQLOperation$3.run(SQLOperation.java:314)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.net.SocketTimeoutException: callTimeout=60000, callDuration=68267: row 'hbase_image,,' on table 'hbase:meta' at region=hbase:meta,,1.1588230740, hostname=node06,60020,1543042854540, seqNum=0
at org.apache.hadoop.hbase.client.RpcRetryingCaller.callWithRetries(RpcRetryingCaller.java:169)
at org.apache.hadoop.hbase.client.ResultBoundedCompletionService$QueueingFuture.run(ResultBoundedCompletionService.java:80)
... 3 more
Caused by: org.apache.hadoop.hbase.exceptions.ConnectionClosingException: Call to node06/10.137.65.9:60020 failed on local exception: org.apache.hadoop.hbase.exceptions.ConnectionClosingException: Connection to node06/10.137.65.9:60020 is closing. Call id=142, waitTime=1
at org.apache.hadoop.hbase.ipc.AbstractRpcClient.wrapException(AbstractRpcClient.java:289)
at org.apache.hadoop.hbase.ipc.RpcClientImpl.call(RpcClientImpl.java:1273)
at org.apache.hadoop.hbase.ipc.AbstractRpcClient.callBlockingMethod(AbstractRpcClient.java:227)
at org.apache.hadoop.hbase.ipc.AbstractRpcClient$BlockingRpcChannelImplementation.callBlockingMethod(AbstractRpcClient.java:336)
at org.apache.hadoop.hbase.protobuf.generated.ClientProtos$ClientService$BlockingStub.scan(ClientProtos.java:34094)
at org.apache.hadoop.hbase.client.ScannerCallable.openScanner(ScannerCallable.java:400)
at org.apache.hadoop.hbase.client.ScannerCallable.call(ScannerCallable.java:204)
at org.apache.hadoop.hbase.client.ScannerCallable.call(ScannerCallable.java:65)
at org.apache.hadoop.hbase.client.RpcRetryingCaller.callWithoutRetries(RpcRetryingCaller.java:210)
at org.apache.hadoop.hbase.client.ScannerCallableWithReplicas$RetryingRPC.call(ScannerCallableWithReplicas.java:397)
at org.apache.hadoop.hbase.client.ScannerCallableWithReplicas$RetryingRPC.call(ScannerCallableWithReplicas.java:371)
at org.apache.hadoop.hbase.client.RpcRetryingCaller.callWithRetries(RpcRetryingCaller.java:136)
... 4 more
Caused by: org.apache.hadoop.hbase.exceptions.ConnectionClosingException: Connection to node06/10.137.65.9:60020 is closing. Call id=142, waitTime=1
at org.apache.hadoop.hbase.ipc.RpcClientImpl$Connection.cleanupCalls(RpcClientImpl.java:1085)
at org.apache.hadoop.hbase.ipc.RpcClientImpl$Connection.close(RpcClientImpl.java:864)
at org.apache.hadoop.hbase.ipc.RpcClientImpl$Connection.run(RpcClientImpl.java:581)

解决方法:
1、spark gateway要在每一个节点上部署(这个我不知道有没有效)
2、hbase gateway要在每一个节点上部署(部署后如下错误解除!)

二、 hive on spark 执行sql的时候,报错。报错为:
thrift:bdp01:9083无法连接

到处查找资料都找不到问题的原因。端口检查那些都是正常的。后来测试spark submit任务也是错误的。
最后执行spark-shell指令,一启动就报错。最后确认为无法初始化sqlcontext。因为spark任务一启动就会初始化sqlcontext到hive。
现在也没有明白什么情况,最后重新启用 hive cli才行了,但是这样没有安全意义了。因为kerberos建议禁用hive cli。

三、hive集成了kerberos后,当使用beeline执行sql:load local inpath 指令的时候,碰到好几个问题,如下:
1、第一个问题就是在load local inpath的时候报错:invalid path。死活找不到本地文件路径。后来才发现:
如果你在hiverserver2部署到node01节点的时候,实际执行load local inpath是指node01的本地路径,而不是你beeline执行的那个节点的本地路径。例如你将数据文件放在node17上,然后从node17执行beeline连接到node01的hiverserver2,那么load local 实际是在node01上执行,所以自然就找不到数据文件。
解决方法:
a、在node17上安装一个hiverserver2,简单粗暴!只是如果做了HA后,那么这么解决方案失效
b、不要使用load local 指令。而是首先将文件从本地通过 hdfs put 到hdfs一个路径上。再使用load inpath指令从hdfs上读取。

2、第二个问题是通过部署一个hiverserver2,解决第一个问题后,虽然能找到那个本地文件。但是报错:permission denied
。不允许读取本地文件。
后来发现:beeline在执行hive on spark的时候,无论你kinit的用户是谁,实际执行的用户是hive。所以本地数据文件必须要在hive组里面。解决方法有两个:
a、新建一个用户加载数据文件的操作系统用户,并将该系统用户加入hive组
usermod -g hive test
b、使用chown -R test:hive 数据文件目录。但是要执行这个指令必须具有chown权限的用户才行。

四、hdfs报missblock
在安装好集群后使用一段时间后,突然要演示如何删除节点,再添加回来。结果在添加节点回来的时候,hdfs报错:missing block,超过阀值99%的可用块,导致hdfs经常进入安全模式。整个hdfs集群不可用。
a、hdfs进入安全模式后,会自动修复missingblock。但是我们的集群一直报错,连接不上zookeeper,可能是我们安装的问题。

b、手动处理missing block.但是手动处理的时候一定要确认这些missingblock是否存在备份,如果没有备份,那么这些数据块就真的丢失了,因为也没法恢复。
首先查看missblock的情况:hdfs dfsadmin -report

通过查看
然后执行 hdfs fsck -delete指令。

CDH 集群节点通用调优

1、增加操作系统文件句柄数

a)ulimit

编辑/etc/security/limits.conf

增加四行:

* soft nofile 65000

* hard nofile 65000

* soft nproc 65000

* hard nproc 65000

保存后重登录生效

 


PS:今天碰到几台服务器怎么修改soft nproc的值都无法生效。这里是你得检查

ls /etc/security/limits.d/ 下面20-nproc.conf中的配置是否把soft nproc的配置给覆写了,修改即可。

b)file-max

编辑/etc/sysctl.conf

增加一行:fs.file-max = 200000

然后执行指令:sysctl -p。这样无需重启即可生效

c)检查:使用ulimit -a,ulimit -Sn,ulimit -Hn检查参数是否生效。

2、降低交换比例

编辑 /etc/sysctl.conf

增加一行:vm.swappiness=10

然后执行指令:sysctl -p。这样无需重启即可生效。

(CDH以前推荐设置为0.现在推荐设置为10)

3、禁用大透明页:
transparent_hugepages=never
echo never > /sys/kernel/mm/transparent_hugepage/defrag
echo never > /sys/kernel/mm/transparent_hugepage/enabled

4、mount盘的时候需要为noatime,

参考文章:

1、http://crazyadmins.com/tune-hadoop-cluster-to-get-maximum-performance-part-1/

2、http://crazyadmins.com/tune-hadoop-cluster-to-get-maximum-performance-part-2/