hive day2

强制类型转换

hive的原子数据类型是可以进行隐式转换的,类似于Java的类型转换,例如某表达式使用INT类型,TINYINT会自动转换为INT类型,但是Hive不会进行反向转化,例如,某表达式使用TINYINT类型,INT不会自动转换为TINYINT类型,它会返回错误,除非使用CAST操作

1. 隐式类型转换规则如下’1’+1 = 2.0
   1. 任何整数类型都可以隐式地转换为一个范围更广的类型,如TINYINT可以转换成INT,INT可以转换成BIGINT
   2. 所有整数类型 FLOAT和STRING类型都可以隐式地转换成DOUBLE
   3. TINYINT SMALLINT INT都可以转换为FLOAT
   4. BOOLEAN类型不可以转换为任何其它的类型
2. 可以使用CAST操作显示进行数据类型转换

例如CAST(‘1’AS INT)将把字符串’1’转换成整数1;如果强制类型转换失败,如执行CAST(‘X’AS INT),表达式返回空值NULL.

建表语法

• row format delimited fields terminated by’分隔符’ 以分隔符隔开行
• create table创建一个知道名字的表 如果相同名字的表已经存在,则抛出异常;用户可以用IF NOT EXISTS选项来忽略这个异常
• EXTERNAL关键字可以让用户创建一个外部表,在建表的同时指定一个指向实际数据的路径(LOCATION),Hive创建内部表时,会将数据移动到数据仓库指向的路径;若创建外部表,仅记录数据所在的路径,不对数据的位置做任何改变.在删除表的时候,内部表的元数据和数据会被一起删除,而外部表只删除元数据,不删除数据
• COMMENT:为表和列添加注释
• PARTITIONED BY 创建分区表
• CLUSTERED BY 创建分桶表
• SORTED BY 不常用
• STORED AS指定存储文件类型
• LOCATION:指定表在HDFS上的存储位置
• LIKE允许用户复制现有的表结构 但是不复制数据

Hive内部表与外部表的区别

1. 未被external修饰的是内部表[managed table],被external修饰的为外部表[externaltable]
2. 内部表数据由Hive自身管理,外部表数据由HDFS管理
3. 内部表数据存储在hive.metastore.warehouse.dir[默认:/user/hive/warehouse],外部表数据存储位置由用户自己决定
4. 删除内部表会直接删除元数据[metadata]及存储数据,删除外部表仅仅删除元数据,HDFS上的文件不会被删除
5. 对内部表的修改会直接同步到元数据,而对外部表的表结构和分区进行修改,则需要修改[MSCKREPAIR TABLE table_name]

静态分区错误数据修剪

• 静态分区:在HDFS存在大量的静态数据,并且数据是分类别的日志服务器 每天都会产生日志数据

• 使用静态分区将不同的数据导入到hive中的不同分区中 如果静态文件中的数据有错误 比如01-02.log中有01-01的数据 分区下的数据有错误的

  修改分区表结构

• 查看表的所有分区

  • show partitions 表名;
• 添加分区 load数据的时候自动的创建没有的分区
  • alter table 表名 add partition(数据字段)partition(数据字段);
• 删除分区
  • alter table 表名 drop partition(错误数据)

    查看表结构和详细信息

    desc formatted tb_name(
    表字段 数据类型
    表位置数据位置 所在的数据库
    表类型
    文件存储格式化 Inputformat
    分区字段
    )

管理表和外部表转换

1. 查询表的类型

hive(default)>desc formatted student2;
Table type: MANAGED_TABL

1. 修改内部表student2为外部表

alter table student2 set
tblproperties('EXTERNAL'='TRUE')

1. 查询表的类型

hive(default)>desc formatted student2;
Table Type: EXTERNAL_TABLE

1. 修改外部表student2为内部表

alter table student2 set
tblproperties('EXTERNAL'='FALSE');--要求KV的大小写

1. 查询表的类型

hive(default)>desc formatted student2;
Table Type: MANAGED_TABLE
注意:('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')为固定写法,
区分大小写

数据导入

1. 可以将对应的数据 存储在指定的表的目录
   1. hdfs dfs -put
   2. hdfs dfs -mv
   3. hdfs dfs -cp
2. load数据到指定的表中 将数据自动的传到表目录下
   1. 本地:load data local inpath’/root/文件’ into table tb_order;
   2. hdfs:load data inpath’/文件名’into table tb_order;

插入数据(insert)

1. 使用insert into values

insert into tb_orders values('oid010','uid003',98),(),();

1. insert into select

• 将查询结果保存在已经存在的一张表中 增量保存处理结果

  查询语句中创建表并加载数据(As Select)

  create table tb_emp3 as select eno,name,job from tb_emp;

  创建表时通过Localtion指定加载数据路径

  一般适用于外部表
  location '/data/user';

数据导出

将a表的数据导出到b表中
insert into b select *from a;
将表数据导出到文件夹中
--将查询的结果保存在文件夹中
insert overwrite local directory'/data/output'
select*from tb_orders;
hive Shell命令导出
hive -e "SQL1;SQL2;"--直接执行SQL语句
hive -e "use db_doit31; select *from tb_orders;" >> res.data 保存sql结果
hive -f my_sql.sh > res.data
Export导出到HDFS上
export table tb_order to 路径

清除表中数据

truncate table 表名;
注意:Truncate只能删除管理表,不能删除外部表中数据

查询

全表和特定列查询

1. 全表查询 在开发中不要出现*

select *from 表名;

1. 选择特定列查询

select 字段,字段,字段 from 表名;
注意

1. SQL语言大小写不敏感
2. SQL可以写在一行或者多行
3. 关键字不能被缩写也不能分行
4. 各子句一般要分行写

5. 使用缩进提高语句的可读性

   列别名

6. 重命名一个列

7. 便于计算
8. 紧跟列名 也可以在列名和别名之间加入关键字’AS’

常用聚合函数

自动的忽略null值
求总行数(count)
select count(*) from 表名;
求最大值(所求的字段)
select max(值)from 表名;
求最小值(min)
slect  min(所求的字段) from 表名;
求总和(sum)
select sum(所求字段)from 表名;
求平均值(avg)
select avg(所求字段)from 表名;

Limit语句

典型的查询会返回多行数据 LIMIT子句用于限制返回的行数
select*from 表名 limit 5;
参数一 起始的行数0 开始计数 参数2 每页显示的条数
select *from 表名 limit 2,5;

窗口函数

窗口:聚合函数执行的范围,组内执行聚合,窗口大小就是组
在进行分组聚合以后,我们还想操作集合以前的数据 使用到窗口函数

• 函数说明
  • OVER():指定分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变化而变化
  • CURRENT ROW:当前行 current row
  • n PRECEDING:往前n行数据
  • n FOLLOWING:往后n行数据
  • UNBOUNDED:无限
  • 起点,UNBOUNDED PRECEDING 表示从前面的起点
  • 终点,UNBOUNDED FOLLOWING 表示到后面的终点
  • LAG(col,n,[default]):往前第n行数据lag 参数一字段 n
  • LEAD(col,n):往后第n行数据 lead

编号函数

• RANK()排序相同时会重复,总数不会变
• DENSE_RANK()排序相同时会重复,总数会减少
• ROW_NUMBER()会根据顺序计算