内部表和外部表区别

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

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

查看表类型语句

语句：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/

hive内外表、分区表以及数据导入导出相关操作

内部表和外部表区别

查看表类型语句

内外表转换

建表指定字段分隔符

修改/新增/替换列

数据导入

数据导出

排序4个by总结

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

分桶表

问题汇总

遇到的问题

相关推荐

idea本地配置连接远程hadoop集群的一些网络问题解决汇总

无缓存不行?例行升级的入门级阿斯加特AN2 SSD装机点评

Ceph运维手册(基于P版本)

大数据开发前要做什么准备?8台Hadoop服务器进行集群规划前配置

Tensorflow分类loss函数总结 tensorflow绘制loss曲线

R语言学习笔记(七) -离散型数据的模型预测2

iOS Runtime详解

PHP 远程调试最佳实践

7 个对 Java 意义重大的性能指标，你知道几个?

Laravel框架使用图片处理简单教程