<commit_before># Idea: to validate data in a text file, we first load it to a data table
# then use the validation utilities to validate the table
#
# The followings are performed by this script:
#     * 1 Load raw text files into corresponding SQL tables
#     * 2 Perform validation
#     * 3 Generate reports
#
# @author NghiPM
# @date May 2014

###################################################################################
# SET UP ENVIRONMENT VARIABLES
###################################################################################
# Instead of passing PostgreSQL credentials as parameters to every validation command,
# you can set the corresponding environment variables which can be used by the those commands
export HOST="localhost"
export USERNAME="postgres"
export PASSWORD="<PASSWORD>"
export DATABASE="sampledb"
export LISTEN=5432

# Input file paths and corresponding table names
FVENDOR="VendorMaster.csv"
VENDOR="vendors"

FITEM="ItemMaster.csv"
ITEM="items"

# Specify a temp folder for writing temporary outputs
# Specify the path to the output summary report
TMP="/tmp"
REPORT="/tmp/report.xls"

###################################################################################
# STEP 1 - Load raw text files into corresponding SQL tables
###################################################################################

# Load data from VendorMaster.csv to the corresponding vendors table
# and from ItemMaster.csv to items table.
# Note: instead of using iload utility,you can use the PSQL COPY of PostgreSQL
iload -i "$FVENDOR" -t "$VENDOR" -f csv
iload -i "$FITEM" -t "$ITEM" -f csv

###################################################################################
# STEP 2 - Perform validation, log the results to an additional field
###################################################################################
# validate the vendors table
ivalidate --table=$VENDOR --log-to=validation_errors \
       --not-null="vendor_code" \
       --not-null="vendor_name" \
       --unique="vendor_code" \
       --unique="vendor_name" \
       --match="vendor_code/[a-zA-Z0-9]/" \
       --match="vendor_name/[a-zA-Z0-9]/" \
       --consistent-by="vendor_code|vendor_name" \
       --consistent-by="vendor_name|vendor_code" \
       --consistent-by="country_code|country_name" \
       --consistent-by="country_name|country_code"

# validate the items table
ivalidate --table=$ITEM \
       --log-to=validation_errors \
       --not-null="item_id" \
       --match="item_id/[a-zA-Z0-9]/" \
       --not-null="item_desc" \
       --match="item_desc/[a-zA-Z0-9]/" \
       --not-null="item_uom" \<commit_msg>Guideline update<commit_after># Idea: to validate data in a text file, we first load it to a data table
# then use the validation utilities to validate the table
#
# The followings are performed by this script:
#     * 1 Load raw text files into corresponding SQL tables
#     * 2 Perform validation
#     * 3 Generate reports
#
# @author NghiPM
# @date May 2014

###################################################################################
# SET UP ENVIRONMENT VARIABLES
###################################################################################
# Instead of passing PostgreSQL credentials as parameters to every validation command,
# you can set the corresponding environment variables which can be used by the those commands
export HOST="localhost"
export USERNAME="postgres"
export PASSWORD="<PASSWORD>"
export DATABASE="sampledb"
export LISTEN=5432

# Input file paths and corresponding table names
FVENDOR="VendorMaster.csv"
VENDOR="vendors"

FITEM="ItemMaster.csv"
ITEM="items"

# Specify a temp folder for writing temporary outputs
# Specify the path to the output summary report
TMP="/tmp"
REPORT="/tmp/report.xls"

###################################################################################
# STEP 1 - Load raw text files into corresponding SQL tables
###################################################################################

# Load data from VendorMaster.csv to the corresponding vendors table
# and from ItemMaster.csv to items table.
# Note: instead of using iload utility,you can use the PSQL COPY of PostgreSQL
#       in such case, make sure an AUTO ID (unique) field is added to every table
iload -i "$FVENDOR" -t "$VENDOR" -f csv
iload -i "$FITEM" -t "$ITEM" -f csv

###################################################################################
# STEP 2 - Perform validation, log the results to an additional field
###################################################################################
# validate the vendors table
ivalidate --table=$VENDOR --log-to=validation_errors \
       --not-null="vendor_code" \
       --not-null="vendor_name" \
       --unique="vendor_code" \
       --unique="vendor_name" \
       --match="vendor_code/[a-zA-Z0-9]/" \
       --match="vendor_name/[a-zA-Z0-9]/" \
       --consistent-by="vendor_code|vendor_name" \
       --consistent-by="vendor_name|vendor_code" \
       --consistent-by="country_code|country_name" \
       --consistent-by="country_name|country_code"

# validate the items table
ivalidate --table=$ITEM \
       --log-to=validation_errors \
       --not-null="item_id" \
       --match="item_id/[a-zA-Z0-9]/" \
       --not-null="item_desc" \
       --match="item_desc/[a-zA-Z0-9]/" \
       --not-null="item_uom" \
{'fraction_non_alphanumeric': 0.2784904257296895, 'fraction_numerical': 0.007808142777467931, 'mean_word_length': 4.2745098039215685, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6901536', 'n_tokens_mistral': 1481, 'n_tokens_neox': 1419, 'n_words': 444}