### Example: Show Create View for 'orders_by_date'

Source: https://docs.aws.amazon.com/athena/latest/ug/show-create-view.md

This example demonstrates how to retrieve the creation statement for the 'orders_by_date' view.

```sql
SHOW CREATE VIEW orders_by_date
```

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

### Example publish.sh command

Source: https://docs.aws.amazon.com/athena/latest/ug/udf-creating-and-deploying.md

An example of how to run the `publish.sh` script, specifying the S3 bucket path and the YAML file name for deploying your Athena UDFs.

```bash
./publish.sh amzn-s3-demo-bucket/mysarapps/athenaudf my-athena-udfs

```

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

### Ping IdP Host Connection String Example

Source: https://docs.aws.amazon.com/athena/latest/ug/odbc-v2-driver-ping.md

Example for specifying the address of your Ping server. This parameter is required.

```text
idp_host=ec2-1-83-65-12.compute-1.amazonaws.com;
```

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

### Example Passthrough Query for BigQuery

Source: https://docs.aws.amazon.com/athena/latest/ug/connectors-bigquery.md

This example demonstrates pushing a query to select all columns from the 'customer' table with a limit of 10 to BigQuery.

```sql
SELECT * FROM TABLE(
        system.query(
            query => 'SELECT * FROM customer LIMIT 10'
        ))

```

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

### Ping Partner SPID Connection String Example

Source: https://docs.aws.amazon.com/athena/latest/ug/odbc-v2-driver-ping.md

Example for specifying the service provider address. This parameter is required.

```text
partner_spid=https://us-east-1.signin.aws.amazon.com/platform/saml/<...>;;
```

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

### Ping Authentication Connection String Example

Source: https://docs.aws.amazon.com/athena/latest/ug/odbc-v2-driver-ping.md

Example of a connection string using Ping authentication. Ensure all required parameters are correctly set.

```text
AuthenticationType=Ping;
```

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

### Ping Password Connection String Example

Source: https://docs.aws.amazon.com/athena/latest/ug/odbc-v2-driver-ping.md

Example for specifying the Password (PWD) for the PingFederate server in the connection string.

```text
PWD=pingpassword;
```

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

### Create a virtual environment for package installation

Source: https://docs.aws.amazon.com/athena/latest/ug/notebooks-import-files-libraries.md

Set up a local virtual environment using `virtualenv` to manage Python packages. This is a prerequisite for installing packages from PyPI.

```bash
/tmp $ mkdir testpiglatin
/tmp $ cd testpiglatin
testpiglatin $ virtualenv .
```

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

### Create Iceberg Table Example

Source: https://docs.aws.amazon.com/athena/latest/ug/querying-iceberg-creating-tables.md

Example of creating an Iceberg table with specified columns, partitioning, location, and table properties. Ensure 'table_type' is set to 'ICEBERG'.

```sql
CREATE TABLE iceberg_table (
  id int,
  data string,
  category string) 
PARTITIONED BY (category, bucket(16,id)) 
LOCATION 's3://amzn-s3-demo-bucket/{{iceberg-folder}}' 
TBLPROPERTIES (
  'table_type'='ICEBERG',
  'format'='parquet',
  'write_compression'='snappy',
  'optimize_rewrite_delete_file_threshold'='10'
)
```

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

### Ping IdP Port Connection String Example

Source: https://docs.aws.amazon.com/athena/latest/ug/odbc-v2-driver-ping.md

Example for specifying the port number to connect to your IdP host. This parameter is required.

```text
idp_port=443;
```

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

### Example SHOW CREATE TABLE Command

Source: https://docs.aws.amazon.com/athena/latest/ug/querying-iceberg-show-create-table.md

Execute this example command to retrieve the CREATE TABLE DDL statement for a table named 'iceberg_table' in Athena.

```sql
SHOW CREATE TABLE iceberg_table
```

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

### Ping User ID Connection String Example

Source: https://docs.aws.amazon.com/athena/latest/ug/odbc-v2-driver-ping.md

Example for specifying the User ID (UID) for the PingFederate server in the connection string.

```text
UID=pingusername@domain.com;
```

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

### Single Db2 AS/400 Connection String Example

Source: https://docs.aws.amazon.com/athena/latest/ug/connectors-ibm-db2-as400.md

Example of a default connection string for a single Db2 AS/400 instance, used with single connection handlers.

```properties
default = db2as400://jdbc:as400://{{<ip_address>}};{{<properties>}};:${{{<secret_name>}}};
```

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

### Example PostgreSQL Connection String (No SSL)

Source: https://docs.aws.amazon.com/athena/latest/ug/connectors-postgresql.md

This example connection string demonstrates a typical PostgreSQL connection without SSL enabled.

```sql
postgres://jdbc:postgresql://example-asdf-aurora-postgres-endpoint:5432/asdf?user=someuser&password=somepassword
```

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

### Oracle Single Connection Handler Example

Source: https://docs.aws.amazon.com/athena/latest/ug/connectors-oracle.md

Example property for a single Oracle instance supported by a Lambda function, demonstrating connection string formats.

```text
oracle://jdbc:oracle:thin:${Test/RDS/Oracle}@//hostname:port/servicename
```

```text
oracle://jdbc:oracle:thin:${Test/RDS/Oracle}@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS) (HOST=<HOST_NAME>)(PORT=))(CONNECT_DATA=(SID=))(SECURITY=(SSL_SERVER_CERT_DN=)))
```

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

### Combined Predicate Pushdown Example

Source: https://docs.aws.amazon.com/athena/latest/ug/connectors-cloudera-impala.md

Demonstrates combining predicate pushdown types for enhanced querying. This example pushes down a greater than condition to Cloudera Impala.

```sql
SELECT *
FROM my_table
WHERE col_a > 10

```

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

### Set Python path and start Python interpreter

Source: https://docs.aws.amazon.com/athena/latest/ug/notebooks-import-files-libraries.md

Configures the PYTHONPATH environment variable to include the zip file and then starts the Python interpreter for local testing.

```bash
/home $ PYTHONPATH=/tmp/testpiglatin/library.zip 
/home $ python3
```

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

### MySQL Connection String Example

Source: https://docs.aws.amazon.com/athena/latest/ug/connectors-mysql.md

Example of a connection string for a single MySQL instance when using a Lambda function. It specifies the database host, port, and a secret for authentication.

```text
mysql://mysql1.host:3306/default?secret=Test/RDS/MySql1
```

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

### Example: Rename Partition

Source: https://docs.aws.amazon.com/athena/latest/ug/alter-table-rename-partition.md

This example demonstrates renaming a partition for the 'orders' table. It changes the 'dt' partition from '2014-05-14' to '2014-05-15' while keeping the 'country' partition as 'IN'.

```sql
ALTER TABLE orders 
PARTITION (dt = '2014-05-14', country = 'IN') RENAME TO PARTITION (dt = '2014-05-15', country = 'IN');
```

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

### Aggregation Query with Spill-to-Disk Support

Source: https://docs.aws.amazon.com/athena/latest/ug/release-notes.md

Example of an aggregation query that can benefit from spill-to-disk support for DISTINCT and ORDER BY clauses. This is useful for large datasets where intermediate results might exceed memory.

```sql
SELECT array_agg(orderstatus ORDER BY orderstatus)
FROM orders
GROUP BY orderpriority, custkey
```

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

### EXPLAIN output showing partition key values

Source: https://docs.aws.amazon.com/athena/latest/ug/performance-tuning-data-optimization-techniques.md

This example demonstrates how EXPLAIN output displays partition key values that will be scanned. It helps in understanding query selectivity on partition keys.

```text
dt := dt:string:PARTITION_KEY
    :: [[2023-06-11], [2023-06-12], [2023-06-13]]
```

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

### Query Table with Derived Timestamp

Source: https://docs.aws.amazon.com/athena/latest/ug/querying-iis-logs-w3c-extended-log-file-format.md

Query the newly created table `iis_w3c_logs_w_timestamp` to access the combined timestamp column. This example filters for successful GET requests.

```sql
SELECT derived_timestamp, cs_uri_stem, time_taken
FROM iis_w3c_logs_w_timestamp
WHERE cs_method = 'GET' AND sc_status = '200'
```

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

### Clone the AWS Athena Query Federation SDK

Source: https://docs.aws.amazon.com/athena/latest/ug/udf-creating-and-deploying.md

Clone the SDK repository to access the UDF framework, examples, and data source connectors. Ensure git is installed before running.

```bash
git clone https://github.com/awslabs/aws-athena-query-federation.git
```

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

### Create virtual environment and project directory

Source: https://docs.aws.amazon.com/athena/latest/ug/notebooks-import-files-libraries.md

Sets up a project directory and a virtual environment for installing Python packages, including those with dependencies.

```bash
/tmp $ mkdir testmd2gemini
/tmp $ cd testmd2gemini
testmd2gemini$ virtualenv .
```

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

### Example IIS log format select query

Source: https://docs.aws.amazon.com/athena/latest/ug/querying-iis-logs-iis-log-file-format.md

Query to select specific fields from the IIS log table. Note the required leading spaces in the WHERE clause for ' GET' and ' 200'.

```sql
SELECT request_date, request_time, target_of_operation, time_taken_millisec
FROM iis_format_logs
WHERE request_type = ' GET' AND service_status_code = ' 200'
```

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

### Prepare and Execute a Query with Multiple Parameters

Source: https://docs.aws.amazon.com/athena/latest/ug/sql-execute.md

This example shows how to prepare a query that requires multiple parameters and then execute it by providing values for each parameter in the USING clause.

```sql
PREPARE my_select3 FROM 
SELECT order FROM orders WHERE productid = ? and quantity < ? 
EXECUTE my_select3 USING 346078, 12
```

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

### Example JSON Output for Database Properties

Source: https://docs.aws.amazon.com/athena/latest/ug/create-database.md

This JSON structure represents the output from the AWS CLI command to get database properties, showing details like name, description, location, and parameters.

```json
{
    "Database": {
        "Name": "{{<your-database-name>}}",
        "Description": "{{<your-database-comment>}}",
        "LocationUri": "s3://amzn-s3-demo-bucket",
        "Parameters": {
            "{{<your-database-property-name>}}": "{{<your-database-property-value>}}"
        },
        "CreateTime": 1603383451.0,
        "CreateTableDefaultPermissions": [
            {
                "Principal": {
                    "DataLakePrincipalIdentifier": "IAM_ALLOWED_PRINCIPLES"
                },
                "Permissions": [
                    "ALL"
                ]
            }
        ]
    }
}
```

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

### Accessing Array Elements with element_at() Function

Source: https://docs.aws.amazon.com/athena/latest/ug/accessing-array-elements.md

The `element_at()` function retrieves array elements by index. Positive indices count from the start, while negative indices count from the end. Use `cardinality(array)` to get the total number of elements.

```sql
WITH dataset AS (
  SELECT ARRAY ['hello', 'amazon', 'athena'] AS words
)
SELECT
  element_at(words, 1) AS first_word,
  element_at(words, -2) AS middle_word,
  element_at(words, cardinality(words)) AS last_word
FROM dataset
```

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

### Prepare and Execute a Query with No Parameters

Source: https://docs.aws.amazon.com/athena/latest/ug/sql-execute.md

This example demonstrates preparing a simple SELECT statement and then executing it without any parameters.

```sql
PREPARE my_select1 FROM 
SELECT name FROM nation 
EXECUTE my_select1
```

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

### Verify ODBC Driver Installation with RPM

Source: https://docs.aws.amazon.com/athena/latest/ug/odbc-v2-driver-getting-started-linux.md

Verify the installation of the Amazon Athena ODBC driver by querying installed RPM packages.

```bash
rpm -qa | grep amazon
```

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

### Verify ODBC Driver Installation with YUM

Source: https://docs.aws.amazon.com/athena/latest/ug/odbc-v2-driver-getting-started-linux.md

Check if the Amazon Athena ODBC driver is installed by listing installed packages and filtering for the driver name.

```bash
yum list | grep amazon-athena-odbc-driver
```

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

### Verify Athena ODBC Driver Installation on macOS

Source: https://docs.aws.amazon.com/athena/latest/ug/odbc-v2-driver-getting-started-macos.md

Run this command after installation to confirm the Athena ODBC driver components are present. The output should list the installed packages.

```bash
> pkgutil --pkgs | grep athena.odbc
```

```bash
com.amazon.athena.odbc.Runtime
com.amazon.athena.odbc.Documentation
```

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

### Create Bucketed and Partitioned Avro Table

Source: https://docs.aws.amazon.com/athena/latest/ug/ctas-examples.md

Create a table using both partitioning by 'nationkey' and bucketing by 'mktsegment' with 3 buckets. This example demonstrates advanced CTAS configurations.

```sql
CREATE TABLE ctas_avro_bucketed 
WITH ( 
      format = 'AVRO', 
      external_location = 's3://amzn-s3-demo-bucket/ctas_avro_bucketed/', 
      partitioned_by = ARRAY['nationkey'], 
      bucketed_by = ARRAY['mktsegment'], 
      bucket_count = 3) 
AS SELECT key1, name1, address1, phone1, acctbal, mktsegment, comment1, nationkey 
FROM table1;
```

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

### Amazon Ion Document Example

Source: https://docs.aws.amazon.com/athena/latest/ug/ion-serde-using-search-paths-in-path-extractors.md

An example of an Amazon Ion document structure.

```ion
{
    foo: ["foo1", "foo2"] ,
    bar: "myBarValue", 
    bar: A::"annotatedValue"
}
```

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

### Combined Pushdown Example for Redshift Connector

Source: https://docs.aws.amazon.com/athena/latest/ug/connectors-redshift.md

Demonstrates combining various pushdown types like WHERE clauses, arithmetic operations, LIKE, and ORDER BY with LIMIT for optimized Redshift queries.

```sql
SELECT * 
FROM my_table 
WHERE col_a > 10 
    AND ((col_a + col_b) > (col_c % col_d)) 
    AND (col_e IN ('val1', 'val2', 'val3') OR col_f LIKE '%pattern%') 
ORDER BY col_a DESC 
LIMIT 10;
```

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

### Count HTTP GET Requests by Client IP

Source: https://docs.aws.amazon.com/athena/latest/ug/query-alb-access-logs-examples.md

Counts the number of HTTP GET requests received by the load balancer, grouped by the client IP address. Useful for identifying top clients making GET requests.

```sql
SELECT COUNT(request_verb) AS
 count,
 request_verb,
 client_ip
FROM alb_access_logs
GROUP BY request_verb, client_ip
LIMIT 100;
```

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

### Db2 Connection String Example

Source: https://docs.aws.amazon.com/athena/latest/ug/connectors-ibm-db2.md

Example of a Db2 connection string for use with the AWS Glue connector.

```sql
dbtwo://jdbc:db2://hostname:port/{{database_name}}:user={{user_name}};password={{password}};
```

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

### Add Partition with IF NOT EXISTS Clause

Source: https://docs.aws.amazon.com/athena/latest/ug/alter-table-add-partition.md

This example shows how to use the `IF NOT EXISTS` clause to prevent an error if the partition you are trying to add already exists in the table.

```sql
ALTER TABLE {{table_name}} ADD IF NOT EXISTS PARTITION (ds='2023-01-01')
```

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

### Specify Zstandard compression for Amazon Ion output

Source: https://docs.aws.amazon.com/athena/latest/ug/ion-serde-using-ctas-and-insert-into-to-create-ion-tables.md

This example demonstrates specifying the Zstandard compression algorithm for writing Amazon Ion output files. Other options include GZIP, BZIP2, SNAPPY, and NONE.

```sql
WITH (format='ION', write_compression = 'ZSTD')
```

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

### Show Create View Syntax

Source: https://docs.aws.amazon.com/athena/latest/ug/show-create-view.md

Use this synopsis to display the SQL statement for a given view.

```sql
SHOW CREATE VIEW {{view_name}}
```

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

### Extracted Table Example

Source: https://docs.aws.amazon.com/athena/latest/ug/ion-serde-generated-path-extractors.md

This example shows the structure of the table after Athena has extracted the data using the generated path extractors.

```text
|                  identification                    |  alias   |
|----------------------------------------------------|----------|
|{["name", "driver_license"],["John Smith", "XXXX"]} | "Johnny" |
```

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

### Create a Simple Database

Source: https://docs.aws.amazon.com/athena/latest/ug/create-database.md

Use this command to create a basic database. The database name must be unique.

```sql
CREATE DATABASE clickstreams;
```

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

### Interval Data Type Examples

Source: https://docs.aws.amazon.com/athena/latest/ug/data-types-examples.md

Represents a duration. Examples show INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND.

```sql
INTERVAL '3' MONTH
```

```sql
INTERVAL '2' DAY
```

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

### Create Iceberg Table with Partitioning

Source: https://docs.aws.amazon.com/athena/latest/ug/querying-iceberg-creating-tables.md

This example demonstrates creating an Iceberg table with partitioning using the PARTITIONED BY clause. It includes partitioning by a string column and by a hashed integer column using the bucket transform.

```sql
CREATE TABLE iceberg_table (id bigint, data string, category string)
  PARTITIONED BY (category, bucket(16, id))
  LOCATION 's3://amzn-s3-demo-bucket/{{your-folder}}/'
  TBLPROPERTIES ( 'table_type' = 'ICEBERG' )
```

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

### Install Python package into a specific directory

Source: https://docs.aws.amazon.com/athena/latest/ug/notebooks-import-files-libraries.md

Installs a Python package and its dependencies into a specified directory, preparing it for packaging into a .zip file.

```bash
testpiglatin $ bin/pip install -t $PWD/unpacked piglatin
```

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

### AWS CLI: Start Query Execution with Parameters

Source: https://docs.aws.amazon.com/athena/latest/ug/querying-with-prepared-statements-running-queries-with-execution-parameters-using-the-aws-cli.md

Use the `start-query-execution` command to run a parameterized query. Provide the query string with placeholders and then supply the values for these placeholders in the `execution-parameters` argument.

```bash
aws athena start-query-execution \
--query-string "SELECT * FROM table WHERE x = ? AND y = ?" \
--query-execution-context "Database"="default" \
--result-configuration "OutputLocation"="s3://amzn-s3-demo-bucket;/..." \
--execution-parameters "1" "2"
```

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

### Create a zip archive of project files

Source: https://docs.aws.amazon.com/athena/latest/ug/notebooks-import-files-libraries.md

Use the zip command to package project files into a zip archive for import. The `-r9` flags recursively include directories and use maximum compression.

```bash
unpacked $ zip -r9 ../md2gemini *

```

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

### Time Data Type Examples

Source: https://docs.aws.amazon.com/athena/latest/ug/data-types-examples.md

Examples for TIME and TIME WITH TIME ZONE data types. Precision can be specified for fractional seconds.

```sql
TIME '10:11:12'
```

```sql
TIME '10:11:12.345'
```

```sql
TIME '10:11:12.345 -06:00'
```

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

### List Partitions for a Specific Table using SHOW PARTITIONS

Source: https://docs.aws.amazon.com/athena/latest/ug/querying-glue-catalog-listing-partitions.md

Use the SHOW PARTITIONS command to list all partitions for a specified table. This is a straightforward way to view partition information.

```sql
SHOW PARTITIONS cloudtrail_logs_test2
```

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

### Example Partition Structure in Athena

Source: https://docs.aws.amazon.com/athena/latest/ug/ctas-insert-into.md

This is a comment block showing an example of partition keys and their values for a table, typically displayed after running SHOW PARTITIONS or inspecting table metadata.

```sql
/*
l_shipdate=1992-01-02
l_shipdate=1992-01-03
l_shipdate=1992-01-04
l_shipdate=1992-01-05
l_shipdate=1992-01-06

...

l_shipdate=1992-02-20
l_shipdate=1992-02-21
l_shipdate=1992-02-22
l_shipdate=1992-02-23
l_shipdate=1992-02-24
l_shipdate=1992-02-25
l_shipdate=1992-02-26
l_shipdate=1992-02-27
l_shipdate=1992-02-28
l_shipdate=1992-02-29
*/
```

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

### Example Redis Passthrough Query

Source: https://docs.aws.amazon.com/athena/latest/ug/connectors-redis.md

This example demonstrates running a Lua script to retrieve the value associated with the key 'l:a' in Redis.

```sql
SELECT * FROM TABLE(
        system.script(
            script => 'return redis.call("GET", KEYS[1])',
            keys => '[l:a]',
            argv => '[]'
))

```

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

### Example datetimeFormatMapping values

Source: https://docs.aws.amazon.com/athena/latest/ug/connectors-dynamodb.md

These are example formats for the 'datetimeFormatMapping' table property, used to specify how 'date' or 'datetime' values should be parsed from columns.

```text
yyyyMMdd'T'HHmmss
```

```text
ddMMyyyy'T'HH:mm:ss
```

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

### Install Python package with dependencies into a specific directory

Source: https://docs.aws.amazon.com/athena/latest/ug/notebooks-import-files-libraries.md

Installs a Python package and its dependencies into a specified directory using pip, preparing it for packaging.

```bash
/testmd2gemini $ bin/pip install -t $PWD/unpacked md2gemini
```

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

### Describe Table Schema and Data Types

Source: https://docs.aws.amazon.com/athena/latest/ug/describe-table.md

This example shows the output of the DESCRIBE command for the 'impressions' table, listing each column name, its data type, and source.

```sql
DESCRIBE impressions
```

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

### Example Cross-Account Query in Athena

Source: https://docs.aws.amazon.com/athena/latest/ug/security-iam-cross-account-glue-catalog-access.md

This is an example of how to reference a table in an owner account's Glue catalog from a borrower account using Athena.

```sql
SELECT * FROM ownerCatalog.tpch1000.customer
```

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

### Add Multiple Partitions for Hive-Style Data

Source: https://docs.aws.amazon.com/athena/latest/ug/alter-table-add-partition.md

This example shows how to add multiple partitions to a table with Hive-style partitioning in a single statement. Each partition is defined with its column name/value pairs.

```sql
ALTER TABLE orders ADD
  PARTITION (dt = '2016-05-31', country = 'IN')
  PARTITION (dt = '2016-06-01', country = 'IN');
```