hive指定字段插入数据,包含了分区表和非分区表

1、建表

语句如下：

CREATE EXTERNAL TABLE `ods_lineitem_full`
(`l_shipdate`      date,`l_orderkey`      bigint,`l_linenumber`    int,`l_partkey`       int,`l_suppkey`       int,`l_quantity`      decimal(15, 2),`l_extendedprice` decimal(15, 2),`l_discount`      decimal(15, 2),`l_tax`           decimal(15, 2),`l_returnflag`    varchar(1),`l_linestatus`    varchar(1),`l_commitdate`    date,`l_receiptdate`   date,`l_shipinstruct`  varchar(25),`l_shipmode`      varchar(10),`l_comment`       varchar(44)
)PARTITIONED BY (`pt` int)ROW FORMAT SERDE'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'WITH SERDEPROPERTIES ('field.delim' = '|','serialization.format' = '|','serialization.null.format' = '')STORED AS INPUTFORMAT'org.apache.hadoop.mapred.TextInputFormat'OUTPUTFORMAT'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'LOCATION'hdfs://HAservice1/hive/warehouse/tpch/ods/ods_lineitem_full'TBLPROPERTIES ('compression.codec' = 'org.apache.hadoop.io.compress.GzipCodec','transient_lastDdlTime' = '1688000864');

2、指定字段插入数

insert into ods_lineitem_full partition(pt='2023') (l_shipdate, l_orderkey) select to_date("2023-09-05 00:00:00"),9128;

3查询数据是否插入成功

select * from ods_lineitem_full where pt='2023' and l_orderkey=9128;

非分区表指定字段插入数据

insert into ods_lxxxxm_full (l_shxxxate, l_orxxy) select to_date("2023-09-05 00:00:00"),9128;