Hadoop下hive数据库基础操作命令代码合集
来源:互联网 发布:我的手机没有4g网络 编辑:程序博客网 时间:2024/06/06 00:01
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'
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'
1 0
- Hadoop下hive数据库基础操作命令代码合集
- hadoop命令及hive数据库操作语句的简单使用
- hadoop hive 基本操作命令
- Hadoop Hive基础SQL语法(DDL 操作)
- hadoop备战:hive命令集
- linux与hadoop命令合集
- vim常用操作命令 合集
- Hadoop Hive基础SQL语法(DML 操作:元数据存储)
- Hadoop Hive基础SQL语法(DQL 操作:数据查询SQL)
- hadoop hive 操作整理
- hadoop hive基本操作
- Hadoop Hive sql操作
- linux 下 oracle 日常操作合集
- Hadoop生态系统命令合集(更新中……)
- Hive Shell命令之一(数据库和表的操作)
- Hadoop学习第四天之hadoop命令操作(下)
- hive库基础命令
- hive基础命令
- Android中attrs.xml文件的使用详解
- DVWA-1.9系列操作之FileInclusion
- R
- jquery事件与绑定事件
- numpy学习日记(二)
- Hadoop下hive数据库基础操作命令代码合集
- mvn-lifecycle、phase、goal
- Android MVC、MVP、MVVM模式框架
- 面试记录总结
- eclipse下的Servers工程与Tomcat的关系
- 广告点击率预估模型
- 微软的序列号生成和验证
- inputbox()和val(inputbox())
- redis集群数据备份和数据复制方式