hive sql脚本学习

show databases ;

-- 创建数据库  相当于新增一个 目录
create database test_db1;
-- 删除数据库   相当于删除一个目录
drop database test_db1;

use test_db;
show tables ;
create table helloword(id int,name string) row format delimited fields terminated by ',';
create table sex(id int,name string) row format delimited fields terminated by ',';

drop table helloword;

-- 需要将文件放到hive所在服务器
load data local inpath '/usr/local/src/test/测试文件.txt' overwrite into table helloword;
load data local inpath '/usr/local/src/test/join测试.txt' overwrite into table sex;
-- '/usr/local/src/test/测试文件.txt': 如果不是本地导入可以指定从hdfs中导入
load data inpath 'hdfs://localhost:8020/data/测试.txt' overwrite into table sex;
-- overwrite 是否覆盖


show tables ;
select * from helloword;
select count(*) from helloword;
select * from helloword where id = 1;
-- 测试join
select * from sex;
select a.id, a.name,s.name from helloword a left join sex s on a.id = s.id;

-- group by 分组   having 分组情况条件筛选
select count(id) from helloword group by id having id = 1;


-- 不同数据类型
create external table t2(
    id      int
   ,name    string
   ,hobby   array<string>
   ,add     map<String,string>
)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
location '/user/t2'
;


-- 查表法建表 将数据  和  表结构一起 复制
create table t3 as
select
    id
   ,name
from t2
;

--  like 建表  只会复制 表结构   数据为空
create table t4 like t2;


---  分区相关sql操作  external 零时表关键字
create external table t2(
    id      int
   ,name    string
    ,day     string
   ,hobby   array<string>
   ,add     map<String,string>
)
partitioned by (day string)    --- 按天分区
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
location '/user/t2/'
;
load data local inpath '/usr/local/src/test/测试文件.txt' overwrite into table helloword partition (day = '2021-02-03');
select * from t2 where day = '2021-02-03';


-- 将统计数据导入到另外一张表
insert overwrite table track_info_province_stat partition (day = '2021-02-03')
select province,count(*) as cnt from t2 where day = '2021-02-03' group by province;