常用HSQL

创建内部表通过，分割
create table t13(id int,name string,subject string)
row format delimited fields terminated by ‘,’;

LOAD DATA LOCAL INPATH ‘/root/t13.txt’ into table t13;
– collect_set 和 炸裂explode演示
select explode(a_subject) as sub from
(select id,name,collect_set(subject) as a_subject from t13 group by id,name) o1;

---

select id,if(age>25,‘青年’,‘少年’) from t_user;
select moive_name,if(array_contains(actors,‘吴刚’),‘好电影’,‘烂片儿’)
from t_movie;

select from_unixtime(unix_timestamp(),“yyyy/MM/dd HH:mm:ss”);

select greatest(1,99) ;

select concat(“A”,“B”);
select concat_ws("*", “A”,“B”);

–号需要转义
select split(concat_ws("", “A”,“B”), “\*”);

create table t_ip(dt string,ip string,url string)
row format delimited fields terminated by ‘,’;
–注意into和overwrite的区别
load data local inpath ‘/root/t_ip.txt’ into table t_ip;
load data local inpath ‘/root/t_ip.txt’ overwrite into table t_ip;
load data local inpath ‘/root/t10.txt’ overwrite into table t10;
load data local inpath ‘/root/t_s.txt’ overwrite into table t_s;

create table t_person_struct(id int,name string,info structage:int,sex:string,addr:string)
row format delimited fields terminated by ‘,’
collection items terminated by ‘:’;

select id,name,brother
from
(select id,name,family_members[‘brother’] as brother from t_person) tmp
where brother is not null;

select * from t_person where array_contains(map_keys(family_members),‘brother’);

–arryays创建数组

create table t_movie(moive_name string,actors array,first_show date)
row format delimited fields terminated by ‘,’
collection items terminated by ‘:’;

load data local inpath ‘/root/t_moive.txt’ into table t_movie;
load data local inpath ‘/root/t_person.txt’ into table t_person;
load data local inpath ‘/root/t_strut.txt’ into table t_person_struct;
load data local inpath ‘/root/t_ip.txt’ into table t_ip;
load data local inpath ‘/root/sale.txt’ into table t_sale;

select moive_name,actors from t_movie where array_contains(actors,‘吴刚’);

select * from a order by id desc limit 2;
select * from t_p where is_married;

create table t_customer(id int,name string,birthday date)
row format delimited fields terminated by ‘,’;

load data local inpath ‘/root/customer.dat’ into table t_customer;
load data local inpath ‘/root/t_user.txt’ into table t_user;
load data local inpath ‘/root/boolen.txt’ into table t_p;

create table t_p(id int,name string,age int,is_married string) row format delimited fields terminated by ‘,’;

–创建数据库
create database mytestdb;
–展示数据库
show databases;
–切换数据库
use default;
–创建表，每个字段按照 ， 间隔
create table t_1(id string, name string, sex string , age string) row format delimited fields terminated by ‘,’;
create table t_2(id string, name string, sex string , age string) row format delimited fields terminated by ‘,’;

–创建外部表
create external table t_3(id string, name string, sex string , age string) row format delimited fields terminated by ‘,’ LOCATION ‘/aa/bb’;

– 更改创建表的默认目录
create table t_3(id string, name string, sex string , age string) row format delimited fields terminated by ‘,’ LOCATION ‘/xx/oo’;

–查看创建表的信息
show create table t_2;
desc t_2;

–导入数据
–方式一：
LOAD DATA LOCAL INPATH ‘/root/t_1.txt’ into TABLE t_1;

–方式二：在hdfs上导入数据表，就是剪切数据
LOAD DATA INPATH ‘/t_1.txt’ into TABLE t_1;

select * from t_1;

–表的字段类型的修改
alter table t_x change string（字段名） id int first;

alter table t_p change string is_married boolean;

–添加一个字段
alter table t_1 add columns (clo1 String);

–表名的修改
alter table t_1 rename to t_x;

–创建分区表
CREATE TABLE t_2(id string, name string, sex string , age string)
partitioned by (day string,city string)
row format delimited fields terminated by ‘,’;

–导入分区数据
LOAD DATA LOCAL INPATH ‘/root/t_1.txt’ into TABLE t_2 PARTITION(day=‘2018-04-15’,city=‘beijing’);
select * from t_2 where day=‘2018-04-15’ and city=‘shanghai’;

select upper(“abc”); --做测试
set hive.exec.mode.local.auto=true; --设置本机运行模式

–清空表的数据
truncate table a;

select * from a order by id desc limit 2;