sql复制的配置单元表比原来的配置单元表大得多

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

我有一张table table1 ，然后我用了“ create table table2 as select * from table1 where partition_key is not null; “复制它。 table1 只是 463.2 GB ，但是 table2 结果是 2.8 TB . 为什么会这样？
ps：我刚刚显示了分区，似乎表1和表2的分区不同。因此，我补充了一个问题：如何复制一个表并保留其原始分区信息？
表1： hdfs dfs -du -s -h /user/hive/warehouse/map_services.db/userhistory1/* ```
7.9 G 23.7 G /user/hive/warehouse/map_services.db/userhistory/datestr=1970-01-01
25.7 G 77.1 G /user/hive/warehouse/map_services.db/userhistory/datestr=2017-10-01
18.8 G 56.3 G /user/hive/warehouse/map_services.db/userhistory/datestr=2017-10-02
16.8 G 50.5 G /user/hive/warehouse/map_services.db/userhistory/datestr=2017-10-03
17.5 G 52.5 G /user/hive/warehouse/map_services.db/userhistory/datestr=2017-10-04
18.0 G 53.9 G /user/hive/warehouse/map_services.db/userhistory/datestr=2017-10-05
22.4 G 67.1 G /user/hive/warehouse/map_services.db/userhistory/datestr=2017-10-06
27.3 G 81.8 G /user/hive/warehouse/map_services.db/userhistory/datestr=2017-10-07

  1. 表2： `hdfs dfs -du -s -h /user/hive/warehouse/map_services.db/userhistory2/*` ```
  2. 929.2 M  2.7 G  /user/hive/warehouse/map_services.db/userhistory2/000000_0
  3. 651.1 M  1.9 G  /user/hive/warehouse/map_services.db/userhistory2/000001_0
  4. 1.1 G  3.3 G  /user/hive/warehouse/map_services.db/userhistory2/000002_0
  5. 1.1 G  3.3 G  /user/hive/warehouse/map_services.db/userhistory2/000003_0
  6. 1.6 G  4.7 G  /user/hive/warehouse/map_services.db/userhistory2/000004_0
  7. 1.3 G  4.0 G  /user/hive/warehouse/map_services.db/userhistory2/000005_0
  8. 1.2 G  3.7 G  /user/hive/warehouse/map_services.db/userhistory2/000006_0
  9. 1.5 G  4.5 G  /user/hive/warehouse/map_services.db/userhistory2/000007_0
  10. 1.5 G  4.4 G  /user/hive/warehouse/map_services.db/userhistory2/000008_0
  11. 1.5 G  4.4 G  /user/hive/warehouse/map_services.db/userhistory2/000009_0
  12. 1.5 G  4.5 G  /user/hive/warehouse/map_services.db/userhistory2/000010_0
  13. 1.4 G  4.3 G  /user/hive/warehouse/map_services.db/userhistory2/000011_0
  14. 1.4 G  4.3 G  /user/hive/warehouse/map_services.db/userhistory2/000012_0
  15. 1.3 G  3.8 G  /user/hive/warehouse/map_services.db/userhistory2/000013_0
  16. 1.5 G  4.4 G  /user/hive/warehouse/map_services.db/userhistory2/000014_0
  17. 1.4 G  4.2 G  /user/hive/warehouse/map_services.db/userhistory2/000015_0
  18. 1.2 G  3.6 G  /user/hive/warehouse/map_services.db/userhistory2/000016_0
  19. 1.5 G  4.5 G  /user/hive/warehouse/map_services.db/userhistory2/000017_0
  20. 1.5 G  4.4 G  /user/hive/warehouse/map_services.db/userhistory2/000018_0
  21. 1.4 G  4.2 G  /user/hive/warehouse/map_services.db/userhistory2/000019_0
  22. 1.5 G  4.6 G  /user/hive/warehouse/map_services.db/userhistory2/000020_0
  23. 1.5 G  4.5 G  /user/hive/warehouse/map_services.db/userhistory2/000021_0
  24. 1.6 G  4.7 G  /user/hive/warehouse/map_services.db/userhistory2/000022_0
  25. 1.3 G  4.0 G  /user/hive/warehouse/map_services.db/userhistory2/000023_0
  26. 1.1 G  3.4 G  /user/hive/warehouse/map_services.db/userhistory2/000024_0
  27. 908.7 M  2.7 G  /user/hive/warehouse/map_services.db/userhistory2/000025_0
  28. 1.4 G  4.2 G  /user/hive/warehouse/map_services.db/userhistory2/000026_0
  29. 1.4 G  4.3 G  /user/hive/warehouse/map_services.db/userhistory2/000027_0
  30. 1.3 G  3.8 G  /user/hive/warehouse/map_services.db/userhistory2/000028_0
  31. 1.4 G  4.1 G  /user/hive/warehouse/map_services.db/userhistory2/000029_0
  32. 1.6 G  4.7 G  /user/hive/warehouse/map_services.db/userhistory2/000030_0
  33. 1.3 G  4.0 G  /user/hive/warehouse/map_services.db/userhistory2/000031_0
  34. 1.3 G  4.0 G  /user/hive/warehouse/map_services.db/userhistory2/000032_0
  35. 1.6 G  4.8 G  /user/hive/warehouse/map_services.db/userhistory2/000033_0
  36. 1.5 G  4.4 G  /user/hive/warehouse/map_services.db/userhistory2/000034_0
  37. 1.3 G  3.8 G  /user/hive/warehouse/map_services.db/userhistory2/000035_0
  38. 940.0 M  2.8 G  /user/hive/warehouse/map_services.db/userhistory2/000036_0
  39. 1.3 G  4.0 G  /user/hive/warehouse/map_services.db/userhistory2/000037_0
  40. 1.2 G  3.6 G  /user/hive/warehouse/map_services.db/userhistory2/000038_0
  41. 1.5 G  4.6 G  /user/hive/warehouse/map_services.db/userhistory2/000039_0
  42. 1.2 G  3.7 G  /user/hive/warehouse/map_services.db/userhistory2/000040_0
  43. 1.1 G  3.4 G  /user/hive/warehouse/map_services.db/userhistory2/000041_0
  44. 1.1 G  3.4 G  /user/hive/warehouse/map_services.db/userhistory2/000042_0
  45. 1.0 G  3.1 G  /user/hive/warehouse/map_services.db/userhistory2/000043_0
  46. 1.4 G  4.3 G  /user/hive/warehouse/map_services.db/userhistory2/000044_0
  47. 1.3 G  4.0 G  /user/hive/warehouse/map_services.db/userhistory2/000045_0
  48. 1.4 G  4.1 G  /user/hive/warehouse/map_services.db/userhistory2/000046_0
  49. 1.5 G  4.5 G  /user/hive/warehouse/map_services.db/userhistory2/000047_0
  50. 1.1 G  3.3 G  /user/hive/warehouse/map_services.db/userhistory2/000048_0
  51. 706.3 M  2.1 G  /user/hive/warehouse/map_services.db/userhistory2/000049_0
  52. 1.4 G  4.2 G  /user/hive/warehouse/map_services.db/userhistory2/000050_0
  53. 1.5 G  4.6 G  /user/hive/warehouse/map_services.db/userhistory2/000051_0
  54. 872.2 M  2.6 G  /user/hive/warehouse/map_services.db/userhistory2/000052_0
  55. 1.2 G  3.5 G  /user/hive/warehouse/map_services.db/userhistory2/000053_0
  56. 1.2 G  3.7 G  /user/hive/warehouse/map_services.db/userhistory2/000054_0
  57. 943.9 M  2.8 G  /user/hive/warehouse/map_services.db/userhistory2/000055_0
  58. 1.6 G  4.7 G  /user/hive/warehouse/map_services.db/userhistory2/000056_0
  59. 1.5 G  4.4 G  /user/hive/warehouse/map_services.db/userhistory2/000057_0
  60. 1.3 G  4.0 G  /user/hive/warehouse/map_services.db/userhistory2/000058_0
  61. 1.4 G  4.3 G  /user/hive/warehouse/map_services.db/userhistory2/000059_0
  62. 961.5 M  2.8 G  /user/hive/warehouse/map_services.db/userhistory2/000060_0
  63. 1.3 G  3.8 G  /user/hive/warehouse/map_services.db/userhistory2/000061_0
  64. 1.4 G  4.3 G  /user/hive/warehouse/map_services.db/userhistory2/000062_0
  65. 1.4 G  4.2 G  /user/hive/warehouse/map_services.db/userhistory2/000063_0
  66. 1.4 G  4.1 G  /user/hive/warehouse/map_services.db/userhistory2/000064_0
  67. 924.4 M  2.7 G  /user/hive/warehouse/map_services.db/userhistory2/000065_0
sqlHivecreate-tablehiveddl

1条答案

按热度按时间
wswtfjt7

wswtfjt71#

您的目标表没有压缩和分区。
要创建具有相同分区的表，请使用以下命令：

  1. create table 2 like table1;

插入前打开压缩：

  1. SET hive.exec.compress.output=true;

插入覆盖动态分区：

  1. set hive.exec.dynamic.partition=true;  
  2. set hive.exec.dynamic.partition.mode=nonstrict; 
  4. insert overwrite table2 partition(partition_key)
  5. select * from table1;
赞(0）分享  回复(0）举报  2021-06-26
我来回答

相关问题

    查看更多

    热门标签

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

    最新问答

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