You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@drill.apache.org by Bob Rumsby <br...@maprtech.com> on 2014/08/11 19:57:00 UTC
Drill terminology issue: "schema"
We talked to Rahul about this last week, and we think that the term *schema*
is overloaded and misleading for several reasons:
1. The meaning of the term, generally speaking, depends on the context.
2. A *schema* usually refers to the type and structure of data in a data
source, but Drill operates on data that is "schema-less" or
"self-describing."
3. We have commands that refer to schemas, workspaces, file systems, and
databases interchangeably.
4. A *schema* in the relational database world is a namespace within a
single physical database. Drill does not use the term in this way.
Below are some specific examples where the use of the term *schema* does
not seem appropriate or accurate. It's a little bit tricky to explain
clearly. Please let me and Bridget know if you think we should try to
change the terminology, and we'll open a Jira.
1. Does it make sense to set a *workspace* or a *database* name with the
USE command and have it be called the "default schema"?
0: jdbc:drill:> use hive.`default`;
+------------+------------+
| ok | summary |
+------------+------------+
| true | Default schema changed to 'hive.default' |
+------------+------------+
1 row selected (0.113 seconds)
2. The SHOW DATABASES command returns a list of "schemas" not databases?
0: jdbc:drill:> show databases;
+-------------+
| SCHEMA_NAME |
+-------------+
| hive.default |
| dfs.default |
....
3. The SHOW SCHEMAS command returns the same list, really a list of
workspaces and databases (workspaces for file systems, databases for Hive
and HBase):
0: jdbc:drill:> show schemas;
+-------------+
| SCHEMA_NAME |
+-------------+
| hive.default |
| dfs.default |
| dfs.root |
| dfs.tmp |
| sys |
| MFS.default |
| MFS.json_click |
| lab.default |
| lab.root |
| lab.views |
| lab.clicks |
| hbase |
| INFORMATION_SCHEMA |
+-------------+
13 rows selected (0.114 seconds)
4. What is a "Drill schema" versus a "non-Drill schema"? This non-Drill
schema appears in the show schemas list.
0: jdbc:drill:> create table mobile as select * from
MFS.json_click.`mobile.json` limit 2;
+------------+------------+
| ok | summary |
+------------+------------+
| false | Error: Current schema is not a Drill schema. Can't create
new relations (tables or views) in non-Drill schemas. |
+------------+------------+
1 row selected (0.086 seconds)
0: jdbc:drill:> show schemas;
+-------------+
| SCHEMA_NAME |
+-------------+
| hive.default |
| dfs.default |
| dfs.root |
| dfs.tmp |
| sys |
| MFS.default |
| MFS.json_click |
....
Re: Drill terminology issue: "schema"
Posted by "M. C. Srivas" <sr...@maprtech.com>.
Don't want to call it a database. The point of Drill is to dissolve the
concept of a database.
thanks & regards,
Srivas.
*---*
*M.C. Srivas, CTO & Co-founder, *MapR Technologies, Inc.
<http://www.mapr.com>
On Mon, Aug 11, 2014 at 9:47 PM, Jacques Nadeau <ja...@apache.org> wrote:
> Regarding (4), agree with Neeraja. Please file JIRA to this effect.
>
> Regarding Keys' comment: workspaces are lightweight by their nature.
> Databases are generally thought of as heavyweight and managed by a dba.
> This is directly in conflict with the vision of workspaces. The purpose of
> workspaces is that all users can ultimately create many workspaces for
> themselves. In many cases, they will hold views, pointers, udfs but
> potentially no data. This behavior is atypical compared to the traditional
> database concept and thus is created as such. This is part of the vision
> of a loosely coupled data system.
>
> Regarding "SCHEMA". You're right that we have overloaded that word to mean
> two entirely different things. The first is a logical collection of
> tables/views. The latter (as in schemaless) is field names and datatypes
> within a complex record. At some level, this is the conflict with bringing
> two worlds together. Database isn't necessarily the right concept for the
> former (see comments above). However, JDBC and some BI tools use this
> concept. At the same time the latter meaning is commonly used when talking
> about things like avro schemas. I'm up for an improved approach that also
> works in both domains if we can come up with one. But let's make sure we
> have good analogs before inventing something new.
>
>
>
>
> On Mon, Aug 11, 2014 at 2:53 PM, Keys Botzum <kb...@maprtech.com> wrote:
>
> > This raises a point I've asked. If a workspace is basically a database,
> why
> > not call it that?
> >
> > Keys
> >
> >
> > On Mon, Aug 11, 2014 at 5:10 PM, Neeraja Rentachintala <
> > nrentachintala@maprtech.com> wrote:
> >
> > > Bob
> > > Thanks for bringing this up.
> > > Below are my thoughts.
> > >
> > > In some of the relational databases such as Oracle/SQL server ,
> 'Schema'
> > > and 'Database' actually are different in that a database can have
> > multiple
> > > schemas (each schema is typically associated with a user). This is not
> > > however a common practice across all the relational databases. For
> > example,
> > > in MySQL , schema and database are just same (refer to
> > >
> >
> http://stackoverflow.com/questions/11618277/difference-between-schema-database-in-mysql
> > > )
> > >
> > > Drill has the similar paradigm as MySQL and I believe the behavior
> above
> > > is expected for 1,2,3 above. Essentially schemas and databases mean
> the
> > > same thing.
> > > For the file system based queries, each workspace that the user sets up
> > > corresponds to a schema/database in Drill for the clients to interact
> > with
> > > it (A workspace is conceptually same as a Hive database or a HBase
> > > namespace i.e essentially it is a group of tables).
> > >
> > > For 4. above, if I understand the issue correctly, the error message
> from
> > > the original thread very misleading.
> > > Drill is a distributed query engine on top of databases and adopts a
> > > de-centralized metadata model.Currently Drill allows creating
> > tables/views
> > > within the file system based schemas. Eventually I see this being
> > extended
> > > to Hive and HBase schemas as well.
> > > The error should probably something like this.
> > > 'This schema does not allow creation of new tables/views. Please
> ensure
> > > that the schema is enabled for write or choose a different schema'.
> > >
> > > thanks
> > >
> > >
> > >
> > > On Mon, Aug 11, 2014 at 12:14 PM, Bob Rumsby <br...@maprtech.com>
> > wrote:
> > >
> > >> I am not sure now what the schema was set to when I ran that example.
> > >> Currently I am getting a different error when I set the schema to
> > >> json_click:
> > >>
> > >> 0: jdbc:drill:> use MFS.json_click;
> > >>
> > >> +------------+------------+
> > >>
> > >> | ok | summary |
> > >>
> > >> +------------+------------+
> > >>
> > >> | true | Default schema changed to 'MFS.json_click' |
> > >>
> > >> +------------+------------+
> > >>
> > >> 1 row selected (0.326 seconds)
> > >>
> > >> 0: jdbc:drill:> select * from MFS.json_click.`mobile.json` limit 2;
> > >>
> > >>
> > >>
> >
> +------------+------------+------------+------------+----------------+------------+
> > >>
> > >> | trans_id | datestamp | timestamp | user_info | marketing_info |
> > >> trans_info |
> > >>
> > >>
> > >>
> >
> +------------+------------+------------+------------+----------------+------------+
> > >>
> > >> | 0 | 07/31/2013 | 11:54:24 |
> > >> {"cust_id":31,"device":"IOS5","state":"ms"} |
> > >> {"camp_id":1,"searchwords":["no","kiss","ever"]} | {"prod_id":[6], |
> > >>
> > >> | 1 | 06/12/2013 | 11:27:12 |
> > >> {"cust_id":0,"device":"IOS5","state":"wa"} |
> > >> {"camp_id":3,"searchwords":["you"]} | {"prod_id":[93],"purch_flag": |
> > >>
> > >>
> > >>
> >
> +------------+------------+------------+------------+----------------+------------+
> > >>
> > >> 2 rows selected (0.439 seconds)
> > >>
> > >> 0: jdbc:drill:> create table mobile as select * from
> > >> MFS.json_click.`mobile.json` limit 2;
> > >>
> > >> +------------+------------+
> > >>
> > >> | ok | summary |
> > >>
> > >> +------------+------------+
> > >>
> > >> | false | Table 'mobile' already exists. |
> > >>
> > >> +------------+------------+
> > >>
> > >> 1 row selected (0.144 seconds)
> > >>
> > >> 0: jdbc:drill:> create table mobile2 as select * from
> > >> MFS.json_click.`mobile.json` limit 2;
> > >>
> > >> Query failed: Screen received stop request sent. null
> > >> [72d50e2d-2072-428a-96f4-e7b1debdc2a2]
> > >>
> > >>
> > >> Error: exception while executing query: Failure while trying to get
> next
> > >> result batch. (state=,code=0)
> > >>
> > >>
> > >> On Mon, Aug 11, 2014 at 11:49 AM, Steven Phillips <
> > sphillips@maprtech.com
> > >> > wrote:
> > >>
> > >>> For question 4, what is the current schema?
> > >>>
> > >>>
> > >>> On Mon, Aug 11, 2014 at 10:57 AM, Bob Rumsby <br...@maprtech.com>
> > >>> wrote:
> > >>>
> > >>>> We talked to Rahul about this last week, and we think that the term
> > >>>> *schema* is overloaded and misleading for several reasons:
> > >>>>
> > >>>> 1. The meaning of the term, generally speaking, depends on the
> > >>>> context.
> > >>>> 2. A *schema* usually refers to the type and structure of data
> in a
> > >>>> data source, but Drill operates on data that is "schema-less" or
> > >>>> "self-describing."
> > >>>> 3. We have commands that refer to schemas, workspaces, file
> > >>>> systems, and databases interchangeably.
> > >>>> 4. A *schema* in the relational database world is a namespace
> > >>>> within a single physical database. Drill does not use the term in
> > this way.
> > >>>>
> > >>>>
> > >>>> Below are some specific examples where the use of the term *schema*
> > >>>> does not seem appropriate or accurate. It's a little bit tricky to
> > >>>> explain clearly. Please let me and Bridget know if you think we
> should
> > >>>> try to change the terminology, and we'll open a Jira.
> > >>>>
> > >>>> 1. Does it make sense to set a *workspace* or a *database* name with
> > >>>> the USE command and have it be called the "default schema"?
> > >>>>
> > >>>> 0: jdbc:drill:> use hive.`default`;
> > >>>>
> > >>>> +------------+------------+
> > >>>>
> > >>>> | ok | summary |
> > >>>>
> > >>>> +------------+------------+
> > >>>>
> > >>>> | true | Default schema changed to 'hive.default' |
> > >>>>
> > >>>> +------------+------------+
> > >>>>
> > >>>> 1 row selected (0.113 seconds)
> > >>>>
> > >>>> 2. The SHOW DATABASES command returns a list of "schemas" not
> > databases?
> > >>>>
> > >>>>
> > >>>> 0: jdbc:drill:> show databases;
> > >>>>
> > >>>> +-------------+
> > >>>>
> > >>>> | SCHEMA_NAME |
> > >>>>
> > >>>> +-------------+
> > >>>>
> > >>>> | hive.default |
> > >>>>
> > >>>> | dfs.default |
> > >>>>
> > >>>> ....
> > >>>>
> > >>>>
> > >>>> 3. The SHOW SCHEMAS command returns the same list, really a list of
> > >>>> workspaces and databases (workspaces for file systems, databases for
> > Hive
> > >>>> and HBase):
> > >>>>
> > >>>> 0: jdbc:drill:> show schemas;
> > >>>>
> > >>>> +-------------+
> > >>>>
> > >>>> | SCHEMA_NAME |
> > >>>>
> > >>>> +-------------+
> > >>>>
> > >>>> | hive.default |
> > >>>>
> > >>>> | dfs.default |
> > >>>>
> > >>>> | dfs.root |
> > >>>>
> > >>>> | dfs.tmp |
> > >>>>
> > >>>> | sys |
> > >>>>
> > >>>> | MFS.default |
> > >>>>
> > >>>> | MFS.json_click |
> > >>>>
> > >>>> | lab.default |
> > >>>>
> > >>>> | lab.root |
> > >>>>
> > >>>> | lab.views |
> > >>>>
> > >>>> | lab.clicks |
> > >>>>
> > >>>> | hbase |
> > >>>>
> > >>>> | INFORMATION_SCHEMA |
> > >>>>
> > >>>> +-------------+
> > >>>>
> > >>>> 13 rows selected (0.114 seconds)
> > >>>>
> > >>>>
> > >>>> 4. What is a "Drill schema" versus a "non-Drill schema"? This
> > non-Drill
> > >>>> schema appears in the show schemas list.
> > >>>>
> > >>>>
> > >>>> 0: jdbc:drill:> create table mobile as select * from
> > >>>> MFS.json_click.`mobile.json` limit 2;
> > >>>>
> > >>>> +------------+------------+
> > >>>>
> > >>>> | ok | summary |
> > >>>>
> > >>>> +------------+------------+
> > >>>>
> > >>>> | false | Error: Current schema is not a Drill schema. Can't
> > >>>> create new relations (tables or views) in non-Drill schemas. |
> > >>>>
> > >>>> +------------+------------+
> > >>>>
> > >>>> 1 row selected (0.086 seconds)
> > >>>>
> > >>>> 0: jdbc:drill:> show schemas;
> > >>>>
> > >>>> +-------------+
> > >>>>
> > >>>> | SCHEMA_NAME |
> > >>>>
> > >>>> +-------------+
> > >>>>
> > >>>> | hive.default |
> > >>>>
> > >>>> | dfs.default |
> > >>>>
> > >>>> | dfs.root |
> > >>>>
> > >>>> | dfs.tmp |
> > >>>>
> > >>>> | sys |
> > >>>>
> > >>>> | MFS.default |
> > >>>>
> > >>>> | MFS.json_click |
> > >>>>
> > >>>> ....
> > >>>>
> > >>>>
> > >>>
> > >>>
> > >>> --
> > >>> Steven Phillips
> > >>> Software Engineer
> > >>>
> > >>> mapr.com
> > >>>
> > >>
> > >>
> > >
> >
> >
> > --
> > ________________________________
> > Keys Botzum
> > Senior Principal Technologist
> > kbotzum@maprtech.com
> > 443-718-0098
> > MapR Technologies
> > http://www.mapr.com <http://mapr.com>
> >
>
Re: Drill terminology issue: "schema"
Posted by Jacques Nadeau <ja...@apache.org>.
Regarding (4), agree with Neeraja. Please file JIRA to this effect.
Regarding Keys' comment: workspaces are lightweight by their nature.
Databases are generally thought of as heavyweight and managed by a dba.
This is directly in conflict with the vision of workspaces. The purpose of
workspaces is that all users can ultimately create many workspaces for
themselves. In many cases, they will hold views, pointers, udfs but
potentially no data. This behavior is atypical compared to the traditional
database concept and thus is created as such. This is part of the vision
of a loosely coupled data system.
Regarding "SCHEMA". You're right that we have overloaded that word to mean
two entirely different things. The first is a logical collection of
tables/views. The latter (as in schemaless) is field names and datatypes
within a complex record. At some level, this is the conflict with bringing
two worlds together. Database isn't necessarily the right concept for the
former (see comments above). However, JDBC and some BI tools use this
concept. At the same time the latter meaning is commonly used when talking
about things like avro schemas. I'm up for an improved approach that also
works in both domains if we can come up with one. But let's make sure we
have good analogs before inventing something new.
On Mon, Aug 11, 2014 at 2:53 PM, Keys Botzum <kb...@maprtech.com> wrote:
> This raises a point I've asked. If a workspace is basically a database, why
> not call it that?
>
> Keys
>
>
> On Mon, Aug 11, 2014 at 5:10 PM, Neeraja Rentachintala <
> nrentachintala@maprtech.com> wrote:
>
> > Bob
> > Thanks for bringing this up.
> > Below are my thoughts.
> >
> > In some of the relational databases such as Oracle/SQL server , 'Schema'
> > and 'Database' actually are different in that a database can have
> multiple
> > schemas (each schema is typically associated with a user). This is not
> > however a common practice across all the relational databases. For
> example,
> > in MySQL , schema and database are just same (refer to
> >
> http://stackoverflow.com/questions/11618277/difference-between-schema-database-in-mysql
> > )
> >
> > Drill has the similar paradigm as MySQL and I believe the behavior above
> > is expected for 1,2,3 above. Essentially schemas and databases mean the
> > same thing.
> > For the file system based queries, each workspace that the user sets up
> > corresponds to a schema/database in Drill for the clients to interact
> with
> > it (A workspace is conceptually same as a Hive database or a HBase
> > namespace i.e essentially it is a group of tables).
> >
> > For 4. above, if I understand the issue correctly, the error message from
> > the original thread very misleading.
> > Drill is a distributed query engine on top of databases and adopts a
> > de-centralized metadata model.Currently Drill allows creating
> tables/views
> > within the file system based schemas. Eventually I see this being
> extended
> > to Hive and HBase schemas as well.
> > The error should probably something like this.
> > 'This schema does not allow creation of new tables/views. Please ensure
> > that the schema is enabled for write or choose a different schema'.
> >
> > thanks
> >
> >
> >
> > On Mon, Aug 11, 2014 at 12:14 PM, Bob Rumsby <br...@maprtech.com>
> wrote:
> >
> >> I am not sure now what the schema was set to when I ran that example.
> >> Currently I am getting a different error when I set the schema to
> >> json_click:
> >>
> >> 0: jdbc:drill:> use MFS.json_click;
> >>
> >> +------------+------------+
> >>
> >> | ok | summary |
> >>
> >> +------------+------------+
> >>
> >> | true | Default schema changed to 'MFS.json_click' |
> >>
> >> +------------+------------+
> >>
> >> 1 row selected (0.326 seconds)
> >>
> >> 0: jdbc:drill:> select * from MFS.json_click.`mobile.json` limit 2;
> >>
> >>
> >>
> +------------+------------+------------+------------+----------------+------------+
> >>
> >> | trans_id | datestamp | timestamp | user_info | marketing_info |
> >> trans_info |
> >>
> >>
> >>
> +------------+------------+------------+------------+----------------+------------+
> >>
> >> | 0 | 07/31/2013 | 11:54:24 |
> >> {"cust_id":31,"device":"IOS5","state":"ms"} |
> >> {"camp_id":1,"searchwords":["no","kiss","ever"]} | {"prod_id":[6], |
> >>
> >> | 1 | 06/12/2013 | 11:27:12 |
> >> {"cust_id":0,"device":"IOS5","state":"wa"} |
> >> {"camp_id":3,"searchwords":["you"]} | {"prod_id":[93],"purch_flag": |
> >>
> >>
> >>
> +------------+------------+------------+------------+----------------+------------+
> >>
> >> 2 rows selected (0.439 seconds)
> >>
> >> 0: jdbc:drill:> create table mobile as select * from
> >> MFS.json_click.`mobile.json` limit 2;
> >>
> >> +------------+------------+
> >>
> >> | ok | summary |
> >>
> >> +------------+------------+
> >>
> >> | false | Table 'mobile' already exists. |
> >>
> >> +------------+------------+
> >>
> >> 1 row selected (0.144 seconds)
> >>
> >> 0: jdbc:drill:> create table mobile2 as select * from
> >> MFS.json_click.`mobile.json` limit 2;
> >>
> >> Query failed: Screen received stop request sent. null
> >> [72d50e2d-2072-428a-96f4-e7b1debdc2a2]
> >>
> >>
> >> Error: exception while executing query: Failure while trying to get next
> >> result batch. (state=,code=0)
> >>
> >>
> >> On Mon, Aug 11, 2014 at 11:49 AM, Steven Phillips <
> sphillips@maprtech.com
> >> > wrote:
> >>
> >>> For question 4, what is the current schema?
> >>>
> >>>
> >>> On Mon, Aug 11, 2014 at 10:57 AM, Bob Rumsby <br...@maprtech.com>
> >>> wrote:
> >>>
> >>>> We talked to Rahul about this last week, and we think that the term
> >>>> *schema* is overloaded and misleading for several reasons:
> >>>>
> >>>> 1. The meaning of the term, generally speaking, depends on the
> >>>> context.
> >>>> 2. A *schema* usually refers to the type and structure of data in a
> >>>> data source, but Drill operates on data that is "schema-less" or
> >>>> "self-describing."
> >>>> 3. We have commands that refer to schemas, workspaces, file
> >>>> systems, and databases interchangeably.
> >>>> 4. A *schema* in the relational database world is a namespace
> >>>> within a single physical database. Drill does not use the term in
> this way.
> >>>>
> >>>>
> >>>> Below are some specific examples where the use of the term *schema*
> >>>> does not seem appropriate or accurate. It's a little bit tricky to
> >>>> explain clearly. Please let me and Bridget know if you think we should
> >>>> try to change the terminology, and we'll open a Jira.
> >>>>
> >>>> 1. Does it make sense to set a *workspace* or a *database* name with
> >>>> the USE command and have it be called the "default schema"?
> >>>>
> >>>> 0: jdbc:drill:> use hive.`default`;
> >>>>
> >>>> +------------+------------+
> >>>>
> >>>> | ok | summary |
> >>>>
> >>>> +------------+------------+
> >>>>
> >>>> | true | Default schema changed to 'hive.default' |
> >>>>
> >>>> +------------+------------+
> >>>>
> >>>> 1 row selected (0.113 seconds)
> >>>>
> >>>> 2. The SHOW DATABASES command returns a list of "schemas" not
> databases?
> >>>>
> >>>>
> >>>> 0: jdbc:drill:> show databases;
> >>>>
> >>>> +-------------+
> >>>>
> >>>> | SCHEMA_NAME |
> >>>>
> >>>> +-------------+
> >>>>
> >>>> | hive.default |
> >>>>
> >>>> | dfs.default |
> >>>>
> >>>> ....
> >>>>
> >>>>
> >>>> 3. The SHOW SCHEMAS command returns the same list, really a list of
> >>>> workspaces and databases (workspaces for file systems, databases for
> Hive
> >>>> and HBase):
> >>>>
> >>>> 0: jdbc:drill:> show schemas;
> >>>>
> >>>> +-------------+
> >>>>
> >>>> | SCHEMA_NAME |
> >>>>
> >>>> +-------------+
> >>>>
> >>>> | hive.default |
> >>>>
> >>>> | dfs.default |
> >>>>
> >>>> | dfs.root |
> >>>>
> >>>> | dfs.tmp |
> >>>>
> >>>> | sys |
> >>>>
> >>>> | MFS.default |
> >>>>
> >>>> | MFS.json_click |
> >>>>
> >>>> | lab.default |
> >>>>
> >>>> | lab.root |
> >>>>
> >>>> | lab.views |
> >>>>
> >>>> | lab.clicks |
> >>>>
> >>>> | hbase |
> >>>>
> >>>> | INFORMATION_SCHEMA |
> >>>>
> >>>> +-------------+
> >>>>
> >>>> 13 rows selected (0.114 seconds)
> >>>>
> >>>>
> >>>> 4. What is a "Drill schema" versus a "non-Drill schema"? This
> non-Drill
> >>>> schema appears in the show schemas list.
> >>>>
> >>>>
> >>>> 0: jdbc:drill:> create table mobile as select * from
> >>>> MFS.json_click.`mobile.json` limit 2;
> >>>>
> >>>> +------------+------------+
> >>>>
> >>>> | ok | summary |
> >>>>
> >>>> +------------+------------+
> >>>>
> >>>> | false | Error: Current schema is not a Drill schema. Can't
> >>>> create new relations (tables or views) in non-Drill schemas. |
> >>>>
> >>>> +------------+------------+
> >>>>
> >>>> 1 row selected (0.086 seconds)
> >>>>
> >>>> 0: jdbc:drill:> show schemas;
> >>>>
> >>>> +-------------+
> >>>>
> >>>> | SCHEMA_NAME |
> >>>>
> >>>> +-------------+
> >>>>
> >>>> | hive.default |
> >>>>
> >>>> | dfs.default |
> >>>>
> >>>> | dfs.root |
> >>>>
> >>>> | dfs.tmp |
> >>>>
> >>>> | sys |
> >>>>
> >>>> | MFS.default |
> >>>>
> >>>> | MFS.json_click |
> >>>>
> >>>> ....
> >>>>
> >>>>
> >>>
> >>>
> >>> --
> >>> Steven Phillips
> >>> Software Engineer
> >>>
> >>> mapr.com
> >>>
> >>
> >>
> >
>
>
> --
> ________________________________
> Keys Botzum
> Senior Principal Technologist
> kbotzum@maprtech.com
> 443-718-0098
> MapR Technologies
> http://www.mapr.com <http://mapr.com>
>
Re: Drill terminology issue: "schema"
Posted by Keys Botzum <kb...@maprtech.com>.
This raises a point I've asked. If a workspace is basically a database, why
not call it that?
Keys
On Mon, Aug 11, 2014 at 5:10 PM, Neeraja Rentachintala <
nrentachintala@maprtech.com> wrote:
> Bob
> Thanks for bringing this up.
> Below are my thoughts.
>
> In some of the relational databases such as Oracle/SQL server , 'Schema'
> and 'Database' actually are different in that a database can have multiple
> schemas (each schema is typically associated with a user). This is not
> however a common practice across all the relational databases. For example,
> in MySQL , schema and database are just same (refer to
> http://stackoverflow.com/questions/11618277/difference-between-schema-database-in-mysql
> )
>
> Drill has the similar paradigm as MySQL and I believe the behavior above
> is expected for 1,2,3 above. Essentially schemas and databases mean the
> same thing.
> For the file system based queries, each workspace that the user sets up
> corresponds to a schema/database in Drill for the clients to interact with
> it (A workspace is conceptually same as a Hive database or a HBase
> namespace i.e essentially it is a group of tables).
>
> For 4. above, if I understand the issue correctly, the error message from
> the original thread very misleading.
> Drill is a distributed query engine on top of databases and adopts a
> de-centralized metadata model.Currently Drill allows creating tables/views
> within the file system based schemas. Eventually I see this being extended
> to Hive and HBase schemas as well.
> The error should probably something like this.
> 'This schema does not allow creation of new tables/views. Please ensure
> that the schema is enabled for write or choose a different schema'.
>
> thanks
>
>
>
> On Mon, Aug 11, 2014 at 12:14 PM, Bob Rumsby <br...@maprtech.com> wrote:
>
>> I am not sure now what the schema was set to when I ran that example.
>> Currently I am getting a different error when I set the schema to
>> json_click:
>>
>> 0: jdbc:drill:> use MFS.json_click;
>>
>> +------------+------------+
>>
>> | ok | summary |
>>
>> +------------+------------+
>>
>> | true | Default schema changed to 'MFS.json_click' |
>>
>> +------------+------------+
>>
>> 1 row selected (0.326 seconds)
>>
>> 0: jdbc:drill:> select * from MFS.json_click.`mobile.json` limit 2;
>>
>>
>> +------------+------------+------------+------------+----------------+------------+
>>
>> | trans_id | datestamp | timestamp | user_info | marketing_info |
>> trans_info |
>>
>>
>> +------------+------------+------------+------------+----------------+------------+
>>
>> | 0 | 07/31/2013 | 11:54:24 |
>> {"cust_id":31,"device":"IOS5","state":"ms"} |
>> {"camp_id":1,"searchwords":["no","kiss","ever"]} | {"prod_id":[6], |
>>
>> | 1 | 06/12/2013 | 11:27:12 |
>> {"cust_id":0,"device":"IOS5","state":"wa"} |
>> {"camp_id":3,"searchwords":["you"]} | {"prod_id":[93],"purch_flag": |
>>
>>
>> +------------+------------+------------+------------+----------------+------------+
>>
>> 2 rows selected (0.439 seconds)
>>
>> 0: jdbc:drill:> create table mobile as select * from
>> MFS.json_click.`mobile.json` limit 2;
>>
>> +------------+------------+
>>
>> | ok | summary |
>>
>> +------------+------------+
>>
>> | false | Table 'mobile' already exists. |
>>
>> +------------+------------+
>>
>> 1 row selected (0.144 seconds)
>>
>> 0: jdbc:drill:> create table mobile2 as select * from
>> MFS.json_click.`mobile.json` limit 2;
>>
>> Query failed: Screen received stop request sent. null
>> [72d50e2d-2072-428a-96f4-e7b1debdc2a2]
>>
>>
>> Error: exception while executing query: Failure while trying to get next
>> result batch. (state=,code=0)
>>
>>
>> On Mon, Aug 11, 2014 at 11:49 AM, Steven Phillips <sphillips@maprtech.com
>> > wrote:
>>
>>> For question 4, what is the current schema?
>>>
>>>
>>> On Mon, Aug 11, 2014 at 10:57 AM, Bob Rumsby <br...@maprtech.com>
>>> wrote:
>>>
>>>> We talked to Rahul about this last week, and we think that the term
>>>> *schema* is overloaded and misleading for several reasons:
>>>>
>>>> 1. The meaning of the term, generally speaking, depends on the
>>>> context.
>>>> 2. A *schema* usually refers to the type and structure of data in a
>>>> data source, but Drill operates on data that is "schema-less" or
>>>> "self-describing."
>>>> 3. We have commands that refer to schemas, workspaces, file
>>>> systems, and databases interchangeably.
>>>> 4. A *schema* in the relational database world is a namespace
>>>> within a single physical database. Drill does not use the term in this way.
>>>>
>>>>
>>>> Below are some specific examples where the use of the term *schema*
>>>> does not seem appropriate or accurate. It's a little bit tricky to
>>>> explain clearly. Please let me and Bridget know if you think we should
>>>> try to change the terminology, and we'll open a Jira.
>>>>
>>>> 1. Does it make sense to set a *workspace* or a *database* name with
>>>> the USE command and have it be called the "default schema"?
>>>>
>>>> 0: jdbc:drill:> use hive.`default`;
>>>>
>>>> +------------+------------+
>>>>
>>>> | ok | summary |
>>>>
>>>> +------------+------------+
>>>>
>>>> | true | Default schema changed to 'hive.default' |
>>>>
>>>> +------------+------------+
>>>>
>>>> 1 row selected (0.113 seconds)
>>>>
>>>> 2. The SHOW DATABASES command returns a list of "schemas" not databases?
>>>>
>>>>
>>>> 0: jdbc:drill:> show databases;
>>>>
>>>> +-------------+
>>>>
>>>> | SCHEMA_NAME |
>>>>
>>>> +-------------+
>>>>
>>>> | hive.default |
>>>>
>>>> | dfs.default |
>>>>
>>>> ....
>>>>
>>>>
>>>> 3. The SHOW SCHEMAS command returns the same list, really a list of
>>>> workspaces and databases (workspaces for file systems, databases for Hive
>>>> and HBase):
>>>>
>>>> 0: jdbc:drill:> show schemas;
>>>>
>>>> +-------------+
>>>>
>>>> | SCHEMA_NAME |
>>>>
>>>> +-------------+
>>>>
>>>> | hive.default |
>>>>
>>>> | dfs.default |
>>>>
>>>> | dfs.root |
>>>>
>>>> | dfs.tmp |
>>>>
>>>> | sys |
>>>>
>>>> | MFS.default |
>>>>
>>>> | MFS.json_click |
>>>>
>>>> | lab.default |
>>>>
>>>> | lab.root |
>>>>
>>>> | lab.views |
>>>>
>>>> | lab.clicks |
>>>>
>>>> | hbase |
>>>>
>>>> | INFORMATION_SCHEMA |
>>>>
>>>> +-------------+
>>>>
>>>> 13 rows selected (0.114 seconds)
>>>>
>>>>
>>>> 4. What is a "Drill schema" versus a "non-Drill schema"? This non-Drill
>>>> schema appears in the show schemas list.
>>>>
>>>>
>>>> 0: jdbc:drill:> create table mobile as select * from
>>>> MFS.json_click.`mobile.json` limit 2;
>>>>
>>>> +------------+------------+
>>>>
>>>> | ok | summary |
>>>>
>>>> +------------+------------+
>>>>
>>>> | false | Error: Current schema is not a Drill schema. Can't
>>>> create new relations (tables or views) in non-Drill schemas. |
>>>>
>>>> +------------+------------+
>>>>
>>>> 1 row selected (0.086 seconds)
>>>>
>>>> 0: jdbc:drill:> show schemas;
>>>>
>>>> +-------------+
>>>>
>>>> | SCHEMA_NAME |
>>>>
>>>> +-------------+
>>>>
>>>> | hive.default |
>>>>
>>>> | dfs.default |
>>>>
>>>> | dfs.root |
>>>>
>>>> | dfs.tmp |
>>>>
>>>> | sys |
>>>>
>>>> | MFS.default |
>>>>
>>>> | MFS.json_click |
>>>>
>>>> ....
>>>>
>>>>
>>>
>>>
>>> --
>>> Steven Phillips
>>> Software Engineer
>>>
>>> mapr.com
>>>
>>
>>
>
--
________________________________
Keys Botzum
Senior Principal Technologist
kbotzum@maprtech.com
443-718-0098
MapR Technologies
http://www.mapr.com <http://mapr.com>
Re: Drill terminology issue: "schema"
Posted by Neeraja Rentachintala <nr...@maprtech.com>.
Bob
Thanks for bringing this up.
Below are my thoughts.
In some of the relational databases such as Oracle/SQL server , 'Schema'
and 'Database' actually are different in that a database can have multiple
schemas (each schema is typically associated with a user). This is not
however a common practice across all the relational databases. For example,
in MySQL , schema and database are just same (refer to
http://stackoverflow.com/questions/11618277/difference-between-schema-database-in-mysql
)
Drill has the similar paradigm as MySQL and I believe the behavior above is
expected for 1,2,3 above. Essentially schemas and databases mean the same
thing.
For the file system based queries, each workspace that the user sets up
corresponds to a schema/database in Drill for the clients to interact with
it (A workspace is conceptually same as a Hive database or a HBase
namespace i.e essentially it is a group of tables).
For 4. above, if I understand the issue correctly, the error message from
the original thread very misleading.
Drill is a distributed query engine on top of databases and adopts a
de-centralized metadata model.Currently Drill allows creating tables/views
within the file system based schemas. Eventually I see this being extended
to Hive and HBase schemas as well.
The error should probably something like this.
'This schema does not allow creation of new tables/views. Please ensure
that the schema is enabled for write or choose a different schema'.
thanks
On Mon, Aug 11, 2014 at 12:14 PM, Bob Rumsby <br...@maprtech.com> wrote:
> I am not sure now what the schema was set to when I ran that example.
> Currently I am getting a different error when I set the schema to
> json_click:
>
> 0: jdbc:drill:> use MFS.json_click;
>
> +------------+------------+
>
> | ok | summary |
>
> +------------+------------+
>
> | true | Default schema changed to 'MFS.json_click' |
>
> +------------+------------+
>
> 1 row selected (0.326 seconds)
>
> 0: jdbc:drill:> select * from MFS.json_click.`mobile.json` limit 2;
>
>
> +------------+------------+------------+------------+----------------+------------+
>
> | trans_id | datestamp | timestamp | user_info | marketing_info |
> trans_info |
>
>
> +------------+------------+------------+------------+----------------+------------+
>
> | 0 | 07/31/2013 | 11:54:24 |
> {"cust_id":31,"device":"IOS5","state":"ms"} |
> {"camp_id":1,"searchwords":["no","kiss","ever"]} | {"prod_id":[6], |
>
> | 1 | 06/12/2013 | 11:27:12 |
> {"cust_id":0,"device":"IOS5","state":"wa"} |
> {"camp_id":3,"searchwords":["you"]} | {"prod_id":[93],"purch_flag": |
>
>
> +------------+------------+------------+------------+----------------+------------+
>
> 2 rows selected (0.439 seconds)
>
> 0: jdbc:drill:> create table mobile as select * from
> MFS.json_click.`mobile.json` limit 2;
>
> +------------+------------+
>
> | ok | summary |
>
> +------------+------------+
>
> | false | Table 'mobile' already exists. |
>
> +------------+------------+
>
> 1 row selected (0.144 seconds)
>
> 0: jdbc:drill:> create table mobile2 as select * from
> MFS.json_click.`mobile.json` limit 2;
>
> Query failed: Screen received stop request sent. null
> [72d50e2d-2072-428a-96f4-e7b1debdc2a2]
>
>
> Error: exception while executing query: Failure while trying to get next
> result batch. (state=,code=0)
>
>
> On Mon, Aug 11, 2014 at 11:49 AM, Steven Phillips <sp...@maprtech.com>
> wrote:
>
>> For question 4, what is the current schema?
>>
>>
>> On Mon, Aug 11, 2014 at 10:57 AM, Bob Rumsby <br...@maprtech.com>
>> wrote:
>>
>>> We talked to Rahul about this last week, and we think that the term
>>> *schema* is overloaded and misleading for several reasons:
>>>
>>> 1. The meaning of the term, generally speaking, depends on the
>>> context.
>>> 2. A *schema* usually refers to the type and structure of data in a
>>> data source, but Drill operates on data that is "schema-less" or
>>> "self-describing."
>>> 3. We have commands that refer to schemas, workspaces, file systems,
>>> and databases interchangeably.
>>> 4. A *schema* in the relational database world is a namespace within
>>> a single physical database. Drill does not use the term in this way.
>>>
>>>
>>> Below are some specific examples where the use of the term *schema*
>>> does not seem appropriate or accurate. It's a little bit tricky to
>>> explain clearly. Please let me and Bridget know if you think we should
>>> try to change the terminology, and we'll open a Jira.
>>>
>>> 1. Does it make sense to set a *workspace* or a *database* name with
>>> the USE command and have it be called the "default schema"?
>>>
>>> 0: jdbc:drill:> use hive.`default`;
>>>
>>> +------------+------------+
>>>
>>> | ok | summary |
>>>
>>> +------------+------------+
>>>
>>> | true | Default schema changed to 'hive.default' |
>>>
>>> +------------+------------+
>>>
>>> 1 row selected (0.113 seconds)
>>>
>>> 2. The SHOW DATABASES command returns a list of "schemas" not databases?
>>>
>>>
>>> 0: jdbc:drill:> show databases;
>>>
>>> +-------------+
>>>
>>> | SCHEMA_NAME |
>>>
>>> +-------------+
>>>
>>> | hive.default |
>>>
>>> | dfs.default |
>>>
>>> ....
>>>
>>>
>>> 3. The SHOW SCHEMAS command returns the same list, really a list of
>>> workspaces and databases (workspaces for file systems, databases for Hive
>>> and HBase):
>>>
>>> 0: jdbc:drill:> show schemas;
>>>
>>> +-------------+
>>>
>>> | SCHEMA_NAME |
>>>
>>> +-------------+
>>>
>>> | hive.default |
>>>
>>> | dfs.default |
>>>
>>> | dfs.root |
>>>
>>> | dfs.tmp |
>>>
>>> | sys |
>>>
>>> | MFS.default |
>>>
>>> | MFS.json_click |
>>>
>>> | lab.default |
>>>
>>> | lab.root |
>>>
>>> | lab.views |
>>>
>>> | lab.clicks |
>>>
>>> | hbase |
>>>
>>> | INFORMATION_SCHEMA |
>>>
>>> +-------------+
>>>
>>> 13 rows selected (0.114 seconds)
>>>
>>>
>>> 4. What is a "Drill schema" versus a "non-Drill schema"? This non-Drill
>>> schema appears in the show schemas list.
>>>
>>>
>>> 0: jdbc:drill:> create table mobile as select * from
>>> MFS.json_click.`mobile.json` limit 2;
>>>
>>> +------------+------------+
>>>
>>> | ok | summary |
>>>
>>> +------------+------------+
>>>
>>> | false | Error: Current schema is not a Drill schema. Can't create
>>> new relations (tables or views) in non-Drill schemas. |
>>>
>>> +------------+------------+
>>>
>>> 1 row selected (0.086 seconds)
>>>
>>> 0: jdbc:drill:> show schemas;
>>>
>>> +-------------+
>>>
>>> | SCHEMA_NAME |
>>>
>>> +-------------+
>>>
>>> | hive.default |
>>>
>>> | dfs.default |
>>>
>>> | dfs.root |
>>>
>>> | dfs.tmp |
>>>
>>> | sys |
>>>
>>> | MFS.default |
>>>
>>> | MFS.json_click |
>>>
>>> ....
>>>
>>>
>>
>>
>> --
>> Steven Phillips
>> Software Engineer
>>
>> mapr.com
>>
>
>
Re: Drill terminology issue: "schema"
Posted by Bob Rumsby <br...@maprtech.com>.
I am not sure now what the schema was set to when I ran that example.
Currently I am getting a different error when I set the schema to
json_click:
0: jdbc:drill:> use MFS.json_click;
+------------+------------+
| ok | summary |
+------------+------------+
| true | Default schema changed to 'MFS.json_click' |
+------------+------------+
1 row selected (0.326 seconds)
0: jdbc:drill:> select * from MFS.json_click.`mobile.json` limit 2;
+------------+------------+------------+------------+----------------+------------+
| trans_id | datestamp | timestamp | user_info | marketing_info |
trans_info |
+------------+------------+------------+------------+----------------+------------+
| 0 | 07/31/2013 | 11:54:24 |
{"cust_id":31,"device":"IOS5","state":"ms"} |
{"camp_id":1,"searchwords":["no","kiss","ever"]} | {"prod_id":[6], |
| 1 | 06/12/2013 | 11:27:12 |
{"cust_id":0,"device":"IOS5","state":"wa"} |
{"camp_id":3,"searchwords":["you"]} | {"prod_id":[93],"purch_flag": |
+------------+------------+------------+------------+----------------+------------+
2 rows selected (0.439 seconds)
0: jdbc:drill:> create table mobile as select * from
MFS.json_click.`mobile.json` limit 2;
+------------+------------+
| ok | summary |
+------------+------------+
| false | Table 'mobile' already exists. |
+------------+------------+
1 row selected (0.144 seconds)
0: jdbc:drill:> create table mobile2 as select * from
MFS.json_click.`mobile.json` limit 2;
Query failed: Screen received stop request sent. null
[72d50e2d-2072-428a-96f4-e7b1debdc2a2]
Error: exception while executing query: Failure while trying to get next
result batch. (state=,code=0)
On Mon, Aug 11, 2014 at 11:49 AM, Steven Phillips <sp...@maprtech.com>
wrote:
> For question 4, what is the current schema?
>
>
> On Mon, Aug 11, 2014 at 10:57 AM, Bob Rumsby <br...@maprtech.com> wrote:
>
>> We talked to Rahul about this last week, and we think that the term
>> *schema* is overloaded and misleading for several reasons:
>>
>> 1. The meaning of the term, generally speaking, depends on the
>> context.
>> 2. A *schema* usually refers to the type and structure of data in a
>> data source, but Drill operates on data that is "schema-less" or
>> "self-describing."
>> 3. We have commands that refer to schemas, workspaces, file systems,
>> and databases interchangeably.
>> 4. A *schema* in the relational database world is a namespace within
>> a single physical database. Drill does not use the term in this way.
>>
>>
>> Below are some specific examples where the use of the term *schema* does
>> not seem appropriate or accurate. It's a little bit tricky to explain
>> clearly. Please let me and Bridget know if you think we should try to
>> change the terminology, and we'll open a Jira.
>>
>> 1. Does it make sense to set a *workspace* or a *database* name with the
>> USE command and have it be called the "default schema"?
>>
>> 0: jdbc:drill:> use hive.`default`;
>>
>> +------------+------------+
>>
>> | ok | summary |
>>
>> +------------+------------+
>>
>> | true | Default schema changed to 'hive.default' |
>>
>> +------------+------------+
>>
>> 1 row selected (0.113 seconds)
>>
>> 2. The SHOW DATABASES command returns a list of "schemas" not databases?
>>
>>
>> 0: jdbc:drill:> show databases;
>>
>> +-------------+
>>
>> | SCHEMA_NAME |
>>
>> +-------------+
>>
>> | hive.default |
>>
>> | dfs.default |
>>
>> ....
>>
>>
>> 3. The SHOW SCHEMAS command returns the same list, really a list of
>> workspaces and databases (workspaces for file systems, databases for Hive
>> and HBase):
>>
>> 0: jdbc:drill:> show schemas;
>>
>> +-------------+
>>
>> | SCHEMA_NAME |
>>
>> +-------------+
>>
>> | hive.default |
>>
>> | dfs.default |
>>
>> | dfs.root |
>>
>> | dfs.tmp |
>>
>> | sys |
>>
>> | MFS.default |
>>
>> | MFS.json_click |
>>
>> | lab.default |
>>
>> | lab.root |
>>
>> | lab.views |
>>
>> | lab.clicks |
>>
>> | hbase |
>>
>> | INFORMATION_SCHEMA |
>>
>> +-------------+
>>
>> 13 rows selected (0.114 seconds)
>>
>>
>> 4. What is a "Drill schema" versus a "non-Drill schema"? This non-Drill
>> schema appears in the show schemas list.
>>
>>
>> 0: jdbc:drill:> create table mobile as select * from
>> MFS.json_click.`mobile.json` limit 2;
>>
>> +------------+------------+
>>
>> | ok | summary |
>>
>> +------------+------------+
>>
>> | false | Error: Current schema is not a Drill schema. Can't create
>> new relations (tables or views) in non-Drill schemas. |
>>
>> +------------+------------+
>>
>> 1 row selected (0.086 seconds)
>>
>> 0: jdbc:drill:> show schemas;
>>
>> +-------------+
>>
>> | SCHEMA_NAME |
>>
>> +-------------+
>>
>> | hive.default |
>>
>> | dfs.default |
>>
>> | dfs.root |
>>
>> | dfs.tmp |
>>
>> | sys |
>>
>> | MFS.default |
>>
>> | MFS.json_click |
>>
>> ....
>>
>>
>
>
> --
> Steven Phillips
> Software Engineer
>
> mapr.com
>
Re: Drill terminology issue: "schema"
Posted by Steven Phillips <sp...@maprtech.com>.
For question 4, what is the current schema?
On Mon, Aug 11, 2014 at 10:57 AM, Bob Rumsby <br...@maprtech.com> wrote:
> We talked to Rahul about this last week, and we think that the term
> *schema* is overloaded and misleading for several reasons:
>
> 1. The meaning of the term, generally speaking, depends on the context.
> 2. A *schema* usually refers to the type and structure of data in a
> data source, but Drill operates on data that is "schema-less" or
> "self-describing."
> 3. We have commands that refer to schemas, workspaces, file systems,
> and databases interchangeably.
> 4. A *schema* in the relational database world is a namespace within a
> single physical database. Drill does not use the term in this way.
>
>
> Below are some specific examples where the use of the term *schema* does
> not seem appropriate or accurate. It's a little bit tricky to explain
> clearly. Please let me and Bridget know if you think we should try to
> change the terminology, and we'll open a Jira.
>
> 1. Does it make sense to set a *workspace* or a *database* name with the
> USE command and have it be called the "default schema"?
>
> 0: jdbc:drill:> use hive.`default`;
>
> +------------+------------+
>
> | ok | summary |
>
> +------------+------------+
>
> | true | Default schema changed to 'hive.default' |
>
> +------------+------------+
>
> 1 row selected (0.113 seconds)
>
> 2. The SHOW DATABASES command returns a list of "schemas" not databases?
>
>
> 0: jdbc:drill:> show databases;
>
> +-------------+
>
> | SCHEMA_NAME |
>
> +-------------+
>
> | hive.default |
>
> | dfs.default |
>
> ....
>
>
> 3. The SHOW SCHEMAS command returns the same list, really a list of
> workspaces and databases (workspaces for file systems, databases for Hive
> and HBase):
>
> 0: jdbc:drill:> show schemas;
>
> +-------------+
>
> | SCHEMA_NAME |
>
> +-------------+
>
> | hive.default |
>
> | dfs.default |
>
> | dfs.root |
>
> | dfs.tmp |
>
> | sys |
>
> | MFS.default |
>
> | MFS.json_click |
>
> | lab.default |
>
> | lab.root |
>
> | lab.views |
>
> | lab.clicks |
>
> | hbase |
>
> | INFORMATION_SCHEMA |
>
> +-------------+
>
> 13 rows selected (0.114 seconds)
>
>
> 4. What is a "Drill schema" versus a "non-Drill schema"? This non-Drill
> schema appears in the show schemas list.
>
>
> 0: jdbc:drill:> create table mobile as select * from
> MFS.json_click.`mobile.json` limit 2;
>
> +------------+------------+
>
> | ok | summary |
>
> +------------+------------+
>
> | false | Error: Current schema is not a Drill schema. Can't create
> new relations (tables or views) in non-Drill schemas. |
>
> +------------+------------+
>
> 1 row selected (0.086 seconds)
>
> 0: jdbc:drill:> show schemas;
>
> +-------------+
>
> | SCHEMA_NAME |
>
> +-------------+
>
> | hive.default |
>
> | dfs.default |
>
> | dfs.root |
>
> | dfs.tmp |
>
> | sys |
>
> | MFS.default |
>
> | MFS.json_click |
>
> ....
>
>
--
Steven Phillips
Software Engineer
mapr.com