Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   MySQL (https://www.askmehelpdesk.com/forumdisplay.php?f=442)
-   -   Writing SQL query output into text files of configsize (shell script & MySQL code at) (https://www.askmehelpdesk.com/showthread.php?t=604786)

  • Oct 17, 2011, 10:54 PM
    josepjerry
    Writing SQL query output into text files of configsize (shell script & MySQL code at)
    In the application requirement, the current and required funcationality are as follows:-

    Current scenario:-
    (I)A MySQL stored proc is called from the shell script as given in below code.
    (ii)The procedure generates the transaction dump file by querying the relevant table
    And using the syntax "SELECT * FROM table INTO OUTFILE <file_name>".
    (iii)A single large file is generated based on volume of transactions.
    (iv) A cron is scheduled to generate the files at 1 hour interval.

    Required scenario:-
    Required that, at any given run of the cron job, the maximum size of file
    Generated by the stored proc should not exceed, say 500MB. If it exceeds,
    The limit, write the rest of the records into a second / third file and so on.

    That is it is required to control the file generation from the time the
    Procedure is called.
    Note:-In the code given below the place where stored proc is called is marked in bold red.
    I presume the change has to be done here.

    Code:
    Code:

    #The shell script is shown below:-

    #----------------------------------------------------------------------------------------
    #!/bin/bash
    #!/usr/bin/bsh

    #TEST_HOME : Path of application home
    #SDF_CONFIG.DAT : Configuration file defining variables for schema,port,host,MySQL Home,Application home,file dump path etc.
    #MYSQL_HOME : MySQL Home path defined in SDF_CONFIG.DAT file
    #ROOT_PSWD : Root Password as defined in SDF_CONFIG.DAT file
    #MYSQL_HOST : MySQL Host IP as defined in SDF_CONFIG.DAT file
    #MYSQL_PORT : Port as defined in SDF_CONFIG.DAT file
    #DUMP_HOST_PATH: Path of the generated tranasaction dump file, defined in SDF_CONFIG.DAT file

    TEST_HOME=/root/Test

    if test -f $TEST_HOME/Program/SysConfigs/SDF_CONFIG.DAT; then
    source $TEST_HOME/Program/SysConfigs/SDF_CONFIG.DAT
    else
    echo "Error: SDF_CONFIG.DAT not found"
    exit
    fi;

    DG=$(date +%d-%m-%Y_%T) # date of generation

    #Call MySQL Procedure that will generate the transaction files
    #Required to generate file with a configured size (fetched from database or hardcoded)
    #also if the file size exceeds the specified size, write into a new file (again of max size and so on till data is exhausted)
    $MYSQL_HOME/bin/mysql -e 'Call transaction.offline_transaction_proc()' -u root -p$ROOT_PSWD -h $MYSQL_HOST -P$MYSQL_PORT --skip-column-names


    echo "File generation Completed !!"
    exit 0;
    #---------------------------------------------------------------------------------------


    ########################################################################################


    The MySQL stored procedure being called is :-
    ----------------------------------------------------------------------------------------

    DELIMITER $$

    DROP PROCEDURE IF EXISTS `tranasction`.`offline_transaction_proc`$$

    CREATE PROCEDURE `offline_transaction_proc`()
    BEGIN
    DECLARE v_reason_code int(2);
    DECLARE v_offline_file_path_name varchar(750);
    DECLARE v_offline_query varchar(5000);
    DECLARE v_offline_where_clause varchar(1024);
    DECLARE v_csv_path varchar(512);
    DECLARE v_from_date varchar(30);
    DECLARE v_from_date_temp varchar(30);
    DECLARE v_to_date varchar(30);
    DECLARE v_current_date varchar(30);
    label: BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION SET v_reason_code = -99;

    --datetime from which to generate the transaction dump
    SELECT param_value INTO v_from_date
    FROM configuration.configuration_detail
    WHERE param_id=4;

    --update param value to the datetime upto which the transactions are picked up for processing
    UPDATE configuration.configuration_detail
    SET param_value= now()
    WHERE param_id=5;

    --fetch and store in variable the datetime upto which to be fetched
    SELECT param_value INTO v_to_date
    FROM rt_configuration.configuration_detail
    WHERE param_id=5;

    --fetch the CSV path wherin to store the file
    SELECT param_value INTO v_csv_path
    FROM rt_configuration.configuration_detail
    WHERE param_id=7;

    SET v_from_date_temp=v_from_date;

    --do following until the from datetime range is less than current time fetched in variable
    --ie;increment the from time by one hour and see if it is less than to time, if not continue
    WHILE DATE_ADD(v_from_date_temp, INTERVAL 1 hour) < v_to_date
    DO

    --read tranasctions from main table for writing to file
    SET v_reason_code = 0;
    --Prepared statement using SELECT INTO OUTFILE <filename>
    --filename is dynamic based on time of generation
    SET v_offline_query = "SELECT * FROM offline_transaction WHERE dump_time BETWEEN ";
    SET v_offline_file_path_name = concat(v_csv_path, "/OFFLINE/OFFLINE_", DATE_FORMAT(DATE_ADD(v_from_date_temp,
    INTERVAL 1 hour),'%Y%m%d.%H.%i.%s'), ".TXT");
    SET v_offline_file_path_name = concat("'", v_offline_file_path_name, "'");
    SET @v_offline_where_clause = concat( "'", v_from_date_temp , "' AND '",

    DATE_ADD(v_from_date_temp, INTERVAL 1 hour), "' INTO OUTFILE ");
    SET @v_offline_final_query = concat(v_offline_query, @v_offline_where_clause,

    v_offline_file_path_name, " FIELDS TERMINATED BY ',' ");
    PREPARE offline_stmt FROM @v_offline_final_query;
    EXECUTE offline_stmt;
    DEALLOCATE PREPARE offline_stmt;--increment and update the configuration table to fromtime plus one hour
    UPDATE configuration.configuration_detail
    SET param_value=DATE_ADD(v_from_date_temp, INTERVAL 1 hour)
    WHERE param_id=4;

    SET v_from_date_temp = DATE_ADD(v_from_date_temp, INTERVAL 1 hour);

    -- call another procedure to drop and recreate the partition corresponding to the dump time
    -- from the offline_transaction table inorder to purge the data.
    Call partition_proc('offline');

    END WHILE; --continue loop till from time is less than to time.
    --update the dumptime completion time
    UPDATE configuration.configuration_detail
    SET param_value=now()
    WHERE param_id=6;
    END;
    END$$

    DELIMITER ;

    ----------------------------------------------------------------------------------------


  • All times are GMT -7. The time now is 06:26 PM.