暑假生活每周总结6

本周进行了大数据操作数据库的操作：

创建数据库：

CREATE DATABASE [IF NOT EXISTS] db_name [LOCATION 'path'] [COMMENT database_comment];

• IF NOT EXISTS，如存在同名数据库不执行任何操作，否则执行创建数据库操作

• [LOCATION]，自定义数据库存储位置，如不填写，默认数据库在HDFS的路径为：/user/hive/warehouse

• [COMMENT database_comment]，可选，数据库注释

删除数据库：

DROP DATABASE [IF EXISTS] db_name [CASCADE];

• [IF EXISTS]，可选，如果存在此数据库执行删除，不存在不执行任何操作

• [CASCADE]，可选，级联删除，即数据库内存在表，使用CASCADE可以强制删除数据库

选择数据库：

USE db_name;

• 选择数据库后，后续SQL操作基于当前选择的库执行

• 如不使用use，默认在default库执行如果想要切换为default数据库可使用 USE DEFAULT;

基础建表：

CREATE [EXTERNAL] TABLE tb_name
    (col_name col_type [COMMENT col_comment], ......)
    [COMMENT tb_comment]//表注释
    [PARTITIONED BY(col_name, col_type, ......)]//分区
    [CLUSTERED BY(col_name, col_type, ......) INTO num BUCKETS]//分桶
    [ROW FORMAT DELIMITED FIELDS TERMINATED BY '']//指定分隔符
    [LOCATION 'path']//指定路径

• [EXTERNAL]，外部表，需搭配

• [ROW FORMAT DELIMITED FIELDS TERMINATED BY '']指定列分隔符

• 外部表示意

  CREATE EXTERNAL TABLE test_ext(id int) COMMENT 'external table' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LOCATION 'hdfs://node1:8020/tmp/test_ext';

• [COMMENT tb_comment]表注释，可选
• `[PARTITIONED BY(col_name, col_type, ......)]`基于列分区

• 分区表示意
  CREATE TABLE test_ext(id int) COMMENT 'partitioned table' PARTITION BY(year string, month string, day string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

• `[CLUSTERED BY(col_name, col_type, ......)]`基于列分桶
• 分桶示意   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';

基于其他表创建表

CREATE TABLE tbl_name LIKE other_tbl;

基于查询结果建表

CREATE TABLE tbl_name AS SELECT ...;

删除表

DROP TABLE tbl;

修改表

重命名

ALTER TABLE old RENAME TO new;

修改属性：

ALTER TABLE tbl SET TBLPROPERTIES(key=value);
-- 常用属性
("EXTERNAL"="TRUE") -- 内外部表，TRUE表示外部表
('comment' = new_comment) -- 修改表注释

更多属性进入https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-listTableProperties

分区操作

创建分区表

-- 分区表示意
CREATE TABLE test_ext(id int) COMMENT 'partitioned table' PARTITION BY(year string, month string, day string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

添加分区

ALTER TABLE tablename ADD PARTITION (partition_key='partition_value', ......);

修改分区值

ALTER TABLE tablename PARTITION (partition_key='old_partition_value') RENAME TO PARTITION (partition_key='new_partition_value');

删除分区

ALTER TABLE tablename DROP PARTITION (partition_key='partition_value');

加载数据

LOAD DATA [LOCAL] INPATH 'path' INTO TABLE tbl PARTITION(partition_key='partition_value');

INSERT (OVERWRITE | INTO) TABLE tbl PARTITION(partition_key='partition_value') SELECT ... FROM ...;

分桶操作

创建表

CREATE TABLE course (c_id string,c_name string,t_id string) 
    [PARTITION(partition_key='partition_value')] 
    CLUSTERED BY(c_id) INTO 3 BUCKETS ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

• CLUSTERED BY(col) 指定分桶列

• INTO 3 BUCKETS，设定3个桶

数据加载：

INSERT (OVERWRITE | INTO) TABLE tbl 
    [PARTITION(partition_key='partition_value')] 
    SELECT ... FROM ... CLUSTER BY(col);

数据加载

LOAD DATA

将数据文件加载到表

LOAD DATA [LOCAL] INPATH 'path' INTO TABLE tbl [PARTITION(partition_key='partition_value')];    -- 指定分区可选

INSERT SELECT

将其它表数据，加载到目标表

INSERT (OVERWRITE | INTO) TABLE tbl 
    [PARTITION(partition_key='partition_value')]         -- 指定分区，可选
    SELECT ... FROM ... [CLUSTER BY(col)];                -- 指定分桶列，可选

数据导出

INSERT OVERWRITE SELECT

INSERT OVERWRITE [LOCAL] DIRECTORY ‘path’                 -- LOCAL可选，带LOCAL导出Linux本地，不带LOCAL导出到HDFS
    [ROW FORMAT DELIMITED FIELDS TERMINATED BY '']        -- 可选，自定义列分隔符
    SELECT ... FROM ...;

查询语句与之前学的相似。

sampling采样

# 随机桶抽取， 分配桶是有规则的
# 可以按照列的hash取模分桶
# 按照完全随机分桶
-- 其它条件不变的话，每一次运行结果一致
select username, orderId, totalmoney FROM itheima.orders 
    tablesample(bucket 3 out of 10 on username);
    
-- 完全随机，每一次运行结果不同
select * from itheima.orders 
    tablesample(bucket 3 out of 10 on rand());
    


# 数据块抽取，按顺序抽取，每次条件不变，抽取结果不变
-- 抽取100条
select * from itheima.orders
    tablesample(100 rows);
    
-- 取1%数据
select * from itheima.orders
    tablesample(1 percent);
    
-- 取 1KB数据
select * from itheima.orders
    tablesample(1K);

函数

数值、集合、转换、日期函数

-- 查看所有可用函数
show functions;
-- 查看函数使用方式
describe function extended count;
-- 数值函数
-- round 取整，设置小数精度
select round(3.1415926);        -- 取整(四舍五入)
select round(3.1415926, 4);        -- 设置小数精度4位(四舍五入)
-- 随机数
select rand();                    -- 完全随机
select rand(3);                    -- 设置随机数种子，设置种子后每次运行结果一致的
-- 绝对值
select abs(-3);
-- 求PI
select pi();

-- 集合函数
-- 求元素个数
select size(work_locations) from test_array;
select size(members) from test_map;
-- 取出map的全部key
select map_keys(members) from test_map;
-- 取出map的全部value
select map_values(members) from test_map;
-- 查询array内是否包含指定元素，是就返回True
select * from test_array where ARRAY_CONTAINS(work_locations, 'tianjin');
-- 排序
select *, sort_array(work_locations) from test_array;


-- 类型转换函数
-- 转二进制
select binary('hadoop');
-- 自由转换，类型转换失败报错或返回NULL
select cast('1' as bigint);

-- 日期函数
-- 当前时间戳
select current_timestamp();
-- 当前日期
select current_date();
-- 时间戳转日期
select to_date(current_timestamp());
-- 年月日季度等
select year('2020-01-11');
select month('2020-01-11');
select day('2020-01-11');
select quarter('2020-05-11');
select dayofmonth('2020-05-11');
select hour('2020-05-11 10:36:59');
select minute('2020-05-11 10:36:59');
select second('2020-05-11 10:36:59');
select weekofyear('2020-05-11 10:36:59');
-- 日期之间的天数
select datediff('2022-12-31', '2019-12-31');
-- 日期相加、相减
select date_add('2022-12-31', 5);
select date_sub('2022-12-31', 5);