1 2 3 Previous Next 34 Replies Latest reply on Dec 16, 2014 3:15 PM by Ramesh Reddy Go to original post
      • 16. Re: External Materialization with Postgresql
        tu nguyen Newbie

        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
          Ramesh Reddy Master

          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
            tu nguyen Newbie

            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
              Ramesh Reddy Master

              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
                tu nguyen Newbie

                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
                  Ramesh Reddy Master

                  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
                    tu nguyen Newbie

                    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?

                    • 25. Re: External Materialization with Postgresql
                      tu nguyen Newbie

                      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
                        Steven Hawkins Master

                        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
                          tu nguyen Newbie

                          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
                            Ramesh Reddy Master

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

                            • 29. Re: External Materialization with Postgresql
                              tu nguyen Newbie

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