Hi, I’m using the mysql tap and I’m having some tr...
# plugins-general
m
Hi, I’m using the mysql tap and I’m having some trouble using environment variables in the
select
statement. I need to do this because my local database has a different name than my stage and prod database. Here’s an example of what I’m trying to do:
Copy code
plugins:
  extractors:
  - name: tap-mysql
    variant: transferwise
    pip_url: pipelinewise-tap-mysql
    config:
      host: host
      user: root
      database: ${DB_NAME}
    select:
    - ${DB_NAME}-table_name.id
    - ${DB_NAME}-table_name.email
    metadata:
      ${DB_NAME}-table_name:
        replication-method: FULL_TABLE
That doesn’t work. It doesn’t recognize the columns I’m trying to select. I’ll add what does work and what I’d ideally like to do in the thread.
This works:
Copy code
plugins:
  extractors:
  - name: tap-mysql
    variant: transferwise
    pip_url: pipelinewise-tap-mysql
    config:
      host: host
      user: root
      database: ${DB_NAME}
    select:
    - db_name-table_name.id
    - db_name-table_name.email
    metadata:
      db_name-table_name:
        replication-method: FULL_TABLE
This would be ideal:
Copy code
table_names:
  table_name: db_name-table_name
plugins:
  extractors:
  - name: tap-mysql
    variant: transferwise
    pip_url: pipelinewise-tap-mysql
    config:
      host: host
      user: root
      database: ${DB_NAME}
    select:
    - talbe_name.id
    - table_name.email
    metadata:
      table_name:
        replication-method: FULL_TABLE
d
@matt_cooley How about the following, leveraging wildcards:
Copy code
select:
    - "*-users.id"
    - "*-users.email"
Env var substitution isn't working because Meltano currently only traverses subproperties of
object
config values, not
array
values as you're using here.
m
I have not tried that. Since Schema = DB in MySQL there shouldn’t be any issues with it pointing at the wrong “schema” either
d
@matt_cooley Yeah, the filter is only applied on streams already selected by the database, so we can be broad and accept these tables in any schema
Trying env var substitution is clever though, and I think it should be supported. Would you like to file a bug about https://meltano.slack.com/archives/C013EKWA2Q1/p1608070305251900?thread_ts=1608070036.251400&cid=C013EKWA2Q1?
m
Happily
Unfortunately the wildcard is pulling from other schemas, which seems weird since I’m already selecting the db
d
That is odd
You also see tables from across schemas/databases listed when you run
meltano select tap-mysql --list --all
?
m
yes
d
No idea why that would be necessary on top of
database
, though
m
OK that did it! Thanks and I agree that it’s weird that there’s so much duplicate config. I’ll get that bug report in there too.
d
Sounds good!
@matt_cooley Thanks for filing https://gitlab.com/meltano/meltano/-/issues/2481, I've already left some comments 🙂