Skip to content

Understand the format of exported data

This page gives an example and some clarifications on the format of the SQLFlow-Ingester exported data.

We may have some similar fields in the exported json data due to the fact that different databases can have various structure concepts. These similar fields won't all appear in a real case.

Following json is an exhaustive example which lists all possible fields:

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
{
  "createTime":"", //export time
  "createdBy":"sqlflow-exporter",//name of the export tool
  "physicalInstance":"",//server address
  "servers":[
      {
          "name":"",//server name
          "dbVendor":"",//database type，possible values are: dbvathena,dbvazuresql,dbvbigquery,dbvcouchbase,dbvdb2,dbvgreenplum,dbvhana,dbvhive,dbvimpala,dbvinformix,dbvmdx,dbvmysqldbvnetezza,dbvopenedge,dbvoracle,dbvpostgresql,dbvpresto,dbvredshift,dbvsnowflake,dbvsparksql,dbvmssql,dbvsybase,dbvteradata,dbvvertica
          "supportsCatalogs":false,//whether there's a database layer
          "supportsSchemas":true,//whether there's a schema layer 
          "databases":[
              {
                  "name":"",//database name
                  "schemas":[
                      {
                          "name":"",//schema name
                          "synonyms":[
                              {
                                  "name":"",
                                  "sourceName":"",
                                  "sourceSchema":"",
                                  "sourceDbLinkName":""
                              }
                          ],
                          "sequences":[
                              {
                                  "name":"",
                                  "incrementBy":""
                              }
                          ],
                          "tables":[
                              {
                                  "name":"",//table name 
                                  "columns":[
                                      {
                                          "dataType":"",//column data type
                                          "name":"",//column name
                                          "comment":""//column comment
                                      }
                                  ]
                              }
                          ],
                          "views":[ 
                            {
                                  "name":"",//view name
                                  "columns":[
                                      {
                                          "dataType":"",
                                          "name":"",
                                          "comment":""
                                      }
                                  ]
                              }
                          ],
                          "others":[//others, including resultset, variable, path etc 
                              {
                                  "name":"",
                                  "columns":[
                                      {
                                          "dataType":"",
                                          "name":"",
                                          "comment":""
                                      }
                                  ]
                              }
                          ],
                          "packages":[
                              {
                                  "name":"",//package name 
                                  "procedures":[//prcedures in the package
                                  ],
                                  "functions":[//functions in the package
                                  ],
                                  "triggers":[//triggers in the package
                                  ]
                              }
                          ],
                          "procedures":[
                              {
                                  "name":"",
                                  "type":"",
                                  "arguments":[
                                      {
                                          "name":"",
                                          "dataType":"",
                                          "inout":""
                                      }
                                  ]
                              }
                          ],
                          "functions":[
                              {
                                  "name":"",
                                  "type":"",
                                  "arguments":[
                                      {
                                          "name":"",
                                          "dataType":"",
                                          "inout":""
                                      }
                                  ]
                              }
                          ],
                          "triggers":[
                              {
                                  "name":"",
                                  "type":"",
                                  "arguments":[
                                      {
                                          "name":"",
                                          "dataType":"",
                                          "inout":""
                                      }
                                  ]
                              }
                          ]
                      }
                  ],
                  "synonyms":[//synonym list when there's no schema object, same structure as the above servers.databases.schemas.synonyms

                  ],
                  "sequences":[//sequence list when there's no schema object, same structure as the above servers.databases.schemas.sequences 

                  ],
                  "tables":[//table list when there's no schema object, same structure as the above servers.databases.schemas.tables 


                  ],
                  "views":[//view list when there's no schema object, same structure as the above servers.databases.schemas.views 

                  ],
                  "others":[//others when there's no schema object, including resultset, variable, path etc. same structure as the above servers.databases.schemas.others 

                  ],
                  "packages":[//package list when there's no schema object, same structure as the above servers.databases.schemas.packages 

                  ],
                  "procedures":[//procedure list when there's no schema object, same structure as the above servers.databases.schemas.procedures 

                  ],
                  "functions":[//function list when there's no schema object, same structure as the above servers.databases.schemas.functions 

                  ],
                  "triggers":[//trigger list when there's no schema object, same structure as the above servers.databases.schemas.triggers 

                  ]
              }
          ],
          "schemas":[//schema list when there's no database object, same structure as the above servers.databases.schemas 

          ],
          "dbLinks":[
              {
                  "owner":"",
                  "name":"",
                  "userName":"",
                  "host":""
              }
          ],
          "queries":[//DDL scripts in database
              {
                  "database":"",
                  "schema":"",
                  "name":"",
                  "type":"",
                  "sourceCode":"",
                  "groupName":""
              }
          ]
      }
  ],
  "errorMessages":[//errors during the exporting 
      {
          "errorMessage":"",
          "errorType":"",
          "file":""
      }
  ]
}

You may have found that some of the fields appear multiple times in the above json. The reason is that there are three types of database from the database/schema structure layer:

Database who has the database layer and the schema layer (SQL Server for example)

The database has the database layer and the schema layer. supportsCatalogs and supportsSchemas will be both true. No extra db unit field outside the database list because all units belong to the specific schema and database.

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
{
    "createTime":"", //export time
    "createdBy":"sqlflow-exporter",//name of the export tool
    "physicalInstance":"",//server address
    "servers":[
        {
            "name":"",//server name
            "dbVendor":"",//database type，possible values are: dbvathena,dbvazuresql,dbvbigquery,dbvcouchbase,dbvdb2,dbvgreenplum,dbvhana,dbvhive,dbvimpala,dbvinformix,dbvmdx,dbvmysqldbvnetezza,dbvopenedge,dbvoracle,dbvpostgresql,dbvpresto,dbvredshift,dbvsnowflake,dbvsparksql,dbvmssql,dbvsybase,dbvteradata,dbvvertica
            "supportsCatalogs":true,// has database layer
            "supportsSchemas":true,// has schema layer
            "databases":[ // No extra db unit field outside the database list because all units belong to the specific schema and database.
                {
                    "name":"",//database name
                    "schemas":[
                        {
                            "name":"",//schema name
                            "synonyms":[
                                {
                                    "name":"",
                                    "sourceName":"",
                                    "sourceSchema":"",
                                    "sourceDbLinkName":""
                                }
                            ],
                            "sequences":[
                                {
                                    "name":"",
                                    "incrementBy":""
                                }
                            ],
                            "tables":[
                                {
                                    "name":"",//table name 
                                    "columns":[
                                        {
                                            "dataType":"",//column data type
                                            "name":"",//column name
                                            "comment":""//column comment
                                        }
                                    ]
                                }
                            ],
                            "views":[ 
                              {
                                    "name":"",//view name
                                    "columns":[
                                        {
                                            "dataType":"",
                                            "name":"",
                                            "comment":""
                                        }
                                    ]
                                }
                            ],
                            "others":[//others, including resultset, variable, path etc 
                                {
                                    "name":"",
                                    "columns":[
                                        {
                                            "dataType":"",
                                            "name":"",
                                            "comment":""
                                        }
                                    ]
                                }
                            ],
                            "packages":[
                                {
                                    "name":"",//package name 
                                    "procedures":[//prcedures in the package
                                    ],
                                    "functions":[//functions in the package
                                    ],
                                    "triggers":[//triggers in the package
                                    ]
                                }
                            ],
                            "procedures":[
                                {
                                    "name":"",
                                    "type":"",
                                    "arguments":[
                                        {
                                            "name":"",
                                            "dataType":"",
                                            "inout":""
                                        }
                                    ]
                                }
                            ],
                            "functions":[
                                {
                                    "name":"",
                                    "type":"",
                                    "arguments":[
                                        {
                                            "name":"",
                                            "dataType":"",
                                            "inout":""
                                        }
                                    ]
                                }
                            ],
                            "triggers":[
                                {
                                    "name":"",
                                    "type":"",
                                    "arguments":[
                                        {
                                            "name":"",
                                            "dataType":"",
                                            "inout":""
                                        }
                                    ]
                                }
                            ]
                        }
                    ],
            "dbLinks":[
                {
                    "owner":"",
                    "name":"",
                    "userName":"",
                    "host":""
                }
            ],
            "queries":[//DDL scripts in database
                {
                    "database":"",
                    "schema":"",
                    "name":"",
                    "type":"",
                    "sourceCode":"",
                    "groupName":""
                }
            ]
        }
    ],
    "errorMessages":[//errors during the exporting 
        {
            "errorMessage":"",
            "errorType":"",
            "file":""
        }
    ]
  }

Database who has the database layer only (MYSQL for example)

The database has the database layer but doesn't have the schema layer(or no actual schema layer). supportsCatalogs will be true and supportsSchemas will be false. No schemas list under the databases list but extra db unit lists will be present.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
{
    "createTime":"", //export time
    "createdBy":"sqlflow-exporter",//name of the export tool
    "physicalInstance":"",//server address
    "servers":[
        {
            "name":"",//server name
            "dbVendor":"",//database type，possible values are: dbvathena,dbvazuresql,dbvbigquery,dbvcouchbase,dbvdb2,dbvgreenplum,dbvhana,dbvhive,dbvimpala,dbvinformix,dbvmdx,dbvmysqldbvnetezza,dbvopenedge,dbvoracle,dbvpostgresql,dbvpresto,dbvredshift,dbvsnowflake,dbvsparksql,dbvmssql,dbvsybase,dbvteradata,dbvvertica
            "supportsCatalogs":true,//there's a database layer
            "supportsSchemas":false,//there's no schema layer
            "databases":[
                {
                    "name":"",//database name

                    "synonyms":[//synonym list when there's no schema object, same structure as the above servers.databases.schemas.synonyms

                    ],
                    "sequences":[//sequence list when there's no schema object, same structure as the above servers.databases.schemas.sequences 

                    ],
                    "tables":[//table list when there's no schema object, same structure as the above servers.databases.schemas.tables 


                    ],
                    "views":[//view list when there's no schema object, same structure as the above servers.databases.schemas.views 

                    ],
                    "others":[//others when there's no schema object, including resultset, variable, path etc. same structure as the above servers.databases.schemas.others 

                    ],
                    "packages":[//package list when there's no schema object, same structure as the above servers.databases.schemas.packages 

                    ],
                    "procedures":[//procedure list when there's no schema object, same structure as the above servers.databases.schemas.procedures 

                    ],
                    "functions":[//function list when there's no schema object, same structure as the above servers.databases.schemas.functions 

                    ],
                    "triggers":[//trigger list when there's no schema object, same structure as the above servers.databases.schemas.triggers 

                    ]
                }
            ],
            "dbLinks":[
                {
                    "owner":"",
                    "name":"",
                    "userName":"",
                    "host":""
                }
            ],
            "queries":[//DDL scripts in database
                {
                    "database":"",
                    "schema":"",
                    "name":"",
                    "type":"",
                    "sourceCode":"",
                    "groupName":""
                }
            ]
        }
    ],
    "errorMessages":[//errors during the exporting 
        {
            "errorMessage":"",
            "errorType":"",
            "file":""
        }
    ]
  }

Database who has the schema layer only (Currently no supported database belongs to this type)

The database doesn't have the database layer(or no actual database layer) but has the schema layer only. supportsCatalogs will be false and supportsSchemas will be true. No databases list under the servers list and no extra db unit list. An extra schemas list will be present directly under the servers list.

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
{
    "createTime":"", //export time
    "createdBy":"sqlflow-exporter",//name of the export tool
    "physicalInstance":"",//server address
    "servers":[
        {
            "name":"",//server name
            "dbVendor":"",//database type，possible values are: dbvathena,dbvazuresql,dbvbigquery,dbvcouchbase,dbvdb2,dbvgreenplum,dbvhana,dbvhive,dbvimpala,dbvinformix,dbvmdx,dbvmysqldbvnetezza,dbvopenedge,dbvoracle,dbvpostgresql,dbvpresto,dbvredshift,dbvsnowflake,dbvsparksql,dbvmssql,dbvsybase,dbvteradata,dbvvertica
            "supportsCatalogs":false,//there's no database layer
            "supportsSchemas":true,//there's schema layer
            "schemas":[//schema list when there's no database object, same structure as the above servers.databases.schemas 
                {
                    "name":"",//schema name
                    "synonyms":[
                        {
                            "name":"",
                            "sourceName":"",
                            "sourceSchema":"",
                            "sourceDbLinkName":""
                        }
                    ],
                    "sequences":[
                        {
                            "name":"",
                            "incrementBy":""
                        }
                    ],
                    "tables":[
                        {
                            "name":"",//table name 
                            "columns":[
                                {
                                    "dataType":"",//column data type
                                    "name":"",//column name
                                    "comment":""//column comment
                                }
                            ]
                        }
                    ],
                    "views":[ 
                      {
                            "name":"",//view name
                            "columns":[
                                {
                                    "dataType":"",
                                    "name":"",
                                    "comment":""
                                }
                            ]
                        }
                    ],
                    "others":[//others, including resultset, variable, path etc 
                        {
                            "name":"",
                            "columns":[
                                {
                                    "dataType":"",
                                    "name":"",
                                    "comment":""
                                }
                            ]
                        }
                    ],
                    "packages":[
                        {
                            "name":"",//package name 
                            "procedures":[//prcedures in the package
                            ],
                            "functions":[//functions in the package
                            ],
                            "triggers":[//triggers in the package
                            ]
                        }
                    ],
                    "procedures":[
                        {
                            "name":"",
                            "type":"",
                            "arguments":[
                                {
                                    "name":"",
                                    "dataType":"",
                                    "inout":""
                                }
                            ]
                        }
                    ],
                    "functions":[
                        {
                            "name":"",
                            "type":"",
                            "arguments":[
                                {
                                    "name":"",
                                    "dataType":"",
                                    "inout":""
                                }
                            ]
                        }
                    ],
                    "triggers":[
                        {
                            "name":"",
                            "type":"",
                            "arguments":[
                                {
                                    "name":"",
                                    "dataType":"",
                                    "inout":""
                                }
                            ]
                        }
                    ]
                }
            ],
            "dbLinks":[
                {
                    "owner":"",
                    "name":"",
                    "userName":"",
                    "host":""
                }
            ],
            "queries":[//DDL scripts in database
                {
                    "database":"",
                    "schema":"",
                    "name":"",
                    "type":"",
                    "sourceCode":"",
                    "groupName":""
                }
            ]
        }
    ],
    "errorMessages":[//errors during the exporting 
        {
            "errorMessage":"",
            "errorType":"",
            "file":""
        }
    ]
  }

Database list

Check here to get a full database list and the type details.

Other clarifications on the json fields:

  • $.createTime : export time
  • $.createdBy : name of the export tool
  • $.physicalInstance : server address
  • $.servers[0].name : server name
  • $.servers[0].dbVendor : database type，possible values are: dbvathena,dbvazuresql,dbvbigquery,dbvcouchbase,dbvdb2,dbvgreenplum,dbvhana,dbvhive,dbvimpala,dbvinformix,dbvmdx,dbvmysqldbvnetezza,dbvopenedge,dbvoracle,dbvpostgresql,dbvpresto,dbvredshift,dbvsnowflake,dbvsparksql,dbvmssql,dbvsybase,dbvteradata,dbvvertica
  • $.servers[0].supportsCatalogs : whether there's a database layer
  • $.servers[0].supportsSchemas : whether there's a schema layer
  • $.servers[0].databases[0].name : database name
  • $.servers[0].databases[0].schemas[0].others : //others, including resultset, variable, path etc
  • $.servers[0].databases[0].synonyms : synonym list when there's no schema object, same structure as the above servers.databases.schemas.synonyms
  • $.servers[0].databases[0].sequences : sequence list when there's no schema object, same structure as the above servers.databases.schemas.sequences
  • $.servers[0].databases[0].tables : table list when there's no schema object, same structure as the above servers.databases.schemas.tables
  • $.servers[0].databases[0].views : view list when there's no schema object, same structure as the above servers.databases.schemas.views
  • $.servers[0].databases[0].others : others when there's no schema object, including resultset, variable, path etc. same structure as the above servers.databases.schemas.others
  • $.servers[0].databases[0].packages : package list when there's no schema object, same structure as the above servers.databases.schemas.packages
  • $.servers[0].databases[0].procedures : procedure list when there's no schema object, same structure as the above servers.databases.schemas.procedures
  • $.servers[0].databases[0].functions : function list when there's no schema object, same structure as the above servers.databases.schemas.functions
  • $.servers[0].databases[0].triggers : trigger list when there's no schema object, same structure as the above servers.databases.schemas.triggers
  • $.servers[0].schemas : schema list when there's no database object, same structure as the above servers.databases.schemas
  • $.servers[0].queries : DDL scripts in database
  • $.errorMessages : errors during the exporting