Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

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

Closed
ghost opened this issue Jul 27, 2022 · 13 comments · Fixed by #982
Closed

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

ghost opened this issue Jul 27, 2022 · 13 comments · Fixed by #982
Labels
help wanted ❤️ we'd love your help!
Milestone
2.3.0

Comments

@ghost
Copy link

ghost commented Jul 27, 2022

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'),
^
@mgirlich
Copy link
Collaborator

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.

@mgirlich mgirlich added this to the 2.3.0 milestone Jul 29, 2022
@mgirlich mgirlich added the help wanted ❤️ we'd love your help! label Jul 31, 2022
@ghost
Copy link
Author

ghost commented Aug 1, 2022 via email

@ghost
Copy link
Author

ghost commented Aug 1, 2022 via email

@mgirlich
Copy link
Collaborator

mgirlich commented Aug 2, 2022

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?

@ghost
Copy link
Author

ghost commented Aug 2, 2022 via email

@ghost
Copy link
Author

ghost commented Aug 2, 2022 via email

@ghost
Copy link
Author

ghost commented Aug 2, 2022 via email

@mgirlich
Copy link
Collaborator

mgirlich commented Aug 3, 2022

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.

@ghost
Copy link
Author

ghost commented Aug 3, 2022 via email

@ejneer
Copy link
Contributor

ejneer commented Aug 3, 2022 •
edited
Loading

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"

@mgirlich
Copy link
Collaborator

mgirlich commented Aug 4, 2022

@ejneer I would be happy about a PR 👍

@mgirlich mgirlich mentioned this issue Aug 4, 2022
Closed
@ejneer ejneer mentioned this issue Aug 4, 2022
Closed
@ejneer
Copy link
Contributor

ejneer commented Aug 4, 2022

See GH-961

@ghost
Copy link
Author

ghost commented Aug 4, 2022 via email

@mgirlich mgirlich mentioned this issue Aug 22, 2022
Merged
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
help wanted ❤️ we'd love your help!
Projects
None yet
Milestone
2.3.0
2 participants
@ejneer @mgirlich