Comments (6)
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.
Hi @nicholsn, interesting. Have you tried dplyr::show_query()
to find out the query it is trying to send to athena?
from noctua.
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.
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.
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.
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)
- Swap to Github Actions instead of travis
- Release noctua 2.3.0
- The 'statement' argument to dbGetQuery and dbSendQuery methods isn't exposed in older versions of R HOT 7
- Caching issue HOT 2
- Method to set unload at a package level HOT 1
- Release noctua 2.4.0 on to cran
- Prevent Noctua from printing Data Scanned -information HOT 7
- Release noctua 2.5.0 HOT 3
- Release noctua 2.6.0 HOT 1
- cran-2.6.1 release
- Can I set various parameters in `.aws/config` file and have `DBI::dbConnect()` read those directly from that file? HOT 5
- Add catalog support HOT 26
- Column Bucketing
- Allow for Partition columns to change data types
- Can't write/append an empty data frame
- Connecting using long-term-creds returns Error 400 HOT 5
- dbFetch(..., n=small number) is quite slow when run on a large result set HOT 4
- `dbExistsTable()` doesn't work anymore HOT 3
- fix: for dbplyr 2.3.3.9000 +
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
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.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from noctua.