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 <[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
