[Feature] Athena: iceberg tables without uuid4 names

### Is this your first time submitting a feature request?

- [x] I have read the [expectations for open source contributors](https://docs.getdbt.com/docs/contributing/oss-expectations)
- [x] I have searched the existing issues, and I could not find an existing issue for this feature
- [x] I am requesting a straightforward extension of existing dbt functionality, rather than a Big Idea better suited to a discussion

### Describe the feature

Allow materializing non-incremental Iceberg tables without unique naming (`uuid4`)

### Describe alternatives you've considered

Currently not possible due to hardcoded exception: https://github.com/dbt-labs/dbt-adapters/blob/733b5f766e91a0ef65896fab29d3bc52e11a3104/dbt-athena/src/dbt/include/athena/macros/materializations/models/table/create_table_as.sql#L110

### Who will this benefit?

Whoever uses Iceberg tables and wants:
- **Consistent naming strategy** between incremental and non-incremental model
- **A predictable path to Iceberg metadata** via `/path/to/{model_name}/metatadata` to consume easily via S3+Iceberg without additional lookup via Glue catalog or S3 (some of our consumers don't go through Athena)

### Are you interested in contributing this feature?

yes, I'd like to submit a PR

### Anything else?

🗺️ **CONTEXT**:
Using **dbt-athena** adapter to create **non-incremental** **Iceberg** tables (i.e. the default DBT `table` materialization)

👉 **CURRENT BEVAVIOUR**:
We must use [one of the unique naming rules](https://docs.getdbt.com/reference/resource-configs/athena-configs?version=2.0&name=Fusion#table-location) which create a `uuid4` subfolder
- **unique**: `{s3_data_dir}/{uuid4()}/`
- **table_unique**: `{s3_data_dir}/{table}/{uuid4()}/`
- **schema_table_unique**: `{s3_data_dir}/{schema}/{table}/{uuid4()}/`

💡 **UNDERSTANDING**:
DBT wants to have a unique table name so it can create the new table without conflict w/ existing, then do a rename, thus minimizing downtime, hence the use of the `uuid4` subfolder. We don't have such issue with incremental models since we keep the same table, just append to it.

🔴 **PROBLEMS**:
- **Inconsistent patterns**: we have our incremental models under `/path/to/{model_name}` and non-incremental under `/path/to/{model_name}/uuid4`
- **Extra glue or S3 lookup for uuid => slower + extra logic**: since iceberg metadata folder s3 location changes, jobs that want to consume the data via Iceberg+S3 directly can no longer point directly to `/path/to/{model_name}/metatadata`, they have to do an extra lookup via Glue API or S3 to determine the `uuid4` subfolder

🟢 **SOLUTION**
While we appreciate the concern for performance, for our use case, doing Iceberg+Table without the unique/rename works just fine:

`dbt/include/athena/macros/materializations/models/table/create_table_as.sql`

Replace **CTAS**:
https://github.com/dbt-labs/dbt-adapters/blob/733b5f766e91a0ef65896fab29d3bc52e11a3104/dbt-athena/src/dbt/include/athena/macros/materializations/models/table/create_table_as.sql#L47-L49

with a **CORTAS**:
```jinja
{%- if table_type == 'iceberg' -%}
{%- set spark_ctas -%}
create or replace table {{ relation.schema | replace('\"', '`') }}.{{ relation.identifier | replace('\"', '`') }}
```

Convert the exception:
https://github.com/dbt-labs/dbt-adapters/blob/733b5f766e91a0ef65896fab29d3bc52e11a3104/dbt-athena/src/dbt/include/athena/macros/materializations/models/table/create_table_as.sql#L105-L111

Into a warning:
```jinja
{%- if materialized == 'table' and ( 'unique' not in s3_data_naming or external_location is not none) -%}
{%- set warning_unique_location_iceberg -%}
We recommend using a unique naming strategy for Iceberg tables
to benefit from the RENAME feature and minimize downtime.
{%- endset -%}
{% do exceptions.warn(warning_unique_location_iceberg) %}
{%- endif -%}
```

🟢 **Doing multiple materializations of the table works just fine, no conflict about an existing table, performance is acceptable to us.**

❓ **QUESTION**
Can I submit a PR accordingly?