11 Replies Latest reply on Jul 25, 2019 11:29 AM by Ramesh Reddy

    How to connect to json data file using ftp or sftp connection using teiid

    Pranita Gurav Newbie

      We need to query json data file kept at any location so can you please help us, how to connect to json file using teiid.

       

      To connect to text,csv file we were using HXTT driver but using HXTT driver we are unable to connect to JSON file.

       

      Could yo uplease suggect any driver using which we can connect to JSON file using ftp/sftp connection.

       

      Thanks in advance,

      Pranita.

        • 1. Re: How to connect to json data file using ftp or sftp connection using teiid
          Ramesh Reddy Master

          You can still use the "file" translator, but instead of file resource adapter you need to use the FTP resource adapter. See here Ftp/Ftps Data Sources · GitBook

           

          Once you have the JSON file, you need to use either XMLTABLE, JSONTABLE (jsontable only from version 12.2) construct to parse the contents and turn into relational table.

          • 2. Re: How to connect to json data file using ftp or sftp connection using teiid
            Pranita Gurav Newbie

            Hi Ramesh,

             

            Using file translator and FTP resource adapter I am able to connect to FTP server, but not able to fetch metadata of json file.

             

            Resource-Adapter

            <resource-adapter id="jsonModel">

                                <module slot="main" id="org.jboss.teiid.resource-adapter.ftp"/>

                                <transaction-support>NoTransaction</transaction-support>

                                <connection-definitions>

                                    <connection-definition class-name="org.teiid.resource.adapter.ftp.FtpManagedConnectionFactory" jndi-name="java:/jsonModel" enabled="true" pool-name="jsonModel">

                                        <config-property name="Username">

                                            <ftp_username>

                                        </config-property>

                                        <config-property name="ParentDirectory">

                                            <file_loction_parent_directory>

                                        </config-property>

                                        <config-property name="Host">

                                            <ftp_host>

                                        </config-property>

                                        <config-property name="Password">

                                            <ftp_pwd>

                                        </config-property>

                                        <config-property name="Port">

                                            <ftp_port>

                                        </config-property>

                                    </connection-definition>

                                </connection-definitions>

             

                            </resource-adapter>

             

            VDB details:

            <?xml version="1.0" ?><vdb name="json" version="1"><description>VDB for: json, Version: 1</description><connection-type>BY_VERSION</connection-type><model name="json" type="PHYSICAL" visible="true"><property name="importer.tableTypes" value="TABLE"></property><property name="importer.importKeys" value="false"></property><source name="json" translator-name="file" connection-jndi-name="java:/jsonModel"></source></model></vdb>

             

            Please correct me if I have generated wrong VDB.

             

            As you mentioned above you need to use  XMLTABLE, JSONTABLE (jsontable only from version 12.2) construct to parse the contents and turn into relational table.

            Could you please eloberate more on this as I am beginner for resource adapter concept.

            We will not be able to provide DDL earliar in VDB, as we need to connect to dynamic files at runtime.

            Do we need to provide schema file?

             

            We are using AdminAPI 11.1.2 to create Datasource and create VDB dynamicaly using java code.

            Could you please help me in this.

             

            Thanks in advance,

            Pranita.

            • 3. Re: How to connect to json data file using ftp or sftp connection using teiid
              Ramesh Reddy Master

              Pranita,

               

              The resource-adapter only get you access to the JSON file using FTP. A translator is one that converts the access to something meaningful. In the case of "file" translator, it does not help you much with sniffing the format of the file and getting you a readymade table. This is where you need to use XMLTABLE/JSONTABLE like constructs like this [1]. However, that means that in the VDB you created that definition of the View needs to be inserted into a Virtual schema. For this you need to know the format of the JSON upfront so that you can autogenerate. so something like

               

              <?xml version="1.0" encoding="UTF-8"?>

              <vdb name="json" version="1">

                 <description>VDB for: json, Version: 1</description>

                 <connection-type>BY_VERSION</connection-type>

                 <model name="json" type="PHYSICAL" visible="true">

                    <source name="json" translator-name="file" connection-jndi-name="java:/jsonModel" />

                 </model>

                <model visible="true" type="VIRTUAL" name="view-model">

                  <metadata type="DDL"><![CDATA[

                      CREATE VIEW MyTable AS

                        SELECT t.timezone AS id

                          FROM (EXEC json.getFiles('myfile.json')) AS f, 

                          jsontable(f.file, '$' COLUMNS first string, second integer) x;

                  ]]>

                  </metadata>

                </model>

              </vdb>

               

              You can find how to use JSONTABLE here [3] and XMLTABLE [4]

               

              If you do not want to write the VDB, you can also define the structure of the JSON in a Java Class file, however for this you need to use Teiid Spring Boot. See this example [2], note that this model uses an embedded Teiid server, depending upon your usecase it may or may not suit your needs. This uses the same engine, but the model is completely different.

               

              Let me know how it goes.

               

              Ramesh..

               

              [1] Teiid import Json

              [2] teiid-spring-boot/samples/json at master · teiid/teiid-spring-boot · GitHub

              [3] JSONTABLE · GitBook

              [4] XMLTABLE · GitBook

              • 4. Re: How to connect to json data file using ftp or sftp connection using teiid
                Pranita Gurav Newbie

                Hi Ramesh,

                I am working on the above solution provided by you.

                 

                <?xml version="1.0" encoding="UTF-8"?>

                <vdb name="json" version="1">

                   <description>VDB for: json, Version: 1</description>

                   <connection-type>BY_VERSION</connection-type>

                   <model name="json" type="PHYSICAL" visible="true">

                      <source name="json" translator-name="file" connection-jndi-name="java:/jsonModel" />

                   </model>

                  <model visible="true" type="VIRTUAL" name="view-model">

                    <metadata type="DDL"><![CDATA[

                        CREATE VIEW MyTable AS

                          SELECT t.timezone AS id

                            FROM (EXEC json.getFiles('myfile.json')) AS f,

                            jsontable(f.file, '$' COLUMNS first string, second integer) x;

                    ]]>

                    </metadata>

                  </model>

                </vdb>

                 

                Could you please help me with how to create metadata with nested json data.

                 

                Example:

                {"movieCollection": [

                    {

                        "type": "Action Movie",

                        "name": "Brave Heart",

                        "details": {

                            "release": "1995",

                            "rating": "5",

                            "starring": "Mel Gibson"

                        }

                    },

                    {

                        "type": "Action Movie",

                        "name": "Edge of Darkness",

                        "details": {

                            "release": "2010",

                            "rating": "5",

                            "starring": "Mel Gibson"

                        }

                    }

                ]}

                • 5. Re: How to connect to json data file using ftp or sftp connection using teiid
                  Ramesh Reddy Master

                  With JSONTABLE I could get to all the data as it does not support placing the route of scanning at a child nested document and refer to parent to extract the data. Which I think is very limiting factor in Teiid's implementation of the JSONTABLE. I am not sure if there are any other techniques available for it. So, I went back to using XMLTABLE and JSONTOXML and I can do the following

                   

                  SELECT x.* FROM XMLTABLE('/movies/movieCollection/details' PASSING JSONTOXML('movies', '{
                    "movieCollection":[
                        {
                          "type":"Action Movie",
                          "name":"Brave Heart",
                          "details":{
                              "release":"1995",
                              "rating":"5",
                              "starring":"Mel Gibson"
                          }
                        },
                        {
                          "type":"Action Movie",
                          "name":"Edge of Darkness",
                          "details":{
                              "release":"2010",
                              "rating":"5",
                              "starring":"Mel Gibson"
                          }
                        }
                    ]
                  }')
                  COLUMNS "type" string PATH '../type', name string PATH '../name', "release" integer, rating integer, starring string) as x;

                   

                  and get data like

                   

                  typenamereleaseratingstarring
                  Action MovieBrave Heart19955Mel Gibson
                  Action MovieEdge of Darkness20105Mel Gibson

                   

                   

                  Now converting that to a View You can do like

                  CREATE VIEW Movies AS
                  SELECT x.type, x.name, x.release, x.rating, x.starring 
                    FROM FROM (EXEC json.getFiles('myfile.json')) AS f, 
                    XMLTABLE('/movies/movieCollection/details' PASSING JSONTOXML('movies', f.file),
                    COLUMNS "type" string PATH '../type', name string PATH '../name', "release" integer, rating integer, starring string) as x;

                    

                  Then depending your configuration of FTP and other stuff, when you execute

                  "SELECT * FROM Movies" it should return above result set by reading the frile from FTP and then parsing into a table and returning to you in real time.

                   

                  Hope this helps. Let me know how it goes.

                   

                  Ramesh..

                  • 6. Re: How to connect to json data file using ftp or sftp connection using teiid
                    Pranita Gurav Newbie

                    Hello Ramesh,

                     

                    I have created VDB using XMLTABLE:

                    <?xml version="1.0" ?><vdb name="SvcSourceVdb_json01" version="1"><description>VDB for: SvcSourceVdb_json01, Version: 1</description><connection-type>BY_VERSION</connection-type><model name="json01" type="PHYSICAL" visible="true"><property name="importer.tableTypes" value="TABLE"></property><property name="importer.importKeys" value="false"></property><source name="json01" translator-name="file" connection-jndi-name="java:/DSJSON20190723144550004"></source><metadata type="DDL"><![CDATA[CREATE VIEW movies AS

                    SELECT x.type, x.name, x.release, x.rating, x.starring

                    FROM (EXEC json.getFiles('abc.json')) AS f,

                    XMLTABLE ('/movies/movieCollection/details' PASSING JSONTOXML ('movies', f.file), COLUMNS 'type' string PATH '../type', 'name' string PATH '../name', 'release' integer, 'rating' integer, 'starring' string) as x;

                    ]]></metadata></model></vdb>

                     

                    But while testing connection I am getting below error:

                     

                    36659" metadata failed to load. Reason:TEIID30386 org.teiid.api.exception.query.

                    QueryParserException: TEIID31100 Parsing error: Encountered "), COLUMNS [*]name[

                    *] string PATH" at line 5, column 10.

                    Was expecting: "and" | "as" | "between" | "in" | "is" | "like" | "like_regex" |

                    "not" | "or" | "similar" ...: org.teiid.metadata.ParseException: TEIID30386 org.

                    teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encoun

                    tered "), COLUMNS [*]name[*] string PATH" at line 5, column 10.

                    Was expecting: "and" | "as" | "between" | "in" | "is" | "like" | "like_regex" |

                    "not" | "or" | "similar" ...

                     

                    Could you please help me if I am doing wrong?

                     

                    Thanks In Advance,

                    Pranita

                    • 7. Re: How to connect to json data file using ftp or sftp connection using teiid
                      Ramesh Reddy Master

                      Many errors in your definition of your VDB, you failed to copy as is

                      - 'type' is reserved word, you need to define in double quotes "type", can not be in single quotes

                      - You can not define the columns in single quotes, the values in single quotes are literal string values. The values in double-quotes are identifiers.

                      - You can not define source model and view model in single model. i.e view can not be same model as in source model.

                       

                      <?xml version="1.0" encoding="UTF-8"?>

                      <vdb name="SvcSourceVdb_json01" version="1">

                        <description>VDB for: SvcSourceVdb_json01, Version: 1</description>

                        <connection-type>BY_VERSION</connection-type>

                        <model name="json01" type="PHYSICAL" visible="true">

                            <property name="importer.tableTypes" value="TABLE" />

                            <property name="importer.importKeys" value="false" />

                            <source name="json01" translator-name="file" connection-jndi-name="java:/DSJSON20190723144550004" />

                        </model>

                        <model name="json02" type="VIRTUAL" visible="true">

                            <metadata type="DDL"><![CDATA[CREATE VIEW movies AS

                              SELECT x.type, x.name, x.release, x.rating, x.starring

                              FROM (EXEC json01.getFiles('abc.json')) AS f,

                              XMLTABLE ('/movies/movieCollection/details' PASSING JSONTOXML ('movies', f.file)

                              COLUMNS "type" string PATH '../type', name string PATH '../name', "release" integer, rating integer, starring string) as x;

                            ]]></metadata>

                        </model>

                      </vdb>

                       

                      Ramesh..

                      • 8. Re: How to connect to json data file using ftp or sftp connection using teiid
                        Pranita Gurav Newbie

                        Hi Ramesh,

                         

                        I tried and deploy VDB as per you instruction.

                        I am trying to connect to Linux ftp server.

                        Below are the properties I have set in resources adapter:

                         

                        <resource-adapter id="TESTDSJSON20190724125640107">

                        <module slot="main" id="org.jboss.teiid.resource-adapter.ftp"/>

                          <transaction-support>NoTransaction</transaction-support>

                          <connection-definitions>

                          <connection-definition class-name="org.teiid.resource.adapter.ftp.FtpManagedConnectionFactory" jndi-name="java:/TESTDSJSON20190724125640107" enabled="true" pool-name="TESTDSJSON20190724125640107">

                          <config-property name="Host">

                        <IP of Linux FTP server>

                          </config-property>

                          <config-property name="ParentDirectory">

                          /pranita

                          </config-property>

                          <config-property name="Username">

                        <username>

                          </config-property>

                          <config-property name="Password">

                          <password>

                          </config-property>

                          <config-property name="Port">

                        <Port>

                          </config-property>

                          </connection-definition>

                          </connection-definitions>

                        </resource-adapter>

                        </resource-adapters>

                         

                        JSON Data in file:

                         

                        {"employees": [

                        {"name":"Shyam", "email":"shyamjaiswal@gmail.com"}, 

                        {"name":"Bob", "email":"bob32@gmail.com"},

                        {"name":"Jai", "email":"jai87@gmail.com"}

                        ]}

                         

                        VDB created:

                         

                        <?xml version="1.0" ?>

                        <vdb name="SvcSourceVdb_json08" version="1">

                        <description>VDB for: SvcSourceVdb_json08, Version: 1</description>

                        <connection-type>BY_VERSION</connection-type>

                        <model name="json08" type="PHYSICAL" visible="true">

                        <property name="importer.tableTypes" value="TABLE"></property>

                        <property name="importer.importKeys" value="false"></property>

                        <source name="json08" translator-name="file" connection-jndi-name="java:/DSJSON20190723144550004"></source>

                        </model>

                        <model name="json08_viewModel" type="VIRTUAL" visible="true">

                        <description>json08</description>

                        <metadata type="DDL">

                                   <![CDATA[CREATE VIEW emp AS 

                                       SELECT x.*

                        FROM (EXEC json08.getFiles('empFile.json')) AS f,

                                       XMLTABLE ('/emp/employees' PASSING JSONTOXML('emp', f.file)

                                       COLUMNS name string PATH '../name', email string PATH '../email') as x;]]>

                        </metadata></model></vdb>

                         

                        Query:

                        Select * from emp;

                         

                        Test connection is successful but  while Querying on ‘emp’ table I am facing issue, I have attached error logs.

                        Also I have setup ParentDirectory as /pranita here I have kept json file on Linux server ,but in logs I am getting error as

                        Filesystem already mounted at mount point ""/C:/pranita""

                        Please find attached error logs for more details.

                         

                         

                        • 9. Re: How to connect to json data file using ftp or sftp connection using teiid
                          Ramesh Reddy Master

                          Can you add the following to your above resource-adapter configuration and see if the issue goes away? You need to restart the server after the change.

                           

                          <pool>

                             <min-pool-size>0</min-pool-size>

                             <max-pool-size>1</max-pool-size>

                          </pool>

                          • 10. Re: How to connect to json data file using ftp or sftp connection using teiid
                            Pranita Gurav Newbie

                            I tried with pool setting but facing same issue:

                             

                            When I am trying to run Query first time it is giving below error:

                            2019-07-25 12:40:45,168 WARN [org.teiid.PROCESSOR] (Worker8_QueryProcessorQueue43) alTTE6th5rPw TEIID30020 Processing exception for request alTTE6th5rPw.0 'TEIID30504 json11: Can not open multiple ftp stream based file in one connection'. Originally TeiidProcessingException FtpFileConnectionImpl.java:81. Enable more detailed logging to see the entire stacktrace.

                            2019-07-25 13:23:14,445 ERROR [org.teiid.PROCESSOR] (Worker10_QueryProcessorQueue46) XH6GMB1xbOX6 TEIID30019 Unexpected exception for request XH6GMB1xbOX6.0: java.lang.NullPointerException

                                   at org.teiid.core.types.BlobImpl.getBytes(BlobImpl.java:100)

                                   at org.teiid.core.types.BlobType.getBytes(BlobType.java:66)

                                   at org.teiid.common.buffer.LobManager.persistLob(LobManager.java:229)

                                   at org.teiid.common.buffer.LobManager.updateReferences(LobManager.java:145)

                             

                            When I am again trying to run the query, we are continuously facing FTP connection issue and getting below error:

                            2019-07-25 13:23:55,656 WARN  [org.teiid.CONNECTOR] (Worker11_QueryProcessorQueue48) n/vcvzEKKWI0 Connector worker process failed for atomic-request=n/vcvzEKKWI0.0.3.4: org.teiid.translator.TranslatorException: Can not open multiple ftp stream based file in one connection

                                            at org.teiid.translator.file.FileExecutionFactory$VirtualFileProcedureExecution.execute(FileExecutionFactory.java:208)

                                            at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:392)

                                            at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

                                            at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

                             

                            I have attached the detailed logs by enabling DEBUG mode for org.teiid package.

                            • 11. Re: How to connect to json data file using ftp or sftp connection using teiid
                              Ramesh Reddy Master

                              That looks like a bug, can log an issue here Teiid - JBoss Issue Tracker  include your VDB and sample data file and error log on the issue.