### Reproducible Example Session Info

Source: https://github.com/dyfanjones/noctua/blob/master/docs/issue_template.html

Use this command to get session information for reproducible examples.

```r
devtools::session_info()
#> output
```

--------------------------------

### Install noctua Development Version from GitHub

Source: https://github.com/dyfanjones/noctua/blob/master/README.md

Install the latest development version of the noctua package from GitHub using remotes.

```r
remotes::install_github("dyfanjones/noctua")
```

--------------------------------

### SQL String Concatenation Example

Source: https://github.com/dyfanjones/noctua/blob/master/NEWS.md

Demonstrates basic SQL string concatenation using the || operator.

```sql
('hi'||'-'||'bye')
```

--------------------------------

### Install Noctua Package

Source: https://github.com/dyfanjones/noctua/blob/master/docs/articles/getting_started.html

Install the Noctua package from CRAN or the development version from GitHub.

```r
# cran version
install.packages("noctua")

# Dev version
remotes::install_github("dyfanjones/noctua")
```

--------------------------------

### Install noctua from CRAN

Source: https://github.com/dyfanjones/noctua/blob/master/README.md

Install the stable version of the noctua package from CRAN.

```r
install.packages("noctua")
```

--------------------------------

### List Athena Tables

Source: https://github.com/dyfanjones/noctua/blob/master/docs/reference/dbListTables.html

This example demonstrates how to connect to Athena using a profile name and list all available tables. It requires an AWS account and the noctua package. Ensure you have configured your AWS credentials.

```R
if (FALSE) {
# Note:
# - Require AWS Account to run below example.
# - Different connection methods can be used please see `noctua::dbConnect` documnentation

library(DBI)

# Demo connection to Athena using profile name
con <- dbConnect(noctua::athena())

# Return list of tables in Athena
dbListTables(con)

# Disconnect conenction
dbDisconnect(con)
}
```

--------------------------------

### Get Athena Table Partitions

Source: https://github.com/dyfanjones/noctua/blob/master/docs/reference/dbGetPartition.html

Demonstrates how to connect to Athena, write a table with partitions, retrieve those partitions using dbGetPartition, and disconnect. Requires an AWS account and a valid S3 location.

```R
if (FALSE) {
# Note:
# - Require AWS Account to run below example.
# - Different connection methods can be used please see `noctua::dbConnect` documnentation

library(DBI)

# Demo connection to Athena using profile name
con <- dbConnect(noctua::athena())

# write iris table to Athena
dbWriteTable(con, "iris",
  iris,
  partition = c("timestamp" = format(Sys.Date(), "%Y%m%d")),
  s3.location = "s3://path/to/store/athena/table/"
)

# return table partitions
noctua::dbGetPartition(con, "iris")

# disconnect from Athena
dbDisconnect(con)
}
```

--------------------------------

### Get Column Information from Athena Query

Source: https://github.com/dyfanjones/noctua/blob/master/docs/reference/dbColumnInfo.html

This example demonstrates how to connect to AWS Athena, send a query, retrieve column information using dbColumnInfo, and then clear the result. It requires an AWS account and the noctua package. Different connection methods are available via RAthena::dbConnect.

```R
if (FALSE) {
# Note:
# - Require AWS Account to run below example.
# - Different connection methods can be used please see `RAthena::dbConnect` documnentation

library(DBI)

# Demo connection to Athena using profile name
con <- dbConnect(noctua::athena())

# Get Column information from query
res <- dbSendQuery(con, "select * from information_schema.tables")
dbColumnInfo(res)
dbClearResult(res)

# Disconnect from Athena
dbDisconnect(con)
}
```

--------------------------------

### Execute SQL Query and Retrieve Results with Athena

Source: https://github.com/dyfanjones/noctua/blob/master/docs/reference/dbGetQuery.html

This example demonstrates how to establish a connection to AWS Athena using a profile name, execute a 'show databases' query, and then disconnect. Ensure you have an AWS account and the necessary permissions to run this code.

```R
if (FALSE) {
# Note:
# - Require AWS Account to run below example.
# - Different connection methods can be used please see `noctua::dbConnect` documnentation

library(DBI)

# Demo connection to Athena using profile name
con <- dbConnect(noctua::athena())

# Sending Queries to Athena
dbGetQuery(con, "show databases")

# Disconnect conenction
dbDisconnect(con)
}
```

--------------------------------

### Writing Table to AWS S3 with Partition

Source: https://github.com/dyfanjones/noctua/blob/master/docs/articles/aws_s3_backend.html

Example of writing a table to AWS S3, specifying a custom S3 location and a partition. Noctua will construct the full S3 path including the partition.

```r
dbWriteTable(con, "myschema.table", table, 
                 s3.location = "s3://mybucket/myschema/table",
                 partition = c("year" = "2020"))
```

--------------------------------

### Get Formatted Partition Information

Source: https://github.com/dyfanjones/noctua/blob/master/NEWS.md

Retrieve and format partition information for a given table. Use .format = TRUE for tidied output.

```r
library(DBI)
library(noctua)
con <- dbConnect(athena())
dbGetPartition(con, "test_df2", .format = T)
```

```r
dbGetPartition(con, "test_df2")
```

--------------------------------

### Connect to Athena with Keyboard Interrupt Handling

Source: https://github.com/dyfanjones/noctua/blob/master/NEWS.md

Demonstrates how to connect to AWS Athena using the noctua package. The first example shows how to stop an Athena query when R is interrupted, while the second shows how to let the Athena query continue running.

```r
# Stop AWS Athena when R has been interrupted:

con <- dbConnect(noctua::athena())
```

```r
# Let AWS Athena keep running when R has been interrupted:

con <- dbConnect(noctua::athena(),
                 keyboard_interrupt = F)
```

--------------------------------

### Get Statement from DBIResult

Source: https://github.com/dyfanjones/noctua/blob/master/docs/reference/dbGetStatement.html

Retrieves the SQL statement from a DBIResult object. This example demonstrates connecting to AWS Athena, sending a query, and then retrieving the statement using dbGetStatement. Requires an AWS account and the noctua package.

```R
if (FALSE) {
# Note:
# - Require AWS Account to run below example.
# - Different connection methods can be used please see `noctua::dbConnect` documnentation

library(DBI)

# Demo connection to Athena using profile name
con <- dbConnect(noctua::athena())

rs <- dbSendQuery(con, "SHOW TABLES in default")
dbGetStatement(rs)
}
```

--------------------------------

### Write and Remove Parquet Table with Batch Deletion

Source: https://github.com/dyfanjones/noctua/blob/master/NEWS.md

Demonstrates writing a Parquet table and then removing it using `dbRemoveTable`. The example highlights the performance improvement of using `delete_objects` (new method) over `delete_object` (old method) for removing AWS S3 files.

```r
library(DBI)
library(data.table)

X <- 1010
value <- data.table(x = 1:X,
                    y = sample(letters, X, replace = T),
                    z = sample(c(TRUE, FALSE), X, replace = T))

con <- dbConnect(noctua::athena())

# create a removable table with 1010 parquet files in AWS S3.
dbWriteTable(con, "rm_tbl", value, file.type = "parquet", overwrite = T, max.batch = 1)

# old method: delete_object
system.time({dbRemoveTable(con, "rm_tbl", confirm = T)})
# user  system elapsed 
# 31.004   8.152 115.906 

# new method: delete_objects
system.time({dbRemoveTable(con, "rm_tbl", confirm = T)})
# user  system elapsed 
# 17.319   0.370  22.709 
```

--------------------------------

### Create partitioned table from SQL query

Source: https://github.com/dyfanjones/noctua/blob/master/docs/reference/dbConvertTable.html

Creates a new partitioned table in Athena using a SQL DML query. This example demonstrates converting a non-partitioned table into a partitioned 'orc' format table, adding a partition column based on the current date.

```r
dbConvertTable(
  con,
  obj = SQL("select
                                iris.*
,                                date_format(current_date, '%Y%m%d') as time_stamp
                              from iris"),
  name = "iris_orc_partitioned",
  file.type = "orc",
  partition = "time_stamp"
)
```

--------------------------------

### List Fields of Athena Table

Source: https://github.com/dyfanjones/noctua/blob/master/docs/reference/dbListFields.html

This example demonstrates how to connect to Athena, write a data frame to a table, list the fields of that table using dbListFields, and then disconnect. Requires an AWS account and a specified S3 location for data storage.

```R
if (FALSE) {
# Note:
# - Require AWS Account to run below example.
# - Different connection methods can be used please see `noctua::dbConnect` documnentation

library(DBI)

# Demo connection to Athena using profile name
con <- dbConnect(noctua::athena())

# Write data.frame to Athena table
dbWriteTable(con, "mtcars", mtcars,
  partition = c("TIMESTAMP" = format(Sys.Date(), "%Y%m%d")),
  s3.location = "s3://mybucket/data/"
)

# Return list of fields in table
dbListFields(con, "mtcars")

# Disconnect conenction
dbDisconnect(con)
}
```

--------------------------------

### Disconnect Athena Connection

Source: https://github.com/dyfanjones/noctua/blob/master/docs/reference/dbDisconnect.html

This example demonstrates how to establish a connection to AWS Athena using noctua::athena() and then properly close the connection using dbDisconnect(). Ensure you have an AWS account configured to run this code.

```R
if (FALSE) {
# Note:
# - Require AWS Account to run below example.
# - Different connection methods can be used please see `noctua::dbConnect` documnentation

library(DBI)

# Demo connection to Athena using profile name
con <- dbConnect(noctua::athena())

# Disconnect conenction
dbDisconnect(con)
}
```

--------------------------------

### Get DBMS Metadata from Connection

Source: https://github.com/dyfanjones/noctua/blob/master/docs/reference/dbGetInfo.html

Demonstrates how to connect to AWS Athena using a profile name and retrieve metadata from the connection object. Requires an AWS account.

```R
if (FALSE) {
# Note:
# - Require AWS Account to run below example.
# - Different connection methods can be used please see `noctua::dbConnect` documnentation

library(DBI)

# Demo connection to Athena using profile name
con <- dbConnect(noctua::athena())

# Returns metadata from connnection object
metadata <- dbGetInfo(con)

# Return metadata from Athena query object
res <- dbSendQuery(con, "show databases")
dbGetInfo(res)

# Clear result
dbClearResult(res)

# disconnect from Athena
dbDisconnect(con)
}
```

--------------------------------

### Assume AWS ARN Role and Set Environment Variables

Source: https://github.com/dyfanjones/noctua/blob/master/docs/reference/assume_role.html

This example demonstrates how to use the assume_role function to assume a specified AWS ARN role and set the returned temporary credentials as environment variables. This is useful when subsequent AWS operations need to use these assumed credentials.

```R
if (FALSE) {
# Note: 
# - Require AWS Account to run below example.

library(noctua)
library(DBI)

# Assuming demo ARN role
assume_role(profile_name = "YOUR_PROFILE_NAME",
            role_arn = "arn:aws:sts::123456789012:assumed-role/role_name/role_session_name",
            set_env = TRUE)
            
# Connect to Athena using ARN Role
con <- dbConnect(noctua::athena())
}
```

--------------------------------

### Check if Athena Table Exists

Source: https://github.com/dyfanjones/noctua/blob/master/docs/reference/dbExistsTable.html

This example demonstrates how to connect to Athena, write a data frame to a table, check if the table exists using dbExistsTable, and then disconnect. Ensure you have an AWS account and configure your connection details.

```R
if (FALSE) {
# Note:
# - Require AWS Account to run below example.
# - Different connection methods can be used please see `noctua::dbConnect` documnentation

library(DBI)

# Demo connection to Athena using profile name
con <- dbConnect(noctua::athena())

# Write data.frame to Athena table
dbWriteTable(con, "mtcars", mtcars,
  partition = c("TIMESTAMP" = format(Sys.Date(), "%Y%m%d")),
  s3.location = "s3://mybucket/data/"
)

# Check if table exists from Athena
dbExistsTable(con, "mtcars")

# Disconnect conenction
dbDisconnect(con)
}
```

--------------------------------

### Swap File Parsers On-the-Fly

Source: https://github.com/dyfanjones/noctua/blob/master/docs/articles/changing_backend_file_parser.html

Demonstrates switching between data.table and vroom file parsers between query executions. This allows for flexible performance optimization.

```r
library(DBI)
library(noctua)

con = dbConnect(athena())

# upload data
dbWriteTable(con, "iris", iris)

# use default data.table file parser
df1 = dbGetQuery(con, "select * from iris")

# use vroom as file parser
noctua_options("vroom")
df2 = dbGetQuery(con, "select * from iris")

# return back to data.table file parser
noctua_options()
df3 = dbGetQuery(con, "select * from iris")
```

--------------------------------

### Remove Table from Athena

Source: https://github.com/dyfanjones/noctua/blob/master/docs/reference/dbRemoveTable.html

This example demonstrates how to remove a table from AWS Athena using the dbRemoveTable function. It requires an active AWS account and a connection to Athena. The example also shows how to write data to an Athena table before removing it.

```R
if (FALSE) {
# Note:
# - Require AWS Account to run below example.
# - Different connection methods can be used please see `noctua::dbConnect` documnentation

library(DBI)

# Demo connection to Athena using profile name
con <- dbConnect(noctua::athena())

# Write data.frame to Athena table
dbWriteTable(con, "mtcars", mtcars,
  partition = c("TIMESTAMP" = format(Sys.Date(), "%Y%m%d")),
  s3.location = "s3://mybucket/data/"
)

# Remove Table from Athena
dbRemoveTable(con, "mtcars")

# Disconnect conenction
dbDisconnect(con)
}
```

--------------------------------

### Enable and Use Noctua Caching

Source: https://github.com/dyfanjones/noctua/blob/master/docs/articles/aws_athena_query_caching.html

Connect to Athena, set cache size, upload data, and observe performance differences between initial and repeated queries.

```R
library(DBI)
library(noctua)

con = dbConnect(athena())

# Start caching queries
noctua_options(cache_size = 10)

# Upload Data to AWS Athena
dbWriteTable(con, "iris", iris, partition = c("Partition" = "01"))

# initial query to AWS Athena
system.time(df1 = dbGetQuery(con, "select * from iris"))

# Info: (Data scanned: 3.63 KB)
#   user  system elapsed 
#  0.105   0.004   3.397 

# repeat query to AWS Athena
system.time(df2 = dbGetQuery(con, "select * from iris"))

# Info: (Data scanned: 3.63 KB)
#   user  system elapsed 
#  0.072   0.000   0.348 
```

--------------------------------

### get_session_token()

Source: https://github.com/dyfanjones/noctua/blob/master/docs/reference/index.html

Get Session Tokens for PAWS Connection.

```APIDOC
## get_session_token()

### Description
Retrieves session tokens required for establishing a PAWS connection.

### Method
Not applicable (function call)

### Endpoint
Not applicable (function call)

### Parameters
None explicitly documented.

### Request Example
```R
get_session_token()
```

### Response
Session tokens for PAWS connection.
```

--------------------------------

### dbGetStatement

Source: https://github.com/dyfanjones/noctua/blob/master/docs/reference/index.html

Get the statement associated with an Athena result set.

```APIDOC
## dbGetStatement

### Description
Get the statement associated with an Athena result set.

### Method
Not specified (likely a function call in R)

### Endpoint
Not applicable (R function)

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Request Example
```R
dbGetStatement(result)
```

### Response
#### Success Response
Not specified

#### Response Example
Not specified
```

--------------------------------

### Format AWS Athena Partitions

Source: https://github.com/dyfanjones/noctua/blob/master/docs/news/index.html

Demonstrates how to use the optional formatting for dbGetPartition to tidy up the default AWS Athena partition format.

```R
library(DBI)
library(noctua)
con <- dbConnect(athena())
dbGetPartition(con, "test_df2", .format = T)
# Info: (Data scanned: 0 Bytes)
#    year month day
# 1: 2020    11  17
dbGetPartition(con, "test_df2")
# Info: (Data scanned: 0 Bytes)
#                    partition
# 1: year=2020/month=11/day=17
```

--------------------------------

### Fetch all records from Athena query

Source: https://github.com/dyfanjones/noctua/blob/master/docs/reference/dbFetch.html

Demonstrates how to connect to AWS Athena, send a query to show databases, fetch all results using dbFetch, and clear the result set. Requires an AWS account and the noctua package.

```R
if (FALSE) {
# Note:
# - Require AWS Account to run below example.
# - Different connection methods can be used please see `noctua::dbConnect` documnentation

library(DBI)

# Demo connection to Athena using profile name
con <- dbConnect(noctua::athena())

res <- dbSendQuery(con, "show databases")
dbFetch(res)
dbClearResult(res)

# Disconnect from Athena
dbDisconnect(con)
}
```

--------------------------------

### dbGetInfo

Source: https://github.com/dyfanjones/noctua/blob/master/docs/reference/index.html

Get DBMS metadata for an Athena connection or result.

```APIDOC
## dbGetInfo

### Description
Get DBMS metadata for an Athena connection or result.

### Method
Not specified (likely a function call in R)

### Endpoint
Not applicable (R function)

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Request Example
```R
dbGetInfo(connection_or_result)
```

### Response
#### Success Response
Not specified

#### Response Example
Not specified
```

--------------------------------

### Build and Run Noctua Docker Image

Source: https://github.com/dyfanjones/noctua/blob/master/docs/articles/getting_started.html

Build a Docker image for Noctua and run a container, passing AWS credentials and region as environment variables.

```bash
# build docker image
docker build . -t noctua

# start container with aws credentials passed from local
docker run \
      -e AWS_ACCESS_KEY_ID="$(aws configure get aws_access_key_id)" \
      -e AWS_SECRET_ACCESS_KEY="$(aws configure get aws_secret_access_key)" \
      -e AWS_SESSION_TOKEN="$(aws configure get aws_session_token)" \
      -e AWS_DEFAULT_REGION="$(aws configure get region)" \
      -it noctua
```

--------------------------------

### List Athena Tables

Source: https://github.com/dyfanjones/noctua/blob/master/docs/reference/dbGetTables.html

Demonstrates how to connect to AWS Athena using a profile name and retrieve a hierarchy of tables. Requires an AWS account and the noctua and DBI R packages.

```R
if (FALSE) {
# Note:
# - Require AWS Account to run below example.
# - Different connection methods can be used please see `noctua::dbConnect` documnentation

library(DBI)
library(noctua)

# Demo connection to Athena using profile name
con <- dbConnect(noctua::athena())

# Return hierarchy of tables in Athena
dbGetTables(con)

# Disconnect conenction
dbDisconnect(con)
}
```

--------------------------------

### Get Specific Athena Work Group Information

Source: https://github.com/dyfanjones/noctua/blob/master/docs/reference/work_group.html

Retrieves detailed information about a specific Athena work group by its name.

```R
get_work_group(conn, work_group = NULL)
```

--------------------------------

### Get Partitions for an Athena Table

Source: https://github.com/dyfanjones/noctua/blob/master/README.md

Use noctua::dbGetPartition to retrieve all partitions for a specified table in Athena. Returns a data.frame.

```r
noctua::dbGetPartition(con, "impressions")
```

--------------------------------

### Determine String Data Type

Source: https://github.com/dyfanjones/noctua/blob/master/docs/reference/dbDataType.html

Use `dbDataType` with an Athena driver and a character vector to get the 'STRING' SQL type.

```R
dbDataType(athena(), c("x", "abc"))
#> [1] "STRING"
```

--------------------------------

### Determine Timestamp Data Type

Source: https://github.com/dyfanjones/noctua/blob/master/docs/reference/dbDataType.html

Use `dbDataType` with an Athena driver and a POSIXct object to get the 'TIMESTAMP' SQL type.

```R
dbDataType(athena(), Sys.time())
#> [1] "TIMESTAMP"
```

--------------------------------

### Determine Date Data Type

Source: https://github.com/dyfanjones/noctua/blob/master/docs/reference/dbDataType.html

Use `dbDataType` with an Athena driver and a Date object to get the 'DATE' SQL type.

```R
dbDataType(athena(), Sys.Date())
#> [1] "DATE"
```

--------------------------------

### Demonstrating Environment-based Slot Updates in Noctua

Source: https://github.com/dyfanjones/noctua/blob/master/NEWS.md

Illustrates how changes to parent connection slots are now reflected in child result slots due to the use of environments. This contrasts with the old method where modifications were isolated.

```R
library(DBI)
con <- dbConnect(noctua::athena(),
                 rstudio_conn_tab = F)

res <- dbExecute(con, "select 'helloworld'")

# Old Method
# modifying parent class to influence child
con@info$made_up <- "helloworld"

# nothing happened
res@connection@info$made_up
# > NULL

# modifying child class to influence parent
res@connection@info$made_up <- "oh no!"

# nothing happened
con@info$made_up
# > "helloworld"

# New Method
# modifying parent class to influence child
con@info$made_up <- "helloworld"

# picked up change
res@connection@info$made_up
# > "helloworld"

# modifying child class to influence parent
res@connection@info$made_up <- "oh no!"

# picked up change
con@info$made_up
# > "oh no!"
```

--------------------------------

### Compare dbRemoveTable performance: Athena vs. Glue

Source: https://github.com/dyfanjones/noctua/blob/master/NEWS.md

Compares the system time taken to remove a table using the Athena method versus the AWS Glue method. Demonstrates performance enhancement with Glue.

```r
library(DBI)

con = dbConnect(noctua::athena())

# upload iris dataframe for removal test
dbWriteTable(con, "iris2", iris)

# Athena method
system.time(dbRemoveTable(con, "iris2", confirm = T))
# user  system elapsed 
# 0.247   0.091   2.243 

# upload iris dataframe for removal test
dbWriteTable(con, "iris2", iris)

# Glue method
system.time(dbRemoveTable(con, "iris2", confirm = T))
# user  system elapsed 
# 0.110   0.045   1.094 
```

--------------------------------

### Determine Boolean Data Type

Source: https://github.com/dyfanjones/noctua/blob/master/docs/reference/dbDataType.html

Use `dbDataType` with an Athena driver and a boolean value to get the 'BOOLEAN' SQL type.

```R
dbDataType(athena(), TRUE)
#> [1] "BOOLEAN"
```

--------------------------------

### Determine Integer Data Type

Source: https://github.com/dyfanjones/noctua/blob/master/docs/reference/dbDataType.html

Use `dbDataType` with an Athena driver and an integer vector to get the 'INT' SQL type.

```R
library(noctua)
dbDataType(athena(), 1:5)
#> [1] "INT"
```

--------------------------------

### Simplified dbWriteTable with Default S3 Staging

Source: https://github.com/dyfanjones/noctua/blob/master/NEWS.md

Illustrates a simplified usage of dbWriteTable by leveraging the default S3 staging directory configured during dbConnect. This reduces the need to explicitly specify the s3.location parameter.

```r
library(DBI)

con <- dbConnect(noctua::athena())

dbWriteTable(con, "iris", iris)
```

--------------------------------

### Get Work Group Metadata

Source: https://github.com/dyfanjones/noctua/blob/master/docs/reference/work_group.html

Retrieves metadata for a specific work group by its name. Requires an active AWS connection object.

```R
library(noctua)

con <- dbConnect(noctua::athena())

# get meta data from work group
wg <- get_work_group(con, "demo_work_group")
```

--------------------------------

### Old Method: AthenaConnection and AthenaResult Class Behavior

Source: https://github.com/dyfanjones/noctua/blob/master/docs/news/index.html

Demonstrates the previous behavior where modifications to parent or child connection environments did not affect the other. This was due to copying environments instead of referencing them.

```R
library(DBI)
con <- dbConnect(noctua::athena(),
                 rstudio_conn_tab = F)

res <- dbExecute(con, "select 'helloworld'")

# modifying parent class to influence child
con@info$made_up <- "helloworld"

# nothing happened
res@connection@info$made_up
# > NULL

# modifying child class to influence parent
res@connection@info$made_up <- "oh no!"

# nothing happened
con@info$made_up
# > "helloworld"
```

--------------------------------

### Get Athena Work Group Metadata

Source: https://github.com/dyfanjones/noctua/blob/master/README.md

Retrieves detailed metadata for a specific work group in AWS Athena, including its configuration and description.

```r
get_work_group(con, "demo_work_group")
```

--------------------------------

### athena()

Source: https://github.com/dyfanjones/noctua/blob/master/docs/reference/athena.html

Initializes the Athena Driver, returning an S4 class object that can be used with `dbConnect` to establish a connection to AWS Athena.

```APIDOC
## athena()

### Description
Initializes the Athena Driver, returning an S4 class object that can be used with `dbConnect` to establish a connection to AWS Athena.

### Usage

```r
athena()
```

### Value

An S4 class object that is used to activate Athena methods for `dbConnect`.
```

--------------------------------

### Determine Single Value Data Type

Source: https://github.com/dyfanjones/noctua/blob/master/docs/reference/dbDataType.html

Use `dbDataType` with an Athena driver and a single numeric value to get the 'DOUBLE' SQL type.

```R
dbDataType(athena(), 1)
#> [1] "DOUBLE"
```

--------------------------------

### Changing Noctua File Parser

Source: https://github.com/dyfanjones/noctua/blob/master/NEWS.md

Demonstrates how to change the backend file parser in the Noctua package to 'vroom' using the noctua_options function.

```r
library(noctua)

noctua_options("vroom")
```

--------------------------------

### Execute and Fetch Query Results from Athena

Source: https://github.com/dyfanjones/noctua/blob/master/README.md

Connect to Athena using the default profile, execute a SQL query, fetch the results, and clear the result object.

```r
library(DBI)

# using default profile to connect
con <- dbConnect(noctua::athena(),
                 s3_staging_dir = 's3://path/to/query/bucket/')

res <- dbExecute(con, "SELECT * FROM one_row")
dbFetch(res)
dbClearResult(res)
```

--------------------------------

### Get Updated Work Group Metadata

Source: https://github.com/dyfanjones/noctua/blob/master/docs/reference/work_group.html

Retrieves the metadata for a work group after an update to verify changes. Requires an active AWS connection object.

```R
library(noctua)

con <- dbConnect(noctua::athena())

# get updated meta data from work group
wg <- get_work_group(con, "demo_work_group")
```

--------------------------------

### sqlCreateTable(_<AthenaConnection>_)

Source: https://github.com/dyfanjones/noctua/blob/master/docs/reference/index.html

Creates a query to create a simple Athena table.

```APIDOC
## sqlCreateTable(_<AthenaConnection>_)

### Description
Generates a SQL query to create a simple table in AWS Athena.

### Method
Not applicable (function call)

### Endpoint
Not applicable (function call)

### Parameters
* **_&lt;AthenaConnection&gt;** (object) - An object representing the Athena connection.

### Request Example
```R
sqlCreateTable(athena_connection)
```

### Response
A SQL query string for creating an Athena table.
```

--------------------------------

### Determine Raw Data Type

Source: https://github.com/dyfanjones/noctua/blob/master/docs/reference/dbDataType.html

Use `dbDataType` with an Athena driver and a raw vector to get the 'STRING' SQL type. Note that raw data is mapped to STRING.

```R
dbDataType(athena(), list(raw(10), raw(20)))
#> [1] "STRING"
```

--------------------------------

### Change File Parser to vroom

Source: https://github.com/dyfanjones/noctua/blob/master/docs/reference/noctua_options.html

Example of changing the default file parser from 'data.table' to 'vroom'. This affects how tables are read and written to Athena and the format of the returned data (tibble for 'vroom').

```R
library(noctua)

# change file parser from default data.table to vroom
noctua_options("vroom")
```

--------------------------------

### New Method: AthenaConnection and AthenaResult Class Behavior

Source: https://github.com/dyfanjones/noctua/blob/master/docs/news/index.html

Illustrates the updated behavior where modifications to parent or child connection environments are reflected in both. This is achieved by using environments that are shared by reference.

```R
library(DBI)
con <- dbConnect(noctua::athena(),
                 rstudio_conn_tab = F)

res <- dbExecute(con, "select 'helloworld'")

# modifying parent class to influence child
con@info$made_up <- "helloworld"

# picked up change
res@connection@info$made_up
# > "helloworld"

# modifying child class to influence parent
res@connection@info$made_up <- "oh no!"

# picked up change
con@info$made_up
# > "oh no!"
```

--------------------------------

### Connect to AWS Athena and List Work Groups

Source: https://github.com/dyfanjones/noctua/blob/master/docs/articles/getting_started.html

Connect to AWS Athena using the Noctua DBI interface and list all available work groups.

```r
library(DBI)
library(noctua)

con <- dbConnect(athena())

# list all current work groups in AWS Athena
list_work_groups(con)
```

--------------------------------

### Show Athena Table DDL

Source: https://github.com/dyfanjones/noctua/blob/master/docs/reference/dbShow.html

Demonstrates how to connect to Athena, write a table, retrieve its DDL using dbShow, and disconnect. Requires an AWS account and a specified S3 location for table storage.

```R
if (FALSE) {
# Note:
# - Require AWS Account to run below example.
# - Different connection methods can be used please see `noctua::dbConnect` documnentation

library(DBI)

# Demo connection to Athena using profile name
con <- dbConnect(noctua::athena())

# write iris table to Athena
dbWriteTable(con, "iris",
  iris,
  partition = c("timestamp" = format(Sys.Date(), "%Y%m%d")),
  s3.location = "s3://path/to/store/athena/table/"
)

# return table ddl
noctua::dbShow(con, "iris")

# disconnect from Athena
dbDisconnect(con)
}
```

--------------------------------

### Convert SQL Query to ORC with Partition

Source: https://github.com/dyfanjones/noctua/blob/master/docs/articles/convert_and_save_cost.html

Convert the results of a SQL query into an ORC file, partitioned by a generated timestamp column. This allows for dynamic data conversion based on query results.

```r
dbConvertTable(con,
                  obj = SQL("select 
                              Sepal_Length,
                              Sepal_Width,
                              date_format(current_date, '%Y%m%d') as time_stamp 
                            from temp.iris_delim"),
                  name = "iris_orc_partition",
                  partition = "time_stamp",
                  file.type = "orc")
```

--------------------------------

### Retrieve AWS Athena Statistics

Source: https://github.com/dyfanjones/noctua/blob/master/docs/reference/dbStatistics.html

Use this snippet to get statistics from an executed Athena query. Requires an AWS account and a database connection. The connection can be established using various methods, such as `RAthena::dbConnect`.

```r
if (FALSE) {
# Note:
# - Require AWS Account to run below example.
# - Different connection methods can be used please see `RAthena::dbConnect` documnentation

library(DBI)
library(noctua)

# Demo connection to Athena using profile name
con <- dbConnect(noctua::athena())

res <- dbSendQuery(con, "show databases")
dbStatistics(res)

# Clean up
dbClearResult(res)
}
```

--------------------------------

### Chunked writeBin for Large Raw Vectors

Source: https://github.com/dyfanjones/noctua/blob/master/docs/news/index.html

Demonstrates how to write large raw vectors in chunks using a custom `write_bin` function to overcome the 2^31 - 1 byte limit. Compares its performance against `readr::write_file`.

```R
library(readr)
library(microbenchmark)

# creating some dummy data for testing
X <- 1e8
df <- 
data.frame(
    w = runif(X),
    x = 1:X,
    y = sample(letters, X, replace = T), 
    z = sample(c(TRUE, FALSE), X, replace = T)))
write_csv(df, "test.csv")

# read in text file into raw format
obj <- readBin("test.csv", what = "raw", n = file.size("test.csv"))

format(object.size(obj), units = "auto")
# 3.3 Gb

# writeBin in a loop
write_bin <- function(
  value,
  filename,
  chunk_size = 2L ^ 20L) {
  
  total_size <- length(value)
  split_vec <- seq(1, total_size, chunk_size)
  
  con <- file(filename, "a+b")
  on.exit(close(con))
  
  sapply(split_vec, function(x){
    writeBin(value[x:min(total_size,(x+chunk_size-1))],con)
  })
  invisible(TRUE)
}

microbenchmark(writeBin_loop = write_bin(obj, tempfile()),
               readr = write_file(obj, tempfile()),
               times = 5)
```

--------------------------------

### Send Queries to Athena

Source: https://github.com/dyfanjones/noctua/blob/master/docs/reference/Query.html

Demonstrates how to send different types of SQL queries to Athena using dbSendQuery, dbSendStatement, and dbExecute. Requires an AWS account and a connection to Athena.

```R
if (FALSE) {
# Note:
# - Require AWS Account to run below example.
# - Different connection methods can be used please see `noctua::dbConnect` documnentation

library(DBI)

# Demo connection to Athena using profile name
con <- dbConnect(noctua::athena())

# Sending Queries to Athena
res1 <- dbSendQuery(con, "show databases")
res2 <- dbSendStatement(con, "show databases")
res3 <- dbExecute(con, "show databases")

# Disconnect conenction
dbDisconnect(con)
}
```

--------------------------------

### Write Data Frame to Athena Table

Source: https://github.com/dyfanjones/noctua/blob/master/docs/reference/AthenaWriteTables.html

This example demonstrates writing an R data frame to an Athena table, including partitioning by date and specifying an S3 location. Ensure you have an AWS account and the necessary connection details.

```R
library(DBI)

# Demo connection to Athena using profile name 
con <- dbConnect(noctua::athena())

# List existing tables in Athena
dbListTables(con)

# Write data.frame to Athena table
dbWriteTable(con, "mtcars", mtcars,
             partition=c("TIMESTAMP" = format(Sys.Date(), "%Y%m%d")),
             s3.location = "s3://mybucket/data/")

# Read entire table from Athena
dbReadTable(con, "mtcars")

# List all tables in Athena after uploading new table to Athena
dbListTables(con)

# Checking if uploaded table exists in Athena
dbExistsTable(con, "mtcars")

# using default s3.location
dbWriteTable(con, "iris", iris)

# Read entire table from Athena
dbReadTable(con, "iris")

# List all tables in Athena after uploading new table to Athena
dbListTables(con)

# Checking if uploaded table exists in Athena
dbExistsTable(con, "iris")

# Disconnect from Athena
dbDisconnect(con)
```

--------------------------------

### Set vroom as File Parser

Source: https://github.com/dyfanjones/noctua/blob/master/docs/articles/changing_backend_file_parser.html

Use noctua_options to set 'vroom' as the file parser. This requires loading the DBI and noctua libraries and establishing a database connection.

```r
library(DBI)
library(noctua)

con = dbConnect(athena())

noctua_options(file_parser = c("data.table", "vroom"))
```

--------------------------------

### Configure Noctua Retry Settings

Source: https://github.com/dyfanjones/noctua/blob/master/docs/articles/how_to_retry.html

Change the default retry settings to perform 10 retries and do it quietly. This is useful for reducing log verbosity during retries.

```R
noctua_options(retry = 10, retry_quiet = TRUE)
```

--------------------------------

### db_explain.AthenaConnection

Source: https://github.com/dyfanjones/noctua/blob/master/docs/reference/backend_dbplyr.html

Attempts to explain a SQL query for AWS Athena. Note that AWS Athena does not support EXPLAIN queries.

```APIDOC
## db_explain.AthenaConnection

### Description
This function is intended to provide query execution plan details for AWS Athena. However, AWS Athena does not support `EXPLAIN` queries, so this function will raise an error.

### Method
Not applicable (R function)

### Endpoint
Not applicable (R function)

### Parameters
#### Arguments
- **con** (AthenaConnection) - A `dbConnect` object for Athena.
- **sql** (string) - The SQL code to be explained.
- **...** - Other parameters, currently not implemented.

### Value
Raises an `error` as AWS Athena does not support `EXPLAIN` queries.
```

--------------------------------

### Get Temporary AWS Credentials

Source: https://github.com/dyfanjones/noctua/blob/master/docs/reference/session_token.html

Use this function to obtain temporary credentials for an AWS account or IAM user. It supports MFA authentication and allows setting environment variables for subsequent AWS operations. Requires an AWS account to run.

```R
if (FALSE) {
# Note: 
# - Require AWS Account to run below example.

library(noctua)
library(DBI)

# Create Temporary Credentials duration 1 hour
get_session_token("YOUR_PROFILE_NAME",
                  serial_number='arn:aws:iam::123456789012:mfa/user',
                  token_code = "531602",
                  set_env = TRUE)

# Connect to Athena using temporary credentials
con <- dbConnect(athena())
}
```

--------------------------------

### dbShow

Source: https://github.com/dyfanjones/noctua/blob/master/docs/reference/index.html

Show Athena table's DDL.

```APIDOC
## dbShow

### Description
Show Athena table's DDL.

### Method
Not specified (likely a function call in R)

### Endpoint
Not applicable (R function)

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Request Example
```R
dbShow(table_name)
```

### Response
#### Success Response
Not specified

#### Response Example
Not specified
```

--------------------------------

### Read CSV and Write Parquet to S3 with AWS Athena

Source: https://github.com/dyfanjones/noctua/blob/master/NEWS.md

Demonstrates reading data from S3 using CSV format and writing it to S3 as a Parquet dataset, creating an AWS Athena table. Requires AWS credentials and an S3 bucket.

```python
import awswrangler as wr

import getpass
bucket = getpass.getpass()
path = f"s3://{bucket}/data/"


if "awswrangler_test" not in wr.catalog.databases().values:
    wr.catalog.create_database("awswrangler_test")

cols = ["id", "dt", "element", "value", "m_flag", "q_flag", "s_flag", "obs_time"]

df = wr.s3.read_csv(
    path="s3://noaa-ghcn-pds/csv/189",
    names=cols,
    parse_dates=["dt", "obs_time"])

wr.s3.to_parquet(
    df=df,
    path=path,
    dataset=True,
    mode="overwrite",
    database="awswrangler_test",
    table="noaa");

wr.catalog.table(database="awswrangler_test", table="noaa")
```

--------------------------------

### Clear Noctua Cache

Source: https://github.com/dyfanjones/noctua/blob/master/docs/articles/aws_athena_query_caching.html

Demonstrates how to clear the cache by setting the 'clear_cache' option to TRUE.

```R
noctua_options(clear_cache = T)
```

--------------------------------

### noctua_options()

Source: https://github.com/dyfanjones/noctua/blob/master/docs/reference/index.html

A method to configure noctua backend options.

```APIDOC
## noctua_options()

### Description
Configures the backend options for Noctua.

### Method
Not applicable (function call)

### Endpoint
Not applicable (function call)

### Parameters
None explicitly documented.

### Request Example
```R
noctua_options()
```

### Response
Configuration settings for Noctua.
```