导出JSON数据并加载到关系数据库中 [英] Export JSON data and load into a Relational Database

问题描述

是否存在将JSON数据转换为具有多个表的关系数据库的标准方法?我们需要从MongoDB实例导出数据并将其导入Redshift集群.唯一的问题是，MongoDB的某些字段包含对象和数组. Redshift群集接受CSV，因此我认为每个新表的输出至少是一个CSV文件.

Is there a standard way of converting JSON data to relational database with multiple tables? We need to export our data from a MongoDB instance and import them into a Redshift cluster. The only problem is that some of MongoDB fields contain objects and arrays. Redshift cluster accepts CSV so I'm thinking the output would be at least one CSV file for each new table.

我不需要特定的实现.我只是想了解有关如何有效地将JSON/NoSQL数据转换为关系格式的概念.

I don't need specific implementation. I just wanted to know the concepts on how to efficiently transform the JSON/NoSQL data into relational format.

推荐答案

我们有用于存储JSON数据的mongo数据库.我们希望出于某些查询目的将数据迁移到Redshift.

we have our mongo DB which stores JSON data. We wanted to migrated data to Redshift for some querying purposes.

我们使用mongoexport csv从mongo表创建了csv，并将其上传到S3.我们在Redshift中创建了相应的关系模式，并使用了复制命令将这些csv数据从s3加载到redshift.

We used mongoexport csv to create csv from mongo table and uploaded it to S3. We created corresponding relational schema in Redshift and used copy commands to load these csv data from s3 to redshift.

我们可以使用Java API来查询mongo并创建csv并将其上传到s3.可以将相同的内容加载到redshift.

We can use java apis to query mongo and create and upload csv to s3. The same can be loaded to redshift.

真正的问题是，由于我们使用mongo(json)或NoSQL，对于同一个表(如JSON)，给定对象的列数可能会不同，但是在Redshift中，每个表的列数是固定的，因此在这种情况下，您需要创建所有可能的列并加载数据.对于那些没有所有列的对象，我们可以为它们填充空值.

The real problem is since we use mongo (json) or NoSQL we might have different number of columns for given object which belongs to same table ( as we JSON) , but in Redshift we have fixed number of columns per table So in such cases you need to create all possible columns and load data . For those objects which does not have all columns we can populate null values for them.

这篇关于导出JSON数据并加载到关系数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！