HIVE建内部表语句
create table dll102.sougou (id string,pwd string,name string,count int,seqno int,address string)
row format delimited fields terminated by ' ' ;
创建字段中含有数组类型，对象类型的表，并查询特殊字段
外部表默认 存在HDFS下的 ‘/user/hadoop/指定路径’
将本地文件加载到HIVE
load data local inpath '/home/hadoop/sgtest' into table sougou;
查看表结构
desc formatted emp;[table_name]；
常用SQL语句
// 创建内部表 student create table student(id int, name string, sex string, age int, department string) row format delimited fields terminated by ',' //行分隔符用 lines stored as textfile; // 从本地导入数据 load data local inpath '/home/hadoop/student.txt' into table student; // 查询数据 select id, name, sex, age, department from student; // 创建一个external表 create external table ext_student(id int, name string, sex string, age int, department string) row format delimited fields terminated by ',' location '/ext_student'; // 导入数据 hadoop fs -put student.txt /ext_student; load data local inpath '/home/hadoop/mingxing.txt' into table ext_student; // 查询数据 select id, name ,sex, age , department from ext_student; // 创建内部表引用外部路径 create table mng_student(id int, name string, sex string, age int, department string) row format delimited fields terminated by ',' location '/ext_student'; // 创建分区表 create table ptn_student(id int, name string, sex string, age int, department string) partitioned by(code string) row format delimited fields terminated by ','; // 往分区表导入数据 load data local inpath '/home/hadoop/student.txt' into table ptn_student partition(code='112233'); load data local inpath '/home/hadoop/student.txt' into table ptn_student1 partition(code='335566');
//Hive表数据导出到HDSF
export table table_name to '/test/xx';
//Hive查询结果的导出
insert overwrite local directory '/opt/bigdata/export/student' select * from student;
insert overwrite local directory '/opt/bigdata/export/student' row format delimited fields terminated by ',' select * from student;
有local指的是导出到本地 ，没有local 导出到HDFS
// 创建多字段分区表 create table ptn_student1(id int, name string, sex string, age int, department string) partitioned by(code string, province string) row format delimited fields terminated by ','; // 往多分区表导入数据，需指定两个分区字段值 load data local inpath '/home/hadoop/student.txt' into table ptn_student1 partition(code='335566', province='beijing'); load data local inpath '/home/hadoop/student.txt' into table ptn_student1 partition(code='335566', province='tianjing'); load data local inpath '/home/hadoop/student.txt' into table ptn_student1 partition(code='kk', province='tianjing'); // 创建分桶表 create table bck_student(id int, name string, sex string, age int, department string) clustered by(department) into 4 buckets row format delimited fields terminated by ','; // 重命名表 alter table student rename to studentss; // 修改表的字段 alter table studentss add columns(abc string, efg int); // 删除表的字段，有一些问题。drop字段有问题。不确定能不能执行成功 alter table studentss drop column efg; // 修改表的字段 alter table studentss change abc code int; // 修改表的字段，并且改变字段的位置 alter table studentss change efg province string after name; // 替换所有字段 alter table studentss replace columns(id int, name string, sex string , age int , department string); // 删除表 drop table bck_student; drop table if exists bck_student; // 添加分区 alter table ptn_student add partition(code='445566'); alter table ptn_student add partition(code='445566') partition(code='778899'); // 删除分区 alter table ptn_student drop partition(code='445566'); // insert ... select ..... insert into table student select id, name, sex, age, department from studentss where department = 'MA'; // 以下两句操作是为多重插入准备两张表 create table student11(id int, name string) row format delimited fields terminated by ',' stored as textfile; create table student22(sex string, age int, department string) row format delimited fields terminated by ',' stored as textfile; // 多重插入 from studentss insert into table student11 select id, name insert into table student22 select sex, age, department; // 静态分区插入 load data local inpath 'student.txt' into table ptn_student partition(code='112233'); // 动态分区插入 set hive.exec.dynamic.partition=true; set hive.exec.dynamic.partition.mode=nonstrict; insert into table d_student partition(age) select id, name, sex, department,age from studentss; // 创建一个分区表，为上面的动态分区做准备的 create table d_student(id int, name string, sex string ,department string) partitioned by(age int) row format delimited fields terminated by ','; // CTAS create table my_student as select id, name , sex, age, department from studentss; // like create table like_student like studentss; // insert导出hive表数据到本地 insert overwrite local directory '/home/hadoop/myoutput' select id, name, sex, age, department from studentss;