[yinzhengjie@s101 ~]$ more `which xcall.sh`

#!/bin/bash

#@author :yinzhengjie

#blog:http://www.cnblogs.com/yinzhengjie

#EMAIL:y1053419035@qq.com

#判断用户是否传参

if [ $# -lt  ];then

        echo "请输入参数"

        exit

fi

#获取用户输入的命令

cmd=$@

for (( i=;i<=;i++ ))

do

        #使终端变绿色

        tput setaf

        echo ============= s$i $cmd ============

        #使终端变回原来的颜色，即白灰色

        tput setaf

        #远程执行命令

        ssh s$i $cmd

        #判断命令是否执行成功

        if [ $? ==  ];then

                echo "命令执行成功"

        fi

done

[yinzhengjie@s101 ~]$ 

[yinzhengjie@s101 ~]$ more `which start-dfs.sh` | grep -v ^# | grep -v ^$

usage="Usage: start-dfs.sh [-upgrade|-rollback] [other options such as -clusterId]"

bin=`dirname "${BASH_SOURCE-$0}"`

bin=`cd "$bin"; pwd`

DEFAULT_LIBEXEC_DIR="$bin"/../libexec

HADOOP_LIBEXEC_DIR=${HADOOP_LIBEXEC_DIR:-$DEFAULT_LIBEXEC_DIR}

. $HADOOP_LIBEXEC_DIR/hdfs-config.sh

if [[ $# -ge  ]]; then

  startOpt="$1"

  shift

  case "$startOpt" in

    -upgrade)

      nameStartOpt="$startOpt"

    ;;

    -rollback)

      dataStartOpt="$startOpt"

    ;;

    *)

      echo $usage

      exit

    ;;

  esac

fi

nameStartOpt="$nameStartOpt $@"

NAMENODES=$($HADOOP_PREFIX/bin/hdfs getconf -namenodes)

echo "Starting namenodes on [$NAMENODES]"

"$HADOOP_PREFIX/sbin/hadoop-daemons.sh" \

  --config "$HADOOP_CONF_DIR" \

  --hostnames "$NAMENODES" \

  --script "$bin/hdfs" start namenode $nameStartOpt

if [ -n "$HADOOP_SECURE_DN_USER" ]; then

  echo \

    "Attempting to start secure cluster, skipping datanodes. " \

    "Run start-secure-dns.sh as root to complete startup."

else

  "$HADOOP_PREFIX/sbin/hadoop-daemons.sh" \

    --config "$HADOOP_CONF_DIR" \

    --script "$bin/hdfs" start datanode $dataStartOpt

fi

SECONDARY_NAMENODES=$($HADOOP_PREFIX/bin/hdfs getconf -secondarynamenodes >/dev/null)

if [ -n "$SECONDARY_NAMENODES" ]; then

  echo "Starting secondary namenodes [$SECONDARY_NAMENODES]"

  "$HADOOP_PREFIX/sbin/hadoop-daemons.sh" \

      --config "$HADOOP_CONF_DIR" \

      --hostnames "$SECONDARY_NAMENODES" \

      --script "$bin/hdfs" start secondarynamenode

fi

SHARED_EDITS_DIR=$($HADOOP_PREFIX/bin/hdfs getconf -confKey dfs.namenode.shared.edits.dir >&-)

case "$SHARED_EDITS_DIR" in

qjournal://*)

  JOURNAL_NODES=$(echo "$SHARED_EDITS_DIR" | sed 's,qjournal://\([^/]*\)/.*,\1,g; s/;/ /g; s/:[0-9]*//g')

  echo "Starting journal nodes [$JOURNAL_NODES]"

  "$HADOOP_PREFIX/sbin/hadoop-daemons.sh" \

      --config "$HADOOP_CONF_DIR" \

      --hostnames "$JOURNAL_NODES" \

      --script "$bin/hdfs" start journalnode ;;

esac

AUTOHA_ENABLED=$($HADOOP_PREFIX/bin/hdfs getconf -confKey dfs.ha.automatic-failover.enabled)

if [ "$(echo "$AUTOHA_ENABLED" | tr A-Z a-z)" = "true" ]; then

  echo "Starting ZK Failover Controllers on NN hosts [$NAMENODES]"

  "$HADOOP_PREFIX/sbin/hadoop-daemons.sh" \

    --config "$HADOOP_CONF_DIR" \

    --hostnames "$NAMENODES" \

    --script "$bin/hdfs" start zkfc

fi

[yinzhengjie@s101 ~]$

[yinzhengjie@s101 ~]$ cat /soft/hadoop/sbin/start-yarn.sh | grep -v ^# | grep -v ^$

echo "starting yarn daemons"

bin=`dirname "${BASH_SOURCE-$0}"`

bin=`cd "$bin"; pwd`

DEFAULT_LIBEXEC_DIR="$bin"/../libexec

HADOOP_LIBEXEC_DIR=${HADOOP_LIBEXEC_DIR:-$DEFAULT_LIBEXEC_DIR}

. $HADOOP_LIBEXEC_DIR/yarn-config.sh

"$bin"/yarn-daemon.sh --config $YARN_CONF_DIR  start resourcemanager

"$bin"/yarn-daemons.sh --config $YARN_CONF_DIR  start nodemanager

[yinzhengjie@s101 ~]$

[yinzhengjie@s101 ~]$ more `which xzk.sh`

#!/bin/bash

#@author :yinzhengjie

#blog:http://www.cnblogs.com/yinzhengjie

#EMAIL:y1053419035@qq.com

#判断用户是否传参

if [ $# -ne  ];then

    echo "无效参数，用法为: $0  {start|stop|restart|status}"

    exit

fi

#获取用户输入的命令

cmd=$

#定义函数功能

function zookeeperManger(){

    case $cmd in

    start)

        echo "启动服务"

        remoteExecution start

        ;;

    stop)

        echo "停止服务"

        remoteExecution stop

        ;;

    restart)

        echo "重启服务"

        remoteExecution restart

        ;;

    status)

        echo "查看状态"

        remoteExecution status

        ;;

    *)

        echo "无效参数，用法为: $0  {start|stop|restart|status}"

        ;;

    esac

}

#定义执行的命令

function remoteExecution(){

    for (( i= ; i<= ; i++ )) ; do

            tput setaf

            echo ========== s$i zkServer.sh  $ ================

            tput setaf

            ssh s$i  "source /etc/profile ; zkServer.sh $1"

    done

}

#调用函数

zookeeperManger

[yinzhengjie@s101 ~]$ 

[yinzhengjie@s101 ~]$ xzk.sh start

启动服务

========== s102 zkServer.sh start ================

ZooKeeper JMX enabled by default

Using config: /soft/zk/bin/../conf/zoo.cfg

Starting zookeeper ... STARTED

========== s103 zkServer.sh start ================

ZooKeeper JMX enabled by default

Starting zookeeper ... Using config: /soft/zk/bin/../conf/zoo.cfg

STARTED

========== s104 zkServer.sh start ================

ZooKeeper JMX enabled by default

Using config: /soft/zk/bin/../conf/zoo.cfg

Starting zookeeper ... STARTED

[yinzhengjie@s101 ~]$

[yinzhengjie@s101 ~]$ xcall.sh jps

============= s101 jps ============

 Jps

命令执行成功

============= s102 jps ============

 QuorumPeerMain

 Jps

命令执行成功

============= s103 jps ============

 QuorumPeerMain

 Jps

命令执行成功

============= s104 jps ============

 Jps

 QuorumPeerMain

命令执行成功

============= s105 jps ============

 Jps

命令执行成功

[yinzhengjie@s101 ~]$ 

[yinzhengjie@s101 ~]$ start-dfs.sh

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in [jar:file:/soft/hadoop-2.7./share/hadoop/common/lib/slf4j-log4j12-1.7..jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/soft/apache-hive-2.1.-bin/lib/log4j-slf4j-impl-2.4..jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.

SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]

Starting namenodes on [s101 s105]

s101: starting namenode, logging to /soft/hadoop-2.7./logs/hadoop-yinzhengjie-namenode-s101.out

s105: starting namenode, logging to /soft/hadoop-2.7./logs/hadoop-yinzhengjie-namenode-s105.out

s103: starting datanode, logging to /soft/hadoop-2.7./logs/hadoop-yinzhengjie-datanode-s103.out

s102: starting datanode, logging to /soft/hadoop-2.7./logs/hadoop-yinzhengjie-datanode-s102.out

s104: starting datanode, logging to /soft/hadoop-2.7./logs/hadoop-yinzhengjie-datanode-s104.out

Starting journal nodes [s102 s103 s104]

s102: starting journalnode, logging to /soft/hadoop-2.7./logs/hadoop-yinzhengjie-journalnode-s102.out

s103: starting journalnode, logging to /soft/hadoop-2.7./logs/hadoop-yinzhengjie-journalnode-s103.out

s104: starting journalnode, logging to /soft/hadoop-2.7./logs/hadoop-yinzhengjie-journalnode-s104.out

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in [jar:file:/soft/hadoop-2.7./share/hadoop/common/lib/slf4j-log4j12-1.7..jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/soft/apache-hive-2.1.-bin/lib/log4j-slf4j-impl-2.4..jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.

SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]

Starting ZK Failover Controllers on NN hosts [s101 s105]

s101: starting zkfc, logging to /soft/hadoop-2.7./logs/hadoop-yinzhengjie-zkfc-s101.out

s105: starting zkfc, logging to /soft/hadoop-2.7./logs/hadoop-yinzhengjie-zkfc-s105.out

[yinzhengjie@s101 ~]$

[yinzhengjie@s101 ~]$

[yinzhengjie@s101 ~]$ xcall.sh jps

============= s101 jps ============

 Jps

 NameNode

 DFSZKFailoverController

命令执行成功

============= s102 jps ============

 JournalNode

 QuorumPeerMain

 DataNode

 Jps

命令执行成功

============= s103 jps ============

 Jps

 DataNode

 JournalNode

 QuorumPeerMain

命令执行成功

============= s104 jps ============

 Jps

 DataNode

 QuorumPeerMain

 JournalNode

命令执行成功

============= s105 jps ============

 DFSZKFailoverController

 NameNode

 Jps

命令执行成功

[yinzhengjie@s101 ~]$ 

[yinzhengjie@s101 ~]$ start-yarn.sh

starting yarn daemons

s101: starting resourcemanager, logging to /soft/hadoop-2.7./logs/yarn-yinzhengjie-resourcemanager-s101.out

s105: starting resourcemanager, logging to /soft/hadoop-2.7./logs/yarn-yinzhengjie-resourcemanager-s105.out

s103: starting nodemanager, logging to /soft/hadoop-2.7./logs/yarn-yinzhengjie-nodemanager-s103.out

s102: starting nodemanager, logging to /soft/hadoop-2.7./logs/yarn-yinzhengjie-nodemanager-s102.out

s104: starting nodemanager, logging to /soft/hadoop-2.7./logs/yarn-yinzhengjie-nodemanager-s104.out

[yinzhengjie@s101 ~]$

[yinzhengjie@s101 ~]$

[yinzhengjie@s101 ~]$ xcall.sh jps

============= s101 jps ============

 ResourceManager

 Jps

 NameNode

 DFSZKFailoverController

命令执行成功

============= s102 jps ============

 JournalNode

 QuorumPeerMain

 NodeManager

 Jps

 DataNode

命令执行成功

============= s103 jps ============

 DataNode

 JournalNode

 NodeManager

 Jps

 QuorumPeerMain

命令执行成功

============= s104 jps ============

 NodeManager

 Jps

 DataNode

 QuorumPeerMain

 JournalNode

命令执行成功

============= s105 jps ============

 DFSZKFailoverController

 NameNode

 Jps

命令执行成功

[yinzhengjie@s101 ~]$ 

[yinzhengjie@s101 download]$ cat teachers.txt

    Dennis MacAlistair Ritchie

    Linus Benedict Torvalds

    Bjarne Stroustrup

    Guido van Rossum

    James Gosling

    Martin Odersky

    Rob Pike

    Rasmus Lerdorf

    Brendan Eich

[yinzhengjie@s101 download]$ 

[yinzhengjie@s101 ~]$ hive -help

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in [jar:file:/soft/apache-hive-2.1.-bin/lib/log4j-slf4j-impl-2.4..jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/soft/hbase-1.2./lib/phoenix-4.10.-HBase-1.2-client.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/soft/hadoop-2.7./share/hadoop/common/lib/slf4j-log4j12-1.7..jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.

usage: hive

 -d,--define <key=value>          Variable subsitution to apply to hive

                                  commands. e.g. -d A=B or --define A=B

    --database <databasename>     Specify the database to use

 -e <quoted-query-string>         SQL from command line

 -f <filename>                    SQL from files

 -H,--help                        Print help information

    --hiveconf <property=value>   Use value for given property

    --hivevar <key=value>         Variable subsitution to apply to hive

                                  commands. e.g. --hivevar A=B

 -i <filename>                    Initialization SQL file

 -S,--silent                      Silent mode in interactive shell

 -v,--verbose                     Verbose mode (echo executed SQL to the

                                  console)

[yinzhengjie@s101 ~]$ 

[yinzhengjie@s101 ~]$ hive

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in [jar:file:/soft/apache-hive-2.1.-bin/lib/log4j-slf4j-impl-2.4..jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/soft/hbase-1.2./lib/phoenix-4.10.-HBase-1.2-client.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/soft/hadoop-2.7./share/hadoop/common/lib/slf4j-log4j12-1.7..jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.

Logging initialized using configuration in jar:file:/soft/apache-hive-2.1.-bin/lib/hive-common-2.1..jar!/hive-log4j2.properties Async: true

default

yinzhengjie

Hive-on-MR is deprecated in Hive  and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive .X releases.

hive> 

[yinzhengjie@s101 ~]$ hive

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in [jar:file:/soft/apache-hive-2.1.-bin/lib/log4j-slf4j-impl-2.4..jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/soft/hbase-1.2./lib/phoenix-4.10.-HBase-1.2-client.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/soft/hadoop-2.7./share/hadoop/common/lib/slf4j-log4j12-1.7..jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.

Logging initialized using configuration in jar:file:/soft/apache-hive-2.1.-bin/lib/hive-common-2.1..jar!/hive-log4j2.properties Async: true

default

yinzhengjie

Hive-on-MR is deprecated in Hive  and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive .X releases.

hive> show databases;

OK

default

yinzhengjie

Time taken: 0.01 seconds, Fetched:  row(s)

hive> 

[yinzhengjie@s101 ~]$ hive

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in [jar:file:/soft/apache-hive-2.1.-bin/lib/log4j-slf4j-impl-2.4..jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/soft/hbase-1.2./lib/phoenix-4.10.-HBase-1.2-client.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/soft/hadoop-2.7./share/hadoop/common/lib/slf4j-log4j12-1.7..jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.

Logging initialized using configuration in jar:file:/soft/apache-hive-2.1.-bin/lib/hive-common-2.1..jar!/hive-log4j2.properties Async: true

default

yinzhengjie

Hive-on-MR is deprecated in Hive  and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive .X releases.

hive> show databases;

OK

default

yinzhengjie

Time taken: 0.008 seconds, Fetched:  row(s)

hive> use yinzhengjie;

OK

Time taken: 0.018 seconds

hive> 

hive> show databases;

OK

default

yinzhengjie

Time taken: 0.008 seconds, Fetched:  row(s)

hive> use yinzhengjie;

OK

Time taken: 0.018 seconds

hive> show tables;

OK

az_top3

az_wc

test1

test2

test3

test4

yzj

Time taken: 0.025 seconds, Fetched:  row(s)

hive> 

hive> show databases;

OK

default

yinzhengjie

Time taken: 0.008 seconds, Fetched:  row(s)

hive> use yinzhengjie;

OK

Time taken: 0.018 seconds

hive> show tables;

OK

az_top3

az_wc

test1

test2

test3

test4

yzj

Time taken: 0.025 seconds, Fetched:  row(s)

hive> create table Teacher(id int,name string)row format delimited fields terminated by '\t';

OK

Time taken: 0.626 seconds

hive> show tables;

OK

az_top3

az_wc

teacher

test1

test2

test3

test4

yzj

Time taken: 0.028 seconds, Fetched:  row(s)

hive> 

hive> show tables;

OK

teacher

yzj

Time taken: 0.022 seconds, Fetched:  row(s)

hive> select * from teacher;

OK

Time taken: 0.105 seconds

hive> load data local inpath '/home/yinzhengjie/download/teachers.txt' into table yinzhengjie.teacher;

Loading data to table yinzhengjie.teacher

OK

Time taken: 0.256 seconds

hive> select * from teacher;

OK

    Dennis MacAlistair Ritchie

    Linus Benedict Torvalds

    Bjarne Stroustrup

    Guido van Rossum

    James Gosling

    Martin Odersky

    Rob Pike

    Rasmus Lerdorf

    Brendan Eich

Time taken: 0.104 seconds, Fetched:  row(s)

hive> 

hive (yinzhengjie)> load data inpath '/home/yinzhengjie/data/logs/umeng/raw-log/201808/06/2346' into table raw_logs partition(ym=201808 , day=06 ,hm=2346);

Loading data to table yinzhengjie.raw_logs partition (ym=201808, day=6, hm=2346)

OK

Time taken: 1.846 seconds

hive (yinzhengjie)>

从hdfs上加载数据到hive中已经存在的表（hive (yinzhengjie)> load data inpath '/home/yinzhengjie/data/logs/umeng/raw-log/201808/06/2346' into table raw_logs partition(ym=201808 , day=06 ,hm=2346);）

[yinzhengjie@s101 download]$ cat /home/yinzhengjie/download/umeng_create_logs_ddl.sql

use yinzhengjie ;

--startuplogs

create table if not exists startuplogs

(

  appChannel             string ,

  appId             string ,

  appPlatform             string ,

  appVersion             string ,

  brand             string ,

  carrier             string ,

  country             string ,

  createdAtMs             bigint ,

  deviceId             string ,

  deviceStyle             string ,

  ipAddress             string ,

  network             string ,

  osType             string ,

  province             string ,

  screenSize             string ,

  tenantId             string

)

partitioned by (ym int ,day int , hm int)

stored as parquet ;

--eventlogs

create table if not exists eventlogs

(

  appChannel             string ,

  appId             string ,

  appPlatform             string ,

  appVersion             string ,

  createdAtMs             bigint ,

  deviceId             string ,

  deviceStyle             string ,

  eventDurationSecs             bigint ,

  eventId             string ,

  osType             string ,

  tenantId             string

)

partitioned by (ym int ,day int , hm int)

stored as parquet ;

--errorlogs

create table if not exists errorlogs

(

  appChannel             string ,

  appId             string ,

  appPlatform             string ,

  appVersion             string ,

  createdAtMs             bigint ,

  deviceId             string ,

  deviceStyle             string ,

  errorBrief             string ,

  errorDetail             string ,

  osType             string ,

  tenantId             string

)

partitioned by (ym int ,day int , hm int)

stored as parquet ;

--usagelogs

create table if not exists usagelogs

(

  appChannel             string ,

  appId             string ,

  appPlatform             string ,

  appVersion             string ,

  createdAtMs             bigint ,

  deviceId             string ,

  deviceStyle             string ,

  osType             string ,

  singleDownloadTraffic             bigint ,

  singleUploadTraffic             bigint ,

  singleUseDurationSecs             bigint ,

  tenantId             string

)

partitioned by (ym int ,day int , hm int)

stored as parquet ;

--pagelogs

create table if not exists pagelogs

(

  appChannel             string ,

  appId             string ,

  appPlatform             string ,

  appVersion             string ,

  createdAtMs             bigint ,

  deviceId             string ,

  deviceStyle             string ,

  nextPage             string ,

  osType             string ,

  pageId             string ,

  pageViewCntInSession             int ,

  stayDurationSecs             bigint ,

  tenantId             string ,

  visitIndex             int

)

partitioned by (ym int ,day int , hm int)

stored as parquet ;

[yinzhengjie@s101 download]$ 

HQL测试语句（[yinzhengjie@s101 download]$ cat /home/yinzhengjie/download/umeng_create_logs_ddl.sql）

hive (yinzhengjie)> show tables;

OK

tab_name

myusers

raw_logs

student

teacher

teacherbak

teachercopy

Time taken: 0.044 seconds, Fetched: 6 row(s)

hive (yinzhengjie)>

hive (yinzhengjie)> source /home/yinzhengjie/download/umeng_create_logs_ddl.sql;

OK

Time taken: 0.008 seconds

OK

Time taken: 0.257 seconds

OK

Time taken: 0.058 seconds

OK

Time taken: 0.073 seconds

OK

Time taken: 0.065 seconds

OK

Time taken: 0.053 seconds

hive (yinzhengjie)> show tables;

OK

tab_name

errorlogs

eventlogs

myusers

pagelogs

raw_logs

startuplogs

student

teacher

teacherbak

teachercopy

usagelogs

Time taken: 0.014 seconds, Fetched: 11 row(s)

hive (yinzhengjie)> 

在hive中执行HQL语句文本文件（hive (yinzhengjie)> source /home/yinzhengjie/download/umeng_create_logs_ddl.sql;）

[yinzhengjie@s101 ~]$ hive

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in [jar:file:/soft/apache-hive-2.1.-bin/lib/log4j-slf4j-impl-2.4..jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/soft/hbase-1.2./lib/phoenix-4.10.-HBase-1.2-client.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/soft/hadoop-2.7./share/hadoop/common/lib/slf4j-log4j12-1.7..jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.

Logging initialized using configuration in jar:file:/soft/apache-hive-2.1.-bin/lib/hive-common-2.1..jar!/hive-log4j2.properties Async: true

Hive-on-MR is deprecated in Hive  and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive .X releases.

hive> dfs -cat /user/hive/warehouse/yinzhengjie.db/teacher/teachers.txt;

    Dennis MacAlistair Ritchie

    Linus Benedict Torvalds

    Bjarne Stroustrup

    Guido van Rossum

    James Gosling

    Martin Odersky

    Rob Pike

    Rasmus Lerdorf

    Brendan Eich

hive>

[yinzhengjie@s101 ~]$ hive

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in [jar:file:/soft/apache-hive-2.1.-bin/lib/log4j-slf4j-impl-2.4..jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/soft/hbase-1.2./lib/phoenix-4.10.-HBase-1.2-client.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/soft/hadoop-2.7./share/hadoop/common/lib/slf4j-log4j12-1.7..jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.

Logging initialized using configuration in jar:file:/soft/apache-hive-2.1.-bin/lib/hive-common-2.1..jar!/hive-log4j2.properties Async: true

Hive-on-MR is deprecated in Hive  and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive .X releases.

hive> ! ls /home/yinzhengjie/download;

derby.log

hivef.sql

metastore_db

MySpark.jar

spark-2.1.-bin-hadoop2..tgz

teachers.txt

temp

hive> 

[yinzhengjie@s101 download]$ cat ~/.hivehistory

show databases;

 quit;

show databases;

quit

;

create table(id int,name string) row format delimited

fields terminated by '\t'

lines terminated by '\n'

stored as textfile;

create table users(id int , name string) row format delimited

fields terminated by '\t'

lines terminated by '\n'

stored as textfile;

load data local inpath 'user.txt' into table users;

!pwd

;

!cd /home/yinzhengjie

;

!pwd

;

quit;

load data local inpath 'user.txt' into table users;

load data inpath  'user.txt' into table users;

hdfs dfs -put 'user.txt';

hdfs dfs put 'user.txt';

dfs put 'user.txt';

dfs -put 'user.txt';

dfs -put 'user.txt' /;

dfs -put user.txt ;

dfs -put user.txt /;

load data inpath  'user.txt' into table users;

load data inpath  '/user.txt' into table users;

;;

;

;;

ipconfig

;

quit

quit;

exit

exit;

show databases;

use yinzhengjie

;

show tables;

SET hive.support.concurrency = true;

show tables;

use yinzhengjie;

show tables;

select * from yzj;

SET hive.support.concurrency = true;

SET hive.enforce.bucketing = true;

SET hive.exec.dynamic.partition.mode = nonstrict;

SET hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;

SET hive.compactor.initiator.on = true;

SET hive.compactor.worker.threads = ;

select * from yzj;

use yinzhengjie;

SET hive.support.concurrency = true;

SET hive.enforce.bucketing = true;

SET hive.exec.dynamic.partition.mode = nonstrict;

SET hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;

SET hive.compactor.initiator.on = true;

SET hive.compactor.worker.threads = ;

show tables;

select * from yzj;

show databases;

use yinzhengjie;

show tables;

hive

show databases;

use yinzhengjie;

show tables;

select * from az_top3;

quit;

show databases;

use yinzhengjie

;

show tables;

use yinzhengjie;

show databases;

use yinzhengjie;

show tables;

create table Teacher(id int,name string)row format delimited fields terminated by '\t';

show tables;

load data local inpath '/home/yinzhengjie/download/teachers.txt'

;

show tables;

drop table taacher;

show databases;

use yinzhengjie;

show tables;

drop table teacher;

show tables;

;

show tables;

create table Teacher(id int,name string)row format delimited fields terminated by '\t';

show tables;

drop table test1,test2,test3;

drop table test1;

drop table test2;

drop table test3;

drop table test4;

show tables;

drop table az_top3;

drop table az_wc;

show tbales;

show databasers;

show databases;

drop database yinzhengjie;

;

use yinzhengjie;

show tables;

drop table teacher;

show tables;

create table Teacher(id int,name string)row format delimited fields terminated by '\t';

show tables;

load data local inpath '/home/yinzhengjie/download/teachers.txt';

load data local inpath `/home/yinzhengjie/download/teachers.txt`;

use yinzhengjie

;

show tables;

load data local inpath '/home/yinzhengjie/download/teachers.txt' into table yinzhengjie.teacher;

select * from teacher;

drop table teacher;

;

create table Teacher(id int,name string)row format delimited fields terminated by '\t';

show tables;

select * from teacher;

load data local inpath '/home/yinzhengjie/download/teachers.txt' into table yinzhengjie.teacher;

select * from teacher;

quit;

exit;

exit

;

dfs -cat /user/hive/warehouse/yinzhengjie.db/teacher/teachers.txt;

dfs -lsr /;

;

! ls /home/yinzhengjie;

! ls /home/yinzhengjie/download;

[yinzhengjie@s101 download]$ 

[yinzhengjie@s101 download]$ hive -e "select * from yinzhengjie.teacher;"

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in [jar:file:/soft/apache-hive-2.1.-bin/lib/log4j-slf4j-impl-2.4..jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/soft/hbase-1.2./lib/phoenix-4.10.-HBase-1.2-client.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/soft/hadoop-2.7./share/hadoop/common/lib/slf4j-log4j12-1.7..jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.

Logging initialized using configuration in jar:file:/soft/apache-hive-2.1.-bin/lib/hive-common-2.1..jar!/hive-log4j2.properties Async: true

default

yinzhengjie

OK

    Dennis MacAlistair Ritchie

    Linus Benedict Torvalds

    Bjarne Stroustrup

    Guido van Rossum

    James Gosling

    Martin Odersky

    Rob Pike

    Rasmus Lerdorf

    Brendan Eich

Time taken: 3.414 seconds, Fetched:  row(s)

[yinzhengjie@s101 download]$

[yinzhengjie@s101 download]$ hive -f /home/yinzhengjie/download/hivef.sql

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in [jar:file:/soft/apache-hive-2.1.-bin/lib/log4j-slf4j-impl-2.4..jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/soft/hbase-1.2./lib/phoenix-4.10.-HBase-1.2-client.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/soft/hadoop-2.7./share/hadoop/common/lib/slf4j-log4j12-1.7..jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.

Logging initialized using configuration in jar:file:/soft/apache-hive-2.1.-bin/lib/hive-common-2.1..jar!/hive-log4j2.properties Async: true

default

yinzhengjie

OK

Time taken: 0.023 seconds

OK

teacher

yzj

Time taken: 0.085 seconds, Fetched:  row(s)

OK

    Dennis MacAlistair Ritchie

    Linus Benedict Torvalds

    Bjarne Stroustrup

    Guido van Rossum

    James Gosling

    Martin Odersky

    Rob Pike

    Rasmus Lerdorf

    Brendan Eich

Time taken: 2.044 seconds, Fetched:  row(s)

[yinzhengjie@s101 download]$ 

[yinzhengjie@s101 ~]$ hive

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in [jar:file:/soft/apache-hive-2.1.-bin/lib/log4j-slf4j-impl-2.4..jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/soft/hbase-1.2./lib/phoenix-4.10.-HBase-1.2-client.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/soft/hadoop-2.7./share/hadoop/common/lib/slf4j-log4j12-1.7..jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.

Logging initialized using configuration in jar:file:/soft/apache-hive-2.1.-bin/lib/hive-common-2.1..jar!/hive-log4j2.properties Async: true

Hive-on-MR is deprecated in Hive  and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive .X releases.

hive> quit;

[yinzhengjie@s101 ~]$

[yinzhengjie@s101 ~]$ hive

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in [jar:file:/soft/apache-hive-2.1.-bin/lib/log4j-slf4j-impl-2.4..jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/soft/hbase-1.2./lib/phoenix-4.10.-HBase-1.2-client.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/soft/hadoop-2.7./share/hadoop/common/lib/slf4j-log4j12-1.7..jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.

Logging initialized using configuration in jar:file:/soft/apache-hive-2.1.-bin/lib/hive-common-2.1..jar!/hive-log4j2.properties Async: true

Hive-on-MR is deprecated in Hive  and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive .X releases.

hive> exit;

[yinzhengjie@s101 ~]$ 

hive (yinzhengjie)> show databases;

OK

database_name

default

yinzhengjie

Time taken: 0.007 seconds, Fetched:  row(s)

hive (yinzhengjie)> create database if not exists db_hive;

OK

Time taken: 0.034 seconds

hive (yinzhengjie)> show databases;

OK

database_name

db_hive

default

yinzhengjie

Time taken: 0.009 seconds, Fetched:  row(s)

hive (yinzhengjie)> 

hive (yinzhengjie)> show databases;

OK

database_name

db_hive

default

yinzhengjie

Time taken: 0.008 seconds, Fetched:  row(s)

hive (yinzhengjie)> create database if not exists db_hive2 location "/db_hive2";

OK

Time taken: 0.04 seconds

hive (yinzhengjie)> show databases;

OK

database_name

db_hive

db_hive2

default

yinzhengjie

Time taken: 0.006 seconds, Fetched:  row(s)

hive (yinzhengjie)> 

    用户可以使用ALTER DATABASE 命令为某个数据库的DBPROPERTIES设置键-值对属性值，来描述这个数据库的属性信息。

数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。

    hive (yinzhengjie)> show databases;

    OK

    database_name

    db_hive

    db_hive2

    default

    yinzhengjie

    Time taken: 0.007 seconds, Fetched:  row(s)

    hive (yinzhengjie)> ALTER DATABASE db_hive set dbproperties('Owner'='yinzhengjie');        #给数据库添加额外的属性，注意，这里并没有修改数据库里的元数据！

    OK

    Time taken: 0.03 seconds

    hive (yinzhengjie)> desc database db_hive;                                                #使用这条命令是查不到的咱们定义的属性的哟！

    OK

    db_name    comment    location    owner_name    owner_type    parameters

    db_hive        hdfs://mycluster/user/hive/warehouse/db_hive.db    yinzhengjie    USER

    Time taken: 0.017 seconds, Fetched:  row(s)

    hive (yinzhengjie)> desc database extended db_hive;                                        #我们需要在数据库前加一个extended关键字，就能查看到我们定义的数据库属性。

    OK

    db_name    comment    location    owner_name    owner_type    parameters

    db_hive        hdfs://mycluster/user/hive/warehouse/db_hive.db    yinzhengjie    USER    {Owner=yinzhengjie}

    Time taken: 0.011 seconds, Fetched:  row(s)

    hive (yinzhengjie)> 

hive (yinzhengjie)> show databases;                                #显示所有的数据库

OK

database_name

db_hive

db_hive2

default

yinzhengjie

Time taken: 0.008 seconds, Fetched:  row(s)

hive (yinzhengjie)>

hive (yinzhengjie)> show databases like 'yin*';                    #过滤显示的查询的数据库

OK

database_name

yinzhengjie

Time taken: 0.009 seconds, Fetched:  row(s)

hive (yinzhengjie)>

hive (yinzhengjie)> desc database db_hive;                          #显示数据库信息

OK

db_name    comment    location    owner_name    owner_type    parameters

db_hive        hdfs://mycluster/user/hive/warehouse/db_hive.db    yinzhengjie    USER

Time taken: 0.012 seconds, Fetched:  row(s)

hive (yinzhengjie)> desc database extended db_hive;                  #显示数据库详细信息，使用关键字：extended

OK

db_name    comment    location    owner_name    owner_type    parameters

db_hive        hdfs://mycluster/user/hive/warehouse/db_hive.db    yinzhengjie    USER    {Owner=yinzhengjie}

Time taken: 0.013 seconds, Fetched:  row(s)

hive (yinzhengjie)>

hive (yinzhengjie)> show databases;

OK

database_name

db_hive

db_hive2

default

yinzhengjie

Time taken: 0.006 seconds, Fetched:  row(s)

hive (yinzhengjie)> use default;                                     #使用数据库

OK

Time taken: 0.012 seconds

hive (default)> 

hive (yinzhengjie)> show databases;

OK

database_name

db_hive

db_hive2

default

yinzhengjie

Time taken: 0.006 seconds, Fetched:  row(s)

hive (yinzhengjie)> use db_hive2;                                #使用db_hive2数据库

OK

Time taken: 0.014 seconds

hive (db_hive2)> show tables;                                    #db_hive2数据库中没有任何表

OK

tab_name

Time taken: 0.015 seconds

hive (db_hive2)> drop database if exists db_hive2;                #删除空的数据库db_hive2

OK

Time taken: 0.05 seconds

hive (db_hive2)> show databases;

OK

database_name

db_hive

default

yinzhengjie

Time taken: 0.006 seconds, Fetched:  row(s)

hive (db_hive2)> use db_hive;                                    #使用db_hive数据库

OK

Time taken: 0.012 seconds

hive (db_hive)> show tables;                                    #db_hive2数据库中是有数据表的

OK

tab_name

classlist

student

teacher

Time taken: 0.016 seconds, Fetched:  row(s)

hive (db_hive)> drop database db_hive cascade;                    #使用关键字cascade强制删除有数据的数据库db_hive

OK

Time taken: 0.304 seconds

hive (db_hive)> use yinzhengjie;

OK

Time taken: 0.016 seconds

hive (yinzhengjie)> show databases;

OK

database_name

default

yinzhengjie

Time taken: 0.007 seconds, Fetched:  row(s)

hive (yinzhengjie)> 

hive (yinzhengjie)> show tables;

OK

tab_name

student

teacher

Time taken: 0.015 seconds, Fetched:  row(s)

hive (yinzhengjie)> create table if not exists teacherbak as select id, name from teacher;                #根据查询结果创建表，即查询的结果会添加到新创建的表中，它会自动启用一个job

WARNING: Hive-on-MR is deprecated in Hive  and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive .X releases.

Query ID = yinzhengjie_20180806000505_71d796a2----b5d39abd58c9

Total jobs =

Launching Job  out of

Number of reduce tasks is set to  since there's no reduce operator

Starting Job = job_1533518652134_0001, Tracking URL = http://s101:8088/proxy/application_1533518652134_0001/

Kill Command = /soft/hadoop-2.7./bin/hadoop job  -kill job_1533518652134_0001

Hadoop job information for Stage-: number of mappers: ; number of reducers:

-- ::, Stage- map = %,  reduce = %

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 2.02 sec

MapReduce Total cumulative CPU time:  seconds  msec

Ended Job = job_1533518652134_0001

Stage- is selected by condition resolver.

Stage- is filtered out by condition resolver.

Stage- is filtered out by condition resolver.

Moving data to directory hdfs://mycluster/user/hive/warehouse/yinzhengjie.db/.hive-staging_hive_2018-08-06_00-05-05_947_8165112419833752968-1/-ext-10002

Moving data to directory hdfs://mycluster/user/hive/warehouse/yinzhengjie.db/teacherbak

MapReduce Jobs Launched:

Stage-Stage-: Map:    Cumulative CPU: 2.02 sec   HDFS Read:  HDFS Write:  SUCCESS

Total MapReduce CPU Time Spent:  seconds  msec

OK

id    name

Time taken: 33.117 seconds

hive (yinzhengjie)> show tables;

OK

tab_name

student

teacher

teacherbak

Time taken: 0.014 seconds, Fetched:  row(s)

hive (yinzhengjie)> select id, name from teacher;                #查看teacher表中的数据

OK

id    name

    Dennis MacAlistair Ritchie

    Linus Benedict Torvalds

    Bjarne Stroustrup

    Guido van Rossum

    James Gosling

    Martin Odersky

    Rob Pike

    Rasmus Lerdorf

    Brendan Eich

Time taken: 0.093 seconds, Fetched:  row(s)

hive (yinzhengjie)>

hive (yinzhengjie)> select id, name from teacherbak;            #查看teacherbak表中的数据，我们会发现其内容和teacher一致

OK

id    name

    Dennis MacAlistair Ritchie

    Linus Benedict Torvalds

    Bjarne Stroustrup

    Guido van Rossum

    James Gosling

    Martin Odersky

    Rob Pike

    Rasmus Lerdorf

    Brendan Eich

Time taken: 0.083 seconds, Fetched:  row(s)

hive (yinzhengjie)> 

hive (yinzhengjie)> show tables;

OK

tab_name

student

teacher

teacherbak

Time taken: 0.013 seconds, Fetched:  row(s)

hive (yinzhengjie)> desc teacher;

OK

col_name    data_type    comment

id                      int

name                    string

Time taken: 0.029 seconds, Fetched:  row(s)

hive (yinzhengjie)> select * from teacher;

OK

teacher.id    teacher.name

    Dennis MacAlistair Ritchie

    Linus Benedict Torvalds

    Bjarne Stroustrup

    Guido van Rossum

    James Gosling

    Martin Odersky

    Rob Pike

    Rasmus Lerdorf

    Brendan Eich

Time taken: 0.1 seconds, Fetched:  row(s)

hive (yinzhengjie)> create table if not exists teacherCopy like teacher;        #根据已经存在的表结构创建表,即只复制表结构，并不会复制表中的数据

OK

Time taken: 0.181 seconds

hive (yinzhengjie)> show tables;

OK

tab_name

student

teacher

teacherbak

teachercopy

Time taken: 0.014 seconds, Fetched:  row(s)

hive (yinzhengjie)> select * from teachercopy;

OK

teachercopy.id    teachercopy.name

Time taken: 0.103 seconds

hive (yinzhengjie)> desc teachercopy;

OK

col_name    data_type    comment

id                      int

name                    string

Time taken: 0.03 seconds, Fetched:  row(s)

hive (yinzhengjie)> 

hive (yinzhengjie)> show tables;

OK

tab_name

student

teacher

teacherbak

teachercopy

Time taken: 0.012 seconds, Fetched:  row(s)

hive (yinzhengjie)> desc formatted teacher;                        #查询表的类型

OK

col_name    data_type    comment

# col_name                data_type               comment             

id                      int

name                    string                                      

# Detailed Table Information

Database:               yinzhengjie

Owner:                  yinzhengjie

CreateTime:             Sun Aug  :: PDT

LastAccessTime:         UNKNOWN

Retention:

Location:               hdfs://mycluster/user/hive/warehouse/yinzhengjie.db/teacher

Table Type:             MANAGED_TABLE                            #显示器对面的小哥哥小姐姐往这里看，这里可以查看当前表的类型哟，这里明显是管理表，也称为内部表。

Table Parameters:

    numFiles

    numRows

    rawDataSize

    totalSize

    transient_lastDdlTime              

# Storage Information

SerDe Library:          org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

InputFormat:            org.apache.hadoop.mapred.TextInputFormat

OutputFormat:           org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat

Compressed:             No

Num Buckets:            -

Bucket Columns:         []

Sort Columns:           []

Storage Desc Params:

    field.delim             \t

    serialization.format    \t

Time taken: 0.036 seconds, Fetched:  row(s)

hive (yinzhengjie)> 

一.查看原始数据

[yinzhengjie@s101 download]$ pwd

/home/yinzhengjie/download

[yinzhengjie@s101 download]$

[yinzhengjie@s101 download]$ cat dept.dat

    ACCOUNTING

    RESEARCH

    SALES

    OPERATIONS

[yinzhengjie@s101 download]$

[yinzhengjie@s101 download]$ more emp.dat

    SMITH    CLERK        --    800.00

    ALLEN    SALESMAN        --    1600.00    300.00

    WARD    SALESMAN        --    1250.00    500.00

    JONES    MANAGER        --    2975.00

    MARTIN    SALESMAN        --    1250.00    1400.00

    BLAKE    MANAGER        --    2850.00

    CLARK    MANAGER        --    2450.00

    SCOTT    ANALYST        --    3000.00

    KING    PRESIDENT        --    5000.00

    TURNER    SALESMAN        --    1500.00    0.00

    ADAMS    CLERK        --    1100.00

    JAMES    CLERK        --    950.00

    FORD    ANALYST        --    3000.00

    MILLER    CLERK        --    1300.00

[yinzhengjie@s101 download]$ 

二.使用关键字external创建外部表语句

>.创建部门表

hive (yinzhengjie)> create external table if not exists yinzhengjie.dept(

                  >     deptno int,

                  >     dname string,

                  >     loc int

                  > )

                  > row format delimited fields terminated by '\t';

OK

Time taken: 0.096 seconds

hive (yinzhengjie)> 

>.创建员工表

hive (yinzhengjie)> create external table if not exists yinzhengjie.emp(

                  >     empno int,

                  >     ename string,

                  >     job string,

                  >     mgr int,

                  >     hiredate string,

                  >     sal double,

                  >     comm double,

                  >     deptno int

                  > )

                  > row format delimited fields terminated by '\t';

OK

Time taken: 0.064 seconds

hive (yinzhengjie)> 

>.向外部表中导入数据

hive (yinzhengjie)> load data local inpath '/home/yinzhengjie/download/dept.dat' into table yinzhengjie.dept;

Loading data to table yinzhengjie.dept

OK

Time taken: 0.222 seconds

hive (yinzhengjie)>

hive (yinzhengjie)> select * from dept;                            #导入成功后需要查看dept表中是否有数据

OK

dept.deptno    dept.dname    dept.loc

    ACCOUNTING

    RESEARCH

    SALES

    OPERATIONS

Time taken: 0.088 seconds, Fetched:  row(s)

hive (yinzhengjie)>

hive (yinzhengjie)> load data local inpath '/home/yinzhengjie/download/emp.dat' into table yinzhengjie.emp;

Loading data to table yinzhengjie.emp

OK

Time taken: 0.21 seconds

hive (yinzhengjie)>

hive (yinzhengjie)> select * from emp;                            #导入成功后需要查看emp表中是否有数据

OK

emp.empno    emp.ename    emp.job    emp.mgr    emp.hiredate    emp.sal    emp.comm    emp.deptno

    SMITH    CLERK        --    800.0    NULL

    ALLEN    SALESMAN        --    1600.0    300.0

    WARD    SALESMAN        --    1250.0    500.0

    JONES    MANAGER        --    2975.0    NULL

    MARTIN    SALESMAN        --    1250.0    1400.0

    BLAKE    MANAGER        --    2850.0    NULL

    CLARK    MANAGER        --    2450.0    NULL

    SCOTT    ANALYST        --    3000.0    NULL

    KING    PRESIDENT    NULL    --    5000.0    NULL

    TURNER    SALESMAN        --    1500.0    0.0

    ADAMS    CLERK        --    1100.0    NULL

    JAMES    CLERK        --    950.0    NULL

    FORD    ANALYST        --    3000.0    NULL

    MILLER    CLERK        --    1300.0    NULL

Time taken: 0.079 seconds, Fetched:  row(s)

hive (yinzhengjie)> 

>.查看表类型

hive (yinzhengjie)> desc formatted dept;                #查看dept表格式化数据

OK

col_name    data_type    comment

# col_name                data_type               comment             

deptno                  int

dname                   string

loc                     int                                         

# Detailed Table Information

Database:               yinzhengjie

Owner:                  yinzhengjie

CreateTime:             Mon Aug  :: PDT

LastAccessTime:         UNKNOWN

Retention:

Location:               hdfs://mycluster/user/hive/warehouse/yinzhengjie.db/dept

Table Type:             EXTERNAL_TABLE                   #Duang~显示器面前的小哥哥小姐姐往这看，这里有查看dept表的的类型是外部表哟！

Table Parameters:

    EXTERNAL                TRUE

    numFiles

    numRows

    rawDataSize

    totalSize

    transient_lastDdlTime              

# Storage Information

SerDe Library:          org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

InputFormat:            org.apache.hadoop.mapred.TextInputFormat

OutputFormat:           org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat

Compressed:             No

Num Buckets:            -

Bucket Columns:         []

Sort Columns:           []

Storage Desc Params:

    field.delim             \t

    serialization.format    \t

Time taken: 0.036 seconds, Fetched:  row(s)

hive (yinzhengjie)> desc formatted emp;                #查看emp表格式化数据

OK

col_name    data_type    comment

# col_name                data_type               comment             

empno                   int

ename                   string

job                     string

mgr                     int

hiredate                string

sal                     double

comm                    double

deptno                  int                                         

# Detailed Table Information

Database:               yinzhengjie

Owner:                  yinzhengjie

CreateTime:             Mon Aug  :: PDT

LastAccessTime:         UNKNOWN

Retention:

Location:               hdfs://mycluster/user/hive/warehouse/yinzhengjie.db/emp

Table Type:             EXTERNAL_TABLE                   #Duang~显示器面前的小哥哥小姐姐往这看，这里有查看emp表的的类型是外部表哟！

Table Parameters:

    EXTERNAL                TRUE

    numFiles

    numRows

    rawDataSize

    totalSize

    transient_lastDdlTime              

# Storage Information

SerDe Library:          org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

InputFormat:            org.apache.hadoop.mapred.TextInputFormat

OutputFormat:           org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat

Compressed:             No

Num Buckets:            -

Bucket Columns:         []

Sort Columns:           []

Storage Desc Params:

    field.delim             \t

    serialization.format    \t

Time taken: 0.036 seconds, Fetched:  row(s)

hive (yinzhengjie)> 

>.在hive中删除外部表并不会删除hdfs的真实数据

hive (yinzhengjie)> show tables;

OK

tab_name

dept

emp

student

teacher

teacherbak

teachercopy

Time taken: 0.014 seconds, Fetched:  row(s)

hive (yinzhengjie)> drop table dept;

OK

Time taken: 0.122 seconds

hive (yinzhengjie)> drop table emp;

OK

Time taken: 0.079 seconds

hive (yinzhengjie)> show tables;                                                        #你会发现删除了元数据表，并没有删除真实数据，我们可以在hive中通过dfs命令查看真实数据

OK

tab_name

student

teacher

teacherbak

teachercopy

Time taken: 0.013 seconds, Fetched:  row(s)

hive (yinzhengjie)>

hive (yinzhengjie)> dfs -cat /user/hive/warehouse/yinzhengjie.db/dept/dept.dat;            #怎么样？hdfs中的文件内容依旧存在，并没有删除，hive只是删除了元数据而已。

    ACCOUNTING

    RESEARCH

    SALES

    OPERATIONS

hive (yinzhengjie)>

                  > dfs -cat /user/hive/warehouse/yinzhengjie.db/emp/emp.dat;            #怎么样？hdfs中的文件内容依旧存在，并没有删除，hive只是删除了元数据而已。

    SMITH    CLERK        --    800.00

    ALLEN    SALESMAN        --    1600.00    300.00

    WARD    SALESMAN        --    1250.00    500.00

    JONES    MANAGER        --    2975.00

    MARTIN    SALESMAN        --    1250.00    1400.00

    BLAKE    MANAGER        --    2850.00

    CLARK    MANAGER        --    2450.00

    SCOTT    ANALYST        --    3000.00

    KING    PRESIDENT        --    5000.00

    TURNER    SALESMAN        --    1500.00    0.00

    ADAMS    CLERK        --    1100.00

    JAMES    CLERK        --    950.00

    FORD    ANALYST        --    3000.00

    MILLER    CLERK        --    1300.00

hive (yinzhengjie)> 

分区表的特点总结如下：

    >.分区表实际上就是对应一个HDFS文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。

    >.Hive中的分区就是对应一个HDFS文件系统上分目录，把一个大的数据集根据业务的需要分割成小的数据集。

    >.在查询时通过where子句中的表达式选择查询所需要的指定分区，这样的查询效率会提高很多。

[yinzhengjie@s101 download]$ cat users.txt

    yinzhengjie

    Guido van Rossum

    Martin Odersky

    Rasmus Lerdorf

[yinzhengjie@s101 download]$

[yinzhengjie@s101 download]$ cat dept.txt

    开发部门

    运维部门

    测试部门

    产品部门

    销售部门

    财务部门

    人事部门

[yinzhengjie@s101 download]$ 

分区表的特点总结以及测试数据“dept.txt”和"users.txt"文本内容

hive (yinzhengjie)> show tables;

OK

tab_name

Time taken: 0.038 seconds

hive (yinzhengjie)> create table dept_partition(

                  >     deptno int,

                  >     dname string,

                  >     loc string

                  > )

                  > partitioned by (month string)

                  > row format delimited fields terminated by '\t';

OK

Time taken: 0.262 seconds

hive (yinzhengjie)>

hive (yinzhengjie)> show tables;

OK

tab_name

dept_partition

Time taken: 0.035 seconds, Fetched:  row(s)

hive (yinzhengjie)> 

hive (yinzhengjie)> create table users (

                  >     id int,

                  >     name string,

                  >     age int

                  > )

                  > partitioned by (province string, city string)

                  > row format delimited fields terminated by '\t';

OK

Time taken: 1.046 seconds

hive (yinzhengjie)> show tables;

OK

tab_name

dept_partition

raw_logs

student

teacher

teacherbak

teachercopy

users

Time taken: 0.26 seconds, Fetched: 7 row(s)

hive (yinzhengjie)> 

分区表-创建二级分区表语法（hive (yinzhengjie)> create table users (id int,name string, age int) partitioned by (province string, city string) row format delimited fields terminated by '\t';）

hive (yinzhengjie)> create table users (id int,name string, age int) partitioned by (province string, city string) row format delimited fields terminated by '\t';        #创建二级分区

OK

Time taken: 0.071 seconds

hive (yinzhengjie)> load data local inpath '/home/yinzhengjie/download/users.txt' into table users partition(province='hebei',city='shijiazhuang');                #加载数到擦创建的二级分区中

Loading data to table yinzhengjie.users partition (province=hebei, city=shijiazhuang)

OK

Time taken: 0.482 seconds

hive (yinzhengjie)> load data local inpath '/home/yinzhengjie/download/users.txt' into table users partition(province='shanxi',city='xian');

Loading data to table yinzhengjie.users partition (province=shanxi, city=xian)

OK

Time taken: 0.414 seconds

hive (yinzhengjie)> select * from users;

OK

users.id    users.name    users.age    users.province    users.city

1    yinzhengjie    26    hebei    shijiazhuang

2    Guido van Rossum    62    hebei    shijiazhuang

3    Martin Odersky    60    hebei    shijiazhuang

4    Rasmus Lerdorf    50    hebei    shijiazhuang

1    yinzhengjie    26    shanxi    xian

2    Guido van Rossum    62    shanxi    xian

3    Martin Odersky    60    shanxi    xian

4    Rasmus Lerdorf    50    shanxi    xian

Time taken: 0.101 seconds, Fetched: 8 row(s)

hive (yinzhengjie)> select * from users where province='hebei';                #查询分区表中仅含有province='hebei'的数据

OK

users.id    users.name    users.age    users.province    users.city

1    yinzhengjie    26    hebei    shijiazhuang

2    Guido van Rossum    62    hebei    shijiazhuang

3    Martin Odersky    60    hebei    shijiazhuang

4    Rasmus Lerdorf    50    hebei    shijiazhuang

Time taken: 1.775 seconds, Fetched: 4 row(s)

hive (yinzhengjie)> 

分区表-加载数据到二级分区表中（hive (yinzhengjie)> load data local inpath '/home/yinzhengjie/download/users.txt' into table users partition(province='hebei',city='shijiazhuang');）

hive (yinzhengjie)> dfs -mkdir -p /user/hive/warehouse/yinzhengjie.db/users/province=hebei/city=handan;            #在hdfs上创建目录

hive (yinzhengjie)>

hive (yinzhengjie)> dfs -put /home/yinzhengjie/download/users.txt /user/hive/warehouse/yinzhengjie.db/users/province=hebei/city=handan;        #将本地文件的数据上传到hdfs上

hive (yinzhengjie)>

hive (yinzhengjie)> select * from users where province='hebei' and city='handan';                #很显然，查看数据是没有的

OK

users.id    users.name    users.age    users.province    users.city

Time taken: 0.304 seconds

hive (yinzhengjie)>

hive (yinzhengjie)> msck repair table users;                                                    #手动执行修复命令

OK

Partitions not in metastore:    users:province=hebei/city=handan

Repair: Added partition to metastore users:province=hebei/city=handan

Time taken: 0.487 seconds, Fetched: 2 row(s)

hive (yinzhengjie)> select * from users where province='hebei' and city='handan';                #再次查看数据，发现已经是有数据的

OK

users.id    users.name    users.age    users.province    users.city

1    yinzhengjie    26    hebei    handan

2    Guido van Rossum    62    hebei    handan

3    Martin Odersky    60    hebei    handan

4    Rasmus Lerdorf    50    hebei    handan

Time taken: 0.156 seconds, Fetched: 4 row(s)

hive (yinzhengjie)> 

分区表-把数据直接上传到分区目录上，让分区表和数据产生关联的方式一：上传数据后修复(hive (yinzhengjie)> msck repair table users;)

hive (yinzhengjie)> dfs -mkdir -p /user/hive/warehouse/yinzhengjie.db/users/province=shanxi/city=ankang;

hive (yinzhengjie)> dfs -put /home/yinzhengjie/download/users.txt /user/hive/warehouse/yinzhengjie.db/users/province=shanxi/city=ankang;

hive (yinzhengjie)> select * from users where province='shanxi' and city='ankang';            #查询数据，此时数据是没有查到的

OK

users.id    users.name    users.age    users.province    users.city

Time taken: 0.112 seconds

hive (yinzhengjie)>

hive (yinzhengjie)> ALTER TABLE users add partition(province='shanxi',city='ankang');       #上传数据后添加分区

OK

Time taken: 0.14 seconds

hive (yinzhengjie)> select * from users where province='shanxi' and city='ankang';            #再次查询数据，你会发现数据又有了

OK

users.id    users.name    users.age    users.province    users.city

1    yinzhengjie    26    shanxi    ankang

2    Guido van Rossum    62    shanxi    ankang

3    Martin Odersky    60    shanxi    ankang

4    Rasmus Lerdorf    50    shanxi    ankang

Time taken: 0.156 seconds, Fetched: 4 row(s)

hive (yinzhengjie)> 

分区表-把数据直接上传到分区目录上，让分区表和数据产生关联的方式二：上传数据后添加分区（hive (yinzhengjie)> ALTER TABLE users add partition(province='shanxi',city='ankang'); ）

hive (yinzhengjie)> dfs -mkdir -p /user/hive/warehouse/yinzhengjie.db/users/province=shanxi/city=hanzhong;                #在hdfs上创建目录

hive (yinzhengjie)> select * from users where province='shanxi' and city='hanzhong';                                    #很显然，查看数据是没有的

OK

users.id    users.name    users.age    users.province    users.city

Time taken: 0.148 seconds

hive (yinzhengjie)> load data local inpath '/home/yinzhengjie/download/users.txt' into table users partition(province='shanxi',city='hanzhong');        #上传数据后load数据到分区

Loading data to table yinzhengjie.users partition (province=shanxi, city=hanzhong)

OK

Time taken: 0.593 seconds

hive (yinzhengjie)> select * from users where province='shanxi' and city='hanzhong';                                    #再次查看数据，发现已经是有数据的

OK

users.id    users.name    users.age    users.province    users.city

1    yinzhengjie    26    shanxi    hanzhong

2    Guido van Rossum    62    shanxi    hanzhong

3    Martin Odersky    60    shanxi    hanzhong

4    Rasmus Lerdorf    50    shanxi    hanzhong

Time taken: 0.104 seconds, Fetched: 4 row(s)

hive (yinzhengjie)> 

分区表-把数据直接上传到分区目录上，让分区表和数据产生关联的方式三：上传数据后load数据到分区（hive (yinzhengjie)> load data local inpath '/home/yinzhengjie/download/users.txt' into table users partition(province='shanxi',city='hanzhong');）

hive (yinzhengjie)> show tables;                                    #查看当前数据库已经存在的表

OK

tab_name

dept_partition

raw_logs

student

teacher

teacherbak

teachercopy

users

Time taken: 0.071 seconds, Fetched: 7 row(s)

hive (yinzhengjie)> ALTER TABLE users RENAME TO myusers;            #重命名表，将users表名改为myusers

OK

Time taken: 0.341 seconds

hive (yinzhengjie)> show tables;                                    #再次查看当前数据库已经存在的表，发现表名称已经修改了

OK

tab_name

dept_partition

myusers

raw_logs

student

teacher

teacherbak

teachercopy

Time taken: 0.011 seconds, Fetched: 7 row(s)

hive (yinzhengjie)> 

修改表-重名名表实操案例（hive (yinzhengjie)> ALTER TABLE users RENAME TO myusers;）

hive (yinzhengjie)> desc dept_partition;                                        #查看表结构

OK

col_name    data_type    comment

deptno                  int

dname                   string

loc                     string

month                   string                                      

# Partition Information

# col_name                data_type               comment             

month                   string

Time taken: 0.054 seconds, Fetched: 9 row(s)

hive (yinzhengjie)> ALTER TABLE dept_partition ADD COLUMNS(desc string);        #添加新字段(列),温馨提示：ADD是代表新增一字段，字段位置在所有列后面(partition列前)，REPLACE则是表示替换表中所有字段。

OK

Time taken: 0.176 seconds

hive (yinzhengjie)> desc dept_partition;                                        #再次查看表结构

OK

col_name    data_type    comment

deptno                  int

dname                   string

loc                     string

desc                    string

month                   string                                      

# Partition Information

# col_name                data_type               comment             

month                   string

Time taken: 0.059 seconds, Fetched: 10 row(s)

hive (yinzhengjie)> 

修改表-添加列实操案例(hive (yinzhengjie)> ALTER TABLE dept_partition ADD COLUMNS(desc string);)

hive (yinzhengjie)> desc dept_partition;                                                #查看表结构

OK

col_name    data_type    comment

deptno                  int

dname                   string

loc                     string

month                   string                                      

# Partition Information

# col_name                data_type               comment             

month                   string

Time taken: 0.054 seconds, Fetched: 9 row(s)

hive (yinzhengjie)>

hive (yinzhengjie)> alter table dept_partition change column desc deptdesc string;        #修改列名实操案例

OK

Time taken: 0.153 seconds

hive (yinzhengjie)> desc dept_partition;

OK

col_name    data_type    comment

deptno                  int

dname                   string

loc                     string

deptdesc                string

month                   string                                      

# Partition Information

# col_name                data_type               comment             

month                   string

Time taken: 0.027 seconds, Fetched: 10 row(s)

hive (yinzhengjie)> 

修改表-修改列名实操案例(hive (yinzhengjie)> alter table dept_partition change column desc deptdesc string;)

hive (yinzhengjie)> desc dept_partition;

OK

col_name    data_type    comment

deptno                  int

dname                   string

loc                     string

deptdesc                string

month                   string                                      

# Partition Information

# col_name                data_type               comment             

month                   string

Time taken: 0.031 seconds, Fetched: 10 row(s)

hive (yinzhengjie)> alter table dept_partition replace columns(deptno string, dname string, loc string);         #替换列名，温馨提示：ADD是代表新增一字段，字段位置在所有列后面(partition列前)，REPLACE则是表示替换表中所有字段。

OK

Time taken: 0.152 seconds

hive (yinzhengjie)> desc dept_partition;

OK

col_name    data_type    comment

deptno                  string

dname                   string

loc                     string

month                   string                                      

# Partition Information

# col_name                data_type               comment             

month                   string

Time taken: 0.027 seconds, Fetched: 9 row(s)

hive (yinzhengjie)> 

修改表-替换列名实操案例(hive (yinzhengjie)> alter table dept_partition replace columns(deptno string, dname string, loc string);)

hive (yinzhengjie)> show tables;

OK

tab_name

dept_partition

myusers

raw_logs

student

teacher

teacherbak

teachercopy

Time taken: 0.015 seconds, Fetched: 7 row(s)

hive (yinzhengjie)> DROP TABLE dept_partition;         #删除指定的表

OK

Time taken: 0.214 seconds

hive (yinzhengjie)> show tables;

OK

tab_name

myusers

raw_logs

student

teacher

teacherbak

teachercopy

Time taken: 0.015 seconds, Fetched: 6 row(s)

hive (yinzhengjie)> 

修改表-删除指定的表（hive (yinzhengjie)> DROP TABLE dept_partition; ）

数据导入-向表中装载数据（Load）语法

    hive>load data [local] inpath '/home/yinzhengjie/download/user.txt' [overwrite] into table student [partition (partcol1=val1,…)];

以上参数说明：

        >.load data:表示加载数据

        >.local:表示从本地加载数据到hive表；否则从HDFS加载数据到hive表

        >.inpath:表示加载数据的路径

        >.overwrite:表示覆盖表中已有数据，否则表示追加

        >.into table:表示加载到哪张表

        >.student:表示具体的表

        >.partition:表示上传到指定分区

[yinzhengjie@s101 download]$ cat /home/yinzhengjie/download/students.txt

    sunwukong

    zhubajie

    shaheshang

    bailongma

    tangsanzang

[yinzhengjie@s101 download]$ 

登录hive创建表并将数据导入进去：

hive (yinzhengjie)> create table xiyouji(

                  >     id string,

                  >     name string

                  > )

                  > row format delimited fields terminated by '\t';

OK

Time taken: 0.635 seconds

hive (yinzhengjie)>

hive (yinzhengjie)> load data local inpath '/home/yinzhengjie/download/students.txt' into table yinzhengjie.xiyouji;

Loading data to table yinzhengjie.xiyouji

OK

Time taken: 10.337 seconds

hive (yinzhengjie)>

hive (yinzhengjie)> select * from xiyouji;

OK

xiyouji.id    xiyouji.name

    sunwukong

    zhubajie

    shaheshang

    bailongma

    tangsanzang

Time taken: 0.131 seconds, Fetched:  row(s)

hive (yinzhengjie)> 

hive (yinzhengjie)> select * from xiyouji;

OK

xiyouji.id    xiyouji.name

    sunwukong

    zhubajie

    shaheshang

    bailongma

    tangsanzang

Time taken: 0.207 seconds, Fetched:  row(s)

hive (yinzhengjie)> truncate table xiyouji;                        #温馨提示：Truncate只能删除管理表，不能删除外部表中数据

OK

Time taken: 0.169 seconds

hive (yinzhengjie)> select * from xiyouji;

OK

xiyouji.id    xiyouji.name

Time taken: 0.086 seconds

hive (yinzhengjie)> 

hive (yinzhengjie)> select * from xiyouji;                                                                        #查看表中数据是空的

OK

xiyouji.id    xiyouji.name

Time taken: 0.077 seconds

hive (yinzhengjie)> dfs -put /home/yinzhengjie/download/students.txt /home/yinzhengjie/data;                    #上传文件到HDFS

hive (yinzhengjie)> dfs -cat /home/yinzhengjie/data/students.txt;

    sunwukong

    zhubajie

    shaheshang

    bailongma

    tangsanzang

hive (yinzhengjie)> load data inpath '/home/yinzhengjie/data/students.txt' into table yinzhengjie.xiyouji;        #加载HDFS上数据，注意数据会被剪切走哟

Loading data to table yinzhengjie.xiyouji

OK

Time taken: 0.228 seconds

hive (yinzhengjie)> select * from xiyouji;                                                                        #再次查看表中数据

OK

xiyouji.id    xiyouji.name

    sunwukong

    zhubajie

    shaheshang

    bailongma

    tangsanzang

Time taken: 0.073 seconds, Fetched:  row(s)

hive (yinzhengjie)> 

hive (yinzhengjie)> select * from xiyouji;                                                                                    #查看上传之前表中数据

OK

xiyouji.id    xiyouji.name

    sunwukong

    zhubajie

    shaheshang

    bailongma

    tangsanzang

    sunwukong

    zhubajie

    shaheshang

    bailongma

    tangsanzang

    sunwukong

    zhubajie

    shaheshang

    bailongma

    tangsanzang

Time taken: 0.077 seconds, Fetched:  row(s)

hive (yinzhengjie)> dfs -put /home/yinzhengjie/download/students.txt /home/yinzhengjie/data;                                #上传文件到HDFS

hive (yinzhengjie)> dfs -cat /home/yinzhengjie/data/students.txt;                                                            #查看上传到HDFS的文件内容

    sunwukong

    zhubajie

    shaheshang

    bailongma

    tangsanzang

hive (yinzhengjie)> load data inpath '/home/yinzhengjie/data/students.txt' overwrite into table yinzhengjie.xiyouji;        #加载HDFS上数据覆盖表中已有的数据，注意数据会被剪切走哟

Loading data to table yinzhengjie.xiyouji

OK

Time taken: 0.346 seconds

hive (yinzhengjie)> select * from xiyouji;                                                                                    #再次查看表中数据。发现之前的数据已经被覆盖了

OK

xiyouji.id    xiyouji.name

    sunwukong

    zhubajie

    shaheshang

    bailongma

    tangsanzang

Time taken: 0.086 seconds, Fetched:  row(s)

hive (yinzhengjie)>
    

hive (yinzhengjie)> drop table xiyouji;                                                #删除之前的测试表

OK

Time taken: 1.645 seconds

hive (yinzhengjie)>

hive (yinzhengjie)> create table xiyouji(

                  >     id int,

                  >     name string

                  > )

                  > partitioned by (position string)

                  > row format delimited fields terminated by '\t';                                                #创建一张分区表

OK

Time taken: 0.137 seconds

hive (yinzhengjie)>

hive (yinzhengjie)> insert into table  xiyouji partition(position='wuzhishan') values(,'孙悟空');                #基本插入数据

WARNING: Hive-on-MR is deprecated in Hive  and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive .X releases.

Query ID = yinzhengjie_20180809181325_1275bf7f--4d56-afaf-ecd310467701

Total jobs =

Launching Job  out of

Number of reduce tasks is set to  since there's no reduce operator

Starting Job = job_1533789743141_0004, Tracking URL = http://s101:8088/proxy/application_1533789743141_0004/

Kill Command = /soft/hadoop-2.7./bin/hadoop job  -kill job_1533789743141_0004

Hadoop job information for Stage-: number of mappers: ; number of reducers:

-- ::, Stage- map = %,  reduce = %

-- ::, Stage- map = %,  reduce = %

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 2.12 sec

MapReduce Total cumulative CPU time:  seconds  msec

Ended Job = job_1533789743141_0004

Stage- is selected by condition resolver.

Stage- is filtered out by condition resolver.

Stage- is filtered out by condition resolver.

Moving data to directory hdfs://mycluster/user/hive/warehouse/yinzhengjie.db/xiyouji/position=wuzhishan/.hive-staging_hive_2018-08-09_18-13-25_269_2859222729747025112-1/-ext-10000

Loading data to table yinzhengjie.xiyouji partition (position=wuzhishan)

MapReduce Jobs Launched:

Stage-Stage-: Map:    Cumulative CPU: 2.62 sec   HDFS Read:  HDFS Write:  SUCCESS

Total MapReduce CPU Time Spent:  seconds  msec

OK

_col0    _col1

Time taken: 136.695 seconds

hive (yinzhengjie)> select * from xiyouji;

OK

xiyouji.id    xiyouji.name    xiyouji.position

    孙悟空    wuzhishan

Time taken: 0.169 seconds, Fetched:  row(s)

hive (yinzhengjie)> 

hive (yinzhengjie)> select * from xiyouji;                                        #查看表中的数据

OK

xiyouji.id    xiyouji.name    xiyouji.position

    孙悟空    wuzhishan

Time taken: 0.117 seconds, Fetched:  row(s)

hive (yinzhengjie)> insert overwrite table xiyouji partition(position='sandabaigujing') select id, name from xiyouji where position='wuzhishan';        #根据单张表查询结果向表中插入数据

WARNING: Hive-on-MR is deprecated in Hive  and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive .X releases.

Query ID = yinzhengjie_20180809182335_4f9c3b89-bc30-4afb-95f7-bd294520afe9

Total jobs =

Launching Job  out of

Number of reduce tasks is set to  since there's no reduce operator

Starting Job = job_1533789743141_0005, Tracking URL = http://s101:8088/proxy/application_1533789743141_0005/

Kill Command = /soft/hadoop-2.7./bin/hadoop job  -kill job_1533789743141_0005

Hadoop job information for Stage-: number of mappers: ; number of reducers:

-- ::, Stage- map = %,  reduce = %

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 2.61 sec

MapReduce Total cumulative CPU time:  seconds  msec

Ended Job = job_1533789743141_0005

Stage- is selected by condition resolver.

Stage- is filtered out by condition resolver.

Stage- is filtered out by condition resolver.

Moving data to directory hdfs://mycluster/user/hive/warehouse/yinzhengjie.db/xiyouji/position=sandabaigujing/.hive-staging_hive_2018-08-09_18-23-35_915_1607485649232911242-1/-ext-10000

Loading data to table yinzhengjie.xiyouji partition (position=sandabaigujing)

MapReduce Jobs Launched:

Stage-Stage-: Map:    Cumulative CPU: 2.61 sec   HDFS Read:  HDFS Write:  SUCCESS

Total MapReduce CPU Time Spent:  seconds  msec

OK

id    name

Time taken: 50.478 seconds

hive (yinzhengjie)> select * from xiyouji;                                        #再次查看表中的数据，你会发现多了一条数据，只不过position的值发生了变化

OK

xiyouji.id    xiyouji.name    xiyouji.position

    孙悟空    sandabaigujing

    孙悟空    wuzhishan

Time taken: 0.105 seconds, Fetched:  row(s)

hive (yinzhengjie)> 

hive (yinzhengjie)> select * from xiyouji;                                                            #查看数据表当前的数据

OK

xiyouji.id    xiyouji.name    xiyouji.position

    孙悟空    sandabaigujing

    孙悟空    wuzhishan

Time taken: 0.14 seconds, Fetched:  row(s)

hive (yinzhengjie)> from xiyouji

                  > insert overwrite table xiyouji partition(position='nverguo')

                  > select id, name where position='wuzhishan'

                  > insert overwrite table xiyouji partition(position='zhenjiameihouwang')

                  > select id, name where position='wuzhishan';                                        #根据多张表查询结果多插入模式，我测试时只插入了2条数据

WARNING: Hive-on-MR is deprecated in Hive  and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive .X releases.

Query ID = yinzhengjie_20180809183740_ef71ba4e-acec-4ef7--0f01c57bd49d

Total jobs =

Launching Job  out of

Number of reduce tasks is set to  since there's no reduce operator

Starting Job = job_1533789743141_0009, Tracking URL = http://s101:8088/proxy/application_1533789743141_0009/

Kill Command = /soft/hadoop-2.7./bin/hadoop job  -kill job_1533789743141_0009

Hadoop job information for Stage-: number of mappers: ; number of reducers:

-- ::, Stage- map = %,  reduce = %

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 2.08 sec

MapReduce Total cumulative CPU time:  seconds  msec

Ended Job = job_1533789743141_0009

Stage- is selected by condition resolver.

Stage- is filtered out by condition resolver.

Stage- is filtered out by condition resolver.

Stage- is selected by condition resolver.

Stage- is filtered out by condition resolver.

Stage- is filtered out by condition resolver.

Moving data to directory hdfs://mycluster/user/hive/warehouse/yinzhengjie.db/xiyouji/position=nverguo/.hive-staging_hive_2018-08-09_18-37-40_573_1576742180177937358-1/-ext-10000

Moving data to directory hdfs://mycluster/user/hive/warehouse/yinzhengjie.db/xiyouji/position=zhenjiameihouwang/.hive-staging_hive_2018-08-09_18-37-40_573_1576742180177937358-1/-ext-10002

Loading data to table yinzhengjie.xiyouji partition (position=nverguo)

Loading data to table yinzhengjie.xiyouji partition (position=zhenjiameihouwang)

MapReduce Jobs Launched:

Stage-Stage-: Map:    Cumulative CPU: 2.08 sec   HDFS Read:  HDFS Write:  SUCCESS

Total MapReduce CPU Time Spent:  seconds  msec

OK

id    name

Time taken: 63.367 seconds

hive (yinzhengjie)> select * from xiyouji;                                                            #再次查看数据表当前的数据，你会发现又多了2条数据

OK

xiyouji.id    xiyouji.name    xiyouji.position

    孙悟空    nverguo

    孙悟空    sandabaigujing

    孙悟空    wuzhishan

    孙悟空    zhenjiameihouwang

Time taken: 0.141 seconds, Fetched:  row(s)

hive (yinzhengjie)> 

hive (yinzhengjie)> select * from xiyouji;                                                #查看表中的数据

OK

xiyouji.id    xiyouji.name    xiyouji.position

    孙悟空    nverguo

    孙悟空    sandabaigujing

    孙悟空    wuzhishan

    孙悟空    zhenjiameihouwang

Time taken: 0.087 seconds, Fetched:  row(s)

hive (yinzhengjie)> create table if not exists xiyouji2  as select id, name from xiyouji;        #根据查询结果创建表（查询的结果会添加到新创建的表中）

WARNING: Hive-on-MR is deprecated in Hive  and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive .X releases.

Query ID = yinzhengjie_20180809184435_d18b1d0b--4fbe-bffa-ec501fa5fd09

Total jobs =

Launching Job  out of

Number of reduce tasks is set to  since there's no reduce operator

Starting Job = job_1533789743141_0010, Tracking URL = http://s101:8088/proxy/application_1533789743141_0010/

Kill Command = /soft/hadoop-2.7./bin/hadoop job  -kill job_1533789743141_0010

Hadoop job information for Stage-: number of mappers: ; number of reducers:

-- ::, Stage- map = %,  reduce = %

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 2.39 sec

MapReduce Total cumulative CPU time:  seconds  msec

Ended Job = job_1533789743141_0010

Stage- is selected by condition resolver.

Stage- is filtered out by condition resolver.

Stage- is filtered out by condition resolver.

Moving data to directory hdfs://mycluster/user/hive/warehouse/yinzhengjie.db/.hive-staging_hive_2018-08-09_18-44-35_127_6564594081639052485-1/-ext-10002

Moving data to directory hdfs://mycluster/user/hive/warehouse/yinzhengjie.db/xiyouji2

MapReduce Jobs Launched:

Stage-Stage-: Map:    Cumulative CPU: 2.39 sec   HDFS Read:  HDFS Write:  SUCCESS

Total MapReduce CPU Time Spent:  seconds  msec

OK

id    name

Time taken: 54.907 seconds

hive (yinzhengjie)> select * from xiyouji2;                                                #查看新生成表的数据

OK

xiyouji2.id    xiyouji2.name

    孙悟空

    孙悟空

    孙悟空

    孙悟空

Time taken: 0.065 seconds, Fetched:  row(s)

hive (yinzhengjie)> 

hive (yinzhengjie)> create table if not exists Student(

                  >     id int,

                  >     name string

                  > )

                  > row format delimited fields terminated by '\t'

                  > location '/home/yinzhengjie/data/students.txt';                                                    #创建表，并指定在hdfs上的加载数据路径

OK

Time taken: 0.017 seconds

hive (yinzhengjie)>

hive (yinzhengjie)> dfs -put /home/yinzhengjie/download/students.txt  /home/yinzhengjie/data/students.txt;            #上传数据到hdfs上

hive (yinzhengjie)>  dfs -cat /home/yinzhengjie/data/students.txt;                                                    #查看上传到hdfs上的数据，这个数据会被Student表自动加载。

    sunwukong

    zhubajie

    shaheshang

    bailongma

    tangsanzang

hive (yinzhengjie)>

hive (yinzhengjie)> select * from Student;                                                                            #我们会发现Student表会自动加载数据，神奇不？

OK

student.id    student.name

    sunwukong

    zhubajie

    shaheshang

    bailongma

    tangsanzang

Time taken: 0.054 seconds, Fetched:  row(s)

hive (yinzhengjie)>

hive (yinzhengjie)> import table xiyoujihouzhuan partition(position='zhenjiameihouwang') from '/home/yinzhengjie/data/xiyouji2';            #从hdfs中导入指定的分区到指定的表中

Copying data from hdfs://mycluster/home/yinzhengjie/data/xiyouji2/position=zhenjiameihouwang

Copying file: hdfs://mycluster/home/yinzhengjie/data/xiyouji2/position=zhenjiameihouwang/000000_0

Loading data to table yinzhengjie.xiyoujihouzhuan partition (position=zhenjiameihouwang)

OK

Time taken: 3.966 seconds

hive (yinzhengjie)> select * from xiyoujihouzhuan;                    #查看是否导入成功

OK

xiyoujihouzhuan.id    xiyoujihouzhuan.name    xiyoujihouzhuan.position

    孙悟空    zhenjiameihouwang

Time taken: 0.293 seconds, Fetched:  row(s)

hive (yinzhengjie)> import table xiyoujihouzhuan partition(position='nverguo') from '/home/yinzhengjie/data/xiyouji2';

Copying data from hdfs://mycluster/home/yinzhengjie/data/xiyouji2/position=nverguo

Copying file: hdfs://mycluster/home/yinzhengjie/data/xiyouji2/position=nverguo/000000_0

Loading data to table yinzhengjie.xiyoujihouzhuan partition (position=nverguo)

OK

Time taken: 0.751 seconds

hive (yinzhengjie)> import table xiyoujihouzhuan partition(position='wuzhishan') from '/home/yinzhengjie/data/xiyouji2';

Copying data from hdfs://mycluster/home/yinzhengjie/data/xiyouji2/position=wuzhishan

Copying file: hdfs://mycluster/home/yinzhengjie/data/xiyouji2/position=wuzhishan/000000_0

Loading data to table yinzhengjie.xiyoujihouzhuan partition (position=wuzhishan)

OK

Time taken: 1.363 seconds

hive (yinzhengjie)> select * from xiyoujihouzhuan;

OK

xiyoujihouzhuan.id    xiyoujihouzhuan.name    xiyoujihouzhuan.position

    孙悟空    nverguo

    孙悟空    wuzhishan

    孙悟空    zhenjiameihouwang

Time taken: 0.488 seconds, Fetched:  row(s)

hive (yinzhengjie)>

hive (yinzhengjie)> insert overwrite local directory '/home/yinzhengjie/download/xiyouji' select * from xiyouji;                      #将查询的结果导出到本地路径，注意这里导出的是一个目录哟

WARNING: Hive-on-MR is deprecated in Hive  and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive .X releases.

Query ID = yinzhengjie_20180809190854_cc079ee4-1d8b-43a0-b360-89ff65fb39fb

Total jobs =

Launching Job  out of

Number of reduce tasks is set to  since there's no reduce operator

Starting Job = job_1533789743141_0011, Tracking URL = http://s101:8088/proxy/application_1533789743141_0011/

Kill Command = /soft/hadoop-2.7./bin/hadoop job  -kill job_1533789743141_0011

Hadoop job information for Stage-: number of mappers: ; number of reducers:

-- ::, Stage- map = %,  reduce = %

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 1.96 sec

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 2.36 sec

MapReduce Total cumulative CPU time:  seconds  msec

Ended Job = job_1533789743141_0011

Moving data to local directory /home/yinzhengjie/download/xiyouji

MapReduce Jobs Launched:

Stage-Stage-: Map:    Cumulative CPU: 2.36 sec   HDFS Read:  HDFS Write:  SUCCESS

Total MapReduce CPU Time Spent:  seconds  msec

OK

xiyouji.id    xiyouji.name    xiyouji.position

Time taken: 77.687 seconds

hive (yinzhengjie)> ! cat /home/yinzhengjie/download/xiyouji/000000_0;                                                                    #查看导出到本地的文本信息

1孙悟空nverguo

1孙悟空sandabaigujing

1孙悟空wuzhishan

1孙悟空zhenjiameihouwang

hive (yinzhengjie)> 

hive (yinzhengjie)> insert overwrite local directory '/home/yinzhengjie/download/xiyouji2'

                  > ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'

                  > select * from xiyouji;                                                                #我们指定以"\t"进行风格字段

WARNING: Hive-on-MR is deprecated in Hive  and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive .X releases.

Query ID = yinzhengjie_20180809191439_7461de80--4e07-82ac-fd54b85a0891

Total jobs =

Launching Job  out of

Number of reduce tasks is set to  since there's no reduce operator

Starting Job = job_1533789743141_0012, Tracking URL = http://s101:8088/proxy/application_1533789743141_0012/

Kill Command = /soft/hadoop-2.7./bin/hadoop job  -kill job_1533789743141_0012

Hadoop job information for Stage-: number of mappers: ; number of reducers:

-- ::, Stage- map = %,  reduce = %

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 2.31 sec

MapReduce Total cumulative CPU time:  seconds  msec

Ended Job = job_1533789743141_0012

Moving data to local directory /home/yinzhengjie/download/xiyouji2

MapReduce Jobs Launched:

Stage-Stage-: Map:    Cumulative CPU: 2.31 sec   HDFS Read:  HDFS Write:  SUCCESS

Total MapReduce CPU Time Spent:  seconds  msec

OK

xiyouji.id    xiyouji.name    xiyouji.position

Time taken: 100.57 seconds

hive (yinzhengjie)> ! cat /home/yinzhengjie/download/xiyouji2/000000_0;                                    #查看导出的数据内容

    孙悟空    nverguo

    孙悟空    sandabaigujing

    孙悟空    wuzhishan

    孙悟空    zhenjiameihouwang

hive (yinzhengjie)> 

hive (yinzhengjie)> insert overwrite directory '/home/yinzhengjie/data/xiyouji'

                  > ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'

                  > select * from xiyouji;                                                                #将查询的结果导出到HDFS上

WARNING: Hive-on-MR is deprecated in Hive  and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive .X releases.

Query ID = yinzhengjie_20180809192105_183285e8-bf4e--93c5-4312a8a31716

Total jobs =

Launching Job  out of

Number of reduce tasks is set to  since there's no reduce operator

Starting Job = job_1533789743141_0013, Tracking URL = http://s101:8088/proxy/application_1533789743141_0013/

Kill Command = /soft/hadoop-2.7./bin/hadoop job  -kill job_1533789743141_0013

Hadoop job information for Stage-: number of mappers: ; number of reducers:

-- ::, Stage- map = %,  reduce = %

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 2.38 sec

MapReduce Total cumulative CPU time:  seconds  msec

Ended Job = job_1533789743141_0013

Stage- is selected by condition resolver.

Stage- is filtered out by condition resolver.

Stage- is filtered out by condition resolver.

Moving data to directory hdfs://mycluster/home/yinzhengjie/data/xiyouji/.hive-staging_hive_2018-08-09_19-21-05_012_3955068750863516339-1/-ext-10000

Moving data to directory /home/yinzhengjie/data/xiyouji

MapReduce Jobs Launched:

Stage-Stage-: Map:    Cumulative CPU: 2.38 sec   HDFS Read:  HDFS Write:  SUCCESS

Total MapReduce CPU Time Spent:  seconds  msec

OK

xiyouji.id    xiyouji.name    xiyouji.position

Time taken: 88.306 seconds

hive (yinzhengjie)> dfs -cat /home/yinzhengjie/data/xiyouji/000000_0;                                    #查询导出在hdfs上的数据

    孙悟空    nverguo

    孙悟空    sandabaigujing

    孙悟空    wuzhishan

    孙悟空    zhenjiameihouwang

hive (yinzhengjie)> 

hive (yinzhengjie)> dfs -get  /home/yinzhengjie/data/xiyouji/000000_0  /home/yinzhengjie/download/xiyouji3;                #通过Hadoop命令将数据导出到本地

hive (yinzhengjie)> ! cat /home/yinzhengjie/download/xiyouji3;                                                            #查看导出到Linux的文本信息

    孙悟空    nverguo

    孙悟空    sandabaigujing

    孙悟空    wuzhishan

    孙悟空    zhenjiameihouwang

hive (yinzhengjie)> 

hive (yinzhengjie)>

hive (yinzhengjie)> export table yinzhengjie.xiyouji to '/home/yinzhengjie/data/xiyouji2';                            #通过Export将数据导出到HDFS上

Copying data from file:/home/yinzhengjie/yinzhengjie/46c2c137-93f5-4f30--6b0d3d62c227/hive_2018--09_19--58_906_1594217512913959561-/-local-/_metadata

Copying file: file:/home/yinzhengjie/yinzhengjie/46c2c137-93f5-4f30--6b0d3d62c227/hive_2018--09_19--58_906_1594217512913959561-/-local-/_metadata

Copying data from hdfs://mycluster/user/hive/warehouse/yinzhengjie.db/xiyouji/position=???

Copying file: hdfs://mycluster/user/hive/warehouse/yinzhengjie.db/xiyouji/position=五指山/000000_0

Copying data from hdfs://mycluster/user/hive/warehouse/yinzhengjie.db/xiyouji/position=nverguo

Copying file: hdfs://mycluster/user/hive/warehouse/yinzhengjie.db/xiyouji/position=nverguo/000000_0

Copying data from hdfs://mycluster/user/hive/warehouse/yinzhengjie.db/xiyouji/position=sandabaigujing

Copying file: hdfs://mycluster/user/hive/warehouse/yinzhengjie.db/xiyouji/position=sandabaigujing/000000_0

Copying data from hdfs://mycluster/user/hive/warehouse/yinzhengjie.db/xiyouji/position=wuzhishan

Copying file: hdfs://mycluster/user/hive/warehouse/yinzhengjie.db/xiyouji/position=wuzhishan/000000_0

Copying data from hdfs://mycluster/user/hive/warehouse/yinzhengjie.db/xiyouji/position=zhenjiameihouwang

Copying file: hdfs://mycluster/user/hive/warehouse/yinzhengjie.db/xiyouji/position=zhenjiameihouwang/000000_0

OK

Time taken: 0.978 seconds

hive (yinzhengjie)> dfs -cat /home/yinzhengjie/data/xiyouji2/position=wuzhishan/000000_0;

    孙悟空

hive (yinzhengjie)> dfs -cat /home/yinzhengjie/data/xiyouji2/position=nverguo/000000_0;

    孙悟空

hive (yinzhengjie)> dfs -cat /home/yinzhengjie/data/xiyouji2/position=sandabaigujing/000000_0;

    孙悟空

hive (yinzhengjie)> dfs -cat /home/yinzhengjie/data/xiyouji2/position=zhenjiameihouwang/000000_0;

    孙悟空

hive (yinzhengjie)> 

[yinzhengjie@s101 ~]$ hive -e 'select * from yinzhengjie.xiyouji;' > /home/yinzhengjie/download/xiyouji6                            #通过命令行访问hive，并将数据重定向到本地的一个文件中。

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in [jar:file:/soft/apache-hive-2.1.-bin/lib/log4j-slf4j-impl-2.4..jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/soft/hbase-1.2./lib/phoenix-4.10.-HBase-1.2-client.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/soft/hadoop-2.7./share/hadoop/common/lib/slf4j-log4j12-1.7..jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.

Logging initialized using configuration in file:/soft/apache-hive-2.1.-bin/conf/hive-log4j2.properties Async: true

OK

Time taken: 20.367 seconds, Fetched:  row(s)

[yinzhengjie@s101 ~]$

[yinzhengjie@s101 ~]$ cat /home/yinzhengjie/download/xiyouji6                #查看查询的结果

xiyouji.id    xiyouji.name    xiyouji.position

    孙悟空    nverguo

    孙悟空    sandabaigujing

    孙悟空    wuzhishan

    孙悟空    zhenjiameihouwang

[yinzhengjie@s101 ~]$ 

hive (yinzhengjie)> select * from teacher;                #全表查询

OK

teacher.id    teacher.name

    Dennis MacAlistair Ritchie

    Linus Benedict Torvalds

    Bjarne Stroustrup

    Guido van Rossum

    James Gosling

    Martin Odersky

    Rob Pike

    Rasmus Lerdorf

    Brendan Eich

Time taken: 0.108 seconds, Fetched:  row(s)

hive (yinzhengjie)>

hive (yinzhengjie)> select name from teacher;            #选择特定列查询

OK

name

Dennis MacAlistair Ritchie

Linus Benedict Torvalds

Bjarne Stroustrup

Guido van Rossum

James Gosling

Martin Odersky

Rob Pike

Rasmus Lerdorf

Brendan Eich

Time taken: 0.1 seconds, Fetched:  row(s)

hive (yinzhengjie)> 

温馨提示:

    >.SQL 语言大小写不敏感。

    >.SQL 可以写在一行或者多行

    >.关键字不能被缩写也不能分行

    >.各子句一般要分行写。

    >.使用缩进提高语句的可读性。

hive (yinzhengjie)> select id AS tid, name AS Tname from teacher;

OK

tid    tname

    Dennis MacAlistair Ritchie

    Linus Benedict Torvalds

    Bjarne Stroustrup

    Guido van Rossum

    James Gosling

    Martin Odersky

    Rob Pike

    Rasmus Lerdorf

    Brendan Eich

Time taken: 0.088 seconds, Fetched:  row(s)

hive (yinzhengjie)> 

温馨提示：

    >.重命名一个列。

    >.便于计算。

    >.紧跟列名，也可以在列名和别名之间加入关键字‘AS’ 

hive (yinzhengjie)> select id AS age, name AS Tname from teacher;

OK

age    tname

    Dennis MacAlistair Ritchie

    Linus Benedict Torvalds

    Bjarne Stroustrup

    Guido van Rossum

    James Gosling

    Martin Odersky

    Rob Pike

    Rasmus Lerdorf

    Brendan Eich

Time taken: 0.157 seconds, Fetched:  row(s)

hive (yinzhengjie)> select id+ AS age, name AS Tname from teacher;

OK

age    tname

    Dennis MacAlistair Ritchie

    Linus Benedict Torvalds

    Bjarne Stroustrup

    Guido van Rossum

    James Gosling

    Martin Odersky

    Rob Pike

    Rasmus Lerdorf

    Brendan Eich

Time taken: 0.091 seconds, Fetched:  row(s)

hive (yinzhengjie)> 

算术运算符                    描述

    A+B                      A和B 相加

    A-B                      A减去B

    A*B                      A和B 相乘

    A/B                      A除以B

    A%B                      A对B取余

    A&B                      A和B按位取与

    A|B                      A和B按位取或

    A^B                      A和B按位取异或

    ~A                      A按位取反

hive (yinzhengjie)> select count(*)cnt from teacher;

WARNING: Hive-on-MR is deprecated in Hive  and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive .X releases.

Query ID = yinzhengjie_20180809202019_6a4b05d8--410b-af4e-3c1839e0bdc6

Total jobs =

Launching Job  out of

Number of reduce tasks determined at compile time:

In order to change the average load for a reducer (in bytes):

  set hive.exec.reducers.bytes.per.reducer=<number>

In order to limit the maximum number of reducers:

  set hive.exec.reducers.max=<number>

In order to set a constant number of reducers:

  set mapreduce.job.reduces=<number>

Starting Job = job_1533789743141_0014, Tracking URL = http://s101:8088/proxy/application_1533789743141_0014/

Kill Command = /soft/hadoop-2.7./bin/hadoop job  -kill job_1533789743141_0014

Hadoop job information for Stage-: number of mappers: ; number of reducers:

-- ::, Stage- map = %,  reduce = %

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 2.61 sec

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 5.51 sec

MapReduce Total cumulative CPU time:  seconds  msec

Ended Job = job_1533789743141_0014

MapReduce Jobs Launched:

Stage-Stage-: Map:   Reduce:    Cumulative CPU: 5.51 sec   HDFS Read:  HDFS Write:  SUCCESS

Total MapReduce CPU Time Spent:  seconds  msec

OK

cnt

Time taken: 123.864 seconds, Fetched:  row(s)

hive (yinzhengjie)> 

hive (yinzhengjie)> select max(id) max_age from teacher;

WARNING: Hive-on-MR is deprecated in Hive  and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive .X releases.

Query ID = yinzhengjie_20180809202410_0146f895-4c54-440f-aa1b-bee4fb566b91

Total jobs =

Launching Job  out of

Number of reduce tasks determined at compile time:

In order to change the average load for a reducer (in bytes):

  set hive.exec.reducers.bytes.per.reducer=<number>

In order to limit the maximum number of reducers:

  set hive.exec.reducers.max=<number>

In order to set a constant number of reducers:

  set mapreduce.job.reduces=<number>

Starting Job = job_1533789743141_0015, Tracking URL = http://s101:8088/proxy/application_1533789743141_0015/

Kill Command = /soft/hadoop-2.7./bin/hadoop job  -kill job_1533789743141_0015

Hadoop job information for Stage-: number of mappers: ; number of reducers:

-- ::, Stage- map = %,  reduce = %

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 2.46 sec

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 5.08 sec

MapReduce Total cumulative CPU time:  seconds  msec

Ended Job = job_1533789743141_0015

MapReduce Jobs Launched:

Stage-Stage-: Map:   Reduce:    Cumulative CPU: 5.08 sec   HDFS Read:  HDFS Write:  SUCCESS

Total MapReduce CPU Time Spent:  seconds  msec

OK

max_age

Time taken: 74.014 seconds, Fetched:  row(s)

hive (yinzhengjie)> 

hive (yinzhengjie)> select min(id) min_age from teacher;

WARNING: Hive-on-MR is deprecated in Hive  and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive .X releases.

Query ID = yinzhengjie_20180809202623_b1b99783-b7d3--901e-4e901795a128

Total jobs =

Launching Job  out of

Number of reduce tasks determined at compile time:

In order to change the average load for a reducer (in bytes):

  set hive.exec.reducers.bytes.per.reducer=<number>

In order to limit the maximum number of reducers:

  set hive.exec.reducers.max=<number>

In order to set a constant number of reducers:

  set mapreduce.job.reduces=<number>

Starting Job = job_1533789743141_0016, Tracking URL = http://s101:8088/proxy/application_1533789743141_0016/

Kill Command = /soft/hadoop-2.7./bin/hadoop job  -kill job_1533789743141_0016

Hadoop job information for Stage-: number of mappers: ; number of reducers:

-- ::, Stage- map = %,  reduce = %

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 1.34 sec

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 3.77 sec

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 4.42 sec

MapReduce Total cumulative CPU time:  seconds  msec

Ended Job = job_1533789743141_0016

MapReduce Jobs Launched:

Stage-Stage-: Map:   Reduce:    Cumulative CPU: 4.42 sec   HDFS Read:  HDFS Write:  SUCCESS

Total MapReduce CPU Time Spent:  seconds  msec

OK

min_age

Time taken: 79.135 seconds, Fetched:  row(s)

hive (yinzhengjie)>

hive (yinzhengjie)> select sum(id) sum_age from teacher;

WARNING: Hive-on-MR is deprecated in Hive  and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive .X releases.

Query ID = yinzhengjie_20180809202800_14580ea4-3e65-461e-a1c6-6607e960c3d7

Total jobs =

Launching Job  out of

Number of reduce tasks determined at compile time:

In order to change the average load for a reducer (in bytes):

  set hive.exec.reducers.bytes.per.reducer=<number>

In order to limit the maximum number of reducers:

  set hive.exec.reducers.max=<number>

In order to set a constant number of reducers:

  set mapreduce.job.reduces=<number>

Starting Job = job_1533789743141_0017, Tracking URL = http://s101:8088/proxy/application_1533789743141_0017/

Kill Command = /soft/hadoop-2.7./bin/hadoop job  -kill job_1533789743141_0017

Hadoop job information for Stage-: number of mappers: ; number of reducers:

-- ::, Stage- map = %,  reduce = %

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 2.27 sec

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 4.58 sec

MapReduce Total cumulative CPU time:  seconds  msec

Ended Job = job_1533789743141_0017

MapReduce Jobs Launched:

Stage-Stage-: Map:   Reduce:    Cumulative CPU: 4.58 sec   HDFS Read:  HDFS Write:  SUCCESS

Total MapReduce CPU Time Spent:  seconds  msec

OK

sum_age

Time taken: 43.081 seconds, Fetched:  row(s)

hive (yinzhengjie)>

hive (yinzhengjie)> select avg(id) avg_age from teacher;

WARNING: Hive-on-MR is deprecated in Hive  and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive .X releases.

Query ID = yinzhengjie_20180809202900_618a9c9f-535a-45ac-94de-16723f47d9b9

Total jobs =

Launching Job  out of

Number of reduce tasks determined at compile time:

In order to change the average load for a reducer (in bytes):

  set hive.exec.reducers.bytes.per.reducer=<number>

In order to limit the maximum number of reducers:

  set hive.exec.reducers.max=<number>

In order to set a constant number of reducers:

  set mapreduce.job.reduces=<number>

Starting Job = job_1533789743141_0018, Tracking URL = http://s101:8088/proxy/application_1533789743141_0018/

Kill Command = /soft/hadoop-2.7./bin/hadoop job  -kill job_1533789743141_0018

Hadoop job information for Stage-: number of mappers: ; number of reducers:

-- ::, Stage- map = %,  reduce = %

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 3.19 sec

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 5.25 sec

MapReduce Total cumulative CPU time:  seconds  msec

Ended Job = job_1533789743141_0018

MapReduce Jobs Launched:

Stage-Stage-: Map:   Reduce:    Cumulative CPU: 5.25 sec   HDFS Read:  HDFS Write:  SUCCESS

Total MapReduce CPU Time Spent:  seconds  msec

OK

avg_age

59.333333333333336

Time taken: 59.897 seconds, Fetched:  row(s)

hive (yinzhengjie)> 

hive (yinzhengjie)> select id AS age , name  from teacher;

OK

age    name

    Dennis MacAlistair Ritchie

    Linus Benedict Torvalds

    Bjarne Stroustrup

    Guido van Rossum

    James Gosling

    Martin Odersky

    Rob Pike

    Rasmus Lerdorf

    Brendan Eich

Time taken: 0.068 seconds, Fetched:  row(s)

hive (yinzhengjie)> select id AS age , name  from teacher limit ;             #典型的查询会返回多行数据。LIMIT子句用于限制返回的行数。

OK

age    name

    Dennis MacAlistair Ritchie

    Linus Benedict Torvalds

    Bjarne Stroustrup

Time taken: 0.1 seconds, Fetched:  row(s)

hive (yinzhengjie)> 

hive (yinzhengjie)> select id, name  from teacher where id> ;                #使用WHERE子句，将不满足条件的行过滤掉。WHERE子句紧随FROM子句。

OK

id    name

    Dennis MacAlistair Ritchie

    Bjarne Stroustrup

    Guido van Rossum

    James Gosling

    Rob Pike

Time taken: 0.056 seconds, Fetched:  row(s)

hive (yinzhengjie)> 

hive (yinzhengjie)> select * from teacher where id = ;                    #查询出id等于60的老师

OK

teacher.id    teacher.name

    Martin Odersky

Time taken: 0.075 seconds, Fetched:  row(s)

hive (yinzhengjie)>

hive (yinzhengjie)> select * from teacher where id between  and ;        #查询id在40到60的老师

OK

teacher.id    teacher.name

    Linus Benedict Torvalds

    Martin Odersky

    Rasmus Lerdorf

    Brendan Eich

Time taken: 0.05 seconds, Fetched:  row(s)

hive (yinzhengjie)>

hive (yinzhengjie)> select * from teacher where name is null;                #查询name字段为空的所有老师信息，很显然我没有这样的数据

OK

teacher.id    teacher.name

Time taken: 0.104 seconds

hive (yinzhengjie)>

hive (yinzhengjie)> select * from teacher where id IN(,);                #查询id是50和60的老师信息

OK

teacher.id    teacher.name

    Martin Odersky

    Rasmus Lerdorf

    Brendan Eich

Time taken: 0.07 seconds, Fetched:  row(s)

hive (yinzhengjie)> 

下面表中描述了谓词操作符，这些操作符同样可以用于JOIN…ON和HAVING语句中。

操作符                                支持的数据类型                                描述

A=B                                    基本数据类型            如果A等于B则返回TRUE，反之返回FALSE

A<=>B                                基本数据类型            如果A和B都为NULL，则返回TRUE，其他的和等号（=）操作符的结果一致，如果任一为NULL则结果为NULL

A<>B, A!=B                            基本数据类型            A或者B为NULL则返回NULL；如果A不等于B，则返回TRUE，反之返回FALSE

A<B                                    基本数据类型            A或者B为NULL，则返回NULL；如果A小于B，则返回TRUE，反之返回FALSE

A<=B                                基本数据类型            A或者B为NULL，则返回NULL；如果A小于等于B，则返回TRUE，反之返回FALSE

A>B                                    基本数据类型            A或者B为NULL，则返回NULL；如果A大于B，则返回TRUE，反之返回FALSE

A>=B                                基本数据类型            A或者B为NULL，则返回NULL；如果A大于等于B，则返回TRUE，反之返回FALSE

A [NOT] BETWEEN B AND C                基本数据类型            如果A，B或者C任一为NULL，则结果为NULL。如果A的值大于等于B而且小于或等于C，则结果为TRUE，反之为FALSE。如果使用NOT关键字则可达到相反的效果。

A IS NULL                            所有数据类型            如果A等于NULL，则返回TRUE，反之返回FALSE

A IS NOT NULL                        所有数据类型            如果A不等于NULL，则返回TRUE，反之返回FALSE

IN(数值1, 数值2)                    所有数据类型            使用 IN运算显示列表中的值

A [NOT] LIKE B                        STRING类型                B是一个SQL下的简单正则表达式，如果A与其匹配的话，则返回TRUE；反之返回FALSE。B的表达式说明如下：‘x%’表示A必须以字母‘x’开头，‘%x’表示A必须以字母’x’结尾，而‘%x%’表示A包含有字母’x’,可以位于开头，结尾或者字符串中间。如果使用NOT关键字则可达到相反的效果。

A RLIKE B, A REGEXP B                STRING类型                B是一个正则表达式，如果A与其匹配，则返回TRUE；反之返回FALSE。匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串A相匹配，而不是只需与其字符串匹配。

>.使用LIKE运算选择类似的值

>.选择条件可以包含字符或数字:

        % ：代表零个或多个字符(任意个字符)。

        _ ：代表一个字符。

>.RLIKE子句是Hive中这个功能的一个扩展，其可以通过Java的正则表达式这个更强大的语言来指定匹配条件。

hive (yinzhengjie)> select * from teacher where id LIKE '5%';                #查找以5开头id的老师信息

OK

teacher.id    teacher.name

    Rasmus Lerdorf

    Brendan Eich

Time taken: 0.126 seconds, Fetched:  row(s)

hive (yinzhengjie)>

hive (yinzhengjie)> select * from teacher where id LIKE '_2%';                #查找第二个数值为2的id的老师信息

OK

teacher.id    teacher.name

    Guido van Rossum

    Rob Pike

Time taken: 0.065 seconds, Fetched:  row(s)

hive (yinzhengjie)>

hive (yinzhengjie)> select * from teacher where name RLIKE '[P]';            #查找name字段中含有“P”字母的老师信息

OK

teacher.id    teacher.name

    Rob Pike

Time taken: 0.049 seconds, Fetched:  row(s)

hive (yinzhengjie)> 

hive (yinzhengjie)> select * from teacher where id NOT IN(,,,,);

OK

teacher.id    teacher.name

    James Gosling

    Martin Odersky

Time taken: 0.076 seconds, Fetched:  row(s)

hive (yinzhengjie)> 

hive (yinzhengjie)> select * from dept_partition;

OK

dept_partition.deptno    dept_partition.dname    dept_partition.loc    dept_partition.month

    开发部门

    运维部门

    测试部门

    产品部门

    销售部门

    财务部门

    人事部门

    开发部门

    开发部门

    运维部门

    测试部门

    产品部门

    销售部门

    财务部门

    人事部门

    运维部门

    测试部门

    产品部门

    销售部门

    财务部门

    人事部门

Time taken: 0.059 seconds, Fetched:  row(s)

hive (yinzhengjie)>

hive (yinzhengjie)> select t.deptno, avg(t.loc) avg_sal from dept_partition t group by t.deptno;            #计算dept_partition表每个部门的平均工资

WARNING: Hive-on-MR is deprecated in Hive  and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive .X releases.

Query ID = yinzhengjie_20180809212224_fcbdaa54-b167-4a43-8a08-c0a984c25a0d

Total jobs =

Launching Job  out of

Number of reduce tasks not specified. Estimated from input data size:

In order to change the average load for a reducer (in bytes):

  set hive.exec.reducers.bytes.per.reducer=<number>

In order to limit the maximum number of reducers:

  set hive.exec.reducers.max=<number>

In order to set a constant number of reducers:

  set mapreduce.job.reduces=<number>

Starting Job = job_1533789743141_0021, Tracking URL = http://s101:8088/proxy/application_1533789743141_0021/

Kill Command = /soft/hadoop-2.7./bin/hadoop job  -kill job_1533789743141_0021

Hadoop job information for Stage-: number of mappers: ; number of reducers:

-- ::, Stage- map = %,  reduce = %

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 2.62 sec

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 5.14 sec

MapReduce Total cumulative CPU time:  seconds  msec

Ended Job = job_1533789743141_0021

MapReduce Jobs Launched:

Stage-Stage-: Map:   Reduce:    Cumulative CPU: 5.14 sec   HDFS Read:  HDFS Write:  SUCCESS

Total MapReduce CPU Time Spent:  seconds  msec

OK

t.deptno    avg_sal

    18333.333333333332

    15666.666666666666

    7666.666666666667

    8266.666666666666

    18666.666666666668

    15000.0

    13566.666666666666

Time taken: 68.573 seconds, Fetched:  row(s)

hive (yinzhengjie)>

hive (yinzhengjie)> select * from dept_partition;

OK

dept_partition.deptno    dept_partition.dname    dept_partition.loc    dept_partition.month

    开发部门

    运维部门

    测试部门

    产品部门

    销售部门

    财务部门

    人事部门

    开发部门

    开发部门

    运维部门

    测试部门

    产品部门

    销售部门

    财务部门

    人事部门

    运维部门

    测试部门

    产品部门

    销售部门

    财务部门

    人事部门

Time taken: 0.072 seconds, Fetched:  row(s)

hive (yinzhengjie)>

hive (yinzhengjie)> select t.deptno, t.dname,max(t.loc) max_sal from dept_partition t group by t.deptno,t.dname;        #计算dept_partition每个部门中每个岗位的最高薪水

WARNING: Hive-on-MR is deprecated in Hive  and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive .X releases.

Query ID = yinzhengjie_20180809213154_e1ea82c8-897d-40b5-b167-5fe42d0e6476

Total jobs =

Launching Job  out of

Number of reduce tasks not specified. Estimated from input data size:

In order to change the average load for a reducer (in bytes):

  set hive.exec.reducers.bytes.per.reducer=<number>

In order to limit the maximum number of reducers:

  set hive.exec.reducers.max=<number>

In order to set a constant number of reducers:

  set mapreduce.job.reduces=<number>

Starting Job = job_1533789743141_0023, Tracking URL = http://s101:8088/proxy/application_1533789743141_0023/

Kill Command = /soft/hadoop-2.7./bin/hadoop job  -kill job_1533789743141_0023

Hadoop job information for Stage-: number of mappers: ; number of reducers:

-- ::, Stage- map = %,  reduce = %

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 1.85 sec

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 3.61 sec

MapReduce Total cumulative CPU time:  seconds  msec

Ended Job = job_1533789743141_0023

MapReduce Jobs Launched:

Stage-Stage-: Map:   Reduce:    Cumulative CPU: 3.61 sec   HDFS Read:  HDFS Write:  SUCCESS

Total MapReduce CPU Time Spent:  seconds  msec

OK

t.deptno    t.dname    max_sal

    开发部门

    运维部门

    测试部门

    产品部门

    销售部门

    财务部门

    人事部门

Time taken: 37.781 seconds, Fetched:  row(s)

hive (yinzhengjie)> 

hive (yinzhengjie)> select deptno,dname,avg(loc) AS avg_sal  from dept_partition  group by dname,deptno;                            #求每个部门的平均工资

WARNING: Hive-on-MR is deprecated in Hive  and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive .X releases.

Query ID = yinzhengjie_20180809213945_f7a1a9c2-8c19--9c1a-37faa29fee44

Total jobs =

Launching Job  out of

Number of reduce tasks not specified. Estimated from input data size:

In order to change the average load for a reducer (in bytes):

  set hive.exec.reducers.bytes.per.reducer=<number>

In order to limit the maximum number of reducers:

  set hive.exec.reducers.max=<number>

In order to set a constant number of reducers:

  set mapreduce.job.reduces=<number>

Starting Job = job_1533789743141_0024, Tracking URL = http://s101:8088/proxy/application_1533789743141_0024/

Kill Command = /soft/hadoop-2.7./bin/hadoop job  -kill job_1533789743141_0024

Hadoop job information for Stage-: number of mappers: ; number of reducers:

-- ::, Stage- map = %,  reduce = %

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 2.2 sec

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 4.69 sec

MapReduce Total cumulative CPU time:  seconds  msec

Ended Job = job_1533789743141_0024

MapReduce Jobs Launched:

Stage-Stage-: Map:   Reduce:    Cumulative CPU: 4.69 sec   HDFS Read:  HDFS Write:  SUCCESS

Total MapReduce CPU Time Spent:  seconds  msec

OK

deptno    dname    avg_sal

    开发部门    18333.333333333332

    运维部门    15666.666666666666

    测试部门    7666.666666666667

    产品部门    8266.666666666666

    销售部门    18666.666666666668

    财务部门    15000.0

    人事部门    13566.666666666666

Time taken: 63.433 seconds, Fetched:  row(s)

hive (yinzhengjie)>

hive (yinzhengjie)> select deptno,dname,avg(loc) AS avg_sal from dept_partition group by dname, deptno having avg_sal > ;            #求每个部门的平均薪水大于10000的部门

WARNING: Hive-on-MR is deprecated in Hive  and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive .X releases.

Query ID = yinzhengjie_20180809214521_d980d9db--4fd4-a062-ec9de0cafca2

Total jobs =

Launching Job  out of

Number of reduce tasks not specified. Estimated from input data size:

In order to change the average load for a reducer (in bytes):

  set hive.exec.reducers.bytes.per.reducer=<number>

In order to limit the maximum number of reducers:

  set hive.exec.reducers.max=<number>

In order to set a constant number of reducers:

  set mapreduce.job.reduces=<number>

Starting Job = job_1533789743141_0026, Tracking URL = http://s101:8088/proxy/application_1533789743141_0026/

Kill Command = /soft/hadoop-2.7./bin/hadoop job  -kill job_1533789743141_0026

Hadoop job information for Stage-: number of mappers: ; number of reducers:

-- ::, Stage- map = %,  reduce = %

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 1.45 sec

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 4.45 sec

MapReduce Total cumulative CPU time:  seconds  msec

Ended Job = job_1533789743141_0026

MapReduce Jobs Launched:

Stage-Stage-: Map:   Reduce:    Cumulative CPU: 4.45 sec   HDFS Read:  HDFS Write:  SUCCESS

Total MapReduce CPU Time Spent:  seconds  msec

OK

deptno    dname    avg_sal

    人事部门    13566.666666666666

    开发部门    18333.333333333332

    财务部门    15000.0

    运维部门    15666.666666666666

    销售部门    18666.666666666668

Time taken: 43.701 seconds, Fetched:  row(s)

hive (yinzhengjie)> 

Join语句-等值Join(hive (yinzhengjie)>  select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on e.deptno = d.deptno;)

    Hive支持通常的SQL JOIN语句，但是只支持等值连接，不支持非等值连接。

测试数据如下:

[yinzhengjie@s101 download]$ cat /home/yinzhengjie/download/dept.txt

    ACCOUNTING

    RESEARCH

    SALES

    OPERATIONS

[yinzhengjie@s101 download]$

[yinzhengjie@s101 download]$ cat /home/yinzhengjie/download/emp.txt

    SMITH    CLERK        --    800.00

    ALLEN    SALESMAN        --    1600.00    300.00

    WARD    SALESMAN        --    1250.00    500.00

    JONES    MANAGER        --    2975.00

    MARTIN    SALESMAN        --    1250.00    1400.00

    BLAKE    MANAGER        --    2850.00

    CLARK    MANAGER        --    2450.00

    SCOTT    ANALYST        --    3000.00

    KING    PRESIDENT        --    5000.00

    TURNER    SALESMAN        --    1500.00    0.00

    ADAMS    CLERK        --    1100.00

    JAMES    CLERK        --    950.00

    FORD    ANALYST        --    3000.00

    MILLER    CLERK        --    1300.00

[yinzhengjie@s101 download]$ 

hive查询操作如下：

hive (yinzhengjie)> create  table if not exists yinzhengjie.dept(

                  >     deptno int,

                  >     dname string,

                  >     loc int

                  > )

                  > row format delimited fields terminated by '\t';                            #创建部门表dept

OK

Time taken: 0.204 seconds

hive (yinzhengjie)> create  table if not exists yinzhengjie.emp(

                  >     empno int,

                  >     ename string,

                  >     job string,

                  >     mgr int,

                  >     hiredate string,

                  >     sal double,

                  >     comm double,

                  >     deptno int

                  >)

                  > row format delimited fields terminated by '\t';                            #创建员工表emp

OK

Time taken: 0.088 seconds

hive (yinzhengjie)>

hive (yinzhengjie)> load data local inpath '/home/yinzhengjie/download/dept.txt' into table yinzhengjie.dept;            #向dept中导入数据

Loading data to table yinzhengjie.dept

OK

Time taken: 0.222 seconds

hive (yinzhengjie)> load data local inpath '/home/yinzhengjie/download/emp.txt' into table yinzhengjie.emp;                #向emp中导入数据

Loading data to table yinzhengjie.emp

OK

Time taken: 0.175 seconds

hive (yinzhengjie)>

hive (yinzhengjie)>  select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on e.deptno = d.deptno;            #根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门编号；

WARNING: Hive-on-MR is deprecated in Hive  and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive .X releases.

Query ID = yinzhengjie_20180809233409_a9437af4-b312-4dfb-86af-f29bcf679577

Total jobs =

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in [jar:file:/soft/apache-hive-2.1.-bin/lib/log4j-slf4j-impl-2.4..jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/soft/hbase-1.2./lib/phoenix-4.10.-HBase-1.2-client.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/soft/hadoop-2.7./share/hadoop/common/lib/slf4j-log4j12-1.7..jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.

-- ::    Starting to launch local task to process map join;    maximum memory =

-- ::    Dump the side-table for tag:  with group count:  into file: file:/home/yinzhengjie/yinzhengjie/46c2c137-93f5-4f30--6b0d3d62c227/hive_2018--09_23--09_040_8075868526571286750-/-local-/HashTable-Stage-/MapJoin-mapfile11--.hashtable

-- ::    Uploaded  File to: file:/home/yinzhengjie/yinzhengjie/46c2c137-93f5-4f30--6b0d3d62c227/hive_2018--09_23--09_040_8075868526571286750-/-local-/HashTable-Stage-/MapJoin-mapfile11--.hashtable ( bytes)

-- ::    End of local task; Time Taken: 9.163 sec.

Execution completed successfully

MapredLocal task succeeded

Launching Job  out of

Number of reduce tasks is set to  since there's no reduce operator

Starting Job = job_1533789743141_0028, Tracking URL = http://s101:8088/proxy/application_1533789743141_0028/

Kill Command = /soft/hadoop-2.7./bin/hadoop job  -kill job_1533789743141_0028

Hadoop job information for Stage-: number of mappers: ; number of reducers:

-- ::, Stage- map = %,  reduce = %

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 2.71 sec

MapReduce Total cumulative CPU time:  seconds  msec

Ended Job = job_1533789743141_0028

MapReduce Jobs Launched:

Stage-Stage-: Map:    Cumulative CPU: 2.71 sec   HDFS Read:  HDFS Write:  SUCCESS

Total MapReduce CPU Time Spent:  seconds  msec

OK

e.empno    e.ename    d.deptno    d.dname

    SMITH        RESEARCH

    SMITH        RESEARCH

    ALLEN        SALES

    ALLEN        SALES

    WARD        SALES

    WARD        SALES

    JONES        RESEARCH

    JONES        RESEARCH

    MARTIN        SALES

    MARTIN        SALES

    BLAKE        SALES

    BLAKE        SALES

    CLARK        ACCOUNTING

    CLARK        ACCOUNTING

    SCOTT        RESEARCH

    SCOTT        RESEARCH

    KING        ACCOUNTING

    KING        ACCOUNTING

    TURNER        SALES

    TURNER        SALES

    ADAMS        RESEARCH

    ADAMS        RESEARCH

    JAMES        SALES

    JAMES        SALES

    FORD        RESEARCH

    FORD        RESEARCH

    MILLER        ACCOUNTING

    MILLER        ACCOUNTING

    SMITH        RESEARCH

    SMITH        RESEARCH

    ALLEN        SALES

    ALLEN        SALES

    WARD        SALES

    WARD        SALES

    JONES        RESEARCH

    JONES        RESEARCH

    MARTIN        SALES

    MARTIN        SALES

    BLAKE        SALES

    BLAKE        SALES

    CLARK        ACCOUNTING

    CLARK        ACCOUNTING

    SCOTT        RESEARCH

    SCOTT        RESEARCH

    KING        ACCOUNTING

    KING        ACCOUNTING

    TURNER        SALES

    TURNER        SALES

    ADAMS        RESEARCH

    ADAMS        RESEARCH

    JAMES        SALES

    JAMES        SALES

    FORD        RESEARCH

    FORD        RESEARCH

    MILLER        ACCOUNTING

    MILLER        ACCOUNTING

Time taken: 98.923 seconds, Fetched:  row(s)

hive (yinzhengjie)> 

Join语句-表的别名(hive (yinzhengjie)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;)

    表的别名有以下两个好处:

        >.使用别名可以简化查询。

        >.使用表名前缀可以提高执行效率。

hive (yinzhengjie)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;            #合并员工表和部门表

WARNING: Hive-on-MR is deprecated in Hive  and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive .X releases.

Query ID = yinzhengjie_20180809233120_cdd0ba5f-33b4-41f6-8f49-4a51e3c104ec

Total jobs =

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in [jar:file:/soft/apache-hive-2.1.-bin/lib/log4j-slf4j-impl-2.4..jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/soft/hbase-1.2./lib/phoenix-4.10.-HBase-1.2-client.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/soft/hadoop-2.7./share/hadoop/common/lib/slf4j-log4j12-1.7..jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.

-- ::    Starting to launch local task to process map join;    maximum memory =

-- ::    Dump the side-table for tag:  with group count:  into file: file:/home/yinzhengjie/yinzhengjie/46c2c137-93f5-4f30--6b0d3d62c227/hive_2018--09_23--20_931_5011927912909131499-/-local-/HashTable-Stage-/MapJoin-mapfile01--.hashtable

-- ::    Uploaded  File to: file:/home/yinzhengjie/yinzhengjie/46c2c137-93f5-4f30--6b0d3d62c227/hive_2018--09_23--20_931_5011927912909131499-/-local-/HashTable-Stage-/MapJoin-mapfile01--.hashtable ( bytes)

-- ::    End of local task; Time Taken: 16.147 sec.

Execution completed successfully

MapredLocal task succeeded

Launching Job  out of

Number of reduce tasks is set to  since there's no reduce operator

Starting Job = job_1533789743141_0027, Tracking URL = http://s101:8088/proxy/application_1533789743141_0027/

Kill Command = /soft/hadoop-2.7./bin/hadoop job  -kill job_1533789743141_0027

Hadoop job information for Stage-: number of mappers: ; number of reducers:

-- ::, Stage- map = %,  reduce = %

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 1.82 sec

MapReduce Total cumulative CPU time:  seconds  msec

Ended Job = job_1533789743141_0027

MapReduce Jobs Launched:

Stage-Stage-: Map:    Cumulative CPU: 1.82 sec   HDFS Read:  HDFS Write:  SUCCESS

Total MapReduce CPU Time Spent:  seconds  msec

OK

e.empno    e.ename    d.deptno

    SMITH

    SMITH

    ALLEN

    ALLEN

    WARD

    WARD

    JONES

    JONES

    MARTIN

    MARTIN

    BLAKE

    BLAKE

    CLARK

    CLARK

    SCOTT

    SCOTT

    KING

    KING

    TURNER

    TURNER

    ADAMS

    ADAMS

    JAMES

    JAMES

    FORD

    FORD

    MILLER

    MILLER

    SMITH

    SMITH

    ALLEN

    ALLEN

    WARD

    WARD

    JONES

    JONES

    MARTIN

    MARTIN

    BLAKE

    BLAKE

    CLARK

    CLARK

    SCOTT

    SCOTT

    KING

    KING

    TURNER

    TURNER

    ADAMS

    ADAMS

    JAMES

    JAMES

    FORD

    FORD

    MILLER

    MILLER

Time taken: 113.095 seconds, Fetched:  row(s)

hive (yinzhengjie)>

hive (yinzhengjie)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;

WARNING: Hive-on-MR is deprecated in Hive  and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive .X releases.

Query ID = yinzhengjie_20180809234054_a83fd2f0-136f--880a-0a928ecb86f0

Total jobs =

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in [jar:file:/soft/apache-hive-2.1.-bin/lib/log4j-slf4j-impl-2.4..jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/soft/hbase-1.2./lib/phoenix-4.10.-HBase-1.2-client.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/soft/hadoop-2.7./share/hadoop/common/lib/slf4j-log4j12-1.7..jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.

-- ::    Starting to launch local task to process map join;    maximum memory =

-- ::    Dump the side-table for tag:  with group count:  into file: file:/home/yinzhengjie/yinzhengjie/46c2c137-93f5-4f30--6b0d3d62c227/hive_2018--09_23--54_618_7309603760212569588-/-local-/HashTable-Stage-/MapJoin-mapfile21--.hashtable

-- ::    Uploaded  File to: file:/home/yinzhengjie/yinzhengjie/46c2c137-93f5-4f30--6b0d3d62c227/hive_2018--09_23--54_618_7309603760212569588-/-local-/HashTable-Stage-/MapJoin-mapfile21--.hashtable ( bytes)

-- ::    End of local task; Time Taken: 5.741 sec.

Execution completed successfully

MapredLocal task succeeded

Launching Job  out of

Number of reduce tasks is set to  since there's no reduce operator

Starting Job = job_1533789743141_0029, Tracking URL = http://s101:8088/proxy/application_1533789743141_0029/

Kill Command = /soft/hadoop-2.7./bin/hadoop job  -kill job_1533789743141_0029

Hadoop job information for Stage-: number of mappers: ; number of reducers:

-- ::, Stage- map = %,  reduce = %

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 2.69 sec

MapReduce Total cumulative CPU time:  seconds  msec

Ended Job = job_1533789743141_0029

MapReduce Jobs Launched:

Stage-Stage-: Map:    Cumulative CPU: 2.69 sec   HDFS Read:  HDFS Write:  SUCCESS

Total MapReduce CPU Time Spent:  seconds  msec

OK

e.empno    e.ename    d.deptno

    SMITH

    SMITH

    ALLEN

    ALLEN

    WARD

    WARD

    JONES

    JONES

    MARTIN

    MARTIN

    BLAKE

    BLAKE

    CLARK

    CLARK

    SCOTT

    SCOTT

    KING

    KING

    TURNER

    TURNER

    ADAMS

    ADAMS

    JAMES

    JAMES

    FORD

    FORD

    MILLER

    MILLER

    SMITH

    SMITH

    ALLEN

    ALLEN

    WARD

    WARD

    JONES

    JONES

    MARTIN

    MARTIN

    BLAKE

    BLAKE

    CLARK

    CLARK

    SCOTT

    SCOTT

    KING

    KING

    TURNER

    TURNER

    ADAMS

    ADAMS

    JAMES

    JAMES

    FORD

    FORD

    MILLER

    MILLER

Time taken: 53.142 seconds, Fetched:  row(s)

hive (yinzhengjie)>

hive (yinzhengjie)> select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;

WARNING: Hive-on-MR is deprecated in Hive  and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive .X releases.

Query ID = yinzhengjie_20180809234222_5966f5f0-b54a--ae82-fd47e8655582

Total jobs =

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in [jar:file:/soft/apache-hive-2.1.-bin/lib/log4j-slf4j-impl-2.4..jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/soft/hbase-1.2./lib/phoenix-4.10.-HBase-1.2-client.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/soft/hadoop-2.7./share/hadoop/common/lib/slf4j-log4j12-1.7..jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.

-- ::    Starting to launch local task to process map join;    maximum memory =

-- ::    Dump the side-table for tag:  with group count:  into file: file:/home/yinzhengjie/yinzhengjie/46c2c137-93f5-4f30--6b0d3d62c227/hive_2018--09_23--22_712_6649379300342030940-/-local-/HashTable-Stage-/MapJoin-mapfile31--.hashtable

-- ::    Uploaded  File to: file:/home/yinzhengjie/yinzhengjie/46c2c137-93f5-4f30--6b0d3d62c227/hive_2018--09_23--22_712_6649379300342030940-/-local-/HashTable-Stage-/MapJoin-mapfile31--.hashtable ( bytes)

-- ::    End of local task; Time Taken: 4.518 sec.

Execution completed successfully

MapredLocal task succeeded

Launching Job  out of

Number of reduce tasks is set to  since there's no reduce operator

Starting Job = job_1533789743141_0030, Tracking URL = http://s101:8088/proxy/application_1533789743141_0030/

Kill Command = /soft/hadoop-2.7./bin/hadoop job  -kill job_1533789743141_0030

Hadoop job information for Stage-: number of mappers: ; number of reducers:

-- ::, Stage- map = %,  reduce = %

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 2.03 sec

MapReduce Total cumulative CPU time:  seconds  msec

Ended Job = job_1533789743141_0030

MapReduce Jobs Launched:

Stage-Stage-: Map:    Cumulative CPU: 2.03 sec   HDFS Read:  HDFS Write:  SUCCESS

Total MapReduce CPU Time Spent:  seconds  msec

OK

e.empno    e.ename    d.deptno

    SMITH

    SMITH

    ALLEN

    ALLEN

    WARD

    WARD

    JONES

    JONES

    MARTIN

    MARTIN

    BLAKE

    BLAKE

    CLARK

    CLARK

    SCOTT

    SCOTT

    KING

    KING

    TURNER

    TURNER

    ADAMS

    ADAMS

    JAMES

    JAMES

    FORD

    FORD

    MILLER

    MILLER

    SMITH

    SMITH

    ALLEN

    ALLEN

    WARD

    WARD

    JONES

    JONES

    MARTIN

    MARTIN

    BLAKE

    BLAKE

    CLARK

    CLARK

    SCOTT

    SCOTT

    KING

    KING

    TURNER

    TURNER

    ADAMS

    ADAMS

    JAMES

    JAMES

    FORD

    FORD

    MILLER

    MILLER

Time taken: 57.477 seconds, Fetched:  row(s)

hive (yinzhengjie)>  

hive (yinzhengjie)>  select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;

WARNING: Hive-on-MR is deprecated in Hive  and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive .X releases.

Query ID = yinzhengjie_20180809234332_c83104d3--4e3d-a2bf-342b5c397b9d

Total jobs =

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in [jar:file:/soft/apache-hive-2.1.-bin/lib/log4j-slf4j-impl-2.4..jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/soft/hbase-1.2./lib/phoenix-4.10.-HBase-1.2-client.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/soft/hadoop-2.7./share/hadoop/common/lib/slf4j-log4j12-1.7..jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.

-- ::    Starting to launch local task to process map join;    maximum memory =

-- ::    Dump the side-table for tag:  with group count:  into file: file:/home/yinzhengjie/yinzhengjie/46c2c137-93f5-4f30--6b0d3d62c227/hive_2018--09_23--32_208_373121853797344697-/-local-/HashTable-Stage-/MapJoin-mapfile40--.hashtable

-- ::    Uploaded  File to: file:/home/yinzhengjie/yinzhengjie/46c2c137-93f5-4f30--6b0d3d62c227/hive_2018--09_23--32_208_373121853797344697-/-local-/HashTable-Stage-/MapJoin-mapfile40--.hashtable ( bytes)

-- ::    End of local task; Time Taken: 4.69 sec.

Execution completed successfully

MapredLocal task succeeded

Launching Job  out of

Number of reduce tasks is set to  since there's no reduce operator

Starting Job = job_1533789743141_0031, Tracking URL = http://s101:8088/proxy/application_1533789743141_0031/

Kill Command = /soft/hadoop-2.7./bin/hadoop job  -kill job_1533789743141_0031

Hadoop job information for Stage-: number of mappers: ; number of reducers:

-- ::, Stage- map = %,  reduce = %

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 2.38 sec

MapReduce Total cumulative CPU time:  seconds  msec

Ended Job = job_1533789743141_0031

MapReduce Jobs Launched:

Stage-Stage-: Map:    Cumulative CPU: 2.38 sec   HDFS Read:  HDFS Write:  SUCCESS

Total MapReduce CPU Time Spent:  seconds  msec

OK

e.empno    e.ename    d.deptno

    CLARK

    KING

    MILLER

    CLARK

    KING

    MILLER

    SMITH

    JONES

    SCOTT

    ADAMS

    FORD

    SMITH

    JONES

    SCOTT

    ADAMS

    FORD

    ALLEN

    WARD

    MARTIN

    BLAKE

    TURNER

    JAMES

    ALLEN

    WARD

    MARTIN

    BLAKE

    TURNER

    JAMES

NULL    NULL

    CLARK

    KING

    MILLER

    CLARK

    KING

    MILLER

    SMITH

    JONES

    SCOTT

    ADAMS

    FORD

    SMITH

    JONES

    SCOTT

    ADAMS

    FORD

    ALLEN

    WARD

    MARTIN

    BLAKE

    TURNER

    JAMES

    ALLEN

    WARD

    MARTIN

    BLAKE

    TURNER

    JAMES

NULL    NULL

Time taken: 87.954 seconds, Fetched:  row(s)

hive (yinzhengjie)> 

Join语句-满外连接(hive (yinzhengjie)> select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno;)

    满外连接：将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。

hive (yinzhengjie)> select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno;

WARNING: Hive-on-MR is deprecated in Hive  and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive .X releases.

Query ID = yinzhengjie_20180809235025_e7e97788-2d65-45e0-b567-004f2d7057e0

Total jobs =

Launching Job  out of

Number of reduce tasks not specified. Estimated from input data size:

In order to change the average load for a reducer (in bytes):

  set hive.exec.reducers.bytes.per.reducer=<number>

In order to limit the maximum number of reducers:

  set hive.exec.reducers.max=<number>

In order to set a constant number of reducers:

  set mapreduce.job.reduces=<number>

Starting Job = job_1533789743141_0035, Tracking URL = http://s101:8088/proxy/application_1533789743141_0035/

Kill Command = /soft/hadoop-2.7./bin/hadoop job  -kill job_1533789743141_0035

Hadoop job information for Stage-: number of mappers: ; number of reducers:

-- ::, Stage- map = %,  reduce = %

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 2.58 sec

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 4.88 sec

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 7.56 sec

MapReduce Total cumulative CPU time:  seconds  msec

Ended Job = job_1533789743141_0035

MapReduce Jobs Launched:

Stage-Stage-: Map:   Reduce:    Cumulative CPU: 7.56 sec   HDFS Read:  HDFS Write:  SUCCESS

Total MapReduce CPU Time Spent:  seconds  msec

OK

e.empno    e.ename    d.deptno

    MILLER

    MILLER

    KING

    KING

    CLARK

    CLARK

    MILLER

    MILLER

    KING

    KING

    CLARK

    CLARK

    SCOTT

    SCOTT

    JONES

    JONES

    JONES

    JONES

    SMITH

    SMITH

    FORD

    FORD

    ADAMS

    ADAMS

    SCOTT

    SCOTT

    SMITH

    SMITH

    FORD

    FORD

    ADAMS

    ADAMS

    JAMES

    JAMES

    TURNER

    TURNER

    TURNER

    TURNER

    ALLEN

    ALLEN

    BLAKE

    BLAKE

    MARTIN

    MARTIN

    JAMES

    JAMES

    WARD

    WARD

    ALLEN

    ALLEN

    MARTIN

    MARTIN

    WARD

    WARD

    BLAKE

    BLAKE

NULL    NULL

NULL    NULL

Time taken: 63.838 seconds, Fetched:  row(s)

hive (yinzhengjie)> 

Join语句-多表连接查询(hive (yinzhengjie)> SELECT e.ename, d.deptno, l. loc_name FROM   emp e JOIN   dept d ON     d.deptno = e.deptno JOIN   location l ON     d.loc = l.loc;)

测试文件内容:

[yinzhengjie@s101 ~]$ cat /home/yinzhengjie/download/location.txt

    Beijing

    London

    Tokyo

[yinzhengjie@s101 ~]$ 

    大多数情况下，Hive会对每对JOIN连接对象启动一个MapReduce任务。以下案例中会首先启动一个MapReduce job对表e和表d进行连接操作，

然后会再启动一个MapReduce job将第一个MapReduce job的输出和表l;进行连接操作。

    温馨提示：为什么不是表d和表l先进行连接操作呢？这是因为Hive总是按照从左到右的顺序执行的。

hive (yinzhengjie)> create table if not exists yinzhengjie.location(

                  >     loc int,

                  >     loc_name string

                  > )

                  > row format delimited fields terminated by '\t';                        #创建location表

OK

Time taken: 0.614 seconds

hive (yinzhengjie)> load data local inpath '/home/yinzhengjie/download/location.txt' into table yinzhengjie.location;        #向表中导入数据

Loading data to table yinzhengjie.location

OK

Time taken: 0.478 seconds

hive (yinzhengjie)>

hive (yinzhengjie)> SELECT e.ename, d.deptno, l. loc_name

                  > FROM   emp e

                  > JOIN   dept d

                  > ON     d.deptno = e.deptno

                  > JOIN   location l

                  > ON     d.loc = l.loc;                    #多表连接查询

WARNING: Hive-on-MR is deprecated in Hive  and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive .X releases.

Query ID = yinzhengjie_20180809235602_7fbd82df--4b76-b5c4-9482d4aa2ccc

Total jobs =

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in [jar:file:/soft/apache-hive-2.1.-bin/lib/log4j-slf4j-impl-2.4..jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/soft/hbase-1.2./lib/phoenix-4.10.-HBase-1.2-client.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/soft/hadoop-2.7./share/hadoop/common/lib/slf4j-log4j12-1.7..jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.

-- ::    Starting to launch local task to process map join;    maximum memory =

-- ::    Dump the side-table for tag:  with group count:  into file: file:/home/yinzhengjie/yinzhengjie/85f0ef7d-ce74-41a8-942e-d1798288e72b/hive_2018--09_23--02_428_1537442849954313200-/-local-/HashTable-Stage-/MapJoin-mapfile01--.hashtable

-- ::    Uploaded  File to: file:/home/yinzhengjie/yinzhengjie/85f0ef7d-ce74-41a8-942e-d1798288e72b/hive_2018--09_23--02_428_1537442849954313200-/-local-/HashTable-Stage-/MapJoin-mapfile01--.hashtable ( bytes)

-- ::    Dump the side-table for tag:  with group count:  into file: file:/home/yinzhengjie/yinzhengjie/85f0ef7d-ce74-41a8-942e-d1798288e72b/hive_2018--09_23--02_428_1537442849954313200-/-local-/HashTable-Stage-/MapJoin-mapfile11--.hashtable

-- ::    Uploaded  File to: file:/home/yinzhengjie/yinzhengjie/85f0ef7d-ce74-41a8-942e-d1798288e72b/hive_2018--09_23--02_428_1537442849954313200-/-local-/HashTable-Stage-/MapJoin-mapfile11--.hashtable ( bytes)

-- ::    End of local task; Time Taken: 3.928 sec.

Execution completed successfully

MapredLocal task succeeded

Launching Job  out of

Number of reduce tasks is set to  since there's no reduce operator

Starting Job = job_1533789743141_0036, Tracking URL = http://s101:8088/proxy/application_1533789743141_0036/

Kill Command = /soft/hadoop-2.7./bin/hadoop job  -kill job_1533789743141_0036

Hadoop job information for Stage-: number of mappers: ; number of reducers:

-- ::, Stage- map = %,  reduce = %

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 2.64 sec

MapReduce Total cumulative CPU time:  seconds  msec

Ended Job = job_1533789743141_0036

MapReduce Jobs Launched:

Stage-Stage-: Map:    Cumulative CPU: 2.64 sec   HDFS Read:  HDFS Write:  SUCCESS

Total MapReduce CPU Time Spent:  seconds  msec

OK

e.ename    d.deptno    l.loc_name

SMITH        London

ALLEN        Tokyo

WARD        Tokyo

JONES        London

MARTIN        Tokyo

BLAKE        Tokyo

CLARK        Beijing

SCOTT        London

KING        Beijing

TURNER        Tokyo

ADAMS        London

JAMES        Tokyo

FORD        London

MILLER        Beijing

SMITH        London

ALLEN        Tokyo

WARD        Tokyo

JONES        London

MARTIN        Tokyo

BLAKE        Tokyo

CLARK        Beijing

SCOTT        London

KING        Beijing

TURNER        Tokyo

ADAMS        London

JAMES        Tokyo

FORD        London

MILLER        Beijing

Time taken: 56.659 seconds, Fetched:  row(s)

hive (yinzhengjie)> 

Join语句-笛卡尔积(hive (yinzhengjie)> select * from emp, dept;)

    笛卡尔集会在下面条件下产生:

        >.省略连接条件

        >.连接条件无效

        >.所有表中的所有行互相连接

hive (yinzhengjie)> set hive.mapred.mode=strict;

hive (yinzhengjie)> set hive.mapred.mode;

hive.mapred.mode=strict

hive (yinzhengjie)> select * from emp, dept;                    #在strict模式执行笛卡尔积操作是失败的

FAILED: SemanticException Cartesian products are disabled for safety reasons. If you know what you are doing, please make sure that hive.strict.checks.cartesian.product is set to false and that hive.mapred.mode is not set to 'strict' to enable them.

hive (yinzhengjie)>

hive (yinzhengjie)> set hive.mapred.mode=nonstrict;

hive (yinzhengjie)> set hive.mapred.mode;

hive.mapred.mode=nonstrict

hive (yinzhengjie)> select empno, deptno from emp, dept;

FAILED: SemanticException Column deptno Found in more than One Tables/Subqueries

hive (yinzhengjie)> select * from emp, dept;                    #在nonstrict模式执行笛卡尔积操作是可以的，但不推荐使用这样的查询语句，意义不大！

Warning: Map Join MAPJOIN[][bigTable=?] in task 'Stage-3:MAPRED' is a cross product

WARNING: Hive-on-MR is deprecated in Hive  and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive .X releases.

Query ID = yinzhengjie_20180810000249_98e28c13-db4d-4e2b-81c6-28e44bf51f1d

Total jobs =

SLF4J: Class path contains multiple SLF4J bindings.

SLF4J: Found binding in [jar:file:/soft/apache-hive-2.1.-bin/lib/log4j-slf4j-impl-2.4..jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/soft/hbase-1.2./lib/phoenix-4.10.-HBase-1.2-client.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found binding in [jar:file:/soft/hadoop-2.7./share/hadoop/common/lib/slf4j-log4j12-1.7..jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.

-- ::    Starting to launch local task to process map join;    maximum memory =

-- ::    Dump the side-table for tag:  with group count:  into file: file:/home/yinzhengjie/yinzhengjie/85f0ef7d-ce74-41a8-942e-d1798288e72b/hive_2018--10_00--49_246_882868568149391185-/-local-/HashTable-Stage-/MapJoin-mapfile21--.hashtable

-- ::    Uploaded  File to: file:/home/yinzhengjie/yinzhengjie/85f0ef7d-ce74-41a8-942e-d1798288e72b/hive_2018--10_00--49_246_882868568149391185-/-local-/HashTable-Stage-/MapJoin-mapfile21--.hashtable ( bytes)

-- ::    End of local task; Time Taken: 3.916 sec.

Execution completed successfully

MapredLocal task succeeded

Launching Job  out of

Number of reduce tasks is set to  since there's no reduce operator

Starting Job = job_1533789743141_0037, Tracking URL = http://s101:8088/proxy/application_1533789743141_0037/

Kill Command = /soft/hadoop-2.7./bin/hadoop job  -kill job_1533789743141_0037

Hadoop job information for Stage-: number of mappers: ; number of reducers:

-- ::, Stage- map = %,  reduce = %

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 1.8 sec

MapReduce Total cumulative CPU time:  seconds  msec

Ended Job = job_1533789743141_0037

MapReduce Jobs Launched:

Stage-Stage-: Map:    Cumulative CPU: 1.8 sec   HDFS Read:  HDFS Write:  SUCCESS

Total MapReduce CPU Time Spent:  seconds  msec

OK

emp.empno    emp.ename    emp.job    emp.mgr    emp.hiredate    emp.sal    emp.comm    emp.deptno    dept.deptno    dept.dname    dept.loc

    SMITH    CLERK        --    800.0    NULL            ACCOUNTING

    SMITH    CLERK        --    800.0    NULL            RESEARCH

    SMITH    CLERK        --    800.0    NULL            SALES

    SMITH    CLERK        --    800.0    NULL            OPERATIONS

    SMITH    CLERK        --    800.0    NULL            ACCOUNTING

    SMITH    CLERK        --    800.0    NULL            RESEARCH

    SMITH    CLERK        --    800.0    NULL            SALES

    SMITH    CLERK        --    800.0    NULL            OPERATIONS

    ALLEN    SALESMAN        --    1600.0    300.0            ACCOUNTING

    ALLEN    SALESMAN        --    1600.0    300.0            RESEARCH

    ALLEN    SALESMAN        --    1600.0    300.0            SALES

    ALLEN    SALESMAN        --    1600.0    300.0            OPERATIONS

    ALLEN    SALESMAN        --    1600.0    300.0            ACCOUNTING

    ALLEN    SALESMAN        --    1600.0    300.0            RESEARCH

    ALLEN    SALESMAN        --    1600.0    300.0            SALES

    ALLEN    SALESMAN        --    1600.0    300.0            OPERATIONS

    WARD    SALESMAN        --    1250.0    500.0            ACCOUNTING

    WARD    SALESMAN        --    1250.0    500.0            RESEARCH

    WARD    SALESMAN        --    1250.0    500.0            SALES

    WARD    SALESMAN        --    1250.0    500.0            OPERATIONS

    WARD    SALESMAN        --    1250.0    500.0            ACCOUNTING

    WARD    SALESMAN        --    1250.0    500.0            RESEARCH

    WARD    SALESMAN        --    1250.0    500.0            SALES

    WARD    SALESMAN        --    1250.0    500.0            OPERATIONS

    JONES    MANAGER        --    2975.0    NULL            ACCOUNTING

    JONES    MANAGER        --    2975.0    NULL            RESEARCH

    JONES    MANAGER        --    2975.0    NULL            SALES

    JONES    MANAGER        --    2975.0    NULL            OPERATIONS

    JONES    MANAGER        --    2975.0    NULL            ACCOUNTING

    JONES    MANAGER        --    2975.0    NULL            RESEARCH

    JONES    MANAGER        --    2975.0    NULL            SALES

    JONES    MANAGER        --    2975.0    NULL            OPERATIONS

    MARTIN    SALESMAN        --    1250.0    1400.0            ACCOUNTING

    MARTIN    SALESMAN        --    1250.0    1400.0            RESEARCH

    MARTIN    SALESMAN        --    1250.0    1400.0            SALES

    MARTIN    SALESMAN        --    1250.0    1400.0            OPERATIONS

    MARTIN    SALESMAN        --    1250.0    1400.0            ACCOUNTING

    MARTIN    SALESMAN        --    1250.0    1400.0            RESEARCH

    MARTIN    SALESMAN        --    1250.0    1400.0            SALES

    MARTIN    SALESMAN        --    1250.0    1400.0            OPERATIONS

    BLAKE    MANAGER        --    2850.0    NULL            ACCOUNTING

    BLAKE    MANAGER        --    2850.0    NULL            RESEARCH

    BLAKE    MANAGER        --    2850.0    NULL            SALES

    BLAKE    MANAGER        --    2850.0    NULL            OPERATIONS

    BLAKE    MANAGER        --    2850.0    NULL            ACCOUNTING

    BLAKE    MANAGER        --    2850.0    NULL            RESEARCH

    BLAKE    MANAGER        --    2850.0    NULL            SALES

    BLAKE    MANAGER        --    2850.0    NULL            OPERATIONS

    CLARK    MANAGER        --    2450.0    NULL            ACCOUNTING

    CLARK    MANAGER        --    2450.0    NULL            RESEARCH

    CLARK    MANAGER        --    2450.0    NULL            SALES

    CLARK    MANAGER        --    2450.0    NULL            OPERATIONS

    CLARK    MANAGER        --    2450.0    NULL            ACCOUNTING

    CLARK    MANAGER        --    2450.0    NULL            RESEARCH

    CLARK    MANAGER        --    2450.0    NULL            SALES

    CLARK    MANAGER        --    2450.0    NULL            OPERATIONS

    SCOTT    ANALYST        --    3000.0    NULL            ACCOUNTING

    SCOTT    ANALYST        --    3000.0    NULL            RESEARCH

    SCOTT    ANALYST        --    3000.0    NULL            SALES

    SCOTT    ANALYST        --    3000.0    NULL            OPERATIONS

    SCOTT    ANALYST        --    3000.0    NULL            ACCOUNTING

    SCOTT    ANALYST        --    3000.0    NULL            RESEARCH

    SCOTT    ANALYST        --    3000.0    NULL            SALES

    SCOTT    ANALYST        --    3000.0    NULL            OPERATIONS

    KING    PRESIDENT    NULL    --    5000.0    NULL            ACCOUNTING

    KING    PRESIDENT    NULL    --    5000.0    NULL            RESEARCH

    KING    PRESIDENT    NULL    --    5000.0    NULL            SALES

    KING    PRESIDENT    NULL    --    5000.0    NULL            OPERATIONS

    KING    PRESIDENT    NULL    --    5000.0    NULL            ACCOUNTING

    KING    PRESIDENT    NULL    --    5000.0    NULL            RESEARCH

    KING    PRESIDENT    NULL    --    5000.0    NULL            SALES

    KING    PRESIDENT    NULL    --    5000.0    NULL            OPERATIONS

    TURNER    SALESMAN        --    1500.0    0.0            ACCOUNTING

    TURNER    SALESMAN        --    1500.0    0.0            RESEARCH

    TURNER    SALESMAN        --    1500.0    0.0            SALES

    TURNER    SALESMAN        --    1500.0    0.0            OPERATIONS

    TURNER    SALESMAN        --    1500.0    0.0            ACCOUNTING

    TURNER    SALESMAN        --    1500.0    0.0            RESEARCH

    TURNER    SALESMAN        --    1500.0    0.0            SALES

    TURNER    SALESMAN        --    1500.0    0.0            OPERATIONS

    ADAMS    CLERK        --    1100.0    NULL            ACCOUNTING

    ADAMS    CLERK        --    1100.0    NULL            RESEARCH

    ADAMS    CLERK        --    1100.0    NULL            SALES

    ADAMS    CLERK        --    1100.0    NULL            OPERATIONS

    ADAMS    CLERK        --    1100.0    NULL            ACCOUNTING

    ADAMS    CLERK        --    1100.0    NULL            RESEARCH

    ADAMS    CLERK        --    1100.0    NULL            SALES

    ADAMS    CLERK        --    1100.0    NULL            OPERATIONS

    JAMES    CLERK        --    950.0    NULL            ACCOUNTING

    JAMES    CLERK        --    950.0    NULL            RESEARCH

    JAMES    CLERK        --    950.0    NULL            SALES

    JAMES    CLERK        --    950.0    NULL            OPERATIONS

    JAMES    CLERK        --    950.0    NULL            ACCOUNTING

    JAMES    CLERK        --    950.0    NULL            RESEARCH

    JAMES    CLERK        --    950.0    NULL            SALES

    JAMES    CLERK        --    950.0    NULL            OPERATIONS

    FORD    ANALYST        --    3000.0    NULL            ACCOUNTING

    FORD    ANALYST        --    3000.0    NULL            RESEARCH

    FORD    ANALYST        --    3000.0    NULL            SALES

    FORD    ANALYST        --    3000.0    NULL            OPERATIONS

    FORD    ANALYST        --    3000.0    NULL            ACCOUNTING

    FORD    ANALYST        --    3000.0    NULL            RESEARCH

    FORD    ANALYST        --    3000.0    NULL            SALES

    FORD    ANALYST        --    3000.0    NULL            OPERATIONS

    MILLER    CLERK        --    1300.0    NULL            ACCOUNTING

    MILLER    CLERK        --    1300.0    NULL            RESEARCH

    MILLER    CLERK        --    1300.0    NULL            SALES

    MILLER    CLERK        --    1300.0    NULL            OPERATIONS

    MILLER    CLERK        --    1300.0    NULL            ACCOUNTING

    MILLER    CLERK        --    1300.0    NULL            RESEARCH

    MILLER    CLERK        --    1300.0    NULL            SALES

    MILLER    CLERK        --    1300.0    NULL            OPERATIONS

    SMITH    CLERK        --    800.0    NULL            ACCOUNTING

    SMITH    CLERK        --    800.0    NULL            RESEARCH

    SMITH    CLERK        --    800.0    NULL            SALES

    SMITH    CLERK        --    800.0    NULL            OPERATIONS

    SMITH    CLERK        --    800.0    NULL            ACCOUNTING

    SMITH    CLERK        --    800.0    NULL            RESEARCH

    SMITH    CLERK        --    800.0    NULL            SALES

    SMITH    CLERK        --    800.0    NULL            OPERATIONS

    ALLEN    SALESMAN        --    1600.0    300.0            ACCOUNTING

    ALLEN    SALESMAN        --    1600.0    300.0            RESEARCH

    ALLEN    SALESMAN        --    1600.0    300.0            SALES

    ALLEN    SALESMAN        --    1600.0    300.0            OPERATIONS

    ALLEN    SALESMAN        --    1600.0    300.0            ACCOUNTING

    ALLEN    SALESMAN        --    1600.0    300.0            RESEARCH

    ALLEN    SALESMAN        --    1600.0    300.0            SALES

    ALLEN    SALESMAN        --    1600.0    300.0            OPERATIONS

    WARD    SALESMAN        --    1250.0    500.0            ACCOUNTING

    WARD    SALESMAN        --    1250.0    500.0            RESEARCH

    WARD    SALESMAN        --    1250.0    500.0            SALES

    WARD    SALESMAN        --    1250.0    500.0            OPERATIONS

    WARD    SALESMAN        --    1250.0    500.0            ACCOUNTING

    WARD    SALESMAN        --    1250.0    500.0            RESEARCH

    WARD    SALESMAN        --    1250.0    500.0            SALES

    WARD    SALESMAN        --    1250.0    500.0            OPERATIONS

    JONES    MANAGER        --    2975.0    NULL            ACCOUNTING

    JONES    MANAGER        --    2975.0    NULL            RESEARCH

    JONES    MANAGER        --    2975.0    NULL            SALES

    JONES    MANAGER        --    2975.0    NULL            OPERATIONS

    JONES    MANAGER        --    2975.0    NULL            ACCOUNTING

    JONES    MANAGER        --    2975.0    NULL            RESEARCH

    JONES    MANAGER        --    2975.0    NULL            SALES

    JONES    MANAGER        --    2975.0    NULL            OPERATIONS

    MARTIN    SALESMAN        --    1250.0    1400.0            ACCOUNTING

    MARTIN    SALESMAN        --    1250.0    1400.0            RESEARCH

    MARTIN    SALESMAN        --    1250.0    1400.0            SALES

    MARTIN    SALESMAN        --    1250.0    1400.0            OPERATIONS

    MARTIN    SALESMAN        --    1250.0    1400.0            ACCOUNTING

    MARTIN    SALESMAN        --    1250.0    1400.0            RESEARCH

    MARTIN    SALESMAN        --    1250.0    1400.0            SALES

    MARTIN    SALESMAN        --    1250.0    1400.0            OPERATIONS

    BLAKE    MANAGER        --    2850.0    NULL            ACCOUNTING

    BLAKE    MANAGER        --    2850.0    NULL            RESEARCH

    BLAKE    MANAGER        --    2850.0    NULL            SALES

    BLAKE    MANAGER        --    2850.0    NULL            OPERATIONS

    BLAKE    MANAGER        --    2850.0    NULL            ACCOUNTING

    BLAKE    MANAGER        --    2850.0    NULL            RESEARCH

    BLAKE    MANAGER        --    2850.0    NULL            SALES

    BLAKE    MANAGER        --    2850.0    NULL            OPERATIONS

    CLARK    MANAGER        --    2450.0    NULL            ACCOUNTING

    CLARK    MANAGER        --    2450.0    NULL            RESEARCH

    CLARK    MANAGER        --    2450.0    NULL            SALES

    CLARK    MANAGER        --    2450.0    NULL            OPERATIONS

    CLARK    MANAGER        --    2450.0    NULL            ACCOUNTING

    CLARK    MANAGER        --    2450.0    NULL            RESEARCH

    CLARK    MANAGER        --    2450.0    NULL            SALES

    CLARK    MANAGER        --    2450.0    NULL            OPERATIONS

    SCOTT    ANALYST        --    3000.0    NULL            ACCOUNTING

    SCOTT    ANALYST        --    3000.0    NULL            RESEARCH

    SCOTT    ANALYST        --    3000.0    NULL            SALES

    SCOTT    ANALYST        --    3000.0    NULL            OPERATIONS

    SCOTT    ANALYST        --    3000.0    NULL            ACCOUNTING

    SCOTT    ANALYST        --    3000.0    NULL            RESEARCH

    SCOTT    ANALYST        --    3000.0    NULL            SALES

    SCOTT    ANALYST        --    3000.0    NULL            OPERATIONS

    KING    PRESIDENT    NULL    --    5000.0    NULL            ACCOUNTING

    KING    PRESIDENT    NULL    --    5000.0    NULL            RESEARCH

    KING    PRESIDENT    NULL    --    5000.0    NULL            SALES

    KING    PRESIDENT    NULL    --    5000.0    NULL            OPERATIONS

    KING    PRESIDENT    NULL    --    5000.0    NULL            ACCOUNTING

    KING    PRESIDENT    NULL    --    5000.0    NULL            RESEARCH

    KING    PRESIDENT    NULL    --    5000.0    NULL            SALES

    KING    PRESIDENT    NULL    --    5000.0    NULL            OPERATIONS

    TURNER    SALESMAN        --    1500.0    0.0            ACCOUNTING

    TURNER    SALESMAN        --    1500.0    0.0            RESEARCH

    TURNER    SALESMAN        --    1500.0    0.0            SALES

    TURNER    SALESMAN        --    1500.0    0.0            OPERATIONS

    TURNER    SALESMAN        --    1500.0    0.0            ACCOUNTING

    TURNER    SALESMAN        --    1500.0    0.0            RESEARCH

    TURNER    SALESMAN        --    1500.0    0.0            SALES

    TURNER    SALESMAN        --    1500.0    0.0            OPERATIONS

    ADAMS    CLERK        --    1100.0    NULL            ACCOUNTING

    ADAMS    CLERK        --    1100.0    NULL            RESEARCH

    ADAMS    CLERK        --    1100.0    NULL            SALES

    ADAMS    CLERK        --    1100.0    NULL            OPERATIONS

    ADAMS    CLERK        --    1100.0    NULL            ACCOUNTING

    ADAMS    CLERK        --    1100.0    NULL            RESEARCH

    ADAMS    CLERK        --    1100.0    NULL            SALES

    ADAMS    CLERK        --    1100.0    NULL            OPERATIONS

    JAMES    CLERK        --    950.0    NULL            ACCOUNTING

    JAMES    CLERK        --    950.0    NULL            RESEARCH

    JAMES    CLERK        --    950.0    NULL            SALES

    JAMES    CLERK        --    950.0    NULL            OPERATIONS

    JAMES    CLERK        --    950.0    NULL            ACCOUNTING

    JAMES    CLERK        --    950.0    NULL            RESEARCH

    JAMES    CLERK        --    950.0    NULL            SALES

    JAMES    CLERK        --    950.0    NULL            OPERATIONS

    FORD    ANALYST        --    3000.0    NULL            ACCOUNTING

    FORD    ANALYST        --    3000.0    NULL            RESEARCH

    FORD    ANALYST        --    3000.0    NULL            SALES

    FORD    ANALYST        --    3000.0    NULL            OPERATIONS

    FORD    ANALYST        --    3000.0    NULL            ACCOUNTING

    FORD    ANALYST        --    3000.0    NULL            RESEARCH

    FORD    ANALYST        --    3000.0    NULL            SALES

    FORD    ANALYST        --    3000.0    NULL            OPERATIONS

    MILLER    CLERK        --    1300.0    NULL            ACCOUNTING

    MILLER    CLERK        --    1300.0    NULL            RESEARCH

    MILLER    CLERK        --    1300.0    NULL            SALES

    MILLER    CLERK        --    1300.0    NULL            OPERATIONS

    MILLER    CLERK        --    1300.0    NULL            ACCOUNTING

    MILLER    CLERK        --    1300.0    NULL            RESEARCH

    MILLER    CLERK        --    1300.0    NULL            SALES

    MILLER    CLERK        --    1300.0    NULL            OPERATIONS

Time taken: 52.698 seconds, Fetched:  row(s)

hive (yinzhengjie)> 

排序-全局排序(hive (yinzhengjie)> select * from emp order by sal desc;)

    Order By：全局排序，一个MapReduce

        >.使用 ORDER BY 子句排序

            ASC（ascend）: 升序（默认）

            DESC（descend）: 降序

        >.ORDER BY 子句在SELECT语句的结尾。

hive (yinzhengjie)> select * from emp order by sal;                                #查询员工信息按工资升序排列,默认就是升序排列

WARNING: Hive-on-MR is deprecated in Hive  and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive .X releases.

Query ID = yinzhengjie_20180810001838_6c529433-c84b-447d-89e0-16af47dc89eb

Total jobs =

Launching Job  out of

Number of reduce tasks determined at compile time:

In order to change the average load for a reducer (in bytes):

  set hive.exec.reducers.bytes.per.reducer=<number>

In order to limit the maximum number of reducers:

  set hive.exec.reducers.max=<number>

In order to set a constant number of reducers:

  set mapreduce.job.reduces=<number>

Starting Job = job_1533789743141_0039, Tracking URL = http://s101:8088/proxy/application_1533789743141_0039/

Kill Command = /soft/hadoop-2.7./bin/hadoop job  -kill job_1533789743141_0039

Hadoop job information for Stage-: number of mappers: ; number of reducers:

-- ::, Stage- map = %,  reduce = %

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 2.66 sec

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 4.41 sec

MapReduce Total cumulative CPU time:  seconds  msec

Ended Job = job_1533789743141_0039

MapReduce Jobs Launched:

Stage-Stage-: Map:   Reduce:    Cumulative CPU: 4.41 sec   HDFS Read:  HDFS Write:  SUCCESS

Total MapReduce CPU Time Spent:  seconds  msec

OK

emp.empno    emp.ename    emp.job    emp.mgr    emp.hiredate    emp.sal    emp.comm    emp.deptno

    SMITH    CLERK        --    800.0    NULL

    SMITH    CLERK        --    800.0    NULL

    JAMES    CLERK        --    950.0    NULL

    JAMES    CLERK        --    950.0    NULL

    ADAMS    CLERK        --    1100.0    NULL

    ADAMS    CLERK        --    1100.0    NULL

    WARD    SALESMAN        --    1250.0    500.0

    WARD    SALESMAN        --    1250.0    500.0

    MARTIN    SALESMAN        --    1250.0    1400.0

    MARTIN    SALESMAN        --    1250.0    1400.0

    MILLER    CLERK        --    1300.0    NULL

    MILLER    CLERK        --    1300.0    NULL

    TURNER    SALESMAN        --    1500.0    0.0

    TURNER    SALESMAN        --    1500.0    0.0

    ALLEN    SALESMAN        --    1600.0    300.0

    ALLEN    SALESMAN        --    1600.0    300.0

    CLARK    MANAGER        --    2450.0    NULL

    CLARK    MANAGER        --    2450.0    NULL

    BLAKE    MANAGER        --    2850.0    NULL

    BLAKE    MANAGER        --    2850.0    NULL

    JONES    MANAGER        --    2975.0    NULL

    JONES    MANAGER        --    2975.0    NULL

    SCOTT    ANALYST        --    3000.0    NULL

    SCOTT    ANALYST        --    3000.0    NULL

    FORD    ANALYST        --    3000.0    NULL

    FORD    ANALYST        --    3000.0    NULL

    KING    PRESIDENT    NULL    --    5000.0    NULL

    KING    PRESIDENT    NULL    --    5000.0    NULL

Time taken: 82.564 seconds, Fetched:  row(s)

hive (yinzhengjie)>

hive (yinzhengjie)> select * from emp order by sal desc;                        #查询员工信息按工资降序排列

WARNING: Hive-on-MR is deprecated in Hive  and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive .X releases.

Query ID = yinzhengjie_20180810002012_ebf1251c-c92b--bea7-bb8a2c34ebdb

Total jobs =

Launching Job  out of

Number of reduce tasks determined at compile time:

In order to change the average load for a reducer (in bytes):

  set hive.exec.reducers.bytes.per.reducer=<number>

In order to limit the maximum number of reducers:

  set hive.exec.reducers.max=<number>

In order to set a constant number of reducers:

  set mapreduce.job.reduces=<number>

Starting Job = job_1533789743141_0040, Tracking URL = http://s101:8088/proxy/application_1533789743141_0040/

Kill Command = /soft/hadoop-2.7./bin/hadoop job  -kill job_1533789743141_0040

Hadoop job information for Stage-: number of mappers: ; number of reducers:

-- ::, Stage- map = %,  reduce = %

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 2.47 sec

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 5.31 sec

MapReduce Total cumulative CPU time:  seconds  msec

Ended Job = job_1533789743141_0040

MapReduce Jobs Launched:

Stage-Stage-: Map:   Reduce:    Cumulative CPU: 5.31 sec   HDFS Read:  HDFS Write:  SUCCESS

Total MapReduce CPU Time Spent:  seconds  msec

OK

emp.empno    emp.ename    emp.job    emp.mgr    emp.hiredate    emp.sal    emp.comm    emp.deptno

    KING    PRESIDENT    NULL    --    5000.0    NULL

    KING    PRESIDENT    NULL    --    5000.0    NULL

    FORD    ANALYST        --    3000.0    NULL

    SCOTT    ANALYST        --    3000.0    NULL

    SCOTT    ANALYST        --    3000.0    NULL

    FORD    ANALYST        --    3000.0    NULL

    JONES    MANAGER        --    2975.0    NULL

    JONES    MANAGER        --    2975.0    NULL

    BLAKE    MANAGER        --    2850.0    NULL

    BLAKE    MANAGER        --    2850.0    NULL

    CLARK    MANAGER        --    2450.0    NULL

    CLARK    MANAGER        --    2450.0    NULL

    ALLEN    SALESMAN        --    1600.0    300.0

    ALLEN    SALESMAN        --    1600.0    300.0

    TURNER    SALESMAN        --    1500.0    0.0

    TURNER    SALESMAN        --    1500.0    0.0

    MILLER    CLERK        --    1300.0    NULL

    MILLER    CLERK        --    1300.0    NULL

    WARD    SALESMAN        --    1250.0    500.0

    MARTIN    SALESMAN        --    1250.0    1400.0

    MARTIN    SALESMAN        --    1250.0    1400.0

    WARD    SALESMAN        --    1250.0    500.0

    ADAMS    CLERK        --    1100.0    NULL

    ADAMS    CLERK        --    1100.0    NULL

    JAMES    CLERK        --    950.0    NULL

    JAMES    CLERK        --    950.0    NULL

    SMITH    CLERK        --    800.0    NULL

    SMITH    CLERK        --    800.0    NULL

Time taken: 51.103 seconds, Fetched:  row(s)

hive (yinzhengjie)>

排序-按照别名排序(hive (yinzhengjie)> select ename, sal* twosal from emp order by twosal;)

hive (yinzhengjie)> select ename, sal* twosal from emp order by twosal;            #按照员工薪水的2倍排序

WARNING: Hive-on-MR is deprecated in Hive  and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive .X releases.

Query ID = yinzhengjie_20180810002258_b9f73ab7-2a29-459a-9b27-119eb56f1dde

Total jobs =

Launching Job  out of

Number of reduce tasks determined at compile time:

In order to change the average load for a reducer (in bytes):

  set hive.exec.reducers.bytes.per.reducer=<number>

In order to limit the maximum number of reducers:

  set hive.exec.reducers.max=<number>

In order to set a constant number of reducers:

  set mapreduce.job.reduces=<number>

Starting Job = job_1533789743141_0041, Tracking URL = http://s101:8088/proxy/application_1533789743141_0041/

Kill Command = /soft/hadoop-2.7./bin/hadoop job  -kill job_1533789743141_0041

Hadoop job information for Stage-: number of mappers: ; number of reducers:

-- ::, Stage- map = %,  reduce = %

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 2.6 sec

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 4.99 sec

MapReduce Total cumulative CPU time:  seconds  msec

Ended Job = job_1533789743141_0041

MapReduce Jobs Launched:

Stage-Stage-: Map:   Reduce:    Cumulative CPU: 4.99 sec   HDFS Read:  HDFS Write:  SUCCESS

Total MapReduce CPU Time Spent:  seconds  msec

OK

ename    twosal

SMITH    1600.0

SMITH    1600.0

JAMES    1900.0

JAMES    1900.0

ADAMS    2200.0

ADAMS    2200.0

WARD    2500.0

WARD    2500.0

MARTIN    2500.0

MARTIN    2500.0

MILLER    2600.0

MILLER    2600.0

TURNER    3000.0

TURNER    3000.0

ALLEN    3200.0

ALLEN    3200.0

CLARK    4900.0

CLARK    4900.0

BLAKE    5700.0

BLAKE    5700.0

JONES    5950.0

JONES    5950.0

SCOTT    6000.0

SCOTT    6000.0

FORD    6000.0

FORD    6000.0

KING    10000.0

KING    10000.0

Time taken: 44.517 seconds, Fetched:  row(s)

hive (yinzhengjie)> 

排序-多个列排序(hive (yinzhengjie)> select ename, deptno, sal from emp order by deptno, sal ;)

hive (yinzhengjie)> select ename, deptno, sal from emp order by deptno, sal ;                #按照部门和工资升序排序

WARNING: Hive-on-MR is deprecated in Hive  and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive .X releases.

Query ID = yinzhengjie_20180810002405_c29a1508--4d7c-9b50-e2fc04c8bdbc

Total jobs =

Launching Job  out of

Number of reduce tasks determined at compile time:

In order to change the average load for a reducer (in bytes):

  set hive.exec.reducers.bytes.per.reducer=<number>

In order to limit the maximum number of reducers:

  set hive.exec.reducers.max=<number>

In order to set a constant number of reducers:

  set mapreduce.job.reduces=<number>

Starting Job = job_1533789743141_0042, Tracking URL = http://s101:8088/proxy/application_1533789743141_0042/

Kill Command = /soft/hadoop-2.7./bin/hadoop job  -kill job_1533789743141_0042

Hadoop job information for Stage-: number of mappers: ; number of reducers:

-- ::, Stage- map = %,  reduce = %

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 1.77 sec

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 3.85 sec

MapReduce Total cumulative CPU time:  seconds  msec

Ended Job = job_1533789743141_0042

MapReduce Jobs Launched:

Stage-Stage-: Map:   Reduce:    Cumulative CPU: 3.85 sec   HDFS Read:  HDFS Write:  SUCCESS

Total MapReduce CPU Time Spent:  seconds  msec

OK

ename    deptno    sal

MILLER        1300.0

MILLER        1300.0

CLARK        2450.0

CLARK        2450.0

KING        5000.0

KING        5000.0

SMITH        800.0

SMITH        800.0

ADAMS        1100.0

ADAMS        1100.0

JONES        2975.0

JONES        2975.0

FORD        3000.0

SCOTT        3000.0

FORD        3000.0

SCOTT        3000.0

JAMES        950.0

JAMES        950.0

WARD        1250.0

MARTIN        1250.0

MARTIN        1250.0

WARD        1250.0

TURNER        1500.0

TURNER        1500.0

ALLEN        1600.0

ALLEN        1600.0

BLAKE        2850.0

BLAKE        2850.0

Time taken: 39.975 seconds, Fetched:  row(s)

hive (yinzhengjie)> 

排序-每个MapReduce内部排序(hive (yinzhengjie)> insert overwrite local directory '/home/yinzhengjie/download/sortby-result' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' select * from emp sort by deptno desc;)

hive (yinzhengjie)> set mapreduce.job.reduces=;                    #设置reduce个数

hive (yinzhengjie)> set mapreduce.job.reduces;                        #查看设置reduce个数

mapreduce.job.reduces=

hive (yinzhengjie)>

hive (yinzhengjie)>  select * from emp sort by empno desc;            #根据部门编号降序查看员工信息

WARNING: Hive-on-MR is deprecated in Hive  and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive .X releases.

Query ID = yinzhengjie_20180810002752_cd4d7e0d-be26---9379c1632a3a

Total jobs =

Launching Job  out of

Number of reduce tasks not specified. Defaulting to jobconf value of:

In order to change the average load for a reducer (in bytes):

  set hive.exec.reducers.bytes.per.reducer=<number>

In order to limit the maximum number of reducers:

  set hive.exec.reducers.max=<number>

In order to set a constant number of reducers:

  set mapreduce.job.reduces=<number>

Starting Job = job_1533789743141_0043, Tracking URL = http://s101:8088/proxy/application_1533789743141_0043/

Kill Command = /soft/hadoop-2.7./bin/hadoop job  -kill job_1533789743141_0043

Hadoop job information for Stage-: number of mappers: ; number of reducers:

-- ::, Stage- map = %,  reduce = %

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 2.02 sec

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 2.45 sec

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 4.76 sec

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 7.48 sec

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 10.02 sec

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 10.69 sec

MapReduce Total cumulative CPU time:  seconds  msec

Ended Job = job_1533789743141_0043

MapReduce Jobs Launched:

Stage-Stage-: Map:   Reduce:    Cumulative CPU: 10.69 sec   HDFS Read:  HDFS Write:  SUCCESS

Total MapReduce CPU Time Spent:  seconds  msec

OK

emp.empno    emp.ename    emp.job    emp.mgr    emp.hiredate    emp.sal    emp.comm    emp.deptno

    ADAMS    CLERK        --    1100.0    NULL

    TURNER    SALESMAN        --    1500.0    0.0

    TURNER    SALESMAN        --    1500.0    0.0

    KING    PRESIDENT    NULL    --    5000.0    NULL

    SCOTT    ANALYST        --    3000.0    NULL

    SCOTT    ANALYST        --    3000.0    NULL

    CLARK    MANAGER        --    2450.0    NULL

    BLAKE    MANAGER        --    2850.0    NULL

    MARTIN    SALESMAN        --    1250.0    1400.0

    MARTIN    SALESMAN        --    1250.0    1400.0

    JONES    MANAGER        --    2975.0    NULL

    SMITH    CLERK        --    800.0    NULL

    MILLER    CLERK        --    1300.0    NULL

    FORD    ANALYST        --    3000.0    NULL

    JAMES    CLERK        --    950.0    NULL

    JAMES    CLERK        --    950.0    NULL

    ADAMS    CLERK        --    1100.0    NULL

    BLAKE    MANAGER        --    2850.0    NULL

    JONES    MANAGER        --    2975.0    NULL

    WARD    SALESMAN        --    1250.0    500.0

    WARD    SALESMAN        --    1250.0    500.0

    ALLEN    SALESMAN        --    1600.0    300.0

    MILLER    CLERK        --    1300.0    NULL

    FORD    ANALYST        --    3000.0    NULL

    KING    PRESIDENT    NULL    --    5000.0    NULL

    CLARK    MANAGER        --    2450.0    NULL

    ALLEN    SALESMAN        --    1600.0    300.0

    SMITH    CLERK        --    800.0    NULL

Time taken: 67.599 seconds, Fetched:  row(s)

hive (yinzhengjie)>

hive (yinzhengjie)> insert overwrite local directory '/home/yinzhengjie/download/sortby-result' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' select * from emp sort by deptno desc;          #将查询结果导入到文件中（按照部门编号降序排序）

WARNING: Hive-on-MR is deprecated in Hive  and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive .X releases.

Query ID = yinzhengjie_20180810003404_42a220b7-02c7-42ae-bf8a-566c6300f4c3

Total jobs =

Launching Job  out of

Number of reduce tasks not specified. Defaulting to jobconf value of:

In order to change the average load for a reducer (in bytes):

  set hive.exec.reducers.bytes.per.reducer=<number>

In order to limit the maximum number of reducers:

  set hive.exec.reducers.max=<number>

In order to set a constant number of reducers:

  set mapreduce.job.reduces=<number>

Starting Job = job_1533789743141_0045, Tracking URL = http://s101:8088/proxy/application_1533789743141_0045/

Kill Command = /soft/hadoop-2.7./bin/hadoop job  -kill job_1533789743141_0045

Hadoop job information for Stage-: number of mappers: ; number of reducers:

-- ::, Stage- map = %,  reduce = %

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 1.22 sec

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 3.35 sec

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 5.71 sec

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 7.57 sec

MapReduce Total cumulative CPU time:  seconds  msec

Ended Job = job_1533789743141_0045

Moving data to local directory /home/yinzhengjie/download/sortby-result

MapReduce Jobs Launched:

Stage-Stage-: Map:   Reduce:    Cumulative CPU: 7.57 sec   HDFS Read:  HDFS Write:  SUCCESS

Total MapReduce CPU Time Spent:  seconds  msec

OK

emp.empno    emp.ename    emp.job    emp.mgr    emp.hiredate    emp.sal    emp.comm    emp.deptno

Time taken: 62.425 seconds

hive (yinzhengjie)> 

排序-分区排序(hive (yinzhengjie)> insert overwrite local directory '/home/yinzhengjie/download/sortby-result'  ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'  select * from emp distribute by deptno sort by empno desc;)

    Distribute By：类似MR中partition，进行分区，结合sort by使用。

    温馨提示，Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果。

hive (yinzhengjie)> set mapreduce.job.reduces;

mapreduce.job.reduces=

hive (yinzhengjie)>

hive (yinzhengjie)> insert overwrite local directory '/home/yinzhengjie/download/sortby-result'  ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'  select * from emp distribute by deptno sort by empno desc;            #先按照部门编号分区，再按照员工编号降序排序。

WARNING: Hive-on-MR is deprecated in Hive  and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive .X releases.

Query ID = yinzhengjie_20180810003826_af885657-4f0a-4e2a-83f3-62cbdabda4f3

Total jobs =

Launching Job  out of

Number of reduce tasks not specified. Defaulting to jobconf value of:

In order to change the average load for a reducer (in bytes):

  set hive.exec.reducers.bytes.per.reducer=<number>

In order to limit the maximum number of reducers:

  set hive.exec.reducers.max=<number>

In order to set a constant number of reducers:

  set mapreduce.job.reduces=<number>

Starting Job = job_1533789743141_0046, Tracking URL = http://s101:8088/proxy/application_1533789743141_0046/

Kill Command = /soft/hadoop-2.7./bin/hadoop job  -kill job_1533789743141_0046

Hadoop job information for Stage-: number of mappers: ; number of reducers:

-- ::, Stage- map = %,  reduce = %

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 2.07 sec

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 4.54 sec

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 6.44 sec

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 8.78 sec

MapReduce Total cumulative CPU time:  seconds  msec

Ended Job = job_1533789743141_0046

Moving data to local directory /home/yinzhengjie/download/sortby-result

MapReduce Jobs Launched:

Stage-Stage-: Map:   Reduce:    Cumulative CPU: 8.78 sec   HDFS Read:  HDFS Write:  SUCCESS

Total MapReduce CPU Time Spent:  seconds  msec

OK

emp.empno    emp.ename    emp.job    emp.mgr    emp.hiredate    emp.sal    emp.comm    emp.deptno

Time taken: 86.59 seconds

hive (yinzhengjie)>

排序-Cluster By(hive (yinzhengjie)> select * from emp cluster by deptno;)

    当distribute by和sorts by字段相同时，可以使用cluster by方式。

        cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是倒序排序，不能指定排序规则为ASC或者DESC。

    我们可以看以下两个案例，以下两种写法等价:

hive (yinzhengjie)> select * from emp cluster by deptno;

WARNING: Hive-on-MR is deprecated in Hive  and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive .X releases.

Query ID = yinzhengjie_20180810004115_0faf59ba-950a-4f86-885a-00865338c95c

Total jobs =

Launching Job  out of

Number of reduce tasks not specified. Defaulting to jobconf value of:

In order to change the average load for a reducer (in bytes):

  set hive.exec.reducers.bytes.per.reducer=<number>

In order to limit the maximum number of reducers:

  set hive.exec.reducers.max=<number>

In order to set a constant number of reducers:

  set mapreduce.job.reduces=<number>

Starting Job = job_1533789743141_0047, Tracking URL = http://s101:8088/proxy/application_1533789743141_0047/

Kill Command = /soft/hadoop-2.7./bin/hadoop job  -kill job_1533789743141_0047

Hadoop job information for Stage-: number of mappers: ; number of reducers:

-- ::, Stage- map = %,  reduce = %

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 1.21 sec

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 3.64 sec

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 5.93 sec

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 8.2 sec

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 8.97 sec

MapReduce Total cumulative CPU time:  seconds  msec

Ended Job = job_1533789743141_0047

MapReduce Jobs Launched:

Stage-Stage-: Map:   Reduce:    Cumulative CPU: 8.97 sec   HDFS Read:  HDFS Write:  SUCCESS

Total MapReduce CPU Time Spent:  seconds  msec

OK

emp.empno    emp.ename    emp.job    emp.mgr    emp.hiredate    emp.sal    emp.comm    emp.deptno

    ALLEN    SALESMAN        --    1600.0    300.0

    TURNER    SALESMAN        --    1500.0    0.0

    WARD    SALESMAN        --    1250.0    500.0

    JAMES    CLERK        --    950.0    NULL

    TURNER    SALESMAN        --    1500.0    0.0

    ALLEN    SALESMAN        --    1600.0    300.0

    MARTIN    SALESMAN        --    1250.0    1400.0

    JAMES    CLERK        --    950.0    NULL

    BLAKE    MANAGER        --    2850.0    NULL

    MARTIN    SALESMAN        --    1250.0    1400.0

    BLAKE    MANAGER        --    2850.0    NULL

    WARD    SALESMAN        --    1250.0    500.0

    MILLER    CLERK        --    1300.0    NULL

    KING    PRESIDENT    NULL    --    5000.0    NULL

    CLARK    MANAGER        --    2450.0    NULL

    MILLER    CLERK        --    1300.0    NULL

    KING    PRESIDENT    NULL    --    5000.0    NULL

    CLARK    MANAGER        --    2450.0    NULL

    SMITH    CLERK        --    800.0    NULL

    FORD    ANALYST        --    3000.0    NULL

    SCOTT    ANALYST        --    3000.0    NULL

    SMITH    CLERK        --    800.0    NULL

    JONES    MANAGER        --    2975.0    NULL

    SCOTT    ANALYST        --    3000.0    NULL

    JONES    MANAGER        --    2975.0    NULL

    ADAMS    CLERK        --    1100.0    NULL

    FORD    ANALYST        --    3000.0    NULL

    ADAMS    CLERK        --    1100.0    NULL

Time taken: 64.632 seconds, Fetched:  row(s)

hive (yinzhengjie)> select * from emp distribute by deptno sort by deptno;

WARNING: Hive-on-MR is deprecated in Hive  and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive .X releases.

Query ID = yinzhengjie_20180810004343_d5ce078f-80a7--8a00-a75b6a97f7b2

Total jobs =

Launching Job  out of

Number of reduce tasks not specified. Defaulting to jobconf value of:

In order to change the average load for a reducer (in bytes):

  set hive.exec.reducers.bytes.per.reducer=<number>

In order to limit the maximum number of reducers:

  set hive.exec.reducers.max=<number>

In order to set a constant number of reducers:

  set mapreduce.job.reduces=<number>

Starting Job = job_1533789743141_0048, Tracking URL = http://s101:8088/proxy/application_1533789743141_0048/

Kill Command = /soft/hadoop-2.7./bin/hadoop job  -kill job_1533789743141_0048

Hadoop job information for Stage-: number of mappers: ; number of reducers:

-- ::, Stage- map = %,  reduce = %

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 1.51 sec

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 4.62 sec

-- ::, Stage- map = %,  reduce = %, Cumulative CPU 10.22 sec

MapReduce Total cumulative CPU time:  seconds  msec

Ended Job = job_1533789743141_0048

MapReduce Jobs Launched:

Stage-Stage-: Map:   Reduce:    Cumulative CPU: 10.22 sec   HDFS Read:  HDFS Write:  SUCCESS

Total MapReduce CPU Time Spent:  seconds  msec

OK

emp.empno    emp.ename    emp.job    emp.mgr    emp.hiredate    emp.sal    emp.comm    emp.deptno

    ALLEN    SALESMAN        --    1600.0    300.0

    TURNER    SALESMAN        --    1500.0    0.0

    WARD    SALESMAN        --    1250.0    500.0

    JAMES    CLERK        --    950.0    NULL

    TURNER    SALESMAN        --    1500.0    0.0

    ALLEN    SALESMAN        --    1600.0    300.0

    MARTIN    SALESMAN        --    1250.0    1400.0

    JAMES    CLERK        --    950.0    NULL

    BLAKE    MANAGER        --    2850.0    NULL

    MARTIN    SALESMAN        --    1250.0    1400.0

    BLAKE    MANAGER        --    2850.0    NULL

    WARD    SALESMAN        --    1250.0    500.0

    MILLER    CLERK        --    1300.0    NULL

    KING    PRESIDENT    NULL    --    5000.0    NULL

    CLARK    MANAGER        --    2450.0    NULL

    MILLER    CLERK        --    1300.0    NULL

    KING    PRESIDENT    NULL    --    5000.0    NULL

    CLARK    MANAGER        --    2450.0    NULL

    SMITH    CLERK        --    800.0    NULL

    FORD    ANALYST        --    3000.0    NULL

    SCOTT    ANALYST        --    3000.0    NULL

    SMITH    CLERK        --    800.0    NULL

    JONES    MANAGER        --    2975.0    NULL

    SCOTT    ANALYST        --    3000.0    NULL

    JONES    MANAGER        --    2975.0    NULL

    ADAMS    CLERK        --    1100.0    NULL

    FORD    ANALYST        --    3000.0    NULL

    ADAMS    CLERK        --    1100.0    NULL

Time taken: 48.312 seconds, Fetched:  row(s)

hive (yinzhengjie)> 

分桶表-分桶抽样查询(hive (yinzhengjie)>  select * from stu_buck tablesample(bucket  out of  on id);)

    对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。Hive可以通过对表进行抽样来满足这个需求。

hive (yinzhengjie)> select * from stu_buck;

OK

stu_buck.id    stu_buck.name

    ss16

    ss12

    ss8

    ss4

    ss1

    ss13

    ss5

    ss9

    ss14

    ss10

    ss6

    ss2

    ss15

    ss7

    ss3

    ss11

Time taken: 0.073 seconds, Fetched:  row(s)

hive (yinzhengjie)>  select * from stu_buck tablesample(bucket  out of  on id);        #查询表stu_buck中的数据。

OK

stu_buck.id    stu_buck.name

    ss16

    ss12

    ss8

    ss4

Time taken: 0.088 seconds, Fetched:  row(s)

hive (yinzhengjie)> 

温馨提示：tablesample是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y) 。

    y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例。例如，table总共分了4份，当y=2时，抽取(/=)2个bucket的数据，当y=8时，抽取(/=)/2个bucket的数据。

    x表示从哪个bucket开始抽取。例如，table总bucket数为4，tablesample(bucket  out of )，表示总共抽取（/=）1个bucket的数据，抽取第4个bucket的数据。

    注意：x的值必须小于等于y的值，否则会抛异常，FAILED: SemanticException [Error ]: Numerator should not be bigger than denominator in sample clause for table stu_buck

分桶表-数据块抽样(hive (yinzhengjie)> select * from stu_buck tablesample(0.1 percent);)

    Hive提供了另外一种按照百分比进行抽样的方式，这种是基于行数的，按照输入路径下的数据块百分比进行的抽样。

温馨提示：

    这种抽样方式不一定适用于所有的文件格式。另外，这种抽样的最小抽样单元是一个HDFS数据块。因此，如果表的数据大小小于普通的块大小128M的话，那么将会返回所有行。

hive (yinzhengjie)> select * from stu_buck;

OK

stu_buck.id    stu_buck.name

    ss16

    ss12

    ss8

    ss4

    ss1

    ss13

    ss5

    ss9

    ss14

    ss10

    ss6

    ss2

    ss15

    ss7

    ss3

    ss11

Time taken: 0.078 seconds, Fetched:  row(s)

hive (yinzhengjie)> select * from stu_buck tablesample(0.1 percent) ;            #注意，stu_buck是一个4和桶的桶表，因此他不会把桶表的数据都查询出来，因为它是从四个桶中随机抽取的一个桶的数据

OK

stu_buck.id    stu_buck.name

    ss16

    ss12

    ss8

    ss4

Time taken: 0.04 seconds, Fetched:  row(s)

hive (yinzhengjie)> select * from stu tablesample(0.1 percent) ;

OK

stu.id    stu.name

    ss1

    ss2

    ss3

    ss4

    ss5

    ss6

    ss7

    ss8

    ss9

    ss10

    ss11

    ss12

    ss13

    ss14

    ss15

    ss16

Time taken: 0.059 seconds, Fetched:  row(s)

hive (yinzhengjie)>