主页 / dba / 问题

问题[bulk-insert](dba)

Martin Hope
gavenkoa
Asked: 2021-12-07 04:26:32 +0800 CST
  • 0

是否可以将数据移动到新表中以获取其他表而不会在中间丢失进度？

我认为plpgsql在每 N 次插入后按块读取数据并提交......如果过程在中间停止，则条件NOT EXISTS (...)允许通过再次重复过程来添加丢失的数据。

我认为它是重要的模式，并且可能直接使用以下语法支持它：

INSERT INTO ... SELECT ... WHERE ... AND NOT EXISTS (...)

复制中断的原因可能是约束或用户暂停操作。我们仍然希望不丢失进度和恢复过程（在NOT EXISTS (...)警卫的帮助下）。

postgresql bulk-insert
Martin Hope
josteinb
Asked: 2021-08-24 05:34:40 +0800 CST
  • 2

概括

我正在尝试将数据批量插入 timescaledb 超表中。无论我尝试什么，内存使用量都会逐渐增长，直到服务器进程由于内存不足而被终止。我在服务器上的数据集小至 160 万行时观察到这一点，其中 128 GB 的 RAM 可用于 postgres/timescaledb，所以我一定做错了什么。对不是超表的表执行完全相同的插入操作就可以了，因此问题必须与 timescaledb 有关。

我想要做什么

我希望插入的表定义如下：

CREATE TABLE test (
    gid BIGINT NOT NULL,
    location_ GEOGRAPHY(POINT),
    -- about 15 other VARCHAR and BIGINT columns omitted for brevity
    far_end_gid BIGINT NOT NULL,
    day_partition_key DATE NOT NULL,
    PRIMARY KEY (gid, far_end_gid, day_partition_key)
);
SELECT create_hypertable(
    'test', 'day_partition_key', chunk_time_interval => INTERVAL '1 day');

要插入的数据在数据库中的另一个表中（通过\COPY操作填充的临时表）；除了某些字段需要一些解析（将字符串转换为日期等）之外，此另一个表具有与目标超表相同的字段。失败的INSERT查询是

INSERT INTO topology_test
    SELECT
        tobigintornull(gid) AS gid_as_int,
        -- about 15 other VARCHAR and BIGINT columns omitted
        CAST(CAST(REPLACE(far_end_gid, ',', '.') AS DOUBLE PRECISION) AS BIGINT),
        CAST(tobigintornull(far_end_cell_id) AS BIGINT),
        TO_DATE(day_partition_key, 'YYYYMMDD')
    FROM test_temp_table
    WHERE
        tobigintornull(gid) is not null
        and tobigintornull(REPLACE(far_end_gid, ',', '.')) is not null
        and day_partition_key is not null
    ON CONFLICT DO NOTHING;

该ON CONFLICT部分旨在静默删除重复的主键，并且该函数tobigintornull执行其名称所暗示的操作：如果可能，它将输入转换为 bigint，如果不是，则返回 null（这有助于删除无法解析的行）。它的定义是

CREATE OR REPLACE FUNCTION tobigintornull(text) RETURNS BIGINT AS $$
DECLARE x BIGINT;
BEGIN
    x = $1::BIGINT;
    RETURN x;
EXCEPTION WHEN others THEN
    RETURN NULL;
END;
$$
STRICT
LANGUAGE plpgsql IMMUTABLE;

输入的定义DOUBLE PRECISION类似。WHERE请注意，该子句仅删除了一小部分行（绝对小于 1%） 。

完整的数据集是 5.93 亿行，并且没有以任何方式排序。怀疑输入缺乏排序是问题的一部分，我创建了一个包含 160 万行的数据子集，其中所有行的值都相同day_partition_key（从 timescaledb 的角度来看，该子集应该是完美排序的）。

问题

问题表现为 postgres 的内存使用量逐渐增加，直到数据库可用的全部 128 GB 被使用（大约需要 5 分钟）。在 100% 内存使用一分钟左右后，插入查询崩溃。日志显示以下内容：

timescaledb_1  | 2021-08-23 11:56:50.727 UTC [1] LOG:  server process (PID 1231) was terminated by signal 9: Killed
timescaledb_1  | 2021-08-23 11:56:50.727 UTC [1] DETAIL:  Failed process was running: INSERT INTO test
timescaledb_1  |            SELECT

(...)

timescaledb_1  | 2021-08-23 11:56:50.727 UTC [1] LOG:  terminating any other active server processes
timescaledb_1  | 2021-08-23 11:56:50.741 UTC [1215] WARNING:  terminating connection because of crash of another server process
timescaledb_1  | 2021-08-23 11:56:50.741 UTC [1215] DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
timescaledb_1  | 2021-08-23 11:56:50.741 UTC [1215] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
timescaledb_1  | 2021-08-23 11:56:50.744 UTC [1221] WARNING:  terminating connection because of crash of another server process
timescaledb_1  | 2021-08-23 11:56:50.744 UTC [1221] DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
timescaledb_1  | 2021-08-23 11:56:50.744 UTC [1221] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
timescaledb_1  | 2021-08-23 11:56:50.771 UTC [1] LOG:  all server processes terminated; reinitializing
timescaledb_1  | 2021-08-23 11:56:51.371 UTC [1259] LOG:  database system was interrupted; last known up at 2021-08-23 11:50:40 UTC
timescaledb_1  | 2021-08-23 11:56:51.711 UTC [1259] LOG:  database system was not properly shut down; automatic recovery in progress
timescaledb_1  | 2021-08-23 11:56:51.720 UTC [1259] LOG:  redo starts at 25A/16567EF8
timescaledb_1  | 2021-08-23 11:56:51.788 UTC [1259] LOG:  invalid record length at 25A/165C1A30: wanted 24, got 0
timescaledb_1  | 2021-08-23 11:56:51.788 UTC [1259] LOG:  redo done at 25A/165C19D0
timescaledb_1  | 2021-08-23 11:56:51.893 UTC [1] LOG:  database system is ready to accept connections
timescaledb_1  | 2021-08-23 11:56:52.039 UTC [1265] LOG:  TimescaleDB background worker launcher connected to shared catalogs

显然，数据库恢复后目标表中不存在任何行。

我试图解决的问题

  • 我已将数据集的大小从 5.93 亿行减少到 160 万行，确保子集对于用于分块的日期列只有一个值 ( day_partition_key)。结果是完全一样的。
  • 在https://github.com/timescale/timescaledb/issues/643的讨论之后，我将 timescaledb 配置更改为SET timescaledb.max_open_chunks_per_insert=1;. 问题仍然完全相同。
  • 我尝试创建另一个目标表而不将其设为超表。然后插入 1.6 M 行子集就可以了。我希望整套都能奏效，但我还没有花时间去做。

版本、硬件和配置

docker 镜像 timescale/timescaledb-postgis:latest-pg13 (bf76e5594c98) 用于运行 timescaledb。它包含：

  • x86_64-pc-linux-musl 上的 PostgreSQL 13.3，由 gcc (Alpine 10.2.1_pre1) 10.2.1 20201203 编译，64 位
  • 时标数据库 2.3.0
  • POSTGIS="2.5.5" [EXTENSION] PGSQL="130" GEOS="3.8.1-CAPI-1.13.3" PROJ="Rel. 7.1.1，2020 年 9 月 1 日" GDAL="GDAL 3.1.4， 2020 年 10 月 20 日发布" LIBXML="2.9.10" LIBJSON="0.15" LIBPROTOBUF="1.3.3" RASTER

docker 容器限制为 16 个内核和 128 GB 内存。对于上面提到的测试，我使用了https://pgtune.leopard.in.ua/#/建议的配置参数用于带有我的参数的数据仓库，除了我将提供给 pgtune 的可用内存降低到 64 GB，希望这会导致数据库使用更少内存的配置（我也尝试了推荐用于 128 GB 内存的设置，结果是相同的）。设置如下：

max_connections = 30
shared_buffers = 16GB
effective_cache_size = 48GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 500
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 34952kB
min_wal_size = 4GB
max_wal_size = 16GB
max_worker_processes = 16
max_parallel_workers_per_gather = 8
max_parallel_workers = 16
max_parallel_maintenance_workers = 4
partitioning bulk-insert
Martin Hope
B.Muthamizhselvi
Asked: 2019-10-15 22:38:45 +0800 CST
  • 1

我的数据格式如下。我必须插入到 sql server 表中。

agency    NO
booknbr   06000330
bookdttm  2006-07-19 10:56:00
arrdttm   2006-05-07 05:42:00
aj_id     A000009454
casenbr   06006640
off1      619
off2      634
arrplace  DENNYS RESTAURANT
howarr    O
juvstat   
ko        
remarks   
armed     
federal   N

agency    NO
booknbr   06000331
bookdttm  2006-07-24 12:11:00
arrdttm   2006-07-16 16:11:00
aj_id     A000003215
casenbr   06010336
off1      641
off2      
arrplace  219 W GLENCOVE AV
howarr    V
juvstat   
ko        
remarks   
armed     
federal   N

agency    NO
booknbr   06000332
bookdttm  2006-07-25 15:11:00
arrdttm   2006-06-13 22:47:00
aj_id     A000009455
casenbr   06008615
off1      624
off2      
arrplace  113 JULIE DR
howarr    V
juvstat   
ko        
remarks   
armed     
federal   N
sql-server bulk-insert
Martin Hope
Kevin Cianfarini
Asked: 2019-07-12 15:19:49 +0800 CST
  • 1

我试图了解类似之间的性能差异

INSERT INTO Person (id, name) VALUES
(1, "Kevin"),
(2, "John"),
(3, "Jane"),
...

和

BEGIN TRANSACTION;

INSERT INTO Person (id, name) VALUES (1, "Kevin");
INSERT INTO Person (id, name) VALUES (2, "John");
INSERT INTO Person (id, name) VALUES (3, "Jane");
...

END TRANSACTION;

我知道在交易期间索引是临时建立的，但我不太确定。我也不知道两者之间的其他性能差异。

transaction bulk-insert
Martin Hope
NOOK
Asked: 2019-07-02 01:30:50 +0800 CST
  • 0

我们有一个在 SQL Server 2016 中运行的批量导入任务，它导入了超过 500 万行。任务本身使用第三方服务在云端进行配置。奇怪的是，正在导入的新数据被附加到数据文件（.mdf）中，并且没有使用已经分配的空白空间。表被截断并重新加载。所有表都定义了聚集索引。我们甚至使用了截断所有相关表的预处理任务 -> 重新组织所有相关表 -> DBCC updateusage，但无济于事。我们甚至将数据文件的大小增加了一倍，但我们仍然看到一些自动增长事件。尽管此类事件减少了 80%。导入后，我们剩下 70% 的数据文件是空的。我是否遗漏了 SQL Server 2016 中的某些内容或任何特定设置/更改，这就是我们遇到这种行为的原因？谢谢。

sql-server-2016 bulk-insert
Martin Hope
Patrick
Asked: 2019-06-25 11:48:32 +0800 CST
  • 0

我正在尝试使用批量插入将数据从 csv 文件插入到 sql server 表中，但它返回 0 行。

这是我正在使用的批量插入语句

BULK INSERT dbo.DEA_Availability from 'C:\SQLPOWERDOC\CSV\DEA_Availability.csv' 
with (
FIRSTROW=2,
FIELDTERMINATOR=',', 
ROWTERMINATOR='\r\n',
ERRORFILE = 'C:\SQLPOWERDOC\CSV\DEA_Availability_ERROR.log',
DATAFILETYPE='char',
keepnulls
)

表定义为：

CREATE TABLE [dbo].[DEA_AVAILABILITY](
    [Server_Name] [varchar](max) NULL,
    [Database_Name] [varchar](max) NULL,
    [Priority] [varchar](max) NULL,
    [Description] [varchar](max) NULL,
    [Details] [varchar](max) NULL,
    [URL] [varchar](max) NULL,
    [IMPORT_DATE] [datetime2](7) NULL
) ON [PRIMARY]
GO

这是 CSV 文件的内容

Server Name,Database Name,Priority,Description,Details,URL HOSTNAME,,None,Cluster Node,This is a node in a cluster.,http://msdn.microsoft.com/en-us/library/ms189134(v=sql.100).aspx

我已验证 CSV 文件的每一行都以 \r\n 结尾

sql-server bulk-insert
Martin Hope
Benjamin Soulas
Asked: 2018-10-25 01:32:39 +0800 CST
  • 0

我需要执行一个测试，我必须在 PostgreSQL（和 TimescaleDB）数据库中模拟 20 年的历史数据。我的问题是我必须生成.sql文件并在目标数据库中摄取它们（使用psql-client ）。

我们做了一些计算，结果是我们的数据库将有2610亿行在我们的表中 20 年，所以每年包含13.05B数据。

对于每一行，我们都有一个时间戳（整数类型），我认为，为了提高效率，我在我的 .sql 文件事务中写了10.000 个元素。 为了使生成的.sql文件在磁​​盘空间中变小（我在 python 中生成这些文件），我将每个文件限制为20M 行。

所以我想我可以在 bash 文件中动态生成这些文件，当生成一个文件时，我运行一个 psql 命令将它摄取到数据库中，但问题是我不知道如何处理它： ingest 过程比 .sql 文件生成花费更多时间，所以在 bash 命令中，恐怕我的 bash 脚本会等待 ingest 过程，然后开始生成新的 .sql 文件并再次执行 ingest 过程。

总而言之，我尝试创建一个伪批处理摄取过程，其中每个已成功摄取的生成的 .sql 文件都将被删除，以避免占用太多磁盘空间。

如何避免在开始另一个 .sql 文件生成之前等待摄取过程然后开始摄取过程的事实？

这是出于开发目的，我想要摄取的这些数据接近于我们想要在生产模式下创建的数据。目前的目标是执行读取请求并在 PostgreSQL 和 TimescaleDB 中比较这些请求。

postgresql bulk-insert
Martin Hope
Jeremy Holovacs
Asked: 2017-06-08 05:18:41 +0800 CST
  • 4

我有一个这样定义的表：

CREATE TABLE [dbo].[IpMetadata](
    [StartIp] [bigint] NOT NULL,
    [EndIp] [bigint] NOT NULL,
    [CountryCode] [char](10) NOT NULL,
    [ProxyType] [varchar](50) NULL,
    [ProxyDescription] [varchar](50) NULL,
    [IspName] [varchar](100) NULL,
    [MobileCarrier] [varchar](50) NULL,
    [MobileCarrierCode] [varchar](50) NULL,
    [Latitude] [varchar](50) NULL,
    [Longitude] [varchar](50) NULL,
    [PostalCode] [varchar](50) NULL,
    [City] [varchar](50) NULL,
    [Region] [varchar](50) NULL,
    [Country] [varchar](50) NULL,
    [GmtOffset] [varchar](50) NULL,
    [SupportsDaylightSavings] [char](10) NULL,
    [MetroCode] [varchar](50) NULL,
    [AddressCount] [int] NOT NULL,
     CONSTRAINT [PK_IpMetadata] PRIMARY KEY CLUSTERED 
    (
        [StartIp] ASC,
        [EndIp] ASC
    )
)

我有一个 UTF-8 编码的采样文件 (D:\data\ipsnip.csv)，其中包含制表符分隔、CRLF 终止的行，可以像这样插入到这个表中：

#start-ip   end-ip  edge-two-letter-country proxy-type  proxy-description   isp-name    mobile-carrier  mobile-carrier-code edge-latitude   edge-longitude  edge-postal-code    edge-city   edge-region edge-country    edge-gmt-offset edge-in-dst edge-metro-code address-count
0   0   **                  0   0   0   0   reserved    *** *** +9999   n   -1  0
1   255 **                  0   0   0   0   reserved    *** *** +9999   n   -1  254
256 16777215    **                  0   0   0   0   reserved    *** *** +9999   n   -1  16776959
16777216    16777343    au                  0   -37.7596    145.134 3106    templestowe vic aus +1000   n   36211   127
16777344    16777407    au                  0   -37.7596    145.134 3106    templestowe vic aus +1000   n   36211   63
16777408    16777471    au                  0   -37.7596    145.134 3106    templestowe vic aus +1000   n   36211   63
16777472    16778239    cn          chinanet fujian province network        0   26.0786 119.298 350000  fuzhou  35  chn +800    n   156115  767
16778240    16779263    au          big red group       0   -37.8387    144.99  3141    south yarra vic aus +1000   n   36206   1023
16779264    16781311    cn          chinanet guangdong province network     0   30.6611 104.082 510000  guangzhou   44  chn +800    n   156196  2047
16781312    16785407    jp          i2ts inc.       0   35.6838 139.754 100-0001    tokyo   13  jpn +900    n   -1  4095

我像这样运行 BCP 命令：

bcp MyDatabase.dbo.IpMetadata in D:\data\ipsnip.csv -F2 -Slocalhost -n -T

我得到这样的回复：

Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 11 for SQL Server]Unexpected EOF encountered in BCP data-file

BCP copy in failed

我已经尝试明确指定列和行终止符。我尝试过使用 unicode 列定义。我尝试将行尾更改为 LF 而不是 CRLF。我尝试用分号/管道替换字段终止符。我试过-n和-N. 我不知道还能尝试什么。有人可以帮忙吗？

sql-server bulk-insert
Martin Hope
Matthew J
Asked: 2016-08-15 13:55:31 +0800 CST
  • 2

我是 SQLite 和 SQL 初学者，所以请多多包涵。我正在尝试创建一个简单的播客索引数据库。EPISODE这是 my 、CAST_MEMBER和EPISODE_CAST表的架构。

CREATE TABLE EPISODE
(
    id                  INTEGER PRIMARY KEY,
    show_number         INTEGER UNIQUE NOT NULL,
    air_date            DATE UNIQUE NOT NULL,
    runtime             VARCHAR(255) NOT NULL
);

CREATE TABLE CAST_MEMBER
(
    id          INTEGER PRIMARY KEY,
    person_name VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE EPISODE_CAST
(
    episode_id      INTEGER NOT NULL,
    cast_member_id  INTEGER NOT NULL,
    PRIMARY KEY(episode_id, cast_member_id),
    FOREIGN KEY(episode_id) REFERENCES EPISODE(id),
    FOREIGN KEY(cast_member_id) REFERENCES CAST_MEMBER(id)
);

我正在尝试找到一种快速填充 EPISODE_CAST 表的方法。我知道所有的关系应该是什么，我只是不想写一百万个插入语句。例如，节目的每一集都有一位演员，所以我自然希望在表格中添加 550 行

episode_id | cast_member_id
===========================
1          | 1
2          | 1
3          | 1

… 等等。

所以这是我写的查询

INSERT INTO EPISODE_CAST (episode_id, cast_member_id) VALUES
   ((SELECT id FROM EPISODE WHERE show_number BETWEEN 1 AND 550),
   (SELECT id FROM CAST_MEMBER WHERE person_name='John Smith'));

但我只插入了一行。（第 1|1 行。）所以我显然在这里遗漏了一些东西。某种循环……

一旦我有了那个演员，我也想和其他演员做类似的事情。但对他们来说，我将使用一个IN包含该人所在的所有剧集的子句（大多数是不连续的）。

再次为我的幼稚感到抱歉。但感谢您的帮助。

sqlite bulk-insert