Firebolt defaults to treating leading or standalone unescaped single quotes as a text wrapper and will look for the column delimiter character following it. If it does not find it, a
Cannot parse input error is thrown.
Here is an example of valid and invalid uses of single quotes in a CSV file, using the default settings:
1000,o'reilly parts is ok,1
2000,'this is ok',1
3000,\'this is ok,1
4000,'this' throws an error,1
5000,'and this throws an error,1
If the data being ingested includes single quotes that are not intended to be text wrappers, the table should be defined to not treat them as having special meaning. This can be done using the CSV Types options when creating the external table. For example, this will allow all the rows in the example to be read without an error:
CREATE EXTERNAL TABLE IF NOT EXISTS ex_single_quote_types (
id INT NOT NULL,
description TEXT NOT NULL,
count INT NOT NULL
) CREDENTIALS = (AWS_ROLE_ARN = 'xxx')
OBJECT_PATTERN = 'single_quote_example.csv'
TYPE = (CSV SKIP_HEADER_ROWS = 1 ALLOW_SINGLE_QUOTES = FALSE)
URL = 's3://xxx'