Hi I wonder doesn't anyone know how to rename the raw table name before the transformation? My situation is - I tried to pull the official dbt model to my local environment and this is the name of those models tap_adwords.base.adwords_accounts tap_adwords.base.adwords_ad_groups tap_adwords.base.adwords_campaigns etc.. After extracted all the data into my database, the table names are accounts, ad_goups and campaigns which means I can't refer the model if a define a SQL file in my transform/model folder:

with cam as (

    SELECT *
    FROM {{ref('adwords_ads')}}  -- This can find the model, but not the table in the DB..

),

Does anyone know what I should do? The only solution that I can think of is changing the raw table name..

so if you do

dbt ls

the model

adwords_ads

exists in the list right? if you make a test model

select * from {{ ref('adwords_accounts`) }}

Whats the error the you get?

yes I used

meltano invoke dbt ls --resource-type=model

# Return
my_meltano_project.tap_adwords
tap_adwords.transform.adwords_account_stats_per_day
tap_adwords.transform.adwords_account_stats_per_day_segments
tap_adwords.transform.adwords_account_stats_per_month
tap_adwords.transform.adwords_account_stats_per_month_segments
tap_adwords.transform.adwords_account_stats_per_week
tap_adwords.base.adwords_accounts
tap_adwords.base.adwords_ad_groups
tap_adwords.base.adwords_ad_report
tap_adwords.transform.adwords_ad_stats_per_day
tap_adwords.transform.adwords_ad_stats_per_month
tap_adwords.transform.adwords_ad_stats_per_week
tap_adwords.transform.adwords_adgroup_stats_per_day
tap_adwords.transform.adwords_adgroup_stats_per_day_segments
tap_adwords.transform.adwords_adgroup_stats_per_month
tap_adwords.transform.adwords_adgroup_stats_per_month_segments
tap_adwords.transform.adwords_adgroup_stats_per_week
tap_adwords.base.adwords_ads
tap_adwords.transform.adwords_campaign_stats_per_day
tap_adwords.transform.adwords_campaign_stats_per_day_segments
tap_adwords.transform.adwords_campaign_stats_per_month
tap_adwords.transform.adwords_campaign_stats_per_month_segments
tap_adwords.transform.adwords_campaign_stats_per_week
tap_adwords.base.adwords_campaigns
....

I got this message when I make a test model as you said.

Runtime Error in model tap_adwords (models/tap_adwords.sql)
  com.facebook.presto.v217.sql.analyzer.SemanticException: line 8:10: Table 638835835266.MY_DATA_BASE.adwords_ads does not exist

Maybe I'm thinking about this wrong but it sounds like the model you made was Filename: tap_adwords.sql

select * from {{ ref('adwords_ads`) }}

You ran the command?

meltano invoke dbt -m adwords_ads

I'd assume even running with the models needed for adwords_ads to be built fails so?

meltano invoke dbt -m +adwords_ads

So the target_relation for the model

adwords_ads

isn't pointing to the

tap_adwords.base.adwords_ads

table as you'd expect? Hmm I vaugely remember something like this when I ran through using a dbt model ages ago.

@sai_tai did you setup the schema (

MY_DATA_BASE

it seems) in your

dbt_projecy.yml

:

models:
  tap_adwords:
    vars:
      schema: 'MY_DATA_BASE'

@edgar_ramirez_mondragon I have this setting in my

dbt_project.yml

models:
  my_meltano_project: null
  tap_adwords: {}
  tap_google_analytics: {}
  tap_facebook: {}
vars: 
  tap_adwords:
    schema: '{{ env_var(''DBT_SOURCE_SCHEMA'') }}'
  tap_google_analytics:
    schema: '{{ env_var(''DBT_SOURCE_SCHEMA'') }}'
  tap_facebook:
    schema: '{{ env_var(''DBT_SOURCE_SCHEMA'') }}'

Oh you didn't use DBT to create the base tables?

Do you know what I should do if I go for option 1? I just tried to remove the whole database and let Meltano do its jobs, but once again, the table names are still like accounts, ad_groups, ads and campaigns. If those tables are created by dbt, it should look something like adwords_account, adwords_ad_groups and adwords_campaigns, right? i triggered the process via this command:

meltano --log-level=debug elt tap-adwords target-athena --transform=run --job_id=adwords-to-athena

The table names aren't really what matter (dbt refers to them as an alias name" ) what matters is that when you

ref

your model, The relation (https://docs.getdbt.com/reference/dbt-classes#relation) needs to return the right table as that's what gets inserted into your sql. When you let DBT build the tables, does the test model we talk about work?

meltano invoke dbt -m +adwords_ads

I just tried to run this command and it returns

meltano invoke dbt run -m adwords_ads
Running with dbt=0.19.1
Found 80 models, 0 tests, 0 snapshots, 0 analyses, 147 macros, 0 operations, 0 seed files, 0 sources, 0 exposures

16:45:54 | Concurrency: 4 threads (target='athena')
16:45:54 | 
16:45:54 | 1 of 1 START table model MY_DB.adwords_ads.................... [RUN]
16:45:55 | 1 of 1 ERROR creating table model MY_DB.adwords_ads........... [ERROR in 0.53s]
16:45:55 | 
16:45:55 | Finished running 1 table model in 6.06s.

Completed with 1 error and 0 warnings:

Runtime Error in model adwords_ads (models/base/adwords_ads.sql)
  An error occurred (InvalidRequestException) when calling the StartQueryExecution operation: line 20:9: identifiers must not contain ':'

Yeah

Runtime Error in model adwords_ads (models/base/adwords_ads.sql)
  An error occurred (InvalidRequestException) when calling the StartQueryExecution operation: line 20:9: identifiers must not contain ':'

Is a new one! Looks to me that https://gitlab.com/meltano/dbt-tap-adwords/-/blob/master/models/base/adwords_ads.sql#L12 isn't valid for all SQL adapters. Double colon is commonnly available in a lot of DBs / DWs but not all. I have to get back to my normal stuff lunch is over. I hope for the best I'll watch over this and try to send something later to help! From this point it depends on what you're after. If it's just a prototype I'd look into setting up a sources file https://docs.getdbt.com/docs/building-a-dbt-project/using-sources If it's to get this working, I'd copy the files from https://gitlab.com/meltano/dbt-tap-adwords/-/tree/master the models directory and build the models that you care about yourself tweaking them for your db. All of this advice could be moot as I'm not a super expert in this stuff either, just have some experience in it 😄

ah you're right @visch. That looks like snowflake's sql flavor

@visch Thank you for your help! I finally made it!!! I can create a view in my Athena DB. Thank you very much for your help! I still got 2 questions here. That would be great if someone can answer that.

The code just isn't updated. I"m not sure how or wheen that would have changed. In tap-adwords it's https://gitlab.com/meltano/tap-adwords/-/blob/master/tap_adwords/schemas/campaigns.json#L150 correctly. Not sure how that model would have ever worked unless something was translating it.