https://linen.dev logo
Join Slack
Powered by
Hi team, just wanted to know how does a target-sno...
# troubleshooting
a
Hi team, just wanted to know how does a target-snowflake knows about the structure of the table which it needs to create in target? Is it needed to be defined somewhere?
v
https://hub.meltano.com/singer/spec#:~:text=a%20single%2Dline.-,Schemas,-Schema%20messages%20define The singer spec defines what's called a schema that's sent with the records to the target!
a
@visch Do i need to manually write them for each schema or it can be generated by a command?
please help @visch I am unable to figure out how to get the target-snowflake working 😞
@edgar_ramirez_mondragon @pat_nadolny Any help will be much appreciated
p
Hey @ashish_kumar - thats part of the singer spec. Did you check out the link Derek sent? It might be helpful to start from the top also to have the full context https://hub.meltano.com/singer/spec . The TLDR is that the tap creates schema messages based on the data that its extracting/sending and then the target receives the schema message and uses it when loading data to the destination. So for target snowflake the schema message is received and is translated into DDL to create the table before inserting rows
a
Hi @pat_nadolny I have gone through the specs and updated my config as expected. But I am getting error as 
2022-05-04T14:27:35.011678Z [info     ] snowflake.connector.errors.ProgrammingError: 000904 (42000): SQL compilation error: error line 1 at position 70 cmd_type=loader job_id=mongo-to-snowflake name=target-snowflake run_id=5c3be2a5-2523-4b6b-b09e-916c78bb2e36 stdio=stderr
2022-05-04T14:27:35.011884Z [info     ] invalid identifier '_ID'       cmd_type=loader job_id=mongo-to-snowflake name=target-snowflake run_id=5c3be2a5-2523-4b6b-b09e-916c78bb2e36 stdio=stderr
p
@ashish_kumar can you share your meltano.yml if you remove any sensitive info? Also a few lines of the output from a 
meltano invoke
of your tap would be helpful too
a
Copy code
version: 1
default_environment: dev
project_id: 0df72b50-ba8a-4169-884b-bafd34832785
plugins:
  extractors:
  - name: tap-mongodb
    variant: singer-io
    pip_url: tap-mongodb
    config:
      host: ************
      port: 27017
      user: f*****************
      database: admin
      replica_set: atlas-f6gbhi-shard-0
  loaders:
  - name: target-jsonl
    variant: andyh1203
    pip_url: target-jsonl
    config:
      do_timestamp_file: true
  - name: target-snowflake
    variant: transferwise
    pip_url: pipelinewise-target-snowflake
    config:
        account: *************
        dbname: ANALYSIS
        user: ASHISH_DEV
        warehouse: DEV_TEST_WH
        file_format: ANALYSIS.MELTANO_ASHISH.FILE_FORMAT_CSV
        role: DBT_DEVELOPER
        default_target_schema: MELTANO_ASHISH
        add_metadata_columns: false
        logging_level: DEBUG
        batch_size_rows: 1000000
        schema_mapping:
          public:
            traget_schema: MELTANO_ASHISH
        primary_key_required: false
      

schedules:
- name: mongodb-to-jsonl
  extractor: tap-mongodb
  loader: target-jsonl
  transform: skip
  interval: '@once'
  start_date: 2022-04-30 09:55:11.751603
environments:
- name: dev
  config:
    plugins:
      extractors:
      - name: tap-mongodb
        config:
          ssl: true
          filter_schemas: public
          default_replication_method: FULL_TABLE
        metadata:
          '*':
            replication-method: FULL_TABLE
        select:
        - koalas-applications-test-applications
       
    
      loaders:
      - name: target-jsonl
        config:
          destination_path: origination_elt/output
      - name: target-snowflake
        config:
          account: ********
          dbname: ANALYSIS
          user: ASHISH_DEV
          warehouse: DEV_TEST_WH
          file_format: ANALYSIS.MELTANO_ASHISH.FILE_FORMAT_CSV
          role: DBT_DEVELOPER
          default_target_schema: MELTANO_ASHISH
          add_metadata_columns: false
          logging_level: DEBUG
          batch_size_rows: 1000000
          primary_key_required: false
- name: staging
- name: prod
```INFO Connected to MongoDB host: ***************, version: 4.4.13 {"type": "STATE", "value": {"bookmarks": {"koalas-applications-test-applications": {"last_replication_method": "FULL_TABLE"}}, "currently_syncing": "koalas-applications-test-applications"}} {"type": "SCHEMA", "stream": "applications", "schema": {"type": "object"}, "key_properties": ["_id"]} INFO Starting full table sync for koalas-applications-test-applications {"type": "STATE", "value": {"bookmarks": {"koalas-applications-test-applications": {"last_replication_method": "FULL_TABLE", "version": 1651675972890}}, "currently_syncing": "koalas-applications-test-applications"}} {"type": "ACTIVATE_VERSION", "stream": "applications", "version": 1651675972890} INFO Querying koalas-applications-test-applications with: Find Parameters: {'$lte': ObjectId('61b85cec5ff23b3b382d468e')} {"type": "SCHEMA", "stream": "applications", "schema": {"type": "object", "properties": {"loan": {"anyOf": [{}]}, "existingCharges": {"anyOf": [{}]}, "applicants": {"anyOf": [{"type": "array", "items": {"anyOf": [{"type": "object", "properties": {"names": {"anyOf": [{}]}, "residentialAddressHistory": {"anyOf": [{"type": "array", "items": {"anyOf": [{"type": "object", "properties": {"moveInDate": {"anyOf": [{"type": "string", "format": "date-time"}, {}]}, "moveOutDate": {"anyOf": [{"type": "string", "format": "date-time"}, {}]}}}, {}]}}, {}]}, "dateOfBirth": {"anyOf": [{"type": "string", "format": "date-time"}, {}]}, "jobs": {"anyOf": [{"type": "array", "items": {"anyOf": [{"type": "object", "properties": {"startDate": {"anyOf": [{"type": "string", "format": "date-time"}, {}]}, "endDate": {"anyOf": [{"type": "string", "format": "date-time"}, {}]}, "employer": {"anyOf": [{}]}, "incomes": {"anyOf": [{}]}}}, {}]}}, {}]}, "nationalities": {"anyOf": [{}]}, "contactDetails": {"anyOf": [{}]}, "nonJobIncomes": {"anyOf": [{}]}}}, {}]}}, {}]}, "expenditures": {"anyOf": [{}]}, "createdAt": {"anyOf": [{"type": "string", "format": "date-time"}, {}]}, "updatedAt": {"anyOf": [{"type": "string", "format": "date-time"}, {}]}}}, "key_properties": ["_id"]} {"type": "RECORD", "stream": "applications", "record": {"_id": "61b85cec5ff23b3b382d468e", "loan": {"type": "fixed", "loanRequirements": {"repaymentType": "capital", "repaymentFrequency": "monthly", "term": "P25Y", "initialTermType": "home-improvements", "initialTermDuration": "P5Y"}, "securityProperty": {"value": 100000}, "productId": "TBC", "purpose": "homeImprovement"}, "existingCharges": [{"lien": "yes", "balance": 0, "repaymentFrequency": "monthly", "repaymentType": "capital", "repaymentAmount": 700, "lender": "santander", "outstandingTerm": 20, "isSpecialistScheme": false, "currentInterestRate": "3.1", "remainingInitialTerm": "P3Y", "initialTermType": "fixed", "isInInitialTerm": true}], "applicants": [{"exPinId": "TBC", "type": "individual", "names": [{"title": "ms", "firstName": "Jessica", "middleName": "", "lastName": "Rabbit", "type": "primary"}], "residentialAddressHistory": [{"moveInDate": "1959-12-31T183000.000000Z", "moveOutDate": "1959-12-31T183000.000000Z", "type": "detatched"}], "employmentType": "selfEmployed", "dateOfBirth": "1959-12-31T183000.000000Z", "jobs": [{"type": "selfEmployed", "title": "accountant", "startDate": "1959-12-31T183000.000000Z", "endDate": "1959-12-31T183000.000000Z", "contractType": "fullTime", "employer": {"name": "DisneyLand", "email": "j.rabbit@disney.com", "phoneNumber": "07123456789", "industry": "financialServices", "sicCode": ["7011"]}, "incomes": [{"type": "selfEmployed", "amount": 5000, "frequency": "monthly", "taxable": true}]}], "plannedRetirementAge": 65, "nationalities": ["American"], "rightToStay": "yes", "residentialStatus": "own", "maritalStatus": "married", "contactDetails": {"email": "jess@yahoo.com", "phoneNumber": "07123456789"}, "nonJobIncomes": []}], "expenditures": {"creditCommitments": [], "recurrentExpenditures": [], "salaryD…
p
I havent used this tap but it does seem a little weird that 
{"type": "SCHEMA", "stream": "applications", "schema": {"type": "object"}, "key_properties": ["_id"]}
is being sent as the first schema message for that stream. The second one looks right. Maybe its a bug with the tap? I found this https://github.com/singer-io/tap-mongodb/issues/48 and someone said they resolved by using https://github.com/transferwise/pipelinewise-tap-mongodb instead. @ptd and @nigel_ainscoe I saw you both were using tap-mongodb, did you see any schema issues? what variant is working for you all?
p
I have no experience pulling data from mongo, no
p
@ptd oops sorry must have misread 😄
n
That's an interesting spot. I'm working with @ashish_kumar on this but I've been off this project and on another one for the last week. Just jumping back on so this is definitely something to check out. Reading the docs now.
Open in Slack
PreviousNext
Powered by