Hive详解之参数和变量设置

Hive参数大全：https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties

开发Hive应用时，不可避免地需要设定Hive的参数。设定Hive的参数可以调优HQL代码的执行效率，或帮助定位问题。然而实践中经常遇到的一个问题是，为什么设定的参数没有起作用？这通常是错误的设定方式导致的。

一、hive 参数设置方式

1.1、配置文件

Hive的配置文件包括：

• 用户自定义配置文件：$HIVE_CONF_DIR/hive-site.xml 
• 默认配置文件：$HIVE_CONF_DIR/hive-default.xml 

用户自定义配置会覆盖默认配置。另外，Hive也会读入Hadoop的配置，因为Hive是作为Hadoop的客户端启动的，Hive的配置会覆盖Hadoop的配置。配置文件的设定对本机启动的所有Hive进程都有效。

1.2、命令行参数

启动Hive（客户端或Server方式）时，可以在命令行添加-hiveconf param=value来设定参数，

例如：bin/hive -hiveconf hive.root.logger=INFO,console

常用：hive --hiveconf hive.cli.print.header=true （查看：set +属性名称）

这一设定对本次启动的Session（对于Server方式启动，则是所有请求的Sessions）有效。

1.3、参数声明

可以在HQL中使用SET关键字设定参数

例如：set mapred.reduce.tasks=100;

这一设定的作用域也是session级的。

总结：

上述三种设定方式的优先级依次递增。即参数声明覆盖命令行参数，命令行参数覆盖配置文件设定。注意某些系统级的参数，例如log4j相关的设定，必须用前两种方式设定，因为那些参数的读取在Session建立以前已经完成了。

1. hive参数初始化配置.hiverc文件 如:   ~/.hiverc 如果没有，可直接创建该文件，将需要设置的参数写到该文件中，hive启动运行时，会加载改文件中的配置。
2. 查看hive历史操作命令集：cat  ~/.hivehistory

二、hive 变量设置方式

• hive当中的参数、变量，都是以命名空间开头
• 通过${}方式进行引用，其中system、env下的变量必须以前缀开头

注：临时会话参数 hive -d val=1; 或者 hive -define val=1;  或者 hive --hivevar val=1;

补充：hive相关全部参数

hive> set;
_hive.hdfs.session.path=/tmp/hive/root/9a85e67f-c351-4f98-8589-7c1b2b7d17dd
_hive.local.session.path=/tmp/root/9a85e67f-c351-4f98-8589-7c1b2b7d17dd
_hive.tmp_table_space=/tmp/hive/root/9a85e67f-c351-4f98-8589-7c1b2b7d17dd/_tmp_space.db
datanucleus.autoCreateSchema=true
datanucleus.autoStartMechanismMode=checked
datanucleus.cache.level2=false
datanucleus.cache.level2.type=none
datanucleus.connectionPoolingType=BONECP
datanucleus.fixedDatastore=false
datanucleus.identifierFactory=datanucleus1
datanucleus.plugin.pluginRegistryBundleCheck=LOG
datanucleus.rdbms.useLegacyNativeValueStrategy=true
datanucleus.storeManagerType=rdbms
datanucleus.transactionIsolation=read-committed
datanucleus.validateColumns=false
datanucleus.validateConstraints=false
datanucleus.validateTables=false
dfs.block.access.key.update.interval=600
dfs.block.access.token.enable=false
dfs.block.access.token.lifetime=600
dfs.blockreport.initialDelay=0
dfs.blockreport.intervalMsec=21600000
dfs.blockreport.split.threshold=1000000
dfs.blocksize=134217728
dfs.bytes-per-checksum=512
dfs.cachereport.intervalMsec=10000
dfs.client-write-packet-size=65536
dfs.client.block.write.replace-datanode-on-failure.best-effort=false
dfs.client.block.write.replace-datanode-on-failure.enable=true
dfs.client.block.write.replace-datanode-on-failure.policy=DEFAULT
dfs.client.block.write.retries=3
dfs.client.cached.conn.retry=3
dfs.client.context=default
dfs.client.datanode-restart.timeout=30
dfs.client.domain.socket.data.traffic=false
dfs.client.failover.connection.retries=0
dfs.client.failover.connection.retries.on.timeouts=0
dfs.client.failover.max.attempts=15
dfs.client.failover.sleep.base.millis=500
dfs.client.failover.sleep.max.millis=15000
dfs.client.file-block-storage-locations.num-threads=10
dfs.client.file-block-storage-locations.timeout.millis=1000
dfs.client.https.keystore.resource=ssl-client.xml
dfs.client.https.need-auth=false
dfs.client.mmap.cache.size=256
dfs.client.mmap.cache.timeout.ms=3600000
dfs.client.mmap.enabled=true
dfs.client.mmap.retry.timeout.ms=300000
dfs.client.read.shortcircuit=false
dfs.client.read.shortcircuit.skip.checksum=false
dfs.client.read.shortcircuit.streams.cache.expiry.ms=300000
dfs.client.read.shortcircuit.streams.cache.size=256
dfs.client.short.circuit.replica.stale.threshold.ms=1800000
dfs.client.slow.io.warning.threshold.ms=30000
dfs.client.use.datanode.hostname=false
dfs.client.use.legacy.blockreader.local=false
dfs.client.write.exclude.nodes.cache.expiry.interval.millis=600000
dfs.datanode.address=0.0.0.0:50010
dfs.datanode.available-space-volume-choosing-policy.balanced-space-preference-fraction=0.75f
dfs.datanode.available-space-volume-choosing-policy.balanced-space-threshold=10737418240
dfs.datanode.balance.bandwidthPerSec=1048576
dfs.datanode.block.id.layout.upgrade.threads=12
dfs.datanode.bp-ready.timeout=20
dfs.datanode.cache.revocation.polling.ms=500
dfs.datanode.cache.revocation.timeout.ms=900000
dfs.datanode.data.dir=/opt/lxk/hadoop/hdfs/data
dfs.datanode.data.dir.perm=700
dfs.datanode.directoryscan.interval=21600
dfs.datanode.directoryscan.threads=1
dfs.datanode.dns.interface=default
dfs.datanode.dns.nameserver=default
dfs.datanode.drop.cache.behind.reads=false
dfs.datanode.drop.cache.behind.writes=false
dfs.datanode.du.reserved=0
dfs.datanode.failed.volumes.tolerated=0
dfs.datanode.fsdatasetcache.max.threads.per.volume=4
dfs.datanode.handler.count=10
dfs.datanode.hdfs-blocks-metadata.enabled=false
dfs.datanode.http.address=0.0.0.0:50075
dfs.datanode.https.address=0.0.0.0:50475
dfs.datanode.ipc.address=0.0.0.0:50020
dfs.datanode.max.locked.memory=0
dfs.datanode.max.transfer.threads=4096
dfs.datanode.readahead.bytes=4193404
dfs.datanode.shared.file.descriptor.paths=/dev/shm,/tmp
dfs.datanode.slow.io.warning.threshold.ms=300
dfs.datanode.sync.behind.writes=false
dfs.datanode.use.datanode.hostname=false
dfs.default.chunk.view.size=32768
dfs.encrypt.data.transfer=false
dfs.encrypt.data.transfer.cipher.key.bitlength=128
dfs.ha.automatic-failover.enabled=false
dfs.ha.log-roll.period=120
dfs.ha.tail-edits.period=60
dfs.heartbeat.interval=3
dfs.http.policy=HTTP_ONLY
dfs.https.enable=false
dfs.https.server.keystore.resource=ssl-server.xml
dfs.image.compress=false
dfs.image.compression.codec=org.apache.hadoop.io.compress.DefaultCodec
dfs.image.transfer.bandwidthPerSec=0
dfs.image.transfer.chunksize=65536
dfs.image.transfer.timeout=60000
dfs.journalnode.http-address=0.0.0.0:8480
dfs.journalnode.https-address=0.0.0.0:8481
dfs.journalnode.rpc-address=0.0.0.0:8485
dfs.namenode.accesstime.precision=3600000
dfs.namenode.acls.enabled=false
dfs.namenode.audit.loggers=default
dfs.namenode.avoid.read.stale.datanode=false
dfs.namenode.avoid.write.stale.datanode=false
dfs.namenode.backup.address=0.0.0.0:50100
dfs.namenode.backup.http-address=0.0.0.0:50105
dfs.namenode.checkpoint.check.period=60
dfs.namenode.checkpoint.dir=file://${hadoop.tmp.dir}/dfs/namesecondary
dfs.namenode.checkpoint.edits.dir=${dfs.namenode.checkpoint.dir}
dfs.namenode.checkpoint.max-retries=3
dfs.namenode.checkpoint.period=3600
dfs.namenode.checkpoint.txns=1000000
dfs.namenode.datanode.registration.ip-hostname-check=true
dfs.namenode.decommission.interval=30
dfs.namenode.decommission.nodes.per.interval=5
dfs.namenode.delegation.key.update-interval=86400000
dfs.namenode.delegation.token.max-lifetime=604800000
dfs.namenode.delegation.token.renew-interval=86400000
dfs.namenode.edit.log.autoroll.check.interval.ms=300000
dfs.namenode.edit.log.autoroll.multiplier.threshold=2.0
dfs.namenode.edits.dir=${dfs.namenode.name.dir}
dfs.namenode.edits.journal-plugin.qjournal=org.apache.hadoop.hdfs.qjournal.client.QuorumJournalManager
dfs.namenode.edits.noeditlogchannelflush=false
dfs.namenode.enable.retrycache=true
dfs.namenode.fs-limits.max-blocks-per-file=1048576
dfs.namenode.fs-limits.max-component-length=255
dfs.namenode.fs-limits.max-directory-items=1048576
dfs.namenode.fs-limits.max-xattr-size=16384
dfs.namenode.fs-limits.max-xattrs-per-inode=32
dfs.namenode.fs-limits.min-block-size=1048576
dfs.namenode.handler.count=10
dfs.namenode.http-address=0.0.0.0:50070
dfs.namenode.https-address=0.0.0.0:50470
dfs.namenode.inotify.max.events.per.rpc=1000
dfs.namenode.invalidate.work.pct.per.iteration=0.32f
dfs.namenode.kerberos.internal.spnego.principal=${dfs.web.authentication.kerberos.principal}
dfs.namenode.lazypersist.file.scrub.interval.sec=300
dfs.namenode.list.cache.directives.num.responses=100
dfs.namenode.list.cache.pools.num.responses=100
dfs.namenode.list.encryption.zones.num.responses=100
dfs.namenode.logging.level=info
dfs.namenode.max.extra.edits.segments.retained=10000
dfs.namenode.max.objects=0
dfs.namenode.name.dir=/opt/lxk/hadoop/hdfs/name
dfs.namenode.name.dir.restore=false
dfs.namenode.num.checkpoints.retained=2
dfs.namenode.num.extra.edits.retained=1000000
dfs.namenode.path.based.cache.block.map.allocation.percent=0.25
dfs.namenode.path.based.cache.refresh.interval.ms=30000
dfs.namenode.path.based.cache.retry.interval.ms=30000
dfs.namenode.reject-unresolved-dn-topology-mapping=false
dfs.namenode.replication.considerLoad=true
dfs.namenode.replication.interval=3
dfs.namenode.replication.min=1
dfs.namenode.replication.work.multiplier.per.iteration=2
dfs.namenode.resource.check.interval=5000
dfs.namenode.resource.checked.volumes.minimum=1
dfs.namenode.resource.du.reserved=104857600
dfs.namenode.retrycache.expirytime.millis=600000
dfs.namenode.retrycache.heap.percent=0.03f
dfs.namenode.safemode.extension=30000
dfs.namenode.safemode.min.datanodes=0
dfs.namenode.safemode.threshold-pct=0.999f
dfs.namenode.secondary.http-address=node04:50090
dfs.namenode.secondary.https-address=0.0.0.0:50091
dfs.namenode.stale.datanode.interval=30000
dfs.namenode.startup.delay.block.deletion.sec=0
dfs.namenode.support.allow.format=true
dfs.namenode.write.stale.datanode.ratio=0.5f
dfs.namenode.xattrs.enabled=true
dfs.permissions.enabled=true
dfs.permissions.superusergroup=supergroup
dfs.replication=2
dfs.replication.max=512
dfs.secondary.namenode.kerberos.internal.spnego.principal=${dfs.web.authentication.kerberos.principal}
dfs.short.circuit.shared.memory.watcher.interrupt.check.ms=60000
dfs.storage.policy.enabled=true
dfs.stream-buffer-size=4096
dfs.support.append=true
dfs.user.home.dir.prefix=/user
dfs.webhdfs.enabled=true
dfs.webhdfs.user.provider.user.pattern=^[A-Za-z_][A-Za-z0-9._-]*[$]?$
fs.har.impl=org.apache.hadoop.hive.shims.HiveHarFileSystem
hadoop.bin.path=/opt/lxk/hadoop-2.6.5/bin/hadoop
hadoop.fuse.connection.timeout=300
hadoop.fuse.timer.period=5
hadoop.hdfs.configuration.version=1
hive.analyze.stmt.collect.partlevel.stats=true
hive.archive.enabled=false
hive.auto.convert.join=true
hive.auto.convert.join.noconditionaltask=true
hive.auto.convert.join.noconditionaltask.size=10000000
hive.auto.convert.join.use.nonstaged=false
hive.auto.convert.sortmerge.join=false
hive.auto.convert.sortmerge.join.bigtable.selection.policy=org.apache.hadoop.hive.ql.optimizer.AvgPartitionSizeBasedBigTableSelectorForAutoSMJ
hive.auto.convert.sortmerge.join.to.mapjoin=false
hive.auto.progress.timeout=0s
hive.autogen.columnalias.prefix.includefuncname=false
hive.autogen.columnalias.prefix.label=_c
hive.binary.record.max.length=1000
hive.cache.expr.evaluation=true
hive.cbo.costmodel.cpu=0.000001
hive.cbo.costmodel.extended=false
hive.cbo.costmodel.hdfs.read=1.5
hive.cbo.costmodel.hdfs.write=10.0
hive.cbo.costmodel.local.fs.read=4.0
hive.cbo.costmodel.local.fs.write=4.0
hive.cbo.costmodel.network=150.0
hive.cbo.enable=true
hive.cbo.returnpath.hiveop=false
hive.cli.errors.ignore=false
hive.cli.pretty.output.num.cols=-1
hive.cli.print.current.db=false
hive.cli.print.header=true
hive.cli.prompt=hive
hive.cluster.delegation.token.store.class=org.apache.hadoop.hive.thrift.MemoryTokenStore
hive.cluster.delegation.token.store.zookeeper.znode=/hivedelegation
hive.compactor.abortedtxn.threshold=1000
hive.compactor.check.interval=300s
hive.compactor.cleaner.run.interval=5000ms
hive.compactor.delta.num.threshold=10
hive.compactor.delta.pct.threshold=0.1
hive.compactor.initiator.on=false
hive.compactor.worker.threads=0
hive.compactor.worker.timeout=86400s
hive.compat=0.12
hive.compute.query.using.stats=false
hive.compute.splits.in.am=true
hive.conf.restricted.list=hive.security.authenticator.manager,hive.security.authorization.manager,hive.users.in.admin.role
hive.conf.validation=true
hive.convert.join.bucket.mapjoin.tez=false
hive.counters.group.name=HIVE
hive.debug.localtask=false
hive.decode.partition.name=false
hive.default.fileformat=TextFile
hive.default.fileformat.managed=none
hive.default.rcfile.serde=org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe
hive.default.serde=org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
hive.display.partition.cols.separately=true
hive.downloaded.resources.dir=/tmp/${hive.session.id}_resources
hive.enforce.bucketing=false
hive.enforce.bucketmapjoin=false
hive.enforce.sorting=false
hive.enforce.sortmergebucketmapjoin=false
hive.entity.capture.transform=false
hive.entity.separator=@
hive.error.on.empty.partition=false
hive.exec.check.crossproducts=true
hive.exec.compress.intermediate=false
hive.exec.compress.output=false
hive.exec.concatenate.check.index=true
hive.exec.copyfile.maxsize=33554432
hive.exec.counters.pull.interval=1000
hive.exec.default.partition.name=__HIVE_DEFAULT_PARTITION__
hive.exec.drop.ignorenonexistent=true
hive.exec.dynamic.partition=true
hive.exec.dynamic.partition.mode=strict
hive.exec.infer.bucket.sort=false
hive.exec.infer.bucket.sort.num.buckets.power.two=false
hive.exec.job.debug.capture.stacktraces=true
hive.exec.job.debug.timeout=30000
hive.exec.local.scratchdir=/tmp/root
hive.exec.max.created.files=100000
hive.exec.max.dynamic.partitions=1000
hive.exec.max.dynamic.partitions.pernode=100
hive.exec.mode.local.auto=false
hive.exec.mode.local.auto.input.files.max=4
hive.exec.mode.local.auto.inputbytes.max=134217728
hive.exec.orc.block.padding.tolerance=0.05
hive.exec.orc.compression.strategy=SPEED
hive.exec.orc.default.block.padding=true
hive.exec.orc.default.block.size=268435456
hive.exec.orc.default.buffer.size=262144
hive.exec.orc.default.compress=ZLIB
hive.exec.orc.default.row.index.stride=10000
hive.exec.orc.default.stripe.size=67108864
hive.exec.orc.dictionary.key.size.threshold=0.8
hive.exec.orc.encoding.strategy=SPEED
hive.exec.orc.memory.pool=0.5
hive.exec.orc.skip.corrupt.data=false
hive.exec.orc.split.strategy=HYBRID
hive.exec.orc.zerocopy=false
hive.exec.parallel=false
hive.exec.parallel.thread.number=8
hive.exec.perf.logger=org.apache.hadoop.hive.ql.log.PerfLogger
hive.exec.rcfile.use.explicit.header=true
hive.exec.rcfile.use.sync.cache=true
hive.exec.reducers.bytes.per.reducer=256000000
hive.exec.reducers.max=1009
hive.exec.rowoffset=false
hive.exec.scratchdir=/tmp/hive
hive.exec.script.allow.partial.consumption=false
hive.exec.script.maxerrsize=100000
hive.exec.script.trust=false
hive.exec.show.job.failure.debug.info=true
hive.exec.stagingdir=.hive-staging
hive.exec.submit.local.task.via.child=true
hive.exec.submitviachild=false
hive.exec.tasklog.debug.timeout=20000
hive.exec.temporary.table.storage=default
hive.execution.engine=mr
hive.exim.strict.repl.tables=true
hive.exim.uri.scheme.whitelist=hdfs,pfile
hive.explain.dependency.append.tasktype=false
hive.explain.user=false
hive.fetch.output.serde=org.apache.hadoop.hive.serde2.DelimitedJSONSerDe
hive.fetch.task.aggr=false
hive.fetch.task.conversion=more
hive.fetch.task.conversion.threshold=1073741824
hive.file.max.footer=100
hive.fileformat.check=true
hive.groupby.mapaggr.checkinterval=100000
hive.groupby.orderby.position.alias=false
hive.groupby.skewindata=false
hive.hadoop.supports.splittable.combineinputformat=false
hive.hashtable.initialCapacity=100000
hive.hashtable.key.count.adjustment=1.0
hive.hashtable.loadfactor=0.75
hive.hbase.generatehfiles=false
hive.hbase.snapshot.restoredir=/tmp
hive.hbase.wal.enabled=true
hive.heartbeat.interval=1000
hive.hmshandler.force.reload.conf=false
hive.hmshandler.retry.attempts=10
hive.hmshandler.retry.interval=2000ms
hive.hwi.listen.host=0.0.0.0
hive.hwi.listen.port=9999
hive.hwi.war.file=${env:HWI_WAR_FILE}
hive.ignore.mapjoin.hint=true
hive.in.test=false
hive.in.tez.test=false
hive.index.compact.binary.search=true
hive.index.compact.file.ignore.hdfs=false
hive.index.compact.query.max.entries=10000000
hive.index.compact.query.max.size=10737418240
hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat
hive.insert.into.external.tables=true
hive.insert.into.multilevel.dirs=false
hive.int.timestamp.conversion.in.seconds=false
hive.io.rcfile.column.number.conf=0
hive.io.rcfile.record.buffer.size=4194304
hive.io.rcfile.record.interval=2147483647
hive.io.rcfile.tolerate.corruptions=false
hive.jobname.length=50
hive.join.cache.size=25000
hive.join.emit.interval=1000
hive.lazysimple.extended_boolean_literal=false
hive.limit.optimize.enable=false
hive.limit.optimize.fetch.max=50000
hive.limit.optimize.limit.file=10
hive.limit.pushdown.memory.usage=-1.0
hive.limit.query.max.table.partition=-1
hive.limit.row.max.size=100000
hive.localize.resource.num.wait.attempts=5
hive.localize.resource.wait.interval=5000ms
hive.lock.manager=org.apache.hadoop.hive.ql.lockmgr.zookeeper.ZooKeeperHiveLockManager
hive.lock.mapred.only.operation=false
hive.lock.numretries=100
hive.lock.sleep.between.retries=60s
hive.lockmgr.zookeeper.default.partition.name=__HIVE_DEFAULT_ZOOKEEPER_PARTITION__
hive.log.every.n.records=0
hive.log.explain.output=false
hive.map.aggr=true
hive.map.aggr.hash.force.flush.memory.threshold=0.9
hive.map.aggr.hash.min.reduction=0.5
hive.map.aggr.hash.percentmemory=0.5
hive.map.groupby.sorted=false
hive.map.groupby.sorted.testmode=false
hive.mapjoin.bucket.cache.size=100
hive.mapjoin.check.memory.rows=100000
hive.mapjoin.followby.gby.localtask.max.memory.usage=0.55
hive.mapjoin.followby.map.aggr.hash.percentmemory=0.3
hive.mapjoin.hybridgrace.hashtable=true
hive.mapjoin.hybridgrace.memcheckfrequency=1024
hive.mapjoin.hybridgrace.minnumpartitions=16
hive.mapjoin.hybridgrace.minwbsize=524288
hive.mapjoin.localtask.max.memory.usage=0.9
hive.mapjoin.optimized.hashtable=true
hive.mapjoin.optimized.hashtable.wbsize=10485760
hive.mapjoin.smalltable.filesize=25000000
hive.mapper.cannot.span.multiple.partitions=false
hive.mapred.local.mem=0
hive.mapred.mode=nonstrict
hive.mapred.partitioner=org.apache.hadoop.hive.ql.io.DefaultHivePartitioner
hive.mapred.reduce.tasks.speculative.execution=true
hive.mapred.supports.subdirectories=false
hive.merge.mapfiles=true
hive.merge.mapredfiles=false
hive.merge.orcfile.stripe.level=true
hive.merge.rcfile.block.level=true
hive.merge.size.per.task=256000000
hive.merge.smallfiles.avgsize=16000000
hive.merge.sparkfiles=false
hive.merge.tezfiles=false
hive.metadata.move.exported.metadata.to.trash=true
hive.metastore.aggregate.stats.cache.clean.until=0.8
hive.metastore.aggregate.stats.cache.enabled=true
hive.metastore.aggregate.stats.cache.fpp=0.01
hive.metastore.aggregate.stats.cache.max.full=0.9
hive.metastore.aggregate.stats.cache.max.partitions=10000
hive.metastore.aggregate.stats.cache.max.reader.wait=1000ms
hive.metastore.aggregate.stats.cache.max.variance=0.01
hive.metastore.aggregate.stats.cache.max.writer.wait=5000ms
hive.metastore.aggregate.stats.cache.size=10000
hive.metastore.aggregate.stats.cache.ttl=600s
hive.metastore.archive.intermediate.archived=_INTERMEDIATE_ARCHIVED
hive.metastore.archive.intermediate.extracted=_INTERMEDIATE_EXTRACTED
hive.metastore.archive.intermediate.original=_INTERMEDIATE_ORIGINAL
hive.metastore.authorization.storage.checks=false
hive.metastore.batch.retrieve.max=300
hive.metastore.batch.retrieve.table.partition.max=1000
hive.metastore.cache.pinobjtypes=Table,StorageDescriptor,SerDeInfo,Partition,Database,Type,FieldSchema,Order
hive.metastore.client.connect.retry.delay=1s
hive.metastore.client.drop.partitions.using.expressions=true
hive.metastore.client.socket.lifetime=0s
hive.metastore.client.socket.timeout=600s
hive.metastore.connect.retries=3
hive.metastore.direct.sql.batch.size=0
hive.metastore.disallow.incompatible.col.type.changes=false
hive.metastore.dml.events=false
hive.metastore.event.clean.freq=0s
hive.metastore.event.db.listener.timetolive=86400s
hive.metastore.event.expiry.duration=0s
hive.metastore.execute.setugi=true
hive.metastore.expression.proxy=org.apache.hadoop.hive.ql.optimizer.ppr.PartitionExpressionForMetastore
hive.metastore.failure.retries=1
hive.metastore.filter.hook=org.apache.hadoop.hive.metastore.DefaultMetaStoreFilterHookImpl
hive.metastore.fs.handler.class=org.apache.hadoop.hive.metastore.HiveMetaStoreFsImpl
hive.metastore.integral.jdo.pushdown=false
hive.metastore.kerberos.principal=hive-metastore/_HOST@EXAMPLE.COM
hive.metastore.orm.retrieveMapNullsAsEmptyStrings=false
hive.metastore.rawstore.impl=org.apache.hadoop.hive.metastore.ObjectStore
hive.metastore.sasl.enabled=false
hive.metastore.schema.verification=false
hive.metastore.schema.verification.record.version=true
hive.metastore.server.max.message.size=104857600
hive.metastore.server.max.threads=1000
hive.metastore.server.min.threads=200
hive.metastore.server.tcp.keepalive=true
hive.metastore.stats.ndv.densityfunction=false
hive.metastore.thrift.compact.protocol.enabled=false
hive.metastore.thrift.framed.transport.enabled=false
hive.metastore.try.direct.sql=true
hive.metastore.try.direct.sql.ddl=true
hive.metastore.warehouse.dir=/usr/hive_remote/warehouse
hive.multi.insert.move.tasks.share.dependencies=false
hive.multigroupby.singlereducer=true
hive.new.job.grouping.set.cardinality=30
hive.optimize.bucketingsorting=true
hive.optimize.bucketmapjoin=false
hive.optimize.bucketmapjoin.sortedmerge=false
hive.optimize.constant.propagation=true
hive.optimize.correlation=false
hive.optimize.distinct.rewrite=true
hive.optimize.groupby=true
hive.optimize.index.autoupdate=false
hive.optimize.index.filter=false
hive.optimize.index.filter.compact.maxsize=-1
hive.optimize.index.filter.compact.minsize=5368709120
hive.optimize.index.groupby=false
hive.optimize.listbucketing=false
hive.optimize.metadataonly=true
hive.optimize.null.scan=true
hive.optimize.ppd=true
hive.optimize.ppd.storage=true
hive.optimize.reducededuplication=true
hive.optimize.reducededuplication.min.reducer=4
hive.optimize.remove.identity.project=true
hive.optimize.sampling.orderby=false
hive.optimize.sampling.orderby.number=1000
hive.optimize.sampling.orderby.percent=0.1
hive.optimize.skewjoin=false
hive.optimize.skewjoin.compiletime=false
hive.optimize.sort.dynamic.partition=false
hive.optimize.union.remove=false
hive.orc.cache.stripe.details.size=10000
hive.orc.compute.splits.num.threads=10
hive.orc.row.index.stride.dictionary.check=true
hive.orc.splits.include.file.footer=false
hive.outerjoin.supports.filters=true
hive.parquet.timestamp.skip.conversion=true
hive.plan.serialization.format=kryo
hive.ppd.recognizetransivity=true
hive.ppd.remove.duplicatefilters=true
hive.prewarm.enabled=false
hive.prewarm.numcontainers=10
hive.query.id=root_20191002081124_28d18779-aa35-40e6-b209-99234305058d
hive.query.result.fileformat=TextFile
hive.query.string=select  * from psn
hive.querylog.enable.plan.progress=true
hive.querylog.location=/tmp/root
hive.querylog.plan.progress.interval=60000ms
hive.reorder.nway.joins=true
hive.repl.task.factory=org.apache.hive.hcatalog.api.repl.exim.EximReplicationTaskFactory
hive.resultset.use.unique.column.names=true
hive.rework.mapredwork=false
hive.rpc.query.plan=false
hive.sample.seednumber=0
hive.scratch.dir.permission=700
hive.script.auto.progress=false
hive.script.operator.env.blacklist=hive.txn.valid.txns,hive.script.operator.env.blacklist
hive.script.operator.id.env.var=HIVE_SCRIPT_OPERATOR_ID
hive.script.operator.truncate.env=false
hive.script.recordreader=org.apache.hadoop.hive.ql.exec.TextRecordReader
hive.script.recordwriter=org.apache.hadoop.hive.ql.exec.TextRecordWriter
hive.script.serde=org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
hive.security.authenticator.manager=org.apache.hadoop.hive.ql.security.HadoopDefaultAuthenticator
hive.security.authorization.enabled=false
hive.security.authorization.manager=org.apache.hadoop.hive.ql.security.authorization.DefaultHiveAuthorizationProvider
hive.security.authorization.sqlstd.confwhitelist=hive\.auto\..*
hive.server.read.socket.timeout=10s
hive.server.tcp.keepalive=true
hive.server2.allow.user.substitution=true
hive.server2.async.exec.keepalive.time=10s
hive.server2.async.exec.shutdown.timeout=10s
hive.server2.async.exec.threads=100
hive.server2.async.exec.wait.queue.size=100
hive.server2.authentication=NONE
hive.server2.enable.doAs=true
hive.server2.global.init.file.location=/root/hive/apache-hive-1.2.1-bin/conf
hive.server2.idle.operation.timeout=5d
hive.server2.idle.session.check.operation=true
hive.server2.idle.session.timeout=7d
hive.server2.logging.operation.enabled=true
hive.server2.logging.operation.level=EXECUTION
hive.server2.logging.operation.log.location=/tmp/root/operation_logs
hive.server2.long.polling.timeout=5000ms
hive.server2.map.fair.scheduler.queue=true
hive.server2.max.start.attempts=30
hive.server2.session.check.interval=6h
hive.server2.support.dynamic.service.discovery=false
hive.server2.table.type.mapping=CLASSIC
hive.server2.tez.initialize.default.sessions=false
hive.server2.tez.sessions.per.default.queue=1
hive.server2.thrift.exponential.backoff.slot.length=100ms
hive.server2.thrift.http.cookie.auth.enabled=true
hive.server2.thrift.http.cookie.is.httponly=true
hive.server2.thrift.http.cookie.is.secure=true
hive.server2.thrift.http.cookie.max.age=86400s
hive.server2.thrift.http.max.idle.time=1800s
hive.server2.thrift.http.path=cliservice
hive.server2.thrift.http.port=10001
hive.server2.thrift.http.worker.keepalive.time=60s
hive.server2.thrift.login.timeout=20s
hive.server2.thrift.max.message.size=104857600
hive.server2.thrift.max.worker.threads=500
hive.server2.thrift.min.worker.threads=5
hive.server2.thrift.port=10000
hive.server2.thrift.sasl.qop=auth
hive.server2.thrift.worker.keepalive.time=60s
hive.server2.transport.mode=binary
hive.server2.use.SSL=false
hive.server2.zookeeper.namespace=hiveserver2
hive.session.history.enabled=false
hive.session.id=9a85e67f-c351-4f98-8589-7c1b2b7d17dd
hive.session.silent=false
hive.skewjoin.key=100000
hive.skewjoin.mapjoin.map.tasks=10000
hive.skewjoin.mapjoin.min.split=33554432
hive.smbjoin.cache.rows=10000
hive.spark.client.connect.timeout=1000ms
hive.spark.client.future.timeout=60s
hive.spark.client.rpc.max.size=52428800
hive.spark.client.rpc.sasl.mechanisms=DIGEST-MD5
hive.spark.client.rpc.threads=8
hive.spark.client.secret.bits=256
hive.spark.client.server.connect.timeout=90000ms
hive.spark.job.monitor.timeout=60s
hive.ssl.protocol.blacklist=SSLv2,SSLv3
hive.stageid.rearrange=none
hive.start.cleanup.scratchdir=false
hive.stats.atomic=false
hive.stats.autogather=true
hive.stats.collect.rawdatasize=true
hive.stats.collect.scancols=false
hive.stats.collect.tablekeys=false
hive.stats.dbclass=fs
hive.stats.dbconnectionstring=jdbc:derby:;databaseName=TempStatsStore;create=true
hive.stats.deserialization.factor=1.0
hive.stats.fetch.column.stats=false
hive.stats.fetch.partition.stats=true
hive.stats.gather.num.threads=10
hive.stats.jdbc.timeout=30s
hive.stats.jdbcdriver=org.apache.derby.jdbc.EmbeddedDriver
hive.stats.join.factor=1.1
hive.stats.key.prefix.max.length=150
hive.stats.key.prefix.reserve.length=24
hive.stats.list.num.entries=10
hive.stats.map.num.entries=10
hive.stats.max.variable.length=100
hive.stats.ndv.error=20.0
hive.stats.reliable=false
hive.stats.retries.max=0
hive.stats.retries.wait=3000ms
hive.stats.tmp.loc=hdfs://192.168.18.103:9000/tmp/hive/root/9a85e67f-c351-4f98-8589-7c1b2b7d17dd/hive_2019-10-02_08-11-24_573_6621744203570378937-1/-mr-10000/.hive-staging_hive_2019-10-02_08-11-24_573_6621744203570378937-1/-ext-10002
hive.support.concurrency=false
hive.support.quoted.identifiers=column
hive.support.sql11.reserved.keywords=true
hive.test.authz.sstd.hs2.mode=false
hive.test.mode=false
hive.test.mode.prefix=test_
hive.test.mode.samplefreq=32
hive.tez.auto.reducer.parallelism=false
hive.tez.container.size=-1
hive.tez.cpu.vcores=-1
hive.tez.dynamic.partition.pruning=true
hive.tez.dynamic.partition.pruning.max.data.size=104857600
hive.tez.dynamic.partition.pruning.max.event.size=1048576
hive.tez.exec.inplace.progress=true
hive.tez.exec.print.summary=false
hive.tez.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat
hive.tez.log.level=INFO
hive.tez.max.partition.factor=2.0
hive.tez.min.partition.factor=0.25
hive.tez.smb.number.waves=0.5
hive.transform.escape.input=false
hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DummyTxnManager
hive.txn.max.open.batch=1000
hive.txn.timeout=300s
hive.txn.valid.txns=9223372036854775807:
hive.typecheck.on.insert=true
hive.udtf.auto.progress=false
hive.unlock.numretries=10
hive.user.install.directory=hdfs:///user/
hive.variable.substitute=true
hive.variable.substitute.depth=40
hive.vectorized.execution.enabled=false
hive.vectorized.execution.mapjoin.minmax.enabled=false
hive.vectorized.execution.mapjoin.native.enabled=true
hive.vectorized.execution.mapjoin.native.fast.hashtable.enabled=false
hive.vectorized.execution.mapjoin.native.multikey.only.enabled=false
hive.vectorized.execution.mapjoin.overflow.repeated.threshold=-1
hive.vectorized.execution.reduce.enabled=true
hive.vectorized.execution.reduce.groupby.enabled=true
hive.vectorized.groupby.checkinterval=100000
hive.vectorized.groupby.flush.percent=0.1
hive.vectorized.groupby.maxentries=1000000
hive.warehouse.subdir.inherit.perms=true
hive.zookeeper.clean.extra.nodes=false
hive.zookeeper.client.port=2181
hive.zookeeper.connection.basesleeptime=1000ms
hive.zookeeper.connection.max.retries=3
hive.zookeeper.namespace=hive_zookeeper_namespace
hive.zookeeper.session.timeout=1200000ms
javax.jdo.PersistenceManagerFactoryClass=org.datanucleus.api.jdo.JDOPersistenceManagerFactory
javax.jdo.option.ConnectionDriverName=com.mysql.jdbc.Driver
javax.jdo.option.ConnectionPassword=123
javax.jdo.option.ConnectionURL=jdbc:mysql://node04/hive_remote?createDatabaseIfNotExist=true
javax.jdo.option.ConnectionUserName=root
javax.jdo.option.DetachAllOnCommit=true
javax.jdo.option.Multithreaded=true
javax.jdo.option.NonTransactionalRead=true
mapreduce.input.fileinputformat.input.dir.recursive=false
mapreduce.input.fileinputformat.split.maxsize=256000000
mapreduce.input.fileinputformat.split.minsize=1
mapreduce.input.fileinputformat.split.minsize.per.node=1
mapreduce.input.fileinputformat.split.minsize.per.rack=1
mapreduce.job.committer.setup.cleanup.needed=false
mapreduce.job.committer.task.cleanup.needed=false
mapreduce.job.name=
mapreduce.job.reduces=-1
mapreduce.workflow.id=hive_root_20191002081124_28d18779-aa35-40e6-b209-99234305058d
mapreduce.workflow.name=select  * from psn
nfs.allow.insecure.ports=true
nfs.dump.dir=/tmp/.hdfs-nfs
nfs.mountd.port=4242
nfs.rtmax=1048576
nfs.server.port=2049
nfs.wtmax=1048576
parquet.memory.pool.ratio=0.5
rpc.engine.org.apache.hadoop.hdfs.protocolPB.ClientNamenodeProtocolPB=org.apache.hadoop.ipc.ProtobufRpcEngine
silent=off
stream.stderr.reporter.enabled=true
stream.stderr.reporter.prefix=reporter:
env:CLASSPATH=/opt/lxk/hadoop-2.6.5/etc/hadoop:/opt/lxk/hadoop-2.6.5/share/hadoop/common/lib/*:
env:FLUME_HOME=/opt/lxk/apache-flume-1.6.0-bin
env:G_BROKEN_FILENAMES=1
env:HADOOP_CLIENT_OPTS=-Xmx512m
env:HADOOP_COMMON_HOME=/opt/lxk/hadoop-2.6.5
env:HADOOP_CONF_DIR=/opt/lxk/hadoop-2.6.5/etc/hadoop
env:HADOOP_DATANODE_OPTS=-Dhadoop.security.logger=ERROR,RFAS
env:HADOOP_HDFS_HOME=/opt/lxk/hadoop-2.6.5
env:HADOOP_HEAPSIZE=256
env:HADOOP_HOME=/opt/lxk/hadoop-2.6.5
env:HADOOP_HOME_WARN_SUPPRESS=true
env:HADOOP_IDENT_STRING=root
env:HADOOP_MAPRED_HOME=/opt/lxk/hadoop-2.6.5
env:HADOOP_NAMENODE_OPTS=-Dhadoop.security.logger=INFO,RFAS -Dhdfs.audit.logger=INFO,NullAppender
env:HADOOP_NFS3_OPTS=
env:HADOOP_OPTS= -Djava.net.preferIPv4Stack=true -Dhadoop.log.dir=/opt/lxk/hadoop-2.6.5/logs -Dhadoop.log.file=hadoop.log -Dhadoop.home.dir=/opt/lxk/hadoop-2.6.5 -Dhadoop.id.str=root -Dhadoop.root.logger=INFO,console -Djava.library.path=/opt/lxk/hadoop-2.6.5/lib/native -Dhadoop.policy.file=hadoop-policy.xml -Djava.net.preferIPv4Stack=true -Xmx512m  -Dhadoop.security.logger=INFO,NullAppender
env:HADOOP_PID_DIR=
env:HADOOP_PORTMAP_OPTS=-Xmx512m
env:HADOOP_PREFIX=/opt/lxk/hadoop-2.6.5
env:HADOOP_SECONDARYNAMENODE_OPTS=-Dhadoop.security.logger=INFO,RFAS -Dhdfs.audit.logger=INFO,NullAppender
env:HADOOP_SECURE_DN_LOG_DIR=/
env:HADOOP_SECURE_DN_PID_DIR=
env:HADOOP_SECURE_DN_USER=
env:HADOOP_YARN_HOME=/opt/lxk/hadoop-2.6.5
env:HBASE_HOME=/root/hbase/hbase0.98
env:HISTCONTROL=ignoredups
env:HISTSIZE=1000
env:HIVE_AUX_JARS_PATH=
env:HIVE_CONF_DIR=/root/hive/apache-hive-1.2.1-bin/conf
env:HIVE_HOME=/root/hive/apache-hive-1.2.1-bin
env:HOME=/root
env:HOSTNAME=node03
env:JAVA_HOME=/usr/local/src/java/jdk1.8.0_181
env:LANG=en_US.UTF-8
env:LD_LIBRARY_PATH=:/opt/lxk/hadoop-2.6.5/lib/native
env:LESSOPEN=|/usr/bin/lesspipe.sh %s
env:LOGNAME=root
env:MAIL=/var/spool/mail/root
env:MALLOC_ARENA_MAX=4
env:PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/src/java/jdk1.8.0_181/bin:/opt/lxk/hadoop-2.6.5/bin:/opt/lxk/hadoop-2.6.5/sbin:/root/hive/apache-hive-1.2.1-bin/bin:/root/hbase/hbase0.98/bin:/opt/huawei/zookeeper-3.4.6/bin:/opt/lxk/apache-flume-1.6.0-bin/bin:/root/bin
env:PWD=/root
env:SERVICE_LIST=beeline cli help hiveburninclient hiveserver2 hiveserver hwi jar lineage metastore metatool orcfiledump rcfilecat schemaTool version
env:SHELL=/bin/bash
env:SHLVL=1
env:SSH_CLIENT=192.168.18.1 6609 22
env:SSH_CONNECTION=192.168.18.1 6609 192.168.18.103 22
env:SSH_TTY=/dev/pts/0
env:TERM=xterm
env:USER=root
env:ZOOKEEPER_HOME=/opt/huawei/zookeeper-3.4.6
system:awt.toolkit=sun.awt.X11.XToolkit
system:file.encoding=UTF-8
system:file.encoding.pkg=sun.io
system:file.separator=/
system:hadoop.home.dir=/opt/lxk/hadoop-2.6.5
system:hadoop.id.str=root
system:hadoop.log.dir=/opt/lxk/hadoop-2.6.5/logs
system:hadoop.log.file=hadoop.log
system:hadoop.policy.file=hadoop-policy.xml
system:hadoop.root.logger=INFO,console
system:hadoop.security.logger=INFO,NullAppender
system:java.awt.graphicsenv=sun.awt.X11GraphicsEnvironment
system:java.awt.printerjob=sun.print.PSPrinterJob
system:java.class.path=/opt/lxk/hadoop-2.6.5/etc/hadoop:/opt/lxk/hadoop-2.6.5/share/hadoop/common/lib/htrace-core-3.0.4.jar
system:java.class.version=52.0
system:java.endorsed.dirs=/usr/local/src/java/jdk1.8.0_181/jre/lib/endorsed
system:java.ext.dirs=/usr/local/src/java/jdk1.8.0_181/jre/lib/ext:/usr/java/packages/lib/ext
system:java.home=/usr/local/src/java/jdk1.8.0_181/jre
system:java.io.tmpdir=/tmp
system:java.library.path=/opt/lxk/hadoop-2.6.5/lib/native
system:java.net.preferIPv4Stack=true
system:java.runtime.name=Java(TM) SE Runtime Environment
system:java.runtime.version=1.8.0_181-b13
system:java.specification.name=Java Platform API Specification
system:java.specification.vendor=Oracle Corporation
system:java.specification.version=1.8
system:java.vendor=Oracle Corporation
system:java.vendor.url=http://java.oracle.com/
system:java.vendor.url.bug=http://bugreport.sun.com/bugreport/
system:java.version=1.8.0_181
system:java.vm.info=mixed mode
system:java.vm.name=Java HotSpot(TM) 64-Bit Server VM
system:java.vm.specification.name=Java Virtual Machine Specification
system:java.vm.specification.vendor=Oracle Corporation
system:java.vm.specification.version=1.8
system:java.vm.vendor=Oracle Corporation
system:java.vm.version=25.181-b13
system:line.separator=
system:os.arch=amd64
system:os.name=Linux
system:os.version=2.6.32-431.el6.x86_64
system:path.separator=:
system:sun.arch.data.model=64
system:sun.boot.class.path=/usr/local/src/java/jdk1.8.0_181/jre/lib/resources.jar:/usr/local/src/java/jdk1.8.0_181/jre/lib/rt.jar:/usr/local/src/java/jdk1.8.0_181/jre/lib/sunrsasign.jar:/usr/local/src/java/jdk1.8.0_181/jre/lib/jsse.jar:/usr/local/src/java/jdk1.8.0_181/jre/lib/jce.jar:/usr/local/src/java/jdk1.8.0_181/jre/lib/charsets.jar:/usr/local/src/java/jdk1.8.0_181/jre/lib/jfr.jar:/usr/local/src/java/jdk1.8.0_181/jre/classes
system:sun.boot.library.path=/usr/local/src/java/jdk1.8.0_181/jre/lib/amd64
system:sun.cpu.endian=little
system:sun.cpu.isalist=
system:sun.io.unicode.encoding=UnicodeLittle
system:sun.java.command=org.apache.hadoop.util.RunJar /root/hive/apache-hive-1.2.1-bin/lib/hive-cli-1.2.1.jar org.apache.hadoop.hive.cli.CliDriver
system:sun.java.launcher=SUN_STANDARD
system:sun.jnu.encoding=UTF-8
system:sun.management.compiler=HotSpot 64-Bit Tiered Compilers
system:sun.os.patch.level=unknown
system:user.country=US
system:user.dir=/root
system:user.home=/root
system:user.language=en
system:user.name=root
system:user.timezone=PRC