内部表与外部表
CREATE TABLE... 表示创建的是内部表
内部表
-- 内部表
CREATE table if not exists stu(id int,name string);
INSERT INTO STU values(1,"陈平安"),(2,"宁姚");
SELECT * FROM STU;
-- 自行指定列之间的分隔符
CREATE table if not exists stu2(id int,name string)
row format delimited fields terminated by '\t'; -- 固定写法，略显臭长
INSERT INTO STU2 values(1,"陈平安"),(2,"宁姚");
SELECT * FROM STU2;
-- 查看表类型和详情
desc formatted stu2 ;
MANAGED_TABLE -- 表示内部表
-- 删除表后，表关系和数据均被删除
DROP table stu2;
外部表
CREATE external table...
external：表示创建的是外部表
1、先创建外部表，然后移动数据
-- 1、先创建外部表，然后移动数据
-- 建表
-- 必须使用row format delimited fields terminated by指定列分隔符（分隔符是为了与数据保持一致）
-- 必须使用LOCATION指定数据路径
CREATE external table test_ext1(id int,name string)
row format delimited fields terminated by '\t'
LOCATION '/tmp/test_ext1/';
-- 本地手动创建数据，需要注意数据的分隔符需要与建表时设置的分隔符保持一致
-- 上传本地数据至 hdfs 的 /tmp/test_ext1/ 目录下
-- 数据上传成功后，表、数据均存在，即可查询验证，看到数据结果
SELECT * FROM test_ext1 ;
-- 查看表类型和详情
desc formatted test_ext1 ;
EXTERNAL_TABLE -- 表示外部表
2、先上传数据，后创建外部表
-- 2、先上传数据，后创建外部表
-- 本地手动创建数据，需要注意数据的分隔符需要与建表时设置的分隔符保持一致
-- 上传本地数据至 hdfs 的 /tmp/test_ext2/ 目录下
-- 建表
CREATE external table test_ext2(id int,name string)
row format delimited fields terminated by '\t'
LOCATION '/tmp/test_ext2/';
-- 建表成功后，表、数据均存在，即可查询验证，看到数据结果
SELECT * FROM test_ext2 ;
-- 查看表类型和详情
desc formatted test_ext1 ;
EXTERNAL_TABLE -- 表示外部表
-- 删除表后，hdfs处数据文件不会被删除，删除的只是表关系，物理层面不受影响
DROP table test_ext2;
内部表与外部表的相互转换
-- 内部表与外部表之间的转换
-- 'EXTERNAL' = 'TRUE' 为固定写法，必须大写，不可更改
desc formatted stu ;
desc formatted test_ext1 ;
ALTER table stu set TBLPROPERTIES ('EXTERNAL' = 'TRUE');
ALTER table test_ext1 set TBLPROPERTIES ('EXTERNAL' = 'FALSE');
数据加载
1、LOAD 语法
-- 1、LOAD语法
-- 建表
CREATE TABLE test_load(
dt STRING comment '时间（时分秒）',
user_id STRING comment '用户ID',
word STRING comment '搜索词',
url STRING comment '用户访问网址'
) comment '搜索引擎日志表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
-- 加载数据
-- 从本地加载数据，数据不移动
LOAD DATA LOCAL INPATH '/home/hadoop/tmpdata/search_log.txt' INTO TABLE myhive.test_load;
-- 从 hdfs 加载数据，数据本身是被移动的
LOAD DATA INPATH '/tmp/search_log.txt' INTO TABLE MYHIVE.TEST_LOAD ;
-- OVERWRITE，加载数据时，覆盖已存在数据
LOAD DATA INPATH '/tmp/search_log.txt' OVERWRITE INTO TABLE MYHIVE.TEST_LOAD ;
SELECT * FROM test_load ;
2、INSERT INTO 语法
-- 2、INSERT INTO 语法
-- 建表
CREATE TABLE test_load2(
dt STRING comment '时间（时分秒）',
user_id STRING comment '用户ID',
word STRING comment '搜索词',
url STRING comment '用户访问网址'
) comment '搜索引擎日志表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
-- INTO 是追加模式
INSERT INTO TABLE TEST_LOAD2
SELECT * FROM TEST_LOAD ;
-- OVERWRITE 是覆盖模式
INSERT OVERWRITE TABLE TEST_LOAD2
SELECT * FROM TEST_LOAD ;
SELECT * FROM TEST_LOAD2 TL ;
数据导出
1、INSERT OVERWRITE 方式
-- 1、INSERT OVERWRITE 方式
-- 将查询结果导出到本地 - 使用默认分隔符
INSERT OVERWRITE LOCAL directory '/home/hadoop/tmpdata/'
SELECT * FROM TEST_LOAD ;
-- 查询的结果导出到本地 - 指定列分隔符
INSERT OVERWRITE LOCAL directory '/home/hadoop/tmpdata/'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
SELECT * FROM TEST_LOAD ;
-- 将查询的结果导出到HDFS上(不带local关键字)
INSERT OVERWRITE directory '/tmp/export/'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
SELECT * FROM TEST_LOAD ;
2、hive shell 方式
-- 2、hive shell 方式
-- > 表重定向，将输出结果重定向到 目标文件中
-- "-e"表示执行 字符串中的sql语句
bin/hive -e "select * from myhive.test_load;" > /home/hadoop/export3/export4.txt
-- "-f"表示执行 指定的sql文件
bin/hive -f export.sql > /home/hadoop/export4/export4.txt
分区表
partitioned by (month STRING，...)
month STRING：指定的分区列，分区类型，可以有多个
单分区表
-- 创建 单分区表 ，分区按月分区
CREATE TABLE score(
id STRING,
cid STRING ,
score STRING
)
partitioned by (month STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
-- 加载数据到分区表
LOAD DATA LOCAL INPATH '/home/hadoop/tmpdata/score.txt' INTO TABLE MYHIVE.SCORE partition (month = '202408');
LOAD DATA LOCAL INPATH '/home/hadoop/tmpdata/score.txt' INTO TABLE MYHIVE.SCORE partition (month = '202407');
SELECT * FROM SCORE S ;
多分区表
-- 创建 多分区表 ，分区层次：年、月、日
CREATE TABLE score2(
id STRING,
cid STRING ,
score STRING
)
partitioned by (year STRING , month STRING , day STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
-- 加载数据到分区表
LOAD DATA LOCAL INPATH '/home/hadoop/tmpdata/score.txt'
INTO TABLE MYHIVE.SCORE2
partition (year = '2024' , month = '08' , day = '05');
LOAD DATA LOCAL INPATH '/home/hadoop/tmpdata/score.txt'
INTO TABLE MYHIVE.SCORE2
partition (year = '2024' , month = '08' , day = '06');
SELECT * FROM SCORE2 S ;
分桶表
clustered by (c_id) c_id：指定的分桶字段
INTO 3 buckets 3：指定的分桶数量
- 问：为什么只能使用 INSERT INTO 方法加载数据？
- 答：需要使用 INSERT INTO 触发 MapReduce 进行
hash 取模运算，基于分桶列的值，确定哪一条数据进入哪一个桶文件中。
-- 开启分桶的自动优化
set hive.enforce.bucketing=true;
-- 创建分桶表
CREATE TABLE course(
c_id STRING,
c_name STRING ,
t_id STRING
)
clustered by (c_id)
INTO 3 buckets
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
-- 加载数据
-- 1、创建临时中转表
CREATE TABLE course_tmp(
c_id STRING,
c_name STRING ,
t_id STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
-- 2、向中转表 load data 数据
LOAD DATA LOCAL INPATH '/home/hadoop/tmpdata/course.txt' INTO TABLE MYHIVE.COURSE_TMP ;
SELECT * FROM COURSE_TMP ;
-- 3、从中转表进行 insert select 向分桶表加载数据
INSERT INTO TABLE COURSE
SELECT * FROM COURSE_TMP cluster by (c_id);
SELECT * FROM COURSE ;
复杂数据类型
Array
COLLECTION ITEMS TERMINATED BY ',';
‘,’：表示array值之间的分隔符是 ,
-- 创建一个 array 测试表
CREATE TABLE test_array (
name STRING,
work_locations array<STRING >
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ',';
-- 加载数据
LOAD DATA LOCAL INPATH '/home/hadoop/tmpdata/data_for_array_type.txt'
INTO TABLE test_array ;
-- 查询所有数据
SELECT * FROM test_array ;
函数
- 数组[数字序号]：可以取出指定需要元素（从0开始）
- size(数组)：可以统计数组元素个数
- array_contains(数组, 数据)：可以查看指定数据是否在数组中存在
-- 查询loction数组中第一个元素
SELECT name,work_locations [4]
FROM test_array ;
-- 查询location数组中元素的个数
SELECT name,SIZE (work_locations)
FROM test_array ;
-- 查询location数组中包含tianjin的信息
SELECT *
FROM test_array
WHERE ARRAY_CONTAINS(work_locations, 'tianjin');
Map
COLLECTION ITEMS TERMINATED BY '#'；
‘#'：表示array值之间的分隔符是 #
MAP KEYS TERMINATED BY ':';
':'：表示键与值之间的分隔符是 :
-- 建表
CREATE TABLE test_map(
id INT,
name STRING,
members map<STRING ,STRING >,
age INT
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '#'
MAP KEYS TERMINATED BY ':';
-- 加载数据
LOAD DATA LOCAL INPATH '/home/hadoop/tmpdata/data_for_map_type.txt'
INTO TABLE TEST_MAP ;
-- 查询全部
SELECT * FROM TEST_MAP TM ;
函数
- map[key]：获取指定key的值
- map_keys(map)：取到全部的key作为array返回，map_values(map)取到全部values
- size(map)：统计K-V对的个数
- array_contains(map_values(map), 数据) ：统计map是否包含指定数据
-- 查询father、mother这两个map的key
SELECT id,name,MEMBERS['father'] father,MEMBERS['mother'] mother ,AGE FROM TEST_MAP TM ;
-- 查询全部map的key，使用map_keys函数，结果是array类型
SELECT *,map_keys(MEMBERS) FROM TEST_MAP TM ;
-- 查询全部map的value，使用map_values函数，结果是array类型
SELECT *,map_values (MEMBERS) FROM TEST_MAP TM ;
-- 查询map类型的KV对数量
SELECT *, SIZE(MEMBERS) FROM TEST_MAP TM ;
-- 查询map的key中有brother的数据
SELECT * FROM TEST_MAP
WHERE ARRAY_CONTAINS(map_keys(MEMBERS),'brother') ;
Struct
COLLECTION ITEMS TERMINATED BY ':';
':'：表示strcut各个数据之间的分隔符是 :
drop table TEST_STRUCT ;
-- 建表
CREATE TABLE test_struct(
id STRING ,
info struct<name:STRING ,age:INT>
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '#'
COLLECTION ITEMS TERMINATED BY ':';
-- 加载数据
LOAD DATA LOCAL INPATH '/home/hadoop/tmpdata/data_for_struct_type.txt'
INTO TABLE TEST_STRUCT ;
-- 查询全部
SELECT * FROM TEST_STRUCT TS ;
-- 直接使用列名.子列名 即可从struct中取出子列查询
SELECT id, info.name, info.age FROM TEST_STRUCT TS ;
Sampling采样
1、基于随机分桶抽样
SELECT ... FROM tbl TABLESAMPLE(BUCKET x OUT OF y ON(colname | rand()))
- x表示从y里面随机抽取x份数据作为取样
- y表示将表数据随机划分成y份（y个桶）
- 使用colname作为随机依据，则其它条件不变下，每次抽样结果一致
- 使用rand()作为随机依据，每次抽样结果都不同
# 根据 ORDERID列，分成 10 桶， 取 2 桶
SELECT *
FROM ORDERS
TABLESAMPLE(BUCKET 2 OUT OF 10 ON ORDERID);
# 随机分成 10 桶，随机取 2 桶
SELECT *
FROM ORDERS
TABLESAMPLE(BUCKET 2 OUT OF 10 ON rand());
2、基于数据块抽样
SELECT ... FROM tbl TABLESAMPLE(num ROWS | num PERCENT | num(K|M|G));
- num ROWS 表示抽样num条数据
- num PERCENT 表示抽样num百分百比例的数据
- num(K|M|G) 表示抽取num大小的数据，单位可以是K、M、G表示KB、MB、GB
# 按表中数据顺序，取前 10条 数据
SELECT *
FROM ORDERS
TABLESAMPLE(10 ROWS);
# 按表中数据顺序，取前 1% 数据
SELECT *
FROM ORDERS
TABLESAMPLE(1 PERCENT);
# 按表中数据顺序，取前 1K大小 数据
SELECT *
FROM ORDERS
TABLESAMPLE(1K);
# 按表中数据顺序，取前 1M大小 数据
SELECT *
FROM ORDERS
TABLESAMPLE(1M);
常用函数
内置函数
-- 使用show functions查看当下可用的所有函数
show functions
-- 通过describe function extended funcname来查看函数的使用方式
describe function extended funcname
-- 取整函数: round 返回double类型的整数值部分 （遵循四舍五入）
select round(3.1415926);
-- 指定精度取整函数
round(double a, int d)
select round(3.1415926,4);
-- 取随机数函数: rand 每次执行都不一样 返回一个0到1范围内的随机数
select rand();
-- 指定种子取随机数函数，得到一个稳定的随机数序列
rand(int seed)
select rand(3);
-- 求数字的绝对值
select abs(-3);
**```**
