Hive 常用 HQL 语句

2019独角兽企业重金招聘Python工程师标准>>>

-- 创建数据库
create database if not exists db1 comment '数据库1';
-- 秀数据库
show databases;
-- 使用数据库
use db1;
-- 秀表
show tables;
show tables in db1;
-- 创建表
create table db1.table1(word string comment '词语', count int comment '数量');
-- 创建静态分区表
create table if not exists stocks (
ymd date,
price_open float,
price_high float,
price_low float,
price_close float,
volume int,
price_adj_close float
) 
partitioned by (exchanger string, symbol string) 
row format delimited fields terminated by ',';
-- 给静态分区表导入数据
load data local inpath '/home/bigdata/my/NASDAQ/AAPL/stocks.csv' overwrite into table stocks 
partition(exchanger='NASDAQ', symbol='AAPL');
-- 设置动态分区可用
set hive.exec.dynamic.partition=true;  --可通过 set hive.exec.dynamic.partition; 查看
set hive.exec.dynamic.partition.mode=nonstrict;  --可通过 set hive.exec.dynamic.partition.mode; 查看
set hive.exec.max.dynamic.partitions=1000;  --可通过 set hive.exec.max.dynamic.partitions 查看
-- 创建动态分区表
create table if not exists record_partition(
rid string,
uid string,
bid string,
price int,
source_province string,
target_province string,
site string,
express_number string,
express_company string
)
partitioned by (trancation_date date);
-- 给动态分区表导入数据
insert into table record_partition partition(trancation_date) select * from record;
-- 查看分区表分区信息
show partitions table1;
-- 查看建表信息
show create table table1;
-- 查看的数据库描述
describe database db1;
-- 查看表的描述
describe table1;
-- 删除数据库
drop database db1;
-- 删除表
drop table table1;
-- 查询语句
select * from db1.table1;
-- 载入本地数据到表中
load data local inpath '/home/bigdata/user.data' overwrite into table user_table;
-- 载入HDFS上的数据到表中
load data inpath '/hdfs/bigdata/user.data' overwrite into table user_table;

 

转载于:https://my.oschina.net/zdtdtel/blog/1608686