You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@hop.apache.org by "samdai (via GitHub)" <gi...@apache.org> on 2025/05/14 01:37:19 UTC
[D] How to improve the Snowflake upsert speed (hop)
GitHub user samdai created a discussion: How to improve the Snowflake upsert speed
Currently I want to load huge records from Salesforce, then upsert into Snowflake database, but the Insert/Update control is very slow to save data into Snowflake, when I use Snowflake Bulk Loader control, it's fast, but it can only insert records, can't upsert records, is there better solution for this issue?
GitHub link: https://github.com/apache/hop/discussions/5310
----
This is an automatically sent email for users@hop.apache.org.
To unsubscribe, please send an email to: users-unsubscribe@hop.apache.org
Re: [D] How to improve the Snowflake upsert speed (hop)
Posted by "liosc (via GitHub)" <gi...@apache.org>.
GitHub user liosc added a comment to the discussion: How to improve the Snowflake upsert speed
Another solution could be keeping an history table and use hash for detecting new/changed records. In case you have a lot of columns, this ca be faster than "Merge rows (diff)".
GitHub link: https://github.com/apache/hop/discussions/5310#discussioncomment-13140826
----
This is an automatically sent email for users@hop.apache.org.
To unsubscribe, please send an email to: users-unsubscribe@hop.apache.org
Re: [D] How to improve the Snowflake upsert speed (hop)
Posted by "dmainou (via GitHub)" <gi...@apache.org>.
GitHub user dmainou edited a comment on the discussion: How to improve the Snowflake upsert speed
Perhaps an architectural one?
Using the insert/update step requires reading the data to then upsert.
Couple of solutions.
1- Load the data into Snowflake then run a statement that accomplishes what you need.
or
1. Read your source data and snowflake data inputs on two streams
2. sort them to be in the same order
3. use the Merge rows (diff) transform to determine new, deleted, changed & identical
4. toss the identical aside
5. then bulk insert hat's needed, update as required, etc.
Ps I've previously dropt the length of a jub from 6+ hours to 15 minutes.
GitHub link: https://github.com/apache/hop/discussions/5310#discussioncomment-13138748
----
This is an automatically sent email for users@hop.apache.org.
To unsubscribe, please send an email to: users-unsubscribe@hop.apache.org
Re: [D] How to improve the Snowflake upsert speed (hop)
Posted by "dmainou (via GitHub)" <gi...@apache.org>.
GitHub user dmainou added a comment to the discussion: How to improve the Snowflake upsert speed
Perhaps an architectural one?
Using the insert/update step requires reading the data to then upsert.
Couple of solutions.
1- Load the data into Snowflake then run a statement that accomplishes what you need.
or
1. Read your source data and snowflake data inputs on two streams
2. sort them to be in the same order
3. use the Merge rows (diff) transform to determine new, deleted, changed & identical
4. bulk insert hat's needed, update as required, etc.
GitHub link: https://github.com/apache/hop/discussions/5310#discussioncomment-13138748
----
This is an automatically sent email for users@hop.apache.org.
To unsubscribe, please send an email to: users-unsubscribe@hop.apache.org
Re: [D] How to improve the Snowflake upsert speed (hop)
Posted by "dmainou (via GitHub)" <gi...@apache.org>.
GitHub user dmainou edited a comment on the discussion: How to improve the Snowflake upsert speed
Perhaps an architectural one?
Using the insert/update step requires reading the data to then upsert.
Couple of solutions.
1- Load the data into Snowflake then run a statement that accomplishes what you need.
or
1. Read your source data and snowflake data inputs on two streams
2. sort them to be in the same order
3. use the Merge rows (diff) transform to determine new, deleted, changed & identical
4. toss the identical aside
5. then bulk insert hat's needed, update as required, etc.
Ps I've previously dropped the length of a job from 6+ hours to 15 minutes.
GitHub link: https://github.com/apache/hop/discussions/5310#discussioncomment-13138748
----
This is an automatically sent email for users@hop.apache.org.
To unsubscribe, please send an email to: users-unsubscribe@hop.apache.org
Re: [D] How to improve the Snowflake upsert speed (hop)
Posted by "dmainou (via GitHub)" <gi...@apache.org>.
GitHub user dmainou added a comment to the discussion: How to improve the Snowflake upsert speed
And you can even combine both!
GitHub link: https://github.com/apache/hop/discussions/5310#discussioncomment-13150482
----
This is an automatically sent email for users@hop.apache.org.
To unsubscribe, please send an email to: users-unsubscribe@hop.apache.org
Re: [D] How to improve the Snowflake upsert speed (hop)
Posted by "liosc (via GitHub)" <gi...@apache.org>.
GitHub user liosc edited a comment on the discussion: How to improve the Snowflake upsert speed
Another solution could be keeping an history table and use hash for detecting new/changed records. In case you have a lot of columns, this can be faster than "Merge rows (diff)".
GitHub link: https://github.com/apache/hop/discussions/5310#discussioncomment-13140826
----
This is an automatically sent email for users@hop.apache.org.
To unsubscribe, please send an email to: users-unsubscribe@hop.apache.org