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 <[email protected]> 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 <[email protected]> > wrote: > >> For question 4, what is the current schema? >> >> >> On Mon, Aug 11, 2014 at 10:57 AM, Bob Rumsby <[email protected]> >> 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 >> > >
