Boaz, Thanks for full explanation. I got impression that Apache Drill is internally creating json schema for input type. I thought we would be able to look deep into that schema, Maybe it is not in "describe" level but it is API level we can achieve it. That do you think?
Erol Akarsu On Thu, Mar 1, 2018 at 7:04 PM, Boaz Ben-Zvi <[email protected]> wrote: > From the docs (https://drill.apache.org/docs/describe/): “Currently, > DESCRIBE does not support tables created in a file system.” > Seems that it only works well for Hive and HBase tables. > > The create view statement does not explore the actual schema of the > query’s table(s); it only parses and keeps the query. > Hence it can not tell what ‘*’ (or any list of column names) means: > > 0: jdbc:drill:zk=local> create view pview as select * from > dfs.`/data/PARQUET-1M.parquet`; > +-------+-------------------------------------------------------+ > | ok | summary | > +-------+-------------------------------------------------------+ > | true | View 'pview' created successfully in 'dfs.tmp' schema | > +-------+-------------------------------------------------------+ > 1 row selected (0.274 seconds) > 0: jdbc:drill:zk=local> describe pview; > +--------------+---------------+--------------+ > | COLUMN_NAME | DATA_TYPE | IS_NULLABLE | > +--------------+---------------+--------------+ > | ** | DYNAMIC_STAR | YES | > +--------------+---------------+--------------+ > 1 row selected (0.419 seconds) > 0: jdbc:drill:zk=local> create view pview2 as select > row_count,just_garbage from dfs.`/data/PARQUET-1M.parquet`; > +-------+---------------------------------------------------------+ > | ok | summary | > +-------+---------------------------------------------------------+ > | true | View 'pview2' created successfully in 'dfs.tmp' schema | > +-------+---------------------------------------------------------+ > 1 row selected (0.3 seconds) > 0: jdbc:drill:zk=local> describe pview2; > +---------------+------------+--------------+ > | COLUMN_NAME | DATA_TYPE | IS_NULLABLE | > +---------------+------------+--------------+ > | row_count | ANY | YES | > | just_garbage | ANY | YES | > +---------------+------------+--------------+ > 2 rows selected (0.533 seconds) > 0: jdbc:drill:zk=local> > > Thanks, > > Boaz > > On 3/1/18, 3:05 PM, "Padma Penumarthy" <[email protected]> wrote: > > Not sure why it is not showing the fields. It does not work for me > either. > Does anyone know more ? Is this broken ? > > Thanks > Padma > > > On Mar 1, 2018, at 2:54 PM, Erol Akarsu <[email protected]> wrote: > > > > Somehow, after "user dfs.tmp", I was able to create view. But > "describe" > > for view does not give much information. I was expecting "describe" > command > > would give type definitions of fields " employee_id | > full_name | > > first_name | last_name | position_id | position_title | > store_id | > > depart ". But it does give a very generic field type. > > > > > > 0: jdbc:drill:zk=local> create view mydonuts2 as SELECT * FROM > > cp.`employee.json` LIMIT 3; > > +-------+--------------------------------------------------- > ---------+ > > | ok | summary > | > > +-------+--------------------------------------------------- > ---------+ > > | true | View 'mydonuts2' created successfully in 'dfs.tmp' schema > | > > +-------+--------------------------------------------------- > ---------+ > > 1 row selected (0.283 seconds) > > 0: jdbc:drill:zk=local> describe mydonuts2; > > +--------------+------------+--------------+ > > | COLUMN_NAME | DATA_TYPE | IS_NULLABLE | > > +--------------+------------+--------------+ > > | * | ANY | YES | > > +--------------+------------+--------------+ > > 1 row selected (0.388 seconds) > > 0: jdbc:drill:zk=local> SELECT * FROM cp.`employee.json` LIMIT 3; > > +--------------+------------------+-------------+----------- > -+--------------+---------------------+-----------+--------+ > > | employee_id | full_name | first_name | last_name | > position_id > > | position_title | store_id | depart | > > +--------------+------------------+-------------+----------- > -+--------------+---------------------+-----------+--------+ > > | 1 | Sheri Nowmer | Sheri | Nowmer | 1 > > | President | 0 | 1 | > > | 2 | Derrick Whelply | Derrick | Whelply | 2 > > | VP Country Manager | 0 | 1 | > > | 4 | Michael Spence | Michael | Spence | 2 > > | VP Country Manager | 0 | 1 | > > +--------------+------------------+-------------+----------- > -+--------------+---------------------+-----------+--------+ > > 3 rows selected (0.579 seconds) > > > > On Thu, Mar 1, 2018 at 3:18 PM, Erol Akarsu <[email protected]> > wrote: > > > >> Padma, > >> > >> I have not created any user. I just installed the system and run > drill > >> with "sqlline.bat -u "jdbc:drill:zk=local" > >> Therefore, what is shortest procedure to achieve what you have > described > >> in previous email? > >> > >> Thanks > >> > >> Erol Akarsu > >> > >> On Thu, Mar 1, 2018 at 3:00 PM, Padma Penumarthy < > [email protected]> > >> wrote: > >> > >>> Check if you have permissions to root directory or not. > >>> You may have to specify the complete directory path (for which you > have > >>> permissions for) in the create view command. > >>> > >>> For example: > >>> > >>> 0: jdbc:drill:zk=local> create view dfs.root.`/Users/ppenumarthy/ > parquet/test-view` > >>> as select * from dfs.root.`/Users/ppenumarthy/ > parquet/0_0_0.parquet`; > >>> +-------+--------------------------------------------------- > >>> -------------------------------------+ > >>> | ok | summary > >>> | > >>> +-------+--------------------------------------------------- > >>> -------------------------------------+ > >>> | true | View '/Users/ppenumarthy/parquet/test-view' created > >>> successfully in 'dfs.root' schema | > >>> +-------+--------------------------------------------------- > >>> -------------------------------------+ > >>> 1 row selected (0.148 seconds) > >>> 0: jdbc:drill:zk=local> > >>> > >>> > >>> Thanks > >>> Padma > >>> > >>> On Mar 1, 2018, at 11:37 AM, Erol Akarsu <[email protected] > <mailto:eaka > >>> [email protected]>> wrote: > >>> > >>> Padma, > >>> > >>> I have changed dfs storage plugin through web interface as below. > But I am > >>> getting same error response. > >>> > >>> { > >>> "type": "file", > >>> "enabled": true, > >>> "connection": "file:///", > >>> "config": null, > >>> "workspaces": { > >>> "root": { > >>> "location": "/", > >>> "writable": true, > >>> "defaultInputFormat": null, > >>> "allowAccessOutsideWorkspace": true > >>> }, > >>> "tmp": { > >>> "location": "/tmp", > >>> "writable": true, > >>> "defaultInputFormat": null, > >>> "allowAccessOutsideWorkspace": true > >>> } > >>> }, > >>> > >>> On Thu, Mar 1, 2018 at 1:15 PM, Padma Penumarthy < > [email protected] > >>> <mailto:[email protected]>> > >>> wrote: > >>> > >>> Make "writable": true for the workspace (dfs.root) in the storage > plugin > >>> configuration. > >>> > >>> Thanks > >>> Padma > >>> > >>> > >>> On Mar 1, 2018, at 10:10 AM, Erol Akarsu <[email protected] > <mailto:eaka > >>> [email protected]><mailto:eaka > >>> [email protected]<mailto:[email protected]>>> wrote: > >>> > >>> Thanks Padma. > >>> > >>> I am getting problem while creating view > >>> > >>> 0: jdbc:drill:zk=local> create view mydonuts as SELECT * FROM > >>> cp.`employee.json` LIMIT 3; > >>> Error: VALIDATION ERROR: Root schema is immutable. Creating or > dropping > >>> tables/views is not allowed in root schema.Select a schema using > 'USE > >>> schema' command. > >>> > >>> > >>> [Error Id: 68a31047-5a4e-4768-8722-55648d9a80f6 on > DESKTOP-8OANV3A:31010] > >>> (state=,code=0) > >>> 0: jdbc:drill:zk=local> > >>> > >>> On Thu, Mar 1, 2018 at 12:49 PM, Padma Penumarthy < > [email protected] > >>> <mailto:[email protected]> > >>> <mailto:[email protected]>> > >>> wrote: > >>> > >>> Try creating a view and use describe. > >>> > >>> https://urldefense.proofpoint.com/v2/url?u=https-3A__drill. > >>> apache.org_docs_describe_&d=DwIBaQ&c=cskdkSMqhcnjZxdQVpwTXg&r= > >>> XVqW14B9eGK9QR_fKKCb5H5LxKnqNMmq1U7RdNlhq1c& > m=blTmu-WQJa5RUrxqG46o20B-a- > >>> UP0yCHKGqcBo1ETlI&s=SxKqC4w_I5bbnNAtIvzO_9qptLP-KsHi0iIbjtzyrc8&e= > >>> > >>> Thanks > >>> Padma > >>> > >>> > >>> On Mar 1, 2018, at 9:22 AM, Erol Akarsu <[email protected]<mailto: > eaka > >>> [email protected]><mailto:eaka > >>> [email protected]<mailto:[email protected]>><mailto:eaka > >>> [email protected]<mailto:[email protected]><mailto:[email protected]>>> wrote: > >>> > >>> When Use limit 0 query, I am getting only field names. I am > looking for > >>> json schema for input that will describe input type > >>> > >>> 0: jdbc:drill:> select * from `clicks/clicks.json` limit 0; > >>> > >>> +-----------+-------+-------+------------+-------------+ > >>> | trans_id | date | time | user_info | trans_info | > >>> +-----------+-------+-------+------------+-------------+ > >>> +-----------+-------+-------+------------+-------------+ > >>> No > >>> > >>> On Thu, Mar 1, 2018 at 11:24 AM, Erol Akarsu <[email protected] > <mailto: > >>> [email protected]><mailto: > >>> [email protected]<mailto:[email protected]>><mailto: > >>> [email protected]<mailto:[email protected]><mailto:eakarsu@ > gmail.com>>> > >>> wrote: > >>> > >>> I am sorry Sorabh > >>> Can you give an example? I am still learning Drill > >>> Thanks > >>> > >>> On Thu, Mar 1, 2018 at 11:11 AM Sorabh Hamirwasia < > [email protected] > >>> <mailto:[email protected]> > >>> <mailto:[email protected]> > >>> <mailto:[email protected]>> > >>> wrote: > >>> > >>> Hi Erol, > >>> > >>> You can run limit 0 query from client to retrieve just the schema > for > >>> your input. > >>> > >>> > >>> Thanks, > >>> Sorabh > >>> > >>> ________________________________ > >>> From: Erol Akarsu <[email protected]<mailto:[email protected] > ><mailto: > >>> [email protected]><mailto: > >>> [email protected]<mailto:[email protected]>>> > >>> Sent: Thursday, March 1, 2018 5:28:52 AM > >>> To: [email protected]<mailto:[email protected]><mailto:u > >>> [email protected]><mailto:u > >>> [email protected]<mailto:[email protected]>> > >>> Subject: Accessing underlying scheme of input > >>> > >>> I know Apache drill is creating a json schema for input data file > or hdfs > >>> input before user query on it. > >>> I like to know whether or not Apache drill has API that will help > user to > >>> obtain that derived schema for say an json file or excel file or > hive > >>> input. > >>> I appreciate your help > >>> > >>> Erol Akarsu > >>> > >>> Sent from Mail for Windows 10 > >>> > >>> -- > >>> > >>> Erol Akarsu > >>> > >>> > >>> > >>> > >>> -- > >>> > >>> Erol Akarsu > >>> > >>> > >>> > >>> > >>> -- > >>> > >>> Erol Akarsu > >>> > >>> > >>> > >>> > >>> -- > >>> > >>> Erol Akarsu > >>> > >>> > >> > >> > >> -- > >> > >> Erol Akarsu > >> > >> > > > > > > -- > > > > Erol Akarsu > > > > -- Erol Akarsu
