Solved

How to migrate tables from database to another with dynamic schema

  • 20 December 2019
  • 9 replies
  • 16 views
C
Badge

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.

icon

Best answer by takashi 21 December 2019, 06:30

View original

9 replies

sigtill
Rank
Badge +21

What is the error-message?

The real SigTill
C
Badge

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
Rank
Badge +21

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
Rank
Badge +21

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
Rank
Userlevel 2
Badge +17

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.

C
Badge

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
Badge

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
Rank
Userlevel 2
Badge +17

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.

C
Badge

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


Community Stats

28,723
Posts
109,513
Replies
36,561
Members
Terms & ConditionsCookie settings
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.