Skip to main content
Best Answer

How to migrate tables from database to another with dynamic schema

  • December 20, 2019
  • 9 replies
  • 191 views
C
Forum|alt.badge.img

Hi,

Il have to migrate several tables from a mysql database to a postgres database.

This seems quit simple but the main difficulty is that the table list to migrate is defined in a specific table and the schema (structure) of all tables to migrate is not known and has to be dynamic.

I tried the following:

- read the table list with a mysql reader

- for each entry in the table list, read the concerned table with a featurereader

- send (write) the concerned table in Postgres with a dynamic schema.

But this doesn't work, I always have errors:

Failed to obtain any schemas from reader 'MYSQL_DB' from 1 datasets. This may be due to invalid datasets or format accessibility issues due to licensing, dependencies, or module loading. See logfile for more information.

 

 

I tried to change many parameters but never achieve to make it work.

I would really appreciate any help.

Thanks,

Christian.

Best answer by takashi

Hi @christian_b, the reader reads a table list so it provides just a schema definition for the list, won't provide any schema for actual tables you need to migrate.

The schema definitions of your required tables will be provided by the FeatureReader in this case. The schema features output from the <Schema> port of the FeatureReader will have schema definitions.

Just connect between the <Schema> port and the dynamic writer feature type, then set "Schema from Schema Feature" to the Schema Sources parameter.

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.

9 replies

sigtill
Supporter
Forum|alt.badge.img+25
  • sigtillSupporter
  • Supporter
  • December 20, 2019

What is the error-message?

The real SigTill
C
Forum|alt.badge.img
  • christian_b
  • Author
  • December 20, 2019

What is the error-message?

Hi, it was in the body of my question:

Failed to obtain any schemas from reader 'MYSQL_DB' from 1 datasets. This may be due to invalid datasets or format accessibility issues due to licensing, dependencies, or module loading. See logfile for more information.

sigtill
Supporter
Forum|alt.badge.img+25
  • sigtillSupporter
  • Supporter
  • December 20, 2019

Hi, it was in the body of my question:

Failed to obtain any schemas from reader 'MYSQL_DB' from 1 datasets. This may be due to invalid datasets or format accessibility issues due to licensing, dependencies, or module loading. See logfile for more information.

Sorry, didnt see that. See it now

The real SigTill
sigtill
Supporter
Forum|alt.badge.img+25
  • sigtillSupporter
  • Supporter
  • December 20, 2019

1. There are many MYSQL readers, make sure you are using the right one

2. Try to Read only Schema and see if you have the exact same name of the tables (all prefixes and small/large letters)

The real SigTill
takashi
Celebrity
  • takashiCelebrity
  • Best Answer
  • December 21, 2019

Hi @christian_b, the reader reads a table list so it provides just a schema definition for the list, won't provide any schema for actual tables you need to migrate.

The schema definitions of your required tables will be provided by the FeatureReader in this case. The schema features output from the <Schema> port of the FeatureReader will have schema definitions.

Just connect between the <Schema> port and the dynamic writer feature type, then set "Schema from Schema Feature" to the Schema Sources parameter.

Why not inspect features with Visual/Data Preview and Feature/Record Information before writing them into a destination dataset?
C
Forum|alt.badge.img
  • christian_b
  • Author
  • December 22, 2019

Thank you very much @sigtill and @takashi for your help, I achieved to get the expected result.

There was several parameters that I had to change:

- Like Takashi said, I had to merge the result of the schema output port (supplier) and the generic output port (requestor) with fme_feature_type of requestor = fme_feature_type_name of supplier

- I had to remove the fme_schema_handling attribute

- In the writer, I had to set Schema sources = "Schema From Schema Feature"

C
Forum|alt.badge.img
  • christian_b
  • Author
  • December 22, 2019

1. There are many MYSQL readers, make sure you are using the right one

2. Try to Read only Schema and see if you have the exact same name of the tables (all prefixes and small/large letters)

Thanks @sigtill, I used the correct reader (MariaDB non spatial), the problem was different (look at my other answer).

takashi
Celebrity
  • takashiCelebrity
  • December 22, 2019

Thank you very much @sigtill and @takashi for your help, I achieved to get the expected result.

There was several parameters that I had to change:

- Like Takashi said, I had to merge the result of the schema output port (supplier) and the generic output port (requestor) with fme_feature_type of requestor = fme_feature_type_name of supplier

- I had to remove the fme_schema_handling attribute

- In the writer, I had to set Schema sources = "Schema From Schema Feature"

Good to hear you achieved the goal.

You can also connect the <Schema> port and the writer feature type directly to achieve the goal. The fme_schema_handling attribute storing "schema_only" tells the dynamic writer feature type that the feature is a schema feature only to be used to configure the destination schema, not contains any data to be written into the destination dataset.

Why not inspect features with Visual/Data Preview and Feature/Record Information before writing them into a destination dataset?
C
Forum|alt.badge.img
  • christian_b
  • Author
  • January 3, 2020

Good to hear you achieved the goal.

You can also connect the <Schema> port and the writer feature type directly to achieve the goal. The fme_schema_handling attribute storing "schema_only" tells the dynamic writer feature type that the feature is a schema feature only to be used to configure the destination schema, not contains any data to be written into the destination dataset.

Thanks for the details, Takashi, good to know !

Community Stats

32,182
Posts
122,176
Replies
39,856
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.