You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by AshwinKumar AshwinKumar <aa...@g.clemson.edu> on 2017/10/09 15:42:34 UTC
Need to extract only the values in the field
Hi Team,
I have an elasticsearch view called zips . Below is the table data -
0: jdbc:calcite:model=./elasticsearch2/src/te> select POP from
"elasticsearch".ZIPS;
+----------------------+
| POP |
+----------------------+
| {POP=13367} |
| {POP=1652} |
| {POP=3184} |
| {POP=43704} |
| {POP=2084} |
| {POP=1350} |
| {POP=8194} |
| {POP=1732} |
| {POP=9808} |
| {POP=4441} |
+----------------------+
10 rows selected (0.319 seconds)
Could you please let me know if there is a way to select only the values in
POP field using SQL. Like for eg I need only the integer values like
13367,1652 and so on from the tables. I need to join these values with
another table in postgres schema.
Thanks,
Ashwin
Re: Need to extract only the values in the field
Posted by Christian Beikov <ch...@gmail.com>.
Hey,
I'm not the original author of the ES2 adapter but I must admit, it
seems odd that "select POP FROM "elasticsearch".ZIPS" doesn't work.
Could you try ES5 and tell me if that works for you? I changed a few
things related to projections for my test purposes which might just be
what the ES2 adapter is missing.
Maybe the original ES2 author could step up and explain the rationale
behind the way it works currently?
Mit freundlichen Grüßen,
------------------------------------------------------------------------
*Christian Beikov*
Am 10.10.2017 um 16:47 schrieb AshwinKumar AshwinKumar:
> Hi Christian,
>
> Belowis my json file - {
> "version": "1.0",
> "defaultSchema": "elasticsearch1",
> "schemas": [
> {
> "name": "postgrestest",
> "type": "custom",
> "factory": "org.apache.calcite.adapter.jdbc.JdbcSchema$Factory",
> "operand": {
> "jdbcDriver": "org.postgresql.Driver",
> "jdbcUrl": "jdbc:postgresql://localhost/bigdawg_catalog",
> "jdbcUser": "pguser",
> "jdbcPassword": "test"
> }
> },
> {
> "type": "custom",
> "name": "elasticsearch_raw",
> "factory":
> "org.apache.calcite.adapter.elasticsearch2.Elasticsearch2SchemaFactory",
> "operand": {
> "coordinates": "{'127.0.0.1': 9300}",
> "userConfig": "{'bulk.flush.max.actions': 25,
> 'bulk.flush.max.size.mb': 1}",
> "index": "usa"
> }
> },
> {
> "name": "elasticsearch",
> "tables": [
> {
> "name": "ZIPS",
> "type": "view",
> "sql": [
> "select cast(_MAP['CITY'] AS varchar(20)) AS \"CITY\",\n",
> " cast(_MAP['LOC'][0] AS float) AS \"LONGITUDE\",\n",
> " cast(_MAP['LOC'][1] AS float) AS \"LATITUDE\",\n",
> " cast(_MAP['POP'] AS integer) AS \"POP\",\n",
> " cast(_MAP['STATE'] AS varchar(2)) AS \"STATE\",\n",
> " cast(_MAP['IDNUM'] AS varchar(5)) AS \"ID\"\n",
> "from \"elasticsearch_raw\".\"ZIPS\""
> ]
> }
> ]
> }
>
>
> Earlier I was executing - select POP from "elasticsearch".ZIPS ; I got the
> below error -
>
> 0: jdbc:calcite:model=./elasticsearch2/src/te>* select POP from
> "elasticsearch".ZIPS;*
> +------------+
> | POP |
> +------------+
> java.lang.ClassCastException: java.util.HashMap cannot be cast to
> java.lang.Integer
> at
> org.apache.calcite.avatica.util.AbstractCursor$IntAccessor.getInt(AbstractCursor.java:531)
> at org.apache.calcite.avatica.AvaticaSite.get(AvaticaSite.java:339)
> at
> org.apache.calcite.avatica.AvaticaResultSet.getObject(AvaticaResultSet.java:409)
> at sqlline.Rows$Row.<init>(Rows.java:157)
> at sqlline.IncrementalRows.hasNext(IncrementalRows.java:66)
> at sqlline.TableOutputFormat.print(TableOutputFormat.java:33)
> at sqlline.SqlLine.print(SqlLine.java:1648)
> at sqlline.Commands.execute(Commands.java:834)
> at sqlline.Commands.sql(Commands.java:733)
> at sqlline.SqlLine.dispatch(SqlLine.java:795)
> at sqlline.SqlLine.begin(SqlLine.java:668)
> at sqlline.SqlLine.start(SqlLine.java:373)
> at sqlline.SqlLine.main(SqlLine.java:265)
> 0: jdbc:calcite:model=./elasticsearch2/src/te>
>
> I tried the one which you suggested. I am getting the below error -
>
> 0: jdbc:calcite:model=./elasticsearch2/src/te> *select cast(_MAP['POP'] as
> integer) from "elasticsearch".ZIPS;*
> 2017-10-10 15:44:33,334 [main] ERROR -
> org.apache.calcite.sql.validate.SqlValidatorException: Column '_MAP' not
> found in any table
> 2017-10-10 15:44:33,347 [main] ERROR -
> org.apache.calcite.runtime.CalciteContextException: From line 1, column 13
> to line 1, column 16: Column '_MAP' not found in any table
> Error: Error while executing SQL "select cast(_MAP['POP'] as integer) from
> "elasticsearch".ZIPS": From line 1, column 13 to line 1, column 16: Column
> '_MAP' not found in any table (state=,code=0)
>
> All the varchar fields are working fine. For eg -
>
> 0: jdbc:calcite:model=./elasticsearch2/src/te> select STATE from
> "elasticsearch".ZIPS;
> +-------+
> | STATE |
> +-------+
> | {STATE=MA} |
> | {STATE=MA} |
> | {STATE=MA} |
> | {STATE=MA} |
> | {STATE=MA} |
> | {STATE=MA} |
> | {STATE=MA} |
> | {STATE=MA} |
> | {STATE=MA} |
> | {STATE=MA} |
> +-------+
> 10 rows selected (0.322 seconds)
>
> The issue is with the integer fields. Could you please suggest.
>
> Thanks,
> Ashwin
>
>
>
> On Mon, Oct 9, 2017 at 9:43 PM, Christian Beikov <christian.beikov@gmail.com
>> wrote:
>> The following should do it
>>
>> select cast(_MAP['POP'] as integer) from "elasticsearch".zips
>>
>> In the test of the adapter you can see other usages:
>>
>> https://github.com/apache/calcite/blob/cc20ca13db4d506d9d4d1
>> b861dd1c7ac3944e56e/elasticsearch2/src/test/resources/elasticsearch-zips-
>> model.json#L37
>>
>>
>> Mit freundlichen Grüßen,
>> ------------------------------------------------------------------------
>> *Christian Beikov*
>>
>> Am 09.10.2017 um 19:40 schrieb AshwinKumar AshwinKumar:
>>
>>> Hi Team,
>>>
>>> Could you please help here. I am stuck on this problem for like 2 days
>>> now.
>>>
>>> Thanks,
>>> Ashwin
>>>
>>> On Mon, Oct 9, 2017 at 4:42 PM, AshwinKumar AshwinKumar <
>>> aashwin@g.clemson.edu> wrote:
>>>
>>> Hi Team,
>>>> I have an elasticsearch view called zips . Below is the table data -
>>>>
>>>> 0: jdbc:calcite:model=./elasticsearch2/src/te> select POP from
>>>> "elasticsearch".ZIPS;
>>>> +----------------------+
>>>> | POP |
>>>> +----------------------+
>>>> | {POP=13367} |
>>>> | {POP=1652} |
>>>> | {POP=3184} |
>>>> | {POP=43704} |
>>>> | {POP=2084} |
>>>> | {POP=1350} |
>>>> | {POP=8194} |
>>>> | {POP=1732} |
>>>> | {POP=9808} |
>>>> | {POP=4441} |
>>>> +----------------------+
>>>> 10 rows selected (0.319 seconds)
>>>>
>>>> Could you please let me know if there is a way to select only the values
>>>> in POP field using SQL. Like for eg I need only the integer values like
>>>> 13367,1652 and so on from the tables. I need to join these values with
>>>> another table in postgres schema.
>>>>
>>>> Thanks,
>>>> Ashwin
>>>>
>>>>
Re: Need to extract only the values in the field
Posted by AshwinKumar AshwinKumar <aa...@g.clemson.edu>.
Hi Christian,
Belowis my json file - {
"version": "1.0",
"defaultSchema": "elasticsearch1",
"schemas": [
{
"name": "postgrestest",
"type": "custom",
"factory": "org.apache.calcite.adapter.jdbc.JdbcSchema$Factory",
"operand": {
"jdbcDriver": "org.postgresql.Driver",
"jdbcUrl": "jdbc:postgresql://localhost/bigdawg_catalog",
"jdbcUser": "pguser",
"jdbcPassword": "test"
}
},
{
"type": "custom",
"name": "elasticsearch_raw",
"factory":
"org.apache.calcite.adapter.elasticsearch2.Elasticsearch2SchemaFactory",
"operand": {
"coordinates": "{'127.0.0.1': 9300}",
"userConfig": "{'bulk.flush.max.actions': 25,
'bulk.flush.max.size.mb': 1}",
"index": "usa"
}
},
{
"name": "elasticsearch",
"tables": [
{
"name": "ZIPS",
"type": "view",
"sql": [
"select cast(_MAP['CITY'] AS varchar(20)) AS \"CITY\",\n",
" cast(_MAP['LOC'][0] AS float) AS \"LONGITUDE\",\n",
" cast(_MAP['LOC'][1] AS float) AS \"LATITUDE\",\n",
" cast(_MAP['POP'] AS integer) AS \"POP\",\n",
" cast(_MAP['STATE'] AS varchar(2)) AS \"STATE\",\n",
" cast(_MAP['IDNUM'] AS varchar(5)) AS \"ID\"\n",
"from \"elasticsearch_raw\".\"ZIPS\""
]
}
]
}
Earlier I was executing - select POP from "elasticsearch".ZIPS ; I got the
below error -
0: jdbc:calcite:model=./elasticsearch2/src/te>* select POP from
"elasticsearch".ZIPS;*
+------------+
| POP |
+------------+
java.lang.ClassCastException: java.util.HashMap cannot be cast to
java.lang.Integer
at
org.apache.calcite.avatica.util.AbstractCursor$IntAccessor.getInt(AbstractCursor.java:531)
at org.apache.calcite.avatica.AvaticaSite.get(AvaticaSite.java:339)
at
org.apache.calcite.avatica.AvaticaResultSet.getObject(AvaticaResultSet.java:409)
at sqlline.Rows$Row.<init>(Rows.java:157)
at sqlline.IncrementalRows.hasNext(IncrementalRows.java:66)
at sqlline.TableOutputFormat.print(TableOutputFormat.java:33)
at sqlline.SqlLine.print(SqlLine.java:1648)
at sqlline.Commands.execute(Commands.java:834)
at sqlline.Commands.sql(Commands.java:733)
at sqlline.SqlLine.dispatch(SqlLine.java:795)
at sqlline.SqlLine.begin(SqlLine.java:668)
at sqlline.SqlLine.start(SqlLine.java:373)
at sqlline.SqlLine.main(SqlLine.java:265)
0: jdbc:calcite:model=./elasticsearch2/src/te>
I tried the one which you suggested. I am getting the below error -
0: jdbc:calcite:model=./elasticsearch2/src/te> *select cast(_MAP['POP'] as
integer) from "elasticsearch".ZIPS;*
2017-10-10 15:44:33,334 [main] ERROR -
org.apache.calcite.sql.validate.SqlValidatorException: Column '_MAP' not
found in any table
2017-10-10 15:44:33,347 [main] ERROR -
org.apache.calcite.runtime.CalciteContextException: From line 1, column 13
to line 1, column 16: Column '_MAP' not found in any table
Error: Error while executing SQL "select cast(_MAP['POP'] as integer) from
"elasticsearch".ZIPS": From line 1, column 13 to line 1, column 16: Column
'_MAP' not found in any table (state=,code=0)
All the varchar fields are working fine. For eg -
0: jdbc:calcite:model=./elasticsearch2/src/te> select STATE from
"elasticsearch".ZIPS;
+-------+
| STATE |
+-------+
| {STATE=MA} |
| {STATE=MA} |
| {STATE=MA} |
| {STATE=MA} |
| {STATE=MA} |
| {STATE=MA} |
| {STATE=MA} |
| {STATE=MA} |
| {STATE=MA} |
| {STATE=MA} |
+-------+
10 rows selected (0.322 seconds)
The issue is with the integer fields. Could you please suggest.
Thanks,
Ashwin
On Mon, Oct 9, 2017 at 9:43 PM, Christian Beikov <christian.beikov@gmail.com
> wrote:
> The following should do it
>
> select cast(_MAP['POP'] as integer) from "elasticsearch".zips
>
> In the test of the adapter you can see other usages:
>
> https://github.com/apache/calcite/blob/cc20ca13db4d506d9d4d1
> b861dd1c7ac3944e56e/elasticsearch2/src/test/resources/elasticsearch-zips-
> model.json#L37
>
>
> Mit freundlichen Grüßen,
> ------------------------------------------------------------------------
> *Christian Beikov*
>
> Am 09.10.2017 um 19:40 schrieb AshwinKumar AshwinKumar:
>
>> Hi Team,
>>
>> Could you please help here. I am stuck on this problem for like 2 days
>> now.
>>
>> Thanks,
>> Ashwin
>>
>> On Mon, Oct 9, 2017 at 4:42 PM, AshwinKumar AshwinKumar <
>> aashwin@g.clemson.edu> wrote:
>>
>> Hi Team,
>>>
>>> I have an elasticsearch view called zips . Below is the table data -
>>>
>>> 0: jdbc:calcite:model=./elasticsearch2/src/te> select POP from
>>> "elasticsearch".ZIPS;
>>> +----------------------+
>>> | POP |
>>> +----------------------+
>>> | {POP=13367} |
>>> | {POP=1652} |
>>> | {POP=3184} |
>>> | {POP=43704} |
>>> | {POP=2084} |
>>> | {POP=1350} |
>>> | {POP=8194} |
>>> | {POP=1732} |
>>> | {POP=9808} |
>>> | {POP=4441} |
>>> +----------------------+
>>> 10 rows selected (0.319 seconds)
>>>
>>> Could you please let me know if there is a way to select only the values
>>> in POP field using SQL. Like for eg I need only the integer values like
>>> 13367,1652 and so on from the tables. I need to join these values with
>>> another table in postgres schema.
>>>
>>> Thanks,
>>> Ashwin
>>>
>>>
>
Re: Need to extract only the values in the field
Posted by Christian Beikov <ch...@gmail.com>.
The following should do it
select cast(_MAP['POP'] as integer) from "elasticsearch".zips
In the test of the adapter you can see other usages:
https://github.com/apache/calcite/blob/cc20ca13db4d506d9d4d1b861dd1c7ac3944e56e/elasticsearch2/src/test/resources/elasticsearch-zips-model.json#L37
Mit freundlichen Grüßen,
------------------------------------------------------------------------
*Christian Beikov*
Am 09.10.2017 um 19:40 schrieb AshwinKumar AshwinKumar:
> Hi Team,
>
> Could you please help here. I am stuck on this problem for like 2 days now.
>
> Thanks,
> Ashwin
>
> On Mon, Oct 9, 2017 at 4:42 PM, AshwinKumar AshwinKumar <
> aashwin@g.clemson.edu> wrote:
>
>> Hi Team,
>>
>> I have an elasticsearch view called zips . Below is the table data -
>>
>> 0: jdbc:calcite:model=./elasticsearch2/src/te> select POP from
>> "elasticsearch".ZIPS;
>> +----------------------+
>> | POP |
>> +----------------------+
>> | {POP=13367} |
>> | {POP=1652} |
>> | {POP=3184} |
>> | {POP=43704} |
>> | {POP=2084} |
>> | {POP=1350} |
>> | {POP=8194} |
>> | {POP=1732} |
>> | {POP=9808} |
>> | {POP=4441} |
>> +----------------------+
>> 10 rows selected (0.319 seconds)
>>
>> Could you please let me know if there is a way to select only the values
>> in POP field using SQL. Like for eg I need only the integer values like
>> 13367,1652 and so on from the tables. I need to join these values with
>> another table in postgres schema.
>>
>> Thanks,
>> Ashwin
>>
Re: Need to extract only the values in the field
Posted by AshwinKumar AshwinKumar <aa...@g.clemson.edu>.
Hi Team,
Could you please help here. I am stuck on this problem for like 2 days now.
Thanks,
Ashwin
On Mon, Oct 9, 2017 at 4:42 PM, AshwinKumar AshwinKumar <
aashwin@g.clemson.edu> wrote:
> Hi Team,
>
> I have an elasticsearch view called zips . Below is the table data -
>
> 0: jdbc:calcite:model=./elasticsearch2/src/te> select POP from
> "elasticsearch".ZIPS;
> +----------------------+
> | POP |
> +----------------------+
> | {POP=13367} |
> | {POP=1652} |
> | {POP=3184} |
> | {POP=43704} |
> | {POP=2084} |
> | {POP=1350} |
> | {POP=8194} |
> | {POP=1732} |
> | {POP=9808} |
> | {POP=4441} |
> +----------------------+
> 10 rows selected (0.319 seconds)
>
> Could you please let me know if there is a way to select only the values
> in POP field using SQL. Like for eg I need only the integer values like
> 13367,1652 and so on from the tables. I need to join these values with
> another table in postgres schema.
>
> Thanks,
> Ashwin
>