I am trying to run a Hive query with pyspark. I am using Hortonworks so I need to use the   Hive WarehouseConnector. Running one or even multiple queries is easy and works. My problem is that I want to issue   set   commands before. For instance to set the dag name in tez ui: set hive.query.name=something relevant or to set up some memory configuration: set hive.tez.container.size = 8192 For these statements to take effect, they need to run on the same session than the main query and that's my issue.   I tried 2 ways:   The first one was to generate a new hive session   for each query, with a properly setup url eg.:   url='jdbc:hive2://hiveserver:10000/default?hive.query.name=relevant' builder = HiveWarehouseSession.session(self.spark) builder.hs2url(url) hive = builder.build() hive.execute("select * from whatever")   It works well for the first query, but the same url is reused for the next one (even if I try to manually delete   builder   and   hive), so does not work.   The second way is to set spark.sql.hive.thriftServer.singleSession=true globally in the spark thrift server. his does seem to work, but I find it a shame to limit the global spark thrift server for the benefit of one application only.   Is there a way to achieve what I am looking for? Maybe there could be a way to pin a query to one executor, so hopefully one session? ... View more

Hello, I have a new hdp3.0.1 installation with ats-hbase which runs embedded (with proper queue configured, as per the documentation). At the end of all tasks (seen with the hive compactor, oozie steps), I have hundreds of lines with org.apache.hadoop.yarn.event.AsyncDispatcher: Waiting for AsyncDispatcher to drain. Thread state is :WAITING ending up with : org.apache.hadoop.mapreduce.jobhistory.JobHistoryEventHandler: Failed to process Event JOB_FINISHED for the job : job_1542872934100_0068 org.apache.hadoop.yarn.exceptions.YarnException: Failed while publishing entity at org.apache.hadoop.yarn.client.api.impl.TimelineV2ClientImpl$TimelineEntityDispatcher.dispatchEntities(TimelineV2ClientImpl.java:548) at org.apache.hadoop.yarn.client.api.impl.TimelineV2ClientImpl.putEntities(TimelineV2ClientImpl.java:149) at org.apache.hadoop.mapreduce.jobhistory.JobHistoryEventHandler.processEventForNewTimelineService(JobHistoryEventHandler.java:1405) at org.apache.hadoop.mapreduce.jobhistory.JobHistoryEventHandler.handleTimelineEvent(JobHistoryEventHandler.java:742) at org.apache.hadoop.mapreduce.jobhistory.JobHistoryEventHandler.access$1200(JobHistoryEventHandler.java:93) at org.apache.hadoop.mapreduce.jobhistory.JobHistoryEventHandler$ForwardingEventHandler.handle(JobHistoryEventHandler.java:1795) at org.apache.hadoop.mapreduce.jobhistory.JobHistoryEventHandler$ForwardingEventHandler.handle(JobHistoryEventHandler.java:1791) at org.apache.hadoop.yarn.event.AsyncDispatcher.dispatch(AsyncDispatcher.java:197) at org.apache.hadoop.yarn.event.AsyncDispatcher$1.run(AsyncDispatcher.java:126) at java.lang.Thread.run(Thread.java:745) Caused by: com.sun.jersey.api.client.ClientHandlerException: java.net.SocketTimeoutException: Call From null to prod-nl-dpnode3.dmdelivery.local:33602 failed on socket timeout exception:t java.lang.Thread.run(Thread.java:745) Caused by: com.sun.jersey.api.client.ClientHandlerException: java.net.SocketTimeoutException: Call From null to prod-nl-dpnode3.dmdelivery.local:33602 failed on socket timeout exception Looking at /var/log/hadoop-yarn/yar/hadoop-yarn-nodemanager I have a lot of lines with: Call exception, tries=7, retries=7, started=8194 ms ago, cancelled=false, msg=Call to xxxxx/192.168.x.x:17020 failed on connection exception: org.apache.hbase.thirdparty.io.netty.channel.AbstractChannel$AnnotatedConnectException: Connection refused: prod-nl-dpnode1.dmdelivery.local/192.168.36.161:17020, details=row 'prod.timelineservice.entity,hive!yarn-cluster!xxxx-34-compactor-vault.contact.license_name=lectiva!^?�����@@!^?����d��^?���!MAPREDUCE_TASK_ATTEMPT!^?�����!attempt_1542205428050_2307_m_000461_0,99999999999999' on table 'hbase:meta' at region=hbase:meta,,1.1588230740, hostname=xxx,17020,1542294270073, seqNum=-1 Looking at /var/log/hadoop-yarn/yarn/hadoop-yarn-timelinereader, I see Connection refused: dpnode1/192.168.36.161:17020 Indeed, there is no hbase on dpnode1. Hbase does run on dpnode5 (or another one, depending on yarn restart), but in any case, the timelinereader does not know which server to reach, and always goes to one seemingly hardcoded hostname. How can I tell yarn to use the right node to connect to hbase? Thanks, ... View more

Hello, I installed a new (not an update) HDP 3.0.1 and seem to have many issues with the timeline server. 1) The first weird thing is that the Yarn tab in ambari keeps showing this error: ATSv2 HBase Application The HBase application reported a 'STARTED' state. Check took 2.125s 2) The second issue seems to be with oozie. Running a job, it starts but stalls with the following log repeated hundreds of times 2018-11-01 11:15:37,842 INFO [Thread-82] org.apache.hadoop.yarn.event.AsyncDispatcher: Waiting for AsyncDispatcher to drain. Thread state is :WAITING Then with: 2018-11-01 11:15:37,888 ERROR [Job ATS Event Dispatcher] org.apache.hadoop.mapreduce.jobhistory.JobHistoryEventHandler: Exception while publishing configs on JOB_SUBMITTED Event for the job : job_1541066376053_0066 org.apache.hadoop.yarn.exceptions.YarnException: Failed while publishing entity at org.apache.hadoop.yarn.client.api.impl.TimelineV2ClientImpl$TimelineEntityDispatcher.dispatchEntities(TimelineV2ClientImpl.java:548) at org.apache.hadoop.yarn.client.api.impl.TimelineV2ClientImpl.putEntities(TimelineV2ClientImpl.java:149) at org.apache.hadoop.mapreduce.jobhistory.JobHistoryEventHandler.publishConfigsOnJobSubmittedEvent(JobHistoryEventHandler.java:1254) at org.apache.hadoop.mapreduce.jobhistory.JobHistoryEventHandler.processEventForNewTimelineService(JobHistoryEventHandler.java:1414) at org.apache.hadoop.mapreduce.jobhistory.JobHistoryEventHandler.handleTimelineEvent(JobHistoryEventHandler.java:742) at org.apache.hadoop.mapreduce.jobhistory.JobHistoryEventHandler.access$1200(JobHistoryEventHandler.java:93) at org.apache.hadoop.mapreduce.jobhistory.JobHistoryEventHandler$ForwardingEventHandler.handle(JobHistoryEventHandler.java:1795) at org.apache.hadoop.mapreduce.jobhistory.JobHistoryEventHandler$ForwardingEventHandler.handle(JobHistoryEventHandler.java:1791) at org.apache.hadoop.yarn.event.AsyncDispatcher.dispatch(AsyncDispatcher.java:197) at org.apache.hadoop.yarn.event.AsyncDispatcher$1.run(AsyncDispatcher.java:126) at java.lang.Thread.run(Thread.java:745) Caused by: com.sun.jersey.api.client.ClientHandlerException: java.net.SocketTimeoutException: Read timed out 3) In hadoop-yarn-timelineserver-${hostname}.log I see, repeated many times: 2018-11-01 11:32:47,715 WARN timeline.EntityGroupFSTimelineStore (LogInfo.java:doParse(208)) - Error putting entity: dag_1541066376053_0144_2 (TEZ_DAG_ID): 6 4) In hadoop-yarn-timelinereader-${hostname}.log I see, repeated many times: Thu Nov 01 11:34:10 CET 2018, RpcRetryingCaller{globalStartTime=1541068444076, pause=1000, maxAttempts=4}, java.net.ConnectException: Call to /192.168.x.x:17020 failed on connection exception: org.apache.hbase.thirdparty.io.netty.channel.AbstractChannel$AnnotatedConnectException: Connection refused: /192.168.x.x:17020 at org.apache.hadoop.hbase.client.RpcRetryingCallerImpl.callWithRetries(RpcRetryingCallerImpl.java:145) at org.apache.hadoop.hbase.client.ResultBoundedCompletionService$QueueingFuture.run(ResultBoundedCompletionService.java:80) ... 3 more Caused by: java.net.ConnectException: Call to /192.168.x.x:17020 failed on connection exception: org.apache.hbase.thirdparty.io.netty.channel.AbstractChannel$AnnotatedConnectException: Connection refused: /192.168.x.x:17020 at org.apache.hadoop.hbase.ipc.IPCUtil.wrapException(IPCUtil.java:165) and indeed, there is nothing listening to port 17020 on 192.168.x.x. 5) I cannot find on any server a process named ats-hbase, this might be the reason for everything else. The queue set is just yarn_hbase_system_service_queue_name=default, which has no limit which would prevent Hbase to start. I am sure that something is very wrong here, and any help would be appreciated. ... View more

I was using Oozie 4.2.0 (hdp2.6) and am now trying Oozie 4.3.1 (hdp 3.0). One major difference is that one java action could in the past read its jar from the local filesystem, but it looks like it is not possible anymore. The Java action is basic: <java xmlns="uri:oozie:workflow:0.5"> <job-tracker>http://something.local:8050</job-tracker> <name-node>hdfs://HdfsNameService</name-node> <main-class>io.JsonPoster</main-class> <file>file:///opt/jsonposter/jsonposter.jar</file> </java> The error I get is quite clear: org.apache.oozie.action.ActionExecutorException: UnsupportedOperationException: Accessing local file system is not allowed I know I could put the jar on hdfs put I am trying to avoid that for now (because it used to work and all our deployment are done via rpm). I am ready to take the responsibility of having all jars in sync over all datanodes. I already set oozie.service.HadoopAccessorService.supported.filesystems to * , with no effect. Is there a way to tell Oozie that yes, I am happy for it to read the local FS? ... View more

I created my own (generic) udf, which works very well when added in hive: CREATE FUNCTION myfunc AS 'io.company.hive.udf.myfunc' USING JAR 'hdfs:///myudfs/myfunc.jar'; After a while I wanted to update my udf, so I created a new jar with the same name, and put it in hdfs by overwriting the old jar. Lo and behold, I cannot use my function again! It does not matter if I do first a: drop function if exists myfunc; CREATE FUNCTION myfunc AS 'io.company.hive.udf.myfunc' USING JAR 'hdfs:///myudfs/myfunc.jar'; From beeline, I got one of these error message: java.io.IOException: Previous writer likely failed to write hdfs://ip-10-0-10-xxx.eu-west-1.compute.internal:8020/tmp/hive/hive/_tez_session_dir/0de6055d-190d-41ee-9acb-c6b402969940/hmyfunc.jar Failing because I am unlikely to write too. or org.apache.hadoop.hive.ql.metadata.HiveException: Default queue should always be returned.Hence we should not be here. Looking at the logs, it looks like Hive is localising the jar file (good) but as a session is reused, if the new jar does not match the jar already present in the localised directory hive will complain and will apparently wait indefinitely. If my understanding is correct, is there a way to tell Tez to not reuse any of the current sessions? If my understanding is not correct, is there a way to do what I want? Context: hdp 2.6.0.3, no llap, on aws. Thanks, ... View more

I have a query, always failing with the following error: Container exited with a non-zero exit code 1 ]], TaskAttempt 2 failed, info=[Error: Failure while running task:java.lang.RuntimeException: java.lang.OutOfMemoryError: Java heap space at org.apache.hadoop.hive.ql.exec.tez.TezProcessor.initializeAndRunProcessor(TezProcessor.java:173) [...] The query itself is quite a small MERGE (Other much bigger queries work flawlessly): MERGE INTO summary dst USING ( SELECT e.id1 , e.id2 , e.id3 , e.name , e.subject FROM mailing e ) src ON dst.id1 = src.id1 AND dst.id2 = src.id2 AND dst.id3 = src.id3 WHEN MATCHED THEN UPDATE SET name = src.name , subject=src.subject The source table has 1.7M rows (50M on disk), the destination has 75M rows, (1.5GB on disk). Both are ACID tables, ORC. On the image, map 1 is the one with the issue, and I cannot understand why it has only one task. Naively I would think that more tasks would each have a smaller load and would work better, but I did not manage to do that. Note that I maxed out already all memory parameters, I cannot do more on those: yarn-site/yarn.nodemanager.resource.memory-mb = 24064 yarn-site/yarn.scheduler.minimum-allocation-mb = 1024 yarn-site/yarn.scheduler.maximum-allocation-mb = 24064 mapred-site/mapreduce.map.memory.mb = 4096 mapred-site/mapreduce.reduce.memory.mb = 8192 mapred-site/mapreduce.map.java.opts = 3276 mapred-site/mapreduce.reduce.java.opts = 6553 hive-site/hive.tez.container.size = 4096 Is there a way to increase the number of tasks in the mapper, or another way to not get this out of memory error? ... View more

My end goal is to run a few hive queries, get 1 csv file (with headers) per query, compress all those files together in one zip (not gzip or bzip, unfortunately, needs to open natively under windows) and hopefully get the zip back in hdfs. My current solution (CTAS) ends up creating one directory per table, with possibly multiple files under it (depending on number of reducers and presence/absence of UNION). I can easily generate as well a header file per table with only one line in it. Now how to put all that together? The only option I could find implies to do all the processing locally (hdfs dfs -getmerge followed by a an actual zip command). This adds a lot of overhead and could technically fill up the local disk. So my questions are: is there a way to concatenate files inside hdfs without getting them locally? is there a way to compress a bunch of files together (not individually) in zip, inside hdfs? Thanks ... View more

hdp 2.6.0, HMS has 6GB memory, the metastore itself is mysql. After a few days the server hosting the HMS will have its CPU 100% used, hive queries are slow, and looking at the GC logs the HMS is constantly having stop the world events. Restarting the metastore 'fixes' the problem for a few days. I have found a few JIRA link related to memory leaks https://issues.apache.org/jira/browse/HIVE-15551 or https://issues.apache.org/jira/browse/HIVE-13749 . Is it a known issue in hdp 2.6.0? Is it known if the latest hdp version is fixed? Thanks, ... View more

Context: I have an issue with a MERGE statement, which does not use the partitions of the destination table. Looking for solutions, I stumbled upon this JIRA ticket which creates 3 new (in hive 0.14) configuration options: hive.tez.dynamic.partition.pruning: default true hive.tez.dynamic.partition.pruning.max.event.size: default 1*1024*1024L hive.tez.dynamic.partition.pruning.max.data.size: default 100*1024*1024L Now I wonder why should I not just set these variables to the max value possible to make sure that partition pruning always happen? It is disabled if data size is too big, but I find it counter intuitive as not pruning will massively increase data size. Cheers, ... View more

I have 2 tables, with the same structure: CREATE TABLE IF NOT EXISTS src ( -- DISTINCT field (+ partitions) id BIGINT -- other fields , email STRING , domain STRING , lang STRING , mobile_nr STRING , custom_fields STRING , groups array<struct<group_id:bigint,campaign_id:bigint,member_since_ts_utc:bigint>> , ts_utc TIMESTAMP , sys_schema_version INT , sys_server_ipv4 BIGINT , sys_server_name STRING ) PARTITIONED BY ( license_name STRING , campaign_id INT ) CLUSTERED BY (id) INTO 64 BUCKETS STORED AS ORC One is a source table (basically recreated from scratch with new data for each merge, so needs to be fully reprocessed everytime) one is the destination table, which will grow. Both tables have the same partitions and bucket definitions. When I EXPLAIN the MERGE statement, which has a join on the partitions and the bucketed field, I cannot see any partition pruning happening. set hive.merge.cardinality.check=false; set hive.tez.exec.print.summary=true; set tez.user.explain=true; explain MERGE INTO -- default.2steps_false_64_1 vault.contact dst USING default.2steps_2steps_false_64_1 src ON dst.license_name = src.license_name AND dst.campaign_id = src.campaign_id AND dst.id = src.id -- On match: keep latest loaded WHEN MATCHED AND dst.updated_on_utc < src.ts_utc THEN UPDATE SET -- other fields email = src.email , city = src.city , lang = src.lang , mobile_nr = src.mobile_nr , custom_fields = src.custom_fields , groups = src.groups , updated_on_utc = src.ts_utc , sys_schema_version = src.sys_schema_version , sys_server_ipv4 = src.sys_server_ipv4 , sys_server_name = src.sys_server_name WHEN NOT MATCHED THEN INSERT VALUES ( src.id , src.email , src.city , src.lang , src.mobile_nr , src.custom_fields , src.groups , src.ts_utc , src.ts_utc , NULL -- deleted_on , src.sys_schema_version , src.sys_server_ipv4 , src.sys_server_name , src.license_name , src.campaign_id ) ; +-----------------------------------------------------------------------------------------------------------------------------+--+ | Explain +-----------------------------------------------------------------------------------------------------------------------------+--+ | Vertex dependency in root stage | Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 5 (SIMPLE_EDGE) | Reducer 3 <- Reducer 2 (SIMPLE_EDGE) | Reducer 4 <- Reducer 2 (SIMPLE_EDGE) | | Stage-5 | Stats-Aggr Operator | Stage-0 | Move Operator | partition:{} | table:{"name:":"vault.contact","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"} | Stage-3 | Dependency Collection{} | Stage-2 | Reducer 3 | File Output Operator [FS_904] | compressed:true | Statistics:Num rows: 496014 Data size: 166660704 Basic stats: COMPLETE Column stats: PARTIAL | table:{"name:":"vault.contact","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"} | Select Operator [SEL_901] | | outputColumnNames:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9","_col10","_col11","_col12","_col13","_col14","_col15"] | | Statistics:Num rows: 496014 Data size: 166660704 Basic stats: COMPLETE Column stats: PARTIAL | |<-Reducer 2 [SIMPLE_EDGE] | Reduce Output Operator [RS_900] | key expressions:_col0 (type: struct<transactionid:bigint,bucketid:int,rowid:bigint>) | Map-reduce partition columns:UDFToInteger(_col0) (type: int) | sort order:+ | Statistics:Num rows: 496014 Data size: 257927280 Basic stats: COMPLETE Column stats: PARTIAL | value expressions:_col1 (type: bigint), _col2 (type: string), _col3 (type: string), _col4 (type: string), _col5 (type: string), _col6 (type: string), _col7 (type: array<struct<group_id:bigint,campaign_id:bigint,member_since_ts_utc:bigint>>), _col8 (type: timestamp), _col9 (type: timestamp), _col10 (type: timestamp), _col11 (type: int), _col12 (type: bigint), _col13 (type: string), _col14 (type: string), _col15 (type: bigint) | Select Operator [SEL_899] | outputColumnNames:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9","_col10","_col11","_col12","_col13","_col14","_col15"] | Statistics:Num rows: 496014 Data size: 257927280 Basic stats: COMPLETE Column stats: PARTIAL | Filter Operator [FIL_906] | predicate:((_col13 = _col29) and (_col14 = _col30) and (_col0 = _col18) and (_col7 < _col25)) (type: boolean) | Statistics:Num rows: 496014 Data size: 226182384 Basic stats: COMPLETE Column stats: PARTIAL | Merge Join Operator [MERGEJOIN_916] | | condition map:[{"":"Right Outer Join0 to 1"}] | | keys:{"0":"license_name (type: string), campaign_id (type: bigint), id (type: bigint)","1":"license_name (type: string), UDFToLong(campaign_id) (type: bigint), id (type: bigint)"} | | outputColumnNames:["_col0","_col7","_col8","_col9","_col13","_col14","_col17","_col18","_col19","_col20","_col21","_col22","_col23","_col24","_col25","_col26","_col27","_col28","_col29","_col30"] | | Statistics:Num rows: 11904348 Data size: 25284835152 Basic stats: COMPLETE Column stats: PARTIAL | |<-Map 1 [SIMPLE_EDGE] | | Reduce Output Operator [RS_889] | | key expressions:license_name (type: string), campaign_id (type: bigint), id (type: bigint) | | Map-reduce partition columns:license_name (type: string), campaign_id(type: bigint), id (type: bigint) | | sort order:+++ | | Statistics:Num rows: 129102910 Data size: 16525280556 Basic stats: COMPLETE Column stats: PARTIAL | | value expressions:updated_on_utc (type: timestamp), created_on_utc (type: timestamp), deleted_on_utc (type: timestamp), ROW__ID (type: struct<transactionid:bigint,bucketid:int,rowid:bigint>) | | TableScan [TS_887] | | ACID table:true | | alias:dst | | Statistics:Num rows: 129102910 Data size: 16525280556 Basic stats: COMPLETE Column stats: PARTIAL | |<-Map 5 [SIMPLE_EDGE] | Reduce Output Operator [RS_890] | key expressions:license_name (type: string), UDFToLong(campaign_id) (type: bigint), id (type: bigint) | Map-reduce partition columns:license_name (type: string), UDFToLong(campaign_id) (type: bigint), id (type: bigint) | sort order:+++ | Statistics:Num rows: 11904348 Data size: 29935728348 Basic stats: COMPLETE Column stats: PARTIAL | value expressions:email (type: string), city (type: string), lang (type: string), mobile_nr (type: string), custom_fields (type: string), groups (type: array<struct<group_id:bigint,campaign_id:bigint,member_since_ts_utc:bigint>>), ts_utc (type: timestamp), sys_schema_version (type: int), sys_server_ipv4 (type: bigint), sys_server_name (type: string), campaign_id (type: int) | TableScan [TS_888] | alias:src | Statistics:Num rows: 11904348 Data size: 29935728348 Basic stats: COMPLETE Column stats: PARTIAL | Reducer 4 | File Output Operator [FS_897] | compressed:true | Statistics:Num rows: 1 Data size: 188 Basic stats: COMPLETE Column stats: PARTIAL | table:{"name:":"vault.contact","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"} | Select Operator [SEL_895] | | outputColumnNames:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9","_col10","_col11","_col12","_col13","_col14"] | | Statistics:Num rows: 1 Data size: 188 Basic stats: COMPLETE Column stats: PARTIAL | |<-Reducer 2 [SIMPLE_EDGE] | Reduce Output Operator [RS_894] | Map-reduce partition columns:_col0 (type: bigint) | sort order: | Statistics:Num rows: 1 Data size: 188 Basic stats: COMPLETE Column stats: PARTIAL | value expressions:_col0 (type: bigint), _col1 (type: string), _col2 (type: string), _col3 (type: string), _col4 (type: string), _col5 (type: string), _col6 (type: array<struct<group_id:bigint,campaign_id:bigint,member_since_ts_utc:bigint>>), _col7 (type: timestamp), _col10 (type: int), _col11 (type: bigint), _col12 (type: string), _col13 (type: string), _col14 (type: int) | Select Operator [SEL_893] | outputColumnNames:["_col0","_col1","_col10","_col11","_col12","_col13","_col14","_col2","_col3","_col4","_col5","_col6","_col7"] | Statistics:Num rows: 1 Data size: 188 Basic stats: COMPLETE Column stats: PARTIAL | Filter Operator [FIL_907] | predicate:(_col13 is null and _col14 is null and _col0 is null) (type: boolean) | Statistics:Num rows: 1 Data size: 456 Basic stats: COMPLETE Column stats: PARTIAL | Please refer to the previous Merge Join Operator [MERGEJOIN_916] | Stage-4 | Stats-Aggr Operator | Stage-1 | Move Operator | partition:{} | table:{"name:":"vault.contact","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"} | Please refer to the previous Stage-3 | Other explain with hive.explain.user=true 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> set hive.merge.cardinality.check=false; 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> -- set hive.tez.dynamic.partition.pruning=true; 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> -- set hive.tez.dynamic.partition.pruning.max.data.size=107374182400; -- 100GB 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> set hive.tez.exec.print.summary=true; 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> set tez.user.explain=true; 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> set hive.explain.user=true; 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> explain MERGE INTO 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> -- default.2steps_false_64_1 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> vault.contact 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> dst 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> USING default.2steps_2steps_false_64_1 src 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> ON 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> dst.license_name = src.license_name 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> AND dst.campaign_id = src.campaign_id 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> AND dst.id = src.id 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> AND dst.license_name = 'baarn' 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> -- On match: keep latest loaded 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> WHEN MATCHED 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> AND dst.updated_on_utc < src.ts_utc 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> THEN UPDATE SET 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> -- other fields 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> email = src.email 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> , domain = src.domain 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> , lang = src.lang 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> , mobile_nr = src.mobile_nr 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> , custom_fields = src.custom_fields 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> , groups = src.groups 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> , updated_on_utc = src.ts_utc 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> , sys_schema_version = src.sys_schema_version 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> , sys_server_ipv4 = src.sys_server_ipv4 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> , sys_server_name = src.sys_server_name 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> WHEN NOT MATCHED THEN INSERT VALUES ( 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> src.id 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> , src.email 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> , src.domain 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> , src.lang 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> , src.mobile_nr 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> , src.custom_fields 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> , src.groups 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> , src.ts_utc 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> , src.ts_utc 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> , NULL -- deleted_on 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> , src.sys_schema_version 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> , src.sys_server_ipv4 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> , src.sys_server_name 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> , src.license_name 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> , src.campaign_id 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> ) 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> ; +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+ | Explain +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+ | Vertex dependency in root stage | Map 2 <- Map 1 (BROADCAST_EDGE) | Reducer 3 <- Map 2 (SIMPLE_EDGE) | Reducer 4 <- Map 2 (SIMPLE_EDGE) | | Stage-5 | Stats-Aggr Operator | Stage-0 | Move Operator | partition:{} | table:{"name:":"vault.contact","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"} | Stage-3 | Dependency Collection{} | Stage-2 | Reducer 3 | File Output Operator [FS_1461] | compressed:true | Statistics:Num rows: 496014 Data size: 123507486 Basic stats: COMPLETE Column stats: PARTIAL | table:{"name:":"vault.contact","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"} | Select Operator [SEL_1458] | | outputColumnNames:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9","_col10","_col11","_col12","_col13","_col14","_col15"] | | Statistics:Num rows: 496014 Data size: 123507486 Basic stats: COMPLETE Column stats: PARTIAL | |<-Map 2 [SIMPLE_EDGE] | Reduce Output Operator [RS_1457] | key expressions:_col0 (type: struct<transactionid:bigint,bucketid:int,rowid:bigint>) | Map-reduce partition columns:UDFToInteger(_col0) (type: int) | sort order:+ | Statistics:Num rows: 496014 Data size: 79362240 Basic stats: COMPLETE Column stats: PARTIAL | value expressions:_col1 (type: bigint), _col2 (type: string), _col3 (type: string), _col4 (type: string), _col5 (type: string), _col6 (type: string), _col7 (type: array<struct<group_id:bigint,campaign_id:bigint,member_since_ts_utc:bigint>>), _col8 (type: timestamp), _col9 (type: timestamp), _col10 (type: timestamp), _col11 (type: int), _col12 (type: bigint), _col13 (type: string), _col15 (type: bigint) | Select Operator [SEL_1456] | outputColumnNames:["_col0","_col1","_col10","_col11","_col12","_col13","_col15","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9"] | Statistics:Num rows: 496014 Data size: 79362240 Basic stats: COMPLETE Column stats: PARTIAL | Filter Operator [FIL_1463] | predicate:((_col13 = 'baarn') and (_col13 = _col29) and (_col14 = _col30) and (_col0 = _col18) and (_col7 < _col25)) (type: boolean) | Statistics:Num rows: 496014 Data size: 179061054 Basic stats: COMPLETE Column stats: PARTIAL | Map Join Operator [MAPJOIN_1474] | | condition map:[{"":"Right Outer Join0 to 1"}] | | HybridGraceHashJoin:true | | keys:{"Map 2":"license_name (type: string), UDFToLong(campaign_id) (type: bigint), id (type: bigint)","Map 1":"license_name (type: string), campaign_id (type: bigint), id (type: bigint)"} | | outputColumnNames:["_col0","_col7","_col8","_col9","_col13","_col14","_col17","_col18","_col19","_col20","_col21","_col22","_col23","_col24","_col25","_col26","_col27","_col28","_col29","_col30"] | | Statistics:Num rows: 11904348 Data size: 24153922092 Basic stats: COMPLETE Column stats: PARTIAL | |<-Map 1 [BROADCAST_EDGE] | | Reduce Output Operator [RS_1446] | | key expressions:license_name (type: string), campaign_id (type: bigint), id (type: bigint) | | Map-reduce partition columns:license_name (type: string), campaign_id (type: bigint), id (type: bigint) | | sort order:+++ | | Statistics:Num rows: 621448 Data size: 79546063 Basic stats: COMPLETE Column stats: PARTIAL | | value expressions:updated_on_utc (type: timestamp), created_on_utc (type: timestamp), deleted_on_utc (type: timestamp), ROW__ID (type: struct<transactionid:bigint,bucketid:int,rowid:bigint>) | | TableScan [TS_1443] | | ACID table:true | | alias:dst | | Statistics:Num rows: 621448 Data size: 79546063 Basic stats: COMPLETE Column stats: PARTIAL | |<-TableScan [TS_1444] | alias:src | Statistics:Num rows: 11904348 Data size: 29935728348 Basic stats: COMPLETE Column stats: PARTIAL | Reduce Output Operator [RS_1451] | Map-reduce partition columns:_col0 (type: bigint) | sort order: | Statistics:Num rows: 1 Data size: 188 Basic stats: COMPLETE Column stats: PARTIAL | value expressions:_col0 (type: bigint), _col1 (type: string), _col2 (type: string), _col3 (type: string), _col4 (type: string), _col5 (type: string), _col6 (type: array<struct<group_id:bigint,campaign_id:bigint,member_since_ts_utc:bigint>>), _col7 (type: timestamp), _col10 (type: int), _col11 (type: bigint), _col12 (type: string), _col13 (type: string), _col14 (type: int) | Select Operator [SEL_1450] | outputColumnNames:["_col0","_col1","_col10","_col11","_col12","_col13","_col14","_col2","_col3","_col4","_col5","_col6","_col7"] | Statistics:Num rows: 1 Data size: 188 Basic stats: COMPLETE Column stats: PARTIAL | Filter Operator [FIL_1464] | predicate:(_col13 is null and _col14 is null and _col0 is null) (type: boolean) | Statistics:Num rows: 1 Data size: 361 Basic stats: COMPLETE Column stats: PARTIAL | Please refer to the previous Map Join Operator [MAPJOIN_1474] | Reducer 4 | File Output Operator [FS_1454] | compressed:true | Statistics:Num rows: 1 Data size: 188 Basic stats: COMPLETE Column stats: PARTIAL | table:{"name:":"vault.contact","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"} | Select Operator [SEL_1452] | | outputColumnNames:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9","_col10","_col11","_col12","_col13","_col14"] | | Statistics:Num rows: 1 Data size: 188 Basic stats: COMPLETE Column stats: PARTIAL | |<- Please refer to the previous Map 2 [SIMPLE_EDGE] | Stage-4 | Stats-Aggr Operator | Stage-1 | Move Operator | partition:{} | table:{"name:":"vault.contact","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"} | Please refer to the previous Stage-3 | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+ 0: jdbc:hive2://ip-10-0-0-21.eu-west-1.comput> Could anybody confirm or infirm from this explain that the partitions are properly pruned? hdp 2.6, small (4 nodes) AWS cluster ... View more