搜索
您的当前位置:首页正文

hive常用于测试的sql语句

来源:意榕旅游网
--构建测试表
with tmp as (
select 'CQH8885' AS callsign,20200501 as tobt,null as asbt,null as aobt
union all
select 'CQH8885' AS callsign,null as tobt,20200522 as asbt,null as aobt
union all
select 'CQH8885' AS callsign,null as tobt,null as asbt,20200523 as aobt
)
select callsign,max(tobt) as tobt,max(asbt) as asbt,max(aobt) as aobt
from tmp 
group by callsign

--开窗函数max多行合成一行

--创建表
//1. 
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name  --# 表名
[(col_name data_type  [COMMENT col_comment], ...)] --# 列名
[COMMENT table_comment] --#表批注
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] --# 分区
[CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] --#分桶
[
   [ROW FORMAT row_format] 
   [STORED AS file_format]
     | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]
] --# 指定行和文件的格式化
[LOCATION hdfs_path] --# 指定表存放的路径
[TBLPROPERTIES (property_name=property_value, ...)] --# 指定表属性
[AS select_statement] --# 在建表的时候,以当前的查询语句的结果作为表数据(可以导数据)


//2. 
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
  LIKE existing_table_or_view_name
  [LOCATION hdfs_path]; --# 创建一张新表,以原有的表或者视图作为数据
  
临时表、外部表(默认是内部表)

--删除表
drop table table_name;

--删除数据不删除表
--删表重建 等同于清楚了表中的数据
truncate table table_name;

因篇幅问题不能全部显示,请点此查看更多更全内容

热门图文

Top