1) 创建数据库
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name[COMMENT database_comment];
2) 删除数据库
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name;
3) 修改数据库
ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES
(property_name=property_value, ...);
ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE]
user_or_role;
4) 使用数据库
USE database_name;
Use default;
8. 创建/删除/表
1) 创建表
create table abc(
id int,
name string,
age int,
likes array<string>,
address map<string,string>
)
row format delimited fields terminated by ','
COLLECTION ITEMS TERMINATED by '-'
map keys terminated by ':'
lines terminated by '\n';
Select address[‘city’] from person where name=‘zs’;
2) 删除表
DROP TABLE [IF EXISTS] table_name [PURGE];
3) 重命名表
ALTER TABLE table_name RENAME TO new_table_name;
Eg: alter table meninem rename to jacke;
4) 更新数据
UPDATE tablename SET column = value [, column = value ...] [WHERE expression]
5) 删除数据
DELETE FROM tablename [WHERE expression]
9. DML 语句
1) 四种插入/导入数据
1. 第一种：
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]
2. 第二种：
INSERT OVERWRITE TABLE person2 [PARTITION(dt='2008-06-08', country)] SELECT id,name, age From ppt;
3. 第三种：
FROM person t1 INSERT OVERWRITE TABLE person1 [PARTITION(dt='2008-06-08', country)]
4. 第四种(处理不了庞大的数据)：
INSERT INTO TABLE students VALUES('fred flintstone', 35, 1.28), ('barney rubble', 32,2.32);
2) 查询数据并保存
1. 保存数据到本地：
insert overwrite local directory '/opt/datas/hive_exp_emp2'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
select * from db_1128.emp ;
2. 保存数据到 HDFS 上：
insert overwrite directory '/user/beifeng/hive/hive_exp_emp'
select * from db_1128.emp ;
3) 备份数据或还原数据
1. 备份数据
EXPORT TABLE db_1128.emp TO '/user/hadoop/hive/datas/export/emp' ;
2. 删除再还原数据：
先删除表。
drop table db_1128.emp;
show tables from db_1128;
再还原数据：
I MPORT FROM '/user/hadoop/hive/datas/export/emp' ;
10. Hive 分区与自定义函数
1) Hive 的分区 partition(注意：必须在表定义时创建 partition)
1. 单分区
1. 单分区建表语句
1. create table day_table(id int, content
string) partitioned by (dt string) row format delimited fields
terminated by ',';
2. 多分区
1. 双分区建表语句
1. create table day_hour_table (id int,
content string) partitioned by (dt string, hour string) row
format delimited fields terminated by ',';
3. 添加分区表的分区
1. （分区表已创建，在此基础上添加分区）：ALTER TABLE table_name
ADD partition_spec [ LOCATION 'location1' ] partition_spec
[ LOCATION 'location2' ] ...
例： ALTER TABLE day_table ADD PARTITION (dt='2008-08-08')
4. 删除分区
1. 语法：ALTER TABLE table_name DROP partition_spec,partition_spec,...
2. 用户可以用 ALTER TABLE DROP PARTITION 来删除分区。分区的元数据和数据将被一并删除。
3. 例：ALTER TABLE day_hour_table DROP PARTITION (dt='2008-08-08',hour='08');
5. 数据加载进分区表中
1. 语法：LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTOTABLE tablename [PARTITION (partcol1=val1,partcol2=val2 ...)]
2. 例： LOAD DATA INPATH '/user/pv.txt' INTO TABLE day_hour_tablePARTITION(dt='2008-08-08', hour='08');
6. 查看分区语句
1. show partitions day_hour_table;
7. 重命名分区
1. ALTER TABLE table_name PARTITION partition_spec RENAME TOPARTITION partition_spec;
2. ALTER TABLE day_table PARTITION (dt='2018-08-08') RENAME TOPARTITION (dt='2008-08-08');
8. 动态分区
1. 在本地文件/home/grid/a.txt 中写入以下 4 行数据：
aaa,US,CA
aaa,US,CB
bbb,CA,BB
bbb,CA,BC
2. 建立非分区表并加载数据
1. CREATE TABLE t1 (name STRING, cty STRING, st STRING) ROW FORMAT DELIMITED FI
ELDS TERMINATED BY ',';
2. LOAD DATA LOCAL INPATH '/home/grid/a.txt' INTO TABLE t1;
3. SELECT * FROM t1;
3. 建立外部分区表并动态加载数据 （注意删除外部表的相关事项）3. 建立外部分区表并动态加载数据 （注意删除外部表的相关事项）3. 建立外部分区表并动态加载数据 （注意删除外部表的相关事项）
1. CREATE EXTERNAL TABLE t2 (name STRING) PARTITIONED BY (country STRING,stateSTRING);
4. 注意设定以下参数
1. hive.exec.dynamic.partition(是否开启动态分区功能，默认 false 关闭)
2. hive.exec.dynamic.partition.mode(动态分区的模式，默认 strict，表示必须指定至少一个分区为静态分区，nonstrict 模式表示允许所有的分区字段都可以使用动态分区)
3. hive.exec.max.dynamic.partitions.pernode(在每个执行 MR 的节点上，最大可以创建多少个动态分区)
4. hive.exec.max.dynamic.partitions(在所有执行 MR 的节点上，最大一共可以创建多少个动态分区)
5. hive.exec.max.created.files(一般默认值足够了，除非你的数据量非常大，需要创建的文件数大于 100000，可根据实际情况加以调整)
6. hive.error.on.empty.partition(当有空分区生成时，是否抛出异常)
9. 函数自定义
1. UDF：一进一出
2. UDAF：聚集函数，多进一出 如：Count/max/min
3. UDTF：一进多出，如 lateral view explore()
11. 案例实战
1) 基站掉话率
1. .建表：
create table cell_monitor(
record_time string,
imei string,
cell string,
ph_num int,
call_num int,
drop_num int,
duration int,
drop_rate DOUBLE,
net_type string,
erl string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE;
2. 结果表
create table cell_drop_monitor(
imei string,
total_call_num int,
total_drop_num int,
d_rate DOUBLE
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;
3. load 数据
LOAD DATA LOCAL INPATH '/opt/data/cdr_summ_imei_cell_info.csv' OVERWRITE INTO TABLE cell_monitor;
4.找出掉线率最高的基站
from cell_monitor cm
insert overwrite table cell_drop_monitor
select cm.imei ,sum(cm.drop_num),sum(cm.duration),sum(cm.drop_num)/sum(cm.duration)
d_rate
group by cm.imei
sort by d_rate desc;
2) 计数
1. 建表
create table docs(line string);
create table wc(word string, totalword int);
2. 加载数据
load data local inpath '/root/wc' into table docs;
3) 统计
from (select explode(split(line, ' ')) as word from docs) w
insert into table wc
select word, count(1) as totalword
group by word
order by word;
4) 查询结果
select * from wc;
12. 分桶
1) 分桶表及应用场景
1. 分桶表是对列值取哈希值的方式，将不同数据放到不同文件中存储。
对于 hive 中每一个表、分区都可以进一步进行分桶。
由列的哈希值除以桶的个数来决定每条数据划分在哪个桶中。
2. 适用场景：
数据抽样（ sampling ）、map-join
2) 开启支持分桶
1. set hive.enforce.bucketing=true;
2. 默认：false；设置为 true 之后，mr 运行时会根据 bucket 的个数自动分配 reduce task 个数
3) 往分桶表中加载数据
insert into table bucket_table select columns from tbl;
insert overwrite table bucket_table select columns from tbl;
4) 抽样查询
select * from bucket_table tablesample(bucket 1 out of 4 on columns);
TABLESAMPLE 语法：
TABLESAMPLE(BUCKET x OUT OF y)
x：表示从哪个 bucket 开始抽取数据
y：必须为该表总 bucket 数的倍数或因子
1826
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
