使用 Vertica 插入 SQL 表时出现资源不足错误 [英] Insufficient Resources error while inserting into SQL table using Vertica

问题描述

我正在运行 Python 脚本以将数据从 DataFrame 加载到 SQL 表中.但是，插入命令抛出了这个错误:

I'm running a Python script to load data from a DataFrame into a SQL Table. However, the insert command is throwing this error:

(pyodbc.Error) ('HY000', '[HY000] ERROR 3587:  Insufficient resources to execute plan on pool fastlane [Request exceeds session memory cap: 28357027KB > 20971520KB]\n (3587) (SQLExecDirectW)')

这是我的代码:

df.to_sql('TableName',engine,schema='trw',if_exists='append',index=False) #copying data from Dataframe df to a SQL Table

推荐答案

你能帮我做以下事情吗:

Can you do the following for me:

运行此命令 - 并共享输出.MAXMEMORYSIZE、MEMORYSIZE 和 MAXQUERYMEMORYSIZE 以及 PLANNEDCONCURRENCY 可让您了解计划查询/复制命令时的(内存)预算.

run this command - and share the output. MAXMEMORYSIZE, MEMORYSIZE and MAXQUERYMEMORYSIZE, plus PLANNEDCONCURRENCY give you an idea of the (memory) budget at the time when the query / copy command was planned.

gessnerm@gessnerm-HP-ZBook-15-G3:~/1/fam/fam-ostschweiz$ vsql -x -c \
 "select * from resource_pools where name='fastlane'"
-[ RECORD 1 ]------------+------------------
pool_id                  | 45035996273841188
name                     | fastlane
is_internal              | f
memorysize               | 0%
maxmemorysize            | 
maxquerymemorysize       | 
executionparallelism     | 16
priority                 | 0
runtimepriority          | MEDIUM
runtimeprioritythreshold | 2
queuetimeout             | 00:05
plannedconcurrency       | 2
maxconcurrency           | 
runtimecap               | 
singleinitiator          | f
cpuaffinityset           | 
cpuaffinitymode          | ANY
cascadeto                | 

然后，您应该从 QUERY_REQUESTS 系统表中挖掘 Python 脚本触发的实际 SQL 命令.它的格式应该是:

Then, you should dig, out of the QUERY_REQUESTS system table, the acutal SQL command that your python script triggered. It should be in the format of:

COPY <_the_target_table_> 
FROM STDIN DELIMITER ',' ENCLOSED BY '"' 
DIRECT REJECTED DATA '<_bad_file_name_>'

或类似的.

那么:文件有多大/您尝试一次性加载的文件是多少?如果太大，那么 B.Muthamizhselvi 是正确的 - 您需要分配加载的数据量.

Then: how big is the file / are the files you're trying to load in one go? If too big, then B.Muthamizhselvi is right - you'll need to portion the data volume you load.

你也可以运行:

vsql -c "SELECT EXPORT_OBJECTS('','<schema>.<table>',FALSE)"

...并分享输出?很可能是您有太多的投影让内存不够用，您正在按太多列进行排序.

.. .and share the output? It could well be that you have too many projections for the memory to be enough, that you are sorting by too many columns.

希望这对初学者有所帮助...

Hope this helps for starters ...

这篇关于使用 Vertica 插入 SQL 表时出现资源不足错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！