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).
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
- 7843 replies
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.
+18- Supporter
- 1442 replies
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
+18- Supporter
- 1442 replies
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.
+11- Supporter
- 140 replies
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?
+11- Supporter
- 140 replies
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
- 7843 replies
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)
+18- Supporter
- 1442 replies
@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
+18- Supporter
- 1442 replies
@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
+11- Supporter
- 140 replies
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.
+11- Supporter
- 140 replies
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.