Two Hive表操作及分区

最新推荐文章于 2024-01-20 20:42:29 发布

龙箬

最新推荐文章于 2024-01-20 20:42:29 发布

阅读量325

点赞数

分类专栏： Hive数据仓库文章标签： hadoop hdfs hive sql 数据库

本文链接：https://blog.csdn.net/weixin_43975035/article/details/110943120

版权

Hive数据仓库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

Author:龙箬
数据科学与大数据技术专业
CSDN@weixin_43975035
纵有千万人在前，我要去，那便去！

Hive表操作及分区

此篇Hive博客在大数据云平台上完成，与在自己电脑上搭建的Hadoop平台上的Hive安装部署部分不同。
输入su，使用root用户
在这里插入图片描述

# service mysql start //启动mysql服务

在这里插入图片描述

# hive  //进入hive

在这里插入图片描述

1 hive sql的用法

1.DDL操作类型
•建表
•删除表
•修改表结构
•创建／删除视图
•创建数据库
•显示命令
2.建表：
语法：
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] 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]
[STORED AS file_format]
[LOCATION hdfs_path];
•CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXIST 选项来忽略这个异常
•EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION）
•LIKE 允许用户复制现有的表结构，但是不复制数据
•COMMENT可以为表与字段增加描述
•ROW FORMAT
DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, …)]
用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive 通过 SerDe 确定表的具体的列的数据。
•STORED AS
SEQUENCEFILE
| TEXTFILE
| RCFILE
| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCE 。
3.创建简单表：

hive> CREATE TABLE pokes (foo INT, bar STRING);

在这里插入图片描述

hive> CREATE TABLE events (foo INT, bar STRING);

在这里插入图片描述

4.创建外部表：
语法：

CREATE EXTERNAL TABLE page_view(viewTime INT, userid BIGINT,
     page_url STRING, referrer_url STRING,
     ip STRING COMMENT 'IP Address of the User',
     country STRING COMMENT 'country of origination')
COMMENT 'This is the staging page view table'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\054'
STORED AS TEXTFILE
LOCATION '<hdfs_location>'

在这里插入图片描述

5.建分区表

CREATE TABLE par_table(viewTime INT, userid BIGINT,
     page_url STRING, referrer_url STRING,
     ip STRING COMMENT 'IP Address of the User')
COMMENT 'This is the page view table'
PARTITIONED BY(start_date STRING, pos STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS SEQUENCEFILE;

在这里插入图片描述
6.建Bucket表

CREATE TABLE par_table2(viewTime INT, userid BIGINT,
     page_url STRING, referrer_url STRING,
     ip STRING COMMENT 'IP Address of the User')
COMMENT 'This is the page view table'
PARTITIONED BY(start_date STRING, pos STRING)
CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS SEQUENCEFILE;

在这里插入图片描述
7.创建表并创建索引字段ds

hive> CREATE TABLE invites (foo INT, bar STRING) PARTITIONED BY (ds STRING);

在这里插入图片描述

8.复制一个空表
语法：
　　CREATE TABLE empty_key_value_store
LIKE key_value_store;
例子：

create table  user_info (user_id int, cid string, ckid string, username string)
row format delimited
fields terminated by '\t'
lines terminated by '\n';

在这里插入图片描述
导入数据表的数据格式是：字段之间是tab键分割，行之间是断行。
及要我们的文件内容格式：
100636 100890 c5c86f4cddc15eb7 yyyvybtvt
100612 100865 97cc70d411c18b6f gyvcycy
100078 100087 ecd6026a15ffddf5 qa000100
9.显示所有表：

hive> SHOW TABLES;

在这里插入图片描述
10.按正条件（正则表达式）显示表

hive> SHOW TABLES '.*s';

在这里插入图片描述

2 DDL类型-修改表结构

•增加分区、删除分区
•重命名表
•修改列的名字、类型、位置、注释
•增加/更新列
•增加表的元数据信息

1.表添加一列：

hive> ALTER TABLE pokes ADD COLUMNS (new_col INT);

在这里插入图片描述
2.添加一列并增加列字段注释

hive> ALTER TABLE invites ADD COLUMNS (new_col2 INT COMMENT 'a comment');

在这里插入图片描述
3.更改表名：

hive> ALTER TABLE events RENAME TO 3koobecaf;

在这里插入图片描述
4.增加、删除分区
•增加
语法：
ALTER TABLE table_name ADD [IF NOT EXISTS] partition_spec [ LOCATION ‘location1’ ] partition_spec [ LOCATION ‘location2’ ] …
partition_spec:
: PARTITION (partition_col = partition_col_value, partition_col = partiton_col_value, …)
•删除
语法：
ALTER TABLE table_name DROP partition_spec, partition_spec,…
5.重命名表
语法：
ALTER TABLE table_name RENAME TO new_table_name
6.修改列的名字、类型、位置、注释：
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
这个命令可以允许改变列名、数据类型、注释、列位置或者它们的任意组合
7.增加/更新列
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], …)
• ADD是代表新增一字段，字段位置在所有列后面(partition列前)
REPLACE则是表示替换表中所有字段。
8.增加表的元数据信息
　ALTER TABLE table_name SET TBLPROPERTIES table_properties table_properties:
:[property_name = property_value……]
•用户可以用这个命令向表中增加metadata
9.改变表文件格式与组织
ALTER TABLE table_name SET FILEFORMAT file_format
　　ALTER TABLE table_name CLUSTERED BY(userid) SORTED BY(viewTime) INTO num_buckets BUCKETS
这个命令修改了表的物理存储属性
10.删除表：

hive> DROP TABLE pokes;

在这里插入图片描述
11.创建／删除视图
CREATE VIEW [IF NOT EXISTS] view_name [ (column_name [COMMENT column_comment], …) ][COMMENT view_comment][TBLPROPERTIES (property_name = property_value, …)] AS SELECT
•增加视图
•如果没有提供表名，视图列的名字将由定义的SELECT表达式自动生成
•如果修改基本表的属性，视图中不会体现，无效查询将会失败
•视图是只读的，不能用LOAD/INSERT/ALTER
•DROP VIEW view_name
•删除视图
12.创建数据库

CREATE DATABASE name

在这里插入图片描述
13.显示命令

•show tables;

在这里插入图片描述

•show databases;

在这里插入图片描述

•show partitions ; #展示表格之下所有的分区信息

在这里插入图片描述

•show functions;

在这里插入图片描述

•describe extended table_name;

在这里插入图片描述

3 DML 操作：元数据储存

hive不支持用insert语句一条一条的进行插入操作，也不支持update操作。数据是以load的方式加载到建立好的表中。数据一旦导入就不可以修改。
DML包括：INSERT插入、UPDATE更新、DELETE删除
•向数据表内加载文件
•将查询结果插入到Hive表中
•0.8新特性 insert into
1.向数据表内加载文件
•LOAD DATA [LOCAL] INPATH ‘filepath’ [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 …)]
•Load 操作只是单纯的复制/移动操作，将数据文件移动到 Hive 表对应的位置。
•filepath
•相对路径，例如：project/data1
•绝对路径，例如： /user/hive/project/data1
•包含模式的完整 URI，例如：hdfs://namenode:9000/user/hive/project/data1
例如：
创建pokes表并向数据表加载文件

hive> CREATE TABLE pokes (foo INT, bar STRING);
hive> LOAD DATA LOCAL INPATH '/data/hive/data/kv1.txt' OVERWRITE INTO TABLE pokes;

在这里插入图片描述
2.加载本地数据，同时给定分区信息
•加载的目标可以是一个表或者分区。如果表包含分区，必须指定每一个分区的分区名
•filepath 可以引用一个文件（这种情况下，Hive 会将文件移动到表所对应的目录中）或者是一个目录（在这种情况下，Hive 会将目录中的所有文件移动至表所对应的目录中）
LOCAL关键字
•指定了LOCAL，即本地
•load 命令会去查找本地文件系统中的 filepath。如果发现是相对路径，则路径会被解释为相对于当前用户的当前路径。用户也可以为本地文件指定一个完整的 URI，比如：file:///user/hive/project/data1.
•load 命令会将 filepath 中的文件复制到目标文件系统中。目标文件系统由表的位置属性决定。被复制的数据文件移动到表的数据对应的位置
例如：加载本地数据，同时给定分区信息：

hive> LOAD DATA LOCAL INPATH '/data/hive/data/kv2.txt' OVERWRITE INTO TABLE invites PARTITION (ds='2008-08-15');

在这里插入图片描述
• 没有指定LOCAL
如果 filepath 指向的是一个完整的 URI，hive 会直接使用这个 URI。否则
•如果没有指定 schema 或者 authority，Hive 会使用在 hadoop 配置文件中定义的 schema 和 authority，fs.default.name 指定了 Namenode 的 URI
•如果路径不是绝对的，Hive 相对于 /user/ 进行解释。 Hive 会将 filepath 中指定的文件内容移动到 table （或者 partition）所指定的路径
3.加载HDFS数据，同时给定分区信息：
上传文件到hdfs中

#hdfs  dfs  -put /data/hive/data/kv2.txt  /user

在这里插入图片描述
加载hdfs数据

hive> LOAD DATA INPATH '/user/kv2.txt' OVERWRITE INTO TABLE invites PARTITION (ds='2008-08-15');

在这里插入图片描述
The above command will load data from an HDFS file/directory to the table. Note that loading data from HDFS will result in moving the file/directory. As a result, the operation is almost instantaneous.
OVERWRITE
•指定了OVERWRITE
•目标表（或者分区）中的内容（如果有）会被删除，然后再将 filepath 指向的文件/目录中的内容添加到表/分区中。
•如果目标表（分区）已经有一个文件，并且文件名和 filepath 中的文件名冲突，那么现有的文件会被新文件所替代。
4.将查询结果插入Hive表
•将查询结果插入Hive表
•将查询结果写入HDFS文件系统
•基本模式
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 …)] select_statement1 FROM from_statement
•多插入模式
FROM from_statement
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 …)] select_statement1
[INSERT OVERWRITE TABLE tablename2 [PARTITION …] select_statement2] …
•自动分区模式
INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] …) select_statement FROM from_statement
5.将查询结果写入HDFS文件系统
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 SELECT … FROM …
FROM from_statement
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1
[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2]
•数据写入文件系统时进行文本序列化，且每列用^A 来区分，\n换行
6.INSERT INTO
INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 …)] select_statement1 FROM from_statement

4 新建hive列表及hive分区

1.metastore
Hive 中建立的表都叫metastore表。这些表并不真实的存储数据，而是定义真实数据跟hive之间的映射，就像传统数据库中表的meta信息，所以叫做metastore。实际存储的时候可以定义的存储模式有四种：
内部表（默认）
分区表
桶表
外部表
2.建表语句支持的类型
1 基本数据类型
tinyint / smalint / int /bigint
float / double
boolean
string
2 复杂数据类型
Array/Map/Struct
没有date /datetime
3.建完的表存在哪里呢？
在 /user/hive/warehouse 里面，可以通过hdfs来查看建完的表位置

# hdfs dfs -ls /user/hive/warehouse

在这里插入图片描述
一个文件夹对应一个metastore表

5 Hive 各种类型表使用

1.内部表
举个例子，这是一个简单内部表的语句

CREATE TABLE workers(id INT, name STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\054';

在这里插入图片描述
通过这样的语句就建立了一个内部表叫 workers，并且分隔符是逗号， \054 是ASCII 码
•我们可以通过 show tables; 来看看有多少表，其实hive的很多语句是模仿mysql的，当你们不知道语句的时候，把mysql的语句拿来基本可以用。除了limit比较特殊，在后面会说介绍limit
建立完后，我们试着插入几条数据。这边要告诉大家Hive不支持单句插入的语句，必须批量，所以不要指望能用insert into workers values (1,‘jack’) 这样的语句插入数据。hive支持的插入数据的方式有两种：
从文件读取数据
从别的表读出数据插入(insert from select)
•这里我采用从文件读数据进来。先建立一个叫 worker.csv的文件（找个目录创建文件）

# mkdir /home/file/                         创建目录
# touch /home/file/workers.csv              创建文件
# vi /home/file/workers.csv                 
# cat /home/file/workers.csv                显示文件内容

在这里插入图片描述
•用LOAD DATA 导入到Hive的表中

注意
不要少了那个 LOCAL ， LOAD DATA LOCAL INPATH 跟 LOAD DATA INPATH 的区别是一个是从你本地磁盘上找源文件，一个是从hdfs上找文件
如果加上OVERWRITE可以再导入之前先清空表，比如

LOAD DATA LOCAL INPATH '/home/file/workers.csv' OVERWRITE INTO TABLE workers;

在这里插入图片描述
•查询一下数据

hive> select * from workers;

在这里插入图片描述
•我们去看下导入后在hive内部表是怎么存的

# hdfs dfs -ls /user/hive/warehouse/workers/

在这里插入图片描述
就是原封不动的把文件拷贝进去.
2.分区表
• Hive使用select语句进行查询的时候一般会扫描整个表内容，会消耗很多时间做没必要的工作。Hive可以在创建表的时候指定分区空间，这样在做查询的时候就可以很好的提高查询的效率。
•比如你的数据非常多，但是你的应用场景是基于这些数据做日报表，那你就可以根据日进行分区，当你要做2017-06-06的报表的时候只需要加载2017-06-06这一天的数据就行了。我们来创建一个分区表来看下

create table partition_employee(id int, name string)  
partitioned by(daytime string)  
row format delimited fields TERMINATED BY '\054';

在这里插入图片描述
partitioned by(daytime string)：按日期来分区
可以看到分区的属性，并不是任何一个列
我们先建立2个测试数据文件，分别对应两天的数据

vi /home/file/2017-06-06.csv

在这里插入图片描述
编辑内容：
66,super,2017-06-06
77,now,2017-06-06

vi /home/file/2017-06-07.csv

在这里插入图片描述
88,today,2017-06-07
99,tomorrow,2017-06-07

•导入到分区表里面

hive> LOAD DATA LOCAL INPATH '/home/file/2017-06-06.csv' INTO TABLE partition_employee partition(daytime='2017-06-06');

在这里插入图片描述

hive> LOAD DATA LOCAL INPATH '/home/file/2017-06-07.csv' INTO TABLE partition_employee partition(daytime='2017-06-07');

在这里插入图片描述
导入的时候通过 partition 来指定分区。
•查询的时候通过指定分区来查询

hive> select * from partition_employee where daytime='2017-06-06';

在这里插入图片描述
•这里的查询语句并没有什么特别的语法，hive 会自动判断你的where语句中是否包含分区的字段。而且可以使用大于小于等运算符

hive> select * from partition_employee where daytime>='2017-06-06';

在这里插入图片描述
•可以看看存储的结构

# hdfs dfs -ls /user/hive/warehouse/partition_employee

在这里插入图片描述
•我们试试二维的分区表

hive> create table p_student(id int, name string)  
    > partitioned by(daytime string,country string)  
    > row format delimited fields TERMINATED BY '\054';

在这里插入图片描述
•插入一些数据

•数据导入hive

hive> LOAD DATA LOCAL INPATH '/home/file/2017-08-08-CN.csv' INTO TABLE p_student partition(daytime='2017-08-08',country='CN');

在这里插入图片描述

hive> LOAD DATA LOCAL INPATH '/home/file/2017-08-09-CN.csv' INTO TABLE p_student partition(daytime='2017-08-09',country='CN');

在这里插入图片描述

hive> LOAD DATA LOCAL INPATH '/home/file/2017-08-09-EN.csv' INTO TABLE p_student partition(daytime='2017-08-09',country='EN');

在这里插入图片描述
•查询一下数据

hive> select * from p_student;

在这里插入图片描述

hive> select * from p_student where daytime='2017-08-09' and country='EN';

在这里插入图片描述
•看一下存储结构

# hdfs dfs -ls /user/hive/warehouse/p_student

在这里插入图片描述

在文件系统中可以看出，分区daytime是一个文件夹，分区country是分区daytime的一个子文件夹（分区是作为文件夹的形式存在的）
•删除分区

alter table p_studnet drop if exists partition(daytime='2017-08-09',country='EN');

在这里插入图片描述
3.桶表
桶表是根据某个字段的hash值，来将数据扔到不同的“桶”里面。外国人有个习惯，就是分类东西的时候摆几个桶，上面贴不同的标签，所以他们取名的时候把这种表形象的取名为桶表。桶表表专门用于采样分析
• hive采用列值哈希，然后除以桶的个数以求余的方式决定该条记录存放在哪个桶当中。
•使用桶的好处：
①、获得更高的查询处理效率。桶为表加上了额外的结构，Hive在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接（Map-side join）高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少JOIN的数据量。
②、使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。
需要特别注意的是：clustered by和sorted by不会影响数据的导入，这意味着，用户必须自己负责数据如何如何导入，包括数据的分桶和排序。
因为分区表跟桶表是可以同时使用的，所以这个例子中同时使用了分区跟桶两种特性

hive> CREATE TABLE b_student(id INT, name STRING) 
    > PARTITIONED BY(dt STRING, country STRING) 
    > CLUSTERED BY(id) SORTED BY(name) INTO 4 BUCKETS 
    > row format delimited fields TERMINATED BY '\054';

在这里插入图片描述
按照id分成四个桶，按照name排序存储；
正确理解：
所有桶，先PARTITIONED BY(dt STRING, country STRING)；
再CLUSTERED BY(id) SORTED BY(name) INTO 4 BUCKETS
•插入数据

hive> LOAD DATA LOCAL INPATH '/home/file/2017-08-08-CN.csv' INTO TABLE b_student partition(dt='2017-08-08',country='CN');

在这里插入图片描述

hive> LOAD DATA LOCAL INPATH '/home/file/2017-08-09-CN.csv' INTO TABLE b_student partition(dt='2017-08-09',country='CN');

在这里插入图片描述

hive> LOAD DATA LOCAL INPATH '/home/file/2017-08-09-EN.csv' INTO TABLE b_student partition(dt='2017-08-09',country='EN');

在这里插入图片描述

hive> select * from b_student;

此处由于LOAD DATA LOCAL INPATH ‘/home/file/2017-08-08-CN.csv’ INTO TABLE b_student partition(dt=‘2017-08-08’,country=‘CN’)执行两遍，故数据出现重复
在这里插入图片描述
•从4个桶中采样抽取一个桶的数据

hive> select * from b_student tablesample(bucket 1 out of 4 on id);

在这里插入图片描述
tablesample 是抽样语句，语法如下：
tablesample(bucket x out of y)
y 必须是 table 中 BUCKET 总数的倍数或者因子。hive根据y的大小，决定抽样的比例。例如，table总bucket数为32，tablesample(bucket 3 out of 16)，表示总共抽取（32/16=）2个bucket的数据，分别为第3个bucket和第（3+16=）19个bucket的数据。
查看文件结构会发现所有的数据分成四个桶。当从桶表中进行查询时，hive会根据分桶的字段进行计算分析出数据存放的桶中，然后直接到对应的桶中去取数据，这样做就很好的提高了效率。
4.外部表
1 内部表和外部表的区
内部表：Hive中的表和关系型数据库中的表在概念上很类似，每个表在HDFS中都有相应的目录用来存储表的数据，这个目录可以通过/conf/hive-site.xml配置文件中的hive.metastore.warehouse.dir属性来配置，这个属性默认的值是在HDFS上的/user/hive/warehouse，所有的Hive内部表存储在这个位置。
外部表：
Hive中的外部表和内部表很类似，但是其数据不是放在自己表所属的目录中，而是存放到别处，这样的好处是如果你要删除这个外部表，该外部表所指向的数据是不会被删除的，它只会删除外部表对应的元数据；而如果你要删除内部表，则对应的所有数据包括元数据都会被删除。
所以，一般情况下还是建立外部表比较好。
2 通过实例测试内外部表的区别
•本地建立数据

在这里插入图片描述
•创建内部表

hive> create table hivedb_storage
    > (id int, name string, age int,weizhi string)
    > ROW FORMAT DELIMITED
    > FIELDS TERMINATED BY '\t'
    > STORED AS TEXTFILE;

在这里插入图片描述
•加载数据

hive> load data local inpath '/home/file/data_storage.txt' into table hivedb_storage;

在这里插入图片描述

•外部表

hive> create external table hiveexter_storage
> (id int, name string, age int,weizhi string);

在这里插入图片描述
外部表创建create external table
•插入数据

hive> load data local inpath '/home/file/data_storage.txt' into table hiveexter_storage;

在这里插入图片描述
•删除表

hive> drop table hivedb_storage;

在这里插入图片描述

hive> drop table hiveexter_storage;

在这里插入图片描述

5. 查询数据语法
•显示条数
展示x条数据，用的还是limit

hive> select * from p_student limit 3;

在这里插入图片描述
但是不支持起点，比如offset。

>exit;   #退出hive

在这里插入图片描述

本实验大数据技术平台来自于本人所在高校机器学习实验室
如有侵权，请联系侵删
需要本实验源代码和实验报告的小伙伴请联系QQ：2225872659

龙箬

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Two Hive表操作及分区

Author:龙箬数据科学与大数据技术专业CSDN@weixin_43975035纵有千万人在前，我要去，那便去！Hive表操作及分区此篇Hive博客在大数据云平台上完成，与在自己电脑上搭建的Hadoop平台上的Hive安装部署部分不同。输入su，使用root用户# service mysql start //启动mysql服务# hive //进入hive1 hive sql的用法1.DDL操作类型•建表•删除表•修改表结构•创建／删除视图•创建数据库•显示.
复制链接

扫一扫

专栏目录