You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by "Sivasubramaniam, Latha" <La...@Aspect.com> on 2015/04/08 00:02:54 UTC
Unable to query data from hdfs
Hi,
I have the hdfs storage system registered, below is the storage plugin details, it got registered successfully.
{
"type": "file",
"enabled": true,
"connection": "hdfs://<actualIP addr>:8020/",
"workspaces": {
"root": {
"location": "/user/root/",
"writable": true,
"defaultInputFormat": null
}
},
"formats": {
"parquet": {
"type": "parquet"
},
"csv": {
"type": "text",
"extensions": [
"csv"
],
"delimiter": ","
}
}
}
I am able to list the files from the hdfs location, but when I query one of the file it gives an error. I tried different ways everything says "not found". Appreciate any help.
0: jdbc:drill:zk=rtr-poc-imp1:2181> show files;
+------------+-------------+------------+------------+------------+------------+-------------+------------+------------------+
| name | isDirectory | isFile | length | owner | group | permissions | accessTime | modificationTime |
+------------+-------------+------------+------------+------------+------------+-------------+------------+------------------+
| .Trash | true | false | 0 | root | root | rwx------ | 1969-12-31 18:00:00.0 | 2015-04-01 19:00:00.253 |
| .staging | true | false | 0 | root | root | rwx------ | 1969-12-31 18:00:00.0 | 2015-03-31 15:18:52.599 |
| DIM_Agent_Status | true | false | 0 | root | root | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-31 15:17:12.022 |
| DIM_Agent_Status_Reasons | true | false | 0 | root | root | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-31 15:18:51.35 |
| DIM_Agents | true | false | 0 | root | root | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-26 17:16:54.108 |
| DIM_Call_Action_Reasons | true | false | 0 | root | root | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-31 15:16:04.684 |
| DIM_Call_Actions | true | false | 0 | root | root | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-30 17:18:27.834 |
| DIM_Call_Types | true | false | 0 | root | root | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-30 17:08:48.452 |
| DIM_Dispositions | true | false | 0 | root | root | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-31 15:15:11.433 |
| DIM_Resource_Groups | true | false | 0 | root | root | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-31 15:09:12.781 |
| DIM_Services | true | false | 0 | root | root | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-30 17:15:47.463 |
| DIM_Sites | true | false | 0 | root | root | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-30 17:10:15.353 |
| DIM_Workgroups | true | false | 0 | root | root | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-31 15:14:24.826 |
| FACT_Agent_Activity_Detail_12M | true | false | 0 | root | root | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-31 00:42:54.38 |
| FACT_Contact_Detail | true | false | 0 | root | root | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-27 00:22:36.465 |
| FACT_Contact_Detail_12M | true | false | 0 | root | root | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-27 15:34:40.518 |
| test.csv | false | true | 17 | root | root | rw-r--r-- | 2015-04-07 15:37:13.469 | 2015-04-07 15:37:13.792 |
+------------+-------------+------------+------------+------------+------------+-------------+------------+------------------+
17 rows selected (0.089 seconds)
0: jdbc:drill:zk=rtr-poc-imp1:2181> select * from root.`/test.csv`;
Query failed: SqlValidatorException: Table 'root./test.csv' not found
Error: exception while executing query: Failure while executing query. (state=,code=0)
0: jdbc:drill:zk=rtr-poc-imp1:2181>
Thanks,
Latha
This email (including any attachments) is proprietary to Aspect Software, Inc. and may contain information that is confidential. If you have received this message in error, please do not read, copy or forward this message. Please notify the sender immediately, delete it from your system and destroy any copies. You may not further disclose or distribute this email or its attachments.
Re: Unable to query data from hdfs
Posted by Ramana Inukonda <ri...@maprtech.com>.
Hi Latha,
In your case if you can see the files when you run show files you can just
run
select * from `/test.csv`;
For example:
This is what my workspace looks like:
"userroot": {
"location": "/user/root",
"writable": true,
"defaultInputFormat": null
},
now:
0: jdbc:drill:> use dfs.userroot;
0: jdbc:drill:> show files;
+------------+-------------+------------+------------+------------+------------+-------------+------------+------------------+
| name | isDirectory | isFile | length | owner |
group | permissions | accessTime | modificationTime |
+------------+-------------+------------+------------+------------+------------+-------------+------------+------------------+
| webdata.tbl | false | true | 706353 | root | root
| rwxr-xr-x | 2014-11-13 16:08:31.0 | 2014-11-13 16:08:31.794 |
Now I can either do:
0: jdbc:drill:> select * from `webdata.tbl`;
or
0: jdbc:drill:> select * from dfs.userroot.`webdata.tbl`;
should work.
Regards
Ramana
On Tue, Apr 7, 2015 at 3:02 PM, Sivasubramaniam, Latha <
Latha.Sivasubramaniam@aspect.com> wrote:
> Hi,
>
> I have the hdfs storage system registered, below is the storage plugin
> details, it got registered successfully.
>
>
> {
> "type": "file",
> "enabled": true,
> "connection": "hdfs://<actualIP addr>:8020/",
> "workspaces": {
> "root": {
> "location": "/user/root/",
> "writable": true,
> "defaultInputFormat": null
> }
> },
> "formats": {
> "parquet": {
> "type": "parquet"
> },
> "csv": {
> "type": "text",
> "extensions": [
> "csv"
> ],
> "delimiter": ","
> }
> }
> }
>
> I am able to list the files from the hdfs location, but when I query one
> of the file it gives an error. I tried different ways everything says "not
> found". Appreciate any help.
>
>
> 0: jdbc:drill:zk=rtr-poc-imp1:2181> show files;
>
> +------------+-------------+------------+------------+------------+------------+-------------+------------+------------------+
> | name | isDirectory | isFile | length | owner |
> group | permissions | accessTime | modificationTime |
>
> +------------+-------------+------------+------------+------------+------------+-------------+------------+------------------+
> | .Trash | true | false | 0 | root | root
> | rwx------ | 1969-12-31 18:00:00.0 | 2015-04-01 19:00:00.253 |
> | .staging | true | false | 0 | root | root
> | rwx------ | 1969-12-31 18:00:00.0 | 2015-03-31 15:18:52.599 |
> | DIM_Agent_Status | true | false | 0 | root |
> root | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-31 15:17:12.022 |
> | DIM_Agent_Status_Reasons | true | false | 0 | root
> | root | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-31
> 15:18:51.35 |
> | DIM_Agents | true | false | 0 | root | root
> | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-26 17:16:54.108 |
> | DIM_Call_Action_Reasons | true | false | 0 | root
> | root | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-31
> 15:16:04.684 |
> | DIM_Call_Actions | true | false | 0 | root |
> root | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-30 17:18:27.834 |
> | DIM_Call_Types | true | false | 0 | root |
> root | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-30 17:08:48.452 |
> | DIM_Dispositions | true | false | 0 | root |
> root | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-31 15:15:11.433 |
> | DIM_Resource_Groups | true | false | 0 | root
> | root | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-31
> 15:09:12.781 |
> | DIM_Services | true | false | 0 | root |
> root | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-30 17:15:47.463 |
> | DIM_Sites | true | false | 0 | root | root
> | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-30 17:10:15.353 |
> | DIM_Workgroups | true | false | 0 | root |
> root | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-31 15:14:24.826 |
> | FACT_Agent_Activity_Detail_12M | true | false | 0 |
> root | root | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-31
> 00:42:54.38 |
> | FACT_Contact_Detail | true | false | 0 | root
> | root | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-27
> 00:22:36.465 |
> | FACT_Contact_Detail_12M | true | false | 0 | root
> | root | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-27
> 15:34:40.518 |
> | test.csv | false | true | 17 | root | root
> | rw-r--r-- | 2015-04-07 15:37:13.469 | 2015-04-07 15:37:13.792 |
>
> +------------+-------------+------------+------------+------------+------------+-------------+------------+------------------+
> 17 rows selected (0.089 seconds)
> 0: jdbc:drill:zk=rtr-poc-imp1:2181> select * from root.`/test.csv`;
> Query failed: SqlValidatorException: Table 'root./test.csv' not found
>
> Error: exception while executing query: Failure while executing query.
> (state=,code=0)
> 0: jdbc:drill:zk=rtr-poc-imp1:2181>
>
> Thanks,
> Latha
> This email (including any attachments) is proprietary to Aspect Software,
> Inc. and may contain information that is confidential. If you have received
> this message in error, please do not read, copy or forward this message.
> Please notify the sender immediately, delete it from your system and
> destroy any copies. You may not further disclose or distribute this email
> or its attachments.
>
Re: Unable to query data from hdfs
Posted by Andries Engelbrecht <ae...@maprtech.com>.
I assume the plugin is registered as dfs.
Try
select * from dfs.root.`/test.csv`;
You need to use plugin name and workspace name in the query.
Or you can simple go to the specific schema
use dfs.root;
select * from `/test.csv`;
—Andries
On Apr 7, 2015, at 3:02 PM, Sivasubramaniam, Latha <La...@Aspect.com> wrote:
> Hi,
>
> I have the hdfs storage system registered, below is the storage plugin details, it got registered successfully.
>
>
> {
> "type": "file",
> "enabled": true,
> "connection": "hdfs://<actualIP addr>:8020/",
> "workspaces": {
> "root": {
> "location": "/user/root/",
> "writable": true,
> "defaultInputFormat": null
> }
> },
> "formats": {
> "parquet": {
> "type": "parquet"
> },
> "csv": {
> "type": "text",
> "extensions": [
> "csv"
> ],
> "delimiter": ","
> }
> }
> }
>
> I am able to list the files from the hdfs location, but when I query one of the file it gives an error. I tried different ways everything says "not found". Appreciate any help.
>
>
> 0: jdbc:drill:zk=rtr-poc-imp1:2181> show files;
> +------------+-------------+------------+------------+------------+------------+-------------+------------+------------------+
> | name | isDirectory | isFile | length | owner | group | permissions | accessTime | modificationTime |
> +------------+-------------+------------+------------+------------+------------+-------------+------------+------------------+
> | .Trash | true | false | 0 | root | root | rwx------ | 1969-12-31 18:00:00.0 | 2015-04-01 19:00:00.253 |
> | .staging | true | false | 0 | root | root | rwx------ | 1969-12-31 18:00:00.0 | 2015-03-31 15:18:52.599 |
> | DIM_Agent_Status | true | false | 0 | root | root | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-31 15:17:12.022 |
> | DIM_Agent_Status_Reasons | true | false | 0 | root | root | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-31 15:18:51.35 |
> | DIM_Agents | true | false | 0 | root | root | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-26 17:16:54.108 |
> | DIM_Call_Action_Reasons | true | false | 0 | root | root | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-31 15:16:04.684 |
> | DIM_Call_Actions | true | false | 0 | root | root | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-30 17:18:27.834 |
> | DIM_Call_Types | true | false | 0 | root | root | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-30 17:08:48.452 |
> | DIM_Dispositions | true | false | 0 | root | root | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-31 15:15:11.433 |
> | DIM_Resource_Groups | true | false | 0 | root | root | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-31 15:09:12.781 |
> | DIM_Services | true | false | 0 | root | root | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-30 17:15:47.463 |
> | DIM_Sites | true | false | 0 | root | root | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-30 17:10:15.353 |
> | DIM_Workgroups | true | false | 0 | root | root | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-31 15:14:24.826 |
> | FACT_Agent_Activity_Detail_12M | true | false | 0 | root | root | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-31 00:42:54.38 |
> | FACT_Contact_Detail | true | false | 0 | root | root | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-27 00:22:36.465 |
> | FACT_Contact_Detail_12M | true | false | 0 | root | root | rwxr-xr-x | 1969-12-31 18:00:00.0 | 2015-03-27 15:34:40.518 |
> | test.csv | false | true | 17 | root | root | rw-r--r-- | 2015-04-07 15:37:13.469 | 2015-04-07 15:37:13.792 |
> +------------+-------------+------------+------------+------------+------------+-------------+------------+------------------+
> 17 rows selected (0.089 seconds)
> 0: jdbc:drill:zk=rtr-poc-imp1:2181> select * from root.`/test.csv`;
> Query failed: SqlValidatorException: Table 'root./test.csv' not found
>
> Error: exception while executing query: Failure while executing query. (state=,code=0)
> 0: jdbc:drill:zk=rtr-poc-imp1:2181>
>
> Thanks,
> Latha
> This email (including any attachments) is proprietary to Aspect Software, Inc. and may contain information that is confidential. If you have received this message in error, please do not read, copy or forward this message. Please notify the sender immediately, delete it from your system and destroy any copies. You may not further disclose or distribute this email or its attachments.