hive常用语法，

hive常用语法，

指定分隔符为逗号

create table student(id string,birthday string,grade int,m1 int,m2 int,m3 int,m4 int,memo string)
row format delimited 
fields terminated by ',';

外部表指定位置

create external table student(id string,birthday string,grade int,m1 int,m2 int,m3 int,m4 int,memo string)
row format delimited 
fields terminated by ','
location '/usr/data';

指定表的分区：分区字段不能是表中已存在的字段

create external table student(id string,birthday string,grade int,m1 int,m2 int,m3 int,m4 int,memo string)
row format delimited 
fields terminated by ','
location '/usr/data'
partitioned by(day string);

 向分区表中中导入数据

load data local inpath '/root/../../' into table tablemame partition(partition_name);

 从hdfs导入数据

load data inpath '/root/../../' into table tablemame partition(partition_name);

针对分区数据进行查询

select count(1) from table_name where partition_name='';

实质：就是把分区字段当成表字段来使用，既可以用where字句来查询了

建表语法：建一个和已经存在的表结构相同的表

create table new_table_name like exit_table_name;

用查询语句返回的结果新建一个表

create table new_table_name as select * from table_name where ip>'192.168.156.8';

hive复合数据类型(数组）,建表映射

create table student(id string,actor array<string>,grade int)
row format delimited 
fields terminated by ','
collection items terminated by ':';

//数据
1，王，li:zhang:liu，22


select id actor,grade
from student
where array(actor,'li');  //查询’li'

hive复合数据类型(map）,建表映射

create table student(id string,family_num map<string>,grade int)
row format delimited 
fields terminated by ','
collection items terminated by '#'//每个k-v怎么切
map keys terminated by ':';  // key和value怎么切



select id family['sister'],grade
from student
//查询

hive类型转换函数cast

select cast("8" as int)

用一张表查询的数据创建另一张表

create table table_name
as
select * 
from table;