https://linen.dev logo
Join Slack
Powered by
After two days trying to parse different files, I ...
# troubleshooting
c
After two days trying to parse different files, I am getting a gut feeling that most of the ecosystem Meltano/Singer/Python is tested just for files containing English characters.
e
Hi @cesar_garcia_saez! That may be true. Let's try to pin down what is failing here. What commands and which plugins are you using? If possible, can you share your 
meltano.yml
with any sensitive info redacted
c
This is my 
meltano.yml
file:
Copy code
version: 1
default_environment: dev
send_anonymous_usage_stats: false
plugins:
 extractors:
 - name: tap-spreadsheets-anywhere
  variant: ets
  pip_url: git+<https://github.com/ets/tap-spreadsheets-anywhere.git>
 loaders:
 - name: target-csv
  variant: hotgluexyz
  pip_url: git+<https://github.com/hotgluexyz/target-csv.git@0.3.3>
environments:
- name: dev
 config:
  plugins:
   extractors:
   - name: tap-spreadsheets-anywhere
    config:
     tables:
     - path: <https://datos.madrid.es/egob/catalogo>
      pattern: 300497-11326516-distritos-municipales-madrid.csv
      name: distritos_madrid
      key_properties:
      - COD_DIS
      format: csv
      delimiter: ;
      quotechar: '"'
      start_date: '2017-05-01T00:00:00Z'
   loaders:
   - name: target-csv
    config:
     delimiter: ;
- name: staging
- name: prod
Running 
meltano invoke tap-spreadsheets-anywhere --discover
returns the schema as expected. However, when I try to write the contents to a csv file using `target-csv`I get the following error:
Copy code
2022-06-17T14:02:28.888121Z [info   ] Traceback (most recent call last): cmd_type=elb consumer=True name=target-csv producer=False stdio=stderr string_id=target-csv
2022-06-17T14:02:28.888548Z [info   ]  File "/Users/cesargarciasaez/dev/meltano-tests/meltano-distritos-local/.meltano/loaders/target-csv/venv/bin/target-csv", line 8, in <module> cmd_type=elb consumer=True name=target-csv producer=False stdio=stderr string_id=target-csv
2022-06-17T14:02:28.889068Z [info   ]   sys.exit(main())       cmd_type=elb consumer=True name=target-csv producer=False stdio=stderr string_id=target-csv
2022-06-17T14:02:28.889418Z [info   ]  File "/Users/cesargarciasaez/dev/meltano-tests/meltano-distritos-local/.meltano/loaders/target-csv/venv/lib/python3.9/site-packages/target_csv.py", line 158, in main cmd_type=elb consumer=True name=target-csv producer=False stdio=stderr string_id=target-csv
2022-06-17T14:02:28.889716Z [info   ]   state = persist_messages(  cmd_type=elb consumer=True name=target-csv producer=False stdio=stderr string_id=target-csv
2022-06-17T14:02:28.890183Z [info   ]  File "/Users/cesargarciasaez/dev/meltano-tests/meltano-distritos-local/.meltano/loaders/target-csv/venv/lib/python3.9/site-packages/target_csv.py", line 102, in persist_messages cmd_type=elb consumer=True name=target-csv producer=False stdio=stderr string_id=target-csv
2022-06-17T14:02:28.890489Z [info   ]   writer.writerow(flattened_record) cmd_type=elb consumer=True name=target-csv producer=False stdio=stderr string_id=target-csv
2022-06-17T14:02:28.890750Z [info   ]  File "/Users/cesargarciasaez/opt/anaconda3/lib/python3.9/csv.py", line 154, in writerow cmd_type=elb consumer=True name=target-csv producer=False stdio=stderr string_id=target-csv
2022-06-17T14:02:28.891365Z [info   ]   return self.writer.writerow(self._dict_to_list(rowdict)) cmd_type=elb consumer=True name=target-csv producer=False stdio=stderr string_id=target-csv
2022-06-17T14:02:28.891757Z [info   ]  File "/Users/cesargarciasaez/opt/anaconda3/lib/python3.9/encodings/utf_8_sig.py", line 37, in encode cmd_type=elb consumer=True name=target-csv producer=False stdio=stderr string_id=target-csv
2022-06-17T14:02:28.892061Z [info   ]   return codecs.utf_8_encode(input, self.errors)[0] cmd_type=elb consumer=True name=target-csv producer=False stdio=stderr string_id=target-csv
2022-06-17T14:02:28.892296Z [info   ] UnicodeEncodeError: 'utf-8' codec can't encode character '\udced' in position 48: surrogates not allowed cmd_type=elb consumer=True name=target-csv producer=False stdio=stderr string_id=target-csv
2022-06-17T14:02:28.892545Z [info   ] INFO Wrote 21 records for stream "distritos_madrid". cmd_type=elb consumer=False name=tap-spreadsheets-anywhere producer=True stdio=stderr string_id=tap-spreadsheets-anywhere
2022-06-17T14:02:28.925860Z [error  ] Loader failed
I have modified the target-csv code, adding explicitly 
utf-8-sig
instead of plain utf-8 encoding, but result is the same. Modified code:
Copy code
91             with open(filename, "a", encoding="utf-8-sig") as csvfile:                                                                                                                 
  92                 writer = csv.DictWriter(                                                                                                                                               
  93                     csvfile,                                                                                                                                                           
  94                     headers[o["stream"]],                                                                                                                                              
  95                     extrasaction="ignore",                                                                                                                                             
  96                     delimiter=delimiter,                                                                                                                                               
  97                     quotechar=quotechar,                                                                                                                                               
  98                 )                                                                                                                                                                      
  99                 if file_is_empty:                                                                                                                                                      
 100                     writer.writeheader()                                                                                                                                               
 101                                                                                                                                                                                        
 102                 writer.writerow(flattened_record)
I have explored two options: • File is encoded as UTF-8-with-BOM (as autodetected by VSCode), so there are non visible characters besides de comma separated values that are visible. This should be solved by using utf-8-sig as encoding • Exporter is having a hard time writing all accentuated characters like Í, Á, etc.
e
Ok, can you try breaking up the EL in two steps: • 
meltano invoke tap-spreadsheets-anywhere > singer.jsonl
• 
cat singer.jsonl | meltano invoke target-csv
c
I am getting the error on the second step:
Copy code
cat singer.jsonl | meltano invoke target-csv
2022-06-17T15:36:44.138808Z [info     ] Environment 'dev' is active
INFO Sending version information to <http://singer.io|singer.io>. To disable sending anonymous usage data, set the config parameter "disable_collection" to true
Traceback (most recent call last):
  File "/Users/cesargarciasaez/dev/meltano-tests/meltano-distritos-local/.meltano/loaders/target-csv/venv/bin/target-csv", line 8, in <module>
    sys.exit(main())
  File "/Users/cesargarciasaez/dev/meltano-tests/meltano-distritos-local/.meltano/loaders/target-csv/venv/lib/python3.9/site-packages/target_csv.py", line 158, in main
    state = persist_messages(
  File "/Users/cesargarciasaez/dev/meltano-tests/meltano-distritos-local/.meltano/loaders/target-csv/venv/lib/python3.9/site-packages/target_csv.py", line 102, in persist_messages
    writer.writerow(flattened_record)
  File "/Users/cesargarciasaez/opt/anaconda3/lib/python3.9/csv.py", line 154, in writerow
    return self.writer.writerow(self._dict_to_list(rowdict))
  File "/Users/cesargarciasaez/opt/anaconda3/lib/python3.9/encodings/utf_8_sig.py", line 37, in encode
    return codecs.utf_8_encode(input, self.errors)[0]
UnicodeEncodeError: 'utf-8' codec can't encode character '\udced' in position 48: surrogates not allowed
e
Ok, and does 
singer.jsonl
look ok or are there some unrecognized characters in there?
c
Opening the jsonl file in VSCode, it is detected as UTF-8 encoded. Checking for the contents, I can see the following line:
Copy code
{
  "type": "RECORD",
  "stream": "distritos_madrid",
  "record": {
    "objectid": 17,
    "shape_leng": "13396,81708",
    "shape_area": "9175481,878",
    "cod_dis": 5,
    "cod_dis_tx": 5,
    "distri_may": "CHAMARTIN",
    "nombre": "Chamart\udcedn",
    "distri_mt": "CHAMART\udccdN",
    "_smart_source_bucket": "<https://datos.madrid.es/egob/catalogo>",
    "_smart_source_file": "300497-11326516-distritos-municipales-madrid.csv",
    "_smart_source_lineno": 6
  }
}
So, the \udced character in the error output is an accented i. In the next field, there is a CAPS version encoded as \udccd.
According to https://unicodelookup.com/, the í should be 0xED and Í should be 0xCD
e
Could it work if you set the 
PYTHONIOENCODING
env var? https://docs.python.org/3/using/cmdline.html#envvar-PYTHONIOENCODING
I think that should make 
sys.stdout
use the right encoding
c
I will try to set it up as 'utf-8-sig', however I am not quite optimistic about it
I have been following up the calls of tap-spreadsheets-anywhere. In the end it's calling csv.dictreader. So I searched for info about passing the encoding to the function call. In this article at Stack Overflow, it suggests:
Copy code
For me, the key was not in manipulating the csv DictReader args, but the file opener itself. This did the trick:
with open(filepath, mode="r", encoding="utf-8-sig") as csv_file:
    csv_reader = csv.DictReader(csv_file)
No special class required. Now I can open files either with or without BOM without crashing.
Tap-spreadsheets-anywhere is not opening the file per se, but calling a reader using 
get_streamreader
function. This function uses smart_open (that also supports encoding parameter as the regular open). So I modified my call like this:
Copy code
def get_streamreader(uri, universal_newlines=True,newline='',open_mode='r'):
    streamreader = smart_open.open(uri, open_mode, newline=newline, errors='surrogateescape', encoding='utf-8-sig')
    if not universal_newlines and isinstance(streamreader, StreamReader):
        return monkey_patch_streamreader(streamreader)
    return streamreader
But file is encoded in the same way as before. I get the same output file. I will try the 
PYTHONIOENCODING
setting instead.
I set the 
PYTHONIOENCODING
to 
utf-8-sig
but I am still getting the same characters in the output file.
Wait a second! I can't believe it, I am trying to download the file from scratch and I am getting 
URLError: <urlopen error [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: certificate has expired (_ssl.c:1129)>
. It's 12:01 PM here.
I am thinking about testing from URL vs local file to see if it makes any difference too.
BTW, I was testing if pandas could read it properly using pd.read_csv when I got the certificate error. I am gonna try again at my MAc laptop (I was testing the last steps on my Windows machine)
e
Sounds good, I know how painful encodings can be.
c
In this computer, I am getting the same error after setting the 
PYTHONIOENCODING
to 
utf-8-sig
. There is one interesting discovery I made during this last run. I added --log-level=debug and got this in the output:
Copy code
2022-06-17T22:25:56.696833Z [debug  ] Encoding detection: ascii is most likely the one.
Sidestepping meltano for a second, I just tried getting the file via curl: 
curl -L <https://datos.madrid.es/egob/catalogo/300497-11326516-distritos-municipales-madrid.csv> --output ./input/Distritos_via_curl.csv
. Then I tried opening the file using VSCode. I clicked on file encoding and then VSCode suggested that the encoding for the file is Windows 1252.
Opening the file while enforcing utf-8-sig via 
PYTHONIOENCODING
I get random interrogation symbols instead of accented characters
But if I switch to Windows 1252, I get the proper output
I need to split my issue on: what happens on Windows vs what happens on Mac. Python supposedly inherits encoding from OS (unless passed as an enviromental variable). So in Windows, I might be introducing the issue by forcing ‘utf-8’ encoding but on Mac, I am not parsing the file with the proper encoding to detect all these characters, given it is not the default option.
Setting `PYTHONIOENCODING`to `cp1252`doesn’t seem to produce any effect. Singer.jsonl still displays \u characters
I think I got the root cause (but not the solution). Let’s recap a few steps back, to the get_streamdeck on `tap-spreadsheets-anywhere`:
Copy code
streamreader = smart_open.open(uri, open_mode, newline=newline, errors='surrogateescape', encoding='utf-8-sig')
There is a clear 
errors='surrogateescape'
argument right there! So whenever 
tap-spreadsheets-anywhere
finds an error reading a character, it replaces it with a surrogate escape. Error manifested in the second part of the elt, when composing the csv file:
Copy code
UnicodeEncodeError: 'utf-8' codec can't encode character '\udced' in position 48: surrogates not allowed cmd_type=elb consumer=True name=target-csv
So 
taps-spreadsheets-anywhere
is silently replacing the characters it can’t read by their surrogate counterparts. And `target-csv`doesn’t allow surrogate characters, as it doesn’t know how to encode them. Based on this article (https://vstinner.github.io/pep-383.html ) this is exactly what we are seeing here: “The surrogateescape encoding is based on *Markus Kuhn*’s idea that he called UTF-8b. Undecodable bytes in range 0x80-0xff are mapped as Unicode surrogate characters: range U+DC80 - U+DCFF.” So, character í (represented in unicode as 0xED is mapped to surrogate U+DCED),Í character 0xCD is mapped to U+DCCD
e
I think another issue is 
json.dumps
trying to force ascii output: Normal execution. Notice how it turns utf-8 
María
into 
Mar\u00eda
Copy code
$ tap-spreadsheets-anywhere -c config.json 2> log.txt
{"type": "SCHEMA", "stream": "data", "schema": {"properties": {"id": {"type": ["null", "integer"]}, "name": {"type": ["null", "string"]}, "_smart_source_bucket": {"type": "string"}, "_smart_source_file": {"type": "string"}, "_smart_source_lineno": {"type": "integer"}}, "selected": true, "type": "object"}, "key_properties": ["id"]}
{"type": "RECORD", "stream": "data", "record": {"id": 1, "name": "Mar\u00eda", "_smart_source_bucket": "<file://data>", "_smart_source_file": "1.csv", "_smart_source_lineno": 2}}
{"type": "STATE", "value": {"data": {"modified_since": "2022-06-17T22:54:34.295314+00:00"}}}
Patching 
json.dumps(..., _ensure_ascii_=False)
Copy code
$ tap-spreadsheets-anywhere -c config.json 2> log.txt
{"type": "SCHEMA", "stream": "data", "schema": {"properties": {"id": {"type": ["null", "integer"]}, "name": {"type": ["null", "string"]}, "_smart_source_bucket": {"type": "string"}, "_smart_source_file": {"type": "string"}, "_smart_source_lineno": {"type": "integer"}}, "selected": true, "type": "object"}, "key_properties": ["id"]}
{"type": "RECORD", "stream": "data", "record": {"id": 1, "name": "María", "_smart_source_bucket": "<file://data>", "_smart_source_file": "1.csv", "_smart_source_lineno": 2}}
{"type": "STATE", "value": {"data": {"modified_since": "2022-06-17T22:54:34.295314+00:00"}}}
c
Changing the call of streamreader like this:
Copy code
streamreader = smart_open.open(
        uri, open_mode, newline=newline, errors="surrogateescape", encoding="cp1252")
forces 
tap-spreadsheets-anywhere
to use cp1252. Instead of surrogate characters (\udced) you get \u00ed in the output file:
Copy code
{
  "type": "RECORD",
  "stream": "distritos_madrid",
  "record": {
    "objectid": 17,
    "shape_leng": "13396,81708",
    "shape_area": "9175481,878",
    "cod_dis": 5,
    "cod_dis_tx": 5,
    "distri_may": "CHAMARTIN",
    "nombre": "Chamartín",
    "distri_mt": "CHAMARTÍN",
    "_smart_source_bucket": "<https://datos.madrid.es/egob/catalogo>",
    "_smart_source_file": "300497-11326516-distritos-municipales-madrid.csv",
    "_smart_source_lineno": 6
  }
}
Then it tried running 
cat singer.jsonl | meltano invoke target-csv
and it worked! This is the output of the ETL for that particular line:
Copy code
17;13396,81708;9175481,878;5;5;CHAMARTIN;Chamartín;CHAMARTÍN;<https://datos.madrid.es/egob/catalogo;300497-11326516-distritos-municipales-madrid.csv;6>
e
Ah nice! So this might be fixed with a PR for 
tap-spreadsheets-anywhere
?
c
I wish it was a simple PR to 
tap-spreadsheets-anywhere
but it looks a bit more complex than that to me. I was trying to download a csv file with non-English characters and it failed to read those properly. The default error level seems to be: in case of errors, use surrogates. So, the tap doesn’t fail or display errors, but data can’t be handled by targets down the line as it is using surrogates. One option would be to expose the encoding as a config parameter. It could be configured in a source by source basis but… you need to detect the errors somehow, or they will end up in your datawarehouse down the line. Other option could be selecting other error mode, so that the import fails if it can’t read or decode characters, so no data errors are introduced into the pipeline. I am also wondering why 
target-csv
doesn’t accept surrogates, given they should be trivially converted to non-surrogates, replacing the \udcXX by \u00xx and then to original encoding.
I also want to verify if this process works under Windows, given it is using cp1252 as the default encoding for OS.
Time to tackle the magic number errors on the other thread!
Going back to the two options, I am pondering creating a PR exposing the encoding parameter in a per tap-spreadsheets-anywhere/table basis. On the other hand, reading the open() function documentation I got the following information: • 
'surrogateescape'
will represent any incorrect bytes as low surrogate code units ranging from U+DC80 to U+DCFF. These surrogate code units will then be turned back into the same bytes when the 
surrogateescape
error handler is used when writing data. This is useful for processing files in an unknown encoding.
So adding the error=
surrogateescape
to 
target-csv
writter shoud revert the encoding in the input, leaving the data in the original format. Do you know what is the best-practice in this regard? Do taps use surrogateescape by default? Should it be suggested for targets to pass it this by default? I assume there is a lot of work/agreement done in this regard in the Singer community.
c
This does not look like a bug to me. Your webserver responds with 
Content-Type: text/plain; charset=UTF-8
and then sends a body that does NOT use UTF-8 encoding. This is not a behaviour that is possible to cater for in a generic way. Also, it would seem that 
tap-spreadsheets-anywhere
is probably assuming that all input is 
utf-8
encoded, which doesn't sound like a good assumption to make, but is probably a trade-off that is worth to make.
c
Hey @christoph thanks for your insights. Did you get the `Content-Type: text/plain; charset=UTF-8`from meltano/singer debug logs or from any other source (like Chrome/Firefox developer tools, curl, etc?
c
curl command:
Copy code
» curl -s -o /dev/stdout --dump-header /dev/stderr <https://geoportal.madrid.es/fsdescargas/IDEAM_WBGEOPORTAL/LIMITES_ADMINISTRATIVOS/Distritos/Distritos.csv> | file -
HTTP/1.1 200 OK
Date: Sun, 19 Jun 2022 22:40:34 GMT
Strict-Transport-Security: max-age=63072000; includeSubdomains;
Last-Modified: Fri, 19 Jun 2020 11:03:37 GMT
Accept-Ranges: bytes
Content-Length: 1507
Content-Type: text/plain; charset=UTF-8
Connection: close
Set-Cookie: TS01d9dd6e=01e90f9b8bf58914fdb827515e1d94aed9f2f598edc3a7b4c01d6bc75acf1c5fdfd2abaa370ad896e07271c299d098d6bd7567eafd; Path=/; Domain=.<http://geoportal.madrid.es|geoportal.madrid.es>

/dev/stdin: ISO-8859 text, with CRLF line terminators
c
Note to self: Using cchardet library to detect the encoding of input file and passing it to the tap invocation could be very nice! https://dev.to/bowmanjd/character-encodings-and-detection-with-python-chardet-and-cchardet-4hj7
To make things even more surreal… They also offer a plain txt alternative too. The plain text file is a csv and no encoding information appears on the headers this time. So it works when requesting txt file instead of csv. Surreal!
Copy code
curl -s -o /dev/stdout --dump-header /dev/stderr <https://datos.madrid.es/egob/catalogo/300497-11326519-distritos-municipales-madrid.txt> | file -
HTTP/2 302 
x-frame-options: SAMEORIGIN
x-xss-protection: 1; mode=block
x-content-type-options: nosniff
location: <https://geoportal.madrid.es/fsdescargas/IDEAM_WBGEOPORTAL/LIMITES_ADMINISTRATIVOS/Distritos/Distritos.txt>
content-encoding: gzip
x-ua-compatible: IE=8
content-length: 20
content-type: text/plain
date: Wed, 22 Jun 2022 14:50:11 GMT
set-cookie: ROUTEID=.app04; path=/
strict-transport-security: max-age=15768000
c
no encoding information appears
@cesar_garcia_saez You will need to either specify 
-L
(follow redirects) on curl, or use the final URL path to the file directly. You are getting a 
HTTP 302
response with a 
Location
header, which has no body. Hence 
file
will read an empty document from 
stdin
Copy code
curl -s --dump-header /dev/stderr <https://geoportal.madrid.es/fsdescargas/IDEAM_WBGEOPORTAL/LIMITES_ADMINISTRATIVOS/Distritos/Distritos.txt> | file -

HTTP/1.1 200 OK
Date: Wed, 22 Jun 2022 21:42:01 GMT
Strict-Transport-Security: max-age=63072000; includeSubdomains;
Last-Modified: Thu, 11 Jun 2020 11:23:19 GMT
Accept-Ranges: bytes
Content-Length: 2117
Content-Type: text/plain; charset=UTF-8
Connection: close
Set-Cookie: TS01d9dd6e=01e90f9b8b1ab667e3203e7b1cee9ae5109b238a9f4efdee50568e1130396a5d325fa4a06d3d32f8b5769b797742f63984a7deed45; Path=/; Domain=.<http://geoportal.madrid.es|geoportal.madrid.es>

/dev/stdin: Unicode text, UTF-8 text, with CRLF line terminators
Looks like you have a winner with the 
.txt
file which has UTF-8 encoding and is semicolon separated.
c
@christoph thanks for the tips! I use to call curl with -L but after so many tests… mistakes happen 🙂
Open in Slack
PreviousNext
Powered by