[Feature] Add application_name in Redshift connector via profile key

### 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

Redshift, the same as Postgres, has a connection parameter called `application_name`, which is visible via the `STL_CONNECTION_LOG` and `SYS_CONNECTION_LOG` system views. Currently it uses the default set by the connector which defaults to the module that called the connection function, which is currently `dbt.adapters.base.connections`, at least for dbt `1.10.20` which is our current version.

In our company, we have several dbt projects being invoked from multiple places and even though we can control lots of elements from the query comments, profiles, users, etc.; controlling connections would also be very useful and give us an extra layer of control.

This feature has already been implemented for Postgres here: https://github.com/dbt-labs/dbt-core/issues/885

And from these comments I can see it's been implemented similarly for other engines as well: https://github.com/dbt-labs/dbt-core/pull/3182#discussion_r599746515

### Describe alternatives you've considered

The simplest alternative I've found is to set a project-wide pre_hook:

```
models:
project:
+pre-hook:
- "SET APPLICATION_NAME = 'some_identifier';"
```

Downside from this workaround is that only model connections set the application_name, whereas internal dbt connections do not.

### Who will this benefit?

dbt-redshift users only, mostly beneffiting managers of dbt implementations and Redshift managers.

### Are you interested in contributing this feature?

Yes, I've already based on the code for the Postgres implementation and will create a PR soon after this issue

### Anything else?

_No response_