小记--------hive sql函数及hive 命令行简单操作-1

1.创建 orc 格式 hive表

 

CREATE TABLE IF NOT EXISTS order_user_all ( renter_id int COMMENT '用户id', order_q int COMMENT '近90天订单数',pay_normal_all double COMMENT '近90天订单产生费用', pay_user_all double COMMENT '近90天订单实付金额', last_time int COMMENT '最后一次打车间隔时间')

STORED AS orc;

 

 

创建hive orc表

CREATE TABLE `renter_info` (

`id_` string  COMMENT '用户id',

`register_city` string  COMMENT '注册城市（城市编码字典）',

`create_time` string  COMMENT '创建时间' )

COMMENT '个人用户表'

CLUSTERED BY (

id_)

INTO 5 BUCKETS

ROW FORMAT SERDE

'org.apache.hadoop.hive.ql.io.orc.OrcSerde'

STORED AS INPUTFORMAT

'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'

OUTPUTFORMAT

'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'

LOCATION

'hdfs://nameservice1/user/hive/warehouse/yuzq_test.db/renter_info'

 

2.从文件中执行hive查询

可以使用 -f 文件名方式执行指定文件中的一个或者多个查询语句，一般吧这些hive查询文件保存为.q 或者.hql后缀名的文件

Hive -f /home/test.hql

 

3.在hive shell中用户可以使用source命令来执行一个脚本文件

source /home/test.hql

 

4.Hive 脚本中的注释使用：  --

 

5.hive 命令行 显示字段名称： 

首先执行：set hive.cli.print.header=true;

然后再执行查询语句就会显示字段名称：select * from test;

 

 

6.hive 中默认的记录和字段分隔符

 

分隔符	描述
\n	对于文本文件来说，每行都是一条记录，因此换行符可以分割记录
^A   (Ctrl+A)	用于分割字段（列）在create table语句中可以使用八进制编码\001表示
^B	用于分割ARRARY 或者 STRUCT中的元素，或用于Map中键-值对之间的分割，在create table语句中可以使用八进制编码\002表示
^C	用于map中键和值之间的分割，在create table中可以使用八进制编码\003表示

7.hivesql语句

 

//修改某个数据库默认存储路径

CREATE DATABASE df LOCATION ‘/my/database/path’

 

//为数据库增加一个描述

CREATE DATABASE df COMMENT ‘东风数据库’

 

//删除数据库(有表的)  CASCADE 关键词

DROP DATABASE IF EXISTS df CASCADE;

 

//查看是外部表还是内部表

DESCRIBE EXTENDED tablename; 最后一行会显示 tableType:MANAGED_TABLE / EXTERNAL_TABLE

 

//查看表的分区

SHOW PARTITIONS tablename;

 

//删除表

DROP TABLE IF EXISTS tablename;

注：如果用户开启了Hadoop回收站功能（默认是关闭的）那么数据将会被转移到用户在分布式文件系统中的用户根目录下的.Trash目录下 ， 也就是HDFS中的/user/$USER/.Trash目录 ， 如果想开启这个功能，只需要将配置数据fs.trash.interval的值设置为一个合理的正整数即可，这个值是“回收站检查点”的时间间隔，单位是min，因此如果设置值为1440，那么久表示是24小时，不过并不能保证所有的分布式系统以及所有版本都是支持的这个功能的， 如果用户不小心删除了一张存储着重要数据的管理表的话，那么可以先重建表，然后重建所需要的分区，再从.Trash目录中将误删的文件移动到正确的文件目录下来重新存储数据。

 

//修改表

ALTER TABLE 语句来修改， 这种操作会修改元数据，但不会修改数据本身，可用于修改表模式中出现的错误，改版分区路径，以及其他一些操作。

//表重命名

ALTER TABLE oldtablename RENAME TO newtablename;

 

//增加表分区（通常用于外部表）增加一个分区

ALTER TABLE tablename ADD IF NOT EXISTS PARTITION (分区字段=值，分区字段=值) LOCATION ‘/logs/2011/01’;

 

//修改分区路径

ALTER TABLE tablename PARTITION(year=2011,month=12) SET LOCATION ‘/logs/2011/01’;

注：这个命令不会将数据从旧的路径转移走，也不会删除旧的数据。

 

//删除分区

ALTER TABLE tablename DROP IF EXISTS PARTITION (year=2011,month=12);

 

//将本地文件灌入表中

LOAD DATA LOCAL INPATH ‘指定要灌入的文件路径’ OVERWRITE INTO TABLE tablename PARTITION (country=‘us’ , state=‘ca’)

注：如果分区目录不存在的话，会先创建分区目录，再将数据拷贝到该目录下。 

LOCAL 关键词 指定的是本地文件

OVERWRITE 关键词：目标文件夹中之前存在的数据将会先删除掉，  如果没有这个关键字，仅仅会把新增的文件增加到目标文件夹中而不会删除之前的数据。如果目标文件夹中已经存在同名文件，那么旧的同名文件将会被覆盖重写。

 

//通过查询语句向表中插入数据

INSERT OVERWRITE TABLE tablename PARTITION (country = ‘us’ , state = ‘or’) 

SELECT * FROM tablename t WHERE t.country=‘us’ AND t.state=‘or’;

注：OVERWRITE 关键字 会将之前分区中的内容覆盖掉，如果没有使用OVERWRITE 而是使用INTO  ， 那么hive将会以追加的方式写入数据而不会覆盖之前的数据

//将同一张表中的数据按照一定规则分别插入新表的不同分区中

FROM srcname sn 

INSERT OVERWRITE TABLE newtable 

    PARTITION (country = ‘us’ , state = ‘or’)

    SELECT * WHERE sn.country = ‘us’ and sn.state=‘or'

INSERT OVERWRITE TABLE newtable

    PARTITION (country = ‘us’ , state = ‘ca’)

    SELECT * WHERE sn.country = ‘us’ and sn.state=‘ca’

INSERT OVERWRITE TABLE newtable

    PARTITION (country = ‘us’ , state = ‘la’)

    SELECT * WHERE sn.country = ‘us’ and sn.state=‘la’

 

//动态分区插入

INSERT OVERWRITE TABLE tablename PARTITION (country , state)

SELECT … , se.contry , se.state FROM oldtablename se ;

注：select 语句的最后几个字段需要和partition中的字段一一对应 即可创建对应的country state分区

如果要使用动态分区的话需要开启动态分区的属性， 默认是关闭的。

Hive.exec.dynamic.partition  设置为true ，表示开启动态分区功能

hive.exec.dynamic.partition.mode  设置为nonstrict ，表示允许所有分区都是动态的

 

//创建表并加载数据

CREATE TABLE newtablename AS SELECT name ,salary FROM oldtablename WHERE state=‘ca’;

注：这个功能不能用于外部表，因为这里本身并没有进行数据加载，而是将元数据执行一个指向数据的路径

 

//导出数据

INSERT OVERWRITE LOCAL DIRECTORY ‘导出到指定的路径(文件夹)’

SELECT name,salary,address FROM employees WHERE state = ‘ca'

注：hive会将所有的字段序列化成字符串写入到文件中， hive使用和hive内部存储的表相同的编码方式来生成输出文件

 

 

hive函数

 

1.数学函数

 

返回值类型	样式	描述
BIGINT	Round(DOUBLE d)	返回DOUBLE型d的BIGINT类型的近似值
DOUBLE	round(DOUBLE d,INT n)	返回DOUBLE醒d的保留n位小数的DOUBLE型的近似值
BIGINT	Floor(DOUBLE d)	d是DOUBLE类型的，返回 <=d 的最大BIGINT型值
BIGINT		Ceil(DOUBLE d ) d是DOUBLE类型的，返回>=d的最小BIGINT型值
DOUBLE	Rand()    ;   rand(INT seed)	每行返回一个DOUBLE型随机数，整数seed是随机因子
DOUBLE	exp(DOUBLE d)	返回e的d幂次方，返回的是个DOUBLE型值

注：函数floor、round、ceil输入的是DOUBLE类型，返回值是BIGINT类型，也就是将浮点型转换成整型了，在进行数据类型转换时，这些函数是首选的处理方式，而不是cast类型转换操作符。