Log File 2 MySQL

[JRSmile]

Hi Folks,

i have written an small logfile to MySQL importer. ( need mysql UDF)

expandcollapse popup

#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_UseX64=y
#AutoIt3Wrapper_Change2CUI=y
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****
#include <array.au3>
#include <file.au3>
#include <FileConstants.au3>
#include "mysql.au3"

_MySQL_InitLibrary()
If @error Then Exit MsgBox(0, '', "")
MsgBox(0, "DLL Version:", _MySQL_Get_Client_Version() & @CRLF & _MySQL_Get_Client_Info())

$MysqlConn = _MySQL_Init()
$connected = _MySQL_Real_Connect($MysqlConn, "localhost", "wcl", "wclp", "test")
If $connected = 0 Then
    $errno = _MySQL_errno($MysqlConn)
    MsgBox(0, "Error:", $errno & @LF & _MySQL_error($MysqlConn))
    If $errno = $CR_UNKNOWN_HOST Then MsgBox(0, "Error:", "$CR_UNKNOWN_HOST" & @LF & $CR_UNKNOWN_HOST)
EndIf

$filedate = FileGetTime(@ScriptDir & "\wcl2.txt",0,0)
ConsoleWrite($filedate[0] & @CRLF)

;13:02:43   INSERT INTO log (a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t) VALUES (2008-5-25 20:01:37.937,SPELL_PERIODIC_ENERGIZE,0x00000000000461A3,\"Aithne\",0x512,0xF140067D00000002,\"sneeuwvlok\",0x1112,1539,\"Feed Pet Effect\",0x1,35,4,NULL,NULL,NULL,NULL,NULL,NULL); Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '20:01:37.937,SPELL_PERIODIC_ENERGIZE,0x00000000000461A3,\"Aithne\",0x512,0xF1400' at line 1    0.000 sec


$afile = FileReadToArray(@ScriptDir & "\wcl2.txt")
ConsoleWrite(UBound($afile))
$upper_bound = UBound($afile) - 2
Local $qu = "", $aCL = ""
For $i = 1 To $upper_bound
    Local $qu
    $tmp = StringSplit($afile[$i], ",")
    $tmp2 = StringSplit($afile[$i], " ")
    $date = $tmp2[1]
    $adate = StringSplit($date,"/")
    $aCL = "'" & $filedate[0] & "-" & $adate[1] & "-" & $adate[2] & "','" & $tmp2[2] & "','"
    $tmp3 = StringSplit($tmp[1], " ")
    $aCL &= _MySQL_Real_Escape_String($MysqlConn, $tmp3[4]) & "','"
    $aCL &= _MySQL_Real_Escape_String($MysqlConn, $tmp[2]) & "','"
    For $x = 3 To UBound($tmp) - 1
        $aCL &= _MySQL_Real_Escape_String($MysqlConn, $tmp[$x]) & "','"
    Next
    $aCL = StringTrimRight($aCL, 1)
    For $x = UBound($tmp) - 1 To 17
        $aCL &= "NULL,"
    Next
    $aCL = StringTrimRight($aCL, 1)
    $tmp = 0
    $tmp2 = 0
    $tmp3 = 0
    $qu = "INSERT INTO log (date,time,action,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t) VALUES (" & $aCL & ")"
    $aCL = ""
    ConsoleWrite($qu & @CRLF)
    ToolTip("Step " & $i & " / " & $upper_bound)
    _MySQL_Real_Query($MysqlConn, $qu)
;~  $res = _MySQL_Store_Result($MysqlConn)

;~      $fields = _MySQL_Num_Fields($res)

;~      $rows = _MySQL_Num_Rows($res)
;~      MsgBox(0, "", $rows & "-" & $fields)
    ; Abfrage freigeben
;~  _MySQL_Free_Result($res)
    $aCL = 0
Next
$afile = 0
; Verbindung beenden
_MySQL_Close($MysqlConn)
; MYSQL beenden
_MySQL_EndLibrary()


#cs
    ;~ MsgBox(0,Default,"check ram")
    while True
    $tTimer = TimerInit()
    $hFile = FileOpen( @ScriptDir & "\wcl.txt", 16 ) ; 16 = binary mode
    $sBinData = FileRead( $hFile )
    FileClose( $hFile )
    $file = BinaryToString($sBinData)
    $sBinData = 0
    $afile= StringRegExp(@CRLF & $file, "([^\r\n]*)(?:\r\n|\n|\r|$)", 3)
    $file = 0
    Local $aCL[UBound($afile)][25]
    for $i = 1 to UBound($afile) - 2
    $tmp = StringSplit($afile[$i],",")
    $tmp2 = StringSplit($afile[$i]," ")
    $aCL[$i][0] = $tmp2[1]
    $aCL[$i][1] = $tmp2[2]
    $tmp3 = StringSplit($tmp[1]," ")
    $aCL[$i][2] = $tmp3[4]
    $aCL[$i][3] = $tmp[2]
    for $x = 3 to UBound($tmp)-1
    $aCL[$i][$x] = $tmp[$x]
    Next
    $tmp  = 0
    $tmp2 = 0
    $tmp3 = 0
    Next
    $afile = 0
    ConsoleWrite(round(TimerDiff($ttimer) /1000,2) &" Sekunden"& @CRLF)
    _ArrayDisplay($aCL)
    WEnd
#ce
#cs
    while True
    $tTimer = TimerInit()
    $hFile = FileOpen( @ScriptDir & "\wcl.txt", 16 ) ; 16 = binary mode
    $sBinData = FileRead( $hFile )
    $iBytes = @extended
    FileClose( $hFile )
    $tInts = DllStructCreate( "int[" & $iBytes/4 & "]" )
    $pInts = DllStructGetPtr( $tInts )
    $tBytes = DllStructCreate( "byte[" & $iBytes & "]", $pInts )
    DllStructSetData( $tBytes, 1, $sBinData )
    $sBinData = 0
    ;~ MsgBox(0,Default,"check ram")
    $file = BinaryToString(DllStructGetData($tBytes, 1))
    ;~ MsgBox(0,Default,"check ram")
    $tBytes = 0
    $tInts = 0
    ;~ MsgBox(0,Default,"check ram")
    ConsoleWrite(round(TimerDiff($ttimer) /1000,2) &" Sekunden"& @CRLF)
    $file = 0
    WEnd


    while True
    $tTimer = TimerInit()
    $hFile = FileOpen( @ScriptDir & "\wcl.txt", $FO_READ ) ; 16 = binary mode
    $sStrData = FileRead($hFile)
    FileClose($hFile)
    ConsoleWrite(round(TimerDiff($ttimer) /1000,2) &" Sekunden"& @CRLF)
    $sStrData = 0
    WEnd

    ;~ CombatLogOpen(@ScriptDir & "\wcl.txt")

    Func CombatLogOpen($path)
    Local $iCountLines = _FileCountLines($path)
    Local $aCL, $oldpercent, $file
    ProgressOn("Combat Log Analyser", "Parsing Combat Log: " & $path, "0%", -1, -1)
    $file = FileOpen($path)
    For $i = 1 To $iCountLines
    CombatLogLineParse(FileReadLine($file, $i))
    $percent = Round($i / $iCountLines * 100, 2)
    If $percent <> $oldpercent Then
    ProgressSet($percent, $percent & "% Line: " & $i & "/" & $iCountLines)
    $oldpercent = $percent
    EndIf
    Next
    ProgressOff()
    FileClose($file)
    EndFunc   ;==>CombatLogOpen

    Func CombatLogLineParse($line)
    ConsoleWrite("+ " & $line & @CRLF)
    Local $params = StringSplit($line," ,")
    _ArrayDisplay($params)
    EndFunc   ;==>CombatLogLineParse
#ce

#cs
CREATE TABLE `test`.`log` (
  `date` VARCHAR(10) NULL,
  `time` VARCHAR(12) NULL,
  `action` VARCHAR(45) NULL,
  `d` VARCHAR(45) NULL,
  `e` VARCHAR(45) NULL,
  `f` VARCHAR(45) NULL,
  `g` VARCHAR(45) NULL,
  `h` VARCHAR(45) NULL,
  `i` VARCHAR(45) NULL,
  `j` VARCHAR(45) NULL,
  `k` VARCHAR(45) NULL,
  `l` VARCHAR(45) NULL,
  `m` VARCHAR(45) NULL,
  `n` VARCHAR(45) NULL,
  `o` VARCHAR(45) NULL,
  `p` VARCHAR(45) NULL,
  `q` VARCHAR(45) NULL,
  `r` VARCHAR(45) NULL,
  `s` VARCHAR(45) NULL,
  `t` VARCHAR(45) NULL);
  #ce

a sample file can be downloaded from here: http://www.gurre.eu/wowlogparser/files/WoWCombatLog_080525_25man.zip

i have one big problem, it is awfully slow. 24 lines per second on a decent machine.

i played with different approaches to open the file like memory mapped or binary read and none of them could handle files bigger 800mb.

the usual size of such a file is 1,2gb and i have no clue on how to speed things up.

Is anyone willed to leave some ideas?

@Mods: Please talk to Jos before thinking of locking the thread.

Best Regards,

JR

Edited August 8, 2014 by JRSmile

[JRSmile]

ps; if someone just wants to import a text file without reencoding the columns and with fixed delimiter you can upload the log file to an sql server and execute the following query:

LOAD DATA INFILE 'wcl.txt' REPLACE INTO TABLE log 

FIELDS TERMINATED BY ',' 

OPTIONALLY ENCLOSED BY '"' 

LINES STARTING BY '' 

TERMINATED BY '\r\n'
 
IGNORE 0 LINES

(date,time,action,d,e,f,g,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy)