Versions Compared

Old Version 10

changes.mady.by.user Dan King

Saved on

compared with

New Version 11

changes.mady.by.user Dan King

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Create separate evaluation sections: one for 'bulkloader' schema, another for the 'public' schema.

...

  1. Remote desktop (RDP) into the ETL server.

  2. Move to the directory that contains the file job.py.

    Code Block
    languagetext
    cd C:\apps\eas_automation\automation\src


  3. Stage the input data.

    Code Block
    languagetext
    titleShapefile Input Format
    python job.py --job stage_bulkload_shapefile --env [Your Environment] --action EXECUTE


    Code Block
    languagetext
    titleCSV Input Format
    python job.py --job stage_bulkload_csv --env [Your Environment] --action EXECUTE


  4. Run the bulkload job.

    Code Block
    languagetext
    python job.py --job bulkload --env [Your Environment] --action EXECUTE


Evaluate the Results

Schema 'bulkloader'

Evaluate the results of the bulk-load by opening the database and inspecting the output table bulkloader.address_extract for values in the field exception_text

  1. Addresses that were successfully bulk-loaded will have a date in the field load_tms and a NULL value in the field exception_text.

    Code Block
    languagesql
    -- Count records successfully bulk-loaded:
SELECT COUNT(*)
FROM bulkloader.address_extract
WHERE NOT(load_tms IS NULL) AND (exception_text IS NULL);

-- View records successfully bulk-loaded:
SELECT *
FROM bulkloader.address_extract
WHERE NOT(load_tms IS NULL) AND (exception_text IS NULL);


  2. The presence of exception text indicates a problem with the source data.

    Code Block
    languagesql
    -- Count records with exception:
SELECT COUNT(*)
FROM bulkloader.address_extract
WHERE NOT(exception_text IS NULL);

-- View records with exception:
SELECT *
FROM bulkloader.address_extract
WHERE NOT(exception_text IS NULL);


  3. To rectify bulk-load exceptions, do not correct any exception records in the output table bulkloader.address_extract. Instead, correct the corresponding records in the source data and then re-run the Bulk Loader.

  4. If block and lot values were included in the input data, a value should be found in the field address_x_parcel_id. Run the query below to find any records that had block and lot input values but did not get assigned a value for address_x_parcel_id.

    Code Block
    languagesql
    -- Count records where block and lot were provided but no address_x_parcel was assigned:
SELECT COUNT(*) FROM bulkloader.address_extract
WHERE (NOT (block IS NULL) OR NOT (block IS NULL))
AND address_x_parcel_id IS NULL;


  5. Base addresses that were submitted by the Bulk Loader but were already in the EAS are indicated by values in the fields address_base_id and load_tms, and NULL values in the fields street_segment, zone_id or exception_text.

    Code Block
    languagesql
    -- Count base addresses that were already in the EAS:
SELECT COUNT(*)
FROM bulkloader.address_extract
WHERE NOT(address_base_id IS NULL)
      AND NOT(load_tms IS NULL)
      AND (street_segment_id IS NULL)
      AND (zone_id IS NULL)
      AND (exception_text IS NULL);

-- View base addresses that were already in the EAS:
SELECT *
FROM bulkloader.address_extract
WHERE NOT(address_base_id IS NULL)
      AND NOT(load_tms IS NULL)
      AND (street_segment_id IS NULL)
      AND (zone_id IS NULL)
      AND (exception_text IS NULL);


  6. Base addresses that were submitted by the Bulk Loader and were not already in the EAS are indicated by values in the street_segment_id field.

    Code Block
    languagesql
    -- Count base addresses that were not already in the EAS:
SELECT COUNT(*)
FROM bulkloader.address_extract
WHERE NOT (street_segment_id IS NULL)

-- View base addresses that were not already in the EAS:
SELECT *
FROM bulkloader.address_extract
WHERE NOT (street_segment_id IS NULL)


  7. Evaluate records with non-NULL address_id values to count/view addresses that were added to the EAS.

    Code Block
    languagesql
    -- Count addresses added to the EAS:
SELECT COUNT(*)
FROM bulkloader.address_extract
WHERE NOT (address_id IS NULL)

-- View addresses added to the EAS:
SELECT *
FROM bulkloader.address_extract
WHERE NOT (address_id IS NULL)


Schema 'public'

Evaluate tables in the public schema to cross-check record counts from the bulkloader schema evaluation.

  1. Find the change_request_id the most recent bulk load.

    Code Block
    languagesql
    -- Get the change_request_id of the most recent bulk load:
SELECT change_request_id
FROM public.change_requests
WHERE requestor_comment LIKE 'bulk load change request'
ORDER BY change_request_id DESC
LIMIT 1;

-- Make note of this id for the queries in the subsequent steps.


Free space after large Bulk Loader batches

...