升级conda至Python 3.9的步骤介绍在本文中，我们将教会你如何将conda从Python 3.7升级到Python 3.9。conda是一个流行的包管理器，可以帮助我们管理不同版本的Python及其相关库。通过升级conda，我们可以获得最新的Python版本，以便利用其新特性和增强功能。升级流程下表展示了升级conda至Python 3.9的步骤：步骤 描述1 检查当前conda版本2 创建新的Python 3.9环境3 迁移已安装的包4 删除旧的Python 3.7环境接下来，我们将逐步介绍每个步骤并提供相应的代码示例。步骤1：检查当前conda版本在开始升级之前，我们需要检查当前conda的版本。可以使用以下命令检查：conda --version该命令会显示当前conda的版本号。步骤2：创建新的Python 3.9环境首先，我们需要创建一个新的Python 3.9环境。使用以下命令创建一个名为"python39"的环境，并指定Python版本为3.9：conda create -n python39 python=3.9该命令将在conda中创建一个新的环境，并安装Python 3.9。步骤3：迁移已安装的包在升级过程中，我们需要迁移已安装的包到新的Python 3.9环境。首先，我们需要激活新的环境，然后使用以下命令导出已安装的包列表：`conda activate python39conda list --explicit > packages.txt`该命令将导出已安装的包列表到一个名为"packages.txt"的文件中。接下来，我们需要在新的Python 3.9环境中安装这些包。使用以下命令：`conda install --name python39 --file packages.txt`该命令将根据"packages.txt"文件中的包列表，安装这些包到新的环境中。步骤4：删除旧的Python 3.7环境升级完成后，我们可以删除旧的Python 3.7环境以释放空间。首先，确保当前处于其他环境中，然后使用以下命令删除旧环境：conda remove --name python37 --all请注意，删除环境会删除该环境下所有的包和依赖项，因此请确保已经迁移了所有需要的包到新的Python 3.9环境。以上就是将conda从Python 3.7升级到Python 3.9的完整步骤。结论通过本文，我们提供了将conda升级到Python 3.9的详细步骤。首先，我们检查当前conda版本，然后创建新的Python 3.9环境。接下来，我们迁移已安装的包到新的环境，并最后删除旧的Python 3.7环境。这样，我们就成功升级了conda并获得了最新的Python版本。
import org.apache.hadoop.hive.ql.exec.UDF; import java.util.regex.Matcher; import java.util.regex.Pattern; public class CountChineseCharactersUDF extends UDF { private static final Pattern CHINESE_CHAR_PATTERN = Pattern.compile("[\\u4e00-\\u9fa5]"); public int evaluate(String str) { if (str == null) { return 0; } Matcher matcher = CHINESE_CHAR_PATTERN.matcher(str); int count = 0; while (matcher.find()) { count++; } return count; } public static void main(String[] args) { String s="小时候看泰剧，\"-->\n" + "\n"; System.out.println(new CountChineseCharactersUDF().evaluate(s)); } }
将数组类型的字符串按照指定的key的值进行排序 并输出select user_id, recommend_id content_id, a.title, viewpoint, content_type, audit_status, sort_json_array_and_concat(to_json(COLLECT_LIST(named_struct('pos', pos, 'item', b.title))),'pos','item') AS theme_infos, create_time from ( SELECT user_id, recommend_id, title, viewpoint, content_type, audit_status, nvl(item.topicId, '') topicId, create_time , pos FROM base1.table2 LATERAL VIEW posexplode_outer( from_json( COALESCE(theme_ids, '[]'), 'array<struct<topicId:string>>' ) ) AS pos, item WHERE day = '${day}' and to_date(create_time) >= '2025-01-20' -- and audit_status in (1, 2, 10) GROUP BY user_id, recommend_id, title, viewpoint, content_type, audit_status, topicId, create_time, pos ) a left outer join ( select subject_id, replace(title, '#', '') title from base1.table1 where day = '${day}' ) b on a.topicId = b.subject_id group by user_id, recommend_id, a.title, viewpoint, content_type, audit_status, create_time排序UDF函数import org.apache.hadoop.hive.ql.exec.Description; import org.apache.hadoop.hive.ql.exec.UDF; import org.apache.hadoop.io.Text; import org.json.JSONArray; import org.json.JSONException; import org.json.JSONObject; import java.util.ArrayList; import java.util.Collections; import java.util.Comparator; import java.util.List; @Description(name = "sort_json_array_and_concat", value = "Sort a JSON array by a given key and concatenate another key's values with commas", extended = "SELECT sort_json_array_and_concat('[{\"id\": 2, \"name\": \"Bob\"}, {\"id\": 1, \"name\": \"Alice\"}]', 'id', 'name');") public class SortJsonArrayAndConcatUDF extends UDF { public String evaluate(String jsonArrayStr, String key1, String key2) { if (jsonArrayStr == null || key1 == null || key2 == null) { return null; } try { // 解析输入的 JSON 字符串为 JSONArray JSONArray jsonArray = new JSONArray(jsonArrayStr.toString()); List<JSONObject> jsonObjects = new ArrayList<>(); for (int i = 0; i < jsonArray.length(); i++) { jsonObjects.add(jsonArray.getJSONObject(i)); } // 按照 key1 的值进行排序 Collections.sort(jsonObjects, new Comparator<JSONObject>() { @Override public int compare(JSONObject o1, JSONObject o2) { Object value1 = null; try { value1 = o1.get(key1.toString()); } catch (JSONException e) { e.printStackTrace(); } Object value2 = null; try { value2 = o2.get(key1.toString()); } catch (JSONException e) { e.printStackTrace(); } if (value1 instanceof Comparable && value2 instanceof Comparable) { return ((Comparable) value1).compareTo(value2); } return 0; } }); // 提取 key2 的值并用逗号拼接 StringBuilder result = new StringBuilder(); for (int i = 0; i < jsonObjects.size(); i++) { JSONObject obj = jsonObjects.get(i); if (i > 0) { result.append(","); } result.append(obj.get(key2.toString())); } return result.toString(); } catch (Exception e) { return null; } } }
timestamp格式 转换成 yyyyMMdd字符串select create_time,from_unixtime(unix_timestamp(create_time), 'yyyyMMdd') timestamp格式 转换成秒2024-11-29 15:58:53.798UNIX_TIMESTAMP(group_data_t.create_time)yyyyMMdd格式 月份间隔-- 定义两个日期 SELECT months_between( to_date(concat_ws('-', substr('20250214', 1, 4), substr('20250214', 5, 2), '01')), to_date(concat_ws('-', substr('20241214', 1, 4), substr('20241214', 5, 2), '01')) ) AS month_diff;毫秒时间戳转yyyyMMddfrom_unixtime(cast(cast(successtime as bigint)/1000 as bigint),'yyyyMMdd')='$day' from_unixtime(cast(eid/1000 as bigint),'yyyy-MM-dd') from_unixtime(cast(max(eid)/1000 as bigint),'yyyy-MM-dd HH:mm:ss') yyyyMMdd 转 yyyy-MM-ddfrom_unixtime(unix_timestamp(day,'yyyyMMdd'),'yyyy-MM-dd') as dt留存case when datediff (t2.dt,t1.dt)=1 then t2.passport end
hive sql中SELECT的表是json的 如:CREATE EXTERNAL TABLE `db`.`table1`( `boxsize` bigint COMMENT 'from deserializer', ****) PARTITIONED BY ( `day` string) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' WITH SERDEPROPERTIES ( 'ignore.malformed.json'='true', 'is.datastream.json'='false') STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION 'hdfs://****' 在执行查询时遇到如下报错Caused by: org.apache.hadoop.hive.serde2.SerDeException: Row is not a valid JSON Object - JSONException: Unterminated string at 1401 [character 1402 line 1]解决办法：alter table db.table1 set serdeproperties ("ignore.malformed.json" = "true");
如何在hive中查出的所有字段包裹整json对象
您可以使用Hive中的内置函数to_json()将查询结果包装成一个完整的JSON对象。例如，以下查询将返回一个包含所有字段的JSON对象：SELECT to_json(struct(*)) AS json_obj FROM table_name;其中，struct(*)表示选择表中的所有列，并将它们组合成一个结构体。to_json()函数将该结构体转换为JSON格式，并将其作为名为json_obj的单个字段返回。SELECT to_json(struct(*)) AS json_obj FROM table_name;
hive运行报错2020-04-14 05:59:33,880 Stage-19 map = 11%, reduce = 0%, Cumulative CPU 310.82 sec 2020-04-14 05:59:34,111 Stage-15 map = 100%, reduce = 100%, Cumulative CPU 1259.2 sec org.apache.hadoop.yarn.exceptions.YarnRuntimeException: java.lang.InterruptedException: sleep interrupted at org.apache.hadoop.mapred.ClientServiceDelegate.invoke(ClientServiceDelegate.java:355) at org.apache.hadoop.mapred.ClientServiceDelegate.getJobStatus(ClientServiceDelegate.java:459) at org.apache.hadoop.mapred.YARNRunner.getJobStatus(YARNRunner.java:578) at org.apache.hadoop.mapreduce.Job$1.run(Job.java:323) at org.apache.hadoop.mapreduce.Job$1.run(Job.java:320) 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:1698) at org.apache.hadoop.mapreduce.Job.updateStatus(Job.java:320) at org.apache.hadoop.mapreduce.Job.ensureFreshStatus(Job.java:310) at org.apache.hadoop.mapreduce.Job.mapProgress(Job.java:552) at org.apache.hadoop.mapred.JobClient$NetworkedJob.mapProgress(JobClient.java:237) at org.apache.hadoop.hive.ql.exec.mr.HadoopJobExecHelper.updateCounters(HadoopJobExecHelper.java:89) at org.apache.hadoop.hive.ql.exec.mr.HadoopJobExecHelper.progress(HadoopJobExecHelper.java:320) at org.apache.hadoop.hive.ql.exec.mr.HadoopJobExecHelper.progress(HadoopJobExecHelper.java:556 ) at org.apache.hadoop.hive.ql.exec.mr.ExecDriver.execute(ExecDriver.java:438) at org.apache.hadoop.hive.ql.exec.mr.MapRedTask.execute(MapRedTask.java:143) at org.apache.hadoop.hive.ql.exec.Task.executeTask(Task.java:197) at org.apache.hadoop.hive.ql.exec.TaskRunner.runSequential(TaskRunner.java:108) at org.apache.hadoop.hive.ql.exec.TaskRunner.run(TaskRunner.java:86) Caused by: java.lang.InterruptedException: sleep interrupted at java.lang.Thread.sleep(Native Method) at org.apache.hadoop.mapred.ClientServiceDelegate.invoke(ClientServiceDelegate.java:352) ... 19 more Job Submission failed with exception 'org.apache.hadoop.yarn.exceptions.YarnRuntimeException(java.lang.InterruptedException: sleep interrupted)' 2020-04-14 05:59:35,944 Stage-19 map = 100%, reduce = 100% 2020-04-14 05:59:36,098 Stage-20 map = 100%, reduce = 83%, Cumulative CPU 115.39 sec MapReduce Total cumulative CPU time: 5 minutes 10 seconds 820 msec 2020-04-14 05:59:37,120 Stage-20 map = 100%, reduce = 100%, Cumulative CPU 118.76 sec org.apache.hadoop.yarn.exceptions.YarnRuntimeException: java.lang.InterruptedException: sleep interrupted 后来看yarn后台的jobid的具体信息是 recuce的时候报错jobhistory/joblogs/hadoop6549.jd.163.org:45454/container_e86_1586760035227_38882_01_000010/attempt_1586760035227_38882_r_000002_0/portal/syslog?start=0************************************************************/ 2020-04-14 05:44:20,626 INFO [main] org.apache.hadoop.conf.Configuration.deprecation: session.id is deprecated. Instead, use dfs.metrics.session-id 2020-04-14 05:44:21,310 INFO [main] org.apache.hadoop.mapred.Task: Using ResourceCalculatorProcessTree : [ ] 2020-04-14 05:44:21,446 INFO [main] org.apache.hadoop.mapred.ReduceTask: Using ShuffleConsumerPlugin: org.apache.hadoop.mapreduce.task.reduce.Shuffle@5827af16 2020-04-14 05:44:29,736 INFO [file] org.apache.hadoop.metrics2.sink.Slf4jSink: 1586814269732 ugi.UgiMetrics: Context=ugi, Hostname=hadoop6549.jd.163.org, LoginSuccessNumOps=0, LoginSuccessAvgTime=0.0, LoginFailureNumOps=0, LoginFailureAvgTime=0.0, GetGroupsNumOps=0, GetGroupsAvgTime=0.0, RenewalFailuresTotal=0, RenewalFailures=01586814269732 jvm.JvmMetrics: Context=jvm, ProcessName=jvm_1586760035227_38882_r_94557999988746, SessionId=, Hostname=hadoop6549.jd.163.org, MemNonHeapUsedM=38.19465, MemNonHeapCommittedM=38.6875, MemNonHeapMaxM=-1.0, MemHeapUsedM=3440.3354, MemHeapCommittedM=3913.125, MemHeapMaxM=4879.75, MemMaxM=4879.75, GcCountCopy=4, GcTimeMillisCopy=2891, GcCountMarkSweepCompact=1, GcTimeMillisMarkSweepCompact=121, GcCount=5, GcTimeMillis=3012, ThreadsNew=0, ThreadsRunnable=6, ThreadsBlocked=0, ThreadsWaiting=11, ThreadsTimedWaiting=5, ThreadsTerminated=0, LogFatal=0, LogError=0, LogWarn=3, LogInfo=151586814269733 metricssystem.MetricsSystem: Context=metricssystem, Hostname=hadoop6549.jd.163.org, NumActiveSources=2, NumAllSources=2, NumActiveSinks=1, NumAllSinks=0, Sink_fileNumOps=0, Sink_fileAvgTime=0.0, Sink_fileDropped=0, Sink_fileQsize=0, SnapshotNumOps=2, SnapshotAvgTime=0.5, PublishNumOps=0, PublishAvgTime=0.0, DroppedPubAll=0 2020-04-14 05:44:32,370 WARN [main] org.apache.hadoop.mapred.YarnChild: Exception running child : org.apache.hadoop.mapreduce.task.reduce.Shuffle$ShuffleError: error in shuffle in fetcher#1 at org.apache.hadoop.mapreduce.task.reduce.Shuffle.run(Shuffle.java:134) at org.apache.hadoop.mapred.ReduceTask.run(ReduceTask.java:376) at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:177) 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:1893) at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:171) Caused by: java.lang.OutOfMemoryError: Java heap space at org.apache.hadoop.io.BoundedByteArrayOutputStream.<init>(BoundedByteArrayOutputStream.java:56) at org.apache.hadoop.io.BoundedByteArrayOutputStream.<init>(BoundedByteArrayOutputStream.java:46) at org.apache.hadoop.mapreduce.task.reduce.InMemoryMapOutput.<init>(InMemoryMapOutput.java:59) at org.apache.hadoop.mapreduce.task.reduce.MergeManagerImpl.unconditionalReserve(MergeManagerImpl.java:312) at org.apache.hadoop.mapreduce.task.reduce.MergeManagerImpl.reserve(MergeManagerImpl.java:302) at org.apache.hadoop.mapreduce.task.reduce.Fetcher.copyMapOutput(Fetcher.java:532) at org.apache.hadoop.mapreduce.task.reduce.Fetcher.copyFromHost(Fetcher.java:342) at org.apache.hadoop.mapreduce.task.reduce.Fetcher.run(Fetcher.java:198) 2020-04-14 05:44:32,381 INFO [main] org.apache.hadoop.mapred.Task: Running cleanup for the task 2020-04-14 05:44:32,486 INFO [main] org.apache.hadoop.metrics2.impl.MetricsSystemImpl: Stopping ReduceTask metrics system... 2020-04-14 05:44:32,486 INFO [file] org.apache.hadoop.metrics2.impl.MetricsSinkAdapter: file thread interrupted. 2020-04-14 05:44:32,487 INFO [main] org.apache.hadoop.metrics2.impl.MetricsSystemImpl: ReduceTask metrics system stopped. 2020-04-14 05:44:32,487 INFO [main] org.apache.hadoop.metrics2.impl.MetricsSystemImpl: ReduceTask metrics system shutdown complete. 解决办法set yarn.app.mapreduce.am.resource.mb=6072; set mapreduce.map.memory.mb=4096; set mapreduce.reduce.memory.mb=7036; set mapreduce.map.java.opt=-Xmx3036M; set mapreduce.reduce.java.opts=-Xmx5048M; set yarn.app.mapreduce.am.command-opts=-Xmx4228M; set mapreduce.reduce.shuffle.memory.limit.percent=0.06;
今天突然任务报错.运行到stage-7的时候报错。知道肯定是数据倾斜导致的,就是查不到具体哪里的问题。看yarn job的log也没发现有用的信息。https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Explain关于Hive执行计划简述 一般执行计划有两个部分： stage dependencies 各个stage之间的依赖性 stage plan 各个stage的执行计划 一个stage并不一定是一个MR，有可能是Fetch Operator，也有可能是Move Operator。 一个MR的执行计划分为两个部分： Map Operator Tree MAP端的执行计划 Reduce Operator Tree Reduce端的执行计划 一些常见的Operator： TableScan 读取数据，常见的属性 alias Select Operator 选取操作 Group By Operator 分组聚合， 常见的属性 aggregations、mode ， 当没有keys属性时只有一个分组。 Reduce Output Operator 输出结果给Reduce , 常见的属性 sort order Fetch Operator 客户端获取数据 ， 常见属性 limit 常见的属性的取值及含义： aggregations 用在Group By Operator中 count()计数 mode 用在Group By Operator中 hash 待定 mergepartial 合并部分聚合结果 final sort order 用于Reduce Output Operator中 + 正序排序 不排序 ++按两列正序排序，如果有两列 +- 正反排序，如果有两列 -反向排序 如此类推后来就通过explain看下执行计划。STAGE DEPENDENCIES: Stage-1 is a root stage Stage-2 depends on stages: Stage-1 Stage-3 depends on stages: Stage-2, Stage-17, Stage-29 Stage-37 depends on stages: Stage-3 Stage-28 depends on stages: Stage-37 Stage-27 depends on stages: Stage-21, Stage-28 , consists of Stage-36, Stage-5 Stage-36 has a backup stage: Stage-5 Stage-26 depends on stages: Stage-36 Stage-35 depends on stages: Stage-5, Stage-26 Stage-7 depends on stages: Stage-35 Stage-13 depends on stages: Stage-7 , consists of Stage-10, Stage-9, Stage-11 Stage-10 Stage-0 depends on stages: Stage-10, Stage-9, Stage-12 Stage-8 depends on stages: Stage-0 Stage-9 Stage-11 Stage-12 depends on stages: Stage-11 Stage-5 Stage-32 is a root stage , consists of Stage-16 Stage-16 Stage-30 depends on stages: Stage-16 , consists of Stage-38, Stage-17 Stage-38 has a backup stage: Stage-17 Stage-29 depends on stages: Stage-38 Stage-17 Stage-34 is a root stage , consists of Stage-21 Stage-21 STAGE PLANS: Stage: Stage-1 Map Reduce Map Operator Tree: TableScan alias: ddm_content_doc_info_logic_day Statistics: Num rows: 502133848 Data size: 209584131934 Basic stats: COMPLETE Column stats: NONE Filter Operator predicate: (((substr(docid, 9, 2)) IN ('04', '02', '03', '07', '00') or (substr(docid, 9, 4) = '9001')) and (topicid = substr(docid, 9, 8))) (type: boolean) Statistics: Num rows: 251066924 Data size: 104792065967 Basic stats: COMPLETE Column stats: NONE Select Operator expressions: topicid (type: string), docid (type: string), title (type: string), digest (type: string), from_unixtime(UDFToInteger((ptime / 1000))) (type: string), source (type: string), url (type: string), search (type: string), finearticle (type: string), category (type: string), quality (type: string), dkeys (type: string), interests (type: string), professional (type: string), body (type: string), buloid (type: string), ispic (type: string), iscomment (type: string), doc_del (type: int), channelid (type: string) outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16, _col17, _col18, _col19 Statistics: Num rows: 251066924 Data size: 104792065967 Basic stats: COMPLETE Column stats: NONE Reduce Output Operator key expressions: _col1 (type: string) sort order: + Map-reduce partition columns: _col1 (type: string) Statistics: Num rows: 251066924 Data size: 104792065967 Basic stats: COMPLETE Column stats: NONE value expressions: _col0 (type: string), _col2 (type: string), _col3 (type: string), _col4 (type: string), _col5 (type: string), _col6 (type: string), _col7 (type: string), _col8 (type: string), _col9 (type: string), _col10 (type: string), _col11 (type: string), _col12 (type: string), _col13 (type: string), _col14 (type: string), _col15 (type: string), _col16 (type: string), _col17 (type: string), _col18 (type: int), _col19 (type: string) TableScan alias: ddm_content_netease_audit_doc_base_day Statistics: Num rows: 251986855 Data size: 588419341799 Basic stats: COMPLETE Column stats: NONE Filter Operator predicate: (doctype) IN ('40', '43', '44', '45', '46', '48', '49', '10', '50') (type: boolean) Statistics: Num rows: 125993428 Data size: 294209672067 Basic stats: COMPLETE Column stats: NONE Select Operator expressions: topicid (type: string), docid (type: string), title (type: string), publish_time (type: string), split(source, '&&')[0] (type: string), doc_url (type: string), regexp_replace(category, ',', '\') (type: string), quality (type: float), keywords (type: string), interests (type: string), picnum (type: tinyint), delstatus (type: tinyint), channel (type: string), doctype (type: tinyint) outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13 Statistics: Num rows: 125993428 Data size: 294209672067 Basic stats: COMPLETE Column stats: NONE Reduce Output Operator key expressions: _col1 (type: string) sort order: + Map-reduce partition columns: _col1 (type: string) Statistics: Num rows: 125993428 Data size: 294209672067 Basic stats: COMPLETE Column stats: NONE value expressions: _col0 (type: string), _col2 (type: string), _col3 (type: string), _col4 (type: string), _col5 (type: string), _col6 (type: string), _col7 (type: float), _col8 (type: string), _col9 (type: string), _col10 (type: tinyint), _col11 (type: tinyint), _col12 (type: string), _col13 (type: tinyint) Reduce Operator Tree: Join Operator condition map: Outer Join 0 to 1 keys: 0 _col1 (type: string) 1 _col1 (type: string) outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16, _col17, _col18, _col19, _col20, _col21, _col22, _col23, _col24, _col25, _col26, _col27, _col28, _col29, _col30, _col31, _col32, _col33 Statistics: Num rows: 276173622 Data size: 115271275062 Basic stats: COMPLETE Column stats: NONE Select Operator expressions: COALESCE(_col0,_col20) (type: string), COALESCE(_col1,_col21) (type: string), COALESCE(_col2,_col22) (type: string), _col3 (type: string), COALESCE(_col23,_col4) (type: string), COALESCE(_col24,_col5) (type: string), COALESCE(_col25,_col6) (type: string), _col7 (type: string), _col8 (type: string), COALESCE(_col9,_col26) (type: string), COALESCE(_col10,_col27) (type: string), COALESCE(_col11,_col28) (type: string), COALESCE(_col12,_col29) (type: string), _col13 (type: string), _col14 (type: string), _col15 (type: string), _col16 (type: string), _col30 (type: tinyint), _col17 (type: string), COALESCE(_col18,_col31) (type: int), COALESCE(_col19,_col32) (type: string), _col33 (type: tinyint) outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16, _col17, _col18, _col19, _col20, _col21 Statistics: Num rows: 276173622 Data size: 115271275062 Basic stats: COMPLETE Column stats: NONE File Output Operator compressed: false table: input format: org.apache.hadoop.mapred.SequenceFileInputFormat output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe Stage: Stage-2 Map Reduce Map Operator Tree: TableScan Reduce Output Operator key expressions: _col1 (type: string) sort order: + Map-reduce partition columns: _col1 (type: string) Statistics: Num rows: 276173622 Data size: 115271275062 Basic stats: COMPLETE Column stats: NONE value expressions: _col0 (type: string), _col2 (type: string), _col3 (type: string), _col4 (type: string), _col5 (type: string), _col6 (type: string), _col7 (type: string), _col8 (type: string), _col9 (type: string), _col10 (type: string), _col11 (type: string), _col12 (type: string), _col13 (type: string), _col14 (type: string), _col15 (type: string), _col16 (type: string), _col17 (type: tinyint), _col18 (type: string), _col19 (type: int), _col20 (type: string), _col21 (type: tinyint) TableScan alias: ddm_content_netease_article_day Statistics: Num rows: 154362377 Data size: 57131042127 Basic stats: COMPLETE Column stats: NONE Select Operator expressions: id (type: string), title (type: string), description (type: string), publish_time (type: string), source (type: string), url (type: string), '40' (type: string), body (type: string) outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7 Statistics: Num rows: 154362377 Data size: 57131042127 Basic stats: COMPLETE Column stats: NONE Reduce Output Operator key expressions: _col0 (type: string) sort order: + Map-reduce partition columns: _col0 (type: string) Statistics: Num rows: 154362377 Data size: 57131042127 Basic stats: COMPLETE Column stats: NONE value expressions: _col1 (type: string), _col2 (type: string), _col3 (type: string), _col4 (type: string), _col5 (type: string), _col6 (type: string), _col7 (type: string) Reduce Operator Tree: Join Operator condition map: Outer Join 0 to 1 keys: 0 _col1 (type: string) 1 _col0 (type: string) outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16, _col17, _col18, _col19, _col20, _col21, _col22, _col23, _col24, _col25, _col26, _col27, _col28, _col29 Statistics: Num rows: 303790990 Data size: 126798405316 Basic stats: COMPLETE Column stats: NONE Select Operator expressions: _col0 (type: string), COALESCE(_col1,_col22) (type: string), COALESCE(_col2,_col23) (type: string), COALESCE(_col3,_col24) (type: string), COALESCE(_col4,_col25) (type: string), COALESCE(_col5,_col26) (type: string), COALESCE(_col6,_col27) (type: string), _col7 (type: string), _col8 (type: string), _col9 (type: string), _col10 (type: string), _col11 (type: string), _col12 (type: string), _col13 (type: string), COALESCE(_col14,_col29) (type: string), _col15 (type: string), _col16 (type: string), _col17 (type: tinyint), _col18 (type: string), _col19 (type: int), _col20 (type: string), COALESCE(_col21,_col28) (type: string) outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16, _col17, _col18, _col19, _col20, _col21 Statistics: Num rows: 303790990 Data size: 126798405316 Basic stats: COMPLETE Column stats: NONE File Output Operator compressed: false table: input format: org.apache.hadoop.mapred.SequenceFileInputFormat output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe Stage: Stage-3 Map Reduce Map Operator Tree: TableScan Reduce Output Operator key expressions: _col1 (type: string) sort order: + Map-reduce partition columns: _col1 (type: string) Statistics: Num rows: 303790990 Data size: 126798405316 Basic stats: COMPLETE Column stats: NONE value expressions: _col0 (type: string), _col2 (type: string), _col3 (type: string), _col4 (type: string), _col5 (type: string), _col6 (type: string), _col7 (type: string), _col8 (type: string), _col9 (type: string), _col10 (type: string), _col11 (type: string), _col12 (type: string), _col13 (type: string), _col14 (type: string), _col15 (type: string), _col16 (type: string), _col17 (type: tinyint), _col18 (type: string), _col19 (type: int), _col20 (type: string), _col21 (type: string) TableScan Reduce Output Operator key expressions: _col0 (type: string) sort order: + Map-reduce partition columns: _col0 (type: string) Statistics: Num rows: 277185546 Data size: 647261290007 Basic stats: COMPLETE Column stats: NONE value expressions: _col1 (type: string) Reduce Operator Tree: Join Operator condition map: Outer Join 0 to 1 keys: 0 _col1 (type: string) 1 _col0 (type: string) outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16, _col17, _col18, _col19, _col20, _col21, _col22, _col23 Statistics: Num rows: 334170096 Data size: 139478248870 Basic stats: COMPLETE Column stats: NONE Select Operator expressions: _col0 (type: string), COALESCE(_col1,_col22) (type: string), _col2 (type: string), _col3 (type: string), _col4 (type: string), _col5 (type: string), _col6 (type: string), _col7 (type: string), _col8 (type: string), _col9 (type: string), _col10 (type: string), _col11 (type: string), _col12 (type: string), _col13 (type: string), _col14 (type: string), _col15 (type: string), _col16 (type: string), _col17 (type: tinyint), _col18 (type: string), _col19 (type: int), _col20 (type: string), COALESCE(_col23,_col21) (type: string) outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16, _col17, _col18, _col19, _col20, _col21 Statistics: Num rows: 334170096 Data size: 139478248870 Basic stats: COMPLETE Column stats: NONE File Output Operator compressed: false table: input format: org.apache.hadoop.mapred.SequenceFileInputFormat output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe Stage: Stage-37 Map Reduce Local Work Alias -> Map Local Tables: t:b:ddm_content_original_column_d Fetch Operator limit: -1 t:d:ddm_content_local_city Fetch Operator limit: -1 Alias -> Map Local Operator Tree: t:b:ddm_content_original_column_d TableScan alias: ddm_content_original_column_d Statistics: Num rows: 6 Data size: 1273 Basic stats: COMPLETE Column stats: NONE Select Operator expressions: topicid (type: string), colname (type: string) outputColumnNames: _col0, _col1 Statistics: Num rows: 6 Data size: 1273 Basic stats: COMPLETE Column stats: NONE HashTable Sink Operator keys: 0 _col0 (type: string) 1 _col0 (type: string) 2 _col0 (type: string) t:d:ddm_content_local_city TableScan alias: ddm_content_local_city Statistics: Num rows: 1652 Data size: 452648 Basic stats: COMPLETE Column stats: NONE Select Operator expressions: local_city_id (type: string), local_city_name (type: string) outputColumnNames: _col0, _col1 Statistics: Num rows: 1652 Data size: 452648 Basic stats: COMPLETE Column stats: NONE HashTable Sink Operator keys: 0 _col0 (type: string) 1 _col0 (type: string) 2 _col0 (type: string) Stage: Stage-28 Map Reduce Map Operator Tree: TableScan Map Join Operator condition map: Left Outer Join0 to 1 Left Outer Join0 to 2 keys: 0 _col0 (type: string) 1 _col0 (type: string) 2 _col0 (type: string) outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16, _col17, _col18, _col19, _col20, _col21, _col22, _col23, _col25 Statistics: Num rows: 735174227 Data size: 306852154164 Basic stats: COMPLETE Column stats: NONE File Output Operator compressed: false table: input format: org.apache.hadoop.mapred.SequenceFileInputFormat output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe Local Work: Map Reduce Local Work Stage: Stage-27 Conditional Operator Stage: Stage-36 Map Reduce Local Work Alias -> Map Local Tables: t:$INTNAME1 Fetch Operator limit: -1 Alias -> Map Local Operator Tree: t:$INTNAME1 TableScan HashTable Sink Operator keys: 0 _col1 (type: string) 1 _col0 (type: string) Stage: Stage-26 Map Reduce Map Operator Tree: TableScan Map Join Operator condition map: Left Outer Join0 to 1 keys: 0 _col1 (type: string) 1 _col0 (type: string) outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16, _col17, _col18, _col19, _col20, _col21, _col22, _col23, _col25, _col27, _col28, _col29, _col30, _col31, _col32, _col33, _col34, _col35, _col36, _col37, _col38 Statistics: Num rows: 808691667 Data size: 337537376896 Basic stats: COMPLETE Column stats: NONE File Output Operator compressed: false table: input format: org.apache.hadoop.mapred.SequenceFileInputFormat output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe Local Work: Map Reduce Local Work Stage: Stage-35 Map Reduce Local Work Alias -> Map Local Tables: t:e:ddm_content_channel_day Fetch Operator limit: -1 Alias -> Map Local Operator Tree: t:e:ddm_content_channel_day TableScan alias: ddm_content_channel_day Statistics: Num rows: 32 Data size: 6460 Basic stats: COMPLETE Column stats: NONE Select Operator expressions: channelid (type: string), channelname (type: string) outputColumnNames: _col0, _col1 Statistics: Num rows: 32 Data size: 6460 Basic stats: COMPLETE Column stats: NONE HashTable Sink Operator keys: 0 _col20 (type: string) 1 _col0 (type: string) Stage: Stage-7 Map Reduce Map Operator Tree: TableScan Map Join Operator condition map: Left Outer Join0 to 1 keys: 0 _col20 (type: string) 1 _col0 (type: string) outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16, _col17, _col18, _col19, _col21, _col22, _col23, _col25, _col27, _col28, _col29, _col30, _col31, _col32, _col33, _col34, _col35, _col36, _col37, _col38, _col40 Statistics: Num rows: 889560852 Data size: 371291122633 Basic stats: COMPLETE Column stats: NONE Reduce Output Operator key expressions: _col1 (type: string), _col4 (type: string) sort order: +- Map-reduce partition columns: _col1 (type: string) Statistics: Num rows: 889560852 Data size: 371291122633 Basic stats: COMPLETE Column stats: NONE value expressions: _col0 (type: string), _col2 (type: string), _col3 (type: string), _col5 (type: string), _col6 (type: string), _col7 (type: string), _col8 (type: string), _col9 (type: string), _col10 (type: string), _col11 (type: string), _col12 (type: string), _col13 (type: string), _col14 (type: string), _col15 (type: string), _col16 (type: string), _col17 (type: tinyint), _col18 (type: string), _col19 (type: int), _col21 (type: string), _col22 (type: string), _col23 (type: string), _col25 (type: string), _col27 (type: string), _col28 (type: string), _col29 (type: string), _col30 (type: string), _col31 (type: string), _col32 (type: string), _col33 (type: string), _col34 (type: int), _col35 (type: int), _col36 (type: int), _col37 (type: string), _col38 (type: int), _col40 (type: string) Local Work: Map Reduce Local Work Reduce Operator Tree: Select Operator expressions: VALUE._col0 (type: string), KEY.reducesinkkey0 (type: string), VALUE._col1 (type: string), VALUE._col2 (type: string), KEY.reducesinkkey1 (type: string), VALUE._col3 (type: string), VALUE._col4 (type: string), VALUE._col5 (type: string), VALUE._col6 (type: string), VALUE._col7 (type: string), VALUE._col8 (type: string), VALUE._col9 (type: string), VALUE._col10 (type: string), VALUE._col11 (type: string), VALUE._col12 (type: string), VALUE._col13 (type: string), VALUE._col14 (type: string), VALUE._col15 (type: tinyint), VALUE._col16 (type: string), VALUE._col17 (type: int), VALUE._col19 (type: string), VALUE._col20 (type: string), VALUE._col21 (type: string), VALUE._col23 (type: string), VALUE._col25 (type: string), VALUE._col26 (type: string), VALUE._col27 (type: string), VALUE._col28 (type: string), VALUE._col29 (type: string), VALUE._col30 (type: string), VALUE._col31 (type: string), VALUE._col32 (type: int), VALUE._col33 (type: int), VALUE._col34 (type: int), VALUE._col35 (type: string), VALUE._col36 (type: int), VALUE._col38 (type: string) outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16, _col17, _col18, _col19, _col21, _col22, _col23, _col25, _col27, _col28, _col29, _col30, _col31, _col32, _col33, _col34, _col35, _col36, _col37, _col38, _col40 Statistics: Num rows: 889560852 Data size: 371291122633 Basic stats: COMPLETE Column stats: NONE PTF Operator Function definitions: Input definition input alias: ptf_0 output shape: _col0: string, _col1: string, _col2: string, _col3: string, _col4: string, _col5: string, _col6: string, _col7: string, _col8: string, _col9: string, _col10: string, _col11: string, _col12: string, _col13: string, _col14: string, _col15: string, _col16: string, _col17: tinyint, _col18: string, _col19: int, _col21: string, _col22: string, _col23: string, _col25: string, _col27: string, _col28: string, _col29: string, _col30: string, _col31: string, _col32: string, _col33: string, _col34: int, _col35: int, _col36: int, _col37: string, _col38: int, _col40: string type: WINDOWING Windowing table definition input alias: ptf_1 name: windowingtablefunction order by: _col4 DESC NULLS LAST partition by: _col1 raw input shape: window functions: window function definition alias: row_number_window_0 name: row_number window function: GenericUDAFRowNumberEvaluator window frame: PRECEDING(MAX)~FOLLOWING(MAX) isPivotResult: true Statistics: Num rows: 889560852 Data size: 371291122633 Basic stats: COMPLETE Column stats: NONE Filter Operator predicate: (row_number_window_0 = 1) (type: boolean) Statistics: Num rows: 444780426 Data size: 185645561316 Basic stats: COMPLETE Column stats: NONE Select Operator expressions: _col0 (type: string), _col1 (type: string), _col2 (type: string), COALESCE(_col3,_col28) (type: string), _col4 (type: string), _col5 (type: string), _col6 (type: string), _col7 (type: string), _col8 (type: string), _col9 (type: string), _col10 (type: string), _col11 (type: string), _col12 (type: string), _col13 (type: string), COALESCE(_col14,_col29) (type: string), _col15 (type: string), _col16 (type: string), UDFToString(_col17) (type: string), _col18 (type: string), UDFToString(_col19) (type: string), _col30 (type: string), _col31 (type: string), _col32 (type: string), UDFToString(_col34) (type: string), UDFToString(_col35) (type: string), UDFToString(_col36) (type: string), _col37 (type: string), UDFToString(_col38) (type: string), CASE WHEN (((_col21 = '40') or (_col21 = '50') or (_col21 = '40-1'))) THEN ('网易号') WHEN (((_col21) IN ('43', '45', '46', '49') or (substr(_col1, 9, 4) = '9001'))) THEN ('抓取') WHEN (((substr(_col1, 9, 2) = '04') or (_col21 = '10'))) THEN ('本地') WHEN (_col22 is not null) THEN ('原创') WHEN ((substr(_col1, 9, 2)) IN ('02', '03', '07', '00')) THEN ('编辑') WHEN ((_col21 = '48')) THEN ('合作媒体') WHEN ((_col21 = '44')) THEN ('有道分享') END (type: string), CASE WHEN (((_col21 = '40') or (_col21 = '50') or (_col21 = '40-1'))) THEN (_col34) WHEN ((_col21 = '43')) THEN ('灰抓今日头条文章（无版权）') WHEN ((_col21 = '45')) THEN ('灰抓微信（无版权）') WHEN ((_col21 = '46')) THEN ('定向增量（无版权）') WHEN (((substr(_col1, 9, 4) = '9001') or (_col21 = '49'))) THEN ('段子抓取') WHEN (((substr(_col1, 9, 2) = '04') or (_col21 = '10'))) THEN (_col25) WHEN (_col22 is not null) THEN (_col23) WHEN ((substr(_col1, 9, 2)) IN ('02', '03', '07', '00')) THEN (_col40) WHEN ((_col21 = '48')) THEN ('合作媒体') WHEN ((_col21 = '44')) THEN ('有道分享') END (type: string), _col21 (type: string), _col27 (type: string), _col33 (type: string) outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16, _col17, _col18, _col19, _col20, _col21, _col22, _col23, _col24, _col25, _col26, _col27, _col28, _col29, _col30, _col31, _col32 Statistics: Num rows: 444780426 Data size: 185645561316 Basic stats: COMPLETE Column stats: NONE File Output Operator compressed: false Statistics: Num rows: 444780426 Data size: 185645561316 Basic stats: COMPLETE Column stats: NONE table: input format: org.apache.hadoop.hive.ql.io.orc.OrcInputFormat output format: org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat serde: org.apache.hadoop.hive.ql.io.orc.OrcSerde name: portal.ddm_content_doc_day Stage: Stage-13 Conditional Operator Stage: Stage-10 Move Operator files: hdfs directory: true destination: hdfs://hz-cluster9/tmp/hive/public/.hive-staging_hive_2019-07-20_07-47-52_070_1672411049055087804-1/-ext-10000 Stage: Stage-0 Move Operator tables: partition: day 20190719 replace: true table: input format: org.apache.hadoop.hive.ql.io.orc.OrcInputFormat output format: org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat serde: org.apache.hadoop.hive.ql.io.orc.OrcSerde name: portal.ddm_content_doc_day Stage: Stage-8 Stats-Aggr Operator Stage: Stage-9 Map Reduce Map Operator Tree: TableScan File Output Operator compressed: false table: input format: org.apache.hadoop.hive.ql.io.orc.OrcInputFormat output format: org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat serde: org.apache.hadoop.hive.ql.io.orc.OrcSerde name: portal.ddm_content_doc_day Stage: Stage-11 Map Reduce Map Operator Tree: TableScan File Output Operator compressed: false table: input format: org.apache.hadoop.hive.ql.io.orc.OrcInputFormat output format: org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat serde: org.apache.hadoop.hive.ql.io.orc.OrcSerde name: portal.ddm_content_doc_day Stage: Stage-12 Move Operator files: hdfs directory: true destination: hdfs://hz-cluster9/tmp/hive/public/.hive-staging_hive_2019-07-20_07-47-52_070_1672411049055087804-1/-ext-10000 Stage: Stage-5 Map Reduce Map Operator Tree: TableScan Reduce Output Operator key expressions: _col1 (type: string) sort order: + Map-reduce partition columns: _col1 (type: string) Statistics: Num rows: 735174227 Data size: 306852154164 Basic stats: COMPLETE Column stats: NONE value expressions: _col0 (type: string), _col2 (type: string), _col3 (type: string), _col4 (type: string), _col5 (type: string), _col6 (type: string), _col7 (type: string), _col8 (type: string), _col9 (type: string), _col10 (type: string), _col11 (type: string), _col12 (type: string), _col13 (type: string), _col14 (type: string), _col15 (type: string), _col16 (type: string), _col17 (type: tinyint), _col18 (type: string), _col19 (type: int), _col20 (type: string), _col21 (type: string), _col22 (type: string), _col23 (type: string), _col25 (type: string) TableScan Reduce Output Operator key expressions: _col0 (type: string) sort order: + Map-reduce partition columns: _col0 (type: string) Statistics: Num rows: 169798618 Data size: 62844147701 Basic stats: COMPLETE Column stats: NONE value expressions: _col1 (type: string), _col2 (type: string), _col3 (type: string), _col4 (type: string), _col5 (type: string), _col6 (type: string), _col7 (type: string), _col8 (type: int), _col9 (type: int), _col10 (type: int), _col11 (type: string), _col12 (type: int) Reduce Operator Tree: Join Operator condition map: Left Outer Join0 to 1 keys: 0 _col1 (type: string) 1 _col0 (type: string) outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13, _col14, _col15, _col16, _col17, _col18, _col19, _col20, _col21, _col22, _col23, _col25, _col27, _col28, _col29, _col30, _col31, _col32, _col33, _col34, _col35, _col36, _col37, _col38 Statistics: Num rows: 808691667 Data size: 337537376896 Basic stats: COMPLETE Column stats: NONE File Output Operator compressed: false table: input format: org.apache.hadoop.mapred.SequenceFileInputFormat output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe Stage: Stage-32 Conditional Operator Stage: Stage-16 Map Reduce Map Operator Tree: TableScan alias: ddm_content_netease_article_day Statistics: Num rows: 154362377 Data size: 57131042127 Basic stats: COMPLETE Column stats: NONE Select Operator expressions: id (type: string) outputColumnNames: _col0 Statistics: Num rows: 154362377 Data size: 57131042127 Basic stats: COMPLETE Column stats: NONE Reduce Output Operator key expressions: _col0 (type: string) sort order: + Map-reduce partition columns: _col0 (type: string) Statistics: Num rows: 154362377 Data size: 57131042127 Basic stats: COMPLETE Column stats: NONE TableScan alias: ddm_content_netease_audit_doc_base_day Statistics: Num rows: 251986855 Data size: 588419341799 Basic stats: COMPLETE Column stats: NONE Filter Operator predicate: (doctype) IN ('40', '43', '44', '45', '46', '48', '49', '10', '50') (type: boolean) Statistics: Num rows: 125993428 Data size: 294209672067 Basic stats: COMPLETE Column stats: NONE Select Operator expressions: docid (type: string) outputColumnNames: _col0 Statistics: Num rows: 125993428 Data size: 294209672067 Basic stats: COMPLETE Column stats: NONE Reduce Output Operator key expressions: _col0 (type: string) sort order: + Map-reduce partition columns: _col0 (type: string) Statistics: Num rows: 125993428 Data size: 294209672067 Basic stats: COMPLETE Column stats: NONE Reduce Operator Tree: Join Operator condition map: Left Outer Join0 to 1 keys: 0 _col0 (type: string) 1 _col0 (type: string) outputColumnNames: _col0, _col1 Statistics: Num rows: 169798618 Data size: 62844147701 Basic stats: COMPLETE Column stats: NONE Select Operator expressions: CASE WHEN (_col1 is null) THEN (_col0) END (type: string) outputColumnNames: _col0 Statistics: Num rows: 169798618 Data size: 62844147701 Basic stats: COMPLETE Column stats: NONE File Output Operator compressed: false table: input format: org.apache.hadoop.mapred.SequenceFileInputFormat output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe Stage: Stage-30 Conditional Operator Stage: Stage-38 Map Reduce Local Work Alias -> Map Local Tables: t:a:cc:c3:ddm_content_netease_audit_doc_base_day Fetch Operator limit: -1 Alias -> Map Local Operator Tree: t:a:cc:c3:ddm_content_netease_audit_doc_base_day TableScan alias: ddm_content_netease_audit_doc_base_day Statistics: Num rows: 251986855 Data size: 588419341799 Basic stats: COMPLETE Column stats: NONE Select Operator expressions: docid (type: string), doctype (type: tinyint) outputColumnNames: _col0, _col1 Statistics: Num rows: 251986855 Data size: 588419341799 Basic stats: COMPLETE Column stats: NONE HashTable Sink Operator keys: 0 _col0 (type: string) 1 _col0 (type: string) Stage: Stage-29 Map Reduce Map Operator Tree: TableScan Map Join Operator condition map: Left Outer Join0 to 1 keys: 0 _col0 (type: string) 1 _col0 (type: string) outputColumnNames: _col0, _col1, _col2 Statistics: Num rows: 277185546 Data size: 647261290007 Basic stats: COMPLETE Column stats: NONE Select Operator expressions: _col0 (type: string), CASE WHEN (_col1 is null) THEN ('40-1') WHEN (_col1 is not null) THEN (_col2) END (type: string) outputColumnNames: _col0, _col1 Statistics: Num rows: 277185546 Data size: 647261290007 Basic stats: COMPLETE Column stats: NONE File Output Operator compressed: false table: input format: org.apache.hadoop.mapred.SequenceFileInputFormat output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe Local Work: Map Reduce Local Work Stage: Stage-17 Map Reduce Map Operator Tree: TableScan Reduce Output Operator key expressions: _col0 (type: string) sort order: + Map-reduce partition columns: _col0 (type: string) Statistics: Num rows: 169798618 Data size: 62844147701 Basic stats: COMPLETE Column stats: NONE TableScan alias: ddm_content_netease_audit_doc_base_day Statistics: Num rows: 251986855 Data size: 588419341799 Basic stats: COMPLETE Column stats: NONE Select Operator expressions: docid (type: string), doctype (type: tinyint) outputColumnNames: _col0, _col1 Statistics: Num rows: 251986855 Data size: 588419341799 Basic stats: COMPLETE Column stats: NONE Reduce Output Operator key expressions: _col0 (type: string) sort order: + Map-reduce partition columns: _col0 (type: string) Statistics: Num rows: 251986855 Data size: 588419341799 Basic stats: COMPLETE Column stats: NONE value expressions: _col1 (type: tinyint) Reduce Operator Tree: Join Operator condition map: Left Outer Join0 to 1 keys: 0 _col0 (type: string) 1 _col0 (type: string) outputColumnNames: _col0, _col1, _col2 Statistics: Num rows: 277185546 Data size: 647261290007 Basic stats: COMPLETE Column stats: NONE Select Operator expressions: _col0 (type: string), CASE WHEN (_col1 is null) THEN ('40-1') WHEN (_col1 is not null) THEN (_col2) END (type: string) outputColumnNames: _col0, _col1 Statistics: Num rows: 277185546 Data size: 647261290007 Basic stats: COMPLETE Column stats: NONE File Output Operator compressed: false table: input format: org.apache.hadoop.mapred.SequenceFileInputFormat output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe Stage: Stage-34 Conditional Operator Stage: Stage-21 Map Reduce Map Operator Tree: TableScan alias: ddm_content_netease_article_day Statistics: Num rows: 154362377 Data size: 57131042127 Basic stats: COMPLETE Column stats: NONE Select Operator expressions: id (type: string), wemedia_id (type: string), description (type: string), body (type: string), original (type: string) outputColumnNames: _col0, _col1, _col2, _col3, _col4 Statistics: Num rows: 154362377 Data size: 57131042127 Basic stats: COMPLETE Column stats: NONE Reduce Output Operator key expressions: _col1 (type: string) sort order: + Map-reduce partition columns: _col1 (type: string) Statistics: Num rows: 154362377 Data size: 57131042127 Basic stats: COMPLETE Column stats: NONE value expressions: _col0 (type: string), _col2 (type: string), _col3 (type: string), _col4 (type: string) TableScan alias: ddm_content_netease_base_info_d Statistics: Num rows: 1234899 Data size: 707579117 Basic stats: COMPLETE Column stats: NONE Select Operator expressions: wemedia_id (type: string), tid (type: string), cname (type: string), tname (type: string), account_quality (type: int), online_state (type: int), account_type (type: int), account_star (type: string), original_flag (type: int) outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8 Statistics: Num rows: 1234899 Data size: 707579117 Basic stats: COMPLETE Column stats: NONE Reduce Output Operator key expressions: _col0 (type: string) sort order: + Map-reduce partition columns: _col0 (type: string) Statistics: Num rows: 1234899 Data size: 707579117 Basic stats: COMPLETE Column stats: NONE value expressions: _col1 (type: string), _col2 (type: string), _col3 (type: string), _col4 (type: int), _col5 (type: int), _col6 (type: int), _col7 (type: string), _col8 (type: int) Reduce Operator Tree: Join Operator condition map: Left Outer Join0 to 1 keys: 0 _col1 (type: string) 1 _col0 (type: string) outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col6, _col7, _col8, _col9, _col10, _col11, _col12, _col13 Statistics: Num rows: 169798618 Data size: 62844147701 Basic stats: COMPLETE Column stats: NONE Select Operator expressions: _col0 (type: string), _col1 (type: string), _col2 (type: string), _col3 (type: string), _col4 (type: string), _col6 (type: string), _col7 (type: string), _col8 (type: string), _col9 (type: int), _col10 (type: int), _col11 (type: int), _col12 (type: string), _col13 (type: int) outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7, _col8, _col9, _col10, _col11, _col12 Statistics: Num rows: 169798618 Data size: 62844147701 Basic stats: COMPLETE Column stats: NONE File Output Operator compressed: false table: input format: org.apache.hadoop.mapred.SequenceFileInputFormat output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe
https://blog.csdn.net/maizi1045/article/details/53142975一、map阶段优化map端： spill(100M,80%)-->meger(压缩)参数：io.sort.mb（default100）当map task开始运算，并产生中间数据时，其产生的中间结果并非直接就简单的写入磁盘。而是会利用到了内存buffer来进行已经产生的部分结果的缓存，并在内存buffer中进行一些预排序来优化整个map的性能。每一个map都会对应存在一个内存buffer,map会将已经产生的部分结果先写入到该buffer中，这个buffer默认是100MB大小，但是这个大小是可以根据job提交时的参数设定来调整的，当map的产生数据非常大时，并且把io.sort.mb调大，那么map在整个计算过程中spill的次数就势必会降低，maptask对磁盘的操作就会变少，如果map tasks的瓶颈在磁盘上，这样调整就会大大提高map的计算性能。参数:o.sort.spill.percent（default0.80,也就是80%）map在运行过程中，不停的向该buffer中写入已有的计算结果，但是该buffer并不一定能将全部的map输出缓存下来，当map输出超出一定阈值（比如100M），那么map就必须将该buffer中的数据写入到磁盘中去，这个过程在mapreduce中叫做spill。map并不是要等到将该buffer全部写满时才进行spill，因为如果全部写满了再去写spill，势必会造成map的计算部分等待buffer释放空间的情况。所以，map其实是当buffer被写满到一定程度（比如80%）时，就开始进行spill。这个阈值也是由一个job的配置参数来控制，这个参数同样也是影响spill频繁程度，进而影响maptask运行周期对磁盘的读写频率的。但非特殊情况下，通常不需要人为的调整。调整io.sort.mb对用户来说更加方便。参数:io.sort.factor当map task的计算部分全部完成后，如果map有输出，就会生成一个或者多个spill文件，这些文件就是map的输出结果。map在正常退出之前，需要将这些spill合并（merge）成一个，所以map在结束之前还有一个merge的过程。merge的过程中，有一个参数可以调整这个过程的行为，该参数为：io.sort.factor。该参数默认为10。它表示当merge spill文件时，最多能有多少并行的stream向merge文件中写入。比如如果map产生的数据非常的大，产生的spill文件大于10，而io.sort.factor使用的是默认的10，那么当map计算完成做merge时，就没有办法一次将所有的spill文件merge成一个，而是会分多次，每次最多10个stream。这也就是说，当map的中间结果非常大，调大io.sort.factor，有利于减少merge次数，进而减少map对磁盘的读写频率，有可能达到优化作业的目的。参数:min.num.spill.for.combine（default3）当job指定了combiner的时候，我们都知道map介绍后会在map端根据combiner定义的函数将map结果进行合并。运行combiner函数的时机有可能会是merge完成之前，或者之后，这个时机可以由一个参数控制，即min.num.spill.for.combine（default3），当job中设定了combiner，并且spill数最少有3个的时候，那么combiner函数就会在merge产生结果文件之前运行。通过这样的方式，就可以在spill非常多需要merge，并且很多数据需要做conbine的时候，减少写入到磁盘文件的数据数量，同样是为了减少对磁盘的读写频率，有可能达到优化作业的目的。参数:mapred.compress.map.output(defaultfalse)减少中间结果读写进出磁盘的方法不止这些，还有就是压缩。也就是说map的中间，无论是spill的时候，还是最后merge产生的结果文件，都是可以压缩的。压缩的好处在于，通过压缩减少写入读出磁盘的数据量。对中间结果非常大，磁盘速度成为map执行瓶颈的job，尤其有用。控制map中间结果是否使用压缩的参数为：mapred.compress.map.output(true/false)。将这个参数设置为true时，那么map在写中间结果时，就会将数据压缩后再写入磁盘，读结果时也会采用先解压后读取数据。这样做的后果就是：写入磁盘的中间结果数据量会变少，但是cpu会消耗一些用来压缩和解压。所以这种方式通常适合job中间结果非常大，瓶颈不在cpu，而是在磁盘的读写的情况。说的直白一些就是用cpu换IO。根据观察，通常大部分的作业cpu都不是瓶颈，除非运算逻辑异常复杂。所以对中间结果采用压缩通常来说是有收益的。参数:mapred.map.output.compression.codec(default org.apache.hadoop.io.compress.DefaultCodec)当采用map中间结果压缩的情况下，用户还可以选择压缩时采用哪种压缩格式进行压缩，现在hadoop支持的压缩格式有：GzipCodec，LzoCodec，BZip2Codec，LzmaCodec等压缩格式。通常来说，想要达到比较平衡的cpu和磁盘压缩比，LzoCodec比较适合。但也要取决于job的具体情况。用户若想要自行选择中间结果的压缩算法，可以设置配置参数：mapred.map.output.compression.codec=org.apache.hadoop.io.compress.DefaultCodec或者其他用户自行选择的压缩方式 二、reduce阶段优化reduce的运行是分成三个阶段的。分别为copy->sort->reduce。由于job的每一个map都会根据reduce(n)数将数据分成map输出结果分成n个partition，所以map的中间结果中是有可能包含每一个reduce需要处理的部分数据的。所以，为了优化reduce的执行时间，hadoop中是等job的第一个map结束后，所有的reduce就开始尝试从完成的map中下载该reduce对应的partition部分数据。这个过程就是通常所说的shuffle，也就是copy过程。参数：mapred.reduce.parallel.copies（default5）说明:每个reduce并行下载map结果的最大线程数。Reduce task在做shuffle时，实际上就是从不同的已经完成的map上去下载属于自己这个reduce的部分数据，由于map通常有许多个，所以对一个reduce来说，下载也可以是并行的从多个map下载，这个并行度是可以调整的，调整参数为：mapred.reduce.parallel.copies（default5）。默认情况下，每个只会有5个并行的下载线程在从map下数据，如果一个时间段内job完成的map有100个或者更多，那么reduce也最多只能同时下载5个map的数据，所以这个参数比较适合map很多并且完成的比较快的job的情况下调大，有利于reduce更快的获取属于自己部分的数据。参数：mapred.reduce.copy.backoff（default300秒）说明:reduce下载线程最大等待时间(秒)reduce的每一个下载线程在下载某个map数据的时候，有可能因为那个map中间结果所在机器发生错误，或者中间结果的文件丢失，或者网络瞬断等等情况，这样reduce的下载就有可能失败，所以reduce的下载线程并不会无休止的等待下去，当一定时间后下载仍然失败，那么下载线程就会放弃这次下载，并在随后尝试从另外的地方下载（因为这段时间map可能重跑）。所以reduce下载线程的这个最大的下载时间段是可以调整的，调整参数为：mapred.reduce.copy.backoff（default300秒）。如果集群环境的网络本身是瓶颈，那么用户可以通过调大这个参数来避免reduce下载线程被误判为失败的情况。不过在网络环境比较好的情况下，没有必要调整。通常来说专业的集群网络不应该有太大问题，所以这个参数需要调整的情况不多。参数:io.sort.factorReduce将map结果下载到本地时，同样也是需要进行merge的，所以io.sort.factor的配置选项同样会影响reduce进行merge时的行为，该参数的详细介绍上文已经提到，当发现reduce在shuffle阶段iowait非常的高的时候，就有可能通过调大这个参数来加大一次merge时的并发吞吐，优化reduce效率。参数:mapred.job.shuffle.input.buffer.percent（default0.7）说明:用来缓存shuffle数据的reducetask heap百分比Reduce在shuffle阶段对下载来的map数据，并不是立刻就写入磁盘的，而是会先缓存在内存中，然后当使用内存达到一定量的时候才刷入磁盘。这个内存大小的控制就不像map一样可以通过io.sort.mb来设定了，而是通过另外一个参数来设置：mapred.job.shuffle.input.buffer.percent（default0.7），这个参数其实是一个百分比，意思是说，shuffile在reduce内存中的数据最多使用内存量为：0.7× maxHeap of reduce task。也就是说，如果该reducetask的最大heap使用量（通常通过mapred.child.java.opts来设置，比如设置为-Xmx1024m）的一定比例用来缓存数据。默认情况下，reduce会使用其heapsize的70%来在内存中缓存数据。如果reduce的heap由于业务原因调整的比较大，相应的缓存大小也会变大，这也是为什么reduce用来做缓存的参数是一个百分比，而不是一个固定的值了。参数：mapred.job.shuffle.merge.percent（default0.66）说明:缓存的内存中多少百分比后开始做merge操作假设mapred.job.shuffle.input.buffer.percent为0.7，reducetask的max heapsize为1G，那么用来做下载数据缓存的内存就为大概700MB左右，这700M的内存，跟map端一样，也不是要等到全部写满才会往磁盘刷的，而是当这700M中被使用到了一定的限度（通常是一个百分比），就会开始往磁盘刷。这个限度阈值也是可以通过job参数来设定的，设定参数为：mapred.job.shuffle.merge.percent（default0.66）。如果下载速度很快，很容易就把内存缓存撑大，那么调整一下这个参数有可能会对reduce的性能有所帮助。参数:mapred.job.reduce.input.buffer.percent（default0.0）说明:sort完成后reduce计算阶段用来缓解数据的百分比当reduce将所有的map上对应自己partition的数据下载完成后，就会开始真正的reduce计算阶段（中间有个sort阶段通常时间非常短，几秒钟就完成了，因为整个下载阶段就已经是边下载边sort，然后边merge的）。当reducetask真正进入reduce函数的计算阶段的时候，有一个参数也是可以调整reduce的计算行为。也就是：mapred.job.reduce.input.buffer.percent（default0.0）。由于reduce计算时肯定也是需要消耗内存的，而在读取reduce需要的数据时，同样是需要内存作为buffer，这个参数是控制，需要多少的内存百分比来作为reduce读已经sort好的数据的buffer百分比。默认情况下为0，也就是说，默认情况下，reduce是全部从磁盘开始读处理数据。如果这个参数大于0，那么就会有一定量的数据被缓存在内存并输送给reduce，当reduce计算逻辑消耗内存很小时，可以分一部分内存用来缓存数据，反正reduce的内存闲着也是闲着。mapred.reduce.slowstart.completed.maps（map完成多少百分比时，开始shuffle）当map运行慢，reduce运行很快时，如果不设置mapred.reduce.slowstart.completed.maps会使job的shuffle时间变的很长，map运行完很早就开始了reduce，导致reduce的slot一直处于被占用状态。mapred.reduce.slowstart.completed.maps这个值是和“运行完的map数除以总map数”做判断的，当后者大于等于设定的值时，开始reduce的shuffle。所以当map比reduce的执行时间多很多时，可以调整这个值（0.75,0.80,0.85及以上）下面从流程里描述一下各个参数的作用：当maptask开始运算，并产生中间数据时，其产生的中间结果并非直接就简单的写入磁盘。这中间的过程比较复杂，并且利用到了内存buffer来进行已经产生的部分结果的缓存，并在内存buffer中进行一些预排序来优化整个map的性能。每一个map都会对应存在一个内存buffer（MapOutputBuffer），map会将已经产生的部分结果先写入到该buffer中，这个buffer默认是100MB大小，但是这个大小是可以根据job提交时的参数设定来调整的，该参数即为：io.sort.mb。当map的产生数据非常大时，并且把io.sort.mb调大，那么map在整个计算过程中spill的次数就势必会降低，maptask对磁盘的操作就会变少，如果map tasks的瓶颈在磁盘上，这样调整就会大大提高map的计算性能。map在运行过程中，不停的向该buffer中写入已有的计算结果，但是该buffer并不一定能将全部的map输出缓存下来，当map输出超出一定阈值（比如100M），那么map就必须将该buffer中的数据写入到磁盘中去，这个过程在mapreduce中叫做spill。map并不是要等到将该buffer全部写满时才进行spill，因为如果全部写满了再去写spill，势必会造成map的计算部分等待buffer释放空间的情况。所以，map其实是当buffer被写满到一定程度（比如80%）时，就开始进行spill。这个阈值也是由一个job的配置参数来控制，即io.sort.spill.percent，默认为0.80或80%。这个参数同样也是影响spill频繁程度，进而影响maptask运行周期对磁盘的读写频率的。但非特殊情况下，通常不需要人为的调整。调整io.sort.mb对用户来说更加方便。当maptask的计算部分全部完成后，如果map有输出，就会生成一个或者多个spill文件，这些文件就是map的输出结果。map在正常退出之前，需要将这些spill合并（merge）成一个，所以map在结束之前还有一个merge的过程。merge的过程中，有一个参数可以调整这个过程的行为，该参数为：io.sort.factor。该参数默认为10。它表示当mergespill文件时，最多能有多少并行的stream向merge文件中写入。比如如果map产生的数据非常的大，产生的spill文件大于10，而io.sort.factor使用的是默认的10，那么当map计算完成做merge时，就没有办法一次将所有的spill文件merge成一个，而是会分多次，每次最多10个stream。这也就是说，当map的中间结果非常大，调大io.sort.factor，有利于减少merge次数，进而减少map对磁盘的读写频率，有可能达到优化作业的目的。当job指定了combiner的时候，我们都知道map介绍后会在map端根据combiner定义的函数将map结果进行合并。运行combiner函数的时机有可能会是merge完成之前，或者之后，这个时机可以由一个参数控制，即min.num.spill.for.combine（default3），当job中设定了combiner，并且spill数最少有3个的时候，那么combiner函数就会在merge产生结果文件之前运行。通过这样的方式，就可以在spill非常多需要merge，并且很多数据需要做conbine的时候，减少写入到磁盘文件的数据数量，同样是为了减少对磁盘的读写频率，有可能达到优化作业的目的。减少中间结果读写进出磁盘的方法不止这些，还有就是压缩。也就是说map的中间，无论是spill的时候，还是最后merge产生的结果文件，都是可以压缩的。压缩的好处在于，通过压缩减少写入读出磁盘的数据量。对中间结果非常大，磁盘速度成为map执行瓶颈的job，尤其有用。控制map中间结果是否使用压缩的参数为：mapred.compress.map.output(true/false)。将这个参数设置为true时，那么map在写中间结果时，就会将数据压缩后再写入磁盘，读结果时也会采用先解压后读取数据。这样做的后果就是：写入磁盘的中间结果数据量会变少，但是cpu会消耗一些用来压缩和解压。所以这种方式通常适合job中间结果非常大，瓶颈不在cpu，而是在磁盘的读写的情况。说的直白一些就是用cpu换IO。根据观察，通常大部分的作业cpu都不是瓶颈，除非运算逻辑异常复杂。所以对中间结果采用压缩通常来说是有收益的。当采用map中间结果压缩的情况下，用户还可以选择压缩时采用哪种压缩格式进行压缩，现在hadoop支持的压缩格式有：GzipCodec，LzoCodec，BZip2Codec，LzmaCodec等压缩格式。通常来说，想要达到比较平衡的cpu和磁盘压缩比，LzoCodec比较适合。但也要取决于job的具体情况。用户若想要自行选择中间结果的压缩算法，可以设置配置参数：mapred.map.output.compression.codec=org.apache.hadoop.io.compress.DefaultCodec或者其他用户自行选择的压缩方式。
1.hive查看rcfile和orchive --orcfiledump /data/datawarehouse/xx/xxxx/xxxx/part-00199 hive --rcfilecat /data/datawarehouse/xx/xxxx/xxxx/part-00199