[DRILL-6842] Export to CSV using CREATE TABLE AS (CTAS) wrong parsed

• Type: Improvement
• Status: Resolved
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 1.14.0
• Fix Version/s: 1.17.0
• Component/s: Storage - Text & CSV, Storage - Writer
• Labels: csv export

Environment

• Tested with latest version Apache Drill 1.14.0, and building the latest version from master (Github repo), commit ad61c6bc1dd24994e50fe7dfed043d5e57dba8f9 at Nov 5, 2018.
• Linux x64, Ubuntu 16.04
• OpenJDK Runtime Environment (build 1.8.0_171-8u171-b11-0ubuntu0.17.10.1-b11)
• Apache Maven 3.5.0

• Assignee: Unassigned
• Reporter: Mariano Ruiz
• Created: 10/Nov/18 15:41
• Updated: 22/Oct/19 10:20
• Resolved: 22/Oct/19 10:20

Description

When you export to a CSV using CTAS the result of a query, most of the time the generated file is OK, but if you have in the results text columns with "," characters, the resulting CSV file is broken, because does not enclose the cells with commas inside with the " character.

Steps to reproduce the bug:

Lets say you have the following table in some source of data, maybe a CSV file too:

/tmp/input.csv

product_ean,product_name,product_brand
12345678900,IPhone X,Apple
99999911100,"Samsung S9, Black",Samsung
11111223456,Smartwatch XY,Some Brand

Note that the second row of data, in the column "product_name", it has a value with a comma inside (Samsung S9, Black), so all the cell value is enclosed with " characters, while the rest of the column cells aren't, despite they could be enclosed too.

So if you query this file, Drill will interpret correctly the file and does not interpret that comma inside the cell as a separator like the rest of the commas in the file:

0: jdbc:drill:zk=local> SELECT * FROM dfs.`/tmp/input.csv`;
+--------------+--------------------+----------------+
| product_ean  |    product_name    | product_brand  |
+--------------+--------------------+----------------+
| 12345678900  | IPhone X           | Apple          |
| 99999911100  | Samsung S9, Black  | Samsung        |
| 11111223456  | Smartwatch XY      | Some Brand     |
+--------------+--------------------+----------------+
3 rows selected (1.874 seconds)

But now, if you want to query the file and export the result as CSV using the CTAS feature, using the following steps:

0: jdbc:drill:zk=local> USE dfs.tmp;
+-------+--------------------------------------+
|  ok   |               summary                |
+-------+--------------------------------------+
| true  | Default schema changed to [dfs.tmp]  |
+-------+--------------------------------------+
1 row selected (0.13 seconds)
0: jdbc:drill:zk=local> ALTER SESSION SET `store.format`='csv';
+-------+------------------------+
|  ok   |        summary         |
+-------+------------------------+
| true  | store.format updated.  |
+-------+------------------------+
1 row selected (0.094 seconds)
0: jdbc:drill:zk=local> CREATE TABLE dfs.tmp.my_output AS SELECT * FROM dfs.`/tmp/input.csv`;
+-----------+----------------------------+
| Fragment  | Number of records written  |
+-----------+----------------------------+
| 0_0       | 3                          |
+-----------+----------------------------+
1 row selected (0.453 seconds)

The output file is this:

/tmp/my_output/0_0_0.csv

product_ean,product_name,product_brand
12345678900,IPhone X,Apple
99999911100,Samsung S9, Black,Samsung
11111223456,Smartwatch XY,Some Brand

The text Samsung S9, Black in the cell is not quoted, so any CSV interpreter like an office tool, a Java/Python/... library will interpret it as two cell instead of one. Even Apache Drill will interpret it wrong:

0: jdbc:drill:zk=local> SELECT * FROM dfs.`/tmp/my_output/0_0_0.csv`;
+--------------+----------------+----------------+
| product_ean  |  product_name  | product_brand  |
+--------------+----------------+----------------+
| 12345678900  | IPhone X       | Apple          |
| 99999911100  | Samsung S9     |  Black         |
| 11111223456  | Smartwatch XY  | Some Brand     |
+--------------+----------------+----------------+
3 rows selected (0.175 seconds)

Note that the ending part _ Black_ was interpreted as a following cell, and the real following cell is not showed, but it's not an error in the Drill interpreter, it's an error of how Drill exported the result that now in the last query was used as input.

Here is how the file is interpreted by LibreOffice Calc:

Issue Links

• relates to: DRILL-6096 Provide mechanisms to specify field delimiters and quoted text for TextRecordWriter