HQL(Hive query language)常用语句
来源:互联网 发布:vb取整函数函数 编辑:程序博客网 时间:2024/05/21 04:16
set hive.cli.print.header=true;
CREATE TABLE page_view(viewTime INT, userid BIGINT,
page_url STRING, referrer_url STRING,
ip STRING COMMENT 'IP Address of the User')
COMMENT 'This is the page view table'
PARTITIONED BY(dt STRING, country STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\001'
STORED AS SEQUENCEFILE;
//sequencefile
create table tab_ip_seq(id int,name string,ip string,country string)
row format delimited
fields terminated by ','
stored as sequencefile;
//使用select语句来批量插入数据
insert overwrite table tab_ip_seq select * from tab_ext;
//create & load
create table tab_ip(id int,name string,ip string,country string)
row format delimited
fields terminated by ','
stored as textfile;
//从本地导入数据到hive的表中(实质就是将文件上传到hdfs中hive管理目录下)
load data local inpath '/home/hadoop/ip.txt' into table tab_ext;
//从hdfs上导入数据到hive表中(实质就是将文件从原始目录移动到hive管理的目录下)
load data inpath 'hdfs://ns1/aa/bb/data.log' into table tab_user;
//external外部表
CREATE EXTERNAL TABLE tab_ip_ext(id int, name string,
ip STRING,
country STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION '/external/user';
// CTAS 根据select语句建表结构
CREATE TABLE tab_ip_ctas
AS
SELECT id new_id, name new_name, ip new_ip,country new_country
FROM tab_ip_ext
SORT BY new_id;
//CLUSTER <--相对高级一点,你可以放在有精力的时候才去学习>
create table tab_ip_cluster(id int,name string,ip string,country string)
clustered by(id) into 3 buckets;
load data local inpath '/home/hadoop/ip.txt' overwrite into table tab_ip_cluster;
set hive.enforce.bucketing=true;
insert into table tab_ip_cluster select * from tab_ip;
select * from tab_ip_cluster tablesample(bucket 2 out of 3 on id);
//PARTITION 分区表
create table tab_ip_part(id int,name string,ip string,country string)
partitioned by (year string)
row format delimited fields terminated by ',';
load data local inpath '/home/hadoop/data.log' overwrite into table tab_ip_part
partition(year='1990');
load data local inpath '/home/hadoop/data2.log' overwrite into table tab_ip_part
partition(year='2000');
select * from tab_ip_part;
select * from tab_ip_part where part_flag='part2';
select count(*) from tab_ip_part where part_flag='part2';
alter table tab_ip change id id_alter string;
ALTER TABLE tab_cts ADD PARTITION (partCol = 'dt') location '/external/hive/dt';
show partitions tab_ip_part;
//insert from select 通过select语句批量插入数据到别的表
create table tab_ip_like like tab_ip;
insert overwrite table tab_ip_like
select * from tab_ip;
//write to hdfs 将结果写入到hdfs的文件中
insert overwrite local directory '/home/hadoop/hivetemp/test.txt' select * from tab_ip_part where part_flag='part1';
insert overwrite directory '/hiveout.txt' select * from tab_ip_part where part_flag='part1';
//cli shell 通过shell执行hive的hql语句
hive -S -e 'select country,count(*) from tab_ext' > /home/hadoop/hivetemp/e.txt
select * from tab_ext sort by id desc limit 5;
select a.ip,b.book from tab_ext a join tab_ip_book b on(a.name=b.name);
//array
create table tab_array(a array<int>,b array<string>)
row format delimited
fields terminated by '\t'
collection items terminated by ',';
select a[0] from tab_array;
select * from tab_array where array_contains(b,'word');
insert into table tab_array select array(0),array(name,ip) from tab_ext t;
//map
create table tab_map(name string,info map<string,string>)
row format delimited
fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':';
load data local inpath '/home/hadoop/hivetemp/tab_map.txt' overwrite into table tab_map;
insert into table tab_map select name,map('name',name,'ip',ip) from tab_ext;
//struct
create table tab_struct(name string,info struct<age:int,tel:string,addr:string>)
row format delimited
fields terminated by '\t'
collection items terminated by ','
load data local inpath '/home/hadoop/hivetemp/tab_st.txt' overwrite into table tab_struct;
insert into table tab_struct select name,named_struct('age',id,'tel',name,'addr',country) from tab_ext;
//UDF
select if(id=1,first,no-first),name from tab_ext;
hive>add jar /home/hadoop/myudf.jar;
hive>CREATE TEMPORARY FUNCTION fanyi AS 'cn.itcast.hive.Fanyi';
select id,name,ip,fanyi(country) from tab_ip_ext;
CREATE TABLE page_view(viewTime INT, userid BIGINT,
page_url STRING, referrer_url STRING,
ip STRING COMMENT 'IP Address of the User')
COMMENT 'This is the page view table'
PARTITIONED BY(dt STRING, country STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\001'
STORED AS SEQUENCEFILE;
//sequencefile
create table tab_ip_seq(id int,name string,ip string,country string)
row format delimited
fields terminated by ','
stored as sequencefile;
//使用select语句来批量插入数据
insert overwrite table tab_ip_seq select * from tab_ext;
//create & load
create table tab_ip(id int,name string,ip string,country string)
row format delimited
fields terminated by ','
stored as textfile;
//从本地导入数据到hive的表中(实质就是将文件上传到hdfs中hive管理目录下)
load data local inpath '/home/hadoop/ip.txt' into table tab_ext;
//从hdfs上导入数据到hive表中(实质就是将文件从原始目录移动到hive管理的目录下)
load data inpath 'hdfs://ns1/aa/bb/data.log' into table tab_user;
//external外部表
CREATE EXTERNAL TABLE tab_ip_ext(id int, name string,
ip STRING,
country STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION '/external/user';
// CTAS 根据select语句建表结构
CREATE TABLE tab_ip_ctas
AS
SELECT id new_id, name new_name, ip new_ip,country new_country
FROM tab_ip_ext
SORT BY new_id;
//CLUSTER <--相对高级一点,你可以放在有精力的时候才去学习>
create table tab_ip_cluster(id int,name string,ip string,country string)
clustered by(id) into 3 buckets;
load data local inpath '/home/hadoop/ip.txt' overwrite into table tab_ip_cluster;
set hive.enforce.bucketing=true;
insert into table tab_ip_cluster select * from tab_ip;
select * from tab_ip_cluster tablesample(bucket 2 out of 3 on id);
//PARTITION 分区表
create table tab_ip_part(id int,name string,ip string,country string)
partitioned by (year string)
row format delimited fields terminated by ',';
load data local inpath '/home/hadoop/data.log' overwrite into table tab_ip_part
partition(year='1990');
load data local inpath '/home/hadoop/data2.log' overwrite into table tab_ip_part
partition(year='2000');
select * from tab_ip_part;
select * from tab_ip_part where part_flag='part2';
select count(*) from tab_ip_part where part_flag='part2';
alter table tab_ip change id id_alter string;
ALTER TABLE tab_cts ADD PARTITION (partCol = 'dt') location '/external/hive/dt';
show partitions tab_ip_part;
//insert from select 通过select语句批量插入数据到别的表
create table tab_ip_like like tab_ip;
insert overwrite table tab_ip_like
select * from tab_ip;
//write to hdfs 将结果写入到hdfs的文件中
insert overwrite local directory '/home/hadoop/hivetemp/test.txt' select * from tab_ip_part where part_flag='part1';
insert overwrite directory '/hiveout.txt' select * from tab_ip_part where part_flag='part1';
//cli shell 通过shell执行hive的hql语句
hive -S -e 'select country,count(*) from tab_ext' > /home/hadoop/hivetemp/e.txt
select * from tab_ext sort by id desc limit 5;
select a.ip,b.book from tab_ext a join tab_ip_book b on(a.name=b.name);
//array
create table tab_array(a array<int>,b array<string>)
row format delimited
fields terminated by '\t'
collection items terminated by ',';
select a[0] from tab_array;
select * from tab_array where array_contains(b,'word');
insert into table tab_array select array(0),array(name,ip) from tab_ext t;
//map
create table tab_map(name string,info map<string,string>)
row format delimited
fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':';
load data local inpath '/home/hadoop/hivetemp/tab_map.txt' overwrite into table tab_map;
insert into table tab_map select name,map('name',name,'ip',ip) from tab_ext;
//struct
create table tab_struct(name string,info struct<age:int,tel:string,addr:string>)
row format delimited
fields terminated by '\t'
collection items terminated by ','
load data local inpath '/home/hadoop/hivetemp/tab_st.txt' overwrite into table tab_struct;
insert into table tab_struct select name,named_struct('age',id,'tel',name,'addr',country) from tab_ext;
//UDF
select if(id=1,first,no-first),name from tab_ext;
hive>add jar /home/hadoop/myudf.jar;
hive>CREATE TEMPORARY FUNCTION fanyi AS 'cn.itcast.hive.Fanyi';
select id,name,ip,fanyi(country) from tab_ip_ext;
0 0
- HQL(Hive query language)常用语句
- Hibernate Query Language(HQL)
- HQL(Hibernate Query Language)
- 06分布式数据仓库 HIVE -- 数据的查询(HQL Hive Query Language)
- HQL(Hibernate Query Language)使用详解
- hibernate-HQL(query language)-16
- HQL(Hibernate Query Language)
- Hibernate Query Language (HQL)
- HQL(Hibernate Query Language)
- (HQL)Hibernate Query Lanaguage常用的语句
- About Hibernate Query Language(HQL)
- Hibernate学习笔记-HQL(Hibernate Query Language)
- Hibernate学习笔记-HQL(Hibernate Query Language)
- Hibernate查询(Query Language,hql,ejbql,nativeql,qbc,qbe)
- hibernate(2) --query 常用方法及hql语句
- hive基础(二)--HQL语句
- hive hql 语句
- Hibernate查询语言(Query Language), 即HQL
- 化简方程
- 第十天2017/04/25(2、企业财富库:)
- Yii2.0 场景的简单使用
- 51nod 2级算法题-1119
- SSM框架本地测试没有问题,线上报错问题解决方案
- HQL(Hive query language)常用语句
- PostgreSQL递归查询
- Java实现LSTM和GRU做分类(以IRIS数据集为例)
- 说说Java中finally、final、finalize。
- WCF之调用模式
- response.write()方法将指定的字符创输出到html页面时遇到的问题
- 霍夫直线和圆检测
- LINQ的连接扩展(左连、右连、全连等)
- WWWFrom提交表单&从Web下载轻量数据