REST API Import - Foreign Key from CSV file - How to | Developers

REST API Import - Foreign Key from CSV file - How to

Hi,

I try to write the JSON mapping file, to import my CSV file, containing FK relations of my schema (already imported)… and I’m a little bit lost.

As FK can points from serveral columns to serveral columns, then into my CSV file, I can have a columns list (comma seperated), and not only just one column from src to dst.

I’ve checked the “Import API Documenation” available here : https://developer.collibra.com/rest/import-api-documentation/ where there is an example for “Field Mapping” complex relation; and try to transpose it to Foregien Key Mapping.

I’ve writtent this JSON mapping:

[{
  "resourceType": "Complex Relation",
  "identifier": {
    "relations": {
      "00000000-0000-0000-0000-000000007091:TARGET": [
        {
          "name": "${4}",
          "domain": {
            "name": "${1}",
            "community": {
              "name": "Technical Data"
            }
          }
        }
      ],
      "00000000-0000-0000-0000-000000007092:TARGET": [
        {
          "name": "${7}",
          "domain": {
            "name": "${5}",
            "community": {
              "name": "Technical Data"
            }
          }
        }
      ],
      "00000000-0000-0000-0000-000000007093:TARGET": [
        {
          "name": "${1}",
          "domain": {
            "name": "${2}",
            "community": {
              "name": "Technical Data"
            }
          }
        }
      ]
    }
  },
  "complexRelationType": {
    "name": "Foreign Key Mapping"
  }
}]

In my community “Technical Data”, I’ve already created one domain per imported schema (with the name of the schema), when I previously import schema/tables/columns.

But here into this JSON, there is no columns list mapping…? How to map a columns list?

Moreover, into the example of the “Import API Documenation”, for the field mapping complex relation, the relations seems both into the “identifier” block and outside it. I do not really understand why…?

Regards,

Alex

alexandrebargeton

Posted 4 years ago · Edited 1 year ago·Last reply 4 years ago

10 comments

alexandrebargeton

OP4 years ago · Edited

One more question to be sure.

I’m doing my technical import in 2 times (for 1 database schema) :

Import a CSV file continaing Schema, Tables, Columns (say TC file)
Import a CSV file containg all the Foreign Keys (say FK file)

When using the synchronize import API :

/import/synchronize/{synchronizationId}/batch/csv-job
/import/synchronize/{synchronizationId}/finalize/job

I have to provide a synchronizion ID.

Can it be the same synch id for both TC and FK files?
Or do I have to use 2 distinct ids?

For now, my synch ID is the concatenation of the server name and the schema name (columns 1 and 2 of my CSV file). So it’s the same ID for both TC and FK.

Regards,

Alex

arthurburkhardt

·4 years ago · Edited

You have to consider the “synchronization ID” as a complete data set: everything that is not present from one load to the next will be deleted.
So if you have two different templates, keep 2 different synchronization IDs. If you have two different payloads with the same synchronization ID, they will keep conflicting with each other.

noor

·4 years ago · Edited

@arthur.burkhardt

Hi Arthur,

Let us consider this scenario.

I have done a FULL load of metadata of a system using file A & sync id 1.

Thereafter, I get updates & deletes of the above metadata in file B. Also, file B may contain fresh metadata (i.e. inserts).

then as per the above discussion, I should not use sync id 1 for file B as it creates conflicts?

Everyday file B will contain updates & deletes of metadata loaded from file A & Sync id 1, and some fresh metadata (inserts).

arthurburkhardt

·4 years ago · Edited

Option1: you consolidate the two files and use a single ID (you always send file A and file B together). Let’s imagine you get them at different times.

time	files
T0	AT0
T1	AT0 + BT1
T2	AT2 + BT1
T3	AT2 + BT3

So you’ll always get inconsistent information (columns without data types at T1 and T3, and columns without foreign keys at T0 and T2), but it will work.

Option 2: Do not use the synchronization, but the regular incremental batch. Then every so often, use the outputModule to identify deleted columns and make a specific batch to delete them.

alexandrebargeton

OP4 years ago · Edited

Really great thanks for the reply…
Finally it’s working!

I make my CSV having 1 line per column. So a FK with 3 colums, will have 3 lines.

Here is what I have (my example used as test) :

SRV|SRC_SCHEMA|SRC_TABLE|SRC_COLA|SRC_TABLE > SRC_COLA|SRC_FK_NAME|DST_SCHEMA|DST_TABLE|DST_COL1|DST_TABLE > DST_COL1|DST_CONSTRAINT_NAME|1
SRV|SRC_SCHEMA|SRC_TABLE|SRC_COLB|SRC_TABLE > SRC_COLB|SRC_FK_NAME|DST_SCHEMA|DST_TABLE|DST_COL2|DST_TABLE > DST_COL2|DST_CONSTRAINT_NAME|2
SRV|SRC_SCHEMA|SRC_TABLE|SRC_COLC|SRC_TABLE > SRC_COLC|SRC_FK_NAME|DST_SCHEMA|DST_TABLE|DST_COL3|DST_TABLE > DST_COL3|DST_CONSTRAINT_NAME|3

CSV Description is:

1 - Server
2 - SRC Schema
3 - SRC Table Name
4 - SRC Original Column Name
5 - SRC Unique Technical Column Name ( which is the fullname)
6 - SRC Constraint Name
7 - DEST Schema
8 - DEST Table Name
9 - DST Original Column Name
10 - DST Unique Technical Column Name ( which is the fullname)
11 - DST Constraint Name
12 - Column order

And the JSON Mapping file:

[{
  "resourceType": "Asset",
  "identifier": {
    "name": "${6}",
    "domain": {
      "name": "${2}",
      "community": {
        "name": "Technical Data"
      }
    }
  },
  "type": {
    "name": "Foreign Key"
  }
},{
  "resourceType": "Complex Relation",
  "identifier": {
    "relations": {
      "00000000-0000-0000-0000-000000007093:TARGET": [{
        "name": "${6}",
        "domain": {
          "name": "${2}",
          "community": {
            "name": "Technical Data"
          }
        }
      }],
      "00000000-0000-0000-0000-000000007092:TARGET": [{
        "name": "${5}",
         "domain": {
          "name": "${2}",
          "community": {
            "name": "Technical Data"
          }
        }
      }],
      "00000000-0000-0000-0000-000000007091:TARGET": [{
        "name": "${10}",
        "domain": {
          "name": "${7}",
          "community": {
            "name": "Technical Data"
          }
        }
      }]
    }
  },
  "complexRelationType": {
    "name": "Foreign Key Mapping"
  },
  "attributes": {
      "Key sequence": [{
        "value": "${12}"
      }]
  }  
}]

The only difference now comparing the same import with the JDBC driver is that in Diagram of the FK asset, I have the “references” and “constraints” arrows not the on the same tables… I have to check and swap the mapping I think…

Alex

arthurburkhardt

·4 years ago · Edited

OK, here’s the fully functional example based on your input. Doing this exercise made me realize I provided some inaccurate information previously, I’ll edit my previous posts to reflect that.

The structure for each complex relation should look like

{"resourceType": "Complex Relation",
 "identifier": {
   "relations": {
     "00000000-0000-0000-0000-000000007093:TARGET": [{"name": "FK Name","domain": {"id": "44f84eb8-cf7a-4e0a-a857-0c4becfedc20"}}],
	 "00000000-0000-0000-0000-000000007092:TARGET": [{"name": "SRC_Table > SRC_Col1","domain": {"id": "44f84eb8-cf7a-4e0a-a857-0c4becfedc20"}}],
	 "00000000-0000-0000-0000-000000007091:TARGET": [{"name": "DST_Table > DST_ColA","domain": {"id": "44f84eb8-cf7a-4e0a-a857-0c4becfedc20"}}]
   }
 },
 "complexRelationType": {"name": "Foreign Key Mapping"},
 "attributes": {"Key sequence": [{"value": 1}]}
}

Code example (python)

import csv
from collibra import Collibra
import json


c = Collibra()
domain_id = '44f84eb8-cf7a-4e0a-a857-0c4becfedc20'
input_example = "FK Name | SRC_Schema |SRC_Table | SRC_Col1 , SRC_Col2, SRC_Col3 | DST_Schema | DST_Table | DST_ColA, DST_ColB, DST_ColC"
fk_name, s_schema, s_table, s_columns, t_schema, t_table, t_columns = [x.strip() for x in input_example.split('|')]
s_columns = [' > '.join([s_table, x.strip()]) for x in s_columns.split(',')]
t_columns = [' > '.join([t_table, x.strip()]) for x in t_columns.split(',')]

get_command = lambda name, typ: {"resourceType": "Asset","identifier": { "name": name, "domain": {"id": domain_id}},"type": {"name": typ},"name": name}

payload = [get_command(name, typ)
           for name, typ in 
           ((fk_name, "Foreign Key"),
            (s_schema, "Schema"),
            (t_schema, "Schema"),
            (s_table, "Table"),
            (t_table, "Table"))]

s_columns_import = [get_command(x, "Column") for x in s_columns]
t_columns_import = [get_command(x, "Column") for x in t_columns]
payload += (s_columns_import + t_columns_import)

payload += [{
"resourceType": "Complex Relation",
"identifier": {
  "relations": {
    "00000000-0000-0000-0000-000000007093:TARGET": [{ "name": fk_name, "domain": {"id": domain_id}}],
    "00000000-0000-0000-0000-000000007092:TARGET": [s['identifier']],
    "00000000-0000-0000-0000-000000007091:TARGET": [t['identifier']],
  }
},
"complexRelationType": {"name": "Foreign Key Mapping"},
  "attributes": {"Key sequence": [{"value": e+1}],},
} for e, (s, t) in enumerate(zip(s_columns_import, t_columns_import))]

r = c.post('import/synchronize/example-fk/batch/json-job', files={"file":json.dumps(payload),'fileName':"example-fk.json"})

Import payload

[{
        "resourceType": "Asset",
        "identifier": {
            "name": "FK Name",
            "domain": {
                "id": "44f84eb8-cf7a-4e0a-a857-0c4becfedc20"
            }
        },
        "type": {
            "name": "Foreign Key"
        },
        "name": "FK Name"
    }, {
        "resourceType": "Asset",
        "identifier": {
            "name": "SRC_Schema",
            "domain": {
                "id": "44f84eb8-cf7a-4e0a-a857-0c4becfedc20"
            }
        },
        "type": {
            "name": "Schema"
        },
        "name": "SRC_Schema"
    }, {
        "resourceType": "Asset",
        "identifier": {
            "name": "DST_Schema",
            "domain": {
                "id": "44f84eb8-cf7a-4e0a-a857-0c4becfedc20"
            }
        },
        "type": {
            "name": "Schema"
        },
        "name": "DST_Schema"
    }, {
        "resourceType": "Asset",
        "identifier": {
            "name": "SRC_Table",
            "domain": {
                "id": "44f84eb8-cf7a-4e0a-a857-0c4becfedc20"
            }
        },
        "type": {
            "name": "Table"
        },
        "name": "SRC_Table"
    }, {
        "resourceType": "Asset",
        "identifier": {
            "name": "DST_Table",
            "domain": {
                "id": "44f84eb8-cf7a-4e0a-a857-0c4becfedc20"
            }
        },
        "type": {
            "name": "Table"
        },
        "name": "DST_Table"
    }, {
        "resourceType": "Asset",
        "identifier": {
            "name": "SRC_Table > SRC_Col1",
            "domain": {
                "id": "44f84eb8-cf7a-4e0a-a857-0c4becfedc20"
            }
        },
        "type": {
            "name": "Column"
        },
        "name": "SRC_Table > SRC_Col1"
    }, {
        "resourceType": "Asset",
        "identifier": {
            "name": "SRC_Table > SRC_Col2",
            "domain": {
                "id": "44f84eb8-cf7a-4e0a-a857-0c4becfedc20"
            }
        },
        "type": {
            "name": "Column"
        },
        "name": "SRC_Table > SRC_Col2"
    }, {
        "resourceType": "Asset",
        "identifier": {
            "name": "SRC_Table > SRC_Col3",
            "domain": {
                "id": "44f84eb8-cf7a-4e0a-a857-0c4becfedc20"
            }
        },
        "type": {
            "name": "Column"
        },
        "name": "SRC_Table > SRC_Col3"
    }, {
        "resourceType": "Asset",
        "identifier": {
            "name": "DST_Table > DST_ColA",
            "domain": {
                "id": "44f84eb8-cf7a-4e0a-a857-0c4becfedc20"
            }
        },
        "type": {
            "name": "Column"
        },
        "name": "DST_Table > DST_ColA"
    }, {
        "resourceType": "Asset",
        "identifier": {
            "name": "DST_Table > DST_ColB",
            "domain": {
                "id": "44f84eb8-cf7a-4e0a-a857-0c4becfedc20"
            }
        },
        "type": {
            "name": "Column"
        },
        "name": "DST_Table > DST_ColB"
    }, {
        "resourceType": "Asset",
        "identifier": {
            "name": "DST_Table > DST_ColC",
            "domain": {
                "id": "44f84eb8-cf7a-4e0a-a857-0c4becfedc20"
            }
        },
        "type": {
            "name": "Column"
        },
        "name": "DST_Table > DST_ColC"
    }, {
        "resourceType": "Complex Relation",
        "identifier": {
            "relations": {
                "00000000-0000-0000-0000-000000007093:TARGET": [{
                        "name": "FK Name",
                        "domain": {
                            "id": "44f84eb8-cf7a-4e0a-a857-0c4becfedc20"
                        }
                    }
                ],
                "00000000-0000-0000-0000-000000007092:TARGET": [{
                        "name": "SRC_Table > SRC_Col1",
                        "domain": {
                            "id": "44f84eb8-cf7a-4e0a-a857-0c4becfedc20"
                        }
                    }
                ],
                "00000000-0000-0000-0000-000000007091:TARGET": [{
                        "name": "DST_Table > DST_ColA",
                        "domain": {
                            "id": "44f84eb8-cf7a-4e0a-a857-0c4becfedc20"
                        }
                    }
                ]
            }
        },
        "complexRelationType": {
            "name": "Foreign Key Mapping"
        },
        "attributes": {
            "Key sequence": [{
                    "value": 1
                }
            ]
        }
    }, {
        "resourceType": "Complex Relation",
        "identifier": {
            "relations": {
                "00000000-0000-0000-0000-000000007093:TARGET": [{
                        "name": "FK Name",
                        "domain": {
                            "id": "44f84eb8-cf7a-4e0a-a857-0c4becfedc20"
                        }
                    }
                ],
                "00000000-0000-0000-0000-000000007092:TARGET": [{
                        "name": "SRC_Table > SRC_Col2",
                        "domain": {
                            "id": "44f84eb8-cf7a-4e0a-a857-0c4becfedc20"
                        }
                    }
                ],
                "00000000-0000-0000-0000-000000007091:TARGET": [{
                        "name": "DST_Table > DST_ColB",
                        "domain": {
                            "id": "44f84eb8-cf7a-4e0a-a857-0c4becfedc20"
                        }
                    }
                ]
            }
        },
        "complexRelationType": {
            "name": "Foreign Key Mapping"
        },
        "attributes": {
            "Key sequence": [{
                    "value": 2
                }
            ]
        }
    }, {
        "resourceType": "Complex Relation",
        "identifier": {
            "relations": {
                "00000000-0000-0000-0000-000000007093:TARGET": [{
                        "name": "FK Name",
                        "domain": {
                            "id": "44f84eb8-cf7a-4e0a-a857-0c4becfedc20"
                        }
                    }
                ],
                "00000000-0000-0000-0000-000000007092:TARGET": [{
                        "name": "SRC_Table > SRC_Col3",
                        "domain": {
                            "id": "44f84eb8-cf7a-4e0a-a857-0c4becfedc20"
                        }
                    }
                ],
                "00000000-0000-0000-0000-000000007091:TARGET": [{
                        "name": "DST_Table > DST_ColC",
                        "domain": {
                            "id": "44f84eb8-cf7a-4e0a-a857-0c4becfedc20"
                        }
                    }
                ]
            }
        },
        "complexRelationType": {
            "name": "Foreign Key Mapping"
        },
        "attributes": {
            "Key sequence": [{
                    "value": 3
                }
            ]
        }
    }
]

Results

alexandrebargeton

OP4 years ago · Edited

I think there is a missunderstanding here.

With the Import REST API, we must be able to import in mass database schemas / tables / columns and foreign keys, with all these metadata inside CSV file (or excel or directly in json). With CSV format, we have to provide a JSON mapping file, to map the CSV content with our metamodel (column 1 in CSV is an asset type column, column 2 in CSV is an asset type table, column 1 is related to column2…).

But the tutorial basically describe the process for importing complex relation (i.e. foreign keys in my case)… and the provided explanation / example is really poor and not sufficient for a beginer like me. But I’m quite sure we can import Foreign Keys in CSV + JSON mapping… But how?

For a little more details for my technical situation: I can’ t connect Collibra to our Databases. So I recieve a weekly extraction of our databases schemas in CSV,… It’s not a big deal, I import these CSV via theCollibra REST Import API. I do some preprocessing of the CSV file with common linux commands (sed, awk,…); for example to transform the “column name” to be “table name > column name” to have a unique name.

But here I do understand how to manage the Foreign Key JSON mapping…

alexandrebargeton

OP4 years ago · Edited

arthur.burkhardt:

One thing though: you need a proper programming/script language with strong data manipulation capabilities to implement this. (i.e. python, ruby, javascript, java, etc.) . You cannot just interpolate strings from a CSV, you need to group your columns based on some keys, etc.

Hmm…
So to import schema/table/column (without FK) we can do it with CSV + JSON mapping; but for FK (i.e. complex relation) we can’t? I have to write the data into a full JSON file?

arthurburkhardt

·4 years ago · Edited

Well, you need neither CSV nor JSON => Every programming language has its own data structures.
But you will probably receive foreign keys in a tabular structures (i.e. rows and columns) and you will need to transform it into a map[array[map]] structure. While it is very easy to do with programming languages with strong data manipulation capabilities, it is impossible (or very hard) to do with just string interpolation.

Here’s an example: tabular foreign key data above, and groupby to get an array of referenced columns on the bottom.

arthurburkhardt

·4 years ago · Edited

This post contains inaccuracies, but has been left for reference. Please see this post for a complete and accurate answer: https://datacitizens.collibra.com/forum/t/rest-api-import-foreign-key-from-csv-file-how-to/970/6?u=arthur.burkhardt

Original post

A complex relation has multiple legs (~relations), like in this diagram

With the import API, you don’t know the ID of the complex relation and it does not have a unique name either. So you need to identify it based on its legs. So you declare the legs twice: once in the identifier (to find if the complex relation already exists) and once in the body to create it if it does not exist. IF you wanted to change a foreign key mapping, you could define a different identifier and body. In practice, you just remove all foreign keys that don’t exist anymore and create the new ones.
You can see that the value corresponding to a relation (e.g. “00000000-0000-0000-0000-000000007092:TARGET” is an array (list). So you can put as many columns as you want there.

e.g.

      "00000000-0000-0000-0000-000000007092:TARGET": [
        {"name":"${7}","domain":{"name":"${5}","community":{"name":"Technical Data"}}},
        {"name":"${7}","domain":{"name":"${5}","community":{"name":"Technical Data"}}},
        {"name":"${7}","domain":{"name":"${5}","community":{"name":"Technical Data"}}}
      ]

One thing though: you need a proper programming/script language with strong data manipulation capabilities to implement this. (i.e. python, ruby, javascript, java, etc.) . You cannot just interpolate strings from a CSV, you need to group your columns based on some keys, etc.