(二)Hive的数据类型，常用 DDL操作，常用DML 操作

Natasha_
276 阅读10分钟

1. 数据类型

1.1 基本数据类型

类型HIVEMySQLJAVA长度例子
整型TINYINTTINYINTbyte1字节整数2
SMALINTSMALINTshort2字节整数20
INT/INTEGERINTint4字节整数20
BIGINTBIGINTlong8字节整数20
字符串类型STRINGVARCHARstring字符串类型“hello world”
VARCHARVARCHARstring字符串类型
CHARVARCHARstring字符串类型
浮点型FLOATFLOATfloat单精度浮点数3.14159
DOUBLEDOUBLEdouble双精度浮点数3.14159
时间类型TIMESTAMPTIMESTAMP时间类型
DATEDATEDATE时间类型
其他类型BINARYBINARY二进制
BOOLEAN无boolean布尔类型TRUE/FALSE

1.2 集合数据类型

数据类型描述语法示例
MAPMAP<primitive_type, data_type>map(“key1”,1)
ARRAYARRAY<data_type>array(“aaa”, ”bbb”, ”ccc”)
STRUCTSTRUCT<col_name : data_type1, data_type2,data_type3>struct(“a”,1,2,3)

Hive的ARRAY和MAP与java中的Array和Map类似，而STRUCT与C语言中的Struct类似，它封装了一个命名字段集合，复杂数据类型允许任意层次的嵌套。

复杂数据类型的声明必须使用<尖括号>指明其中数据字段的类型：

create table t_eg(
         col1 ARRAY<INT>,
         col2 MAP<STRING,INT>,
         col3 STRUCT<a:STRING,b:INT,c:DOUBLE>);

我们用JSON格式来表示其数据结构：

{
    "name": "jack",
    "friends": ["sam", "van"],       //列表Array, 
    "children": {                    //键值Map,
        "chale": 18,
        "davil": 19
    }
    "address": {                     //结构Struct,
        "street": "北京路",
        "phone": 13631230887,
        "money": 246.4
    }
}

1.3 类型转化

Hive的原子数据类型是可以进行隐式转换的，例如某表达式使用INT类型，TINYINT会自动转换为INT类型。

但是Hive不会进行反向转化，除非使用CAST操作。

1.3.1 隐式类型转换规则

  1. 任何整数类型都可以隐式地转换为一个范围更广的整数类型，如TINYINT—>INT，INT—>BIGINT。
  2. 所有整数类型、FLOAT和STRING类型都可以隐式地转换成DOUBLE。
  3. TINYINT、SMALLINT、INT都可以转换为FLOAT。
  4. BOOLEAN类型不可以转换为任何其它的类型。

1.3.2 使用CAST显示转换数据

CAST(‘1’ AS INT) ，将把字符串'1'转换成整数1

2. 常用 DDL 操作

2.1 Database

2.1.1 查看数据列表

show databases;

2.1.2 使用数据库

USE database_name;

2.1.3 新建数据库

语法：

CREATE DATABASE|SCHEMA [IF NOT EXISTS] database_name
	[COMMENT database_comment]                               --数据库注释
	[LOCATION hdfs_path]                                     --存储在 HDFS 上的位置
	[WITH DBPROPERTIES (property_name=property_value, ...)]; --指定额外属性

示例：

CREATE DATABASE IF NOT EXISTS hive_test
	COMMENT 'hive database for test'
	WITH DBPROPERTIES ('create'='范晓敏');

2.1.4 查看数据库信息

语法：

DESC DATABASE [EXTENDED] database_name; --EXTENDED 表示是否显示额外属性

示例：

DESC DATABASE EXTENDED hive_test;

2.1.5 删除数据库

语法：

DROP DATABASE|SCHEMA [IF EXISTS] database_name RESTRICT|CASCADE;
	--默认行为是 RESTRICT，如果数据库中存在表则删除失败。
	--要想删除库及其中的表，可以使用 CASCADE 级联删除。

示例：

DROP DATABASE IF EXISTS hive_test CASCADE;

2.2 创建表

2.2.1 内部表

  CREATE TABLE emp(
    empno INT,
    ename STRING,
    job STRING,
    mgr INT,
    hiredate TIMESTAMP,
    sal DECIMAL(7,2),
    comm DECIMAL(7,2),
    deptno INT)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";

2.2.2 外部表

  CREATE EXTERNAL TABLE emp_external(
    empno INT,
    ename STRING,
    job STRING,
    mgr INT,
    hiredate TIMESTAMP,
    sal DECIMAL(7,2),
    comm DECIMAL(7,2),
    deptno INT)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
    LOCATION '/hive/emp_external';

2.2.3 分区表

  CREATE EXTERNAL TABLE emp_partition(
    empno INT,
    ename STRING,
    job STRING,
    mgr INT,
    hiredate TIMESTAMP,
    sal DECIMAL(7,2),
    comm DECIMAL(7,2)
    )
    -- 按照部门编号进行分区
    PARTITIONED BY (deptno INT)                   
    ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
    LOCATION '/hive/emp_partition';

2.2.4 分桶表

  CREATE EXTERNAL TABLE emp_bucket(
    empno INT,
    ename STRING,
    job STRING,
    mgr INT,
    hiredate TIMESTAMP,
    sal DECIMAL(7,2),
    comm DECIMAL(7,2),
    deptno INT)
    --按照员工编号散列到四个 bucket 中
    CLUSTERED BY(empno) SORTED BY(empno ASC) INTO 4 BUCKETS  
    ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
    LOCATION '/hive/emp_bucket';

2.2.5 倾斜表

通过指定一个或者多个列经常出现的严重偏斜的值，Hive 会自动将涉及到的这些值的数据拆分为单独的文件。

在查询时，如果涉及到倾斜值，它就直接从独立文件中获取数据，而不是扫描所有文件，这使得性能得到提升。

  CREATE EXTERNAL TABLE emp_skewed(
    empno INT,
    ename STRING,
    job STRING,
    mgr INT,
    hiredate TIMESTAMP,
    sal DECIMAL(7,2),
    comm DECIMAL(7,2)
    )
    --指定 empno 的倾斜值 66,88,100
    SKEWED BY (empno) ON (66,88,100)               
    ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
    LOCATION '/hive/emp_skewed';

2.2.6 临时表

临时表仅对当前 session 可见，临时表的数据将存储在用户的暂存目录中，并在会话结束后删除。

如果临时表与永久表表名相同，则对该表名的任何引用都将解析为临时表，而不是永久表。

临时表还具有以下两个限制：

  • 不支持分区列；
  • 不支持创建索引。
  CREATE TEMPORARY TABLE emp_temp(
    empno INT,
    ename STRING,
    job STRING,
    mgr INT,
    hiredate TIMESTAMP,
    sal DECIMAL(7,2),
    comm DECIMAL(7,2)
    )
    ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";

2.2.7 CTAS创建表

从查询到的语句结果去创建表：

CREATE TABLE emp_copy AS SELECT * FROM emp WHERE deptno='20';

2.2.8 复制表结构

语法：

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] target_table_name  --创建表名
	LIKE src_table_name     --被复制表的表名
        [LOCATION hdfs_path];   --存储位置

示例：

CREATE TEMPORARY EXTERNAL TABLE  IF NOT EXISTS  emp_co  LIKE emp

2.2.9 加载数据到表

加载本地数据到表中：

load data local inpath "D:/project/emp.txt" into table emp;

emp.txt 的内容：

7369	SMITH	CLERK	7902	1980-12-17 00:00:00	800.00		20
7499	ALLEN	SALESMAN	7698	1981-02-20 00:00:00	1600.00	300.00	30
7521	WARD	SALESMAN	7698	1981-02-22 00:00:00	1250.00	500.00	30
7566	JONES	MANAGER	7839	1981-04-02 00:00:00	2975.00		20
7654	MARTIN	SALESMAN	7698	1981-09-28 00:00:00	1250.00	1400.00	30
7698	BLAKE	MANAGER	7839	1981-05-01 00:00:00	2850.00		30
7782	CLARK	MANAGER	7839	1981-06-09 00:00:00	2450.00		10
7788	SCOTT	ANALYST	7566	1987-04-19 00:00:00	1500.00		20
7839	KING	PRESIDENT		1981-11-17 00:00:00	5000.00		10
7844	TURNER	SALESMAN	7698	1981-09-08 00:00:00	1500.00	0.00	30
7876	ADAMS	CLERK	7788	1987-05-23 00:00:00	1100.00		20
7900	JAMES	CLERK	7698	1981-12-03 00:00:00	950.00		30
7902	FORD	ANALYST	7566	1981-12-03 00:00:00	3000.00		20
7934	MILLER	CLERK	7782	1982-01-23 00:00:00	1300.00		10

2.3 修改表

2.3.1 重命名表

语法：

ALTER TABLE table_name RENAME TO new_table_name;

示例：

ALTER TABLE emp_temp RENAME TO new_emp; --把 emp_temp表重命名为new_emp

2.3.2 修改列

语法：

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

示例：

-- 修改字段名和类型
ALTER TABLE emp_temp CHANGE empno empno_new INT;
 
-- 修改字段sal的名称 并将其放置到 empno 字段后
ALTER TABLE emp_temp CHANGE sal sal_new decimal(7,2) AFTER ename;

-- 为字段增加注释
ALTER TABLE emp_temp CHANGE mgr mgr_new INT COMMENT 'this is column mgr';

2.3.3 新增列

示例：

ALTER TABLE emp_temp ADD COLUMNS (address STRING COMMENT 'home address');

2.4 清空表/删除表

2.4.1 清空表

语法：

-- 清空整个表或表指定分区中的数据,目前只有内部表才能执行 TRUNCATE 操作
TRUNCATE TABLE table_name [PARTITION (partition_column = partition_col_value,  ...)];

示例：

TRUNCATE TABLE emp_mgt_ptn PARTITION (deptno=20);

2.4.2 删除表

语法：

DROP TABLE [IF EXISTS] table_name [PURGE];
  • 内部表：不仅会删除表的元数据，同时会删除 HDFS 上的数据；
  • 外部表：只会删除表的元数据，不会删除 HDFS 上的数据；

2.5 其他命令

2.5.1 Describe

查看数据库：

Desc DATABASE [EXTENDED] db_name;      --EXTENDED 是否显示额外属性

查看表：

Desc [EXTENDED|FORMATTED] table_name;  --FORMATTED 以友好的展现方式查看表详情

2.5.2 Show

查看数据库列表

语法:

SHOW (DATABASES|SCHEMAS) [LIKE 'identifier_with_wildcards'];

示例：

SHOW DATABASES like 'hive*';

查看表的列表

语法:

SHOW TABLES [IN database_name] ['identifier_with_wildcards'];

示例：

SHOW TABLES IN default;

查看表的分区列表

SHOW PARTITIONS table_name;

查看表/视图的创建语句

SHOW CREATE TABLE (table_name|view_name);

3. 常用 DML 操作

3.1 加载文件数据到表

语法：

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

  • LOCAL 关键字代表从本地文件系统加载文件，省略则代表从 HDFS 上加载文件：

  • filepath 可以是文件路径 (在这种情况下 Hive 会将文件移动到表中)，也可以目录路径 (在这种情况下，Hive 会将该目录中的所有文件移动到表中)；从本地文件系统加载文件时， filepath 可以是绝对路径也可以是相对路径 (建议使用绝对路径)从 HDFS 加载文件时候，filepath 为文件完整的 URL 地址：如 hdfs://namenode:port/user/hive/project/ data1

  • 如果使用 OVERWRITE 关键字，则将删除目标表（或分区）的内容，使用新的数据填充；不使用此关键字，则以追加的方式加入；

  • 加载的目标可以是表或分区。如果是分区表，则必须指定加载数据的分区；

  • 加载文件的格式必须与建表时使用 STORED AS 的存储格式相同。

示例：

  CREATE TABLE emp_ptn(
    empno INT,
    ename STRING,
    job STRING,
    mgr INT,
    hiredate TIMESTAMP,
    sal DECIMAL(7,2),
    comm DECIMAL(7,2)
    deptno INT)
    PARTITIONED BY (deptno INT)                      -- 按照部门编号进行分区
    ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";

从 HDFS 上加载数据到分区表：

LOAD DATA  INPATH "hdfs://hadoop001:8020/mydir/emp.txt" OVERWRITE INTO TABLE emp_ptn PARTITION (deptno=20);

加载后表中数据如下,分区列 deptno 被全部赋值成 20：

https://github.com/heibaiying

3.2 查询结果插入到表

演示数据准备：

CREATE TABLE emp(
    empno INT,
    ename STRING,
    job STRING,
    mgr INT,
    hiredate TIMESTAMP,
    sal DECIMAL(7,2),
    comm DECIMAL(7,2),
    deptno INT)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";
--加载数据到 emp 表中 这里直接从本地加载
load data local inpath "/usr/file/emp.txt" into table emp;

https://github.com/heibaiying

3.2.1 静态分区

语法：

INSERT OVERWRITE/INTO TABLE table_name PARTITION (partcol1[=val1], partcol2[=val2] ...) select statement FROM from_statement;

在向分区表插入数据时候，分区列名是必须的。有分区列值，称为静态分区。

示例：

INSERT OVERWRITE TABLE emp_ptn PARTITION (deptno=20) SELECT empno,ename,job,mgr,hiredate,sal,comm FROM emp WHERE deptno=20;

https://github.com/heibaiying

3.2.2 动态分区

语法：

INSERT OVERWRITE/INTO TABLE table_name PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement;

在向分区表插入数据时候，分区列名是必须的。没有分区列值，称为动态分区。动态分区列必须在 SELECT 语句的列中最后指定，并且与它们在 PARTITION( ) 子句中出现的顺序相同。

示例：

INSERT OVERWRITE TABLE emp_ptn PARTITION (deptno) SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno FROM emp WHERE deptno=30;

https://github.com/heibaiying

3.3 使用SQL语句插入数据

语法：

INSERT INTO TABLE tablename [PARTITION (partcol1[=val1], partcol2[=val2] ...)] VALUES ( value...] )

3.4 更新和删除数据

语法：

-- 更新
UPDATE tablename SET column = value [column = value ...] [WHERE expression]
-- 删除
DELETE FROM tablename [WHERE expression]

更新和删除的语法比较简单，和关系型数据库一致，需要注意的是这两个操作都只能在支持 ACID 的表，也就是事务表上才能执行。

3.5 插入，更新和删除数据示例

首先需要更改 hive-site.xml，添加如下配置，开启事务支持，配置完成后需要重启 Hive 服务。

<property>
    <name>hive.support.concurrency</name>
    <value>true</value>
</property>
<property>
    <name>hive.enforce.bucketing</name>
    <value>true</value>
</property>
<property>
    <name>hive.exec.dynamic.partition.mode</name>
    <value>nonstrict</value>
</property>
<property>
    <name>hive.txn.manager</name>
    <value>org.apache.hadoop.hive.ql.lockmgr.DbTxnManager</value>
</property>
<property>
    <name>hive.compactor.initiator.on</name>
    <value>true</value>
</property>
<property>
    <name>hive.in.test</name>
    <value>true</value>
</property>
复制代码

然后创建用于测试的事务表，建表时候指定属性 transactional = true 则代表该表是事务表。需要注意的是，按照官方文档 的说明，目前 Hive 中的事务表有以下限制：

  • 必须是 buckets Table;
  • 仅支持 ORC 文件格式；
  • 不支持 LOAD DATA ...语句。
CREATE TABLE emp_ts(  
  empno int,  
  ename String
)
CLUSTERED BY (empno) INTO 2 BUCKETS STORED AS ORC
TBLPROPERTIES ("transactional"="true");

-- 插入数据
INSERT INTO TABLE emp_ts  VALUES (1,"ming"),(2,"hong");

插入数据依靠的是 MapReduce 作业，执行成功后数据如下：

https://github.com/heibaiying

-- 更新数据
UPDATE emp_ts SET ename = "lan"  WHERE  empno=1;

-- 删除数据
DELETE FROM emp_ts WHERE empno=2;

更新和删除数据依靠的也是 MapReduce 作业，执行成功后数据如下：

https://github.com/heibaiying

3.6 查询结果写出到文件系统

语法：

INSERT OVERWRITE [LOCAL] DIRECTORY directory1 [ROW FORMAT row_format] [STORED AS file_format]
  • OVERWRITE 关键字表示输出文件存在时，先删除后再重新写入；
  • 和 Load 语句一样，建议无论是本地路径还是 URL 地址都使用完整的；
  • 写入文件系统的数据被序列化为文本，其中列默认由 ^A 分隔，行由换行符分隔。
  • 如果列不是基本类型，则将其序列化为 JSON 格式。
  • 其中行分隔符不允许自定义，但列分隔符可以自定义。

示例：

这里我们将上面创建的 emp_ptn 表导出到本地文件系统，语句如下：

INSERT OVERWRITE LOCAL DIRECTORY '/usr/file/ouput'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'     -- 定义列分隔符为'\t' 
SELECT * FROM emp_ptn;

https://github.com/heibaiying

avatar
文章
阅读
粉丝