hive：分区表中的Map联接

u91tlkcl  于 2021-06-26  发布在  Hive
关注(0)|答案(1)|浏览(580)

考虑到hive中具有事实表和维度表的典型数据仓库场景，假设事实表通过分区拆分到多个数据节点上。在将事实表（已分区）与维度（未分区）连接时，使用Map连接似乎是合乎逻辑的，因为维度表的大小很小&它们要存储在内存中，以便在所有节点上高效地与事实数据连接。
但是，很少有联机资源建议对分区表执行Map联接，两个表上的分区键应该与联接键相同。
所以，这就是我要寻找答案的问题：
分区表（事实）能否与非分区表（维度）Map联接？

Hivehiveqlhadoop-partitioning

1条答案

按热度按时间
i7uq4tfw

i7uq4tfw1#

答案是-是的
Map联接运算符
演示

  1. create table fact (rec_id int,dim_id int) partitioned by (dt date);
  2. create table dim  (dim_id int,descr string);
  1. explain
  2. select  *
  3. from    fact f join dim d
  4.         on d.dim_id = f.dim_id
  1. STAGE DEPENDENCIES:
  2.   Stage-4 is a root stage
  3.   Stage-3 depends on stages: Stage-4
  4.   Stage-0 depends on stages: Stage-3
  6. STAGE PLANS:
  7.   Stage: Stage-4
  8.     Map Reduce Local Work
  9.       Alias -> Map Local Tables:
  10.         d 
  11.           Fetch Operator
  12.             limit: -1
  13.       Alias -> Map Local Operator Tree:
  14.         d 
  15.           TableScan
  16.             alias: d
  17.             Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
  18.             Filter Operator
  19.               predicate: dim_id is not null (type: boolean)
  20.               Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
  21.               HashTable Sink Operator
  22.                 keys:
  23.                   0 dim_id (type: int)
  24.                   1 dim_id (type: int)
  26.   Stage: Stage-3
  27.     Map Reduce
  28.       Map Operator Tree:
  29.           TableScan
  30.             alias: f
  31.             Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
  32.             Filter Operator
  33.               predicate: dim_id is not null (type: boolean)
  34.               Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
  35.               Map Join Operator
  36.                 condition map:
  37.                      Inner Join 0 to 1
  38.                 keys:
  39.                   0 dim_id (type: int)
  40.                   1 dim_id (type: int)
  41.                 outputColumnNames: _col0, _col1, _col2, _col6, _col7
  42.                 Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
  43.                 Select Operator
  44.                   expressions: _col0 (type: int), _col1 (type: int), _col2 (type: date), _col6 (type: int), _col7 (type: string)
  45.                   outputColumnNames: _col0, _col1, _col2, _col3, _col4
  46.                   Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
  47.                   File Output Operator
  48.                     compressed: false
  49.                     Statistics: Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE
  50.                     table:
  51.                         input format: org.apache.hadoop.mapred.SequenceFileInputFormat
  52.                         output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
  53.                         serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
  54.       Local Work:
  55.         Map Reduce Local Work
  57.   Stage: Stage-0
  58.     Fetch Operator
  59.       limit: -1
  60.       Processor Tree:
  61.         ListSink
展开查看全部
赞(0）分享  回复(0）举报  2021-06-26
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com