dbt-labs / dbt-labs/redshift

Too many quotes in generated command

Open
#51 0 comments 0 reactions 0 assignees View on GitHub
bug
Dominant language
PLpgSQL
Stars
105
Forks
39
PR merge metrics
No merged PRs in 30d

Description

I [originally](https://github.com/dbt-labs/dbt-external-tables/issues/127) submitted this issue to `dbt_external_tables`. Perhaps it belongs in `dbt-core` instead.

### Describe the bug

When I run `dbt run-operation stage_external_sources`, I get the error message:

```
Encountered an error while running operation: Database Error cross-database reference to database "sources" is not supported
```

because dbt tries running this command:

```sql
drop table if exists "sources"."src_pendo"."src_accounts" cascade
```

when it [should be](https://repost.aws/questions/QUt7MfW15TRVOfYWJrD00Ijw/drop-table-not-working-because-illegal-characters-in-name):

```sql
drop table if exists "sources.src_pendo.src_accounts" cascade
```

And, indeed, when I run the latter SQL command in Redshift Query Editor on my warehouse there's no error, whereas when I run the former SQL command I get the same error.

### Steps to reproduce

I'm running all CLI commands in PowerShell.

My `models/staging/pendo/src_pendo.yml` file (following [this example](https://stackoverflow.com/a/62996073/8605348)) looks like this (one column for brevity):

```yml
version: 2

sources:
- name: s3_pendo
database: sources
schema: src_pendo
loader: S3
loaded_at_field: _sdc_batched_at
tables:
- name: src_accounts
external:
location:
row_format: serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
table_properties: "('skip.header.line.count'='1')"
stored_as: parquet
columns:
- name: account_id
data_type: varchar
tests:
- not_null
- unique
```

`` contains CSV files.

### Expected results

If I'm understanding correctly, the `dbt run-operation stage_external_sources` macro will create the external schema and table defined in `src_pendo.yml` in Redshift Spectrum, then populate the table with all data in the CSV files in ``. Let me know if this isn't the use case.

### Actual results

See bug description.

### Screenshots and log output

Full (PowerShell) output after running the macro:

![image](https://user-images.githubusercontent.com/9023041/149154845-a0353ce8-2f51-4a98-8cd5-06fb982ff18c.png)

### System information
**The contents of your `packages.yml` file:**

```yml
packages:
- package: dbt-labs/dbt_external_tables
version: 0.8.0
```

**Which database are you using dbt with?**
- [x] redshift
- [ ] snowflake
- [ ] other (specify: ____________)

**The output of `dbt --version`:**
```
installed version: 1.0.1
latest version: 1.0.1

Up to date!

Plugins:
- postgres: 1.0.1
- redshift: 1.0.0
```

**The operating system you're using:** Windows 10

**The output of `python --version`:** `Python 3.9.6`

### Additional context

If this is truly a bug then I'll try submitting a PR.

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.