Change download / ETL process to directly insert tables via DBI interface

Question

Change download / ETL process to directly insert tables via DBI interface

Opened this issue 6 months ago · 4 comments

Answer 1 · 2024-03-14T17:10:00.000Z

The problem with this approach is that DBI::dbAppendTable(...) does RBAR inserts, which will make a mess of the transaction log. The reason we have been writing delimited files to disk is that we can then BULK INSERT them (in SQL Server, other DBMS have similar tools), thus avoiding the transaction log for everything but structural changes (eg page extent addition) to the table.

Answer 2 · 2024-03-14T17:32:58.000Z

would it then make sense to write the translated files out to disk then? I am not sure what the advantage to avoiding the transaction log is...so can't weigh the value vs the cost

…

On Thu, Mar 14, 2024 at 1:10 PM Nathan Palmer ***@***.***> wrote: The problem with this approach is that DBI::dbAppendTable(...) does RBAR inserts, which will make a mess of the transaction log. The reason we have been writing delimited files to disk is that we can then BULK INSERT them (in SQL Server, other DBMS have similar tools), thus avoiding the transaction log for everything but structural changes (eg page extent addition) to the table. — Reply to this email directly, view it on GitHub <#178 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AC7TWAZ5ARZ22WUCB5COSGLYYHKX7AVCNFSM6AAAAABENAALRSVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSOJXHE2DEMRSGE> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

-- Robert Gentleman ***@***.***

Answer 3 · 2024-03-14T18:10:05.000Z

Yes exactly. NathanOn Mar 14, 2024, at 1:33 PM, Robert Gentleman ***@***.***> wrote: would it then make sense to write the translated files out to disk then? I am not sure what the advantage to avoiding the transaction log is...so can't weigh the value vs the cost

…

On Thu, Mar 14, 2024 at 1:10 PM Nathan Palmer ***@***.***> wrote: The problem with this approach is that DBI::dbAppendTable(...) does RBAR inserts, which will make a mess of the transaction log. The reason we have been writing delimited files to disk is that we can then BULK INSERT them (in SQL Server, other DBMS have similar tools), thus avoiding the transaction log for everything but structural changes (eg page extent addition) to the table. — Reply to this email directly, view it on GitHub <#178 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AC7TWAZ5ARZ22WUCB5COSGLYYHKX7AVCNFSM6AAAAABENAALRSVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSOJXHE2DEMRSGE> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

-- Robert Gentleman ***@***.*** —Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you authored the thread.Message ID: ***@***.***>

Answer 4 · 2024-03-19T17:04:47.000Z

We should look into whether this is an issue for the RPostgres version of dbAppendTable(); in particular, see the copy argument in https://rpostgres.r-dbi.org/reference/postgres-tables.html.

If I read this correctly, this should do a COPY instead of multiple INSERTS by default, which seems to be the Postgres recommendation for this situation (https://www.postgresql.org/docs/current/populate.html).