1 2 3 Previous Next 34 Replies Latest reply on Dec 16, 2014 3:15 PM by rareddy Go to original post
      • 15. Re: External Materialization with Postgresql
        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.

        • 16. Re: External Materialization with Postgresql
          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] }

          • 17. Re: Re: External Materialization with Postgresql
            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..

            • 18. Re: External Materialization with Postgresql
              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?

              • 19. Re: External Materialization with Postgresql
                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.

                • 20. Re: External Materialization with Postgresql
                  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

                  • 21. Re: External Materialization with Postgresql
                    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"

                    • 23. Re: External Materialization with Postgresql
                      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?

                      • 24. Re: External Materialization with Postgresql
                        rareddy
                        • 25. Re: External Materialization with Postgresql
                          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 (?)

                          • 26. Re: External Materialization with Postgresql
                            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.

                            • 27. Re: External Materialization with Postgresql
                              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?

                              • 28. Re: External Materialization with Postgresql
                                rareddy

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

                                • 29. Re: External Materialization with Postgresql
                                  tunguyen825

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