DBI_Oracle_Loader

Overview

The DBI_Oracle_Loader is a process that allows data to be loaded into Oracle using the standard Oracle bulk loader.  The benefits of using this process is that the data can be loaded at anytime a new files shows up in the designated AWS S3 location.  The rules for how to load the data is maintained in a MySQL rules table.  Therefore, once a file is uploaded to the designated location, the file will get processed automatically.  A subsequent email providing the results (whether success or failure) will be sent to the users for review.  The following outlines the steps required by IT and business teams for the completion of the setup and file upload process.

Prerequisites

  1. Configure the table rules in the metadata repository.  These are the rules used when processing the file on the Oracle DB.  This step is completed by IT, but details are provided in part by the business teams when defining the table requirements.
  2. Create the table in the Oracle database that will be loaded.  This is completed by IT, which includes a standard ‘stage’ and ‘history’ table.  Note that the below tables have already been created.
  3. Ensure access to the AWS S3 Bucket is provisioned for each user providing data files.  This step is required in order to gain visibility to the files that are being processed and to review the logs.  Access is provisioned by the AWS Operations team, and can be requested by opening a Servicenow ticket to the group glo-aws-ops.
  4. Optional – If you would like to receive gmail notifications when files are processed, request access to the SNS Topic by opening a Servicenow ticket to the group glo-aws-ops.  The topic information is below.

Business Team Steps for Uploading Data into Oracle

  1. Conform the file to be uploaded in a standard text format and follow the agreed upon file format as defined in step 1 above (Prerequisites).
    1. File names need to conform to the same name as the table names.  Do not put spaces in the file name. The format should be as follows:  <table_name>.txt
    2. Delimiter needs to be a comma (or otherwise if directed)
    3. A Header Row needs to be included as this row is omitted when the file is processed.  If this is not included, then the first row of data is removed which is probably not desired.
    4. The number of columns and the column data types need to be consistent with the table format.  Thus, the data in your file should be aligned with the following except for the INSERT DATE (which is system generated)
  2. Place the file in the designated AWS S3 Bucket location.  Access to this Bucket requires a desktop client tool like CloudBerry Explorer.  Click on this link in order to download this MSI approved application.  After receiving your Access Key ID and Secret Access Key, you can configure a connection to the AWS S3 Bucket

Choose ‘Amazon S3’, pick a suitable Display name and enter the Access key and secret key that was sent to you.

The AWS S3 Bucket we are using is defined as:  “msi-dbi-mashup”. Once you are connected, navigate to the folder ‘ORA_Inbox’.  This is the location where you will drop your data files. You can use the split screens to find the location of your file and copy it to the bucket as you see below.

Once the file has been copied or moved to this location, the file will get processed immediately.  You should receive an email shortly which will include the details of the load process. If the process is NOT successful, the original file is left unchanged.  In order to determine any errors, review the content of the email closely.

Advertisements