hive DML

🧙 Questions

• 插入数据
• csv数据导入
• 查询数据
• 删除表所有数据

☄️ Ideas

插入数据

insert into ispong_db.users(username, age, birth) values ('ispong', 18, '2020-12-12');

create table cdh_dev.ispong_table_copy as select * from cdh_dev.ispong_table;
create table cdh_dev.define_part_temp_1 as select * from cdh_dev.define_part where  principal = 'xr20100294';
-- 普通插入数据
insert into rd_dev.ispong_table_avro3(t_username, t_age,lucky_date) values ('ispong', 18,'2021-12-12 12:12:12');


insert into table userinfo_big select * from userinfo;

csv数据导入

Note:
不能直接向分区表加载数据,需要提前创建分区
SequenceFile的表不能使用load来加载数据

# scp上传文件到服务器
scp jac_contract_contract.csv ispong@isxcode:~/
# 将文件推送到hdfs
hadoop fs -put jac_contract_contract.csv hdfs://isxcode:30116/
# hive中导入
hive

LOAD DATA INPATH 'hdfs://isxcode:30116/partmaster.csv' INTO TABLE rd_dev.partmaster;

分区导入

-- 指定分区导入数据
LOAD DATA INPATH 'hdfs://ispong-demo:30110/data.txt' INTO TABLE ispong_demo.access_log_song PARTITION (part_col1='1',part_col2='2');

查询数据

-- 普通查询
select username,joy from cdh_dev.ispong_table;
-- 限制条件查询
select username,age from cdh_dev.ispong_table limit 10;
-- 分页查询
select * from (
    select row_number() over (order by age desc) as rownum, * from cdh_dev.ispong_table
) mm where mm.rownum between 10 and 15;

删除表所有数据

truncate table cdh_dev.ispong_table;

整合小文件

ALTER TABLE table_name CONCATENATE;
ALTER TABLE table_name PARTITION (partition_column='value') CONCATENATE;

🔗 Links

• hive website
• hive dml docs