Hadoop下hive数据库基础操作命令代码合集

create table useinfo(//创建表的名称为useinfo
id int,//int 值
name string) //string值
row format delimited //设置分隔符的格式 
fields terminated by '\t'; //以tab键分割字段

create table classinfo9(
teacher string,
classname string)
row format delimited
fields terminated by '\t';


create table choice (
userid int,
classname string)
row format delimited
fields terminated by '\t';

load data local inpath '/home/liqifeng/hadoop' overwrite into table useinfo //从本地文件导入数据

loacl data local inpath '/home/lidafeng/hadoop' overwrite into table classinfo //从本地文件导入数据

loacl data loacl inpath '/home/lidafeng/hadoop' overwrite into table classinfo //从本地文件导入数据

create table ptest (
userid int)
partitioned by (name string) //以name作为分区字段
row format delimited
fields terminated  by '\t';

create external table liqifeng(
name string,
id int,
class string)
row format delimited
fields terminated by '\t'
lines terminated by '\n'
stored as textfile
location '/user/hadoop'
partitioned by (home string)

load data loacl inpath '/home/lidafeng/hadoop'  overwrite into table ptest partition (name='xiapi'); 从本地文件导入数据到petst表的xiapi分区中

insert overwrite table ptest partitioned (name='xiapi') select id from useinfo where name='xiapi' 从useinfo表中插入name='xiapi'的字段到petst表的name='xiapi'分区中

alter table petst drop partitioned(name='xiapi') //删除分区

alter table petst add partitioned (name='liqifeng') //新增liqifeng分区

show partitioned patst //显示分区字段

create table mutill as select id,name from userinfo //创建mutill表，并且复制userinfo表中的id和name字段

create table mutill2 like userinfo// 只复制userinfo的结构

from userinfo 
insert overwrite table mutill select id,name insert overwrite table mutill2 select count(distinct id),name group by name;//把一个表的数据插入到两个表里  

select userinfo.*,classinfo.* from userinfo join classinfo on (userinfo.id=classinfo.userid);//把userinfo和classinfo表中相同的值保留

select userinfo.*,classinfo.* from userinfo left outer join  classinfo on (userinfo.id=classinfo.userid);//显示左边表的所有数据，右边的一样就显示，不一样就null
select userindo.*,classinfo.* from userinfo right outer join classinfo on (userinfo.id=classinfo.userid);//显示右边表的所有数据，左边的一样就显示，不一样就null
select userinfo.*,classinfo.* from userinfo fullo outer join classinfo on (userinfo.id=classinfo.userid);//两边的所有数据，遇到一样的就都显示，不一样的就null
select userinfo.* from userinfo left semi join  classinfo on (userinfo.id=classinfo.userid);//只显示左表的所有数据，但是一定要和右边一样

create table phy_opt_course(
stname string,stID int,class string,opt_cour string)
row format delimited
fields terminated by '\t'
lines terminated by '\n'
stored as textfile//以txt文本格式存储

local data local inpath '/home/liqifeng/liqifeng.txt' into table phy_opt_course; //把数据导入到phy_opt_course表中；


local data local inpath '/root/liqifeng' into table phy_opt_course partition (home='yangzhou');

insert overwrite table liqifeng partition (name='xiapi') select id,name from userinfo;//向表中插入数据；

set hive.enforce.bucketing=true;//打开桶
set hive.enforce.buckering;//查看是否打开桶
create table tests(
id int,
name string)
  clustered by (id) into 3 buckets 
row format delimited 
field terminated by '\t'