hive调优fetch模式抓取，join表测试空key自动转换random，mapreduce动态分区设置

最新推荐文章于 2023-07-21 11:30:00 发布
最新推荐文章于 2023-07-21 11:30:00 发布
阅读量331 收藏
点赞数
分类专栏： hadoop zookeeper hive 文章标签： 数据库 hive hadoop mysql hdfs
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_37056888/article/details/106652954
版权
hadoop 同时被 3 个专栏收录
40 篇文章 0 订阅
订阅专栏
zookeeper
23 篇文章 0 订阅
订阅专栏
hive
15 篇文章 0 订阅
订阅专栏

1.fetch配置




 
  
  [cevent@hadoop207 ~]$ cd /opt/module/hive-1.2.1/
  [cevent@hadoop207 hive-1.2.1]$ ll
  总用量 524
  drwxrwxr-x. 3 cevent cevent   4096 4月  30 15:59 bin
  drwxrwxr-x. 2 cevent cevent   4096 5月   9 18:40 conf
  -rw-rw-r--. 1 cevent cevent  20403 6月   5 13:48 derby.log
  drwxrwxr-x. 4 cevent cevent   4096 4月  30 15:59 examples
  drwxrwxr-x. 7 cevent cevent   4096 4月  30 15:59 hcatalog
  -rw-rw-r--. 1 cevent cevent     23 5月   9 13:37 hive01.sql
  drwxrwxr-x. 4 cevent cevent   4096 5月   7 13:51 lib
  -rw-rw-r--. 1 cevent cevent  24754 4月  30 2015 LICENSE
  drwxrwxr-x. 2 cevent cevent   4096 6月   7 13:22 logs
  drwxrwxr-x. 5 cevent cevent   4096 4月  30 16:44 metastore_db
  -rw-rw-r--. 1 cevent cevent    397 6月  19 2015 NOTICE
  -rw-rw-r--. 1 cevent cevent   4366 6月  19 2015 README.txt
  -rw-rw-r--. 1 cevent cevent 421129 6月  19 2015 RELEASE_NOTES.txt
  -rw-rw-r--. 1 cevent cevent     11 5月   9 13:27 result.txt
  drwxrwxr-x. 3 cevent cevent   4096 4月  30 15:59 scripts
  -rw-rw-r--. 1 cevent cevent    171 5月   9 13:24 server.log
  -rw-rw-r--. 1 cevent cevent      5 5月   8 14:05 server.pid
  -rw-rw-r--. 1 cevent cevent    326 5月  22 13:34 student.txt
  [cevent@hadoop207 hive-1.2.1]$ bin/beeline 
  Beeline version 1.2.1 by Apache Hive
  beeline> !connect
  jdbc:hive2://hadoop207.cevent.com:10000
  Connecting to jdbc:hive2://hadoop207.cevent.com:10000
  Enter username for
  jdbc:hive2://hadoop207.cevent.com:10000: cevent
  Enter password for
  jdbc:hive2://hadoop207.cevent.com:10000: ******
  Connected to: Apache Hive (version 1.2.1)
  Driver: Hive JDBC (version 1.2.1)
  Transaction isolation:
  TRANSACTION_REPEATABLE_READ
  0: jdbc:hive2://hadoop207.cevent.com:10000>
  show databases;
  +----------------+--+
  | database_name  |
  +----------------+--+
  | cevent01       |
  | default        |
  +----------------+--+
  2 rows selected (1.695 seconds)
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> use
  cevent01;
  No rows affected (0.073 seconds)
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> 
  set
  hive.fetch.task.conversion=none; 将fetch转换为none，开启mapreduce模式
  No rows affected (0.03 seconds)
  0: jdbc:hive2://hadoop207.cevent.com:10000>
  select * from c_emp;
  INFO 
  : Number of reduce tasks is set to 0 since there's no reduce operator
  INFO 
  : number of splits:1
  INFO 
  : Submitting tokens for job: job_1591539871344_0001
  INFO 
  : The url to track the job: http://hadoop207.cevent.com:8088/proxy/application_1591539871344_0001/
  INFO 
  : Starting Job = job_1591539871344_0001, Tracking URL =
  http://hadoop207.cevent.com:8088/proxy/application_1591539871344_0001/
  INFO 
  : Kill Command = /opt/module/hadoop-2.7.2/bin/hadoop job  -kill job_1591539871344_0001
  INFO 
  : Hadoop job information for Stage-1: number of mappers: 1; number of
  reducers: 0
  INFO 
  : 2020-06-07 22:30:41,881 Stage-1 map = 0%,  reduce = 0%
  INFO 
  : 2020-06-07 22:30:53,412 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.11 sec
  INFO 
  : MapReduce Total cumulative CPU time: 1 seconds 110 msec
  INFO 
  : Ended Job = job_1591539871344_0001
  +--------------+--------------+-------------+----------------+-----------------+---------------+----------------+--------------+--+
  | c_emp.empno  |
  c_emp.ename  | c_emp.jobs  | c_emp.manager  | c_emp.hiredata  | c_emp.salary  | c_emp.comment  | c_emp.depno  |
  +--------------+--------------+-------------+----------------+-----------------+---------------+----------------+--------------+--+
  | NULL         | cevent       | NULL        | NULL           | NULL            | NULL          | NULL           | 619          |
  | 7369         | SMITH        | CLERK       | 7902           | 1980-12-17      | 800.0         | NULL           | 20           |
  | 7499         | ALLEN        | SALESMAN    | 7698           | 1981-2-20       | 1600.0        | 300.0          | 30           |
  | 7521         | WARD         | SALESMAN    | 7698           | 1981-2-22       | 1250.0        | 500.0          | 30           |
  | 7566         | JONES        | MANAGER     | 7839           | 1981-4-2        | 2975.0        | NULL           | 20           |
  | 7654         | MARTIN       | SALESMAN    | 7698           | 1981-9-28       | 1250.0        | 1400.0         | 30           |
  | 7698         | BLAKE        | MANAGER     | 7839           | 1981-5-1        | 2850.0        | NULL           | 30           |
  | 7782         | CLARK        | MANAGER     | 7839           | 1981-6-9        | 2450.0        | NULL           | 10           |
  | 7788         | SCOTT        | ANALYST     | 7566           | 1987-4-19       | 3000.0        | NULL           | 20           |
  | 7839         | KING         | PRESIDENT   | NULL           | 1981-11-17      | 5000.0        | NULL           | 10           |
  | 7844         | TURNER       | SALESMAN    | 7698           | 1981-9-8        | 1500.0        | 0.0            | 30           |
  | 7876         | ADAMS        | CLERK       | 7788           | 1987-5-23       | 1100.0        | NULL           | 20           |
  | 7900         | JAMES        | CLERK       | 7698           | 1981-12-3       | 950.0         | NULL           | 30           |
  | 7902         | FORD         | ANALYST     | 7566           | 1981-12-3       | 3000.0        | NULL           | 20           |
  | 7934         | MILLER       | CLERK       | 7782           | 1982-1-23       | 1300.0        | NULL           | 10           |
  +--------------+--------------+-------------+----------------+-----------------+---------------+----------------+--------------+--+
  15 rows selected (30.546 seconds)
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> set
  hive.fetch.task.conversion=more;
   将fetch模式设置为more不开启mapreduce，效率更高
  No rows affected (0.006 seconds)
  0: jdbc:hive2://hadoop207.cevent.com:10000>
  select * from c_emp;
  +--------------+--------------+-------------+----------------+-----------------+---------------+----------------+--------------+--+
  | c_emp.empno  |
  c_emp.ename  | c_emp.jobs  | c_emp.manager  | c_emp.hiredata  | c_emp.salary  | c_emp.comment  | c_emp.depno  |
  +--------------+--------------+-------------+----------------+-----------------+---------------+----------------+--------------+--+
  | NULL         | cevent       | NULL        | NULL           | NULL            | NULL          | NULL           | 619          |
  | 7369         | SMITH        | CLERK       | 7902           | 1980-12-17      | 800.0         | NULL           | 20           |
  | 7499         | ALLEN        | SALESMAN    | 7698           | 1981-2-20       | 1600.0        | 300.0          | 30           |
  | 7521         | WARD         | SALESMAN    | 7698           | 1981-2-22       | 1250.0        | 500.0          | 30           |
  | 7566         | JONES        | MANAGER     | 7839           | 1981-4-2        | 2975.0        | NULL           |
  20           |
  | 7654         | MARTIN       | SALESMAN    | 7698           | 1981-9-28       | 1250.0        | 1400.0         | 30           |
  | 7698         | BLAKE        | MANAGER     | 7839           | 1981-5-1        | 2850.0        | NULL           | 30           |
  | 7782         | CLARK        | MANAGER     | 7839           | 1981-6-9        | 2450.0        | NULL           | 10           |
  | 7788         | SCOTT        | ANALYST     | 7566           | 1987-4-19       | 3000.0        | NULL           | 20           |
  | 7839         | KING         | PRESIDENT   | NULL           | 1981-11-17      | 5000.0        | NULL           | 10           |
  | 7844         | TURNER       | SALESMAN    | 7698           | 1981-9-8        | 1500.0        | 0.0 
            | 30           |
  | 7876         | ADAMS        | CLERK       | 7788           | 1987-5-23       | 1100.0        | NULL           | 20           |
  | 7900         | JAMES        | CLERK       | 7698           | 1981-12-3       | 950.0         | NULL           | 30           |
  | 7902         | FORD         | ANALYST     | 7566           | 1981-12-3       | 3000.0        | NULL           | 20           |
  | 7934         | MILLER       | CLERK       | 7782           | 1982-1-23       | 1300.0        | NULL           | 10           |
  +--------------+--------------+-------------+----------------+-----------------+---------------+----------------+--------------+--+
  15 rows selected (0.513 seconds)
  0: jdbc:hive2://hadoop207.cevent.com:10000>
  set
  hive.exec.mode.local.auto=true; 开启本地模式，执行mapreduce，但效率较fetch none更高，谨慎使用本地模式
  No rows affected (0.005 seconds)
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> select count(*) from c_emp;
  INFO 
  : Number of reduce tasks determined at compile time: 1
  INFO 
  : In order to change the average load for a reducer (in bytes):
  INFO 
  :   set
  hive.exec.reducers.bytes.per.reducer=<number>
  INFO 
  : In order to limit the maximum number of reducers:
  INFO 
  :   set
  hive.exec.reducers.max=<number>
  INFO 
  : In order to set a constant number of reducers:
  INFO 
  :   set
  mapreduce.job.reduces=<number>
  INFO 
  : number of splits:1
  INFO 
  : Submitting tokens for job: job_local1010738823_0001
  INFO 
  : The url to track the job: http://localhost:8080/
  INFO 
  : Job running in-process (local Hadoop)
  INFO 
  : 2020-06-07 22:33:37,811 Stage-1 map = 100%,  reduce = 100%
  INFO 
  : Ended Job = job_local1010738823_0001
  +------+--+
  | _c0 
  |
  +------+--+
  | 15  
  |
  +------+--+
  1 row selected (1.613 seconds)

2.元数据准备

表元数据
表元数据2

3.修改mapreduce默认hive开启的数量




 
  
  [cevent@hadoop207 module]$ cd hive-1.2.1/
  [cevent@hadoop207 hive-1.2.1]$ cd conf/
  [cevent@hadoop207 conf]$ ll
  总用量 192
  -rw-rw-r--. 1 cevent cevent   1139 4月  30 2015
  beeline-log4j.properties.template
  -rw-rw-r--. 1 cevent cevent 168431 6月  19 2015 hive-default.xml.template
  -rw-rw-r--. 1 cevent cevent   2464 4月  30 16:06 hive-env.sh
  -rw-rw-r--. 1 cevent cevent   2662 4月  30 2015
  hive-exec-log4j.properties.template
  -rw-rw-r--. 1 cevent cevent   3093 5月   9 18:12
  hive-log4j.properties
  -rw-rw-r--. 1 cevent cevent   1354 5月   9 18:40 hive-site.xml
  -rw-rw-r--. 1 cevent cevent   1593 4月  30 2015 ivysettings.xml
  [cevent@hadoop207 conf]$ cat hive-site.xml  查看hive配置
  <?xml version="1.0"?>
  <?xml-stylesheet
  type="text/xsl" href="configuration.xsl"?>
   
  <configuration>
   
         
  <!--mySQL数据库地址-->
         
  <property>
           
  <name>javax.jdo.option.ConnectionURL</name>
           
  <value>jdbc:mysql://hadoop207.cevent.com:3306/metastore?createDatabaseIfNotExist=true</value>
           
  <description>JDBC connect string for a JDBC
  metastore</description>
         
  </property>
   
         
  <property>
           
  <name>javax.jdo.option.ConnectionDriverName</name>
           
  <value>com.mysql.jdbc.Driver</value>
           
  <description>Driver class name for a JDBC
  metastore</description>
         
  </property>
   
         
  <!--mySQL数据库访问用户名及密码-->
   
         
  <property>
           
  <name>javax.jdo.option.ConnectionUserName</name>
           
  <value>root</value>
           
  <description>username to use against metastore
  database</description>
         
  </property>
   
         
  <property>
           
  <name>javax.jdo.option.ConnectionPassword</name>
           
  <value>cevent</value>
            <description>password to use
  against metastore database</description>
         
  </property>
   
         
  <!-- 自定义hive查询显示的信息  -->
         
  <property>
           
  <name>hive.cli.print.header</name>
           
  <value>true</value>
         
  </property>
   
         
  <property>
           
  <property>
           
  <name>hive.cli.print.current.db</name>
           
  <value>true</value>
         
  
         
  <!--修改默认的reduce生成数量 ，去掉-->
          </property>
            <name>mapreduce.job.reduce</name>
           
  <value>4</value>
          </property>
   
   
  </configuration>
   
   
  [cevent@hadoop207 conf]$ vim hive-site.xml 修改配置
         
  </property>
           
  <value>4</value>
           
  <name>mapreduce.job.reduce</name>
         
  </property>
  <?xml version="1.0"?>
  <?xml-stylesheet
  type="text/xsl" href="configuration.xsl"?>
   
  <configuration>
   
         
  <!--mySQL数据库地址-->
         
  <property>
           
  <name>javax.jdo.option.ConnectionURL</name>
           
  <value>jdbc:mysql://hadoop207.cevent.com:3306/metastore?createDatabaseIfNotExist=true</value>
           
  <description>JDBC connect string for a JDBC
  metastore</description>
         
  </property>
   
         
  <property>
           
  <name>javax.jdo.option.ConnectionDriverName</name>
           
  <value>com.mysql.jdbc.Driver</value>
           
  <description>Driver class name for a JDBC
  metastore</description>
      
     </property>
   
         
  <!--mySQL数据库访问用户名及密码-->
   
         
  <property>
           
  <name>javax.jdo.option.ConnectionUserName</name>
           
  <value>root</value>
           
  <description>username to use against metastore database</description>
         
  </property>
   
         
  <property>
           
  <name>javax.jdo.option.ConnectionPassword</name>
           
  <value>cevent</value>
           
  <description>password to use against metastore
  database</description>
         
  </property>
   
         
  <!-- 自定义hive查询显示的信息  -->
         
  <property>
           
  <name>hive.cli.print.header</name>
           
  <value>true</value>
         
  </property>
   
         
  <property>
           
  <property>
           
  <name>hive.cli.print.current.db</name>
           
  <value>true</value>
   
         
  <!--修改默认的reduce生成数量-->
   
   
  </configuration>

4.插入数据




 
  
  0: jdbc:hive2://hadoop207.cevent.com:10000>
  create table big_join(
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> id
  bigint,
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> time
  bigint,
  0: jdbc:hive2://hadoop207.cevent.com:10000> uid string,
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> keyword
  string,
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> url_rank
  int,
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> click_num
  int,
  0: jdbc:hive2://hadoop207.cevent.com:10000> click_url string
  0: jdbc:hive2://hadoop207.cevent.com:10000> )
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> row
  format delimited fields terminated by '\t'; 
  No rows affected (0.467 seconds)
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> create
  table small_join(
  0: jdbc:hive2://hadoop207.cevent.com:10000> id bigint,
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> time
  bigint,
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> uid
  string,
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> keyword
  string,
  0: jdbc:hive2://hadoop207.cevent.com:10000>
  url_rank int,
  0: jdbc:hive2://hadoop207.cevent.com:10000>
  click_num int,
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> click_url
  string
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> )
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> row
  format delimited fields terminated by '\t'; 
  No rows affected (0.1 seconds)
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> create
  table join_table(
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> id
  bigint,
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> time
  bigint,
  0: jdbc:hive2://hadoop207.cevent.com:10000> uid string,
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> keyword
  string,
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> url_rank
  int,
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> click_num
  int,
  0: jdbc:hive2://hadoop207.cevent.com:10000>
  click_url string
  0: jdbc:hive2://hadoop207.cevent.com:10000>
  )
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> row
  format delimited fields terminated by '\t'; 
  No rows affected (0.1 seconds)
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> load data
  local inpath '/opt/module/datas/bigtable' into table big_join;
  INFO 
  : Loading data to table cevent01.big_join from
  file:/opt/module/datas/bigtable
  INFO 
  : Table cevent01.big_join stats: [numFiles=1, totalSize=120734753]
  No rows affected (14.891 seconds)
  0: jdbc:hive2://hadoop207.cevent.com:10000> load data local inpath '/opt/module/datas/smalltable' into
  table small_join;
  INFO 
  : Loading data to table cevent01.small_join from
  file:/opt/module/datas/smalltable
  INFO 
  : Table cevent01.small_join stats: [numFiles=1, totalSize=12018355]
  No rows affected (1.723 seconds)
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> select *
  from big_join limit 5;
  +--------------+-----------------+-----------------------------------+-------------------+--------------------+---------------------+----------------------------------------------------------------+--+
  | big_join.id  |  big_join.time  |          
  big_join.uid            |
  big_join.keyword  |
  big_join.url_rank  |
  big_join.click_num  |                       big_join.click_url                       |
  +--------------+-----------------+-----------------------------------+-------------------+--------------------+---------------------+----------------------------------------------------------------+--+
  | 0            | 20111230000005  | 57375476989eea12893c0c3811607bcf  | 奇艺高清              | 1                  | 1                   |
  http://www.qiyi.com/                                           |
  | 0            |
  20111230000005  |
  66c5bb7774e31d0a22278249b26bc83a  | 凡人修仙传             | 3                  | 1                   |
  http://www.booksky.org/BookDetail.aspx?BookID=1050804&Level=1  |
  | 0            |
  20111230000007  |
  b97920521c78de70ac38e3713f524b50  | 本本联盟              | 1                  | 1                   | http://www.bblianmeng.com/                                     |
  | 0            |
  20111230000008  |
  6961d0c97fe93701fc9c0d861d096cd9  | 华南师范大学图书馆         | 1                  | 1                   |
  http://lib.scnu.edu.cn/                                        |
  | 0            | 20111230000008  | f2f5a21c764aebde1e8afcc2871e086f  | 在线代理              | 2                  | 1                   | http://proxyie.cn/                                             |
  +--------------+-----------------+-----------------------------------+-------------------+--------------------+---------------------+----------------------------------------------------------------+--+
  5 rows selected (0.613 seconds)
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> select *
  from small_join limit 5;
  +----------------+------------------+-----------------------------------+---------------------+----------------------+-----------------------+----------------------------------------------------------------+--+
  | small_join.id  |
  small_join.time  |          small_join.uid           | small_join.keyword  | small_join.url_rank  | small_join.click_num  |                      small_join.click_url                      |
  +----------------+------------------+-----------------------------------+---------------------+----------------------+-----------------------+----------------------------------------------------------------+--+
  | 95048          |
  20111230000005   | 57375476989eea12893c0c3811607bcf  | 奇艺高清                | 1                    | 1                     | http://www.qiyi.com/                                           |
  | 95096          |
  20111230000005   |
  66c5bb7774e31d0a22278249b26bc83a  | 凡人修仙传               | 3                    | 1                     |
  http://www.booksky.org/BookDetail.aspx?BookID=1050804&Level=1  |
  | 95144          |
  20111230000007   |
  b97920521c78de70ac38e3713f524b50  | 本本联盟                | 1                    | 1                     | http://www.bblianmeng.com/                                     |
  | 95192          | 20111230000008   | 6961d0c97fe93701fc9c0d861d096cd9  | 华南师范大学图书馆           | 1                    | 1                     |
  http://lib.scnu.edu.cn/                                        |
  | 95192          |
  20111230000008   |
  f2f5a21c764aebde1e8afcc2871e086f  | 在线代理                | 2                    | 1                     |
  http://proxyie.cn/                                            
  |
  +----------------+------------------+-----------------------------------+---------------------+----------------------+-----------------------+----------------------------------------------------------------+--+
  5 rows selected (0.118 seconds)
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> set
  hive.auto.convert.join=false; //将mapjoin打开效率更高，true
  No rows affected (0.017 seconds)
  0: jdbc:hive2://hadoop207.cevent.com:10000>
  insert overwrite table join_table
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> select
  b.id,b.time,b.uid,b.keyword,b.url_rank,b.click_num,b.click_url
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> from
  small_join s
  0: jdbc:hive2://hadoop207.cevent.com:10000> join big_join b
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> on
  b.id=s.id;
  INFO 
  : Number of reduce tasks not specified. Estimated from input data
  size: 1
  INFO 
  : In order to change the average load for a reducer (in bytes):
  INFO 
  :   set hive.exec.reducers.bytes.per.reducer=<number>
  INFO 
  : In order to limit the maximum number of reducers:
  INFO 
  :   set
  hive.exec.reducers.max=<number>
  INFO 
  : In order to set a constant number of reducers:
  INFO 
  :   set mapreduce.job.reduces=<number>
  INFO 
  : number of splits:2
  INFO 
  : Submitting tokens for job: job_1591593753790_0001
  INFO 
  : The url to track the job:
  http://hadoop207.cevent.com:8088/proxy/application_1591593753790_0001/
  INFO 
  : Starting Job = job_1591593753790_0001, Tracking URL =
  http://hadoop207.cevent.com:8088/proxy/application_1591593753790_0001/
  INFO 
  : Kill Command = /opt/module/hadoop-2.7.2/bin/hadoop job  -kill job_1591593753790_0001
  INFO 
  : Hadoop job information for Stage-1: number of mappers: 2; number of
  reducers: 1
  INFO 
  : 2020-06-08 13:41:29,289 Stage-1 map = 0%,  reduce = 0%
  INFO 
  : 2020-06-08 13:42:12,682 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 13.84 sec
  INFO 
  : 2020-06-08 13:42:33,048 Stage-1 map = 100%,  reduce = 99%, Cumulative CPU 17.94 sec
  INFO 
  : 2020-06-08 13:42:34,442 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 23.18 sec
  INFO 
  : MapReduce Total cumulative CPU time: 23 seconds 180 msec
  INFO 
  : Ended Job = job_1591593753790_0001
  INFO 
  : Loading data to table cevent01.join_table from hdfs://hadoop207.cevent.com:8020/user/hive/warehouse/cevent01.db/join_table/.hive-staging_hive_2020-06-08_13-41-10_494_1851985601125007593-1/-ext-10000
  INFO 
  : Table cevent01.join_table stats: [numFiles=1, numRows=999990,
  totalSize=123817216, rawDataSize=122817226]
  No rows affected (95.171 seconds)

5.SQL解析




 
  
  //hive表优化
  //1.join
  //创建大表
  create table big_join(
  id bigint,
  time bigint,
  uid string,
  keyword string,
  url_rank int,
  click_num int,
  click_url string
  )
  row format delimited fields terminated by
  '\t';
   
  //创建小表
  create table small_join(
  id bigint,
  time bigint,
  uid string,
  keyword string,
  url_rank int,
  click_num int,
  click_url string
  )
  row format delimited fields terminated by
  '\t';
   
  //创建join后表的语句
  create table join_table(
  id bigint,
  time bigint,
  uid string,
  keyword string,
  url_rank int,
  click_num int,
  click_url string
  )
  row format delimited fields terminated by
  '\t';
   
  //加载数据
  load data local inpath
  '/opt/module/datas/bigtable' into table big_join;
  load data local inpath '/opt/module/datas/smalltable'
  into table small_join;
   
  //关闭hive.auto.convert.join=false;关闭map join
  //关闭本地模式，影响效率：set hive.exec.mode.local.auto=false;
  //执行查询插入,小表插入大表
  insert overwrite table join_table
  select b.id,b.time,b.uid,b.keyword,b.url_rank,b.click_num,b.click_url
  from small_join s
  join big_join b
  on b.id=s.id;
  //执行查询插入,大表插入小表
  insert overwrite table join_table
  select
  b.id,b.time,b.uid,b.keyword,b.url_rank,b.click_num,b.click_url
  from big_join b
  join small_join s
  on s.id=b.id;

6.准备原始数据ori

ori1
ori2

7.执行插入




 
  
  [cevent@hadoop207 hive-1.2.1]$ bin/beeline 
  Beeline version 1.2.1 by Apache Hive
  beeline>
  !connect jdbc:hive2://hadoop207.cevent.com:10000
  Connecting to jdbc:hive2://hadoop207.cevent.com:10000
  Enter username for
  jdbc:hive2://hadoop207.cevent.com:10000: cevent
  Enter password for jdbc:hive2://hadoop207.cevent.com:10000:
  ******
  Connected to: Apache Hive (version 1.2.1)
  Driver: Hive JDBC (version 1.2.1)
  Transaction isolation:
  TRANSACTION_REPEATABLE_READ
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> show
  databases;
  +----------------+--+
  | database_name  |
  +----------------+--+
  | cevent01       |
  | default        |
  +----------------+--+
  2 rows selected (1.77 seconds)
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> use
  cevent01;
  No rows affected (0.053 seconds)   插入join表数据
  0: jdbc:hive2://hadoop207.cevent.com:10000> insert overwrite table join_table
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> select
  b.id,b.time,b.uid,b.keyword,b.url_rank,b.click_num,b.click_url
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> from
  small_join s
  0: jdbc:hive2://hadoop207.cevent.com:10000> left join big_join b
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> on
  b.id=s.id;
  INFO  : Stage-1 is selected by
  condition resolver.
  INFO  : Number of reduce tasks
  not specified. Estimated from input data size: 1
  INFO  : In order to change the
  average load for a reducer (in bytes):
  INFO  :   set
  hive.exec.reducers.bytes.per.reducer=<number>
  INFO  : In order to limit the
  maximum number of reducers:
  INFO  :   set hive.exec.reducers.max=<number>
  INFO  : In order to set a
  constant number of reducers:
  INFO  :   set mapreduce.job.reduces=<number>
  INFO  : number of splits:2
  INFO  : Submitting tokens for
  job: job_1591675698957_0001
  INFO  : The url to track the
  job: http://hadoop207.cevent.com:8088/proxy/application_1591675698957_0001/
  INFO  : Starting Job = job_1591675698957_0001,
  Tracking URL =
  http://hadoop207.cevent.com:8088/proxy/application_1591675698957_0001/
  INFO  : Kill Command =
  /opt/module/hadoop-2.7.2/bin/hadoop job 
  -kill job_1591675698957_0001
  INFO  : Hadoop job information
  for Stage-1: number of mappers: 2; number of reducers: 1
  INFO  : 2020-06-09 12:13:16,419
  Stage-1 map = 0%,  reduce = 0%
  INFO  : 2020-06-09 12:13:43,229
  Stage-1 map = 50%,  reduce = 0%,
  Cumulative CPU 5.68 sec
  INFO  : 2020-06-09 12:13:57,087
  Stage-1 map = 100%,  reduce = 0%, Cumulative
  CPU 16.96 sec
  INFO  : 2020-06-09 12:14:12,299
  Stage-1 map = 100%,  reduce = 80%,
  Cumulative CPU 22.19 sec
  INFO  : 2020-06-09 12:14:15,774
  Stage-1 map = 100%,  reduce = 98%,
  Cumulative CPU 25.13 sec
  INFO  : 2020-06-09 12:14:24,366
  Stage-1 map = 100%,  reduce = 100%,
  Cumulative CPU 32.81 sec
  INFO  : MapReduce Total
  cumulative CPU time: 32 seconds 810 msec
  INFO  : Ended Job =
  job_1591675698957_0001
  INFO  : Loading data to table
  cevent01.join_table from hdfs://hadoop207.cevent.com:8020/user/hive/warehouse/cevent01.db/join_table/.hive-staging_hive_2020-06-09_12-12-53_365_6693250539177613475-1/-ext-10000
  INFO  : Table
  cevent01.join_table stats: [numFiles=1, numRows=999990, totalSize=123817216,
  rawDataSize=122817226]
  No rows affected (96.166 seconds)
  0: jdbc:hive2://hadoop207.cevent.com:10000>
  create table join_original(  创建原始数据表
  0: jdbc:hive2://hadoop207.cevent.com:10000> id bigint,
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> time
  bigint,
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> uid
  string,
  0: jdbc:hive2://hadoop207.cevent.com:10000> keyword string,
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> url_rank
  int,
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> click_num
  int,
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> click_url
  string
  0: jdbc:hive2://hadoop207.cevent.com:10000> )
  0: jdbc:hive2://hadoop207.cevent.com:10000>
  row format delimited fields terminated by '\t'; 
  No rows affected (0.608 seconds)
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> create
  table join_original_null_id(  创建空key表
  0: jdbc:hive2://hadoop207.cevent.com:10000>
  id bigint,
  0: jdbc:hive2://hadoop207.cevent.com:10000>
  time bigint,
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> uid
  string,
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> keyword
  string,
  0: jdbc:hive2://hadoop207.cevent.com:10000>
  url_rank int,
  0: jdbc:hive2://hadoop207.cevent.com:10000>
  click_num int,
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> click_url
  string
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> )
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> row
  format delimited fields terminated by '\t'; 
  No rows affected (0.135 seconds)
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> create
  table join_original_table(  创建join表
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> id
  bigint,
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> time
  bigint,
  0: jdbc:hive2://hadoop207.cevent.com:10000> uid string,
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> keyword
  string,
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> url_rank
  int,
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> click_num
  int,
  0: jdbc:hive2://hadoop207.cevent.com:10000>
  click_url string
  0: jdbc:hive2://hadoop207.cevent.com:10000>
  )
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> row
  format delimited fields terminated by '\t'; 
  No rows affected (0.122 seconds)
  0: jdbc:hive2://hadoop207.cevent.com:10000> load data local inpath '/opt/module/datas/ori' into table
  join_original;
  INFO 
  : Loading data to table cevent01.join_original from
  file:/opt/module/datas/ori
  INFO 
  : Table cevent01.join_original stats: [numFiles=1,
  totalSize=121734744]
  No rows affected (6.715 seconds)
  0: jdbc:hive2://hadoop207.cevent.com:10000> load data local inpath '/opt/module/datas/nullid' into
  table join_original_null_id;
  INFO 
  : Loading data to table cevent01.join_original_null_id from
  file:/opt/module/datas/nullid
  INFO 
  : Table cevent01.join_original_null_id stats: [numFiles=1, totalSize=118645854]
  No rows affected (13.959 seconds)    插入join表数据，全插
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> insert
  overwrite table join_original_table 
  0: jdbc:hive2://hadoop207.cevent.com:10000> select n.* from 
  0: jdbc:hive2://hadoop207.cevent.com:10000> join_original_null_id n left join join_original o on
  n.id=o.id;
  INFO  : Stage-1 is selected by
  condition resolver.
  INFO  : Number of reduce tasks
  not specified. Estimated from input data size: 1
  INFO  : In order to change the
  average load for a reducer (in bytes):
  INFO  :   set
  hive.exec.reducers.bytes.per.reducer=<number>
  INFO  : In order to limit the
  maximum number of reducers:
  INFO  :   set hive.exec.reducers.max=<number>
  INFO  : In order to set a
  constant number of reducers:
  INFO  :   set mapreduce.job.reduces=<number>
  INFO  : number of splits:2
  INFO  : Submitting tokens for
  job: job_1591675698957_0002
  INFO  : The url to track the
  job: http://hadoop207.cevent.com:8088/proxy/application_1591675698957_0002/
  INFO  : Starting Job =
  job_1591675698957_0002, Tracking URL =
  http://hadoop207.cevent.com:8088/proxy/application_1591675698957_0002/
  INFO  : Kill Command =
  /opt/module/hadoop-2.7.2/bin/hadoop job 
  -kill job_1591675698957_0002
  INFO  : Hadoop job information
  for Stage-1: number of mappers: 2; number of reducers: 1
  INFO  : 2020-06-09 13:26:11,449
  Stage-1 map = 0%,  reduce = 0%
  INFO  : 2020-06-09 13:27:00,291
  Stage-1 map = 100%,  reduce = 0%,
  Cumulative CPU 16.19 sec
  INFO  : 2020-06-09 13:27:21,791
  Stage-1 map = 100%,  reduce = 69%,
  Cumulative CPU 20.71 sec
  INFO  : 2020-06-09 13:27:25,027
  Stage-1 map = 100%,  reduce = 77%,
  Cumulative CPU 22.42 sec
  INFO  : 2020-06-09 13:27:27,828
  Stage-1 map = 100%,  reduce = 90%,
  Cumulative CPU 22.42 sec
  INFO  : 2020-06-09 13:27:33,724
  Stage-1 map = 100%,  reduce = 100%, Cumulative
  CPU 27.9 sec
  INFO  : MapReduce Total
  cumulative CPU time: 27 seconds 900 msec
  INFO  : Ended Job =
  job_1591675698957_0002
  INFO  : Loading data to table cevent01.join_original_table
  from hdfs://hadoop207.cevent.com:8020/user/hive/warehouse/cevent01.db/join_original_table/.hive-staging_hive_2020-06-09_13-26-01_145_7407125718177019072-1/-ext-10000
  INFO  : Table
  cevent01.join_original_table stats: [numFiles=1, numRows=1000000,
  totalSize=118645854, rawDataSize=117645854]
  No rows affected (97.176 seconds)
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> insert
  overwrite table join_original_table 不为空查询
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> select
  n.* from
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> (select *
  from join_original_null_id where id is not null) 
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> n left
  join join_original o on n.id=o.id;
  INFO  : Stage-1 is selected by
  condition resolver.
  INFO  : Number of reduce tasks
  not specified. Estimated from input data size: 1
  INFO  : In order to change the
  average load for a reducer (in bytes):
  INFO  :   set
  hive.exec.reducers.bytes.per.reducer=<number>
  INFO  : In order to limit the
  maximum number of reducers:
  INFO  :   set hive.exec.reducers.max=<number>
  INFO  : In order to set a constant
  number of reducers:
  INFO  :   set mapreduce.job.reduces=<number>
  INFO  : number of splits:2
  INFO  : Submitting tokens for
  job: job_1591675698957_0003
  INFO  : The url to track the
  job: http://hadoop207.cevent.com:8088/proxy/application_1591675698957_0003/
  INFO  : Starting Job = job_1591675698957_0003,
  Tracking URL = http://hadoop207.cevent.com:8088/proxy/application_1591675698957_0003/
  INFO  : Kill Command =
  /opt/module/hadoop-2.7.2/bin/hadoop job 
  -kill job_1591675698957_0003
  INFO  : Hadoop job information
  for Stage-1: number of mappers: 2; number of reducers: 1
  INFO  : 2020-06-09 13:29:56,277
  Stage-1 map = 0%,  reduce = 0%
  INFO  : 2020-06-09 13:30:25,427
  Stage-1 map = 100%,  reduce = 0%,
  Cumulative CPU 12.49 sec
  INFO  : 2020-06-09 13:30:48,717
  Stage-1 map = 100%,  reduce = 100%, Cumulative
  CPU 19.14 sec
  INFO  : MapReduce Total
  cumulative CPU time: 19 seconds 140 msec
  INFO  : Ended Job =
  job_1591675698957_0003
  INFO  : Loading data to table
  cevent01.join_original_table from hdfs://hadoop207.cevent.com:8020/user/hive/warehouse/cevent01.db/join_original_table/.hive-staging_hive_2020-06-09_13-29-44_221_5080146984779335117-1/-ext-10000
  INFO  : Table
  cevent01.join_original_table stats: [numFiles=1, numRows=200001,
  totalSize=24409561, rawDataSize=24209560]
  No rows affected (67.163 seconds)
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> set
  mapreduce.job.reduces=3; 设置reduce的数量
  No rows affected (0.226 seconds)
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> insert
  overwrite table join_original_table  只匹配有id的
  0: jdbc:hive2://hadoop207.cevent.com:10000> select n.* from
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> join_original_null_id
  n left join join_original o on n.id=o.id;
  INFO  : Stage-1 is selected by
  condition resolver.
  INFO  : Number of reduce tasks
  not specified. Defaulting to jobconf value of: 3
  INFO  : In order to change the
  average load for a reducer (in bytes):
  INFO  :   set
  hive.exec.reducers.bytes.per.reducer=<number>
  INFO  : In order to limit the
  maximum number of reducers:
  INFO  :   set hive.exec.reducers.max=<number>
  INFO  : In order to set a
  constant number of reducers:
  INFO  :   set mapreduce.job.reduces=<number>
  INFO  : number of splits:2
  INFO  : Submitting tokens for
  job: job_1591675698957_0004
  INFO  : The url to track the job:
  http://hadoop207.cevent.com:8088/proxy/application_1591675698957_0004/
  INFO  : Starting Job =
  job_1591675698957_0004, Tracking URL =
  http://hadoop207.cevent.com:8088/proxy/application_1591675698957_0004/
  INFO  : Kill Command =
  /opt/module/hadoop-2.7.2/bin/hadoop job 
  -kill job_1591675698957_0004
  INFO  : Hadoop job information
  for Stage-1: number of mappers: 2; number of reducers: 3
  INFO  : 2020-06-09 13:37:54,461
  Stage-1 map = 0%,  reduce = 0%
  INFO  : 2020-06-09 13:38:20,067
  Stage-1 map = 50%,  reduce = 0%,
  Cumulative CPU 15.17 sec
  INFO  : 2020-06-09 13:38:25,554 Stage-1
  map = 100%,  reduce = 0%, Cumulative
  CPU 18.33 sec
  INFO  : 2020-06-09 13:38:46,507
  Stage-1 map = 100%,  reduce = 50%,
  Cumulative CPU 22.28 sec
  INFO  : 2020-06-09 13:39:12,015
  Stage-1 map = 100%,  reduce = 76%,
  Cumulative CPU 31.73 sec
  INFO  : 2020-06-09 13:39:16,761
  Stage-1 map = 100%,  reduce = 100%,
  Cumulative CPU 43.24 sec
  INFO  : MapReduce Total
  cumulative CPU time: 43 seconds 240 msec
  INFO  : Ended Job = job_1591675698957_0004
  INFO  : Loading data to table
  cevent01.join_original_table from hdfs://hadoop207.cevent.com:8020/user/hive/warehouse/cevent01.db/join_original_table/.hive-staging_hive_2020-06-09_13-37-42_754_4173942603345328108-1/-ext-10000
  INFO  : Table
  cevent01.join_original_table stats: [numFiles=3, numRows=1000000,
  totalSize=118645854, rawDataSize=117645854]
  No rows affected (110.936 seconds)
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> insert
  overwrite table join_original_table 设置空key为随机数
  0: jdbc:hive2://hadoop207.cevent.com:10000>
  select n.* from
  0: jdbc:hive2://hadoop207.cevent.com:10000>
  join_original_null_id n full join join_original o on 
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> case when
  n.id is null
  0: jdbc:hive2://hadoop207.cevent.com:10000>
  then concat('hive',rand())
  0: jdbc:hive2://hadoop207.cevent.com:10000>
  else n.id end=o.id;
  INFO  : Number of reduce tasks
  not specified. Defaulting to jobconf value of: 3
  INFO  : In order to change the
  average load for a reducer (in bytes):
  INFO  :   set
  hive.exec.reducers.bytes.per.reducer=<number>
  INFO  : In order to limit the
  maximum number of reducers:
  INFO  :   set hive.exec.reducers.max=<number>
  INFO  : In order to set a
  constant number of reducers:
  INFO  :   set mapreduce.job.reduces=<number>
  INFO  : number of splits:2
  INFO  : Submitting tokens for
  job: job_1591675698957_0005
  INFO  : The url to track the
  job: http://hadoop207.cevent.com:8088/proxy/application_1591675698957_0005/
  INFO  : Starting Job =
  job_1591675698957_0005, Tracking URL = http://hadoop207.cevent.com:8088/proxy/application_1591675698957_0005/
  INFO  : Kill Command = /opt/module/hadoop-2.7.2/bin/hadoop
  job  -kill job_1591675698957_0005
  INFO  : Hadoop job information
  for Stage-1: number of mappers: 2; number of reducers: 3
  INFO  : 2020-06-09 13:43:57,701
  Stage-1 map = 0%,  reduce = 0%
  INFO  : 2020-06-09 13:44:18,174
  Stage-1 map = 33%,  reduce = 0%,
  Cumulative CPU 12.1 sec
  INFO  : 2020-06-09 13:44:19,621
  Stage-1 map = 50%,  reduce = 0%,
  Cumulative CPU 14.39 sec
  INFO  : 2020-06-09 13:44:26,173
  Stage-1 map = 84%,  reduce = 0%,
  Cumulative CPU 24.9 sec
  INFO  : 2020-06-09 13:44:30,664
  Stage-1 map = 100%,  reduce = 0%,
  Cumulative CPU 26.35 sec
  INFO  : 2020-06-09 13:44:45,291
  Stage-1 map = 100%,  reduce = 28%,
  Cumulative CPU 28.45 sec
  INFO  : 2020-06-09 13:44:52,047
  Stage-1 map = 100%,  reduce = 56%,
  Cumulative CPU 31.54 sec
  INFO  : 2020-06-09 13:45:03,169
  Stage-1 map = 100%,  reduce = 76%,
  Cumulative CPU 37.31 sec
  INFO  : 2020-06-09 13:45:04,818
  Stage-1 map = 100%,  reduce = 89%,
  Cumulative CPU 42.86 sec
  INFO  : 2020-06-09 13:45:12,375
  Stage-1 map = 100%,  reduce = 99%,
  Cumulative CPU 53.09 sec
  INFO  : 2020-06-09 13:45:13,851
  Stage-1 map = 100%,  reduce = 100%,
  Cumulative CPU 53.26 sec
  INFO  : MapReduce Total
  cumulative CPU time: 53 seconds 260 msec
  INFO  : Ended Job =
  job_1591675698957_0005
  INFO  : Loading data to table
  cevent01.join_original_table from
  hdfs://hadoop207.cevent.com:8020/user/hive/warehouse/cevent01.db/join_original_table/.hive-staging_hive_2020-06-09_13-43-42_102_6854456494005290452-1/-ext-10000
  INFO  : Table
  cevent01.join_original_table stats: [numFiles=3, numRows=1799999,
  totalSize=135445833, rawDataSize=133645834]
  No rows affected (97.97 seconds)

8.链接测试

链接测试cluster：http://hadoop207.cevent.com:8088/cluster

cluster1
cluster2

9.动态分区




 
  
  0: jdbc:hive2://hadoop207.cevent.com:10000>
  select b.id from big_join b  查询id<=10的数据
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> join
  (select id from join_original where id<=10) o
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> on
  b.id=o.id;
  INFO 
  : Stage-1 is selected by condition resolver.
  INFO 
  : Number of reduce tasks not specified. Estimated from input data
  size: 1
  INFO 
  : In order to change the average load for a reducer (in bytes):
  INFO 
  :   set
  hive.exec.reducers.bytes.per.reducer=<number>
  INFO 
  : In order to limit the maximum number of reducers:
  INFO  :  
  set hive.exec.reducers.max=<number>
  INFO 
  : In order to set a constant number of reducers:
  INFO 
  :   set mapreduce.job.reduces=<number>
  INFO 
  : number of splits:2
  INFO 
  : Submitting tokens for job: job_1591675698957_0014
  INFO 
  : The url to track the job:
  http://hadoop207.cevent.com:8088/proxy/application_1591675698957_0014/
  INFO 
  : Starting Job = job_1591675698957_0014, Tracking URL =
  http://hadoop207.cevent.com:8088/proxy/application_1591675698957_0014/
  INFO 
  : Kill Command = /opt/module/hadoop-2.7.2/bin/hadoop job  -kill job_1591675698957_0014
  INFO 
  : Hadoop job information for Stage-1: number of mappers: 2; number of
  reducers: 1
  INFO 
  : 2020-06-09 17:00:28,898 Stage-1 map = 0%,  reduce = 0%
  INFO 
  : 2020-06-09 17:00:49,450 Stage-1 map = 50%,  reduce = 0%, Cumulative CPU 6.22 sec
  INFO 
  : 2020-06-09 17:00:51,358 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 10.76 sec
  INFO 
  : 2020-06-09 17:01:04,748 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 14.9 sec
  INFO 
  : MapReduce Total cumulative CPU time: 14 seconds 900 msec
  INFO 
  : Ended Job = job_1591675698957_0014
  +-------+--+
  | b.id  |
  +-------+--+
  | 1     |
  | 1     |
  | 1     |
  | 1     |
  | 1     |
  | 1     |
  | 1     |
  | 1     |
  | 1     |
  | 1     |
  | 2     |
  | 2     |
  | 2     |
  | 2     |
  | 2     |
  | 2     |
  | 2     |
  | 2     |
  | 2     |
  | 2     |
  | 3     |
  | 3     |
  | 3     |
  | 3     |
  | 3     |
  | 3     |
  | 3     |
  | 3     |
  | 3     |
  | 3     |
  | 4     |
  | 4     |
  | 4     |
  | 4     |
  | 4     |
  | 4     |
  | 4     |
  | 4     |
  | 4     |
  | 4     |
  | 5     |
  | 5     |
  | 5     |
  | 5     |
  | 5     |
  | 5     |
  | 5     |
  | 5     |
  | 5     |
  | 5     |
  | 6     |
  | 6     |
  | 6     |
  | 6     |
  | 6     |
  | 6     |
  | 6     |
  | 6     |
  | 6     |
  | 6     |
  | 7     |
  | 7     |
  | 7     |
  | 7     |
  | 7     |
  | 7     |
  | 7     |
  | 7     |
  | 7     |
  | 7     |
  | 8     |
  | 8     |
  | 8     |
  | 8     |
  | 8     |
  | 8     |
  | 8     |
  | 8     |
  | 8     |
  | 8     |
  | 9     |
  | 9     |
  | 9     |
  | 9     |
  | 9     |
  | 9     |
  | 9     |
  | 9     |
  | 9     |
  | 9     |
  | 10    |
  | 10    |
  | 10    |
  | 10    |
  | 10    |
  | 10    |
  | 10    |
  | 10    |
  | 10    |
  | 10    |
  +-------+--+
  100 rows selected (48.722 seconds)
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> set
  hive.exec.dynamic.partition=true; 开启动态分区功能
  No rows affected (0.018 seconds)
  0: jdbc:hive2://hadoop207.cevent.com:10000>
  set hive.exec.dynamic.partition.mode=nonstrict;  设置为非严格模式
  No rows affected (0.587 seconds)
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> set
  hive.exec.max.dynamic.partitions=1000; 
  所有maoreduce节点上最大创建的动态分区数量1000
  No rows affected (0.007 seconds) 在执行的每个mapreduce中，最大可以创建100个动态分区
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> set
  hive.exec.max.dynamic.partitions.pernode=100;
  No rows affected (0.009 seconds)
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> set
  hive.exec.max.created.files=100000;  
  最大可以创建多少个HDFS文件，默认为10万
  No rows affected (0.006 seconds)
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> set hive.error.on.empty.partition=false;
  空分区生成时，是否抛出异常
  No rows affected (0.007 seconds)   创建分区大表
  0: jdbc:hive2://hadoop207.cevent.com:10000>
  create table join_dep_partition(
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> id int,
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> name
  string
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> )
  0: jdbc:hive2://hadoop207.cevent.com:10000>
  partitioned by (location int) row format delimited
  fields terminated by '\t';
  No rows affected (2.525 seconds)
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> insert
  into table join_dep_partition  插入查询结果，自动根据类型匹配
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> partition(location)
  
  0: jdbc:hive2://hadoop207.cevent.com:10000>
  select deptno,dname,locations from c_dept;
  INFO  : Number of reduce tasks
  is set to 0 since there's no reduce operator
  INFO  : number of splits:1
  INFO  : Submitting tokens for
  job: job_1591675698957_0015
  INFO  : The url to track the
  job: http://hadoop207.cevent.com:8088/proxy/application_1591675698957_0015/
  INFO  : Starting Job =
  job_1591675698957_0015, Tracking URL =
  http://hadoop207.cevent.com:8088/proxy/application_1591675698957_0015/
  INFO  : Kill Command =
  /opt/module/hadoop-2.7.2/bin/hadoop job 
  -kill job_1591675698957_0015
  INFO  : Hadoop job information
  for Stage-1: number of mappers: 1; number of reducers: 0
  INFO  : 2020-06-09 18:18:43,415
  Stage-1 map = 0%,  reduce = 0%
  INFO  : 2020-06-09 18:18:53,201
  Stage-1 map = 100%,  reduce = 0%,
  Cumulative CPU 1.95 sec
  INFO  : MapReduce Total
  cumulative CPU time: 1 seconds 950 msec
  INFO  : Ended Job =
  job_1591675698957_0015
  INFO  : Stage-4 is selected by
  condition resolver.
  INFO  : Stage-3 is filtered out
  by condition resolver.
  INFO  : Stage-5 is filtered out
  by condition resolver.
  INFO  : Moving data to:
  hdfs://hadoop207.cevent.com:8020/user/hive/warehouse/cevent01.db/join_dep_partition/.hive-staging_hive_2020-06-09_18-18-27_297_470948791343936680-7/-ext-10000
  from hdfs://hadoop207.cevent.com:8020/user/hive/warehouse/cevent01.db/join_dep_partition/.hive-staging_hive_2020-06-09_18-18-27_297_470948791343936680-7/-ext-10002
  INFO  : Loading data to table
  cevent01.join_dep_partition partition (location=null) from
  hdfs://hadoop207.cevent.com:8020/user/hive/warehouse/cevent01.db/join_dep_partition/.hive-staging_hive_2020-06-09_18-18-27_297_470948791343936680-7/-ext-10000
  INFO  :          Time taken for load dynamic
  partitions : 2375
  INFO  :         Loading partition {location=1800}
  INFO  :         Loading partition {location=1900}
  INFO  :         Loading partition {location=1700}
  INFO  :          Time taken for adding to write
  entity : 2
  INFO  : Partition
  cevent01.join_dep_partition{location=1700} stats: [numFiles=1, numRows=2,
  totalSize=28, rawDataSize=26]
  INFO  : Partition
  cevent01.join_dep_partition{location=1800} stats: [numFiles=1, numRows=1,
  totalSize=12, rawDataSize=11]
  INFO  : Partition
  cevent01.join_dep_partition{location=1900} stats: [numFiles=1, numRows=1,
  totalSize=9, rawDataSize=8]
  No rows affected (32.8 seconds)
  0:
  jdbc:hive2://hadoop207.cevent.com:10000> show
  partitions join_dep_partition; 查看分区
  +----------------+--+
  |   partition    |
  +----------------+--+
  | location=1700  |
  | location=1800  |
  | location=1900  |
  +----------------+--+
  3 rows selected (0.502 seconds)

10.链接校验

http://hadoop207.cevent.com:50070/explorer.html#/user/hive/warehouse/cevent01.db
分区结果

cevent
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
快速学习-Hive企业级调优
01-07
Fetch 抓取是指，Hive 中对某些情况的查询可以不必使用 MapReduce 计算。例如：SELECT * FROM employees;在这种情况下，Hive 可以简单地读取 employee 对应的存储目录下的文件，然后输出查询结果到控制台。 在 hive-...
Hive取随机数 rand（）函数
热门推荐
qiezikuaichuan的专栏
03-09 9万+
取随机数函数: rand 语法: rand(),rand(int seed) 返回值: double 说明:返回一个0到1范围内的随机数。如果指定种子seed，则会等到一个稳定的随机数序列 举例： hive> select rand() from lxw_dual; 0.5577432776034763 hive> select rand() from lxw_dual; 0.66
Hive Join相关操作及常见函数
小叮当的博客
08-11 712
hive join相关操作
hive join 数据倾斜解决方案
雾岛与鲸的博客
07-30 5998
理解join的运行原理 select u.name, o.orderid from order o join user u on o.uid = u.uid; 理解join的作用？ 通常我们在执行join的时候，通常是一个表a包含很多的key, 这个key是可重复的，一张表b中对应的key是不能重复且唯一的。(如果两张表包含多个相同的key进行join操作，会产生笛卡尔积, 产生多个结果，显然在生产环境中，这是我们不想看到的) 为什么会产生数据倾斜？ 造成Join数据倾斜的原因是Join on的key分布
hive join时主表空值过多产生数据倾斜问题
qq_40771567的博客
02-22 1312
在各个表做关联的时候，如果主表过大且某个值过多再去join其他表很容易产生数据倾斜问题 我们的思路就是要将数据均匀的分配到每个任务上 这里我们以空值为例，使用rand（1）即可均匀分配数据 select * from (select *,case when department in ('-999','') then rand(1) else department end as department2 from profile_v2.detail_member_user_info_base)
hive-Fetch抓取
weixin_44896621的博客
05-01 1643
hive中fetch抓取模式的概念设置及三种模式的区别
Hive的分区表
01-20
Hive分区表 Hive分区表对应的是HDFS上独立的文件夹，该文件夹存放的是该分区的所有数据，其实分区就是分目录。Hive通过分区把一个大的数据集根据业务需要分割成小的数据集，在查询时能够通过where关键词选择指定分区...
Hive分区表
01-07
一：简介 分区表就是根据指定字段的值进行分类、分组，字段值相同的分为一类然后存储在一个单独的HDFS文件中，多个类就存储在多个文件中。原本存储在一个文件中的数据现在存储在多个文件中，查询...动态分区：创建表
hive调优
01-07
参数调优 如何开启map输出阶段压缩 背景：map完产生了很多中间文件，被保存在磁盘上，然后等待reduce通过网络来拉取，如果开启map输出阶段压缩，可以减少Map和Reduce间的数据传输量。 1）开启hive中间传输数据压缩...
Hive调优全方位指南.pdf
12-28
Hive调优全方位指南，总结了25条关于Hive调优的经验，对于大数据及hive工程师是不可多得的资源。
Hive性能调优之Fetch抓取
寒暄的博客
07-10 1216
Fetch抓取是指hive在某些情况的查询可以不必使用MapReduce计算，例如在执行一个简单的select * from XX 时，我们只需要简单的进行抓取对应目录下的数据即可。 在hive-default.xml.template中，hive.fetch.task.conversion默认是morn，老版本中默认是minimal。 该属性为morn时，在全局查找，字段查找，limit查找等都不走MapReduce。 <property> <name>hive.fetch
Hive调优: Fetch抓取
11-28 5247
Fetch抓取（Hive可以避免进行MapReduce） Hive中对某些情况的查询可以不必使用MapReduce计算。 例如：SELECT * FROM employees;在这种情况下，Hive可以简单地读取employee对应的存储目录下的文件，然后输出查询结果到控制台。 在hive-default.xml.template文件中hive.fetch.task.conversion默认是mo...
【大数据之Hive】二十七、HQL之Fetch抓取、本地模式、并行执行、严格模式
最新发布
阿宁的博客
07-21 431
Fetch抓取是指Hive中对某些情况的查询可以不必使用MapReduce计算。例如：select * from emp；在这种情况下，Hive可以简单地读取emp对应的存储目录下的文件，然后输出查询结果到控制台。
Hive Join 分析和优化
Thinkgamer博客
01-03 1812
转载请注明出处：http://blog.csdn.net/gamer_gyt 博主微博：http://weibo.com/234654758 Github：https://github.com/thinkgamer 背景 Sku对应品牌进行关联，大表对应非大表（这里的非大表并不能用小表来定义） 问题分析 进行表左关联时，最后一个reduce任务卡到99%，运行时间很长，发生了...
【Hive】Hive Join 介绍
w1992wishes的博客
08-13 1021
文章目录一、Join二、Join 类型2.1、INNER JOIN 内连接2.2、LEFT OUTER JOIN 左外连接2.3、RIGHT OUTER JOIN 右外连接2.4、FULL OUTER JOIN 全外连接2.5、LEFT SEMI JOIN 左半开连接2.6、JOIN笛卡尔积三、Join 的实现原理3.1、Common Join3.2、Map Join四、Join 优化4.1、BU...
达观数据文辉：Hadoop和Hive使用经验
达观数据
01-19 2611
近十年来，随着Hadoop生态系统的不断完善，Hadoop早已成为大数据事实上的行业标准之一。面对当今互联网产生的巨大的TB甚至PB级原始数据，利用基于Hadoop的数据仓库解决方案Hive早已是Hadoop的热点应用之一。达观数据团队长期致力于研究和积累Hadoop系统的技术和经验，并构建起了分布式存储、分析、挖掘以及应用的整套大数据处理平台。本文将从Hive的原理、架构及优化等方面来分享Hive的一些心得和使用经验，希望对大家有所收货。
hive if语句_Hive实践（高阶篇）
weixin_39694174的博客
11-25 1506
1.1、Hive SQL执行原理简单来说HIVE就是一个查询引擎，通过语法分析、语法解析、语法优化等一系列操作将SQL转化为MapReduce作业，MR作业一般会有以下几个过程：输入：根据输入数据的大小进行分片MAP阶段：Map任务的个数由输入的分片个数决定；在Map阶段对分片文件中的每行进行检查过滤，并按指定的列保存到bending文件。Combine阶段：将Map阶段的输出文件进行...
hive中的桶表，以及高效的join方式
Samaritan_H的博客
01-18 7319
hive中的join策略 大概可以分为三种 前面2种的话都是经常会用到，说下第三种 桶 join 桶（SMB） 物理上，每个桶就是表(或分区）目录里的一个文件。 smb的设计是为了解决大表和大表之间的join的。简单的说下她的思想：大表化成小表，map side join 解决。经典的分而治之的思想。 对一个表或者一个分区，可以将其划分为更细的数据块，桶。在hive中，分桶的方
hive中通用调优-fetch抓取-mr本地模式
mx13156的博客
09-01 562
mr本地模式：如果非要执行mr，能够执行本地的就不要提交yarn执行，默认是关闭的，意味着只要走mr就提交到yarn。Fetch抓取机制：在执行SQL的时候，能不走mr就不走mr，尽量直接去操作数据文件。hive提供了一个参数，自动切换mr程序为本地模式，如果不满条件，就执行yarn模式。参数设置：hive.fetch.task.conversion= more。mapreduce.framework.name = local 本地模式。通常情况下，全局查找，字段查找，limit查找都不走mr程序。
hive join 调优
04-05
在 Hive 中进行 join 操作时，可以通过以下几种方式进行调优： 1. 使用 Map Join：Map Join 是一种优化技术，它可以将小表加载到内存中，从而避免了大表的全表扫描。在 Hive 中，通过将一个小表的数据放入 Map 中，...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值