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

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

pranitag Jul 8, 2019 6:57 AM

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

rareddy Jul 8, 2019 9:37 AM (in response to pranitag)

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.
Actions
2. Re: How to connect to json data file using ftp or sftp connection using teiid

pranitag Jul 12, 2019 3:19 AM (in response to rareddy)

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.
Actions
3. Re: How to connect to json data file using ftp or sftp connection using teiid

rareddy Jul 12, 2019 8:42 PM (in response to pranitag)

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
Actions
4. Re: How to connect to json data file using ftp or sftp connection using teiid

pranitag Jul 19, 2019 8:29 AM (in response to rareddy)

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"
        }
    }
]}
Actions

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

rareddy Jul 19, 2019 4:32 PM (in response to pranitag)

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

type	name	release	rating	starring
Action Movie	Brave Heart	1995	5	Mel Gibson
Action Movie	Edge of Darkness	2010	5	Mel 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

pranitag Jul 23, 2019 11:14 AM (in response to rareddy)

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
Actions
7. Re: How to connect to json data file using ftp or sftp connection using teiid

rareddy Jul 23, 2019 5:30 PM (in response to pranitag)

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..
Actions
8. Re: How to connect to json data file using ftp or sftp connection using teiid

pranitag Jul 24, 2019 7:16 AM (in response to rareddy)
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.

server.log.zip 2.7 KB
Actions
9. Re: How to connect to json data file using ftp or sftp connection using teiid

rareddy Jul 24, 2019 11:49 AM (in response to pranitag)

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>
Actions
10. Re: How to connect to json data file using ftp or sftp connection using teiid

pranitag Jul 25, 2019 6:09 AM (in response to rareddy)
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.

server.log.zip 4.0 KB
Actions
11. Re: How to connect to json data file using ftp or sftp connection using teiid

rareddy Jul 25, 2019 11:29 AM (in response to pranitag)

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.
Actions

Go to original post