Skip to main content
Question

How to dynamically create a table from json metadata?


aguan
Contributor
Forum|alt.badge.img+11
  • Contributor

I have multiple json files, each containing a database table schema definitions. For example, the extraction of one json file, breaks down to column names and data types (non-spatial), as shown in the attached Data Inspector image. How can I use this information to dynamically create a table in the database ?(Oracle).

10 replies

takashi
Influencer
  • August 29, 2019

Hi @ag, I think you can build SQL statements from the JSON documents and execute them to create DB tables with the SQLExecutor transformer. If you could share a few samples of the JSON files, I would try implementing a workspace example.


itay
Supporter
Forum|alt.badge.img+17
  • Supporter
  • August 29, 2019

Hi @ag,

I would look into creating a schema feature (list) from the JSON attributes and using that in the writer, see the following link for more info.

Hope this helps,

Itay


itay
Supporter
Forum|alt.badge.img+17
  • Supporter
  • August 29, 2019
itay wrote:

Hi @ag,

I would look into creating a schema feature (list) from the JSON attributes and using that in the writer, see the following link for more info.

Hope this helps,

Itay

Here is an example creating some data (name and fme_data_type) and necessary schema attributes (fme_geometry{0} and fme_feature_type) and dynamically writing.


aguan
Contributor
Forum|alt.badge.img+11
  • Author
  • Contributor
  • August 29, 2019
itay wrote:

Hi @ag,

I would look into creating a schema feature (list) from the JSON attributes and using that in the writer, see the following link for more info.

Hope this helps,

Itay

@itay, interesting. I saw a similar example somewhere, but did not work for me. What the writer settings look like?


aguan
Contributor
Forum|alt.badge.img+11
  • Author
  • Contributor
  • August 29, 2019
takashi wrote:

Hi @ag, I think you can build SQL statements from the JSON documents and execute them to create DB tables with the SQLExecutor transformer. If you could share a few samples of the JSON files, I would try implementing a workspace example.

@takashi, curious how you do that dynamically. I am attaching one example of the json file.

basin_header.json


takashi
Influencer
  • August 29, 2019
takashi wrote:

Hi @ag, I think you can build SQL statements from the JSON documents and execute them to create DB tables with the SQLExecutor transformer. If you could share a few samples of the JSON files, I would try implementing a workspace example.

Please find the attached workspace example which demonstrates how you can convert the JSON document to a SQL statement (CREATE TABLE).

json_to_sql.zip (FME 2019.1.1)


itay
Supporter
Forum|alt.badge.img+17
  • Supporter
  • August 30, 2019
aguan wrote:

@itay, interesting. I saw a similar example somewhere, but did not work for me. What the writer settings look like?

Hi @ag, the writer is set according to the same way as described in the link provided. Basically using the dynamic mode with fme_feature_type as table name.

See section 4 https://knowledge.safe.com/articles/1051/index.html


itay
Supporter
Forum|alt.badge.img+17
  • Supporter
  • August 30, 2019
aguan wrote:

@itay, interesting. I saw a similar example somewhere, but did not work for me. What the writer settings look like?

Hi @ag,

Here is an example workspace with your data (FME 2019.1.0.0). json2dynamic.fmw


aguan
Contributor
Forum|alt.badge.img+11
  • Author
  • Contributor
  • August 30, 2019
itay wrote:

Hi @ag, the writer is set according to the same way as described in the link provided. Basically using the dynamic mode with fme_feature_type as table name.

See section 4 https://knowledge.safe.com/articles/1051/index.html

Thanks for the URL. I need to change the column_name to be exactly "name" and use "attribute" as the list. it works now. I have an issue with one column though. The "entitled_key" is varchar2(65536), so fme_varchar(65536). The FME gives and error on "create table ..." sql. This sql is mis-formed at the end in FME. The reason is that the standard length limit for Oracle 12c on varchar2 is 4000. After I change the column_size to be 4000, it successfully creates the table . Many thanks.


aguan
Contributor
Forum|alt.badge.img+11
  • Author
  • Contributor
  • August 30, 2019
takashi wrote:

Please find the attached workspace example which demonstrates how you can convert the JSON document to a SQL statement (CREATE TABLE).

json_to_sql.zip (FME 2019.1.1)

@takshi. I am on FME 2018.1, can not get your Aggregator open. But I can see what you are doing. I did not know the same attribute can be repreated in the same AttributeCreator. This is useful when I want to appliy the primary key and null able constraints. Would like to try this when I find a json that has these constraints. Many thanks.

 


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings