小记--------hive命令行sql小栗子(一)

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排序关键词

 

ORDER BY 和 SORT BY

 

order by : 会对查询结果集执行一个全局排序，也就是说会有一个所有的数据都通过一个reducer进行处理的过程。  确定： 消耗时间太长。

 

sort by：其只会在每个reducer中对数据进行排序，也就是执行一个局部排序过程，这可以保证每个reducer的输出数据都是有序的（但并非全局有序），可以提高后面进行的全局排序的效率

 

注:如果reducer的个数是1的话，那么order by 和sort by的结果是一样的，只有多个reducer的时候order by 会按照每个reducer进行局部排序。

 

DISTRIBUTE BY

distribute by：  控制map的输出在reducer中是如何划分的，MapReduce job中传输的所有数据都是按照键-值对的方式进行组织的，默认情况下，MapReduce计算框架会依据map输入的键计算相应的哈希值，然后按照得到的哈希值将键-值对均匀分发到多个reducer中去，这也就意味着当我们使用sort by时，不同reducer的输出内容会有明显的重叠。

注：distribute by 会将特定的行到某个reducer，distribute by 要写在sort by 语句之前,distribute by 只是做  ‘分’  并不进行排序。

例：

根据年份和气温对气象数据进行排序，以确保所有具有相同年份的行最终都在一个reducer分区中

From record2

select year, temperature

distribute by year

sort by year asc, temperature desc;

因此，distribute by 经常和 sort by 配合使用

 

CLUSTER BY  是 distribute by 和 sort by 的结合体

cluster by id = distribute by id + sort by id 

也就是当分组字段和排序字段是一个字段的时候就使用cluster by  ，否则不要使用cluster by 

注： cluster by  不能和sort by同时使用。

 

创建分桶表 CLUSTERED BY (列名) INTO 分桶数 BUCKETS 

例： 

CREATE TABLE weblog (user_id INT , url STRING , source_ip STRING) PARTITIONED BY (dt STRING) CLUSTERED BY (user_id) INTO 96 BUCKETS;

上述只是仅仅定义了元数据，而不影响实际填充表的命令。

填充表使用INSERT  TABLE 

例：SET hive.enforce.bucketing = true ;   初始化过程设置正确的reducer个数

FROM raw_logs INSERT OVERWEITE TABLE weblog PARTITION (dt=‘20200909’)

SELECT user_id , url , source_ip WHERE dt=‘20200909’

注：如果不使用 hive.enforce.bucketing属性，那么就需要自己设置和分桶个数相匹配的reducer个数。