Prepare the Input Data

The Bulk Loader can load data into the EAS from either a shapefile or comma-separated value (CSV) file, and is run on the ETL server as a job.py job.  Currently, the ETL server is running the Windows operating system.

Shapefile

Ensure that your shapefile has a defined projection before attempting to load it into EAS

• Robert Gaston 21 Jun 2017 Is there a preferred defined projection?  Is it necessary to have a defined projection in the first place?

Shapefiles must be named bulkload.shp and placed in the following directory on the ETL server:

C:\apps\eas_automation\app_data\data\bulkload_shapefile

Input shapefiles require the following schema:

Examples

Comma-separated Value (CSV) file

CSV files must be named bulkload_data.csv and placed in the following directory on the ETL server:

C:\apps\eas_automation\app_data\data

Input CSV files require the following schema:

Examples

* Use this field for streets that have a Street Name Post Directional, for example the "North" in "25th Ave North".

Prepare the Output Table

The Bulk Loader will produce an output table (bulkloader.address_extract) that contains, among other information, the disposition of each input address record that was submitted to the Bulk Loader.  The output table may already have records in it for any of the following reasons:

• The Bulk Loader was just run and the shapefile or CSV file contained at least one address record in it, and so the output table contains at least one result record.
• The database in the current environment (for example DEV) was restored from the database in another environment (for example PROD), and the output table from the other environment (PROD) contained result records from a bulk-load that was performed in that environment (PROD).  Please see the page Restoring for detailed instructions for performing such a database restoration.

If you do not want these existing records to be considered in your upcoming bulk-load attempt, then these records should be deleted prior to running your bulk-load by following these steps:

1. Establish your SSH tunnel to the target EAS database server.
2. Invoke pgAdmin and navigate to the target database, and then the output table (bulkloader.address_extract).
3. Right-click the output table and invoke the Query Tool.

4. Verify that the output table is not empty.

   SELECT COUNT(*) AS record_count
   FROM   bulkloader.address_extract;

5. If the output table is not empty (record_count > 0), then truncate the output table and vacuum the database.

   TRUNCATE bulkloader.address_extract;
   VACUUM FULL ANALYZE;

6. Verify that the output table is now empty (record_count = 0).

   SELECT COUNT(*) AS record_count
   FROM   bulkloader.address_extract;

7. After your next bulk-load attempt, the output table will only contain results from that bulk-load attempt.
8. Repeat these steps as needed.

Run the Bulk Loader

The Bulk Loader is run on the ETL server, and depending on the number of address records that are going to be processed, the Bulk Loader will complete its work at any time from a few minutes, to a few hours.  The following table contains some information for some typical runs.

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

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

   cd C:\apps\eas_automation\automation\src

3. Stage the input data.

   Shapefile Input Format

   python job.py --job stage_bulkload_shapefile --env [Your Environment] --action EXECUTE

   CSV Input Format

   python job.py --job stage_bulkload_csv --env [Your Environment] --action EXECUTE

4. Run the bulkload job.

   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.

   -- 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.

   -- 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.

   -- 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.

   -- 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.

   -- 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.

   -- 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 of the most recent bulk load.

   -- 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.

2. Count and view new base addresses associated with the change request.

   -- Count new base addresses
   SELECT COUNT (*)
   FROM public.addresses
   WHERE activate_change_request_id = change_request_id_goes_here
   AND unit_num IS NULL
   
   -- View new base addresses
   SELECT *
   FROM public.addresses
   WHERE activate_change_request_id = change_request_id_goes_here
   AND unit_num IS NULL.

3. Count and view new unit addresses associated with the change request.

   --- Count new unit addresses
   SELECT COUNT (*)
   FROM public.addresses
   WHERE activate_change_request_id = change_request_id_goes_here
   AND NOT unit_num IS NULL
   
   -- View new unit addresses
   SELECT *
   FROM public.addresses
   WHERE activate_change_request_id = change_request_id_goes_here
   AND NOT unit_num IS NULL

4. Count and view all new addresses associated with the change request.

   -- Count all new addresses
   SELECT COUNT (*)
   FROM public.addresses
   WHERE activate_change_request_id = change_request_id_goes_here
   
   -- View all new addresses
   SELECT COUNT (*)
   FROM public.addresses
   WHERE activate_change_request_id = change_request_id_goes_here

Free space after large Bulk Loader batches

After successful loading and evaluating large batches of addresses, clean up the database by truncating the Bulk Loader output table (bulkloader.address_extract) and vacuuming the database.

TRUNCATE bulkloader.address_extract;
VACUUM FULL ANALYZE;