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

[Abhishek Girish]

Can you please check if you are receiving emails from the Drill user
mailing list? I'm forwarding my previous reply for your reference.

On Fri, Sep 2, 2016 at 4:10 PM, Abhishek Girish 
 wrote:

> Can you try updating your csv format plugin [1]:
>
>
> "csv": {
>   "type": "text",
>   "extensions": [
> "csv"
>   ],
>   "quote": "\u",
>   "extractHeader": true,
>   "delimiter": ","
>
> }
>
>
> [1] https://drill.apache.org/docs/text-files-csv-tsv-psv/#
> using-quotation-marks
>

On Sun, Sep 18, 2016 at 11:51 PM, Sandeep Dugar 
wrote:

> Hi,
>
> Please let me know if there is any setting or workaround for this.
>
> -
> Thanks,
> Sandeep
>
> > On Aug 29, 2016, at 6:18 PM, Sandeep Dugar  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"
> > "a1T41004uG6EAI",1.0,"George","Washington","gwashing...@usa.gov
> ","3200 Mt Vernon Hwy","Mount
> Vernon","VA",22121.0,"!@Satya__ , ()"
> >
> >
> >
> > create table as query:
> >
> > create table <>.<>.`output.csv` as select *
> from <>.<>.`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
> > a1T41004uG6EAI,1.0,George,Washington,gwashing...@usa.gov  gwashing...@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   |
> > +-++---+
> > | a1T41004uG6EAI  | 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?
>
>

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

[Sandeep Dugar]

Hi,

Please let me know if there is any setting or workaround for this.

-
Thanks,
Sandeep

> On Aug 29, 2016, at 6:18 PM, Sandeep Dugar  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"
> "a1T41004uG6EAI",1.0,"George","Washington","gwashing...@usa.gov 
> ","3200 Mt Vernon Hwy","Mount 
> Vernon","VA",22121.0,"!@Satya__ , ()"
> 
> 
> 
> create table as query:
> 
> create table <>.<>.`output.csv` as select * from 
> <>.<>.`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
> a1T41004uG6EAI,1.0,George,Washington,gwashing...@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   |
> +-++---+
> | a1T41004uG6EAI  | 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?

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

[Abhishek Girish]

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


"csv": {
  "type": "text",
  "extensions": [
"csv"
  ],
  "quote": "\u",
  "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  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"
> "a1T41004uG6EAI",1.0,"George","Washington","gwashing...@usa.gov","3200
> Mt Vernon Hwy","Mount Vernon","VA",22121.0,"!@Satya__ , ()"
>
>
>
> *create table as query:*
>
> create table <>.<>.`output.csv` as select * from
> <>.<>.`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
> a1T41004uG6EAI,1.0,George,Washington,gwashing...@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   |
> +-++---+
> | a1T41004uG6EAI  | 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?
>