hivesql修改字段类型_Hive SQL汇总

创建数据库

create database if not exists sopdm

comment 'this is test database'

with dbproperties('creator'='gxw','date'='2014-11-12')

--数据库键值对属性信息

location '/my/preferred/directory';

显示所有表

show tables ;

显示表的描述信息

desc [extended,formatted] tablename;

显示建表语句

show create table tablename;

删除表

drop table tablename;

由一个表创建另一个表，相当于复制，表结构复制，数据没复制

create table test3 like test2;

由其他表查询创建表

create table test4 as select name,addr from test5;

stored as textfile

可以直接查看

stored as sequencefile

必须用hadoop fs -text查看

stored as rcfile

hive -service rcfilecat path 查看

stored as inputformat 'class'(自定义的)

加载jar包

shell窗口add jar path(作用范围本shell)

加载到分布式缓存中供各个节点使用

或者直接拷贝到hive安装目录下的lib目录

SerDe(hive使用SerDe读、写表的行)

读写顺序：

HDFS文件-->InputFileFormat--> -->Deserializer-->Row对象(供hive使用)

Row对象-->Serializer--> -->OutputFileFormat-->HDFS文件

hive自带RegexSerDe.class 正则表达式匹配每一行的数据

create table apachelog(

host STRING,

identity STRING,

user STRING,

time STRING,

request STRING,

status STRING,

size STRING,

refer STRING,

agent STRING)

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'

WITH SERDEPROPERTIES (

"input.regex="([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([0-9]*) ([0-9]*) ([^ ]*) ([^ ]*)"

) stored AS TEXTFILE；

分区表(相当于表的子目录)

create table tablename (name string) partitioned by (key type,...)

create external table employees(

name string,

salary float,

subordinates array,

deductions map,

address struct

)

partitioned by (dt string,type string)

row format delimited fields terminated by '\t'

collection items terminated by ','

map keys terminated by ':'

lines terminated by '\n'

stored as textfile

location '/data';

数据格式：

wang  123  a1,a2,a3  k1:1,k2:2,k3:3  s1,s2,s3,4

查看分区：

show partitions employees

增加分区

alter table employees add if not exists partition(country='xxx'[,state='yyy'])

删除分区

alter table employees drop if  exists partition(country='xxx'[,state='yyy'])

动态分区：

1.不需要为不同的分区添加不同的插入语句

2.分区不确定，需要从数据中获取

参数：(动态分区前两个必须开启)

set hive.exec.dynamic.partition=true;

set hive.exec.dynamic.partition.mode=nonstrick;

//无限制模式，如果是strict，则必须有一个静态分区，且放在最前面

set hive.exec.max.dynamic.partitions.pernode=10000;

//每个节点生成动态分区的最大个数

set hive.exec.max.dynamic.partitions=100000;

//每次sql查询生成动态分区的最大个数

set hive.exec.max.created.files=150000;

//一个任务最多可以创建的文件数目

set dfs.datanode.max.xcievers=8182;

//限定一次最多打开的文件数

1.创建分区表

create table d_part (

name string

)

partitioned by (value string)

row format delimited fields terminated by '\t'

lines terminated by '\n'

stored as textfile;

2.插入动态分区

insert overwrite table d_part partition(value)

select name,addr as value

from testtext;

分桶

set hive.enforce.bucketing=true;

按id分桶

create table bucketed_user

(id string,

name string)

clustered by (id) sorted by(name)  into 4 buckets

row format delimited fields terminated by '\t'

lines terminated by '\n'

stored as textfile;

分桶抽样

select * from bucketed_user tablesample(bucket 1 out of 2 on id )

取一半的桶

优化

set hive.optimize.bucketmapjoin=true;

set hive.optimize.bucketmapjoin.sortedmerge=true;

set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;

beeline底层使用的jdbc，命令行使用jdbc(可以远程访问)

hive -help

hive --help

hive --service -help

查询结果写到文件

hive -V -e "select name from testtext" > /home/data/result

hive命令行输入list jar

显示当前分布式缓存有哪些jar包也就是add jar命令加载的jar包)

命令行执行hql文件(类似于hive -f)，常用于设置初始化参数

source /home/data/hql/select_hql

配置变量

set val='';

hql使用hive变量

${hiveconf:val}

select * from testtext where name ='${hiveconf:val}';

env查看linux环境变量

HOME=/root

hql使用linux环境变量

select '${env:HOME}' from testtext;

1.内表数据加载(overwrite和into不能同时存在，只能存在一个)

(1) 创建表时加载

create table newtable as select col1,col2 from oldtable;

(2)创建表时指定数据位置(对location下的数据具有拥有权，删除内表时也会删除数据)

create table tablename() location '';

(3)本地数据加载

load data local inpath 'localpath' [overwrite] into table tablename;

(4)加载hdfs数据(移动数据，原来的数据移动到表的位置下)

load data  inpath 'hdfspath' [overwrite] into table tablename;

hive命令行执行linux shell命令在前面加个！

!ls /home/data

(5)通过查询语句加载数据

insert  into table test_m select name,addr from testtext where name ='wer';

或者

from testtext insert  into table test_m select name,addr  where name ='wer';

或者

select name,addr from testtext  where name ='wer' insert  into table test_m ;

2.外部表数据加载

(1)创建表时指定数据位置

create external table tablename() location '';

(2)查询插入，同内表

(3)使用hadoop命令拷贝数据到指定位置(hive的shell中执行和linux的shell执行)

3.分区表数据加载

(1)内部分区表数据加载类似于内表

(2)外部分区表数据加载方式类似于外表

注意：数据存放的路径层次要和表的分区一致,并且表要增加相应分区才能查到数据

load data local inpath 'localpath' [overwrite] into table tablename partition(dt='20140905');

4.数据类型对应问题

Load数据，字段类型不能相互转化时，查询返回NULL；

select查询输入，字段类型不能相互转化时，插入数据为NULL(文件保存是 \N)；

select查询输入数据，字段名称可不一致，数据加载不做检查，查询时检查

数据导出

导出到本地，默认分隔符^A

insert overwrite local directory '/home/data3'

row format delimited fields terminated by '\t'

select name,addr from testtext;

导出到hdfs，不支持row format delimited fields terminated by '\t'，只能采用默认分隔符是I

insert overwrite directory '/home/data3'

select name,addr from testtext;

表属性操作

1.修改表名

alter table table_name rename to new_table_name；

2.修改列名

alter table tablename change column c1 c2 int comment 'xxx' after severity;

c1旧列，c2新列，int代表新列数据类型

after severity;可以把该列放到指定列的后面，或者使用'first'放到第一位

3.增加列(默认新增列放到最末尾)

alter  table tablename add column (c1 string comment 'xxxx',c2 string comment 'yyyy')；

4.修改tblproperties

alter table test set tblproperties('comment'='xxxx');

5.修改分隔符(分区表比较特殊)

方式一

alter table city set serdeproperties('field.delim'='\t');(对分区表原始数据无效，对新加分区有效)

方式二 对分区表原始数据也使用最新的分隔符

alter table city partitin(dt='20140908') set serdeproperties('field.delim'='\t');

6.修改location

alter table city [partition(...)] set location 'hdfs://master:9000/location' ;

7.内部表和外部表转换

alter table test set tblproperties('EXTERNAL'='TRUE');内部表转外部表

alter table test set tblproperties('EXTERNAL'='FALSE');外部表转内部表

聚合操作

1.count计数

count(*)所有的字段不全为null，全为null不加1

count(1)不管记录是啥，只要有这条记录都加1

count(col)列不为空加1

2.sum求和

sum(可转成数字的值)  返回bigint

sum(col)+cast(1 as bigint)

3.avg

avg(可转成数字的值) 返回double

where条件在map端执行

group by是在reduce端执行 分组的列组合为key

having字句聚合操作之后执行判断，也是在reduce端执行

groupby数据倾斜优化

hive.groupby.skewindata=true;(多起一个job)

join操作(普通join不支持不等值链接)

优化参数set hive.optimize.skewjoin=true;

样例

select m.col as col,m.col2 as col2,n.col3 as col3

from

(select col,col2

from test

where ...(map端执行)

) m

[left outer|right outer|left semi] join

n (右表)

on m.col=n.col

where condition (reduce端执行)

LEFT SEMI JOIN 是 IN/EXISTS 子查询的一种更高效的实现

Hive 当前没有实现 IN/EXISTS 子查询，所以你可以用 LEFT SEMI JOIN

重写你的子查询语句。LEFT SEMI JOIN 的限制是， JOIN

子句中右边的表只能在

ON 子句中设置过滤条件，在 WHERE 子句、SELECT 子句或其他地方过滤都不行。

SELECT a.key, a.value

FROM a

WHERE a.key in

(SELECT b.key

FROM b);

可以被重写为：

SELECT a.key, a.val

FROM a LEFT SEMI JOIN b on

(a.key = b.key)

order by全局排序，只有一个reduce

distribute by col分散数据，按col分散到不同的reduce

和sort by结合保证每个reduce输出是有序的

union all(不去重)和union(去重)

hive只支持union all，子查询不允许起别名

select col from

(select a as col from t1

union all

select b as col from t2

) tmp;

要求：

1.字段名字一样

2.字段类型一样

3.字段个数一样

4.子表不能有别名

5.如果需要从合并之后的表中查询数据，那么合并之后的表必须要有别名

hive创建索引表hive> create index user_index on table user(id)

> as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'

> with deferred rebuild

> IN TABLE user_index_table;

hive> alter index user_index on user rebuild;

hive> select * from user_index_table limit 5;

直接用hadoop命令复制删除hive存储数据后，需要add partition或alter来同步源数据信息，否则drop表等操作时会查询元数据metastore，查到metastore信息和hdfs信息不一致，会报错。这个也可算是hive的bug，尚未修复，但也可以理解为初衷不建议直接操作hdfs数据。

目前，可以采用命令：MSCK REPAIR TABLE table_name;

该命令会把没添加进partition的数据，都增加对应的partition。同步源数据信息metadata。

Recover Partitions

Hive在metastore中存储每个表的分区列表，如果新的分区加入HDFS后，metastore不会注意这些分区，除非

ALTER TABLE table_name ADD PARTITION

当然可以通过

MSCK REPAIR TABLE table_name;

类似EMR版本中的如下命令

ALTER TABLE table_name RECOVER PARTITIONS;