Skip to main content
Solved

How to migrate tables from database to another with dynamic schema

  • December 20, 2019
  • 9 replies
  • 140 views

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.

View original
Did this help you find an answer to your question?

9 replies

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

What is the error-message?


Forum|alt.badge.img
  • Author
  • December 20, 2019
sigtill wrote:

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+24
  • Supporter
  • December 20, 2019
christian_b wrote:

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


sigtill
Supporter
Forum|alt.badge.img+24
  • 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)


takashi
Influencer
  • 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.


Forum|alt.badge.img
  • 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"


Forum|alt.badge.img
  • Author
  • December 22, 2019
sigtill wrote:

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
Influencer
  • December 22, 2019
christian_b wrote:

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.


Forum|alt.badge.img
  • Author
  • January 3, 2020
takashi wrote:

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 !


Reply


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