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');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

2、指定字段插入数

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

3查询数据是否插入成功

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

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

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