CREATE EXTERNAL TABLE nretail_ability_order.retail_report ( sub_order_no STRING COMMENT '订单号', order_source STRING COMMENT '订单来源:1.T销客 2.方舟 3.方舟移动端 8.T云店', company_name STRING COMMENT '开单客户商务中心', customer_code STRING COMMENT '开单客户编码', customer_name STRING COMMENT '开单客户名称', create_time STRING COMMENT '开单时间', product_line STRING COMMENT '产品线', sku STRING COMMENT '产品skuNo', commodity_name STRING COMMENT '商品名称', product_model STRING COMMENT '产品型号', spec STRING COMMENT '规格', sort STRING COMMENT '分类', quantity INT COMMENT '购买数量', total_price STRING COMMENT '总金额', NAME STRING COMMENT '收货人', telephone STRING COMMENT '收货人手机号', receiving_address STRING COMMENT '收货地址', uuid STRING COMMENT '商品' ) PARTITIonED BY ( dt STRING COMMENT '时间' ) ROW FORMAT DELIMITED FIELDS TERMINATED BY 'u0001' WITH SERDEPROPERTIES ( 'field.delim' = 'u0001', 'serialization.format' = 'u0001' ) STORED AS TEXTFILE LOCATION 'hdfs://nameservice1/user/hive/warehouse/nretail_ability_order.db/retail_report' TBLPROPERTIES ( 'last_modified_by' = 'root', 'last_modified_time' = '1640658557' )增加字段的语句
Alter table retail_report add column buy_proof_img STRING comment '购机凭证图片URL';查看文件里面是有值的
hdfs dfs -text /user/hive/warehouse/nretail_ability_order.db/retail_order_detail/dt=2021-12-23/part-m-00000.snappyimpala查询是有值的 直接hive查询就都是null 2.解决方案
直接删除表、重新建表，将新增字段在建表的时候就增加上来
CREATE EXTERNAL TABLE nretail_ability_order.retail_report ( sub_order_no STRING COMMENT '订单号', order_source STRING COMMENT '订单来源:1.T销客 2.方舟 3.方舟移动端 8.T云店', company_name STRING COMMENT '开单客户商务中心', customer_code STRING COMMENT '开单客户编码', customer_name STRING COMMENT '开单客户名称', create_time STRING COMMENT '开单时间', product_line STRING COMMENT '产品线', sku STRING COMMENT '产品skuNo', commodity_name STRING COMMENT '商品名称', product_model STRING COMMENT '产品型号', spec STRING COMMENT '规格', sort STRING COMMENT '分类', quantity INT COMMENT '购买数量', total_price STRING COMMENT '总金额', NAME STRING COMMENT '收货人', telephone STRING COMMENT '收货人手机号', receiving_address STRING COMMENT '收货地址', uuid STRING COMMENT '商品', buy_proof_img STRING COMMENT '购机凭证图片URL' ) PARTITIonED BY ( dt STRING COMMENT '时间' ) ROW FORMAT DELIMITED FIELDS TERMINATED BY 'u0001' WITH SERDEPROPERTIES ( 'field.delim' = 'u0001', 'serialization.format' = 'u0001' ) STORED AS TEXTFILE LOCATION 'hdfs://nameservice1/user/hive/warehouse/nretail_ability_order.db/retail_report' TBLPROPERTIES ( 'last_modified_by' = 'root', 'last_modified_time' = '1640658557' )
这样数据load上来以后就能和mysql保持一致。hive也能查出来。impala也能查出来， 如果关联以后的中间表用的这个字段，也需要提前将表重新建了。否则也会出现查不出来的情况
