如何使用雪花程序将 javascript 数组插入到表中? [英] How to insert a javascript array into a table with snowflake procedure?

问题描述

我对雪花很陌生，我正在尝试使用存储过程将二维数组插入雪花中的表中.

Hi I’m fairly new to snowflake and I’m trying to insert a 2D array using a stored procedure into a table in snowflake.

数组看起来像这样 [[name,last,age],[name,last,age]..]有几千个项目.

The array looks like this [[name,last,age],[name,last,age]..] With a couple thousand items.

我尝试使用 INSERT INTO 使用循环，但需要很长时间.我需要一种更快的方法来插入数据.知道如何解决这个问题吗?

I tried using INSERT INTO using a loop but it just takes to long. I need a faster way to insert the data. Any idea how to approach this?

推荐答案

您可以尝试将多行批处理合并为一个 单个INSERT 语句.

You can try to batch a number of rows together into a single INSERT statement.

以下 javascript 代码片段说明了通过一系列 绑定参数 在分块行数上生成.我没有测试过它的运行情况，您可能需要在将其安装到现有程序中时进行调整.

The following snippet of javascript code illustrates one way of doing this via a sequence of binding parameters generated over a chunked number of rows. I've not tested it to run and you may need to make adjustments when fitting it into your existing procedure.

var dataArray = [[name,last,age], [name,last,age], …]

// Generates (?,?,?) if there are 3 cols per row
var row_bind = "(" + ",".join(Array(dataArray[0].length).fill("?")) + ")"

while (dataArray.length > 0) {

  // Consume upto 100 rows at a time
  var dataChunk = dataArray.splice(0, 100);

  // Generate (?,?,?),(?,?,?),[...] upto a 100 times
  var params = Array(dataChunk.length).fill(row_bind).join(",");

  // Place the bind params into the query
  var statement = "INSERT […] VALUES " + params;

  // Prepare a statement with a flat stream of row/col data sent along
  var stmt = snowflake.createStatement({
    sqlText: statement,
    binds: dataChunk.flat()
  });

  // Perform your 'stmt' logic, iterate over for the next batch...
}

注意:常规 SQL 无法实现您想要做的事情吗?调用过程实际上是单线程的，如果您迭代 SQL 查询可以更好地处理的数据集，则性能会降低.

Note: Is what you are trying to do impossible from regular SQL? Call procedures are effectively single-threaded and will lack in performance if you're iterating over datasets which a SQL query can handle far better.

通过 Snowflake 的存储过程使用熟悉的命令式编程语言似乎很有吸引力，但它们不是为数据加载或数据转换而设计的，并且在以这种方式使用时不能扩展以使用仓库大小.

It could appear attractive to use a familiar, imperative programming language via Snowflake's stored procedures but they aren't designed for data loading or data transformations and do not scale to use the warehouse sizes when used this way.

这篇关于如何使用雪花程序将 javascript 数组插入到表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！