https://linen.dev logo
Join Slack
Powered by
Hey team, how are you? I'm doing a simple pipelin...
# troubleshooting
g
Hey team, how are you? I'm doing a simple pipeline locally using the MessageBird extractor and the Snowflake Loader (transferwise) and I'm having some issues in the moment of copying the staged table file, that is being created correctly in Snowflake, to the target table. Looks like there's no enclosing of the strings or files by the tap at the moment of staging the file on Snowflake. I've tried to configure different separators and also added the enclosed char to the Snowflake file format, but without success. Is it possible see what the tap-messagebird is using to enclose the strings? Would you have any suggestion how can I avoid this issue and correctly ingest the files? I'm going to put in the thread the file format configured in Snowflake, the error message and the 
meltano.yml
File Format:
Copy code
CREATE OR REPLACE FILE FORMAT MELTANO.MELTANO_CSV
 
TYPE = 'CSV'
FIELD_DELIMITER = ',' -- Also tested with '|'
SKIP_HEADER = 0
FIELD_OPTIONALLY_ENCLOSED_BY = '"'
ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE;
Error message:
Copy code
File "/Users/gabrielmarinho/Documents/GitHub/meltano/inventa/.meltano/loaders/target-snowflake/venv/lib/python3.9/site-packages/snowflake/connector/errors.py", line 210, in default_errorhandler cmd_type=elb consumer=True name=target-snowflake producer=False stdio=stderr string_id=target-snowflake
2023-01-10T18:31:42.232913Z [info     ]     raise error_class(         cmd_type=elb consumer=True name=target-snowflake producer=False stdio=stderr string_id=target-snowflake
2023-01-10T18:31:42.232981Z [info     ] snowflake.connector.errors.ProgrammingError: 100065 (22000): Found character 'i' instead of field delimiter ',' cmd_type=elb consumer=True name=target-snowflake producer=False stdio=stderr string_id=target-snowflake
2023-01-10T18:31:42.233050Z [info     ]   File '@CONVERSATION/batch_07bbm1me.csv.gz', line 1, character 6 cmd_type=elb consumer=True name=target-snowflake producer=False stdio=stderr string_id=target-snowflake
2023-01-10T18:31:42.233118Z [info     ]   Row 1, column "CONVERSATION"["CHANNELS":1] cmd_type=elb consumer=True name=target-snowflake producer=False stdio=stderr string_id=target-snowflake
2023-01-10T18:31:42.734552Z [info     ] 2023-01-10 15:31:42,733 INFO METRIC: {"metric_type": "timer", "metric": "http_request_duration", "value": 0.438749, "tags": {"stream": "conversation", "endpoint": "/conversations", "http_status_code": 200, "status": "succeeded", "url": "/v1/conversations?offset=101100&status=all&limit=20"}} cmd_type=elb consumer=False name=tap-messagebird producer=True stdio=stderr string_id=tap-messagebird
2023-01-10T18:31:43.043209Z [error    ] Loader failed
`meltano.yml`:
Copy code
version: 1
default_environment: prod
project_id: c48df217-cf5a-4999-8028-8ecf58a65a50
environments:
- name: dev
  config:
    plugins:
      extractors:
      - name: tap-messagebird
        config:
          start_date: '2023-01-09'
        select:
        - conversation.*
      loaders:
      - name: target-snowflake
        config:
          account: fn49409.us-east-2.aws
          dbname: RAW
          user: meltano
          warehouse: LOADING
          role: LOADER
          file_format: RAW.MELTANO.MELTANO_CSV
          default_target_schema: MELTANO
- name: staging
- name: prod
  config:
    plugins:
      extractors:
      - name: tap-messagebird
        config:
          start_date: '2023-01-09'
        select:
        - conversation.*
      loaders:
      - name: target-snowflake
        config:
          account: fn49409.us-east-2.aws
          dbname: RAW
          user: meltano
          warehouse: LOADING
          role: LOADER
          file_format: RAW.MELTANO.MELTANO_CSV
          default_target_schema: MELTANO
plugins:
  extractors:
  - name: tap-messagebird
    variant: meltanolabs
    pip_url: tap-messagebird
  loaders:
  - name: target-snowflake
    variant: transferwise
    pip_url: pipelinewise-target-snowflake
s
Try FIELD_OPTIONALLY_ENCLOSED_BY = \" ESCAPE \\
Copy code
File '@CONVERSATION/batch_07bbm1me.csv.gz', line 1, character 6
can you open the file and take a look?
g
Hi @slava_lenskyy, It does look like a issue in escaping the first 
"
. This is how the first line of the file starts like:
Copy code
"[{\"id\": \"36096b13af44454f8b76f019f6f53390\", \"name\": \"\", \"platformId\": \"events\"}]"
I tried the configuration you suggested and worked perfectly. Thank you so much for the tip!
Open in Slack
PreviousNext
Powered by