Question

我有一个储存程序,执行3个insert/select,在Snowflake3个不同表格中填写了3个表格:附表Union、附表Union1、附表Union2。现在,由于每次发言要完成的时间,也就是要在执行发言的同时,缩短总体执行时间。为此,Im试图利用Promise.all。我不想在这里张贴所有发言稿,但这一样本很好地反映了我为什么要这样做:

CREATE OR REPLACE procedure etl."JimTest" ()
RETURNS VARCHAR
LANGUAGE JAVASCRIPT 
AS
$$

   async function runSQL(sqlarray) {

   snowflake.execute({ sqlText: sqlarray.sqltask } );
  }

   async function executeQuery() {

    sql1 = `INSERT into ScheduleUnion Select.....`
    sql2 = `INSERT into ScheduleUnion1 Select.....`
    sql3 = `INSERT into ScheduleUnion2 Select.....`

     const queries = [
        {sqltask: sql1},
        {sqltask: sql2},
        {sqltask: sql3}
    ];

    const sqlTasks = queries.map(runSQL);

    await Promise.all(sqlTasks); 
 }

  executeQuery();

  return  done ;
$$

然而,我仍然看到我以连续方式插入一份声明:

是否有

Answer 1

Might be a bit late, but better late than never:

Javascript parallelism within a single session is disabled in Snowflake. Therefore, regardless of any attempts to force asynchronous thread launch from within a Snowflake stored procedure, all calls will execute sequentially. In the OPs case, the .map() call will generate promises, and Promise.all() will wait for them all to resolve. But the underlying engine will not behave in a multithreaded fashion, so it s all pointless.

In order to perform multi-threaded parallel operations, one must use an external client -- e.g. an ETL tool -- which launches independent session threads in parallel, via an external driver. Both Talend and Matillion are readily capable of doing this. I would recommend Matillion, as it is easier to use and has a simpler deployment architecture.

Answer 2

A Snowflake warehouse is an MPP system that will schedule and execute commands in its queue based on the available processing capacity it has - you can t force it to run anything in parallel and it run will/may differ depending on resources available at run-time (i.e. what else is running).

如果问询速度不够快,你至少可以有2个斯诺瓦特特征:

Increase the size of the warehouse you are using
Run each query with a different warehouse

鉴于Snowflake允许所有这些物品都作包装,在你的印本开始时,你可以增加你的仓库面积(或建立/启动其他仓库),在你的印本结束时,你可以减少你的仓库面积(或关闭其他仓库)。

Answer 3

任务可以用来同时 st。

由于任务可以重叠的方式:

ALLOW_OVERLAPPING_EXECUTION = TRUE | FALSE

https://docs.snowflake.com/en/user-guide/tasks-intro.html#overlapping-dag-runs https://docs.snowflake.com/en/sql-reference/sql/create-task.html#syntax

You can create a procedure that does the insert, then create a task that runs that procedure. In your main function you can execute the task multiple times. The execute task command doesn t wait for the task to end, it runs in the background.

也可以通过询问 * 资料_schema.task_history,等到他们完成学业/失败等,来监测正在完成的任务。

Answer 4

一种选择是使用SnowSQLresults=False配置选项一起,该办法处理的是同步的查询(没有等待结果)。 Caveat:这意味着你需要单独核对结果。

如果你能够把所有问询带入一个文件库,那可以简单地说:

snowsql -o results=False -f queries.sql

友情链接