1

I'm trying to copy a large data set from Postgresql to ScyllaDB, which is supposed to be compatible with Cassandra.

This is what I'm trying:

psql <db_name> -c "COPY (SELECT row_number() OVER () as id, * FROM ds.my_data_set LIMIT 20) TO stdout WITH (FORMAT csv, HEADER, DELIMITER ';');" \
 | \
CQLSH_HOST=172.17.0.3 cqlsh -e 'COPY test.mytable (id, "Ist Einpöster", [....]) FROM STDIN WITH DELIMITER = $$;$$ AND HEADER = TRUE;'

I get an obscure error without a stack trace:

:1:'ascii' codec can't decode byte 0xc3 in position 9: ordinal not in range(128)

My data, and column names, including the ones already in the created table in ScyllaDB, contain values with German text. It's not ASCII, but I haven't found anywhere to set the encoding, and everywhere I looked it seemed to be using utf-8 already. I tried this as well, and saw in the vicinity of line 1135 that, and changed it in my local cqlsh (using vim $(which cqlsh)), but it had no effect.

I'm using cqlsh 5.0.1, installed using pip. (weirdly it was pip install cqlsh==5.0.4)

I also tried the cqlsh from the docker image that I used to install ScyllaDB, and it has the exact same error.

<Update>
As suggested, I piped the data to a file:

psql <db_name> -c "COPY (SELECT row_number() OVER (), * FROM ds.my_data_set ds) TO stdout WITH (FORMAT csv, HEADER);" | head -n 1 > test.csv

I thinned it down to the first row (CSV header). Piping it to cqlsh made it cry with the same error. Then, using python3.5 interactive shell, I did this:

>>> with open('test.csv', 'rb') as fp:
...   data = fp.read()
>>> data
b'row_number,..... Ist Einp\xc3\xb6ster ........`

So there we are, \xc3 in the flesh. Is it UTF-8?

>>> data.decode('utf-8')
'row_number,....... Ist Einpöster ........`

Yes, it's utf-8. So how does the error happen?

>>> data.decode('ascii')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
UnicodeDecodeError: 'ascii' codec can't decode byte 0xc3 in position 336: ordinal not in range(128)

Same error text, so it's probably Python as well, but without a stack trace, I have no idea where this is happening, and default encodings are utf-8. I tried overriding the default with utf-8 but nothing changed. Still, somewhere, something is trying to decode a stream using ASCII.

This is the locale on the server/client:

LANG=
LANGUAGE=
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=en_US.UTF-8

Someone on Slack suggested this answer UnicodeDecodeError: 'ascii' codec can't decode byte 0xd1 in position 2: ordinal not in range(128) Once I added the last 2 lines in cqlsh.py at the beginning, it got past the decoding issue, but the same column was reported as invalid with another error:

:1:Invalid column name Ist Einpöster

side note:

I lost interest in this test at this point, and I'm just trying to not have an unanswered question, so please excuse the wait time. As I was trying it out as an analytical engine, coupled with Spark, as a data source for Tableau, I found "better" alternatives, like Vertica and ClickHouse. "Better" because both of them have limitations.

</Update>

How can I complete this import?

Alex
  • 14,338
  • 5
  • 41
  • 59
  • 1. Can you share the schema from Postgresql (inc. the data types) vs. the schema you created on Scylla / Cassandra (inc. the data types). Sometimes some alterations are required. 2. Do you have any NULL values in your data? – TomerSan Nov 28 '18 at 10:35
  • @TomerSan apparently that doesn't matter. I managed to replicate the error now with stuff as simple as this `echo "not important what you write here" | cqlsh --encoding='utf-8' -e 'COPY test.whatever (id, "ö") FROM STDIN WITH DELIMITER = $$;$$ AND HEADER = TRUE;'` Only the keyspace should exist. The rest is failing before any structure validation. This is obviously the query parser, since the position of the invalid character is the position of the `ö` in the column name. – Alex Nov 29 '18 at 13:07

4 Answers4

2

What was it?

The query passed in as an argument, contained the column list, which contained that column with a non-ASCII character. At some point, cqlsh parsed those as ascii and not utf-8, which lead to this error.

How it was fixed?

First attempt was to add these 2 lines in cqlsh:

reload(sys)
sys.setdefaultencoding('utf-8')

but that still made the script unable to work with that column.

Second attempt was to simply pass the query from a file. If you can't, know that bash supports process substitution, so instead of this:

cqlsh -f path/to/query.cql

you can have

cqlsh -f <(echo "COPY .... FROM STDIN;")

And it's all great, except that it doesn't work either. cqlsh understands stdin as "interactive", from a prompt, and not piped in. The result is that it doesn't import anything. One could just create a file, and load it from the file, but that's an extra step that might take minutes or hours, depending on the data size.
Thankfully, POSIX systems have these virtual files like '/dev/stdin', so the above command is equivalent to this:

cqlsh -f <(echo "COPY .... FROM '/dev/stdin';")

except that cqlsh now thinks that you actually have a file, and it reads it like a file, so you can pipe your data and be happy.

This would probably work, but for some reason I got the last kick:

cqlsh.sql:2:Failed to import 15 rows: InvalidRequest - Error from server: code=2200 [Invalid query] message="Batch too large", will retry later, attempt 4 of 5

I think it's funny that 15 rows are too much for a distributed storage engine. And it's likely that it's again some limitation from the engine related to unicode and just a wrong error message. Or I'm wrong. Nevertheless, the initial question was answered, with some BIG help from the guys in Slack.

Alex
  • 14,338
  • 5
  • 41
  • 59
  • 15 rows failed because inside each of them, you had a too large batch, the output is a bit confusing – dyasny Nov 29 '18 at 20:32
0

I don't see that you ever got an answer to this. UTF-8 should be the default.

Did you try --encoding?

Docs: https://docs.scylladb.com/getting-started/cqlsh/

If you didn't get an answer here, would you wish to ask it on our slack channel?

Peter Corless
  • 781
  • 3
  • 12
  • I also did some searching on that error code, 0xc3. That reminded me of something. I wondered if you may be suffering from some data somewhere that's not actually UTF-8, but may actually be Unicode. Something's happening in the encoding/decoding. I read this thread about people running into the same error, though obviously for different reasons. But the answers may help you think through where the error may be. https://stackoverflow.com/questions/24475393/unicodedecodeerror-ascii-codec-cant-decode-byte-0xc3-in-position-23-ordinal – Peter Corless Nov 27 '18 at 19:46
  • I'll try the Slack channel, just out of curiosity. I think this isn't an UTF-8 problem, since it's trying out an ASCII codec according to the error. Python can decode the stream with no problems with UTF-8. – Alex Nov 29 '18 at 12:12
0

I would try to eliminate all the extra complexity you have in there first. Try to dump a few rows into a CSV, and then load it into Scylla using COPY

dyasny
  • 317
  • 3
  • 9
  • Thanks for the suggestion. I doubt that psql has anything to do with it, as we use this method, and piping it to and from hundreds of sources, and never had this issue. But I tried it nevertheless, and updated the question. – Alex Nov 29 '18 at 12:10
  • OK, looks like a python issue to me. Have you tried with a newer `cqlsh`? I suppose you can get the one that ships with cassandra, or just install the latest one via `pip` – dyasny Nov 29 '18 at 16:10
  • I got around that bug eventually. Details in my answer. Thank you for your time and effort. – Alex Nov 29 '18 at 16:13
0

Update: utf8: Print invalid UTF-8 character position

Add new validate_with_error_position function which returns -1 if data is a valid UTF-8 string or otherwise a byte position of first invalid character. The position is added to exception messages of all UTF-8 parsing errors in Scylla.

validate_with_error_position is done in two passes in order to preserve the same performance in common case when the string is valid.

https://github.com/scylladb/scylla/commit/ffd8c8c505b92a71df7e34d5196c7545f11cb12f

Peter Corless
  • 781
  • 3
  • 12