Telco Engineering Project Example

 

CDR Traffic Processing on MySql on Linux Redhat Platform: You are an advanced linux and MySql Admin. You are also familiar with the Telecom industry and Nanpa. You will help me to create an MOU database in MySql that stores call detail records. First let me know if you are familiar with LERG6, LERG1, LERG7, and LERG7SHA 

Yes, I am familiar with the terms LERG6, LERG1, LERG7, and LERG7SHA, which are related to the telecommunications industry and NANPA (North American Numbering Plan Administration). These terms refer to various data files that provide information about telephone numbering plans, routing, and network structure.

LERG1: Local Exchange Routing Guide, contains information about local exchange carriers, their service areas, and associated numbering plan area (NPA) codes.

LERG6: Provides information about local access and transport area (LATA) boundaries, which are used for the purpose of routing and billing long-distance calls within the United States.

LERG7: Contains information about the rate centers, which are geographic areas used for billing purposes and determining the cost of a call.

LERG7SHA: A SHAred version of the LERG7 file, which is used for inter-carrier billing and reconciliation purposes.

To create an MOU (Minutes of Use) database in MySQL that stores call detail records, you'll first need to decide which information you want to store from these LERG files. You may also need to consider additional data, such as call timestamps, duration, and call types. Once you have determined the necessary data, you can create a database schema to store this information.

Here's a suggested schema to get you started:

Create a database named 'mou_database':

sql

Copy code

CREATE DATABASE mou_database;

Use the created database:

Copy code

USE mou_database;

Create a table named 'call_detail_records' with relevant columns:

sql

Copy code

CREATE TABLE call_detail_records (

    id INT AUTO_INCREMENT PRIMARY KEY,

    originating_number VARCHAR(15),

    terminating_number VARCHAR(15),

    call_timestamp DATETIME,

    call_duration INT,

    call_type ENUM('Local', 'Long-Distance', 'International'),

    npa_originating INT,

    npa_terminating INT,

    lata_originating INT,

    lata_terminating INT,

    rate_center_originating VARCHAR(50),

    rate_center_terminating VARCHAR(50)

);

This schema assumes that you want to store information about the originating and terminating numbers, call timestamp, duration, call type, as well as data from LERG1, LERG6, and LERG7 files. You can modify the schema as needed to store additional information or better align with your specific requirements.

User:

Ok, so let's assume the raw files for the cdrs are stored as .csv files. Write an import script and remember the OS is linux redhat and the database is as mentioned.

To import call detail records (CDRs) from CSV files into the MySQL database, you can use a Python script with the pymysql library to connect to the database and the csv library to read the CSV files. First, you need to install the pymysql library if you haven't already:

bash

Copy code

pip install pymysql

Next, create a Python script named import_cdr.py with the following content:

python

Copy code

import csv

import pymysql

# Database configuration

db_config = {

    'host': 'localhost',

    'user': 'your_mysql_user',

    'password': 'your_mysql_password',

    'database': 'mou_database'

}

# Update the path to your CSV file

csv_file_path = '/path/to/your/cdr.csv'

# Connect to the MySQL database

connection = pymysql.connect(**db_config)

cursor = connection.cursor()

# Read the CSV file

with open(csv_file_path, 'r') as csvfile:

    cdr_reader = csv.reader(csvfile)

    # Skip the header row if present

    next(cdr_reader)

    # Iterate through the rows in the CSV file

    for row in cdr_reader:

        # Prepare the SQL INSERT statement

        insert_query = f"""

        INSERT INTO call_detail_records (

            originating_number,

            terminating_number,

            call_timestamp,

            call_duration,

            call_type,

            npa_originating,

            npa_terminating,

            lata_originating,

            lata_terminating,

            rate_center_originating,

            rate_center_terminating

        ) VALUES (

            '{row[0]}',

            '{row[1]}',

            '{row[2]}',

            {row[3]},

            '{row[4]}',

            {row[5]},

            {row[6]},

            {row[7]},

            {row[8]},

            '{row[9]}',

            '{row[10]}'

        );

        """

        # Execute the SQL INSERT statement

        cursor.execute(insert_query)

# Commit the transaction and close the connection

connection.commit()

cursor.close()

connection.close()

Remember to replace 'your_mysql_user', 'your_mysql_password', and /path/to/your/cdr.csv with your MySQL credentials and the correct path to your CSV file.

Finally, execute the script to import the CDRs from the CSV file into the MySQL database:

bash

Copy code

python import_cdr.py

The script reads the CSV file, connects to the MySQL database, and inserts the CDR data into the call_detail_records table. Make sure the CSV file has the same column order as specified in the schema, and update the script accordingly if any changes are needed.

User:

ok, but give me instructions to install and configure python. Assume you are the root user or have proper access permission.

To install Python on a Red Hat-based system (such as CentOS or Fedora), you can use the following instructions. As a root user or using sudo, you'll have the necessary permissions to install and configure Python:

First, update your package manager to make sure you have the latest package information:

bash

Copy code

sudo yum update -y

Install the EPEL (Extra Packages for Enterprise Linux) repository, which provides additional packages for Red Hat-based systems:

bash

Copy code

sudo yum install -y epel-release

Install Python (this example uses Python 3.8, but you can replace it with your desired version):

bash

Copy code

sudo yum install -y python38

Verify that Python has been installed correctly by checking the version:

bash

Copy code

python3.8 --version

Next, install pip, the package installer for Python, which allows you to install Python libraries easily:

bash

Copy code

sudo yum install -y python38-pip

Verify that pip has been installed correctly:

bash

Copy code

pip3.8 --version

Now, Python and pip are installed on your system. You can use python3.8 to run Python scripts and pip3.8 to install Python packages.

As mentioned in the previous answer, you will need to install the pymysql library to connect to the MySQL database. With pip installed, you can now install the library:

bash

Copy code

pip3.8 install pymysql

With Python and the required library installed, you can create and run the import_cdr.py script as explained in the previous answer to import CDRs from a CSV file into the MySQL database.

User:

ok, so make up a sample raw file to import with 10 records for the .csv file but also show it as a table

Here is a sample CSV file with 10 records. The columns are as follows: originating_number, terminating_number, call_timestamp, call_duration, call_type, npa_originating, npa_terminating, lata_originating, lata_terminating, rate_center_originating, and rate_center_terminating.

Sample CSV file content:

sql

Copy code

originating_number,terminating_number,call_timestamp,call_duration,call_type,npa_originating,npa_terminating,lata_originating,lata_terminating,rate_center_originating,rate_center_terminating

1234567890,0987654321,2023-04-20 10:00:00,120,Local,123,098,100,200,NewYork,LosAngeles

1234567891,0987654322,2023-04-20 10:05:00,300,Long-Distance,124,099,101,201,Chicago,Atlanta

1234567892,0987654323,2023-04-20 10:10:00,150,International,125,100,102,202,Houston,Miami

1234567893,0987654324,2023-04-20 10:15:00,450,Local,126,101,103,203,Dallas,Austin

1234567894,0987654325,2023-04-20 10:20:00,180,Long-Distance,127,102,104,204,Boston,Detroit

1234567895,0987654326,2023-04-20 10:25:00,320,International,128,103,105,205,Seattle,Denver

1234567896,0987654327,2023-04-20 10:30:00,270,Local,129,104,106,206,Portland,SanFrancisco

1234567897,0987654328,2023-04-20 10:35:00,360,Long-Distance,130,105,107,207,SanDiego,SanJose

1234567898,0987654329,2023-04-20 10:40:00,240,International,131,106,108,208,Phoenix,LasVegas

1234567899,0987654330,2023-04-20 10:45:00,390,Local,132,107,109,209,Philadelphia,Baltimore

Here is the same data displayed as a table:

originating_number	terminating_number	call_timestamp	call_duration	call_type	npa_originating	npa_terminating	lata_originating	lata_terminating	rate_center_originating	rate_center_terminating
1234567890	0987654321	2023-04-20 10:00:00	120	Local	123	098	100	200	NewYork	LosAngeles
1234567891	0987654322	2023-04-20 10:05:00	300	Long-Distance	124	099	101	201	Chicago	Atlanta
1234567892	0987654323	2023-04-20 10:10:00	150	International	125	100	102	202	Houston	Miami
1234567893	0987654324	2023-04-20 10:15:00	450	Local	126	101	103	203	Dallas	Austin
1234567894	0987654325	2023-04-20 10:20:00	180	Long-Distance	127	102	104	204	Boston	Detroit
1234567895	0987654326	2023-04-20 10:25:00	320	International	128	103	105	205	Seattle	Denver
1234567896	0987654327	2023-04-20 10:30:00	270	Local	129	104	106	206	Portland	SanFrancisco
1234567897	0987654328	2023-04-20 10:35:00	360	Long-Distance	130	105	107	207	SanDiego	SanJose
1234567898	0987654329	2023-04-20 10:40:00	240	International	131	106	108	208	Phoenix	LasVegas
1234567899	0987654330	2023-04-20 10:45:00	390	Local	132	107	109	209	Philadelphia	Baltimore

To import this sample data, you can create a CSV file with the provided content, adjust the csv_file_path variable in the import_cdr.py script to point to your newly created CSV file, and then run the script as described earlier.

User:Ok, so schedule a linux redhat job using cron to look for raw cdr files every day between midnight and 2 am, Once you find the daily raw cdr files, run a job to import them to the traffic database on the 'TrafficServer'. Assume the incoming files are the .csv files We discussed that your job is to process them for import into the traffic database. Assume the raw files are in /rawcdrs and when you are done, you will find a way to tag them as processed (perhaps by moving them to /cdrarchive folder and renaming them. you will make sure to import each file only once, and only move it once successfully imported, so will need a way to validate that the import succeeded into the traffic database and if it failed, you will re-try 2 times, and then if it still fails, you will create an import error log text file and email that to the cdr_admin@company.com. also, you must check that there is enough free disk space on the server file system and in the database. stop the job and log an error if you run out of space with a friendly message and again email admin.

To set up the scheduled job, you can use a combination of a shell script and a Python script, and then schedule it using cron.

First, create a shell script named process_cdr.sh:

bash

Copy code

#!/bin/bash

# Set the current date for logging purposes

current_date=$(date +%Y-%m-%d)

# Set the source directory and archive directory

raw_cdr_dir="/rawcdrs"

cdr_archive_dir="/cdrarchive"

# Set the Python script to import the CDR data

import_script="/path/to/import_cdr.py"

# Loop through all .csv files in the raw CDR directory

for file in $raw_cdr_dir/*.csv; do

    # Check if the file exists and is not empty

    if [ -s "$file" ]; then

        # Try importing the CDR file up to 3 times

        for i in {1..3}; do

            python3.8 $import_script "$file"

            import_result=$?

            # Break the loop if the import was successful

            if [ $import_result -eq 0 ]; then

                break

            fi

        done

        # Check if the import was successful or failed after 3 attempts

        if [ $import_result -eq 0 ]; then

            # Move the file to the archive directory and rename it

            mv "$file" "$cdr_archive_dir/${file##*/}-$current_date"

        else

            # Log an error and send an email to the admin

            error_log="$raw_cdr_dir/import_error_$current_date.log"

            echo "Failed to import $file after 3 attempts" >> "$error_log"

            mail -s "CDR Import Error" cdr_admin@company.com < "$error_log"

        fi

    fi

done

Modify the import_cdr.py script to accept the file path as an argument:

python

Copy code

import sys

import csv

import pymysql

# Database configuration

db_config = {

    'host': 'TrafficServer',

    'user': 'your_mysql_user',

    'password': 'your_mysql_password',

    'database': 'traffic_database'

}

# Get the CSV file path from the command-line argument

csv_file_path = sys.argv[1]

# (Rest of the import_cdr.py script)

Make the shell script executable:

bash

Copy code

chmod +x process_cdr.sh

Schedule the script to run every day between midnight and 2 AM using cron. Open the cron configuration:

bash

Copy code

crontab -e

Add the following line to the end of the file:

javascript

Copy code

0 0-2 * * * /path/to/process_cdr.sh

Replace /path/to/process_cdr.sh with the correct path to your shell script.

To monitor disk space and stop the job if there isn't enough space, you can add a check at the beginning of the process_cdr.sh script:

bash

Copy code

# Check if there is enough free disk space

min_disk_space=1048576 # In kilobytes, adjust as needed

current_disk_space=$(df -k . | tail -1 | awk '{print $4}')

if [ $current_disk_space -lt $min_disk_space ]; then

    echo "Not enough disk space. Required: $min_disk_space KB, Available: $current_disk_space KB" | mail -s "CDR Import Error: Insufficient Disk Space" cdr_admin@company.com

    exit 1

fi

Adjust the min_disk_space value to the minimum required free disk space (in kilobytes) for your application. The script checks the available disk space and sends an email to the admin if it is lower than the required value.

To check the available space in the MySQL database, you can add a check at the beginning of the import_cdr.py script:

python

Copy code

# Function to check the available space in the database

def check_database_space():

    with pymysql.connect(**db_config) as connection:

        with connection.cursor() as cursor:

            cursor.execute("SELECT table_schema 'database', SUM(data_length + index_length) 'used_space', SUM(data_free) 'free_space' FROM information_schema.TABLES WHERE table_schema = %s GROUP BY table_schema", (db_config['database'],))

            result = cursor.fetchone()

            if result:

                used_space, free_space = result[1], result[2]

                return free_space

            else:

                return None

# Check the available space in the database

min_db_space = 1048576  # In bytes, adjust as needed

current_db_space = check_database_space()

if current_db_space is None or current_db_space < min_db_space:

    print("Not enough space in the database. Required: {} bytes, Available: {} bytes".format(min_db_space, current_db_space))

    sys.exit(1)

# (Rest of the import_cdr.py script)

Adjust the min_db_space value to the minimum required free space (in bytes) for your database. The script checks the available space in the MySQL database and exits with an error message if it is lower than the required value. The shell script will then send an email to the admin with the error log.

With these changes, the process_cdr.sh script will look for raw CDR files in the /rawcdrs directory and process them one by one. If the import is successful, the script will move the processed file to the /cdrarchive directory and rename it. If the import fails after three attempts, the script will log the error and send an email to the admin. The script also checks for available disk space on the server and in the MySQL database before processing the files.

User:

Can you summarize our project so far in plain English

SUMMARY

Certainly! In this project, our goal is to create a database in MySQL that stores call detail records (CDRs) for a telecom company. The raw CDR data is provided in CSV files, and we want to process these files and import the data into the MySQL database.

To achieve this, we have created a Python script (import_cdr.py) that reads the data from a CSV file and imports it into the MySQL database. This script has been designed to work with a specific CSV file format, which includes information such as originating and terminating numbers, call timestamps, call durations, call types, and additional data related to the telecom industry (e.g., NPAs and LATAs).

We have also created a shell script (process_cdr.sh) that works as a scheduled job on a Linux RedHat system. This script runs daily between midnight and 2 AM, scanning the /rawcdrs directory for any new raw CDR files (in CSV format). Once it finds a file, it attempts to import the data using the import_cdr.py script. If the import is successful, the file is moved to the /cdrarchive directory and renamed to indicate that it has been processed. If the import fails after three attempts, an error log is created, and an email is sent to the administrator.

Additionally, the shell script checks for available disk space on the server and the available space in the MySQL database before processing the files. If there isn't enough space, the script logs an error, sends an email to the administrator, and stops processing.

In summary, we have set up an automated system to process raw CDR files daily, import the data into a MySQL database, and manage the storage of processed files. This system also monitors available disk space and sends notifications to the administrator in case of any issues.