copy_inline() and copy_to() do not work in AWS Redshift. Generated SQL appears to be syntactically incompatible with Redshift PostgreSQL:

Error: Failed to prepare query: ERROR: syntax error at or near "," LINE 30: ...f64faee3fc', 'nc', '27607', '3', '035', 'p', 's', 'h', '3'), ^

Jul 27 '22 19:07 scvail195

Would be great if you could add a reprex and provide an example for the correct syntax. Then it is much easier to fix this.

Jul 29 '22 12:07 mgirlich

I have no idea what a reprex is. This is how we create temp tables in Redshift:

-- Create a small campaign mapping table and insert values DROP TABLE IF EXISTS campaign_map ; CREATE temporary TABLE campaign_map ( campaign_id bigint, col varchar(4), lob varchar(12), short_name varchar(32) ) DISTSTYLE ALL ;

INSERT INTO campaign_map (campaign_id, col, lob, short_name) VALUES (26846672, 'C1', 'Fiber', 'NWF_3_AWA_ABS_Fiber'), (26882963, 'C2', 'IPBB', 'NWF_4_AWA_ABS_IPBB'), (26897818, 'C3', 'CRU', 'MBY_8_AWA_ABS_Mobility'), (26893051, 'C4', 'STS-ENT', 'EMN_001_AWA_ABM_Enterprise'), (26871905, 'C5', 'STE-MED', 'EMN_002_AWA_ABM_Medium'), (26908789, 'C6', 'FN', 'FRN_007_AWA_FRN_FirstNet') ;

I hope this is helpful.

Steve

From: Maximilian Girlich @.> Date: Friday, July 29, 2022 at 8:07 AM To: tidyverse/dbplyr @.> Cc: Steve Vail (Annalect) @.>, Author @.> Subject: Re: [tidyverse/dbplyr] copy_inline() and copy_to() not working in AWS Redshift (Issue #949) CAUTION: This email originated from the Internet. The Original Sender is @.***

Would be great if you could add a reprexhttps://dbplyr.tidyverse.org/articles/reprex.html and provide an example for the correct syntax. Then it is much easier to fix this.

— Reply to this email directly, view it on GitHubhttps://github.com/tidyverse/dbplyr/issues/949#issuecomment-1199201108, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AVSDZOJO2ZNEI2SECTAGEGDVWPCQLANCNFSM5422AC7Q. You are receiving this because you authored the thread.Message ID: @.***>

This email is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential or otherwise protected from disclosure. Dissemination, distribution, or copying of this email or the information herein by anyone other than the intended recipient, or an employee or agent responsible for delivering the message to the intended recipient, is prohibited. If you have received this email in error, please notify the sender immediately.

Aug 01 '22 19:08 ghost

See the attached.

Steve

From: Maximilian Girlich @.> Date: Friday, July 29, 2022 at 8:07 AM To: tidyverse/dbplyr @.> Cc: Steve Vail (Annalect) @.>, Author @.> Subject: Re: [tidyverse/dbplyr] copy_inline() and copy_to() not working in AWS Redshift (Issue #949) CAUTION: This email originated from the Internet. The Original Sender is @.***

Would be great if you could add a reprexhttps://dbplyr.tidyverse.org/articles/reprex.html and provide an example for the correct syntax. Then it is much easier to fix this.

— Reply to this email directly, view it on GitHubhttps://github.com/tidyverse/dbplyr/issues/949#issuecomment-1199201108, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AVSDZOJO2ZNEI2SECTAGEGDVWPCQLANCNFSM5422AC7Q. You are receiving this because you authored the thread.Message ID: @.***>

This email is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential or otherwise protected from disclosure. Dissemination, distribution, or copying of this email or the information herein by anyone other than the intended recipient, or an employee or agent responsible for delivering the message to the intended recipient, is prohibited. If you have received this email in error, please notify the sender immediately.

R is free software and comes with ABSOLUTELY NO WARRANTY. You are welcome to redistribute it under certain conditions. Type 'license()' or 'licence()' for distribution details.

Natural language support but running in an English locale

R is a collaborative project with many contributors. Type 'contributors()' for more information and 'citation()' on how to cite R or R packages in publications.

Type 'demo()' for some demos, 'help()' for on-line help, or 'help.start()' for an HTML browser interface to help. Type 'q()' to quit R.

*** Working directory is: /Users/steve.vail

*** .RData successfully loaded ***

[1] "working directory: /Users/steve.vail" ── Attaching packages ─────────────────────────────────────── tidyverse 1.3.2 ── ✔ ggplot2 3.3.6 ✔ purrr 0.3.4 ✔ tibble 3.1.8 ✔ dplyr 1.0.9 ✔ tidyr 1.2.0 ✔ stringr 1.4.0 ✔ readr 2.1.2 ✔ forcats 0.5.1 ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ── ✖ dplyr::filter() masks stats::filter() ✖ dplyr::lag() masks stats::lag() [Workspace loaded from ~/.RData]

library(needs) needs(DBI,RPostgres,dbplyr,dplyr,reprex) ssh_mux1_start <- function ( SleepSecs=5 ) { ... # Start ssh proxy connection on mux1 ... mux1user <- Sys.getenv("MUX1_USER") ... # sshcmd <- 'ssh -v -nNT -F ~/.ssh/mux1_config -i ~/.ssh/mux1_id_rsa mux1.accuenbi.com &' ... sshcmd <- paste0('ssh -v -nNT -F ~/.ssh/mux1_config -i ~/.ssh/mux1_id_rsa @.*** &') ... system ( sshcmd, intern=FALSE, ignore.stdout=FALSE, ignore.stderr=FALSE, wait=TRUE, input=NULL ) ... Sys.sleep(SleepSecs) # give ssh time to finish connecting ... } connect_dsdk <- function ( MySchema='' ) { ... # dsdk via mux1 proxy configured on port 5920 ... DefaultSchema <- 'ads_public,public' ... if ( MySchema == '' ) { Schema <- DefaultSchema } else { Schema <- paste(MySchema,DefaultSchema,,sep=',') } ... require(DBI) ... require(dbplyr) ... require(RPostgres) ... # drv <- dbDriver("PostgreSQL") ... print(MySchema) ... dbConnect ( RPostgres::Postgres(), ... host = "localhost", dbname = "dsdk", port=5920, ... user = Sys.getenv("DSDK_USER"), ... password = Sys.getenv("DSDK_PASS"), ... options = paste0('-c search_path=',Schema) ) ... }

DSDK connect example

ssh_mux1_start() # wait for prompt before continuing OpenSSH_8.6p1, LibreSSL 3.3.6 debug1: Reading configuration data /Users/steve.vail/.ssh/mux1_config debug1: Authenticator provider $SSH_SK_PROVIDER did not resolve; disabling debug1: Connecting to mux1.accuenbi.com port 22. debug1: Connection established. debug1: identity file /Users/steve.vail/.ssh/mux1_id_rsa type -1 debug1: identity file /Users/steve.vail/.ssh/mux1_id_rsa-cert type -1 debug1: Local version string SSH-2.0-OpenSSH_8.6 debug1: Remote protocol version 2.0, remote software version OpenSSH_7.3p1 Ubuntu-1 debug1: compat_banner: match: OpenSSH_7.3p1 Ubuntu-1 pat OpenSSH_7.0*,OpenSSH_7.1*,OpenSSH_7.2*,OpenSSH_7.3*,OpenSSH_7.4*,OpenSSH_7.5*,OpenSSH_7.6*,OpenSSH_7.7* compat 0x04000002 debug1: Authenticating to mux1.accuenbi.com:22 as 'stevev' debug1: load_hostkeys: fopen /Users/steve.vail/.ssh/known_hosts2: No such file or directory debug1: load_hostkeys: fopen /etc/ssh/ssh_known_hosts: No such file or directory debug1: load_hostkeys: fopen /etc/ssh/ssh_known_hosts2: No such file or directory debug1: SSH2_MSG_KEXINIT sent debug1: SSH2_MSG_KEXINIT received debug1: kex: algorithm: @.*** debug1: kex: host key algorithm: ssh-ed25519 debug1: kex: server->client cipher: @.*** MAC: compression: none debug1: kex: client->server cipher: @.*** MAC: compression: none debug1: expecting SSH2_MSG_KEX_ECDH_REPLY debug1: SSH2_MSG_KEX_ECDH_REPLY received debug1: Server host key: ssh-ed25519 SHA256:SK/AsiceDbuobHtD4bpex9KCA29HJvYZuLg1Jct9BIc debug1: load_hostkeys: fopen /Users/steve.vail/.ssh/known_hosts2: No such file or directory debug1: load_hostkeys: fopen /etc/ssh/ssh_known_hosts: No such file or directory debug1: load_hostkeys: fopen /etc/ssh/ssh_known_hosts2: No such file or directory debug1: Host 'mux1.accuenbi.com' is known and matches the ED25519 host key. debug1: Found key in /Users/steve.vail/.ssh/known_hosts:13 debug1: rekey out after 134217728 blocks debug1: SSH2_MSG_NEWKEYS sent debug1: expecting SSH2_MSG_NEWKEYS debug1: SSH2_MSG_NEWKEYS received debug1: rekey in after 134217728 blocks debug1: Will attempt key: /Users/steve.vail/.ssh/mux1_id_rsa explicit debug1: SSH2_MSG_EXT_INFO received debug1: kex_input_ext_info: server-sig-algs=<rsa-sha2-256,rsa-sha2-512> debug1: SSH2_MSG_SERVICE_ACCEPT received debug1: Authentications that can continue: publickey debug1: Next authentication method: publickey debug1: Trying private key: /Users/steve.vail/.ssh/mux1_id_rsa debug1: Authentication succeeded (publickey). Authenticated to mux1.accuenbi.com ([52.203.82.213]:22). debug1: Local connections to LOCALHOST:5920 forwarded to remote address dsdk-v0p1-annalect.clf6bikxcquu.us-east-1.redshift.amazonaws.com:5439 debug1: Local forwarding listening on ::1 port 5920. bind [::1]:5920: Address already in use debug1: Local forwarding listening on 127.0.0.1 port 5920. bind [127.0.0.1]:5920: Address already in use channel_setup_fwd_listener_tcpip: cannot listen to port: 5920 debug1: Local connections to LOCALHOST:5921 forwarded to remote address dt-021.csktdopmxkxl.us-east-1.redshift.amazonaws.com:5439 debug1: Local forwarding listening on ::1 port 5921. bind [::1]:5921: Address already in use debug1: Local forwarding listening on 127.0.0.1 port 5921. bind [127.0.0.1]:5921: Address already in use channel_setup_fwd_listener_tcpip: cannot listen to port: 5921 debug1: Local connections to LOCALHOST:5922 forwarded to remote address dt-022.csktdopmxkxl.us-east-1.redshift.amazonaws.com:5439 debug1: Local forwarding listening on ::1 port 5922. bind [::1]:5922: Address already in use debug1: Local forwarding listening on 127.0.0.1 port 5922. bind [127.0.0.1]:5922: Address already in use channel_setup_fwd_listener_tcpip: cannot listen to port: 5922 debug1: Local connections to LOCALHOST:5923 forwarded to remote address dt-023.csktdopmxkxl.us-east-1.redshift.amazonaws.com:5439 debug1: Local forwarding listening on ::1 port 5923. bind [::1]:5923: Address already in use debug1: Local forwarding listening on 127.0.0.1 port 5923. bind [127.0.0.1]:5923: Address already in use channel_setup_fwd_listener_tcpip: cannot listen to port: 5923 debug1: Local connections to LOCALHOST:5924 forwarded to remote address dt-024.csktdopmxkxl.us-east-1.redshift.amazonaws.com:5439 debug1: Local forwarding listening on ::1 port 5924. bind [::1]:5924: Address already in use debug1: Local forwarding listening on 127.0.0.1 port 5924. bind [127.0.0.1]:5924: Address already in use channel_setup_fwd_listener_tcpip: cannot listen to port: 5924 debug1: Local connections to LOCALHOST:5925 forwarded to remote address dt-025.csktdopmxkxl.us-east-1.redshift.amazonaws.com:5439 debug1: Local forwarding listening on ::1 port 5925. bind [::1]:5925: Address already in use debug1: Local forwarding listening on 127.0.0.1 port 5925. bind [127.0.0.1]:5925: Address already in use channel_setup_fwd_listener_tcpip: cannot listen to port: 5925 debug1: Local connections to LOCALHOST:5926 forwarded to remote address dt-026.csktdopmxkxl.us-east-1.redshift.amazonaws.com:5439 debug1: Local forwarding listening on ::1 port 5926. bind [::1]:5926: Address already in use debug1: Local forwarding listening on 127.0.0.1 port 5926. bind [127.0.0.1]:5926: Address already in use channel_setup_fwd_listener_tcpip: cannot listen to port: 5926 debug1: Local connections to LOCALHOST:5930 forwarded to remote address adestg.clf6bikxcquu.us-east-1.redshift.amazonaws.com:5439 debug1: Local forwarding listening on ::1 port 5930. bind [::1]:5930: Address already in use debug1: Local forwarding listening on 127.0.0.1 port 5930. bind [127.0.0.1]:5930: Address already in use channel_setup_fwd_listener_tcpip: cannot listen to port: 5930 Could not request local forwarding. debug1: Requesting @.*** debug1: Entering interactive session. debug1: pledge: filesystem full debug1: client_input_global_request: rtype @.*** want_reply 0 debug1: client_input_hostkeys: searching /Users/steve.vail/.ssh/known_hosts for mux1.accuenbi.com / (none) debug1: client_input_hostkeys: searching /Users/steve.vail/.ssh/known_hosts2 for mux1.accuenbi.com / (none) debug1: client_input_hostkeys: hostkeys file /Users/steve.vail/.ssh/known_hosts2 does not exist debug1: client_input_hostkeys: no new or deprecated keys from server con <- connect_dsdk() [1] "" con %>% tbl(in_schema("public","affinity")) -> brand_tbl brand_tbl %>% head(n=5)

Source: SQL [5 x 13]

Database: postgres @.***:5920/dsdk]

idl_id txid brand…¹ categ…² subca…³ trans_date trans…⁴ trans…⁵ channel membc…⁶ afs_i…⁷ cardh…⁸ cardh…⁹
1 XY1324gtvNJSUH2X… tx-4… & OTHE… Apparel Women'… 2021-06-26 00:00:… 4.93 B&M c2f48b… 53cf2c… Florida Jackso… 2 XY1324ohP9Xcw4i0… tx-9… & OTHE… Apparel Women'… 2022-05-10 00:00:… 18.9 B&M f0ebd5… bcdc62… South … Columb… 3 XY1324lZRQgz6j-7… tx-0… & OTHE… Apparel Women'… 2020-03-20 NA 140. B&M b2681e… bd85dc… Florida Jackso… 4 XY1324ZxWfRROhfG… tx-3… & OTHE… Apparel Women'… 2022-05-16 15:29:… 14.4 B&M f0afd7… defbd2… Florida Jackso… 5 XY1324X1YR3IAhOv… tx-7… & OTHE… Apparel Women'… 2022-01-30 17:24:… 17.3 B&M fe33e1… c096b0… Florida Tallah…

… with abbreviated variable names ¹brand_name, ²category, ³subcategory, ⁴trans_time, ⁵trans_amount, ⁶membccid,

⁷afs_individual_id, ⁸cardholder_state, ⁹cardholder_dma_name

A little data frame

matrix(c( ... "B&M","Brick+Mortar", ... "ONLINE","eCommerce"), nrow=2, ncol=2 ) %>% t %>% as.data.frame %>% ... rename ( channel=V1, chan_name=V2 ) -> chan_df copy_inline ( con, chan_df ) %>% show_query() <SQL> SELECT CAST("channel" AS TEXT) AS "channel", CAST("chan_name" AS TEXT) AS "chan_name" FROM ( VALUES ('B&M', 'Brick+Mortar'), ('ONLINE', 'eCommerce')) AS drvd("channel", "chan_name") copy_inline ( con, chan_df ) %>% head() Error: Failed to prepare query: ERROR: syntax error at or near "," LINE 6: FROM ( VALUES ('B&M', 'Brick+Mortar'), ('ONLINE', 'eCom... ^ ssh_mux1_kill() # Dont forget to kill ssh processes after you are done using database

Aug 01 '22 21:08 ghost

I have no idea what a reprex is.

I actually added a link that explains what a reprex is 😉

I'm afraid that without an example for the correct SQL I won't be able to solve this (I don't have a redshift database available to test this). Could you provide the correct SQL?

Aug 02 '22 07:08 mgirlich

I sent you an example of the SQL generated by R, an also an example for a similar table using correct PostgreSQL syntax.

Steve

From: Maximilian Girlich @.> Date: Tuesday, August 2, 2022 at 4:00 AM To: tidyverse/dbplyr @.> Cc: Steve Vail (Annalect) @.>, Author @.> Subject: Re: [tidyverse/dbplyr] copy_inline() and copy_to() not working in AWS Redshift (Issue #949) CAUTION: This email originated from the Internet. The Original Sender is @.***

I have no idea what a reprex is.

I actually added a link that explains what a reprexhttps://dbplyr.tidyverse.org/articles/reprex.html is 😉

I'm afraid that without an example for the correct SQL I won't be able to solve this (I don't have a redshift database available to test this). Could you provide the correct SQL?

— Reply to this email directly, view it on GitHubhttps://github.com/tidyverse/dbplyr/issues/949#issuecomment-1202149764, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AVSDZOI7CWCAJ3E4FYYPY2TVXDIPZANCNFSM5422AC7Q. You are receiving this because you authored the thread.Message ID: @.***>

This email is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential or otherwise protected from disclosure. Dissemination, distribution, or copying of this email or the information herein by anyone other than the intended recipient, or an employee or agent responsible for delivering the message to the intended recipient, is prohibited. If you have received this email in error, please notify the sender immediately.

Aug 02 '22 13:08 ghost

Resending a valid PostgreSQL table create:

DROP TABLE IF EXISTS campaign_map ; CREATE temporary TABLE campaign_map ( campaign_id bigint, col varchar(4), lob varchar(12), short_name varchar(32) ) DISTSTYLE ALL ;

INSERT INTO campaign_map (campaign_id, col, lob, short_name) VALUES (26846672, 'C1', 'Fiber', 'NWF_3_AWA_ABS_Fiber'), (26882963, 'C2', 'IPBB', 'NWF_4_AWA_ABS_IPBB'), (26897818, 'C3', 'CRU', 'MBY_8_AWA_ABS_Mobility'), (26893051, 'C4', 'STS-ENT', 'EMN_001_AWA_ABM_Enterprise'), (26871905, 'C5', 'STE-MED', 'EMN_002_AWA_ABM_Medium'), (26908789, 'C6', 'FN', 'FRN_007_AWA_FRN_FirstNet') ;

Steve

From: Maximilian Girlich @.> Date: Tuesday, August 2, 2022 at 4:00 AM To: tidyverse/dbplyr @.> Cc: Steve Vail (Annalect) @.>, Author @.> Subject: Re: [tidyverse/dbplyr] copy_inline() and copy_to() not working in AWS Redshift (Issue #949) CAUTION: This email originated from the Internet. The Original Sender is @.***

I have no idea what a reprex is.

I actually added a link that explains what a reprexhttps://dbplyr.tidyverse.org/articles/reprex.html is 😉

I'm afraid that without an example for the correct SQL I won't be able to solve this (I don't have a redshift database available to test this). Could you provide the correct SQL?

— Reply to this email directly, view it on GitHubhttps://github.com/tidyverse/dbplyr/issues/949#issuecomment-1202149764, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AVSDZOI7CWCAJ3E4FYYPY2TVXDIPZANCNFSM5422AC7Q. You are receiving this because you authored the thread.Message ID: @.***>

This email is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential or otherwise protected from disclosure. Dissemination, distribution, or copying of this email or the information herein by anyone other than the intended recipient, or an employee or agent responsible for delivering the message to the intended recipient, is prohibited. If you have received this email in error, please notify the sender immediately.

Aug 02 '22 13:08 ghost

You will probably need a more complete description of Redshift CREATE syntax and Redshift data types. Here are some links: https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_examples.html https://docs.aws.amazon.com/redshift/latest/dg/c_Supported_data_types.html

Finally you need to know how Redshift distributes rows over compute nodes (slices) in the cluster. For small temporary tables the best choice is DISTSTYLE AUTO or DISTSTYLE ALL. Other distribution options are explained here: https://docs.aws.amazon.com/redshift/latest/dg/c_choosing_dist_sort.html

I hope this helps.

Steve

From: Maximilian Girlich @.> Date: Tuesday, August 2, 2022 at 4:00 AM To: tidyverse/dbplyr @.> Cc: Steve Vail (Annalect) @.>, Author @.> Subject: Re: [tidyverse/dbplyr] copy_inline() and copy_to() not working in AWS Redshift (Issue #949) CAUTION: This email originated from the Internet. The Original Sender is @.***

I have no idea what a reprex is.

I actually added a link that explains what a reprexhttps://dbplyr.tidyverse.org/articles/reprex.html is 😉

I'm afraid that without an example for the correct SQL I won't be able to solve this (I don't have a redshift database available to test this). Could you provide the correct SQL?

— Reply to this email directly, view it on GitHubhttps://github.com/tidyverse/dbplyr/issues/949#issuecomment-1202149764, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AVSDZOI7CWCAJ3E4FYYPY2TVXDIPZANCNFSM5422AC7Q. You are receiving this because you authored the thread.Message ID: @.***>

This email is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential or otherwise protected from disclosure. Dissemination, distribution, or copying of this email or the information herein by anyone other than the intended recipient, or an employee or agent responsible for delivering the message to the intended recipient, is prohibited. If you have received this email in error, please notify the sender immediately.

Aug 02 '22 13:08 ghost

Ah, Redshift does not support the VALUES clause. According to this stackoverflow thread the alternative is to use UNION ALL. So far this is the only database I know that does not support the VALUES clause. @scvail195 Maybe you want to have a go at a PR to support copy_inline() for Redshift.

Aug 03 '22 04:08 mgirlich

What is a PR? How does it work?

Steve

From: Maximilian Girlich @.> Date: Wednesday, August 3, 2022 at 12:47 AM To: tidyverse/dbplyr @.> Cc: Steve Vail (Annalect) @.>, Mention @.> Subject: Re: [tidyverse/dbplyr] copy_inline() and copy_to() not working in AWS Redshift (Issue #949) CAUTION: This email originated from the Internet. The Original Sender is @.***

Ah, Redshift does not support the VALUES clause. According to this stackoverflow threadhttps://stackoverflow.com/questions/58030295/how-to-select-list-of-values-in-redshift the alternative is to use UNION ALL. So far this is the only database I know that does not support the VALUES clause. @scvail195https://github.com/scvail195 Maybe you want to have a go at a PR to support copy_inline() for Redshift.

— Reply to this email directly, view it on GitHubhttps://github.com/tidyverse/dbplyr/issues/949#issuecomment-1203480003, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AVSDZOJUSIZDWDH6XETABODVXH2WLANCNFSM5422AC7Q. You are receiving this because you were mentioned.Message ID: @.***>

This email is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential or otherwise protected from disclosure. Dissemination, distribution, or copying of this email or the information herein by anyone other than the intended recipient, or an employee or agent responsible for delivering the message to the intended recipient, is prohibited. If you have received this email in error, please notify the sender immediately.

Aug 03 '22 13:08 ghost

Hi @mgirlich, I can take a shot at this one if you'd like.

It looks like this can be handled in sql_values_clause catching redshift connections. The below works against my company's redshift db for copy_inline and copy_to. It seems to break the sql formatting in the console though.

 sql_values_clause <- function(con, df, row = FALSE) {
   escaped_values <- purrr::map(df, escape, con = con, collapse = NULL, parens = FALSE)
   rows <- rlang::exec(paste, !!!escaped_values, sep = ", ")
-  rows_sql <- sql(paste0(if (row) "ROW", "(", rows, ")"))
 
-  list(sql_clause("VALUES", rows_sql))
+  if (inherits(con, "Redshift")) {
+    rows_sql <- sql(paste0("SELECT ", rows, collapse = " UNION ALL "))
+    list(sql_clause("", rows_sql))
+  } else {
+    rows_sql <- sql(paste0(if (row) "ROW", "(", rows, ")"))
+    list(sql_clause("VALUES", rows_sql))
+  }
 }

copy_inline(con, head(mtcars[c("mpg", "cyl", "disp")])) %>% show_query()

before:

<SQL>
SELECT
  CAST("mpg" AS FLOAT) AS "mpg",
  CAST("cyl" AS FLOAT) AS "cyl",
  CAST("disp" AS FLOAT) AS "disp"
FROM (
  (
    SELECT NULL AS "mpg", NULL AS "cyl", NULL AS "disp"
    WHERE (0 = 1)
  )
  UNION ALL
  (
  VALUES
    (21.0, 6.0, 160.0),
    (21.0, 6.0, 160.0),
    (22.8, 4.0, 108.0),
    (21.4, 6.0, 258.0),
    (18.7, 8.0, 360.0),
    (18.1, 6.0, 225.0)
  )
) "values_table"

after:

<SQL>
SELECT
  CAST("mpg" AS FLOAT) AS "mpg",
  CAST("cyl" AS FLOAT) AS "cyl",
  CAST("disp" AS FLOAT) AS "disp"
FROM (
  (
    SELECT NULL AS "mpg", NULL AS "cyl", NULL AS "disp"
    WHERE (0 = 1)
  )
  UNION ALL
  ( SELECT 21.0, 6.0, 160.0 UNION ALL SELECT 21.0, 6.0, 160.0 UNION ALL SELECT 22.8, 4.0, 108.0 UNION ALL SELECT 21.4, 6.0, 258.0 UNION ALL SELECT 18.7, 8.0, 360.0 UNION ALL SELECT 18.1, 6.0, 225.0)
) "values_table"

Aug 03 '22 17:08 ejneer

@ejneer I would be happy about a PR 👍

Aug 04 '22 04:08 mgirlich

See GH-961

Aug 04 '22 17:08 ejneer

Your “after” code runs successfully in our Redshift DB using my IDE (DataGrip) running standalone, and without error.

Steve

From: ejneer @.> Date: Thursday, August 4, 2022 at 1:06 PM To: tidyverse/dbplyr @.> Cc: Steve Vail (Annalect) @.>, Mention @.> Subject: Re: [tidyverse/dbplyr] copy_inline() and copy_to() not working in AWS Redshift (Issue #949) CAUTION: This email originated from the Internet. The Original Sender is @.***

See GH-961https://github.com/tidyverse/dbplyr/pull/961

— Reply to this email directly, view it on GitHubhttps://github.com/tidyverse/dbplyr/issues/949#issuecomment-1205533406, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AVSDZOIL3EQH6W2F2TYFTILVXPZ73ANCNFSM5422AC7Q. You are receiving this because you were mentioned.Message ID: @.***>

This email is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential or otherwise protected from disclosure. Dissemination, distribution, or copying of this email or the information herein by anyone other than the intended recipient, or an employee or agent responsible for delivering the message to the intended recipient, is prohibited. If you have received this email in error, please notify the sender immediately.

Aug 04 '22 17:08 ghost

dbplyr dbplyr copied to clipboard

copy_inline() and copy_to() not working in AWS Redshift

DSDK connect example

Source: SQL [5 x 13]

Database: postgres @.***:5920/dsdk]

… with abbreviated variable names ¹​brand_name, ²​category, ³​subcategory, ⁴​trans_time, ⁵​trans_amount, ⁶​membccid,

⁷​afs_individual_id, ⁸​cardholder_state, ⁹​cardholder_dma_name

A little data frame

dbplyr
dbplyr copied to clipboard

… with abbreviated variable names ¹brand_name, ²category, ³subcategory, ⁴trans_time, ⁵trans_amount, ⁶membccid,

⁷afs_individual_id, ⁸cardholder_state, ⁹cardholder_dma_name