Non Relational to Relational Tasks

[joaoftrodrigues]

Description

Planning of action to pass data from mongoDB to SQL.

Definition of SQL structure

Define an ER diagram, that will hold the data. There's 2 draws on chat to begin from.

Process of passing data to new database

How to automate this task, as it includes:

• Export data from MongoDB
  • Define if there's a need to create new collections, to divide info (it's all about what's easier to implement)
• Generate Insert queries, from exported file data, being aware of data types

[inescelorico]

• Export json to csv tables

  • mongoexport --db atp --collection atpplayers --type=csv -- fields variable1, variable2, variable3 --out table_name1.csv
  • mongoexport --db atp --collection atpplayers --type=csv -- fields variable4, variable5, variable6 --out table_name2.csv
  • mongoexport --db atp --collection atpplayers --type=csv -- fields variable7, variable8, variable9 --out table_name3.csv
  • ...

• Create tables at phpmyadmin

  • define names , types, limits and keys for each table

• Import csv tables to phpmyadmin with SQL code

  • ALTER TABLE table_nameDISABLE KEYS; LOAD DATA INFILE 'C:/localização/table_name.csv' INTO TABLE table_name CHARACTER SET UTF8 FIELDS TERMINATED BY ',' ENCLOSED BY '"'; ALTER TABLE table_nameENABLE KEYS;

• Print relacional diagram at phpmyadmin