Guadazi
0%

Hive SQL优化样例

Posted on Edited on In 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
select /*+ MAPJOIN(hj3,hj1,hj0)*/ 20210324 as fdate, TO_CHAR(SYSTIMESTAMP(), 'yyyymmddhh24miss') as fetl_time,hj2.fuin as fuin,COUNT(DISTINCT(SUBSTR(CAST(if (((hj2.fevent_time between 20210318000000 and 20210324235959) and (hj2.fscn in ('1'))) , hj2.fevent_time, null) AS STRING),1,8))) as flabel_25942,   COUNT(DISTINCT(SUBSTR(CAST(if (((hj2.fevent_time between 20210318000000 and 20210324235959)  and (hj2.fscn in ('1'))) , hj2.fevent_time, null) AS STRING),1,8))) as flabel_25970, COUNT(DISTINCT(SUBSTR(CAST(if (((hj2.furl_orig = '/mb/v5/fund/list/steady.shtml')  or (hj2.furl_orig = '/mb/v4/fundlist/fund_all.shtml')    or (hj2.furl_orig = '/mb/v4/fundlist/fund_all_v5.shtml'))  and ((hj2.fevent_time between 20210318000000 and 20210324235959)) , hj2.fevent_time, null) AS STRING),1,8))) as flabel_25972

from dml_base::dml_evt_lct_cft_label_factory_mta_access_dd hj2

left join dim_base::dim_lct_hq_beacon_report_manage hj1 on if(hj2.furl_orig is null
                                                              or trim(hj2.furl_orig) = '', concat('null_', floor(rand() * 10000)), hj2.furl_orig) = hj1.fevent_code

left join dim_base::dim_lct_hq_virtual_event_info hj3 on if(hj2.furl_orig is null
                                                      or trim(hj2.furl_orig) = '', concat('null_', floor(rand() * 10000)), hj2.furl_orig) = hj3.fevent_code
left join dim_base::dim_prd_lct_cft_fund_type_conf hj0 on if(hj2.fspid_fundcode is null
                                                             or trim(hj2.fspid_fundcode) = '', concat('null_', floor(rand() * 10000)), hj2.fspid_fundcode) = hj0.fspid_fundcode

where hj2.fuin is not null

  and trim(hj2.fuin) <> ''

  and hj2.fdate >= 20210318

  and hj2.fdate <= 20210324

group by hj2.fuin
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
ABSTRACT SYNTAX TREE:
  (TOK_QUERY (TOK_FROM (TOK_LEFTOUTERJOIN (TOK_LEFTOUTERJOIN (TOK_LEFTOUTERJOIN (TOK_TABREF (TOK_TAB dml_evt_lct_cft_label_factory_mta_access_dd dml_base) hj2) (TOK_TABREF (TOK_TAB dim_lct_hq_beacon_report_manage dim_base) hj1) (= (TOK_FUNCTION if (or (TOK_FUNCTION TOK_ISNULL (. (TOK_TABLE_OR_COL hj2) furl_orig)) (= (TOK_FUNCTION trim (. (TOK_TABLE_OR_COL hj2) furl_orig)) '')) (TOK_FUNCTION concat 'null_' (TOK_FUNCTION floor (* (TOK_FUNCTION rand) 10000))) (. (TOK_TABLE_OR_COL hj2) furl_orig)) (. (TOK_TABLE_OR_COL hj1) fevent_code))) (TOK_TABREF (TOK_TAB dim_lct_hq_virtual_event_info dim_base) hj3) (= (TOK_FUNCTION if (or (TOK_FUNCTION TOK_ISNULL (. (TOK_TABLE_OR_COL hj2) furl_orig)) (= (TOK_FUNCTION trim (. (TOK_TABLE_OR_COL hj2) furl_orig)) '')) (TOK_FUNCTION concat 'null_' (TOK_FUNCTION floor (* (TOK_FUNCTION rand) 10000))) (. (TOK_TABLE_OR_COL hj2) furl_orig)) (. (TOK_TABLE_OR_COL hj3) fevent_code))) (TOK_TABREF (TOK_TAB dim_prd_lct_cft_fund_type_conf dim_base) hj0) (= (TOK_FUNCTION if (or (TOK_FUNCTION TOK_ISNULL (. (TOK_TABLE_OR_COL hj2) fspid_fundcode)) (= (TOK_FUNCTION trim (. (TOK_TABLE_OR_COL hj2) fspid_fundcode)) '')) (TOK_FUNCTION concat 'null_' (TOK_FUNCTION floor (* (TOK_FUNCTION rand) 10000))) (. (TOK_TABLE_OR_COL hj2) fspid_fundcode)) (. (TOK_TABLE_OR_COL hj0) fspid_fundcode)))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT (TOK_HINTLIST (TOK_HINT TOK_MAPJOIN (TOK_HINTARGLIST (TOK_TABLE_OR_COL hj3) (TOK_TABLE_OR_COL hj1) (TOK_TABLE_OR_COL hj0)))) (TOK_SELEXPR 20210324 fdate) (TOK_SELEXPR (TOK_FUNCTION TO_CHAR (TOK_FUNCTION SYSTIMESTAMP) 'yyyymmddhh24miss') fetl_time) (TOK_SELEXPR (. (TOK_TABLE_OR_COL hj2) fuin) fuin) (TOK_SELEXPR (TOK_FUNCTIONDI COUNT (TOK_FUNCTION SUBSTR (TOK_FUNCTION TOK_STRING (TOK_FUNCTION if (and (and (>= (. (TOK_TABLE_OR_COL hj2) fevent_time) 20210318000000) (<= (. (TOK_TABLE_OR_COL hj2) fevent_time) 20210324235959)) (in (. (TOK_TABLE_OR_COL hj2) fscn) '1')) (. (TOK_TABLE_OR_COL hj2) fevent_time) TOK_NULL)) 1 8)) flabel_25942) (TOK_SELEXPR (TOK_FUNCTIONDI COUNT (TOK_FUNCTION SUBSTR (TOK_FUNCTION TOK_STRING (TOK_FUNCTION if (and (and (>= (. (TOK_TABLE_OR_COL hj2) fevent_time) 20210318000000) (<= (. (TOK_TABLE_OR_COL hj2) fevent_time) 20210324235959)) (in (. (TOK_TABLE_OR_COL hj2) fscn) '1')) (. (TOK_TABLE_OR_COL hj2) fevent_time) TOK_NULL)) 1 8)) flabel_25970) (TOK_SELEXPR (TOK_FUNCTIONDI COUNT (TOK_FUNCTION SUBSTR (TOK_FUNCTION TOK_STRING (TOK_FUNCTION if (and (or (or (= (. (TOK_TABLE_OR_COL hj2) furl_orig) '/mb/v5/fund/list/steady.shtml') (= (. (TOK_TABLE_OR_COL hj2) furl_orig) '/mb/v4/fundlist/fund_all.shtml')) (= (. (TOK_TABLE_OR_COL hj2) furl_orig) '/mb/v4/fundlist/fund_all_v5.shtml')) (and (>= (. (TOK_TABLE_OR_COL hj2) fevent_time) 20210318000000) (<= (. (TOK_TABLE_OR_COL hj2) fevent_time) 20210324235959))) (. (TOK_TABLE_OR_COL hj2) fevent_time) TOK_NULL)) 1 8)) flabel_25972)) (TOK_WHERE (and (and (and (TOK_FUNCTION TOK_ISNOTNULL (. (TOK_TABLE_OR_COL hj2) fuin)) (<> (TOK_FUNCTION trim (. (TOK_TABLE_OR_COL hj2) fuin)) '')) (>= (. (TOK_TABLE_OR_COL hj2) fdate) 20210318)) (<= (. (TOK_TABLE_OR_COL hj2) fdate) 20210324))) (TOK_GROUPBY (. (TOK_TABLE_OR_COL hj2) fuin))))

STAGE DEPENDENCIES:
  Stage-1
    type:root stage;
  Stage-2
    type:;depends on:Stage-1;
  Stage-3
    type:;depends on:Stage-2;
  Stage-0
    type:root stage;

STAGE PLANS:
  Stage: Stage-1
    Map Reduce
      Alias -> Map Operator Tree:
        dml_base/dml_evt_lct_cft_label_factory_mta_access_dd#hj2 
          Operator:          TableScan
            alias: dml_base/dml_evt_lct_cft_label_factory_mta_access_dd#hj2
            Operator:            Filter Operator
              predicate:
                  expr: (((fuin is not null and (trim(fuin) <> '')) and (fdate >= 20210318)) and (fdate <= 20210324))
                  type: boolean
              Operator:              Common Join Operator
                condition map:
                     Left Outer Join0 to 1
                     Left Outer Join0 to 2
                condition expressions:
                  0 {fdate} {fuin} {furl_orig} {fevent_time} {fspid_fundcode} {fscn}
                  1 
                  2 
                handleSkewJoin: false
                keys:
                  0 [class com.tencent.tdw_udf_cloud.hive.udf.generic.GenericUDFIf(class org.apache.hadoop.hive.ql.udf.generic.GenericUDFOPOr(class org.apache.hadoop.hive.ql.udf.generic.GenericUDFOPNull(Column[furl_orig](), class org.apache.hadoop.hive.ql.udf.generic.GenericUDFOPEqual(class org.apache.hadoop.hive.ql.udf.generic.GenericUDFBridge(Column[furl_orig](), Const string ()(), class org.apache.hadoop.hive.ql.udf.generic.GenericUDFBridge(Const string null_, class org.apache.hadoop.hive.ql.udf.generic.GenericUDFBridge(class org.apache.hadoop.hive.ql.udf.generic.GenericUDFBridge(class org.apache.hadoop.hive.ql.udf.generic.GenericUDFBridge((), Const int 10000()()(), Column[furl_orig]()]
                  1 [Column[fevent_code]]
                  2 [Column[fevent_code]]
                outputColumnNames: _col0, _col42, _col85, _col88, _col103, _col104
                Position of Big Table: 0
                Operator:                File Output Operator
                  compressed: false
                  GlobalTableId: 0
                  table:
                    table descs
                      input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                      output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
      Local Work:
        Map Reduce Local Work
          Alias -> Map Local Tables:
            dim_base/dim_lct_hq_beacon_report_manage#hj1 
              Fetch Operator
                limit: -1
            dim_base/dim_lct_hq_virtual_event_info#hj3 
              Fetch Operator
                limit: -1
          Alias -> Map Local Operator Tree:
            dim_base/dim_lct_hq_beacon_report_manage#hj1 
              Operator:              TableScan
                alias: dim_base/dim_lct_hq_beacon_report_manage#hj1
                Operator:                Common Join Operator
                  condition map:
                       Left Outer Join0 to 1
                       Left Outer Join0 to 2
                  condition expressions:
                    0 {fdate} {fuin} {furl_orig} {fevent_time} {fspid_fundcode} {fscn}
                    1 
                    2 
                  handleSkewJoin: false
                  keys:
                    0 [class com.tencent.tdw_udf_cloud.hive.udf.generic.GenericUDFIf(class org.apache.hadoop.hive.ql.udf.generic.GenericUDFOPOr(class org.apache.hadoop.hive.ql.udf.generic.GenericUDFOPNull(Column[furl_orig](), class org.apache.hadoop.hive.ql.udf.generic.GenericUDFOPEqual(class org.apache.hadoop.hive.ql.udf.generic.GenericUDFBridge(Column[furl_orig](), Const string ()(), class org.apache.hadoop.hive.ql.udf.generic.GenericUDFBridge(Const string null_, class org.apache.hadoop.hive.ql.udf.generic.GenericUDFBridge(class org.apache.hadoop.hive.ql.udf.generic.GenericUDFBridge(class org.apache.hadoop.hive.ql.udf.generic.GenericUDFBridge((), Const int 10000()()(), Column[furl_orig]()]
                    1 [Column[fevent_code]]
                    2 [Column[fevent_code]]
                  outputColumnNames: _col0, _col42, _col85, _col88, _col103, _col104
                  Position of Big Table: 0
                  Operator:                  File Output Operator
                    compressed: false
                    GlobalTableId: 0
                    table:
                      table descs
                        input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                        output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
            dim_base/dim_lct_hq_virtual_event_info#hj3 
              Operator:              TableScan
                alias: dim_base/dim_lct_hq_virtual_event_info#hj3
                Operator:                Common Join Operator
                  condition map:
                       Left Outer Join0 to 1
                       Left Outer Join0 to 2
                  condition expressions:
                    0 {fdate} {fuin} {furl_orig} {fevent_time} {fspid_fundcode} {fscn}
                    1 
                    2 
                  handleSkewJoin: false
                  keys:
                    0 [class com.tencent.tdw_udf_cloud.hive.udf.generic.GenericUDFIf(class org.apache.hadoop.hive.ql.udf.generic.GenericUDFOPOr(class org.apache.hadoop.hive.ql.udf.generic.GenericUDFOPNull(Column[furl_orig](), class org.apache.hadoop.hive.ql.udf.generic.GenericUDFOPEqual(class org.apache.hadoop.hive.ql.udf.generic.GenericUDFBridge(Column[furl_orig](), Const string ()(), class org.apache.hadoop.hive.ql.udf.generic.GenericUDFBridge(Const string null_, class org.apache.hadoop.hive.ql.udf.generic.GenericUDFBridge(class org.apache.hadoop.hive.ql.udf.generic.GenericUDFBridge(class org.apache.hadoop.hive.ql.udf.generic.GenericUDFBridge((), Const int 10000()()(), Column[furl_orig]()]
                    1 [Column[fevent_code]]
                    2 [Column[fevent_code]]
                  outputColumnNames: _col0, _col42, _col85, _col88, _col103, _col104
                  Position of Big Table: 0
                  Operator:                  File Output Operator
                    compressed: false
                    GlobalTableId: 0
                    table:
                      table descs
                        input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                        output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
      Path -> Alias:
        hdfs://<hive_hdfs_base_path>/warehouse/dml_base.db/dml_evt_lct_cft_label_factory_mta_access_dd/par_20210318 [dml_base/dml_evt_lct_cft_label_factory_mta_access_dd#hj2]
        hdfs://<hive_hdfs_base_path>/warehouse/dml_base.db/dml_evt_lct_cft_label_factory_mta_access_dd/par_20210319 [dml_base/dml_evt_lct_cft_label_factory_mta_access_dd#hj2]
        hdfs://<hive_hdfs_base_path>/warehouse/dml_base.db/dml_evt_lct_cft_label_factory_mta_access_dd/par_20210320 [dml_base/dml_evt_lct_cft_label_factory_mta_access_dd#hj2]
        hdfs://<hive_hdfs_base_path>/warehouse/dml_base.db/dml_evt_lct_cft_label_factory_mta_access_dd/par_20210321 [dml_base/dml_evt_lct_cft_label_factory_mta_access_dd#hj2]
        hdfs://<hive_hdfs_base_path>/warehouse/dml_base.db/dml_evt_lct_cft_label_factory_mta_access_dd/par_20210322 [dml_base/dml_evt_lct_cft_label_factory_mta_access_dd#hj2]
        hdfs://<hive_hdfs_base_path>/warehouse/dml_base.db/dml_evt_lct_cft_label_factory_mta_access_dd/par_20210323 [dml_base/dml_evt_lct_cft_label_factory_mta_access_dd#hj2]
        hdfs://<hive_hdfs_base_path>/warehouse/dml_base.db/dml_evt_lct_cft_label_factory_mta_access_dd/par_20210324 [dml_base/dml_evt_lct_cft_label_factory_mta_access_dd#hj2]

  Stage: Stage-2
    Map Reduce
      Alias -> Map Operator Tree:
        hdfs://<tmp_hive_hdfs_base_path>/20210325/<hdfs_user>_tdwadmin_20210325195415346_29895878_7_699597773/10002 
          Operator:          Select Operator
            expressions:
                  expr: _col0
                  type: bigint
                  expr: _col42
                  type: string
                  expr: _col85
                  type: string
                  expr: _col88
                  type: bigint
                  expr: _col103
                  type: string
                  expr: _col104
                  type: string
            outputColumnNames: _col0, _col42, _col85, _col88, _col103, _col104
            Operator:            Common Join Operator
              condition map:
                   Left Outer Join0 to 1
              condition expressions:
                0 {_col0} {_col42} {_col85} {_col88} {_col104}
                1 
              handleSkewJoin: false
              keys:
                0 [class com.tencent.tdw_udf_cloud.hive.udf.generic.GenericUDFIf(class org.apache.hadoop.hive.ql.udf.generic.GenericUDFOPOr(class org.apache.hadoop.hive.ql.udf.generic.GenericUDFOPNull(Column[_col103](), class org.apache.hadoop.hive.ql.udf.generic.GenericUDFOPEqual(class org.apache.hadoop.hive.ql.udf.generic.GenericUDFBridge(Column[_col103](), Const string ()(), class org.apache.hadoop.hive.ql.udf.generic.GenericUDFBridge(Const string null_, class org.apache.hadoop.hive.ql.udf.generic.GenericUDFBridge(class org.apache.hadoop.hive.ql.udf.generic.GenericUDFBridge(class org.apache.hadoop.hive.ql.udf.generic.GenericUDFBridge((), Const int 10000()()(), Column[_col103]()]
                1 [Column[fspid_fundcode]]
              outputColumnNames: _col0, _col42, _col85, _col88, _col104
              Position of Big Table: 0
              Operator:              File Output Operator
                compressed: false
                GlobalTableId: 0
                table:
                  table descs
                    input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                    output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
      Local Work:
        Map Reduce Local Work
          Alias -> Map Local Tables:
            dim_base/dim_prd_lct_cft_fund_type_conf#hj0 
              Fetch Operator
                limit: -1
          Alias -> Map Local Operator Tree:
            dim_base/dim_prd_lct_cft_fund_type_conf#hj0 
              Operator:              TableScan
                alias: dim_base/dim_prd_lct_cft_fund_type_conf#hj0
                Operator:                Common Join Operator
                  condition map:
                       Left Outer Join0 to 1
                  condition expressions:
                    0 {_col0} {_col42} {_col85} {_col88} {_col104}
                    1 
                  handleSkewJoin: false
                  keys:
                    0 [class com.tencent.tdw_udf_cloud.hive.udf.generic.GenericUDFIf(class org.apache.hadoop.hive.ql.udf.generic.GenericUDFOPOr(class org.apache.hadoop.hive.ql.udf.generic.GenericUDFOPNull(Column[_col103](), class org.apache.hadoop.hive.ql.udf.generic.GenericUDFOPEqual(class org.apache.hadoop.hive.ql.udf.generic.GenericUDFBridge(Column[_col103](), Const string ()(), class org.apache.hadoop.hive.ql.udf.generic.GenericUDFBridge(Const string null_, class org.apache.hadoop.hive.ql.udf.generic.GenericUDFBridge(class org.apache.hadoop.hive.ql.udf.generic.GenericUDFBridge(class org.apache.hadoop.hive.ql.udf.generic.GenericUDFBridge((), Const int 10000()()(), Column[_col103]()]
                    1 [Column[fspid_fundcode]]
                  outputColumnNames: _col0, _col42, _col85, _col88, _col104
                  Position of Big Table: 0
                  Operator:                  File Output Operator
                    compressed: false
                    GlobalTableId: 0
                    table:
                      table descs
                        input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                        output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
      Path -> Alias:
        hdfs://<tmp_hive_hdfs_base_path>/20210325/<hdfs_user>_tdwadmin_20210325195415346_29895878_7_699597773/10002 [hdfs://<tmp_hive_hdfs_base_path>/20210325/<hdfs_user>_tdwadmin_20210325195415346_29895878_7_699597773/10002]

  Stage: Stage-3
    Map Reduce
      Alias -> Map Operator Tree:
        hdfs://<tmp_hive_hdfs_base_path>/20210325/<hdfs_user>_tdwadmin_20210325195415346_29895878_7_699597773/10002 
          Operator:          Select Operator
            expressions:
                  expr: _col0
                  type: bigint
                  expr: _col42
                  type: string
                  expr: _col85
                  type: string
                  expr: _col88
                  type: bigint
                  expr: _col103
                  type: string
                  expr: _col104
                  type: string
            outputColumnNames: _col0, _col42, _col85, _col88, _col103, _col104
            Operator:            Common Join Operator
              condition map:
                   Left Outer Join0 to 1
              condition expressions:
                0 {_col0} {_col42} {_col85} {_col88} {_col104}
                1 
              handleSkewJoin: false
              keys:
                0 [class com.tencent.tdw_udf_cloud.hive.udf.generic.GenericUDFIf(class org.apache.hadoop.hive.ql.udf.generic.GenericUDFOPOr(class org.apache.hadoop.hive.ql.udf.generic.GenericUDFOPNull(Column[_col103](), class org.apache.hadoop.hive.ql.udf.generic.GenericUDFOPEqual(class org.apache.hadoop.hive.ql.udf.generic.GenericUDFBridge(Column[_col103](), Const string ()(), class org.apache.hadoop.hive.ql.udf.generic.GenericUDFBridge(Const string null_, class org.apache.hadoop.hive.ql.udf.generic.GenericUDFBridge(class org.apache.hadoop.hive.ql.udf.generic.GenericUDFBridge(class org.apache.hadoop.hive.ql.udf.generic.GenericUDFBridge((), Const int 10000()()(), Column[_col103]()]
                1 [Column[fspid_fundcode]]
              outputColumnNames: _col0, _col42, _col85, _col88, _col104
              Position of Big Table: 0
              Operator:              File Output Operator
                compressed: false
                GlobalTableId: 0
                table:
                  table descs
                    input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                    output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
      Path -> Alias:
        hdfs://<tmp_hive_hdfs_base_path>/20210325/<hdfs_user>_tdwadmin_20210325195415346_29895878_7_699597773/10002 [hdfs://<tmp_hive_hdfs_base_path>/20210325/<hdfs_user>_tdwadmin_20210325195415346_29895878_7_699597773/10002]
      Reduce Operator Tree:
        Operator:        Group By Operator
          aggregations:
                expr: count(DISTINCT KEY._col1:1._col0)
                expr: count(DISTINCT KEY._col1:2._col0)
          keys:
                expr: KEY._col0
                type: string
          mode: mergepartial
          outputColumnNames: _col0, _col1, _col2
          UseNewGroupBy: true
          Operator:          Select Operator
            expressions:
                  expr: 20210324
                  type: int
                  expr: (systimestamp to_char 'yyyymmddhh24miss')
                  type: string
                  expr: _col0
                  type: string
                  expr: _col1
                  type: bigint
                  expr: _col1
                  type: bigint
                  expr: _col2
                  type: bigint
            outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5
            Operator:            File Output Operator
              compressed: false
              GlobalTableId: 0
              table:
                table descs
                  input format: org.apache.hadoop.mapred.TextInputFormat
                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat

  Stage: Stage-0
    Fetch Operator
      limit: 100000000

error

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
submitSql has error: 
org.apache.spark.sql.AnalysisException: nondeterministic expressions are only allowed in
Project, Filter, Aggregate or Window, found:
 ((IF(((hj2.`furl_orig` IS NULL) OR (com.tencent.tdw_udf_cloud.hive.udf.UDFTrim(hj2.`furl_orig`) = '')), com.tencent.tdw_udf_cloud.hive.udf.UDFConcat('null_', com.tencent.tdw_udf_cloud.hive.udf.UDFFloor((org.apache.hadoop.hive.ql.udf.UDFRand() * CAST(10000 AS DOUBLE)))), hj2.`furl_orig`)) = hj1.`fevent_code`)
in operator Join LeftOuter, (if ((isnull(furl_orig#92) || (HiveSimpleUDF#com.tencent.tdw_udf_cloud.hive.udf.UDFTrim(furl_orig#92) = ))) HiveSimpleUDF#com.tencent.tdw_udf_cloud.hive.udf.UDFConcat(null_,HiveSimpleUDF#com.tencent.tdw_udf_cloud.hive.udf.UDFFloor((HiveSimpleUDF#org.apache.hadoop.hive.ql.udf.UDFRand() * cast(10000 as double)))) else furl_orig#92 = fevent_code#132)
               ;;
Aggregate [fuin#49], [20210324 AS fdate#0, HiveSimpleUDF#com.tencent.tdw_udf_cloud.hive.udf.UDFToChar(HiveSimpleUDF#org.apache.hadoop.hive.ql.udf.UDFSysTimestamp(),yyyymmddhh24miss) AS fetl_time#1, fuin#49 AS fuin#2, count(distinct HiveSimpleUDF#com.tencent.tdw_udf_cloud.hive.udf.UDFSubstr(cast(if ((((fevent_time#95L >= 20210318000000) && (fevent_time#95L <= 20210324235959)) && fscn#111 IN (1))) fevent_time#95L else cast(null as bigint) as string),1,8)) AS flabel_25942#3L, count(distinct HiveSimpleUDF#com.tencent.tdw_udf_cloud.hive.udf.UDFSubstr(cast(if ((((fevent_time#95L >= 20210318000000) && (fevent_time#95L <= 20210324235959)) && fscn#111 IN (1))) fevent_time#95L else cast(null as bigint) as string),1,8)) AS flabel_25970#4L, count(distinct HiveSimpleUDF#com.tencent.tdw_udf_cloud.hive.udf.UDFSubstr(cast(if (((((furl_orig#92 = /mb/v5/fund/list/steady.shtml) || (furl_orig#92 = /mb/v4/fundlist/fund_all.shtml)) || (furl_orig#92 = /mb/v4/fundlist/fund_all_v5.shtml)) && ((fevent_time#95L >= 20210318000000) && (fevent_time#95L <= 20210324235959)))) fevent_time#95L else cast(null as bigint) as string),1,8)) AS flabel_25972#5L]
+- Filter ((isnotnull(fuin#49) && NOT (HiveSimpleUDF#com.tencent.tdw_udf_cloud.hive.udf.UDFTrim(fuin#49) = )) && ((fdate#7L >= cast(20210318 as bigint)) && (fdate#7L <= cast(20210324 as bigint))))
   +- Join LeftOuter, (if ((isnull(fspid_fundcode#110) || (HiveSimpleUDF#com.tencent.tdw_udf_cloud.hive.udf.UDFTrim(fspid_fundcode#110) = ))) HiveSimpleUDF#com.tencent.tdw_udf_cloud.hive.udf.UDFConcat(null_,HiveSimpleUDF#com.tencent.tdw_udf_cloud.hive.udf.UDFFloor((HiveSimpleUDF#org.apache.hadoop.hive.ql.udf.UDFRand() * cast(10000 as double)))) else fspid_fundcode#110 = fspid_fundcode#209)
      :- Join LeftOuter, (if ((isnull(furl_orig#92) || (HiveSimpleUDF#com.tencent.tdw_udf_cloud.hive.udf.UDFTrim(furl_orig#92) = ))) HiveSimpleUDF#com.tencent.tdw_udf_cloud.hive.udf.UDFConcat(null_,HiveSimpleUDF#com.tencent.tdw_udf_cloud.hive.udf.UDFFloor((HiveSimpleUDF#org.apache.hadoop.hive.ql.udf.UDFRand() * cast(10000 as double)))) else furl_orig#92 = fevent_code#201)
      :  :- Join LeftOuter, (if ((isnull(furl_orig#92) || (HiveSimpleUDF#com.tencent.tdw_udf_cloud.hive.udf.UDFTrim(furl_orig#92) = ))) HiveSimpleUDF#com.tencent.tdw_udf_cloud.hive.udf.UDFConcat(null_,HiveSimpleUDF#com.tencent.tdw_udf_cloud.hive.udf.UDFFloor((HiveSimpleUDF#org.apache.hadoop.hive.ql.udf.UDFRand() * cast(10000 as double)))) else furl_orig#92 = fevent_code#132)
      :  :  :- SubqueryAlias hj2
      :  :  :  +- MetastoreRelation dml_base, dml_evt_lct_cft_label_factory_mta_access_dd
      :  :  +- BroadcastHint
      :  :     +- SubqueryAlias hj1
      :  :        +- MetastoreRelation dim_base, dim_lct_hq_beacon_report_manage
      :  +- BroadcastHint
      :     +- SubqueryAlias hj3
      :        +- MetastoreRelation dim_base, dim_lct_hq_virtual_event_info
      +- BroadcastHint
         +- SubqueryAlias hj0
            +- MetastoreRelation dim_base, dim_prd_lct_cft_fund_type_conf
1
java.lang.RuntimeException: Map local work failed at org.apache.hadoop.hive.ql.exec.ExecMapper.processOldMapLocalWork(ExecMapper.java:317) at org.apache.hadoop.hive.ql.exec.ExecMapper.map(ExecMapper.java:151) at org.apache.hadoop.mapred.MapRunner.run(MapRunner.java:54) at org.apache.hadoop.mapred.MapTask.runOldMapper(MapTask.java:453) at org.apache.hadoop.mapred.MapTask.run(MapTask.java:343) at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:175) 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:2286) at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:169) Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: java.io.IOException: 没有那个文件或目录 at org.apache.hadoop.hive.ql.exec.persistence.HashMapWrapper.getPersistentHash(HashMapWrapper.java:189) at org.apache.hadoop.hive.ql.exec.persistence.HashMapWrapper.put(HashMapWrapper.java:155) at org.apache.hadoop.hive.ql.exec.MapJoinOperator.process(MapJoinOperator.java:474) at org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:471) at org.apache.hadoop.hive.ql.exec.TableScanOperator.process(TableScanOperator.java:37) at org.apache.hadoop.hive.ql.exec.ExecMapper.processOldMapLocalWork(ExecMapper.java:302) ... 9 more Caused by: java.io.IOException: 没有那个文件或目录 at java.io.UnixFileSystem.createFileExclusively(Native Method) at java.io.File.createTempFile(File.java:2024) at org.apache.hadoop.hive.ql.exec.persistence.HashMapWrapper.getPersistentHash(HashMapWrapper.java:176) ... 14 more	N/A

定位出错误原因是：强制指定了mapjoin，内存溢出了

Hive的自动join策略选择：

由于开启了hive.auto.convert.join，但是实际小表大小是hive.mapjoin.smalltable.filesize（默认25M，小表不会超过25M）。由于使用的是orc压缩，解压缩后可能大小到了250M，存放到内存大小可能就会超过1G。mapjoin的时候，hive orcfile 放到内存中会放大40倍
可以看到JVM Max Heap Size大小为：1013645312 （大约1G）

一句话总结

由于使用了hive.auto.convert.join,对小表进行广播，但是原表是orc的，存放到内存可能膨胀到大于localtask的堆内存大小，导致sql执行失败。

解决措施

方案一

调大localtask的内存，set hive.mapred.local.mem=XX ，默认1G，调大到4G

方案二

直接关表autojoin，将hive.auto.convert.join设置成false