大数据（3d）HIVE基础操作

1、注释方法

SHOW  -- 这是注释
DATABASES;

2、库操作

-- 建库
CREATE DATABASE IF NOT EXISTS school
LOCATION '/school';  -- 文件夹在HDFS的位置
-- 查看有什么库
SHOW DATABASES;
-- 查看指定库
DESC DATABASE school;
-- 切换库
USE school;

-- 删库
DROP DATABASE school CASCADE;

3、建表主要语法

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] 表名
[(列名 数据类型 [COMMENT 列注释], ...)]
[COMMENT 表注释]
[PARTITIONED BY (分区列名 数据类型 [COMMENT 列注释], ...)]
[ROW FORMAT]
    [DELIMITED FIELDS TERMINATED BY 字段分隔]
    [COLLECTION ITEMS TERMINATED BY char 数组分隔]
    [MAP KEYS TERMINATED BY 键值分隔]
    [LINES TERMINATED BY 行分隔]
[STORED AS 数据存储格式]
[LOCATION HDFS路径]
[TBLPROPERTIES(property_name=property_value, ...)]
[AS 查询语句（根据查询结果创建表）]
[LIKE 表名（只复制表结构）]

4、管理表 & 外部表

英文名	MANAGED_TABLE	EXTENDED_TABLE
中文名	管理表	外部表
主要区别	删表时 全删	删表时 只删元数据
应用场景	中间表、结果表	原始表
创建方式	默认	EXTENDED

-- 创建 管理表
CREATE TABLE
school.student1 (age INT) LOCATION '/school/student1';
-- 创建 外部表
CREATE EXTERNAL TABLE
school.student2 (age INT) LOCATION '/school/student2';

建表后，MySQL处可查到

删表前

-- 删表
DROP TABLE school.student1;
DROP TABLE school.student2;

删表后，外部表还在

5、分区表

• 分区表是对应一个HDFS上的独立文件夹
• 查询时可以加WHERE，效率高

1、创建分区表

DROP TABLE IF EXISTS school.student;  --删表
CREATE TABLE school.student (s_id INT)
PARTITIONED BY (grade STRING)  --分区表
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','  --行格式
LOCATION '/school/student'  -- 数据在HDFS的存放位置
;

2、插入数据

INSERT INTO TABLE school.student VALUES (1,'a');
INSERT INTO TABLE school.student VALUES (2,'a');
INSERT INTO TABLE school.student VALUES (3,'b');

3、查询分区

SHOW PARTITIONS school.student;

4、查询加WHERE

SELECT * FROM school.student WHERE grade='a';

5、查看HDFS

6、常用数据类型

常用数据类型	名称	建表示例	值示例	范围
字符串	STRING
整数	INT			-2,147,483,648 to 2,147,483,647
日期	DATE		'2016-11-21'	0000-­01-­01 to 9999-­12-­31
时间戳	TIMESTAMP		'2018-6-11 8:30:00'
数组	ARRAY	ARRAY<STRING>	["ab","cd"]
键值对	MAP	MAP<STRING,INT>	{"k1":3,"k2":2}
小数	DECIMAL	DECIMAL(3,2)	1.23

1、建库、删表建表

--建库
CREATE DATABASE IF NOT EXISTS school LOCATION '/school';
DROP TABLE IF EXISTS school.student;  --删表
CREATE TABLE IF NOT EXISTS school.student  --默认创建 管理表
(
  s_id    INT,            --学号
  s_name  STRING,         --姓名
  birth   DATE,           --生日
  s_time  TIMESTAMP,      --入学时间
  course  ARRAY<STRING>,  --课程
  score   MAP<STRING,INT> --成绩
)
LOCATION '/school/student'  -- 数据在HDFS的存放位置
;

2、查看表信息

SHOW CREATE TABLE school.student;

3、插入数据

INSERT INTO TABLE school.student VALUES(
1001,
'a1',
'1994-1-1',
'2020-1-1 8:30:00',
ARRAY('math','physics'),
MAP('math',6,'physics',7)
);

4、查询结果

7、数据导入

主要方法	说明	可用场景
LOAD DATA	整个文件导入	HDFS导入HIVE数仓原始层
INSERT	查询数据插到目标表	数仓内部增量导入
CREATE	使用查询语句建表	数仓应用层

7.1、导入文件模式

区别	LOCAL模式	非LOCAL模式
数据源	从本地	从HDFS
文件操作	复制	移动（源文件会被删）
适用场景	入门练习	企业生产

1、本地数据准备

vi /root/student.txt

1001,a,1994-1-1,2018-6-11 12:30:00,math_physics,math:7_physics:9
1003,a,1992-11-2,2016-7-11 8:30:00,math_physics,math:8_physics:9
1002,b,1993-3-3,2017-11-1 9:00:00,math_chemistry,math:8_chemistry:6

2、建表

DROP TABLE IF EXISTS school.student;  --删表
CREATE TABLE IF NOT EXISTS school.student
(
  s_id    INT              COMMENT "学号",
  grade   STRING           COMMENT "班级",
  birth   DATE             COMMENT "生日",
  s_time  TIMESTAMP        COMMENT "入学时间",
  course  ARRAY<STRING>    COMMENT "课程",
  score   MAP<STRING,INT>  COMMENT "成绩"
)
COMMENT "学生信息表"
ROW FORMAT --行格式
    DELIMITED FIELDS TERMINATED BY ','  --字段间分割符
    COLLECTION ITEMS TERMINATED BY '_'  --数组内分隔符
    MAP KEYS TERMINATED BY ':'          --键值对分隔符
LOCATION '/school/student'  -- 数据在HDFS的存放位置
;

3、数据导入，LOCAL为本地

LOAD DATA  -- 数据导入
LOCAL  -- 从本地导入，去掉就是默认从HDFS
INPATH '/root/student.txt'  -- 数据来源
OVERWRITE  --覆盖写入，不写的话默认追加
INTO TABLE school.student;

4、查看：文件位置、文件名、表内容

7.2、INSERT模式

1、续上面数据，创建一张按grade分区的表

DROP TABLE IF EXISTS school.student2;  --删表
CREATE TABLE IF NOT EXISTS school.student2 (
  s_id    INT              COMMENT "学号",
  birth   DATE             COMMENT "生日",
  s_time  TIMESTAMP        COMMENT "入学时间",
  course  ARRAY<STRING>    COMMENT "课程",
  score   MAP<STRING,INT>  COMMENT "成绩"
) COMMENT "学生信息表"  --表的注释
PARTITIONED BY (grade STRING)  --分区表
ROW FORMAT --行格式
    DELIMITED FIELDS TERMINATED BY ','  --字段间分割符
    COLLECTION ITEMS TERMINATED BY '_'  --数组内分隔符
    MAP KEYS TERMINATED BY ':'          --键值对分隔符
LOCATION '/school/student2'  -- 数据在HDFS的存放位置
;

2、数据导入

INSERT
OVERWRITE  --OVERWRITE覆盖写入，INTO追加
TABLE school.student2  --数据目的地
PARTITION(grade='a')  --分区
SELECT s_id,birth,s_time,course,score FROM school.student WHERE grade='a';

INSERT
OVERWRITE  --OVERWRITE覆盖写入，INTO追加
TABLE school.student2  --数据目的地
PARTITION(grade='b')  --分区
SELECT s_id,birth,s_time,course,score FROM school.student WHERE grade='b';

3、查看

7.3、CREATE模式

DROP TABLE IF EXISTS school.student3;
CREATE TABLE school.student3
AS SELECT s_id,score from student WHERE course[1]='physics';

查询SELECT * FROM school.student3;