Step 2 of the user data pipeline runs the JSON file generated in the translate step and inserts each entry as a record in the target database table.

For the territory example, manage_territory.json is run to insert ~200 national territories into utility.territory.

Structure of the generated process file

Each entry in manage_territory.json calls the manage_territory process with the parameters for one territory:

{
  "process": [
    {
      "root_process_id": "import_tabular_data",
      "process": "manage_territory",
      "delete": false,
      "overwrite": false,
      "parameters": {
        "name": "afghanistan",
        "display_name": "Afghanistan",
        "iso_code_a2": "af",
        "iso_code_a2_ext": "af"
      }
    },
    ...
  ]
}

The delete and overwrite flags default to false. Set overwrite: true to update existing records, or delete: true to remove them. These can also be set globally in the scheme file.

Running step 2 in the notebook

Open project_example/import_data/load_utility_data.ipynb. Step 2 (code block 4) runs the insert process:

#%%script false --no-raise-error
process_file = 'import_data/utility/manage_process/manage_territory.json'

structured_process_D, scheme_params_D = Initiate_process(notebook_path, scheme_file, process_file)

if structured_process_D is not None:

    Run_process(structured_process_D, scheme_params_D)

The framework reads each entry in the file and calls the manage_territory function once per record.

Verifying the data

After the notebook completes, you can verify the inserted records by connecting to the database directly:

SELECT id, name, display_name, iso_code_a2
FROM utility.territory
ORDER BY name
LIMIT 10;

You should see the territory records inserted from the spreadsheet.

Running for your own data

To load your own tabular data:

  1. Create a spreadsheet with column names matching the parameters of your target process
  2. Create a process definition JSON file (see Translate Excel)
  3. Run the translate step to generate the JSON process file
  4. Run the insert step to load the records into the database

The translate and insert steps are independent — you can re-run either one without affecting the other, as long as overwrite and delete are set appropriately.

Input files

File Purpose
manage_territory.json Generated by the translate step; contains one manage_territory process entry per row in territory.xlsx — ~200 entries in total

Updated: