Unexpected preview or query results for virtualized tables over flat files in Watson Query

In Watson Query, if you virtualize a flat file that delimits strings, a preview of the table or queries against the table might not work as expected if the delimiter is not specified.

Symptoms

If you virtualize a table over a file that contains data that is similar to the following example, a data preview or queries against the table might not work as expected if you don't use the string delimiter specification (quoteChar) to indicate that string values are delimited by double quotation marks.

"spain","madrid"
"france","paris"

Query results include the double quotation marks that are used to delimit the string data, as shown in the following example.

NAME                                               CITY
-------------------------------------------------- --------------------------------------------------
"spain"                                            "madrid"
"france"                                           "paris"

Resolving the problem

You can resolve the problem by doing one of the following:

  • Specify the string delimiter (quoteChar) when you preview the data or virtualize the table. For more information, see the best practices for data sources in object storage.
  • Remove the string delimiters before you virtualize the table.
  • Modify your queries to recognize that string values are delimited.

For example, to return all rows in which the CITY column contains the value paris, run the following query.

select * from t1 where city = '"paris"'