Code Monkey home page Code Monkey logo

Comments (6)

DyfanJones avatar DyfanJones commented on June 11, 2024 1

If it is ok, i will have a deeper look tomorrow :) i need to see if i can replicate this issue :) i am guessing it is erroring when it is trying to get the column names for the tbl class.

from noctua.

DyfanJones avatar DyfanJones commented on June 11, 2024

Hi @nicholsn, interesting. Have you tried dplyr::show_query() to find out the query it is trying to send to athena?

from noctua.

nicholsn avatar nicholsn commented on June 11, 2024

Hey @DyfanJones, I did try to pipe it into show_query but the error is thrown even with just tbl(con, query) - no collect() needed.

from noctua.

DyfanJones avatar DyfanJones commented on June 11, 2024

OOO just a thought, I think i know what is going. For tbl, dplyr does an initial query to what ever database it is connected to. It does this by sub-querying the table so it will look something like:

-- example of sub-query
select var1, var2, var3 from blah.table

dplyr's wrapper

select * from (
select var1, var2, var3 from blah.table
) as sub_query
where 1 = 0

However in your query you have ; at the end of your query, so dplyr will do the following:

select * from (
select var1, var2, var3 from blah.table;
) as sub_query
where 1 = 0

As there a ; inside sub-query it will fail. So can you try without ;. I hope this helps to explain what is going on in the backend. Please let me know if this fixes your issue :)

from noctua.

DyfanJones avatar DyfanJones commented on June 11, 2024

I can confirm this is the case. You need to remove ; from your sub-query. Here is an example demonstrating this.

library(DBI)
library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union

con <- dbConnect(noctua::athena())
tbl(con, sql("select * from sampledb.elb_logs;"))
#> Error: InvalidRequestException (HTTP 400). Only one sql statement is allowed. Got: SELECT *
#> FROM (select * from sampledb.elb_logs;) "q01"
#> WHERE (0 = 1)
tbl(con, sql("select * from sampledb.elb_logs"))
#> INFO: (Data scanned: 0 Bytes)
#> INFO: (Data scanned: 2.52 MB)
#> # Source:   SQL [?? x 19]
#> # Database: Athena 0.1.12 [default@eu-west-1/default]
#>    request_tim…¹ elb_n…² reque…³ reque…⁴ backe…⁵ backe…⁶ reque…⁷ backe…⁸ clien…⁹
#>    <chr>         <chr>   <chr>     <int> <chr>     <int>   <dbl>   <dbl>   <dbl>
#>  1 2015-01-01T0… elb_de… 248.46…   31279 172.40…      80 3.26e-4 7.85e-4 6.91e-4
#>  2 2015-01-01T0… elb_de… 248.11…   22272 172.46…    8888 6.53e-4 9.53e-4 7.61e-4
#>  3 2015-01-01T0… elb_de… 253.19…   30529 172.30…     443 1.26e-4 8.1 e-4 3.52e-4
#>  4 2015-01-01T0… elb_de… 247.24…   13001 172.41…    8888 1.43e-3 1.39e-3 5.56e-4
#>  5 2015-01-01T0… elb_de… 248.21…    6024 172.52…    8888 9.31e-4 2.62e-4 6.53e-4
#>  6 2015-01-01T0… elb_de… 245.20…   16173 172.38…     443 1.1 e-3 1.99e-3 1.38e-3
#>  7 2015-01-01T0… elb_de… 253.13…   29168 172.33…      80 5.37e-4 1.67e-3 6.13e-4
#>  8 2015-01-01T0… elb_de… 250.16…   26741 172.49…    8888 1.52e-3 1.08e-3 9.06e-4
#>  9 2015-01-01T0… elb_de… 251.12…   16934 172.32…      80 2.12e-4 9.59e-4 9.69e-4
#> 10 2015-01-01T0… elb_de… 241.13…   18980 172.55…    8888 8.86e-4 9.97e-4 1.32e-3
#> # … with more rows, 10 more variables: elb_response_code <chr>,
#> #   backend_response_code <chr>, received_bytes <int64>, sent_bytes <int64>,
#> #   request_verb <chr>, url <chr>, protocol <chr>, user_agent <chr>,
#> #   ssl_cipher <chr>, ssl_protocol <chr>, and abbreviated variable names
#> #   ¹​request_timestamp, ²​elb_name, ³​request_ip, ⁴​request_port, ⁵​backend_ip,
#> #   ⁶​backend_port, ⁷​request_processing_time, ⁸​backend_processing_time,
#> #   ⁹​client_response_time

Created on 2022-12-15 with reprex v2.0.2

I hope this helps

from noctua.

nicholsn avatar nicholsn commented on June 11, 2024

That was it - nice catch! The error message from dplyr is accurate if you can spot that they're wrapping your query and making it a sub-query. Thank you for your help, @DyfanJones!

from noctua.

Related Issues (20)

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.