内部表和外部表区别

内部表：删除表时，既会删除表结构，也会删除表数据。

外部表：删除表时，只会删除表结构，表数据不删除，外部表用的比较多。

查看表类型语句

语句：desc formatted 表名;

Table Type:          MANAGED_TABLE

内外表转换

转换成外部表：alter table test set tblproperties('EXTERNAL'='TRUE');
转换成内部表：alter table test set tblproperties('EXTERNAL'='FALSE');

注意：('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')为固定写法，区分大小写！

建表指定字段分隔符

脚本: row format delimited fields terminated by ','

#例如: 创建表
create table test1 (id int, name string)
row format delimited
fields terminated by ',';
#插入数据
insert into test1 values(110, '哈哈哈');

修改/新增/替换列

• 修改列ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name
  column_type [COMMENT col_comment] [FIRST|AFTER column_name]
  
  例如:
  hive (hive)> desc test1;
  col_name data_type comment
  id int
  name string
  hive (hive)> alter table test1 change id stu_id string;
  hive (hive)> desc test1;
  col_name data_type comment
  stu_id string
  name string
• 新增/替换列ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT
  col_comment], ...)
  
  例: 新增列
  hive (hive)> desc test1
  col_name data_type comment
  stu_id string
  name string
  hive (hive)> alter table test1 add columns (no string);
  hive (hive)> desc test1;
  col_name data_type comment
  stu_id string
  name string
  no string
  
  例: 替换列
  hive (hive)> alter table test1 replace columns (id string, name string);注：ADD 是代表新增一字段，字段位置在所有列后面(partition 列前)， REPLACE 则是表示替换表中所有字段, 类型问题。

数据导入

• 表存在-load导入方式（overwrite是删除在添加）语法：load data [local] inpath '数据的 path' [overwrite] into table
  student [partition (partcol1=val1,…)];例：
  # 1. 创建表
  hive (default)> create table test1(id string, name string) row format delimited fields terminated by ',';
  # 2. 准备测试文件test1.txt
  1001,xiaomi
  1002,xiaoli
  # 3. 本地load导入hive
  hive (default)> load data local inpath '/usr/local/hive/test1.txt' into table test1;
  # 4. 从hdfs上，load覆盖导入hive
  hadoop fs -put test1.txt /
  load data inpath '/test1.txt' overwrite into table test1;
• 表存在-insert导入方式语法：
  1. insert into 表名 values(),();
  2. insert into 表名 select 字段名/* from 表名
  3. insert overwrite table 表名 select 字段名/* from 表名
• 建表时-as select导入方式create table test3 as select id,name from test2;
• 建表时-指定location导入方式 （hdfs上的文件夹，不是文件）# 1. hdfs上创建文件夹
  hadoop fs -mkdir /test2
  # 2. 上传文件到hdfs上
  hadoop fs -put test2.txt /test2
  # 3. 建表指定location导入数据
  hive (default)> create table test4 (id string, name string) row format delimited fields terminated by ',' location '/test2';

数据导出

• inser导出到本地和hdfs方式语法：
  insert overwrite [local] directory 路径
  row format delimited fields terminated by ','
  select * from 表名;
  
  例：导出到本地文件夹
  hive (default)> insert overwrite local directory '/usr/local/hive/student' row format delimited fields terminated by ',' select * from test4;
  
  例：导出到hdfs文件夹
  hive (default)> insert overwrite directory '/student' row format delimited fields terminated by ',' select * from test4;
• hadoop命令导出到本地（get命令）hadoop fs -get '/user/hive/warehouse/test3/000000_0' /tmp/1.txt
• hive shell 命令导出到本地hive -e 'select * from default.test4' > /tmp/2.txt;
• exprot/import导出导入hdfs上# 导出到hdfs的文件夹
  hive (default)> export table test4 to '/ss/test4';
  # 从hdfs的文件夹导入到表，test5可以是不存在的表，或者空表。但是不能有数据
  hive (default)> import table test5 from '/ss/test4';

排序4个by总结

• order by ：全局排序，只有一个reducer。
• Sort By(每个 Reduce 内部排序)：Sort By：对于大规模的数据集 order by 的效率非常低。在很多情况下，并不需要全局排 序，此时可以使用 sort by。 Sort by 为每个 reducer 产生一个排序文件。每个 Reducer 内部进行排序，对全局结果集 来说不是排序。
• 设置 reduce 个数0: jdbc:hive2://nn:10000> set mapreduce.job.reduces=3;
• 查看设置 reduce 个数0: jdbc:hive2://nn:10000> set mapreduce.job.reduces;
• 根据部门编号降序查看员工信息0: jdbc:hive2://nn:10000> select * from emp sort by deptno desc;
• 将查询结果导入到文件中（按照部门编号降序排序），查看文件0: jdbc:hive2://nn:10000> insert overwrite local directory '/usr/local/hive/emp' row format delimited fields terminated by ' ' select * from emp sort by deptno desc;

• Distribute By（分区）Distribute By： 在有些情况下，我们需要控制某个特定行应该到哪个 reducer，通常是为 了进行后续的聚集操作。distribute by 子句可以做这件事。distribute by 类似 MR 中 partition （自定义分区），进行分区，结合 sort by 使用。 对于 distribute by 进行测试，一定要分配多 reduce 进行处理，否则无法看到 distribute by 的效果。0: jdbc:hive2://nn:10000> set mapreduce.job.reduces=3;
  0: jdbc:hive2://nn:10000> insert overwrite local directory '/usr/local/hive/emp' select * from emp distribute by deptno sort by empno desc;注意：distribute by 的分区规则是根据分区字段的 hash 码与 reduce 的个数进行模除后， 余数相同的分到一个区。Hive 要求 DISTRIBUTE BY 语句要写在 SORT BY 语句之前。
• Cluster By当 distribute by 和 sorts by 字段相同时，可以使用 cluster by 方式。 cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。但是排序只能是升序 排序，不能指定排序规则为 ASC 或者 DESC。
• 准备测试数据# 1. dept.txt文件
  10,ACCOUNTING,1700
  20,RESEARCH,1800
  30,SALES,1900
  40,OPERATIONS,1700
  # 2. emp.txt文件
  7369,SMITH,CLERK,7902,1980-12-17,800.0,20.0,20
  7499,ALLEN,SALESMAN,7698,1981-2-20,1600.0,300.0,30
  7521,WARD,SALESMAN,7698,1981-2-22,1250.0,500.0,10
  7566,JONES,MANAGER,7839,1981-4-2,2975.0,20.0,40
  7654,MARTIN,SALESMAN,7698,1981-9-28,1250.0,1400.0,10
  7698,BLAKE,MANAGER,7839,1981-5-1,2850.0,30.0,20
  7782,CLARK,MANAGER,7839,1981-6-9,2450.0,10.0,30
  7788,SCOTT,ANALYST,7566,1987-4-19,3000.0,20.0,40
  7839,KING,PRESIDENT,7566,1987-4-19,10.0,20.0,30
  7844,TURNER,SALESMAN,7698,1981-9-8,1500.0,0.0,20
  7876,ADAMS,CLERK,7788,1987-5-23,1100.0,20.0,30
  7900,JAMES,CLERK,7698,1981-12-3,950.0,30.0,20
  7902,FORD,ANALYST,7566,1981-12-3,3000.0,20.0,10
  7934,MILLER,CLERK,7782,1982-1-23,1300.0,10.0,30
  
  
  # 3. 建dept、emp表
  hive (default)> create table dept (deptno int, dname string, loc int) row format delimited fields terminated by ',';
  
  hive (default)> load data local inpath '/usr/local/hive/company/dept.txt' into table dept;
  
  hive (default)> create table if not exists emp(
  empno int,
  ename string,
  job string,
  mgr int,
  hiredate string,
  sal double,
  comm double,
  deptno int)
  row format delimited fields terminated by ',';
  
  hive (default)> load data local inpath '/usr/local/hive/company/emp.txt' into table emp;

分区表(隔离数据和优化查询)

• 创建分区表(partitioned by)create table dept_par(deptno string, dname string, loc string) partitioned by (day string) row format delimited fields terminated by ',';
  注意：分区字段不能是表中已经存在的数据，可以将分区字段看作表的伪列。
• 加载数据到分区表# 1. 准备测试数据
  dept_20210909.txt
  10,ACCOUNTING,1700
  20,RESEARCH,1800
  
  dept_20210910.txt
  30,SALES,1900
  40,OPERATIONS,1700
  
  dept_20210911.txt
  50,TEST,2000
  60,DEV,1900
  
  # 2. 导入数据到分区表
  load data local inpath '/opt/data/partition/dept_20210909.txt' into table dept_par partition(day='20210909');
  load data local inpath '/opt/data/partition/dept_20210910.txt' into table dept_par partition(day='20210910');
  load data local inpath '/opt/data/partition/dept_20210911.txt' into table dept_par partition(day='20210911');
• 查看分区表数据# 1. 单分区查询
  select * from dept_par where day='20210909';
  # 2. 多分区查询(union/or)
  select * from dept_par where day='20210909'
  union
  select * from dept_par where day='20210910';
  
  select * from dept_par where day='20210909' or day='20210911';
• 增加分区alter table dept_par add partition(day='20210912') partition(day='20210913');
• 删除分区(partition之间的逗号)alter table dept_par drop partition(day='20210912'),partition(day='20210913');
• 查看分区show partitions dept_par;
• 创建多级分区# 1. 创建多级分区
  create table dept_par2 (deptno string, dname string, loc string) partitioned by (day string, hour string) row format delimited fields terminated by ',';
  # 2. 加载数据到分区
  load data local inpath '/opt/data/partition/dept_20210909.txt' into table dept_par2 partition(day='20210909', hour='13');
• 上传数据到hdfs上分区目录，分区表与数据关联的3种方式(缺少分区元数据）# 1. 上传数据后修复
  hadoop fs -mkdir /user/hive/warehouse/dept_par3/day=20210909
  hadoop fs -put dept_20210909.txt /user/hive/warehouse/dept_par3/day=20210909/
  msck repair table dept_par3;
  
  # 2. 上传数据后添加分区
  hadoop fs -mkdir /user/hive/warehouse/dept_par3/day=20210910
  hadoop fs -put dept_20210909.txt /user/hive/warehouse/dept_par3/day=20210910/
  alter table dept_par3 add partition(day='20210910');
  
  # 3. 创建分区文件夹后，load数据到分区
  hadoop fs -mkdir /user/hive/warehouse/dept_par3/day=20210911
  load data local inpath '/opt/data/partition/dept_20210911.txt' into table dept_par3 partition(day='20210911');
• 动态分区# 1. 开启动态分区功能（默认 true，开启）
  hive.exec.dynamic.partition=true
  # 2. 设置为非严格模式（动态分区的模式，默认 strict，表示必须指定至少一个分区为静态分区，nonstrict 模式表示允许所有的分区字段都可以使用动态分区。）
  hive.exec.dynamic.partition.mode=nonstrict
  # 3. 在所有执行 MR 的节点上，最大一共可以创建多少个动态分区。默认 1000
  hive.exec.max.dynamic.partitions=1000
  # 4. 在每个执行 MR 的节点上，最大可以创建多少个动态分区。该参数需要根据实际的数据来设定。比如：源数据中包含了一年的数据，即 day 字段有 365 个值，那么该参数就需要设置成大于 365，如果使用默认值 100，则会报错。
  hive.exec.max.dynamic.partitions.pernode=100
  # 5. 整个 MR Job 中，最大可以创建多少个 HDFS 文件。默认 100000
  hive.exec.max.created.files=100000
  # 6. 当有空分区生成时，是否抛出异常。一般不需要设置。默认 false
  hive.error.on.empty.partition=false
  
  例：
  # 导入数据，动态分区
  insert into table dept_par4 partition(day) select deptno,dname,loc from dept_par3;
  
  # 不写partition(day)，hive3新特性
  insert into table dept_par4 select deptno,dname,loc from dept_par3;

分桶表

分区针对的是数据的存储路径；分桶针对的是数据文件。配合抽样算法

# 1. 创建分桶表
create table stu_buck(id int, name string) clustered by(id) into 4 buckets row format delimited fields terminated by ',';

问题汇总

遇到的问题

• 问题1： java.lang.NoSuchMethodError: com.google.common.base.Preconditions.checkArgument(ZLjava/lang/String;Ljava/lang/Object;)V

[root@b76e475d5e8a hive]#bin/schematool -dbType derby -initSchema
Exception in thread "main" java.lang.NoSuchMethodError: com.google.common.base.Preconditions.checkArgument(ZLjava/lang/String;Ljava/lang/Object;)V
at org.apache.hadoop.conf.Configuration.set(Configuration.java:1357)
at org.apache.hadoop.conf.Configuration.set(Configuration.java:1338)
at org.apache.hadoop.mapred.JobConf.setJar(JobConf.java:536)
at org.apache.hadoop.mapred.JobConf.setJarByClass(JobConf.java:554)
at org.apache.hadoop.mapred.JobConf.<init>(JobConf.java:448)
at org.apache.hadoop.hive.conf.HiveConf.initialize(HiveConf.java:5141)
at org.apache.hadoop.hive.conf.HiveConf.<init>(HiveConf.java:5104)
at org.apache.hive.beeline.HiveSchemaTool.<init>(HiveSchemaTool.java:96)
at org.apache.hive.beeline.HiveSchemaTool.main(HiveSchemaTool.java:1473)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.hadoop.util.RunJar.run(RunJar.java:323)
at org.apache.hadoop.util.RunJar.main(RunJar.java:236)

# 解决guava.jar版本冲突(hadoop和hive)
mv $HIVE_HOME/lib/guava-19.0.jar $HIVE_HOME/lib/guava-19.0.jar.bak
cp $HADOOP_HOME/share/hadoop/common/lib/guava-27.0-jre.jar $HIVE_HOME/lib/