[jira] [Commented] (DRILL-6840) Exporting to CSV using !set csvquotecharacter '"' not working in latest stable or snapshot versions

["Arina Ielchiieva (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DRILL-6840?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16681591#comment-16681591 ] 

Arina Ielchiieva commented on DRILL-6840:
-----------------------------------------

[~mrsarm] these options are related to SqlLine not to Drill and thus only take affect on SqlLine output.
Drill does not save data with specified delimiters. The workaround discussed on stackoverflow was meant to save data using SqlLine means.
For example: in SqlLine you set output format and quote character.
{noformat}
 !set outputformat csv
 !set csvquotecharacter '"'
{noformat}
Then you start recording the output:
{noformat}
!record "/tmp/output.csv"
{noformat}
Next query the data:
{noformat}
select * from sys.version;
{noformat}
Stop recording:
{noformat}
!record
{noformat}

As the result you'll have the /tmp/output.csv file with the following content:
{noformat}
Saving all output to "/tmp/output.csv". Enter "record" with no arguments to stop it.
"version","commit_id","commit_message","commit_time","build_email","build_time"
"1.15.0-SNAPSHOT","b406367c2846405ba525083ce0d61822453cec7b","added back reader config","09.11.2018 @ 14:05:50 EET","arina.yelchiyeva@gmail.com","09.11.2018 @ 14:27:42 EET"
1 row selected (1.389 seconds)
{noformat}

If you remove first and last lines, you'll have csv with double-quotes.

> Exporting to CSV using !set csvquotecharacter '"' not working in latest stable or snapshot versions
> ---------------------------------------------------------------------------------------------------
>
>                 Key: DRILL-6840
>                 URL: https://issues.apache.org/jira/browse/DRILL-6840
>             Project: Apache Drill
>          Issue Type: Bug
>          Components: Storage - Text & CSV
>    Affects Versions: 1.14.0
>         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
>            Reporter: Mariano Ruiz
>            Priority: Minor
>              Labels: csv, csvparser, export
>
> Using latest stable version and latest SNAPSHOT version, when I export to a CSV file the result of a query, the text fields aren't enclosed with double quotes as specified.
> Steps:
> {code:java}
> 0: jdbc:drill:zk=local> USE dfs.tmp;
> +-------+--------------------------------------+
> |  ok   |               summary                |
> +-------+--------------------------------------+
> | true  | Default schema changed to [dfs.tmp]  |
> +-------+--------------------------------------+
> 1 row selected (0.126 seconds)
> 0: jdbc:drill:zk=local> ALTER SESSION SET `store.format`='csv';
> +-------+------------------------+
> |  ok   |        summary         |
> +-------+------------------------+
> | true  | store.format updated.  |
> +-------+------------------------+
> 1 row selected (0.117 seconds)
> 0: jdbc:drill:zk=local> !set csvquotecharacter '"'
> 0: jdbc:drill:zk=local> CREATE TABLE dfs.tmp.prods_without_brand AS SELECT * FROM dfs.`/tmp/prods.csv` WHERE brand = '';
> +-----------+----------------------------+
> | Fragment  | Number of records written  |
> +-----------+----------------------------+
> | 0_0       | 112                        |
> +-----------+----------------------------+
> 1 row selected (0.198 seconds)
> 0: jdbc:drill:zk=local> 
> {code}
> The CSV output doesn't have any field enclosed with *{color:red}"{color}*, even those that have values with the *{color:red},{color}* character, so the CSV is broken.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)