How to dynamically create a table from json metadata?

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.

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.

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

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

basin_header.json

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)

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

Hi @ag,

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

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.

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