hive基础语法一

hive基础语法一

set $属性（变量）;
with tmp as();
select 
from(
    select
    a.id id,
    a.name name
    from test
    left join test1 b
    on ....
    join ...
    where 
    group by
    having 
    order by / sort by
    union /union all
    )

语法规则：

hive的数据库名、表名都不区分大小写

命名规则：

不能使用关键字、数字开头的字符串来作库表名。

尽量不使用特殊符号

默认数据库

hive有一个默认的数据库default，如果不明确的说明要使用哪个库，则使用默认数据库。

test.a1属于 test_a1

创建库的本质是：、在hive的工作空间（warehouse）目录下创建一个，库名.db的目录

表下分区：分区下有桶

表下有桶：

表下数据

创建表：本质也是创建目录：

create table

建表语法

​

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] TABLENAME
	[COLUMNNAME COLUMNTYPE [COMMENT 'COLUMN COMMENT'],...]
	[COMMENT 'TABLE COMMENT']
	[PARTITIONED BY (COLUMNNAME COLUMNTYPE [COMMENT 'COLUMN COMMENT'],...)]
	[CLUSTERED BY (COLUMNNAME COLUMNTYPE [COMMENT 'COLUMN COMMENT'],...) [SORTED BY (COLUMNNAME [ASC|DESC])...] INTO NUM_BUCKETS BUCKETS]
	[ROW FORMAT ROW_FORMAT]
	[STORED AS FILEFORMAT]
	[LOCATION HDFS_PATH];

hive 默认的字段分隔符：

为ascii码的控制符\001,建表的时候用fields terminated by '\001',如果要测试的话，造数据在vi 打开文件里面，用ctrl+v然后再ctrl+a可以输入这个控制符\001。按顺序，\002的输入方式为ctrl+v,ctrl+b。以此类推。^A /001 /u001   同理：^B  /002  /u002

设置分割：

row format delimited fields terminated by ','
lines terminated by '\n'

内外表的区别：

1、内部表 表目录会创建在hdfs的/user/hive/warehouse/下的相应的库对应的目录中。
将表四加到表一的工作目录下（内部表）未在hive的工作目录创建目录
2、外部表 外部表会根据创建表时LOCATION指定的路径来创建目录，如果没有指定LOCATION，则位置跟内部表相同

内部表和外部表在创建时的差别：就差两个关键字，EXTERNAL LOCATION 
举例：
内部表 -- CRAATE TABLE T_INNER(ID INT);
外部表 -- CREATE EXTERNAL TABLE T_OUTER(ID  INT) LOCATION 'HDFS:///AA/BB/XX';
1.默认创建内部表，创建外部表需要external。

2.一般使用外部表（长期存在的表，数据量大的、不希望把数据块删除的数据），临时表或者确定使用即可清空全部数据（数据块和元数据）则可使用内部表

3.内部表删除时将会删除元数据和Hdfs中表对应的目录，而外部表删除是只会删除元数据 ，hdfs中的数据目录保留。
create  EXTERNAL table if not exists t_user2(id string,name string) 

4.创建表

创建外部表时如果加了location那么会在location指定的目录下

创建表（不会创建表目录），（hive工作目录不显示，指定目录下也不显示）

location加载hdfs中的目录：

如果不指定location，使用load会将（hdfs中数据）数据拉过去。到hive的工作空间下。（此表删除时，hive工作空间下目录不会删除，即元数据删除，数据文件在hive工作目录里）

load加载时hdfs中的数据（文件）。

create  external table t_user2

ods层：把原数据抽取：（表目录）sqoop

DW层：数仓层（抽取数据从ods中）sql

DM层：即视层

hive的本地运行（集群开启时）

set hive.exec.mode.local.auto=true;

hive的表加载

加载数据的本质：
1、如果数据在本地，加载数据的本质就是将数据copy到hdfs上的表目录下。
2、如果数据在hdfs上，加载数据的本质是将数据移动到hdfs的表目录下。
load data local inpath '/hivedata/user.txt' into table t2;

hive使用的是严格的读时模式：加载数据时不检查数据的完整性，读时发现数据不对则使用NULL来代替。
而mysql使用的是写时模式：
1.直接将hdfs中的数据使用命令上传到表所对应的目录

2.创建表的时候，使用location指定表所对应的目录即可
 create  EXTERNAL table t_user3(id string,name string) 
    > row format delimited fields terminated by ' '
    > lines terminated by '\n'
    > location '/user/hive/warehouse/qf_bjdata.db/t_user1';
    将表三加到表一的工作目录下（外部表（外部表的性质，删除表三，数据仍在，表一的元数据依旧在））
    create  table t_user4(id string,name string) 
    > row format delimited fields terminated by ' '
    > lines terminated by '\n'
    > location '/user/hive/warehouse/qf_bjdata.db/t_user1';
    将表四加到表一的工作目录下（内部表（删除表四，及表一的工作目录被删除）表一仍在（数据被删））
3.使用load方式加载数据（默认，和覆盖写，移动）
load data inpath '/neibu' into table t_user1;--移动--
load data local inpath '/hive/data/neibu.txt' into table t_user1;--复制
load data local inpath '/hive/data/neibu.txt' overwrite into table t_user1 --重写复制

4.使用 insert into 方式

法一：

insert into table t_user2

select id ,name from t_user;

法二：

from t_user

insert into table t_user2

insert into table t_user3

select id,name

法三

with tmp as(

select 

id,

name

from t_user

)

insert into table t_user2

select * from tmp;

5、使用ctas方式来

create table t_user3

as 

select 

id,name 

from t_user;

6、使用like（克隆）

create table t_user4 like t_user2;(复制结构)

create table t_user5 like t_user2 location ‘目录’;（复制结构和数据）

注：1/2/4方法常用

查看表的描述：

desc t_user2

desc extended t_user2;

show create table t_user2;  --和mysql一样 

desc t_user2;

表属性修改：

表名：
alter table log rename log2 (改变tbl；元数据的映射（元数据在hdfs的地址（sds）没变）)
字段名：
alter table log2 change column id myid int after mac;
注：没有before和last

字段类型：
字段顺序：
添加字段：
alter table log2 add columns(dt1 int,dt2 string); 
删除字段：
alter table log2 replace columns(dt1 int,dt2 string)//替换，保留（）里的字段
内外部表转换：
alter table log3 set tblproperties('EXTERNAL=TRUE');####内部表转外部表(大写)
alter table log3 set tblproperties('EXTERNAL=false');####外部表转内部表(忽略大小写)

显示当前库：

set hive.cli.print.current.db=true

删除库：

drop database qf24;
drop database qf24 cascade;