Hologres中如何使用INSERT插入数据_实时数仓 Hologres(Hologres)-阿里云帮助中心

INSERT语句用于插入新的行数据至表中。本文为您介绍在实时数仓Hologres中如何使用INSERT插入数据。

命令介绍

您可以插入一个或多个由表达式指定的行，以及插入来自一个查询的零行或多行数据至Hologres。语句如下。

INSERT INTO <schema>.<table> [( <column> [, ...] )]
   VALUES ( {<expression>}  [, ...] )
   [, ...] | <query>}

参数说明如下。

参数	描述
schema	表所在的Schema名称。
table	已创建的表名称。通过Flink写入时可以插入父表并自动路由到子表。从Hologres V1.3版本起，支持符合FixedPlan的Insert语句直接写入分区表父表，详情请参见Fixed Plan加速SQL执行。
column	table表中的某个列的名称。您也可以使用子域名或者数组下标限定列名称。（指向一个组合列的某些列中插入会让其他域为空）。
expression	赋予相应列的表达式或值。
query	需要被插入行的SELECT查询语句。语法详情请参见SELECT语句。

目前INSERT只支持以下两种数据写入方式：

insert into values：

INSERT INTO holo2mysqltest (cate_id, cate_name) VALUES
    (3, 'true'),
    (3, 'fale'),
    (3, 'trxxue'),
    (3, 'x'),
    (4, 'The Dinner Game');

insert into select：
```
INSERT INTO test2
SELECT * FROM test1;
```

技术原理

Hologres表的存储结构分为行存、列存和行列共存，但三者的写入原理一致。

如下图所示，INSERT是以Append Only的方式写入WAL，并实时更新到内存表（MemTable），保证数据实时可见。但Mem Table有一定的大小，写满了之后会切换新的MemTable，并触发后台的异步Flush过程，将其中的数据逐渐Flush到文件中，文件存储在Pangu中。在Flush的过程中会产生很多小文件，后台会将这些小文件做合并和整理，即Compaction。为了写速度尽可能的快，后台会先将数据写完，待异步Compaction时再执行压缩和整理，因此会看到在数据写入过程中，数据的存储会一定的膨胀，等数据写入之后，Compaction完成后存储会下降。技术原理

对于行存、列存、行列共存表的区别一方面是内存的Mem Table的索引格式不同，另外一方面在Flush到文件的过程中：

行存表：会Flush成行存储的文件，即SST格式。
列存表：会Flush成列存文件，即ORC格式。
行列共存表：会分别Flush为行存和列存两种文件，SST和ORC格式，在Flush过程中会保证数据的一致性，只有行存、列存都同时Flush完成才会返回成功，同时在存储上行列共存相当于是两份存储，因此行列共存的表在存储上会有一定的牺牲。

使用限制

若是分区表，必须插入具体的子表，不能插入分区父表，且插入时，对应的分区字段值要和子表分区字段值一致。
目标列的名称可以以任意顺序列出。如果使用insert into select的方式插入数据，目标列和查询列类型需要一一对齐。

使用示例

说明

Hologres从V2.1.17版本起支持Serverless Computing能力，针对大数据量离线导入、大型ETL作业、外表大数据量查询等场景，使用Serverless Computing执行该类任务可以直接使用额外的Serverless资源，避免使用实例自身资源，无需为实例预留额外的计算资源，显著提升实例稳定性、减少OOM概率，且仅需为任务单独付费。Serverless Computing详情请参见Serverless Computing概述，Serverless Computing使用方法请参见Serverless Computing使用指南。

插入普通表。

CREATE TABLE  holotest (
    a int,
    b bigint,
    c bool,
    e decimal(38,10),
    f text,
    g timestamp,
    h timestamptz,
    i jsonb,
    j int[]
);

-- （可选）推荐使用Serverless Computing执行大数据量离线导入和ETL作业
SET hg_computing_resource = 'serverless';

-- 导入数据
INSERT INTO holotest VALUES (1,9223372036854775807,false,123.123456789123,'john','2020-01-01 01:01:01.123456',
'2004-10-19 10:23:54+08','{"a":2}',ARRAY[1, 2, 3, 4]);

-- 重置配置，保证非必要的SQL不会使用serverless资源。
RESET hg_computing_resource;

从a表插入数据至b表。

CREATE TABLE holotest2(
    a int,
    b bigint,
    c bool);

-- （可选）推荐使用Serverless Computing执行大数据量离线导入和ETL作业
SET hg_computing_resource = 'serverless';

-- 导入数据
INSERT INTO holotest2 (a,b,c) SELECT a,b,c FROM holotest;

-- 重置配置，保证非必要的SQL不会使用serverless资源。
RESET hg_computing_resource;

插入分区表数据。

--示例：在public schema下创建不带主键的分区父表和对应的分区子表
BEGIN;
CREATE TABLE public.hologres_parent(
  a text,
  b int,
  c timestamp,
  d text
)
  PARTITION BY list(a);
CALL set_table_property('public.hologres_parent', 'orientation', 'column');

CREATE TABLE public.hologres_2022 partition of public.hologres_parent for values in('2022');

CREATE TABLE public.hologres_2021 partition of public.hologres_parent for values in('2021');

CREATE TABLE public.hologres_2020 partition of public.hologres_parent for values in('2020');
COMMIT;

-- （可选）推荐使用Serverless Computing执行大数据量离线导入和ETL作业
SET hg_computing_resource = 'serverless';

--插入分区子表
INSERT INTO public.hologres_2022 values('2022',1,now(),'a')

-- 重置配置，保证非必要的SQL不会使用serverless资源。
RESET hg_computing_resource;

常见问题

问题一：大数据量写入时，实例CPU和内存资源使用率100%，影响实例其他写入与查询任务。
建议使用Serverless Computing能力执行大数据量写入任务。
说明
Hologres从V2.1.17版本起支持Serverless Computing能力，针对大数据量离线导入、大型ETL作业、外表大数据量查询等场景，使用Serverless Computing执行该类任务可以直接使用额外的Serverless资源，避免使用实例自身资源，无需为实例预留额外的计算资源，显著提升实例稳定性、减少OOM概率，且仅需为任务单独付费。Serverless Computing详情请参见Serverless Computing概述，Serverless Computing使用方法请参见Serverless Computing使用指南。
问题二：数据在写入时，为什么监控指标中存储用量上涨非常多，写入完成后存储用量又下降？
根据写入的技术原理，为了尽可能快地写入数据，后台会先将数据写完，待异步Compaction时再执行压缩和整理，因此会看到在数据写入过程中，数据的存储会一定的膨胀，等数据写入之后，Compaction完成后存储会下降。
问题三：同一张表并行执行insert命令时，延迟增加。
没有走Fixed Plan的insert是表锁，并行执行insert会导致等锁时间增加，从而造成延迟增加。
问题四：数据写入分区父表报错：ERROR: no partition of relation "<table_name>" found for row。
- 报错信息：ERROR: no partition of relation "<table_name>" found for row。
- 问题原因：分区子表不存在。
- 解决方法：写入数据前，需创建对应的分区子表，命令示例如下。
```
CREATE TABLE <child_table_name> partition of <parent_table_name> for values in (<value>);
```
问题五：在导入数据的时候报错：Currently inserting into parent table is not supported。
- 报错信息：导入数据时报错：Currently inserting into parent table is not supported。
- 问题原因：当前Insert的表，是一张分区父表，Hologres不支持直接写入分区父表。
- 解决方法：需要写入对应的分区子表。