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 <[email protected]
> 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 <
>> [email protected]> 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
>>>
>>>
>
