I have Presto set-up done locally with mongodb connector and below is the catalog mongodb properties:
connector.name=mongodb
mongodb.seeds=my-mongodb:27017
mongodb.credentials=admin:password@mydb
mongodb.ssl.enabled=false
mongodb.case-insensitive-name-matching=true
One of the Collections is local_geodata and it has few columns and has around 5 records in it. However, when tried to query through Presto cli, it was throwing me an error as below:
presto> use mongodb.mydb;
USE
presto:mydb> show tables;
Table
-----------------------------------------------------------------------------------
local_geodata
presto_schema
(2 rows)
presto:mydb> select * from local_geodata;
Query 20220213_152931_00039_3h2ec failed: Table has no columns: TableHandle {connectorId='mongodb', connectorHandle='mydb.local_geodata', layout='Optional.empty'}
Based on the documentation, to let Presto understand the schema to my collection, I went ahead and created a new collection presto_schema and added a new entry into this collection with the local_geodata collection schema and updated the catalog mongodb.properties with new entry mongodb.schema-collection=presto_schema, however, this time it identifies the Collection, but returns 0 records (The collection got 5 records in it).
Any inputs here please?
Presto Version: 0.269
MongoDB Version: 4.4
Data inside presto_schema
presto> select * from mongodb.geosnipe.presto_schema;
table | fields
------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
local_geodata | [{name=_id, type=ObjectId, hidden=true}, {name=city, type=varchar, hidden=false}, {name=country, type=varchar, hidden=false}, {name=ip, type=varchar, hidden=false}]
presto_schema | [{name=_id, type=ObjectId, hidden=true}, {name=table, type=varchar, hidden=false}, {name=fields, type=array(row(name varchar,type varchar,hidden boolean)), hidden=false}]
(2 rows)
Did a close look at all my set-up and it seems I found the culprit and it is the collection name, which I named it as local_geoData and changing it to local_geodata (all small letters), it started returning the data.