[rareddy]

[TEIID-3224] MongoDB: support direct query execution for Shell type command - JBoss Issue Tracker

is now resolved, it will be available in 8.10.Alpha1 build next week or so.

[tunguyen825]

Hi Ramesh,

i saw you have  geo spatial support for MongoDB when the data is stored in the GeoJSon format. My question is how to convert latitude and longitude to this format when translating data from a relational database to mongoDB?

In my case, i only need point format: { "type": "Point", "coordinates": [100.0, 0.0] }

[rareddy]

I only provided geoSpatial functions in the MongoDB, not the geo spatial data types. There is another member is trying to do that WFS translator & spatial, once we have that then there will be data types to represent that geo types automatically.

For now, I would say create the MongoDB table called "Geo" like (look at one-2-one association at MongoDB Translator - Teiid 8.10 (draft) - Project Documentation Editor)

CREATE FOREIGN TABLE  Location (
    addressId integer PRIMARY KEY,
    street varchar(25),
) OPTIONS(UPDATABLE 'TRUE');

CREATE FOREIGN TABLE geo (
    addressId integer PRIMARY KEY,
    type varchar(50),
    coordinates double[],
    FOREIGN KEY (addressId) REFERENCES Location (addressId)
) OPTIONS(UPDATABLE 'TRUE', "teiid_mongo:MERGE" 'Location');

then once you deploy the VDB with above model, you can issue

insert into Location (addressId, street) values (1, '1st street');
insert into geo(addressId, type, coordinates) values (1, 'Point', (100.0, 0.0))

will produce a document in the MongoDB as

Location
{
  _id: 1,
  street: "1st street",
  geo:
    { 
        type: "Point",
        coordinates: [100.0, 0.0]
  }
}

that is in geoJson format.

HTH

Ramesh..

[tunguyen825]

Thanks, i tried this approach but i can't issue "insert into" with external mat view, should i have 2 mat views?

Furthermore, i need to convert the coordinates from milliseconds to degree and to build the format of "[100.0, 0.0]". Therefore, i think i need to write my own function to produce the geoJson format, I'm trying to do the following but it doesn't work.

CREATE VIRTUAL FUNCTION getGeoJSON(lat long, lon long) RETURNS varchar(1000) AS

    BEGIN

         RETURN  'convert lat/lon to degree and [lon, lat] format';

    END;   

From what i read, we have to build this function in java and call it when creating the mat view, right?

[rareddy]

Thanks, i tried this approach but i can't issue "insert into" with external mat view, should i have 2 mat views?

The above table structure treated in Teiid as two views, so you would need two different views. In MongoDB they will be in the single document.

You can try "create virtual procedure" that should allow you to do what you are trying to do without java code in virtual procedure language.

[tunguyen825]

Thanks, it works out very well with virtual procedure.

I tried to use the geo spatial function support you have but getting this error:

TEIID30068 Remote org.teiid.api.exception.query.QueryResolverException: TEIID30068 The function

'near(geo, (cast(43.2 AS double), cast(33.97 AS double)), 100)' is an unknown form.  Check that the function name and number of arguments is correct.

My query: select sk, geo from table where near(geo,(cast(43.2 as double),cast(33.97 as double)),100)

From your query sample:

SELECT loc FROM maps where mongo.geoWithin(loc, 'LineString', ((cast(1.0 as double), cast(2.0 as double)), (cast(1.0 as double), cast(2.0 as double))))

I'm not sure what "mongo" entity come from? And also for the "near" function, what is the unit of measure for the maxdistance variable?

ps: i'm using version 8.8.1

[rareddy]

Since all geo functions are MongoDB source specific functions, they need a namespace qualifier, that is "mongo". You need to be using "geoNear" not "near", I think I documented it wrong. maxdistance is defined in "integer"

[rareddy]

I also added [TEIID-3247] MongoDB: Add "MinDistance" to near and nearSphere geo spatial functions - JBoss Issue Tracker

[tunguyen825]

same error when i use: mongo.geoNear(geo,(cast(43.2 as double),cast(33.97 as double)),100)

the maxdistance variable is measured in meter?

[rareddy]

The support is introduced in Teiid 8.9 as defined here [TEIID-3038] Add spatial query support to the Teiid MongoDB translator - JBoss Issue Tracker

[tunguyen825]

I upgraded to Teiid 8.10alpha1 (EAP 6.3.0) to try out external materialization for mongodb. I got  status "FAILED_LOAD" and jboss server error when deploying the VDB:

16:26:07,039 WARN  [org.teiid.PROCESSOR.MATVIEWS] (teiid-async-threads - 4) null TEIID50100 Trigger execution result: org.jboss.as.controller.OperationFailedException [ "TEIID30168 Couldn't execute the dynamic SQL command \"EXECUTE IMMEDIATE ((updateStmt || ' AND loadNumber = ') || matcursor.loadNumber) AS \"rows\" integer INTO #updated USING loadNumber = (matcursor.loadNumber + 1), vdbName = VARIABLES.vdbName, vdbVersion = VARIABLES.vdbVersion, schemaName = schemaName, viewName = viewName, updated = now(), LoadState = 'LOADING', valid = (matcursor.valid) AND (NOT (invalidate)), cardinality = matcursor.cardinality\" with the SQL statement \"((updateStmt || ' AND loadNumber = ') || matcursor.loadNumber)\" due to: TEIID31157 Executed an update with dynamic sql, but expecting a result set.  Please ensure that the dynamic sql is correct and/or use WITHOUT RETURN and no AS columns to indicate no result set is expected." ]: org.teiid.jdbc.TeiidSQLException: TEIID50100 Trigger execution result: org.jboss.as.controller.OperationFailedException [ "TEIID30168 Couldn't execute the dynamic SQL command \"EXECUTE IMMEDIATE ((updateStmt || ' AND loadNumber = ') || matcursor.loadNumber) AS \"rows\" integer INTO #updated USING loadNumber = (matcursor.loadNumber + 1), vdbName = VARIABLES.vdbName, vdbVersion = VARIABLES.vdbVersion, schemaName = schemaName, viewName = viewName, updated = now(), LoadState = 'LOADING', valid = (matcursor.valid) AND (NOT (invalidate)), cardinality = matcursor.cardinality\" with the SQL statement \"((updateStmt || ' AND loadNumber = ') || matcursor.loadNumber)\" due to: TEIID31157 Executed an update with dynamic sql, but expecting a result set.  Please ensure that the dynamic sql is correct and/or use WITHOUT RETURN and no AS columns to indicate no result set is expected." ]

    at org.teiid.jboss.MaterializationManagementService$1.executeQuery(MaterializationManagementService.java:104) [teiid-jboss-integration-8.10.0.Alpha1.jar:8.10.0.Alpha1]

    at org.teiid.runtime.MaterializationManager$QueryJob$1.run(MaterializationManager.java:267) [teiid-runtime-8.10.0.Alpha1.jar:8.10.0.Alpha1]

    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_51]

    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_51]

    at java.lang.Thread.run(Thread.java:744) [rt.jar:1.7.0_51]

    at org.jboss.threads.JBossThread.run(JBossThread.java:122)

My mat view set up:

.... OPTIONS (MATERIALIZED 'TRUE', UPDATABLE 'TRUE',

                MATERIALIZED_TABLE 'mongo.mv_table',

                "teiid_rel:MATERIALIZED_STAGE_TABLE" 'mongo.mv_table_staging',

                "teiid_rel:MATVIEW_TTL" 120000,

                "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',

                "teiid_rel:MATVIEW_STATUS_TABLE" 'mongo.status',

                "teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'call native(''$ShellCmd;mv_table_staging;remove;{}'');',

                "teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" 'call native(''$ShellCmd;mv_table;copyTo;temp_collection'');call native(''$ShellCmd;mv_table_staging;copyTo;mv_table'');call native(''$ShellCmd;temp_collection;copyTo;mv_table_staging'')',

                "teiid_rel:MATVIEW_SHARE_SCOPE" 'VDB',

                "teiid_rel:MATVIEW_ONERROR_ACTION" 'THROW_EXCEPTION',

                "teiid_rel:ON_VDB_DROP_SCRIPT" '')

     AS select ....

I haven't seen this error before, it still exists even when i remove the native calls (?)

[shawkins]

Yes there is an issue there.  [TEIID-3198] NPE on running an "execute immediate" command defined in a virtual procedure (Teiid-8.9.0-CR2) - JBoss Issu… made the validation regarding dynamic sql more strict, so we need to update the materialization script.

[tunguyen825]

Thanks, please let me know when it is fixed.

We have a requirement to publish content of external mat view (mongodb collection) to a message queue. Does current version support this feature? Or else, do we need to extend mongodb translator or the materialization script to achieve this?

[rareddy]

Can you open JIRA on this bug, we will try to get it in asap in 8.10

[tunguyen825]

Could you verify the jira bug is for the earlier post or for the message feature?