You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by "Knapp, Michael" <Mi...@capitalone.com> on 2017/05/15 20:20:55 UTC

creating tables in S3

Hi,

So I have a directory full of pipe separated value files.  I was hoping to convert these to parquet using Drill’s CTAS command. I tried this:

create table s3.tmp.`my_table` (x, y) as SELECT COLUMNS[0] x, COLUMNS[1] y FROM s3.`path/to/my.tbl`

after a little time, I get this error:

org.apache.drill.common.exceptions.UserRemoteException: VALIDATION ERROR: Schema [s3.tmp] is not valid with respect to either root schema or current default schema. Current default schema: No default schema selected

I am able to query data from that S3 file.

This is my S3 plugin configuration:

{
  "type": "file",
  "enabled": true,
  "connection": "s3a://my_bucket",
  "config": null,
  "workspaces": {
    "root": {
      "location": "/",
      "writable": false,
      "defaultInputFormat": null
    },
    "tmp": {
      "location": "drill-tmp",
      "writable": true,
      "defaultInputFormat": null
    }
  },


I have created the directory “drill-tmp” in my bucket, and it is empty.

The file is a pipe separated value, so it does not have a schema.  Does anybody know what I’m doing wrong or how to get this to work?

Michael Knapp
________________________________________________________

The information contained in this e-mail is confidential and/or proprietary to Capital One and/or its affiliates and may only be used solely in performance of work or services for Capital One. The information transmitted herewith is intended only for use by the individual or entity to which it is addressed. If the reader of this message is not the intended recipient, you are hereby notified that any review, retransmission, dissemination, distribution, copying or other use of, or taking of any action in reliance upon this information is strictly prohibited. If you have received this communication in error, please contact the sender and delete the material from your computer.

Re: creating tables in S3

Posted by Padma Penumarthy <pp...@mapr.com>.
I am wondering if location information in plugin configuration  should be

 "location": “/drill-tmp” (instead of “location":”drill-tmp”)


Thanks,
Padma


> On May 15, 2017, at 1:34 PM, Charles Givre <cg...@gmail.com> wrote:
> 
> Hi Michael,
> A few questions:
> 1.  Does the origin query work?
> 
> SELECT COLUMNS[0] x, COLUMNS[1] y FROM s3.`path/to/my.tbl`
> 
> One thing that jumps out at me is that I think "columns" has to be lower
> case.
> 
> 2. Did you set up the .tbl extension to read pipe separated files?  That
> also could be causing problems if not.
> 
> -- C
> 
> 
> On Mon, May 15, 2017 at 4:20 PM, Knapp, Michael <
> Michael.Knapp@capitalone.com> wrote:
> 
>> Hi,
>> 
>> So I have a directory full of pipe separated value files.  I was hoping to
>> convert these to parquet using Drill’s CTAS command. I tried this:
>> 
>> create table s3.tmp.`my_table` (x, y) as SELECT COLUMNS[0] x, COLUMNS[1] y
>> FROM s3.`path/to/my.tbl`
>> 
>> after a little time, I get this error:
>> 
>> org.apache.drill.common.exceptions.UserRemoteException: VALIDATION ERROR:
>> Schema [s3.tmp] is not valid with respect to either root schema or current
>> default schema. Current default schema: No default schema selected
>> 
>> I am able to query data from that S3 file.
>> 
>> This is my S3 plugin configuration:
>> 
>> {
>>  "type": "file",
>>  "enabled": true,
>>  "connection": "s3a://my_bucket",
>>  "config": null,
>>  "workspaces": {
>>    "root": {
>>      "location": "/",
>>      "writable": false,
>>      "defaultInputFormat": null
>>    },
>>    "tmp": {
>>      "location": "drill-tmp",
>>      "writable": true,
>>      "defaultInputFormat": null
>>    }
>>  },
>> 
>> 
>> I have created the directory “drill-tmp” in my bucket, and it is empty.
>> 
>> The file is a pipe separated value, so it does not have a schema.  Does
>> anybody know what I’m doing wrong or how to get this to work?
>> 
>> Michael Knapp
>> ________________________________________________________
>> 
>> The information contained in this e-mail is confidential and/or
>> proprietary to Capital One and/or its affiliates and may only be used
>> solely in performance of work or services for Capital One. The information
>> transmitted herewith is intended only for use by the individual or entity
>> to which it is addressed. If the reader of this message is not the intended
>> recipient, you are hereby notified that any review, retransmission,
>> dissemination, distribution, copying or other use of, or taking of any
>> action in reliance upon this information is strictly prohibited. If you
>> have received this communication in error, please contact the sender and
>> delete the material from your computer.
>>

Re: creating tables in S3

Posted by Charles Givre <cg...@gmail.com>.
Hi Michael,
A few questions:
1.  Does the origin query work?

SELECT COLUMNS[0] x, COLUMNS[1] y FROM s3.`path/to/my.tbl`

One thing that jumps out at me is that I think "columns" has to be lower
case.

2. Did you set up the .tbl extension to read pipe separated files?  That
also could be causing problems if not.

-- C


On Mon, May 15, 2017 at 4:20 PM, Knapp, Michael <
Michael.Knapp@capitalone.com> wrote:

> Hi,
>
> So I have a directory full of pipe separated value files.  I was hoping to
> convert these to parquet using Drill’s CTAS command. I tried this:
>
> create table s3.tmp.`my_table` (x, y) as SELECT COLUMNS[0] x, COLUMNS[1] y
> FROM s3.`path/to/my.tbl`
>
> after a little time, I get this error:
>
> org.apache.drill.common.exceptions.UserRemoteException: VALIDATION ERROR:
> Schema [s3.tmp] is not valid with respect to either root schema or current
> default schema. Current default schema: No default schema selected
>
> I am able to query data from that S3 file.
>
> This is my S3 plugin configuration:
>
> {
>   "type": "file",
>   "enabled": true,
>   "connection": "s3a://my_bucket",
>   "config": null,
>   "workspaces": {
>     "root": {
>       "location": "/",
>       "writable": false,
>       "defaultInputFormat": null
>     },
>     "tmp": {
>       "location": "drill-tmp",
>       "writable": true,
>       "defaultInputFormat": null
>     }
>   },
>
>
> I have created the directory “drill-tmp” in my bucket, and it is empty.
>
> The file is a pipe separated value, so it does not have a schema.  Does
> anybody know what I’m doing wrong or how to get this to work?
>
> Michael Knapp
> ________________________________________________________
>
> The information contained in this e-mail is confidential and/or
> proprietary to Capital One and/or its affiliates and may only be used
> solely in performance of work or services for Capital One. The information
> transmitted herewith is intended only for use by the individual or entity
> to which it is addressed. If the reader of this message is not the intended
> recipient, you are hereby notified that any review, retransmission,
> dissemination, distribution, copying or other use of, or taking of any
> action in reliance upon this information is strictly prohibited. If you
> have received this communication in error, please contact the sender and
> delete the material from your computer.
>