spark load 导入ssb lineorder 800GB数据，时间过长3个小时都导不进去

[gj-zhang]

集群描述:
版本 0.14.0
1个fe节点，3个be节点，4个broker节点
单机配置： 72核，300GB内存， 11T ssd
lineorder表在hive中有60多亿条

-- doris  lineorder
CREATE TABLE IF NOT EXISTS `lineorder` (
  `lo_orderkey` bigint NOT NULL COMMENT "",
  `lo_linenumber` int(11) NOT NULL COMMENT "",
  `lo_custkey` bigint NOT NULL COMMENT "",
  `lo_partkey` bigint NOT NULL COMMENT "",
  `lo_suppkey` bigint NOT NULL COMMENT "",
  `lo_orderdate` date NOT NULL COMMENT "",
  `lo_orderpriority` varchar(16) NOT NULL COMMENT "",
  `lo_shippriority` int(11) NOT NULL COMMENT "",
  `lo_quantity` int(11) NOT NULL COMMENT "",
  `lo_extendedprice` bigint NOT NULL COMMENT "",
  `lo_ordtotalprice` bigint NOT NULL COMMENT "",
  `lo_discount` int(11) NOT NULL COMMENT "",
  `lo_revenue` bigint NOT NULL COMMENT "",
  `lo_supplycost` bigint NOT NULL COMMENT "",
  `lo_tax` int(11) NOT NULL COMMENT "",
  `lo_commitdate` date NOT NULL COMMENT "",
  `lo_shipmode` varchar(11) NOT NULL COMMENT ""
) ENGINE=olap
DUPLICATE KEY(`lo_orderkey`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`lo_orderkey`) BUCKETS 96
PROPERTIES (
"replication_num" = "1",
"colocate_with" = "group2",
"in_memory" = "false",
"storage_format" = "DEFAULT"
);

spark resource

CREATE EXTERNAL RESOURCE "doris_spark_nn1"
PROPERTIES
(
  "type" = "spark",
  "spark.master" = "yarn",
  "spark.submit.deployMode" = "cluster",
  "spark.executor.memory" = "8g",
  "spark.yarn.queue" = "root.default",
  "spark.hadoop.yarn.resourcemanager.address" = "host:port",
  "spark.hadoop.fs.defaultFS" = "hdfs://host:port",
  "working_dir" = "hdfs://host:port/olap_benchmark/doris",
  "broker" = "hdfs_broker"
);

hive外部表方式

CREATE EXTERNAL TABLE benchmark.hive_lineorder
(
  `lo_orderkey` bigint NOT NULL COMMENT "",
  `lo_linenumber` int(11) NOT NULL COMMENT "",
  `lo_custkey` bigint NOT NULL COMMENT "",
  `lo_partkey` bigint NOT NULL COMMENT "",
  `lo_suppkey` bigint NOT NULL COMMENT "",
  `lo_orderdate` date NOT NULL COMMENT "",
  `lo_orderpriority` varchar(16) NOT NULL COMMENT "",
  `lo_shippriority` int(11) NOT NULL COMMENT "",
  `lo_quantity` int(11) NOT NULL COMMENT "",
  `lo_extendedprice` bigint NOT NULL COMMENT "",
  `lo_ordtotalprice` bigint NOT NULL COMMENT "",
  `lo_discount` int(11) NOT NULL COMMENT "",
  `lo_revenue` bigint NOT NULL COMMENT "",
  `lo_supplycost` bigint NOT NULL COMMENT "",
  `lo_tax` int(11) NOT NULL COMMENT "",
  `lo_commitdate` date NOT NULL COMMENT "",
  `lo_shipmode` varchar(11) NOT NULL COMMENT ""
)
ENGINE=hive
properties
(
"database" = "olap_benchmark",
"table" = "lineorder",
"hive.metastore.uris" = "thrift://host:port"
);


LOAD LABEL benchmark.lineorder_load28
(
    DATA FROM TABLE hive_lineorder
    INTO TABLE lineorder
)
WITH RESOURCE 'doris_spark_nn1'
(
    "spark.executor.memory" = "8g",
    "spark.shuffle.compress" = "true",
    "spark.executor.cores" = "1",
    "spark.executor.instances" = "20",
    "spark.driver.memory" = "5g",
    "spark.driver.cores" = "2",
    "spark.dynamicAllocation.enabled" = "false"
)
PROPERTIES
(
    "timeout" = "72000"
);

hdfs文件方式

LOAD LABEL benchmark.zgj_spark_load_hdfs_5
(
    DATA INFILE("hdfs://host:port/olap_benchmark/lineorder/*")
    INTO TABLE lineorder
    COLUMNS TERMINATED BY "\\x01"
)
WITH RESOURCE 'doris_spark_nn1'
(
    "spark.executor.memory" = "8g",
    "spark.shuffle.compress" = "true",
    "spark.executor.cores" = "1",
    "spark.executor.instances" = "20",
    "spark.driver.memory" = "5g",
    "spark.driver.cores" = "2",
    "spark.dynamicAllocation.enabled" = "false"
)
PROPERTIES
(
    "timeout" = "72000"
);

麻烦帮忙看下， 用hdfs文件的方式存在数据倾斜， 用hive外部表的方式耗时很长。 两种方式基本上都跑了3个小时没跑完被我手动杀掉了？ 是否是我用的不对，请求帮助已经搞了好几天了。。。

[morningman]

hdfs文件的方式为什么会存在数据倾斜？800GB数据，建议使用 broker load，分10个批次导入，应该没啥问题。

[wangbo]

这个只能看spark作业看具体是慢在哪一步，是资源不充足还是某一步确实很慢
一般不计算全局字典的话是不会有数据倾斜问题的
如果对spark作业问题诊断不熟悉的话，建议还是先用stream load或者broker load吧
以我们内部场景举例，200亿数据且包含hll复杂列的计算，资源充足的话一个多小时就能跑完
ssb这才60亿数据其实并不是很大