Re: Question related to escaping in create table as for CSV

[Abhishek Girish <ab...@gmail.com>]

Can you try updating your csv format plugin [1]:


"csv": {
      "type": "text",
      "extensions": [
        "csv"
      ],
      "quote": "\u0000",
      "extractHeader": true,
      "delimiter": ","

    }


[1]
https://drill.apache.org/docs/text-files-csv-tsv-psv/#using-quotation-marks

On Mon, Aug 29, 2016 at 5:48 AM, Sandeep Dugar <sd...@gainsight.com> wrote:

> *Input csv (filename: input.csv):*
>
> "Id","customer_id__c","first_name__c","last_name__c","
> email__c","address__c","city__c","state__c","zipcode__c","careOf__c"
> "a1T410000004uG6EAI",1.0,"George","Washington","gwashington@usa.gov","3200
> Mt Vernon Hwy","Mount Vernon","VA",22121.0,"!@Satya__ , ()"
>
>
>
> *create table as query:*
>
> create table <<storageplugin>>.<<workspace>>.`output.csv` as select * from
> <<storageplugin>>.<<workspace>>.`input.csv`
>
>
> *Resultant output (i.e. output.csv):*
>
> Id,customer_id__c,first_name__c,last_name__c,email__c,
> address__c,city__c,state__c,zipcode__c,careOf__c
> a1T410000004uG6EAI,1.0,George,Washington,gwashington@usa.gov,3200 Mt
> Vernon
> Hwy,Mount Vernon,VA,22121.0,!@Satya__ , ()
>
>
> Problem:
> - The create table as output csv does not have columns separated by quotes
> - The next query on this output fails. For eg. in the below output for
> column careOf__c the data is not complete because of the comma
>
> +---------------------+----------------+---------------+
> |         Id          | first_name__c  |   careOf__c   |
> +---------------------+----------------+---------------+
> | a1T410000004uG6EAI  | George         | !@Satya__ \\  |
> +---------------------+----------------+---------------+
>
>
>
> *Two questions:*
> 1. Is there a way to specify "create table as" output csv to have each
> column separated by double quotes?
> 2. If there is no way we can specify point 1, is there a way to escape
> "comma" in the create table output?
>