hive 基本操作相关
-- 删除库
drop database if exists db_name;
-- 强制删除库
drop database if exists db_name cascade;
-- 删除表
drop table if exists employee;
-- 清空表
truncate table employee;
-- 清空表,第二种方式
insert overwrite table employee select * from employee where 1=0;
-- 删除分区
alter table employee_table drop partition (stat_year_month>='2018-01');
-- 按条件删除数据
insert overwrite table employee_table select * from employee_table where id>'180203a15f';
******1、加载本地文件到hive(比较快) hive > load data local inpath '/opt/module/datas/student.txt' into table default.student; 加载HDFS数据覆盖表中已有的数据 hive > load data inpath '/user/atguigu/hive/student.txt' overwrite into table default.student; 向分区表记载数据(overwrite表示覆盖): load data local inpath '/usr/wh/a.txt' overwrite into table test_02 partition (dataday='20190501'); load data local inpath '/home/dip/jin/test1/data.txt' overwrite into table csljc.t_odscb_sales partition (yearmonth='2020-02'); ******2、向分区表插入数据: 1) hive > insert into table student partition(month='201709') values(1,'wangwu'); 2)基本模式插入(根据单张表查询结果) hive > insert overwrite table student partition(month='201708') select id, name from student where month='201709'; *****3、导出数据到本地目录(非常慢) 1) 导出数据到本地: insert overwrite local directory 'filepath' select ... 例: insert overwrite local directory '/home/hadoop/apps/hivedata' select * from stu_dynamic_ptn where department='IS'; 注意:数据写入到文件系统时进行文本序列化,且每列用^A 来区分,\n 为换行符。 用 more 命令查看时不容易看出分割符,可以使用: sed -e 's/\x01/\t/g' filename 来查看。 *****4、表间的数据插入: 1) 单重插入(非常慢,textfile格式向orc格式插入) insert into table stu_test02 select .. from ... 使用查询语句进行插入,每次插入数据是一次查询结果,数据每插入一次 对stu_test01扫描一次 例:insert into table stu_test02 select * from stu_test01 where age<=20; 2) 多重插入(非常慢) from 原来表名 insert into table 新表名1 select ..... insert into table 新表名2 select .... 插入多个查询结果 但是对表扫描仅仅1次 例:from stu_test01 insert into table student_ptn01 partition (age=17) select id,name,sex,department where age<18 insert into table student_ptn01 partition (age=18) select id,name,sex,department where age=18 insert into table student_ptn01 partition (age=19) select id,name,sex,department where age>18; CREATE TABLE `temp_jin_test`( `id` bigint, `name` varchar(255), `create_time` timestamp) row format delimited fields terminated by '\t' STORED as textfile ; CREATE TABLE `jin_test`( `id` bigint, `name` varchar(255), `create_time` timestamp) ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.orc.OrcSerde' WITH SERDEPROPERTIES ( 'field.delim'='\t', 'line.delim'='\n', 'serialization.format'='\t') STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat';