大数据开发学习1.3-Hive的DDL和DML

老土拨鼠
242 阅读6分钟

Hive数据类型

基本数据类型

HiveMySQLJava长度例子
tinyinttinyintbyte1byte有符号整数2
smallintsmallintshort2byte有符号整数20
intintint4byte有符号整数2000
bigintbigintlong8byte有符号整数20000000
boolean无boolean布尔类型，true或者falsetrue false
floatfloatfloat单精度浮点数3.14159
doubledoubledouble双精度浮点数3.14159
stringvarcharstring字符系列。可以指定字符集。可以使用单引号或者双引号。'now is the time'"for all good men"
timestamptimestamp时间类型
binarybinary字节数组

集合数据类型

数据类型描述语法示例
struct结构体由一组称为成员的不同数据组成，其中每个成员可以具有不同的类型。通常用来表示类型不同但是又相关的若干数据。例如，如果某个列的数据类型是struct{first string, last string}，那么第1个元素可以通过字段.first来引用。struct()例如struct<street:string, city:string>
mapmap是一组键-值对元组集合，使用数组表示法可以访问数据。例如，如果某个列的数据类型是map，其中键->值对是'first'->'John'和'last'->'Doe'，那么可以通过字段名['last']获取最后一个元素map()例如map<string, int>
array数组是一组具有相同类型和名称的变量的集合。这些变量称为数组的元素，每个数组元素都有一个编号，编号从零开始。例如，数组值为['John', 'Doe']，那么第2个元素可以通过数组名[1]进行引用。array()例如array

类型转化

Hive的原子数据类型是可以进行隐式转换的，类似于Java的类型转换，例如某表达式使用int类型，tinyint会自动转换为int类型，但是Hive不会进行反向转化，例如，某表达式使用tinyint类型，int不会自动转换为tinyint类型，它会返回错误，除非使用cast操作。

  • 隐式类型转换规则如下

    • 任何整数类型都可以隐式地转换为一个范围更广的类型，如tinyint可以转换成int，int可以转换成bigint
    • 所有整数类型、float和string类型都可以隐式地转换成double
    • tinyint、smallint、int都可以转换为float
    • boolean类型不可以转换为任何其它的类型

  • 可以使用cast操作显示进行数据类型转换

    语法：cast(expr as )

    返回值：Expected "=" to follow "type"

    说明：返回转换后的数据类型

DDL（Data Definition Language）数据定义

数据库操作

创建数据库

CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];

创建一个数据库，数据库在HDFS上的默认存储路径是/user/hive/warehouse/*.db

案例：

create database if not exists db_hive
comment "this is my first db"
with dbproperties ("dbname"="db_hive","owner"="user");

create database db_hive;

--创建数据库，并指定在hdfs上的路径
create database if not exists db_hive2
location '/db_hive2';

查询数据库

show databases；

查看数据库详情

--简单查看
desc database 数据库名;
--详细查看 （详细查看可以看到库的属性信息，简单查看看不到）
desc database extended 数据库名;

切换数据库

use db_hive;

修改数据库

用户可以使用alter database命令为某个数据库的dbproperties设置键-值对属性值，来描述这个数据库的属性信息。数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置

alter database db_hive set dbproperties('createtime'='20220822');

删除数据库

drop database if exists db_hive;

如果数据库不为空，可以采用cascade命令，强制删除

drop database db_hive cascade;

创建表

语法

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name          --指定表名  【external 外部表/内部表】
[(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 DELIMITED      --指定hive表在hdfs上存储的原始数据的格式
 [FIELDS TERMINATED BY char]     --每行数据中字段的分隔符  默认值：ascII码表的第一个字符  ^A 
 [COLLECTION ITEMS TERMINATED BY char]   --集合元素分隔符  默认值：ascII码表的第二个字符  ^B
 [MAP KEYS TERMINATED BY char]  --map集合中 key 和 value 的分隔符    默认值：ascII码表的第三个字符  ^C
 [LINES TERMINATED BY char]      --每行数据的分隔符     默认值：'\n'
]  
[STORED AS file_format]         --指定hive的数据在hdfs上存储的格式
[LOCATION hdfs_path]            --指定hive数据在hdfs上存储的路径  默认值 /user/hive/warehouse/数据库名
[TBLPROPERTIES (property_name=property_value, ...)]    --指定表的属性
[AS select_statement]    --按照as后面的查询语句的结果来创建表，复制表结构以及表数据
[LIKE table_name]     --按照like后面的表结构来创建表，只复制表结构，不复制表数据

管理表和外部表

管理表（内部表）：hive掌控者这个数据的生命周期，如果删除一个管理表，hdfs上存储的数据也跟着一起删除。所以一般我们创建管理表时，一般不会再location表的存储路径，就默认放在/user/hive/warehouse下

外部表：hive不完全掌控外部表的数据的生命周期，删除外部表，只删除hive表的元数据，不会删除掉hdfs上存储的数据

一般外部表都是先有的hdfs上的数据，然后我们创建一个外部表，手动指定这个外部表的存储路径

修改表

语法

ALTER TABLE table_name RENAME TO new_table_name

增加/修改/替换列信息

更新列，列名可以随意修改，列的类型只能小改大，不能大改小（遵循自动转换规则）。

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment]

增加和替换列

ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)

注意：ADD是代表新增一个字段，字段位置在所有列后面（partition列前），REPLACE则是表示替换表中所有字段，REPLACE使用的时候，字段的类型要跟之前的类型对应上，数量可以减少或者增加，其实就是包含了更新列，增加列，删除列的功能。

删除表

drop table student;

清除表

注意：truncate只能删除管理表，不能删除外部表中数据

truncate table student;

DML（Data Manipulation Language）数据操作

导入

通过load命令加载数据

load data [local] inpath '数据的path' [overwrite] into table student [partition (partcol1=val1,…)];

通过本地方式导入

注意：通过本地路径load，本质上其实就是将本地的文件put到hdfs对应的表目录

load data local inpath '/opt/module/hive/datas/student.txt' overwrite into table student;

通过hdfs路径导入

注意：通过hdfs路径导入，本质是将hdfs上路径的数据剪切到对应的表的hdfs路径

load data inpath '/student.txt' into table student;

覆盖导入

注意:加上关键字overwrite 就是覆盖导入，不加的话，默认是追加导入

load data local inpath '/opt/module/hive/datas/student.txt' overwrite into table student;

insert

基本模式插入

into是追加插入，overwrite是覆盖插入，此方式一般没人用

insert into/overwrite table student values(1018,'ss18'),(1019,'ss19');

通过查询结果插入

此方式用的比较多，一般都是查询原始表的数据到临时表，注意select之前不能加as，跟创建表时as select区分开

insert into table student2  select * from student where id < 1006;
insert overwrite table student2  select * from student where id < 1006;

注意：通过inset插入数据，数据格式和列的数量要一致才可以。

建表语句后+ as select

注意：建表语句后跟select语句时，as不能省略，跟上面的insert into刚好相反

create table student3 as  select * from student;

建表指定location位置

提前把数据传到hdfs上，然后创建表的时候，指定表的位置为数据的路径

注意 location后面一定要给一个目录，不能直接给文件路径。

create table student5(
id int,name string
)
row format delimited fields terminated by '\t'
location '/student';

import导入

注意：使用import导入之前，得使用export导出数据，并且因为export会把数据和元数据一起导出，所以我们使用import导入的时候，表不能存在，否则会元数据冲突，报错。

import table student6  from '/stu';

数据导出

insert 导出

默认格式导出

insert overwrite local directory '/opt/module/hive/datas/export/student'
select * from student;

格式化导出

insert overwrite local directory '/opt/module/hive/datas/export/student2'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
select * from student;

注意：因为insert导出，后面只能跟overwrite，所以我们给路径的时候，一定要具体，防止hive误删重要文件、

这个导出路径可以不存在，hive会帮我们创建路径

hadoop命令导出

hadoop dfs -get /user/hive/warehouse/student/student.txt
/opt/module/hive/datas/export/student3.txt;

hive shell 命令导出

hive -e 'select * from db_hive.student' > /opt/module/hive/datas/stu.txt

export 导出

注意;export导出只能导出到hdfs上，并且会将元数据和数据一起导出。

export table db_hive.student to '/stu';
avatar
文章
阅读
粉丝