©2020-2024 ioDraw All rights reserved
<>1 数据库
[ ] 里的都是可选的操作。
<>1.1 创建数据库
语法：
create database [if not exists] database_name [comment database_comment(注释)] [
location hdfs_path] [with dbproperties (property_name-property=property_value,..
.)];
如：
create database db_hive1; create database db_hive2 location '/db_hive2';
create database db_hive3 location '/db_hive3' with dbproperties ('create_date'='
2023-5-12');
如果不指定路径则默认路径为：${hive.metastore.warehouse.dir}/database_name.db
<>1.2 查询数据库
（1）查看数据库
语法：
show databases [like 'identifier_with_wildcards'];
模糊匹配：like通配表达式说明：*表示任意个任意字符，|表示或的关系。
如：
show databases like 'db_hive*';
（2）查看数据库信息
语法：
describe database [extended] db_name;
[extended]：是否要展示更详细信息。
如：
desc database db_hive3; desc database extended db_hive3;
<>1.3 修改数据库
用户可以使用 alter database 命令修改dbproperties、location、owner user信息。
PS：在修改location时不会改变当前已有表的路径信息，只改变后续创建的新表的默认父目录。
语法：
--修改dbproperties: alter database database_name set dbproperties (property_name=
property_value,...); --修改location: alter database database_name set location
hdfs_path; --修改owner user: alter database database_name set owner user user_name
;
如：
alter database db_hive3 set dbproperties ('create_date'='2023-5-12');
<>1.4 删除数据库
语法：
drop database [if not exists] database_name [restrict|cascade];
restrict：严格模式，若数据库不为空，则删除失败，默认为严格模式。
cascade：级联模式，若数据库不为空，则会把库中的表一起删除。
如：
--删除空数据库： drop database db_hive2; --删除非空数据库： drop database db_hive3 cascade;
<>1.5 切换当前数据库
语法：
use database_name;
<>2 表
<>2.1 创建表
<>2.1.1 普通建表
语法：
create [temporary] [external] table [if not exists] [db_name.]table_name [(
col_name data_type[comment col_comment], ...)] [comment table_comment] [
partitioned by(col_name data_type [comment col_comment], ...)] [clustered by (
col_name,col_name, ...) [sorted by (col_name [asc|desc], ...)] into num_buckets
buckets] [row format row_format] [sorted as file_format] [location hdfs_path] [
tblproperties(property_name=property_value, ...)]
关键字说明：
（1）temporary：
临时表，该表只在当前会话可见，会话结束表会被删除，常用于测试。
（2）external（重点）：
表示外部表：Hive只接管元数据，而不完全接管HDFS中的数据；在删除表时只有元数据会被删除，而hdfs文件不会被删除。
而内部表（管理表）：Hive会完全接管该表，包括元数据和HDFS中的数据；在删除表时会把hdfs数据和元数据一起删除。
（3）data_type（重点）：
Hive中字段类型分为基本数据类型和复杂数据类型。
基本数据类型：
复杂数据类型：
类型转换：
Hive的基本数据类型可以进行类型转换。
隐式转换：
用户不需要显式修改sql语句，hive会根据预置规则完成转换。
表中小的类型可以转为大的类型。
规则：
（i）任何整数类型可以隐式转换为范围更广的类型：tinyint->int->bigint。
（ii）所有整数类型、float和string可以隐式转为double。
（iii）boolean不可以转为任何其它类型。
如：select ‘1’+1; 结果为double类型的2，隐式转换中会找到两者可以参与转换的最小数据类型进行转换。
显式转换：
由用户显示修改sql语句，可以借助cast函数完成显式类型转换。
语法：
cast(expr as <type>) --如string->int select cast('111' as int); select '1' +2,
cast('1' as int) +2; --结果： _c0 _c1 3.0 3
（4）partitioned by（重点）：
创建分区表（可以加快查询），将一张表的数据按照指定的分区字段分到不同的路径里，之后的一个路径就代表hive表的一个分区。
分区根据：指定字段相同的值放到同一个分区，或按日期分区。
（5）clustered by … sorted by … into … buckets（重点）：
创建分桶表（可以加快查询），将hive一张表的数据分散存储到多个文件中。
分散规则：指定一个或多个字段（用clustered by
…），和指定buckets个数（一个buckets一个文件），对id进行取模运算并放到buckets中，sorted by … 表示对分桶里的数据进行排序。
（6）row format（重点）：
指定SERDE，SERDE是Serializer and Deserializer的简写。Hive使用SERDE序列化和反序列化每行数据。
Hive表的读数据流程：HDFS files --> InputFileFormat --> <key, value> --> Deserializer
--> Row object
Hive表的写数据流程：Row object --> Serializer --> <key, value> --> OutputFileFormat
--> HDFS files
语法一：
row format delimited [fields terminated by char] [collection items terminated
bychar] [map keys terminated by char] [lines terminated by char] [null defined
aschar]
关键字说明：
（i）delimited：
表示对文件中的每个字段按照特定分割符进行分割，其会使用默认的SERDE对每行数据进行序列化和反序列化。
（ii）fields terminated by：列分割符。
（iii）collection items terminated by
：map、struct（结构体只会保存字段的值，每个字段的分隔符）和array中每个元素之间的分隔符。
（iv）map keys terminated by：map中的key与value的分隔符。
（v）lines terminated by：行分隔符。
（vi）null defined as：如果某个字段为null时也必须要存储，默认”\n”。
语法二：
row format serde serde_name [with serdeproperties(property_name=property_value,
...)]
serde：用于指定其它内置的serde或者用户自定义的serde。如JSON
SERDE可用于出来json字符串，serde_name为全列名（用于序列化和反序列化）。
（7）stored as（重点）：
指定文件格式，常用的文件格式有，textfile（默认值），sequence file，orc file(列式存储)、parquet
file(列式存储)等等，stored as 声明的文件格式由hive自动进行转换操作。
（8）location：
指定表所对应的HDFS路径，若不指定路径，其默认值为${hive.metastore.warehouse.dir}/db_name.db/table_name。
（9）tblproperties：
用于配置表的一些键值对参数。
案例
（1）内部表：
Hive中默认 创建的表都是内部表（管理表），Hive会完全管理表的元数据和数据文件。
在 /opt/module/hive/datas/下创建一个txt文件：
vim /opt/module/hive/datas/student.txt 1001 student1 1002 student2 1003
student31004 student4 1005 student5 1006 student6
创建内部表：
create table if not exists student( id int, name string ) row format delimited
fields terminated by'\t' localtion '/user/hive/warehouse/student';
上传文件到hive表的指定路径：
hadoop fs -put student.txt /user/hive/warehouse/student
删除表，观察HDFS中的数据文件是否存在:
drop table student; --HDFS中的数据被删除
（2）外部表：
外部表用于处理其它工具上传的数据文件，只负责管理元数据，不负责管理HDFS中的数据文件。
创建外部表：
create external table if not exists student( id int, name string ) roe format
delimited fields terminated by'\t' location '/user/hive/warehouse/student';
上传文件到Hive表指定的路径：
hadoop fs -put syudent.txt /user/hive/warehouse/student
删除表，观察HDFS中的数据文件是否存在：
drop table student; --HDFS中的数据还存在，因为删除外部表只会删除元数据，不会删除与之相关的HDFS数据文件。
（3）SERDE和复杂数据类型：
要求：
JSON格式的文件交由Hive处理分析。
在/opt/module/hive/datas/下创建teacher.txt文件：
{ "name":"zhangsan" "friends":[ "lisi", "wangwu" ], "student":{ "chenming":20,
"ligui":23 }, "address":{ "city":"beijing", "street":"jingsong", "postal_code":
10010 } }
使用 json serde设计表字段，表字段与JSON字符串的一级字段一样；使用复杂数据类型保持JSON中的嵌套结构，创建表：
create table teacher ( name string, friends array<string>, student map<string,
int>, address struct<city:string,street:string,postal_code:int> ) row format
serde 'org.apache.hadoop.hive.serde2.JsonSerDe' location '/user/hive/warehouse/
teacher';
将teacher.txt文件上传到Hive表的指定路径：
hadoop fs -put teacher.txt /user/hive/warehouse/teacher
在复杂数据类型的字段中取值：
select name form teacher; --取array中的值： select friends[0] from teacher; select
size(friends) from teacher; --取map<string,int>里的字段值： select map_keys(students)
from teacher; select map_values(students) from teacher; --判断map中是否含有某个key的值：
selectarray_contains(map_keys(students),'ligui') from taecher; --取struct里的字段值：
select address.city from teacher;
<>2.1.2 create table as select (CTAS)建表
用create table as select 建表之后有数据，且只能创建内部表。
用select查询的结果直接建表，新建的表结构和查询语句的结构保持一致，且select查询的结构放入新建的表中。
语法：
create [temporary] table [if not exists] table_name [comment table_comment] [
row format row_format] [stored as file_format] [location hdfs_path] [
tblproperties(property_name=property_value, ...)] [as select_statement]
如：
create table teacher1 as select * from teacher;
<>2.1.3 create table like建表
允许用户复刻一张以及存在的表结构，且创建的新表无数据。
create [temporary] [external] table [if not exists] [db_name.]table_name [like
exist_table_name] [row format row_format] [stored as file_format] [location
hdfs_path] [tblproperties (property_name=property_value, ...)]
如：
create table teacher2 like teacher;
<>2.2 查看表
<>2.2.1 查看完整表的创建语句
语法：
show create table table_name; ROW FORMAT SERDE ‘org.apache.hadoop.hive.serde2.
lazy.LazySimpleSerDe’ 对一行数据进行序列化和反序列化 STORED AS INPUTFORMAT stored
as与文件底层格式有关，因为读写数据流，声明怎么读写文件 ‘org.apache.hadoop.mapred.TextInputFormat’
OUTPUTFORMAT ‘org.apache.hadoop.hive.sq.io.HiveIgnoreKeyTextOutputFormat’
LOCATION ‘hdfs://hadoop102:8020/user/hive/warehouse/stu’ TBLPROPERTIES(
‘bucketing_version’=’2’, ‘last_modified_by’=’liaoyanxia’ ‘last_modified_time’=’
1668755681’ ‘transient_clastDdlTime’=’1668755681’)
<>2.2.2 查看所有表
语法：
show tables [in database_name] like ['identifier_with_wildcards'];
like：通配表达式说明，*表示任意个任意字符，|表示或。
如：
show tables like 'db_hive*';
<>2.2.3 查看表信息
语法：
describe [extended | formatted] [db_name.]table_name;
extended：展示详细信息。
formatted：对详细信息进行格式化展示。
如：
--查看基本信息： desc stu; --查看更多信息： desc formatted stu;
<>2.3 修改表
<>2.3.1 重命名表
语法：
alter table table_name rename to new_table_name;
如：
alter table stu rename to stu1;
<>2.3.2 修改列信息
语法：
--增加列：新增列位于末尾 alter table table_name add columns (col_name data_type [comment
col_comment], ...); --更新列：修改列名、数据类型、注释信息以及在表中的位置 alter table table_name change [
column] col_old_name col_new_name column_type [comment col_comment] [first|
after column_name]; --替换列 alter table table_name replace columns (col_name
data_type[comment col_comment], ...);
如：
--添加列： alter table stu add columns(age int); --更新列： alter table stu change
column age agesdouble; --替换列： alter table stu replace columns(id int,name string
); --查询表结构： desc stu;
<>2.4 删除表
语法：
drop table [if not exists] table_name;
如：
drop table stu;
<>2.5 清空表
语法：
truncate [table] table_name;
truncate只能清空内部表（管理表），不能删除外部表中的数据。
如：
truncate table student;