You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by "G.A. Pagani" <g....@rug.nl> on 2017/02/07 09:35:20 UTC
Problem in WHERE clause querying HBase
Hi all,
I am a newbie in Drill and Hbase.
I have installed Hbase 1.2.1 and Drill 1.9.0 both on a embedded (single
host) configuration. I have enabled the HBase plugin with default
parameters.
I have a table that I manage to connect to and retrieve:
SELECT CONVERT_FROM(row_key, 'UTF8') as key,
CAST(fogDecDB.Location.latitude AS FLOAT) as lat FROM fogDecDB LIMIT 10;
+-------------------------------------------------------------------+------------+
| key |
lat |
+-------------------------------------------------------------------+------------+
| 00001e14dbf570471418f1c26384637a3154561bfc32062bbb416bedc165841d |
51.971 |
| 0000c0ad64df90c8feebd4e191272413d01989c997d0e2aec34cf259f3207ddb |
52.276943 |
| 00023c7cf936bd1933f8b0f690ae3f347d3fd3131278b4e0372d34dde9586b62 |
52.276943 |
| 0003164d842b488a1a0d8572ae06b983ceb3a645f41531acfe4962ad7ede80a2 |
52.276943 |
| 0003bd065e59d03f3907531279579025f49282a3aa1734d1c4d63df891420c58 |
52.1093 |
| 0006963d79ccf3a71f086f77b26a4db7da09a77c8b2c523fc51e04df4087abbd |
52.1093 |
| 00069cd1f16dde18f4b6944c81015e4f7113ae447c02743a903814c0e64bb2e8 |
52.1093 |
| 00070d2b641fc506be01c4b0b15f102d01b43d34f0b98f054154d20ea1ad7aa1 |
52.1093 |
| 0007fd3e7db369f258173e7d47c13f81e3cbb48ecf446dd6ad4fe49bda2abcf0 |
52.276943 |
| 0009c8836878a0065a617ba066c5c6ba51a9e29d22c81a8df28be40352efd4bf |
52.1093 |
+-------------------------------------------------------------------+------------+
10 rows selected (0.18 seconds)
The issue is when I add a WHERE statement to perform a kind of selection
e.g. only the rows with lat>52.0
I do the following command
SELECT CONVERT_FROM(row_key, 'UTF8') as key,
CAST(fogDecDB.Location.latitude AS FLOAT) as lat FROM fogDecDB WHERE
fogDecDB.Location.latitude>52 LIMIT 10;
+------+------+
| key | lat |
+------+------+
+------+------+
Empty table, very weird...I have tried to CAST also in the WHERE statement
the fogDecDB.Location.latitude but same result.
Can anyone please shed some light on the issue. I of course googled a lot
with no luck.
Many thanks in advance.
Cheers,
Andrea
Re: Problem in WHERE clause querying HBase
Posted by Dechang Gu <dg...@mapr.com>.
Hi Andrea,
Thank you for trying out drill and identify the issue.
Column names in HBase should be case-sensitive, according to drill doc:
https://drill.apache.org/docs/lexical-structure/
Please file a jira to keep track of the issue, and drill dev will fix it.
Thanks,
Dechang
Lexical Structure - Apache Drill<https://drill.apache.org/docs/lexical-structure/>
drill.apache.org
A SQL statement used in Drill can include one or more of the following parts: Clause, such as FROM Command, such as SELECT Expression, a combination of one or more ...
________________________________
From: G.A. Pagani <g....@rug.nl>
Sent: Tuesday, February 7, 2017 3:34 AM
To: user@drill.apache.org
Subject: Re: Problem in WHERE clause querying HBase
Hi all again,
I have identified the issue and the behavior and the reason for the error:
My HBase schema for that column I am trying to select is actually:
fogDecDB.Location.Latitute *with a capital "L"*
however it gets somehow identified and properly parsed in the SELECT part
of the statement even if it is not correct (Lexicographically/string
matching speaking) and in the WHERE part it gets somehow not recognized
anymore.
My suggestion to feed to the devel team is to have a homogeneous behavior
and issue a warning for the SELECT part that a "string-matched" column is
inferred.
Thanks,
Andrea
On Tue, Feb 7, 2017 at 10:35 AM, G.A. Pagani <g....@rug.nl> wrote:
> Hi all,
>
> I am a newbie in Drill and Hbase.
> I have installed Hbase 1.2.1 and Drill 1.9.0 both on a embedded (single
> host) configuration. I have enabled the HBase plugin with default
> parameters.
> I have a table that I manage to connect to and retrieve:
>
> SELECT CONVERT_FROM(row_key, 'UTF8') as key, CAST(fogDecDB.Location.latitude
> AS FLOAT) as lat FROM fogDecDB LIMIT 10;
> +-----------------------------------------------------------
> --------+------------+
> | key |
> lat |
> +-----------------------------------------------------------
> --------+------------+
> | 00001e14dbf570471418f1c26384637a3154561bfc32062bbb416bedc165841d |
> 51.971 |
> | 0000c0ad64df90c8feebd4e191272413d01989c997d0e2aec34cf259f3207ddb |
> 52.276943 |
> | 00023c7cf936bd1933f8b0f690ae3f347d3fd3131278b4e0372d34dde9586b62 |
> 52.276943 |
> | 0003164d842b488a1a0d8572ae06b983ceb3a645f41531acfe4962ad7ede80a2 |
> 52.276943 |
> | 0003bd065e59d03f3907531279579025f49282a3aa1734d1c4d63df891420c58 |
> 52.1093 |
> | 0006963d79ccf3a71f086f77b26a4db7da09a77c8b2c523fc51e04df4087abbd |
> 52.1093 |
> | 00069cd1f16dde18f4b6944c81015e4f7113ae447c02743a903814c0e64bb2e8 |
> 52.1093 |
> | 00070d2b641fc506be01c4b0b15f102d01b43d34f0b98f054154d20ea1ad7aa1 |
> 52.1093 |
> | 0007fd3e7db369f258173e7d47c13f81e3cbb48ecf446dd6ad4fe49bda2abcf0 |
> 52.276943 |
> | 0009c8836878a0065a617ba066c5c6ba51a9e29d22c81a8df28be40352efd4bf |
> 52.1093 |
> +-----------------------------------------------------------
> --------+------------+
> 10 rows selected (0.18 seconds)
>
> The issue is when I add a WHERE statement to perform a kind of selection
> e.g. only the rows with lat>52.0
> I do the following command
> SELECT CONVERT_FROM(row_key, 'UTF8') as key, CAST(fogDecDB.Location.latitude
> AS FLOAT) as lat FROM fogDecDB WHERE fogDecDB.Location.latitude>52 LIMIT
> 10;
> +------+------+
> | key | lat |
> +------+------+
> +------+------+
>
> Empty table, very weird...I have tried to CAST also in the WHERE statement
> the fogDecDB.Location.latitude but same result.
>
> Can anyone please shed some light on the issue. I of course googled a lot
> with no luck.
>
>
> Many thanks in advance.
>
> Cheers,
>
> Andrea
>
>
>
>
>
>
Re: Problem in WHERE clause querying HBase
Posted by "G.A. Pagani" <g....@rug.nl>.
Hi all again,
I have identified the issue and the behavior and the reason for the error:
My HBase schema for that column I am trying to select is actually:
fogDecDB.Location.Latitute *with a capital "L"*
however it gets somehow identified and properly parsed in the SELECT part
of the statement even if it is not correct (Lexicographically/string
matching speaking) and in the WHERE part it gets somehow not recognized
anymore.
My suggestion to feed to the devel team is to have a homogeneous behavior
and issue a warning for the SELECT part that a "string-matched" column is
inferred.
Thanks,
Andrea
On Tue, Feb 7, 2017 at 10:35 AM, G.A. Pagani <g....@rug.nl> wrote:
> Hi all,
>
> I am a newbie in Drill and Hbase.
> I have installed Hbase 1.2.1 and Drill 1.9.0 both on a embedded (single
> host) configuration. I have enabled the HBase plugin with default
> parameters.
> I have a table that I manage to connect to and retrieve:
>
> SELECT CONVERT_FROM(row_key, 'UTF8') as key, CAST(fogDecDB.Location.latitude
> AS FLOAT) as lat FROM fogDecDB LIMIT 10;
> +-----------------------------------------------------------
> --------+------------+
> | key |
> lat |
> +-----------------------------------------------------------
> --------+------------+
> | 00001e14dbf570471418f1c26384637a3154561bfc32062bbb416bedc165841d |
> 51.971 |
> | 0000c0ad64df90c8feebd4e191272413d01989c997d0e2aec34cf259f3207ddb |
> 52.276943 |
> | 00023c7cf936bd1933f8b0f690ae3f347d3fd3131278b4e0372d34dde9586b62 |
> 52.276943 |
> | 0003164d842b488a1a0d8572ae06b983ceb3a645f41531acfe4962ad7ede80a2 |
> 52.276943 |
> | 0003bd065e59d03f3907531279579025f49282a3aa1734d1c4d63df891420c58 |
> 52.1093 |
> | 0006963d79ccf3a71f086f77b26a4db7da09a77c8b2c523fc51e04df4087abbd |
> 52.1093 |
> | 00069cd1f16dde18f4b6944c81015e4f7113ae447c02743a903814c0e64bb2e8 |
> 52.1093 |
> | 00070d2b641fc506be01c4b0b15f102d01b43d34f0b98f054154d20ea1ad7aa1 |
> 52.1093 |
> | 0007fd3e7db369f258173e7d47c13f81e3cbb48ecf446dd6ad4fe49bda2abcf0 |
> 52.276943 |
> | 0009c8836878a0065a617ba066c5c6ba51a9e29d22c81a8df28be40352efd4bf |
> 52.1093 |
> +-----------------------------------------------------------
> --------+------------+
> 10 rows selected (0.18 seconds)
>
> The issue is when I add a WHERE statement to perform a kind of selection
> e.g. only the rows with lat>52.0
> I do the following command
> SELECT CONVERT_FROM(row_key, 'UTF8') as key, CAST(fogDecDB.Location.latitude
> AS FLOAT) as lat FROM fogDecDB WHERE fogDecDB.Location.latitude>52 LIMIT
> 10;
> +------+------+
> | key | lat |
> +------+------+
> +------+------+
>
> Empty table, very weird...I have tried to CAST also in the WHERE statement
> the fogDecDB.Location.latitude but same result.
>
> Can anyone please shed some light on the issue. I of course googled a lot
> with no luck.
>
>
> Many thanks in advance.
>
> Cheers,
>
> Andrea
>
>
>
>
>
>