Re: CTAS and save as parquet last column values are shown as null

Filed DRILL-5684 to track the doc issue. On Mon, Jul 24, 2017 at 8:33 AM, Abhishek Girish wrote: > Glad to know that it worked! > > As you are using Drill on Windows, the new line delimiter in text files > can be different

Re: CTAS and save as parquet last column values are shown as null

Glad to know that it worked! As you are using Drill on Windows, the new line delimiter in text files can be different from that on Linux / Mac. We could see \r\n as the lineDelimiter (carriage return & new line) and hence when we set the same in the format plugin, the issue gets resolved. This

Re: CTAS and save as parquet last column values are shown as null

Any best practices guide if any body could share would be great On 24 July 2017 at 14:52, Divya Gehlot wrote: > Thank you so much it worked > > Can you please provide me the pointer to the documentation where updation > for different format type are mentioned . > > As

Re: CTAS and save as parquet last column values are shown as null

Thank you so much it worked Can you please provide me the pointer to the documentation where updation for different format type are mentioned . As I am facing another issue with date type as the data which I receive in csv format has the format of 15/1/2016 when I try to cast or convert to_date

Re: CTAS and save as parquet last column values are shown as null

Can you update your csv format plugin as shown below and retry your query? "csv": { "type": "text", "extensions": [ "csv" ], "lineDelimiter": "\r\n", "extractHeader": true, "delimiter": "," } On Sun, Jul 23, 2017 at 10:37 PM, Divya Gehlot

Re: CTAS and save as parquet last column values are shown as null

0: jdbc:drill:zk=local> select * FROM dfs.`installedsoftwares/ApacheDrill/apache-drill-1.10.0.tar/apache-drill-1.10.0/sample-data/jll/data/mapping/PublicHoliday/PublicHoliday.csv` limit 10 ; +-+ | columns |

Re: CTAS and save as parquet last column values are shown as null

Pasting the result set in text format *Reading parquet file format :* > Day Date Area > Friday 15/1/2016 null > Tuesday 26/1/2016 null > Monday 7/3/2016 null > Friday 25/3/2016 null > Friday 1/4/2016 null > Friday 8/4/2016 null *Reading csv file format * > columns >

Re: CTAS and save as parquet last column values are shown as null

Unfortunately, the attachments / pictures haven't come through. Mailing lists sometimes do not support these. Can you paste as text or share links to it instead? On Sun, Jul 23, 2017 at 9:14 PM, Divya Gehlot wrote: > yes it shows the proper values when I query the csv

Re: CTAS and save as parquet last column values are shown as null

yes it shows the proper values when I query the csv file. CTAS query csv to parquet : Create table `dfs`.`tmp`.`publicholiday.parquet` AS SELECT CASE WHEN `Day` = '' THEN CAST(NULL AS VARCHAR(100)) ELSE CAST(`Day` AS VARCHAR(100)) END AS `Day`, CASE WHEN `Date` = '' THEN CAST(NULL AS VARCHAR(100))

Re: CTAS and save as parquet last column values are shown as null

Can you share a sample row from the CSV and the CTAS query? Also test if a select columns[n] query on the CSV file works as expected [1] ? It could be an issue with delimiters. [1] https://drill.apache.org/docs/querying-plain-text-files/#columns[n]-syntax On Sun, Jul 23, 2017 at 8:44 PM Divya