HiveSQL的常用的一些语句
--使用注意 1、不支持删除字段 2、不支持修改操作 --查询表字段及注释 desc table_name; --查询建表语句 show create table_name; --查询表分区 show partitions table_name; --查询表数据 select t.* from dm_gis.gis_rds_omsto t where inc_day='20190601' limit 1000; --增加分区 alter table dm_gis.table_name add if not exists partition (inc_day = '20190601'); --删除分区 alter table dm_gis.table_name add if not exists partition (inc_day = '20190601'); -- 增加列字段(最后) alter table `dm_gis.table_name` add columns ( `name` string comment '名字', `age` int comment '年龄', `height` float comment '身高'); --改变列字段名称 alter table table_name change column old_column_name new_column_name column_type; --修改表字段注释 alter table table_name change [cloumn] col_old_name col_new_name column_type [conmment col_conmment] [first|after column_name]; alter table table_name change cloumn col_old_name col_new_name column_type conmment col_conmment; --给表重命名 alter table old_table_name rename to new_old_name; --根据已存在的表结构建新表 create table new_table_name like source_table_name; --清除表数据 truncate table table_name; --删除表 drop table table_name; --创建表(内部表) create table if not exists `dm_gis.person`( `name` string comment '名字', `age` int comment '年龄', `hight` string comment '身高' )comment '个人信息' partitioned by (`year` string) row format delimited fields terminated by '\t' lines terminated by '\n' stored as textfile; --创建外部表 create external table if not exists dm_gis.bee_logs_gis_ass_rds_chk_dispatch( log string comment "一行日志数据" ) comment 'rds派件原始日志外部映射表' partitioned by (`inc_day` string) row format delimited fields terminated by '\t' lines terminated by '\n' stored as textfile location '/user/mario/warehouse/bee_logs_gis_ass_rds_chk_dispatch' ; --根据查询语句结果创建表并加载数据(非分区表) create table dm_gis.gis_copy_test001 as select citycode,tc,inc_day from dm_gis.dlv_addr_tc_count where inc_day='20190501' limit 100; --外部表新增映射分区 alter table dm_gis.bee_logs_gis_ass_rds_chk_dispatch add if not exists partition (inc_day='20181108') location '/user/mario/warehouse/bee_logs_gis_ass_rds_chk_dispatch/20181108'; --加载数据到hdfs路径下(先建好分区,文件格式需和定义的表格式一致) load data inpath '/user/01375125/upload/file/aoi_id.csv' into table dm_gis.aoi_id_addr_type partition(inc_day='20190322'); --表之间倒腾数据 set hive.exec.dynamic.partition.mode=nonstrict; insert overwrite table dm_gis.dlv_addr_tc_rate partition(inc_day) select citycode,waybill_count,tc_match_count,tc_mismatch_count,tc_match_rate,inc_day,other,`inc_day` from dm_gis.dlv_tc_rate_info where inc_day between '20190301' and '20190331';