12

【Hive SQL】速查

 3 years ago
source link: https://www.guofei.site/2018/03/01/hive2.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

【Hive SQL】速查

2018年03月01日

Author: Guofei

文章归类: 1-1-算法平台 ，文章编号: 162

版权声明：本文作者是郭飞。转载随意，但需要标明原文链接，并通知本人
原文链接：https://www.guofei.site/2018/03/01/hive2.html

Edit

建表

1. 建一个普通表

DROP TABLE IF EXISTS app.app_example;
CREATE TABLE app.app_example(
id int comment 'id对应的解释',
col1 int comment '解释1',
col2 int comment '解释2'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

2. 建一个分区表

DROP TABLE IF EXISTS app.app_example;
CREATE TABLE app.app_example(
id int comment 'id对应的解释',
col1 int comment '解释1',
col2 int comment '解释2'
)
partitioned by (dt string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
location '/user/guofei/app_example'
stored as orc
TBLPROPERTIES ('author'='guofei');

插入数据

1. 从本地数据插入

load data local inpath 'file_path' into table tbname;
load data local inpath 'file_path' overwrite into table tbname; -- overwrite抹除原数据

LOAD DATA LOCAL INPATH  'file_path' INTO TABLE tbname PARTITION (country='US', state='CA') -- 插入分区表，增量添加，不是覆盖
LOAD DATA LOCAL INPATH  'file_path' OVERWIRTE INTO TABLE tbname PARTITION (country='US', state='CA') -- 插入分区表，带overwirte表示覆盖

2. 从查询插入数据

create  table tb2 as select * from tb1; -- 新建一个表，并以overwrite的方式写入数据
insert overwrite table tb2 select * from tb1; -- 覆盖插入数据
insert into table tb2 select * from tb1; -- 新增插入数据


-- 静态方式插入分区表：
insert overwrite table tmp.tmp_test
partition (country='US',state='OR')
select names from tbname1;

-- 动态方式插入到分区表：
set hive.exec.dynamic.partition.mode=nonstrict;
INSERT OVERWRITE TABLE app.app_example PARTITION(dt)
SELECT col1,col2,sysdate(-1) as dt
FROM
app.example_1
-- 动态方式必须把分区key放在最后，优点是可以同时向多个分区插入数据

-- overwrite表示覆盖插入，去掉overwrite表示增量添加

DROP TABLE IF EXISTS app.app_example;
CREATE TABLE app.app_example
AS
SELECT * FROM app.example_1;

删除分区

ALTER TABLE tablename DROP IF EXISTS PARTITION(year = 2015, month = 10, day = 1);

清除数据

TRUNCATE TABLE tablename;

对列增删改查

Hive修改表名，列名，列注释，表注释，增加列，调整列顺序，属性名等操作

ALTER TABLE name RENAME TO new_name
ALTER TABLE name ADD COLUMNS (col_spec[, col_spec ...])
ALTER TABLE name DROP [COLUMN] column_name
ALTER TABLE name CHANGE column_name new_name new_type
ALTER TABLE name REPLACE COLUMNS (col_spec[, col_spec ...])

重命名表

Rename To…

ALTER TABLE table_name RENAME TO new_table_name;

改变列名/类型/位置/注释

ALTER TABLE table_name
CHANGE COLUMN
col_old_name col_new_name column_type
[COMMENT col_conmment]
[FIRST|AFTER column_name];

用来修改表的列名、数据类型，列注释和列所在的位置 FIRST将列放在第一列，AFTER col_name将列放在col_name后面一列，

alter table tablename1
CHANGE COLUMN
rpt_tag rpt_tag int comment '1新客，2新转老，3新注册，5老客';

增加列

ADD COLUMNS允许用户在当前列的末尾，分区列之前添加新的列

ALTER TABLE table_name ADD
COLUMNS (col_name data_type [CONMMENT col_comment], ...);

select示例

1. 重复数据处理

看看重复数据的样子

select id,count(id)
from tablename
group by id
having count(id)>1

2. 选取前n数据

select * from
(
select row_number() over(partition by id order by col1,col2) as rownum , *
from table
) as temp
where temp.rownum = 1

指的注意的是，如果col1，col2仍有重复的话，可能每次返回的结果都不一样（一个小坑）

3. 抽样查询

select * from tmp.tmp_example tablesample(bucket 3 out of 30 on rand()) s;


-- 以某一列为分桶标准,每次运行结果都一样,实际上等价于 where id%10=0
select * from tmp.tmp_example tablesample(bucket 1 out of 10 on id) s;

-- 前20%行
select * from tmp.tmp_example tablesample(20 percent) s;

其它操作

show databases
use tmp
  1. hive模糊搜索表 
    show tables
show tables like '*name*';
show table extended like 'test_partition';
-- 加上 extended 后，会额外显示以下信息：
-- location，format，columns，partition，文件数，文件大小，最后一次编辑时间和访问时间
SHOW TBLPROPERTIES [table_name] -- 显示指定表名的TBLPROPERTIES信息（好像show create table [table_name]更全）
  2. 查看表结构信息 
    desc formatted table_name;
desc table_name;
  3. 查看分区信息 
    show partitions table_name;

参考资料

https://blog.csdn.net/helloxiaozhe/article/details/80749094
http://blog.csdn.net/mulangren1988/article/details/77509195

您的支持将鼓励我继续创作！

report

Recommend

  • 47
    • www.cocoachina.com 6 years ago
    • Cache

    Objective-C与Swift的转换速查手册

    如果你正要从Objective-C过渡到Swift，或反过来，一个在两种语言间显示等效代码的小手册会很有帮助。

  • 78
    • www.linuxprobe.com 6 years ago
    • Cache

    《DOS命令速查手册》 pdf电子书免费下载

    《DOS命令速查手册分为8章，依次讲解了文件管理命令、磁盘管理命令、网络管理命令、系统管理命令、活动目录管理命令、基本服务管理命令、Internet服务管理命令、自动批处理文件和系统配置文件。最后按英文字母列出了《DOS命令速查手册》所涉及...

  • 48
    • 掘金 juejin.im 5 years ago
    • Cache

    JS方法速查

    ?前言 首先这个文章是一个不断维护的项目，因为里面的内容并不是最优解，如果各位朋友有更好的解决方法，欢迎在下方评论，我也会第一时间感谢和更新文章内容的，拜谢!!! GitHub地址：项目地址 查看更多速查：notes网址 ?数组 1.数组去重 数组去重

  • 51
    • kymjs.com 5 years ago
    • Cache

    C函数速查手册|开源实验室

    C函数速查手册|开源实验室 C 函数速查手册 各位开源实验室用户：这本手册是我...

  • 22
    • www.cnblogs.com 4 years ago
    • Cache

    ES使用总结 --速查手册

    实际工作中使用ES有一段时间了，比起一直在理论上接触ES还是要好上一些的。今天就来总结一些实际工作中用到的一些ES功能吧。本文编排顺序，按使用的先后可能性排序编排。ES的功能很强大，但我们能用到的，也许并不会太多，所以本文可作为...

  • 14
    • answerywj.com 4 years ago
    • Cache

    Git速查手册（第三版）

    本文是对Git速查手册（第二版）的更新，补充了一些近期使用或者收集的一些命令。 设置Git Git下载地址：

  • 3
    • 微信 mp.weixin.qq.com 4 years ago
    • Cache

    Hive SQL迁移Spark SQL在滴滴的实践

    桔妹导读： 在滴滴S QL任务从Hive迁移到Spark后，Spark SQ...

  • 9
    • blog.csdn.net 3 years ago
    • Cache

    02、Hive数据仓库——SQL员工练习题

    Hive数据仓库——SQL员工练习题 一：将下列数据加载hive表。 test_emp数据库 create database test_emp; use test_emp; 员工信息表emp ...

  • 5
    • www.biaodianfu.com 3 years ago
    • Cache

    产品&运营分析技能培训：Hive SQL

    以下内容是自己近期咋在公司内进行的培训PPT的内容，由于示例SQL脚本涉及到公司的一些库表，所以已经去除。 Hive SQL概述 为什么要学SQL？ 性价比高：学习一周，受用终生。（有小学英语能力即可，相当简单，不...

  • 4
    • blog.51cto.com 1 year ago
    • Cache

    深入解析Apache Hive：大数据的SQL接口

    深入解析Apache Hive：大数据的SQL接口 精选 原创 草莓猪猪猪 2024-03...

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK