Hive SQL 的 DQL操作

  首先我们先知道HSQL的建表格式:

-- 建表格式
create [external] table [if not exists] 表名
[(列名 数据类型 [comment 列描述信息], ......)]  
-- 默认是gbk码表,中文会乱码,需要改成utf8码表 
[comment 表的描述信息] 
[partitioned by (分区字段1 数据类型 [comment 描述信息],...... )]  
-- 分区=分文件夹 作用是避免全表扫描,降低扫描次数,提高查询效率
[clustered by (分桶字段1,分桶字段2....) sorted by (排序字段1 asc|desc, 排序字段2 asc|desc, ....) into 桶的个数 buckets] 
-- 分桶=分文件 作用是减少笛卡尔积的数量,提高查询效率,方便数据采集 
[row format delimited|SerDe '指定其他的SerDe 类']
-- 行格式切割符
[stored as TextFile|Orc]
-- 存储方式 行存储|列存储
[location HDFS文件路径]
-- 存储位置
[tblproperties('属性名'='属性值')]
-- 表属性信息 (内外部表,创建者信息,压缩协议.....)

一. HSQL 基本查询

1.MySQL 和 Hive 单表查询对比

  MySQL 单表查询语法结构:

-- MySQL单表查询语法
select 
  [distinct] 列名
from 表名
where 组前筛选
group by 分组字段
having 组后筛选
order by 排序字段 asc| desc 
limit 起始索引,数据条数;

  Hive 单表查询语法结构:

-- Hive 单表查询语法结构
[CTE 表达式]
select 
  [distinct | all] 列名
from 表名
where 组前筛选
group by 分组字段
having 组后筛选
order by 排序字段 asc|desc
cluster by 分桶排序字段 | distribute by 分桶字段 sort by 排序字段 asc| desc 
limit 起始索引,数据条数;

  通过对比我们可以发现MySQL 和 Hive 单表查询有几点不同:

1. Hive 支持 CTE 表达式,可以临时存储某些语句的执行结果,方便查询.

2. Hive 除了有 distinct 关键字还支持 all 关键字写法.

3. Hive 支持分桶查询, cluster by + 分桶排序字段 (分桶排序必须是同一字段,且排序只能是升序),如果只分桶用 distribute by + 分桶字段 ,如果分桶和排序不是同一字段就用 distribute by + 分桶字段 sort by + 排序字段 asc | desc.

2. 基本查询

  2.1 首先建表,上传数据,查询表中所有数据.

-- 1. 建表.
CREATE TABLE orders (
    orderId bigint COMMENT '订单id',
    orderNo string COMMENT '订单编号',
    shopId bigint COMMENT '门店id',
    userId bigint COMMENT '用户id',
    orderStatus tinyint COMMENT '订单状态 -3:用户拒收 -2:未付款的订单 -1：用户取消 0:待发货 1:配送中 2:用户确认收货',
    goodsMoney double COMMENT '商品金额',
    deliverMoney double COMMENT '运费',
    totalMoney double COMMENT '订单金额（包括运费）',
    realTotalMoney double COMMENT '实际订单金额（折扣后金额）',
    payType tinyint COMMENT '支付方式,0:未知;1:支付宝，2：微信;3、现金；4、其他',
    isPay tinyint COMMENT '是否支付 0:未支付 1:已支付',
    userName string COMMENT '收件人姓名'