部署

主机规划

NN JN ZKFC ZK DN RM NM hive server hive cli spark sql spark server2 spark beeline
node01 * * * * *
node02 * * * * * * * * *
node03 * * * * *
node04 * * * *

原来的hadoop机群RM在node03和node04

Standalone

master node01 node02
worker node02 node03 node04

  1. node01 
  2. cd /opt
  3. tar xzvf spark-2.3.4-bin-hadoop2.6.tgz
  4. mv spark-2.3.4-bin-hadoop2.6 spark-2.3.4
  5. 配置环境变量
  6. cd /opt/spark-2.3.4/conf
  7. //配置works
  8. mv slaves.template slaves
  9. vi slaves
  10. node02
  11. node03
  12. node04
  14. //环境参数
  15.  spark-env.sh
  16.  export HADOOP_CONF_DIR=/opt/bigdata/hadoop-2.6.5/etc/hadoop
  17.  export SPARK_MASTER_HOST=node01   # 每台master改成自己的主机名
  18.  export SPARK_MASTER_PORT=7077
  19.  export SPARK_MASTER_WEBUI_PORT=8080
  20.  export SPARK_WORKER_CORES=4
  21.  export SPARK_WORKER_MEMORY=4g
  25. //配置高可用
  26. cp spark-defaults.conf.template  spark-defaults.conf
  27. vi  spark-defaults.conf
  28.  spark.deploy.recoveryMode       ZOOKEEPER
  29.  spark.deploy.zookeeper.url      node02:2181,node03:2181,node04:2181
  30.  spark.deploy.zookeeper.dir      /bryanspark
  31. spark.eventLog.enabled true
  32.  spark.eventLog.dir hdfs://mycluster/spark_log
  33.  spark.history.fs.logDirectory  hdfs://mycluster/spark_log
  35. //复制到其他主机
  36.  scp -r spark-2.3.4 node02:`pwd`
  37.  scp -r spark-2.3.4 node03:`pwd`
  38.  scp -r spark-2.3.4 node04:`pwd`
  42. node01：
  43.    start-dfs.sh  //只启动hdfs
  45.    sbin/start-all.sh  //启动master和slaves
  47. node02 
  48.     sbin/start-master.sh
  51.  测试 node01
  52.  spark-shell --master spark://node01:7077,node02:7077
  53.  scala> sc.textFile("hdfs://mycluster/usr/bryan/wc.txt").flatMap(_.split(" ")).map((_,1)).reduceByKey(_+_).collect().foreach(println)
  54. (jim,1)
  55. (bryan,2)
  56. (lily,1)
  57. (peter,2)
  58. (hello,2)
  59. (msb,2)
  60. (pp,1)
  61. (jiam,1)
  63. 必须要加collect回收数据，要不然不能显示     
  64. spark-shell 只能client模式

spark-shell

查看各节点角色
spark-shell在哪个节点启动的，SparkSubmit进程（driver）就在哪里

  1. node01
  2. [root@node01 ~]# jps
  3. 4848 SparkSubmit
  4. 3569 ResourceManager
  5. 4451 Master
  6. 3460 DFSZKFailoverController
  7. 3289 JournalNode
  8. 5066 Jps
  9. 3087 NameNode
  11. node02
  12. [root@node02 spark-2.3.4]# jps
  13. 2368 QuorumPeerMain
  14. 3456 Worker
  15. 2626 DataNode
  16. 4035 Jps
  17. 3572 Master
  18. 2839 DFSZKFailoverController
  19. 2922 NodeManager
  20. 3114 ResourceManager
  21. 2555 NameNode
  22. 3867 CoarseGrainedExecutorBackend
  23. 2717 JournalNode
  26. node03
  27. [root@node03 ~]# jps
  28. 2196 QuorumPeerMain
  29. 2406 JournalNode
  30. 3334 Jps
  31. 3143 CoarseGrainedExecutorBackend
  32. 2504 NodeManager
  33. 2317 DataNode
  34. 2877 Worker
  38. node04
  39. [root@node04 ~]# jps
  40. 2212 DataNode
  41. 2326 NodeManager
  42. 2103 QuorumPeerMain
  43. 2920 CoarseGrainedExecutorBackend
  44. 3131 Jps
  45. 2670 Worker

spark-submit client

  2. //创建提交脚本，用官方例子
  3. vi submit.sh
  5. #!/bin/bash
  6. jar=$SPARK_HOME/examples/jars/spark-examples_2.11-2.3.4.jar
  7. master=spark://node01:7077,node02:7077
  8. #master=yarn
  9. mode=client
  10. class=org.apache.spark.examples.SparkPi
  12. $SPARK_HOME/bin/spark-submit   \
  13. --master $master \
  14. --deploy-mode $mode \
  15. --class $class  \
  16. $jar \
  17. 1000
  20. node03
  21. ./submit
  23. 在node03节点有SparkSubmit进程

spark-submit cluster

mode=cluster

node03
./submit
此时这个client把程序提交到集群然后退出，集群某个节点启动driver，继续跑！

  1. [root@node01 spark-2.3.4]# jps
  2. 3569 ResourceManager
  3. 10755 Jps
  4. 3460 DFSZKFailoverController
  5. 3289 JournalNode
  6. 7438 Master
  7. 3087 NameNode
  11. [root@node02 spark-2.3.4]# jps
  12. 2368 QuorumPeerMain
  13. 10961 CoarseGrainedExecutorBackend
  14. 2626 DataNode
  15. 9411 Master
  16. 6854 HistoryServer
  17. 2839 DFSZKFailoverController
  18. 5960 Worker
  19. 2922 NodeManager
  20. 3114 ResourceManager
  21. 2555 NameNode
  22. 11004 Jps
  23. 2717 JournalNode
  27. [root@node03 ~]# jps
  28. 5105 Worker
  29. 9522 SparkSubmit   //client提交进程
  30. 2196 QuorumPeerMain
  31. 2406 JournalNode
  32. 9591 Jps
  33. 2504 NodeManager
  34. 9577 CoarseGrainedExecutorBackend
  35. 2317 DataNode
  38. [root@node04 ~]# jps
  39. 8192 DriverWrapper    //driver
  40. 2212 DataNode
  41. 2326 NodeManager
  42. 2103 QuorumPeerMain
  43. 4666 Worker
  44. 8250 CoarseGrainedExecutorBackend
  45. 8266 Jps
  46. [root@node04 ~]# jps
  47. 2212 DataNode
  48. 2326 NodeManager
  49. 2103 QuorumPeerMain
  50. 4666 Worker
  51. 8367 Jps

内存与CPU参数

  1. --deploy-mode  
  2. --driver-memory  1024m  driver 
  3.     #有能力拉取计算结果回自己的jvm
  4.     #driver、executor jvm中会存储中间计算过程的数据的元数据
  6. --driver-cores  # cluster  mode ，默认1 
  8. --total-executor-cores  #standalone     
  9. --executor-cores 1    # standalone默认每节点一个executor并占用全部核，如果设置此参数每个node会出现多个executor    yarn  每executor 1  core
  10. --executor-memory  1024m
  11. --num-executors NUM   默认2 yarn专属
  14. standalone 3个worker,假设CPU足够
  15. --total-executor-cores
  16. 每个节点 一个exector 2个核
  18. --total-executor-cores
  19. --executor-cores 1
  20.  每个节点 二个exector 每executor 1个核

History

master一重启，历史记录就不存在了

  1. vi  spark-defaults.conf
  3. hdfs dfs -mkidr -p /spark_log
  5.  spark.eventLog.enabled true
  6.  spark.eventLog.dir hdfs://mycluster/spark_log   //存的，目录要提前创建
  7.  spark.history.fs.logDirectory  hdfs://mycluster/spark_log  //取的
  10.     a)修改配置一定要分发、重启服务
  12.     b)计算层会自己将自己的计算日志存入hdfs
  14.     c)手动启动：./sbin/start-history-server.sh ,用来取日志
  16.     e)访问 启动有  history-server主机的主机名 默认端口是  18080

Spark on Yarn

  1. spark-env.sh
  3. export HADOOP_CONF_DIR=/opt/bigdata/hadoop-2.6.5/etc/hadoop
  6. spark-defaults.conf
  8.     spark.eventLog.enabled true
  9.     spark.eventLog.dir hdfs://mycluster/spark_log
  10.     spark.history.fs.logDirectory  hdfs://mycluster/spark_log
  11.    spark.yarn.jars  hdfs://mycluster/work/spark_lib/jars/*
  13. 将spark的jar包上传到hdfs,要不然yarn每次都要上传
  14. hdfs dfs -mkdir -p /work/spark_lib/jars
  15. /opt/spark-2.3.4/jars
  16. hdfs dfs -put ./* /work/spark_lib/jars
  18. 没配置jars
  19. [root@node01 conf]# hdfs dfs -ls /user/root/.sparkStaging/application_1627546735806_0006
  20. Found 3 items
  21. -rw-r--r--   2 root supergroup     190850 2021-07-29 16:34 /user/root/.sparkStaging/application_1627546735806_0006/__spark_conf__.zip
  22. -rw-r--r--   2 root supergroup  232507850 2021-07-29 16:34 /user/root/.sparkStaging/application_1627546735806_0006/__spark_libs__5988972910506987504.zip
  23. -rw-r--r--   2 root supergroup    1991004 2021-07-29 16:34 /user/root/.sparkStaging/application_1627546735806_0006/spark-examples_2.11-2.3.4.jars
  24. 配置之后
  25. [root@node01 conf]# hdfs dfs -ls /user/root/.sparkStaging/application_1627546735806_0005
  26. Found 2 items
  27. -rw-r--r--   2 root supergroup     221760 2021-07-29 16:33 /user/root/.sparkStaging/application_1627546735806_0005/__spark_conf__.zip
  28. -rw-r--r--   2 root supergroup    1991004 2021-07-29 16:33 /user/root/.sparkStaging/application_1627546735806_0005/spark-examples_2.11-2.3.4.jar
  31. client:ExecutorLauncher  
  32. cluster:ApplicationMaster
  33. spark-shell  只支持  client模式
  34. spark-submit  跑非repl 的可以是client、cluster

  1. yarn-site.xml
  3. <property>
  4.     <name>yarn.nodemanager.resource.memory-mb</name>
  5.     <value>4096</value>
  6. </property>
  7. <property>
  8.     <name>yarn.nodemanager.resource.cpu-vcores</name>
  9.     <value>4</value>
  10. </property>
  11. <property>
  12.     <name>yarn.nodemanager.vmem-check-enabled</name>
  13.     <value>false</value>
  14. </property>

client

  1. //创建提交脚本，用官方例子
  2. vi submit.sh
  4. #!/bin/bash
  5. jar=$SPARK_HOME/examples/jars/spark-examples_2.11-2.3.4.jar
  6. #master=spark://node01:7077,node02:7077
  7. master=yarn
  8. mode=client
  9. class=org.apache.spark.examples.SparkPi
  11. $SPARK_HOME/bin/spark-submit   \
  12. --master $master \
  13. --deploy-mode $mode \
  14. --class $class  \
  15. $jar \
  16. 1000
  19. node03
  20. ./submit.sh

角色查看

  1. [root@node01 spark-2.3.4]# jps
  2. 3460 DFSZKFailoverController
  3. 15320 Jps
  4. 3289 JournalNode
  5. 14109 ResourceManager
  6. 3087 NameNode
  9. [root@node02 spark-2.3.4]# jps
  10. 2368 QuorumPeerMain
  11. 2626 DataNode
  12. 6854 HistoryServer
  13. 2839 DFSZKFailoverController
  14. 16505 Jps
  15. 2555 NameNode
  16. 2717 JournalNode
  17. 15117 NodeManager
  18. 15214 ResourceManager
  21. [root@node03 ~]# jps
  22. 13970 CoarseGrainedExecutorBackend
  23. 2196 QuorumPeerMain
  24. 12773 NodeManager
  25. 2406 JournalNode
  26. 13768 SparkSubmit  //driver
  27. 2317 DataNode
  28. 14030 Jps
  31. [root@node04 ~]# jps
  32. 2212 DataNode
  33. 2103 QuorumPeerMain
  34. 12137 CoarseGrainedExecutorBackend
  35. 12186 Jps
  36. 11148 NodeManager
  37. 12095 ExecutorLauncher   //appmaster

cluster

将mode改成cluster

角色查看

  1. [root@node01 opt]# jps
  2. 3460 DFSZKFailoverController
  3. 16552 Jps
  4. 3289 JournalNode
  5. 14109 ResourceManager
  6. 3087 NameNode
  9. [root@node02 spark-2.3.4]# jps
  10. 2368 QuorumPeerMain
  11. 2626 DataNode
  12. 6854 HistoryServer
  13. 2839 DFSZKFailoverController
  14. 18010 Jps
  15. 2555 NameNode
  16. 2717 JournalNode
  17. 15117 NodeManager
  18. 15214 ResourceManager
  22. [root@node03 ~]# jps
  23. 2196 QuorumPeerMain
  24. 12773 NodeManager
  25. 15141 SparkSubmit   //client
  26. 2406 JournalNode
  27. 15222 Jps
  28. 2317 DataNode
  31. [root@node04 ~]# jps
  32. 13426 ApplicationMaster  //driver和appmaster都在这一个进程
  33. 13474 Jps
  34. 2212 DataNode
  35. 2103 QuorumPeerMain
  36. 11148 NodeManager

SparkSQL

将hive-site.xml复制到spark/conf文件中
hive-site.xml

  1.   <property>
  2.       <name>hive.metastore.uris</name>
  3.       <value>thrift://node03:9083</value>
  4.   </property>

hive metastore

node03

hive —service metastore

hive cli

node04
hive

spark sql cli

  1. spark-sql --master yarn

spark thrft-server

相当于hiveserver2
node01

  1. $SPARK_HOME/sbin/start-thriftserver.sh --master yarn

beeline
node02

  1. $SPARK_HOME/bin/beeline
  3. !connect jdbc:hive2://node01:10000

分区分表实验

数据准备

  1. part1.txt
  2. 1       小明1   lol,book,movie  beijing:mashibing,shanghai:pudong
  3. 2       小明2   lol,book,movie  beijing:mashibing,shanghai:pudong
  4. 3       小明3   lol,book,movie  beijing:mashibing,shanghai:pudong
  5. 4       小明4   lol,book,movie  beijing:mashibing,shanghai:pudong
  6. 5       小明5   lol,movie       beijing:mashibing,shanghai:pudong
  7. 6       小明6   lol,book,movie  beijing:mashibing,shanghai:pudong
  8. 7       小明7   lol,book        beijing:mashibing,shanghai:pudong
  9. 8       小明8   lol,book        beijing:mashibing,shanghai:pudong
  10. 9       小明9   lol,book,movie  beijing:mashibing,shanghai:pudong
  11. 10      小明10  lol,book,skill  beijing:mashibing,shanghai:pudong
  13. part2.txt
  14. 11      小明11  lol,book,movie  beijing:mashibing,shanghai:pudong
  15. 12      小明12  lol,book,movie  beijing:mashibing,shanghai:pudong
  16. 13      小明13  lol,book,movie  beijing:mashibing,shanghai:pudong
  17. 14      小明14  lol,book,movie  beijing:mashibing,shanghai:pudong
  18. 15      小明15  lol,movie       beijing:mashibing,shanghai:pudong
  19. 16      小明16  lol,book,movie  beijing:mashibing,shanghai:pudong
  20. 17      小明17  lol,book        beijing:mashibing,shanghai:pudong
  21. 18      小明18  lol,book        beijing:mashibing,shanghai:pudong
  22. 19      小明19  lol,book,movie  beijing:mashibing,shanghai:pudong
  23. 20      小明20  lol,book,skill  beijing:mashibing,shanghai:pudong
  26. part3.txt
  27. 21      小明21  lol,book,movie  beijing:mashibing,shanghai:pudong
  28. 22      小明22  lol,book,movie  beijing:mashibing,shanghai:pudong
  29. 23      小明23  lol,book,movie  beijing:mashibing,shanghai:pudong
  30. 24      小明24  lol,book,movie  beijing:mashibing,shanghai:pudong
  31. 25      小明25  lol,movie       beijing:mashibing,shanghai:pudong
  32. 26      小明26  lol,book,movie  beijing:mashibing,shanghai:pudong
  33. 27      小明27  lol,book        beijing:mashibing,shanghai:pudong
  34. 28      小明28  lol,book        beijing:mashibing,shanghai:pudong
  35. 29      小明29  lol,book,movie  beijing:mashibing,shanghai:pudong
  36. 30      小明30  lol,book,skill  beijing:mashibing,shanghai:pudong
  39. part4.txt
  40. 41      小明41  lol,book,movie  beijing:mashibing,shanghai:pudong
  41. 42      小明42  lol,book,movie  beijing:mashibing,shanghai:pudong
  42. 43      小明43  lol,book,movie  beijing:mashibing,shanghai:pudong
  43. 44      小明44  lol,book,movie  beijing:mashibing,shanghai:pudong
  44. 45      小明45  lol,movie       beijing:mashibing,shanghai:pudong
  45. 46      小明46  lol,book,movie  beijing:mashibing,shanghai:pudong
  46. 47      小明47  lol,book        beijing:mashibing,shanghai:pudong
  47. 48      小明48  lol,book        beijing:mashibing,shanghai:pudong
  48. 49      小明49  lol,book,movie  beijing:mashibing,shanghai:pudong
  49. 50      小明50  lol,book,skill  beijing:mashibing,shanghai:pudong

仅分区

内部表

  1. create table part_in (
  2. id int,
  3. name string,
  4. likes array<string>,
  5. address map<string,string>
  6. )
  7. partitioned by(data_dt string,channel int)
  8. row format delimited
  9. fields terminated by '\t'
  10. collection items terminated by ','
  11. map keys terminated by ':';    
  14. 分区字段相当于虚字段，实际数据中不存在，但hive查询时会显示分区列
  16. 本地路径，复制文件到hdfs
  17. load data local inpath '/opt/part1.txt' into table part_in partition (data_dt='20210701',channel=110);
  18. load data local inpath '/opt/part2.txt' into table part_in partition (data_dt='20210701',channel=120);
  19. load data local inpath '/opt/part3.txt' into table part_in partition (data_dt='20210702',channel=110);
  20. load data local inpath '/opt/part4.txt' into table part_in partition (data_dt='20210702',channel=120);
  24. hdfs
  25. 移动文件到hive表
  26. load data  inpath '/data/part1.txt' into table part_in partition (data_dt='20210701',channel=110);
  30. 直接insert into xx values这样插入，效率低
  31. 会产生以下这种copy文件，每插入一次，产生一个
  32. 000000_0_copy_1
  33. 000000_0_copy_2

image.png
image.png
image.png

查询

  1. hive> select * from part_in;
  2. OK
  3. 1    小明1    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    110
  4. 2    小明2    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    110
  5. 3    小明3    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    110
  6. 4    小明4    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    110
  7. 5    小明5    ["lol","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    110
  8. 6    小明6    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    110
  9. 7    小明7    ["lol","book"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    110
  10. 8    小明8    ["lol","book"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    110
  11. 9    小明9    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    110
  12. 10    小明10    ["lol","book","skill"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    110
  13. 11    小明11    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    120
  14. 12    小明12    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    120
  15. 13    小明13    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    120
  16. 14    小明14    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    120
  17. 15    小明15    ["lol","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    120
  18. 16    小明16    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    120
  19. 17    小明17    ["lol","book"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    120
  20. 18    小明18    ["lol","book"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    120
  21. 19    小明19    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    120
  22. 20    小明20    ["lol","book","skill"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    120
  23. 21    小明21    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    110
  24. 22    小明22    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    110
  25. 23    小明23    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    110
  26. 24    小明24    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    110
  27. 25    小明25    ["lol","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    110
  28. 26    小明26    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    110
  29. 27    小明27    ["lol","book"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    110
  30. 28    小明28    ["lol","book"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    110
  31. 29    小明29    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    110
  32. 30    小明30    ["lol","book","skill"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    110
  33. 41    小明41    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    120
  34. 42    小明42    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    120
  35. 43    小明43    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    120
  36. 44    小明44    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    120
  37. 45    小明45    ["lol","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    120
  38. 46    小明46    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    120
  39. 47    小明47    ["lol","book"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    120
  40. 48    小明48    ["lol","book"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    120
  41. 49    小明49    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    120
  42. 50    小明50    ["lol","book","skill"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    120
  43. Time taken: 0.14 seconds, Fetched: 40 row(s)
  45. //用分区列查找，只加载对应文件，速度快 
  46. hive> select * from part_in where data_dt=20210701;
  47. OK
  48. 1    小明1    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    110
  49. 2    小明2    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    110
  50. 3    小明3    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    110
  51. 4    小明4    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    110
  52. 5    小明5    ["lol","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    110
  53. 6    小明6    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    110
  54. 7    小明7    ["lol","book"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    110
  55. 8    小明8    ["lol","book"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    110
  56. 9    小明9    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    110
  57. 10    小明10    ["lol","book","skill"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    110
  58. 11    小明11    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    120
  59. 12    小明12    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    120
  60. 13    小明13    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    120
  61. 14    小明14    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    120
  62. 15    小明15    ["lol","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    120
  63. 16    小明16    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    120
  64. 17    小明17    ["lol","book"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    120
  65. 18    小明18    ["lol","book"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    120
  66. 19    小明19    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    120
  67. 20    小明20    ["lol","book","skill"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    120
  68. Time taken: 0.784 seconds, Fetched: 20 row(s)

外部表

删除外部表只会删除元数据，不会删数据，安全性高。数仓一般这样用

  1. create external table part_out (
  2. id int,
  3. name string,
  4. likes array<string>,
  5. address map<string,string>
  6. )
  7. partitioned by(data_dt string,channel int)
  8. row format delimited
  9. fields terminated by '\t'
  10. collection items terminated by ','
  11. map keys terminated by ':'
  12. location '/var/bigdata/hive_remote/warehouse/part_out';
  15. 本地路径，复制文件到hdfs
  16. load data local inpath '/opt/part1.txt' [overwrite] into table part_out partition (data_dt='20210701',channel=110);
  17. load data local inpath '/opt/part2.txt' [overwrite] into table part_out partition (data_dt='20210701',channel=120);
  18. load data local inpath '/opt/part3.txt' [overwrite] into table part_out partition (data_dt='20210702',channel=110);
  19. load data local inpath '/opt/part4.txt' [overwrite] into table part_out partition (data_dt='20210702',channel=120);
  23. hdfs
  24. 移动文件到hive表
  25. load data inpath '/data/part1.txt' into table part_out partition (data_dt='20210701',channel=110);

目录结构和内部表一样

分区操作

  1. --给分区表添加分区列的值
  2.     alter table table_name add partition(col_name=col_value)
  3. --删除分区列的值
  4.     alter table table_name drop partition(col_name=col_value)
  5. /*
  6.     注意:
  7.         1、添加分区列的值的时候，如果定义的是多分区表，那么必须给所有的分区列都赋值
  8.         2、删除分区列的值的时候，无论是单分区表还是多分区表，都可以将指定的分区（多分区指定一个分区可以级联删除）进行删除,级联删除
  10.  在已有数据目录，建立外部分区表，需要修改分区       
  11. --修复分区
  12.     msck repair table psn7;

动态分区

仅分桶

  1. create table part_bucket (
  2. id int,
  3. name string,
  4. likes array<string>,
  5. address map<string,string>
  6. )
  7. clustered by (id) into 4 buckets    
  8. row format delimited
  9. fields terminated by '\t'
  10. collection items terminated by ','
  11. map keys terminated by ':';    
  13. //不能用load data
  14. insert into table part_bucket select id,name,likes,address from part_in where data_dt='20210701' and channel=110;
  15. insert into table part_bucket select id,name,likes,address from part_in where data_dt='20210701' and channel=120;

image.png

分区再分桶

内部表

  1. create table part_in_bucket (
  2. id int,
  3. name string,
  4. likes array<string>,
  5. address map<string,string>
  6. )
  7. partitioned by(data_dt string,channel int)
  8. clustered by (id) into 4 buckets 
  9. row format delimited
  10. fields terminated by '\t'
  11. collection items terminated by ','
  12. map keys terminated by ':';    
  16. //不能用load data
  17. insert overwrite table part_in_bucket partition (data_dt='20210701',channel=110)
  18.   select id,name,likes,address  from part_in where data_dt='20210701' and channel=110;
  19. //不能select * ,会产生6个字段，包括分区列 overwrite是覆盖
  20. insert overwrite table part_in_bucket partition (data_dt='20210701',channel=120)
  21.   select id,name,likes,address  from part_in where data_dt='20210701' and channel=120;
  22. insert overwrite table part_in_bucket partition (data_dt='20210702',channel=110)
  23.   select id,name,likes,address  from part_in where data_dt='20210702' and channel=110;
  24. insert overwrite table part_in_bucket partition (data_dt='20210702',channel=120)
  25.   select id,name,likes,address  from part_in where data_dt='20210702' and channel=120;

image.png

  1. hive> select *from part_in_bucket;
  2. OK
  3. 8    小明8    ["lol","book"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    110
  4. 4    小明4    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    110
  5. 9    小明9    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    110
  6. 5    小明5    ["lol","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    110
  7. 1    小明1    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    110
  8. 10    小明10    ["lol","book","skill"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    110
  9. 6    小明6    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    110
  10. 2    小明2    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    110
  11. 7    小明7    ["lol","book"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    110
  12. 3    小明3    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    110
  13. 20    小明20    ["lol","book","skill"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    120
  14. 16    小明16    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    120
  15. 12    小明12    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    120
  16. 17    小明17    ["lol","book"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    120
  17. 13    小明13    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    120
  18. 18    小明18    ["lol","book"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    120
  19. 14    小明14    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    120
  20. 19    小明19    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    120
  21. 15    小明15    ["lol","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    120
  22. 11    小明11    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    120
  23. 28    小明28    ["lol","book"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    110
  24. 24    小明24    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    110
  25. 29    小明29    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    110
  26. 25    小明25    ["lol","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    110
  27. 21    小明21    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    110
  28. 30    小明30    ["lol","book","skill"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    110
  29. 26    小明26    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    110
  30. 22    小明22    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    110
  31. 27    小明27    ["lol","book"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    110
  32. 23    小明23    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    110
  33. 48    小明48    ["lol","book"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    120
  34. 44    小明44    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    120
  35. 49    小明49    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    120
  36. 45    小明45    ["lol","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    120
  37. 41    小明41    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    120
  38. 50    小明50    ["lol","book","skill"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    120
  39. 46    小明46    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    120
  40. 42    小明42    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    120
  41. 47    小明47    ["lol","book"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    120
  42. 43    小明43    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    120
  43. Time taken: 0.128 seconds, Fetched: 40 row(s)
  46. 可以看到同一分区的数据顺序错乱了，按照id hash到了不同的桶中

外部表

create external table part_out_bucket (
id int,
name string,
likes array<string>,
address map<string,string>
)
partitioned by(data_dt string,channel int)
clustered by (id) into 4 buckets 
row format delimited
fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':'
location '/var/bigdata/hive_remote/warehouse/part_out_bucket';



//不能用load data
insert overwrite table part_out_bucket partition (data_dt='20210701',channel=110)
  select id,name,likes,address  from part_out where data_dt='20210701' and channel=110;
//不能select * ,会产生6个字段，包括分区列
insert overwrite table part_out_bucket partition (data_dt='20210701',channel=120)
  select id,name,likes,address  from part_out where data_dt='20210701' and channel=120;

insert overwrite table part_out_bucket partition (data_dt='20210702',channel=110)
  select id,name,likes,address  from part_out where data_dt='20210702' and channel=110;

insert overwrite table part_out_bucket partition (data_dt='20210702',channel=120)
  select id,name,likes,address  from part_out where data_dt='20210702' and channel=120;

hive> select * from part_out_bucket;
OK
8    小明8    ["lol","book"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    110
4    小明4    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    110
9    小明9    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    110
5    小明5    ["lol","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    110
1    小明1    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    110
10    小明10    ["lol","book","skill"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    110
6    小明6    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    110
2    小明2    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    110
7    小明7    ["lol","book"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    110
3    小明3    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    110
20    小明20    ["lol","book","skill"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    120
16    小明16    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    120
12    小明12    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    120
17    小明17    ["lol","book"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    120
13    小明13    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    120
18    小明18    ["lol","book"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    120
14    小明14    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    120
19    小明19    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    120
15    小明15    ["lol","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    120
11    小明11    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210701    120
28    小明28    ["lol","book"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    110
24    小明24    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    110
29    小明29    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    110
25    小明25    ["lol","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    110
21    小明21    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    110
30    小明30    ["lol","book","skill"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    110
26    小明26    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    110
22    小明22    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    110
27    小明27    ["lol","book"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    110
23    小明23    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    110
48    小明48    ["lol","book"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    120
44    小明44    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    120
49    小明49    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    120
45    小明45    ["lol","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    120
41    小明41    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    120
50    小明50    ["lol","book","skill"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    120
46    小明46    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    120
42    小明42    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    120
47    小明47    ["lol","book"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    120
43    小明43    ["lol","book","movie"]    {"beijing":"mashibing","shanghai":"pudong"}    20210702    120
Time taken: 0.117 seconds, Fetched: 40 row(s)

spark-sql操作

create table part_in_spark (
id int,
name string,
likes array<string>,
address map<string,string>
)
partitioned by(data_dt string,channel int)
row format delimited
fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':';    


分区字段相当于虚字段，实际数据中不存在，但hive查询时会显示分区列

本地路径，复制文件到hdfs
load data local inpath '/opt/part1.txt' into table part_in_spark partition (data_dt='20210701',channel=110);



create external table part_out_spark (
id int,
name string,
likes array<string>,
address map<string,string>
)
partitioned by(data_dt string,channel int)
row format delimited
fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':'
location '/var/bigdata/hive_remote/warehouse/part_out_spark';


本地路径，复制文件到hdfs
load data local inpath '/opt/part1.txt' overwrite into table part_out_spark partition (data_dt='20210701',channel=110);




create table part_in_spark_bucket (
id int,
name string,
likes array<string>,
address map<string,string>
)
partitioned by(data_dt string,channel int)
clustered by (id) into 4 buckets 
row format delimited
fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':';    



//不能用load data
//
Error in query: Output Hive table `default`.`part_in_spark_bucket` is bucketed but Spark currently does NOT populate bucketed output which is compatible with Hive.;
2.3.4不支持分桶表的插入
//

insert overwrite table part_in_spark_bucket partition (data_dt='20210701',channel=110)
  select id,name,likes,address  from part_in_spark where data_dt='20210701' and channel=110;



create external table part_out_spark_bucket (
id int,
name string,
likes array<string>,
address map<string,string>
)
partitioned by(data_dt string,channel int)
clustered by (id) into 4 buckets 
row format delimited
fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':'
location '/var/bigdata/hive_remote/warehouse/part_out_spark_bucket';



//不能用load data
//
Error in query: Output Hive table `default`.`part_in_spark_bucket` is bucketed but Spark currently does NOT populate bucketed output which is compatible with Hive.;
2.3.4不支持分桶表的插入
//
insert overwrite table part_out_spark_bucket partition (data_dt='20210701',channel=110)
  select id,name,likes,address  from part_out where data_dt='20210701' and channel=110;



对原有hive表操作

load data local inpath '/opt/part1.txt' into table part_in partition (data_dt='20210701',channel=110);
load data local inpath '/opt/part1.txt' [overwrite] into table part_out partition (data_dt='20210701',channel=110);

关于压缩包格式

textFile可以读取gzip和bzip2压缩的数据
但有个问题，压缩包中文件第一行会带有文件元数据信息以及具体文本内容

比如这样

a1       0000644 0000000 0000000 00000000025 14076475501 010006  0                       ustar   root                            root                                                                                                                                                                                                                   a1a11
a2 a22
a3 a33
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           b1                                                                                                  0000644 0000000 0000000 00000000025 14076475516 010015  0                                                                                                    ustar   root                            root                                                                                                                                                                                                                   b1 b11
b2 b22
b3 b33

gzip不能分割，只能一个并行度/分区
bzip2可以分割，可以指定并行度/分区

创建其他表格式


create table pua
(
 id int,
 name string
)
row format delimited
fields terminated by '\t'
stored as PARQUET


查看当前表格式
desc formatted pua
hive> desc formatted pua;
OK
# col_name                data_type               comment             

id                      int                                         
name                    string                                      

# Detailed Table Information          
Database:               default                  
Owner:                  root                     
CreateTime:             Sat Jul 31 14:24:40 CST 2021     
LastAccessTime:         UNKNOWN                  
Retention:              0                        
Location:               hdfs://mycluster/var/bigdata/hive_remote/warehouse/pua     
Table Type:             MANAGED_TABLE            
Table Parameters:          
    COLUMN_STATS_ACCURATE    {\"BASIC_STATS\":\"true\"}
    numFiles                0                   
    numRows                 0                   
    rawDataSize             0                   
    totalSize               0                   
    transient_lastDdlTime    1627712680          

# Storage Information          
SerDe Library:          org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe     
InputFormat:            org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat      // PARQUET
OutputFormat:           org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat      //PARQUET
Compressed:             No                       
Num Buckets:            -1                       
Bucket Columns:         []                       
Sort Columns:           []                       
Storage Desc Params:          
    field.delim             \t                  
    serialization.format    \t                  
Time taken: 0.057 seconds, Fetched: 32 row(s)


    ===================================

hive> desc formatted part_in_spark;
OK
# col_name                data_type               comment             

id                      int                                         
name                    string                                      
likes                   array<string>                               
address                 map<string,string>                          

# Partition Information          
# col_name                data_type               comment             

data_dt                 string                                      
channel                 int                                         

# Detailed Table Information          
Database:               default                  
Owner:                  root                     
CreateTime:             Sat Jul 31 13:57:46 CST 2021     
LastAccessTime:         UNKNOWN                  
Retention:              0                        
Location:               hdfs://mycluster/var/bigdata/hive_remote/warehouse/part_in_spark     
Table Type:             MANAGED_TABLE            
Table Parameters:          
    numFiles                1                   
    numPartitions           1                   
    numRows                 0                   
    rawDataSize             0                   
    spark.sql.create.version    2.3.4               
    spark.sql.sources.schema.numPartCols    2                   
    spark.sql.sources.schema.numParts    1                   
    spark.sql.sources.schema.part.0    {\"type\":\"struct\",\"fields\":[{\"name\":\"id\",\"type\":\"integer\",\"nullable\":true,\"metadata\":{}},{\"name\":\"name\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"likes\",\"type\":{\"type\":\"array\",\"elementType\":\"string\",\"containsNull\":true},\"nullable\":true,\"metadata\":{}},{\"name\":\"address\",\"type\":{\"type\":\"map\",\"keyType\":\"string\",\"valueType\":\"string\",\"valueContainsNull\":true},\"nullable\":true,\"metadata\":{}},{\"name\":\"data_dt\",\"type\":\"string\",\"nullable\":true,\"metadata\":{}},{\"name\":\"channel\",\"type\":\"integer\",\"nullable\":true,\"metadata\":{}}]}
    spark.sql.sources.schema.partCol.0    data_dt             
    spark.sql.sources.schema.partCol.1    channel             
    totalSize               575                 
    transient_lastDdlTime    1627711066          

# Storage Information          
SerDe Library:          org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe     
InputFormat:            org.apache.hadoop.mapred.TextInputFormat        // TEXTFILE
OutputFormat:           org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat      //TEXTFILE
Compressed:             No                       
Num Buckets:            -1                       
Bucket Columns:         []                       
Sort Columns:           []                       
Storage Desc Params:          
    colelction.delim        ,                   
    field.delim             \t                  
    mapkey.delim            :                   
    serialization.format    \t                  
Time taken: 0.097 seconds, Fetched: 48 row(s)
hive>

SparkStreaming

你写的代码都是丢到另一个线程执行的。
_ 作用域分为三个级别：
 application
job
 rdd：task

直接裸露的代码
println(“aaaaaaa”) //application,只执行一次

val res: DStream[(String, Int)] = format.transform( //每job调用一次
(rdd) => {
//我们的函数式每job级别的
println(“bbbbbbbb”) //job driver
_rdd.map(x=>{
_println(“cccccc”) _//task executor
_x
})
}
)
res

res.print() //job