hive中插入数据select排除字段

现象

1. 在执行insert into|overwrite ${tableName} partition(partCol=${value}) select xxx from ${tableName}时，select * 会把分区字段查出来，而insert部分不包含分区字段，此时需要在select列表中把分区字段排除。
2. 有时又要把某列放在最后

举例

1. 排除load_date列

    set hive.support.quoted.identifiers=none;
    INSERT OVERWRITE TABLE lzf.T_LOAD_DIMENSION partition (LOAD_DATE='20220430') 
    select `(load_date)?+.+` from zjdfc.T_LOAD_DIMENSION where load_date='20220524';

2. 排除etl_date列,并把etl_date放到最后

    set hive.support.quoted.identifiers=none;
    select
    `(etl_date)?+.+`,etl_date
    from
        (select 
        row_number() over (partition by uid order by pay_time asc) as num
        ,*
        from order) first_order
    where num = 1;

3. 排除etl_date和uid列

    set hive.support.quoted.identifiers=none;
    select
    `(etl_date|uid)?+.+`,etl_date
    from
        (select 
        row_number() over (partition by uid order by pay_time asc) as num
        ,*
        from order) first_order
    where num = 1;

其他

1. 上面的 set hive.support.quoted.identifiers=none; 可以替换操作为：
   在 ‘hive-site.xml’ 中添加以下配置

   hive.support.quoted.identifiers=none