☰

複数行のデータベースログを1行に変換

tag-icon • tag-icon text-processing
複数行のデータベースログを1行に変換

Postgresデータベースログファイルがあり、複数行のログを1行に変換したいと思います。複数行ログには、省略する必要があるキャリッジリターン、改行、および末尾の行タブがあります。私の研究では、タイムスタンプに基づいて、またはTabをピボットとして使用する2つの方法でこの問題を解決できます。タイムスタンプのない各行には、タブASCII文字があります。タブは常に先頭と同じ位置にあるため、ログ行が同じブロックから来たことを理解するのに役立ちます。それ以外の場合は、新しいタイムスタンプがあるとログブロックが中断されます。

これは私のログファイルです。

ログファイル：

2022-11-03 16:15:54.330 PKT [3421] LOG:  statement: select * from customersabc;

2022-11-03 16:15:54.330 PKT [3421] ERROR:  relation "customersabc" does not exist at character 15

2022-11-03 16:15:54.330 PKT [3421] STATEMENT:  select * from customersabc;

2022-11-03 16:58:26.796 PKT [5221] LOG:  statement: show log_statement;

2022-11-03 16:58:56.674 PKT [5221] LOG:  statement: show log_statement

    ;

2022-11-03 17:01:47.331 PKT [5221] LOG:  statement: SELECT d.datname as "Name",

           pg_catalog.pg_get_userbyid(d.datdba) as "Owner",

           pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",

           d.datcollate as "Collate",

           d.datctype as "Ctype",

           pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"

    FROM pg_catalog.pg_database d

    ORDER BY 1;

2022-11-03 17:03:08.477 PKT [5221] LOG:  statement: SELECT d.datname as "Name",

           pg_catalog.pg_get_userbyid(d.datdba) as "Owner",

           pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",

           d.datcollate as "Collate",

           d.datctype as "Ctype",

           pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"

    FROM pg_catalog.pg_database d

    ORDER BY 1;

2022-11-03 17:03:29.546 PKT [5221] LOG:  statement: SELECT d.datname as "Name",

         pg_catalog.pg_get_userbyid(d.datdba) as "Owner",

           pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",

           d.datcollate as "Collate",

           d.datctype as "Ctype",

           pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"

    FROM pg_catalog.pg_database d

    ORDER BY 1;

2022-11-03 17:03:36.142 PKT [5221] LOG:  statement: show log_statement;

2022-11-03 17:07:30.703 PKT [5221] ERROR:  syntax error at or near "clear" at character 1

2022-11-03 17:07:30.703 PKT [5221] STATEMENT:  clear

    clear

    select

    ;

予想される解決策：

2022-11-03 16:15:54.330 PKT [3421] LOG:  statement: select * from customersabc;
2022-11-03 16:15:54.330 PKT [3421] ERROR:  relation "customersabc" does not exist at character 15
2022-11-03 16:15:54.330 PKT [3421] STATEMENT:  select * from customersabc;
2022-11-03 16:58:26.796 PKT [5221] LOG:  statement: show log_statement;
2022-11-03 16:58:56.674 PKT [5221] LOG:  statement: show log_statement;
2022-11-03 17:01:47.331 PKT [5221] LOG:  statement: SELECT d.datname as "Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", d.datcollate as "Collate",d.datctype as "Ctype",pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges" FROM pg_catalog.pg_database d ORDER BY 1;
2022-11-03 17:03:08.477 PKT [5221] LOG:  statement: SELECT d.datname as "Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", d.datcollate as "Collate",d.datctype as "Ctype",pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"FROM pg_catalog.pg_database d ORDER BY 1;
2022-11-03 17:03:29.546 PKT [5221] LOG:  statement: SELECT d.datname as "Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", d.datcollate as "Collate",d.datctype as "Ctype",pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"FROM pg_catalog.pg_database d ORDER BY 1;
2022-11-03 17:03:36.142 PKT [5221] LOG:  statement: show log_statement;
2022-11-03 17:07:30.703 PKT [5221] ERROR:  syntax error at or near "clear" at character 1
2022-11-03 17:07:30.703 PKT [5221] STATEMENT:  clear clear select;

私も次のことを試しましたワイヤー パスワード：

sed -e '$!N;/^\t.*\n/D' -e 'P;D' db_logs.txt > output.txt

それは私に次のような結果を与えます：

22-11-03 16:15:54.330 PKT [3421] LOG:  statement: select * from customersabc;

2022-11-03 16:15:54.330 PKT [3421] ERROR:  relation "customersabc" does not exist at character 15

2022-11-03 16:15:54.330 PKT [3421] STATEMENT:  select * from customersabc;

2022-11-03 16:58:26.796 PKT [5221] LOG:  statement: show log_statement;

2022-11-03 16:58:56.674 PKT [5221] LOG:  statement: show log_statement

2022-11-03 17:01:47.331 PKT [5221] LOG:  statement: SELECT d.datname as "Name",

2022-11-03 17:03:08.477 PKT [5221] LOG:  statement: SELECT d.datname as "Name",

2022-11-03 17:03:29.546 PKT [5221] LOG:  statement: SELECT d.datname as "Name",

2022-11-03 17:03:36.142 PKT [5221] LOG:  statement: show log_statement;

2022-11-03 17:07:30.703 PKT [5221] ERROR:  syntax error at or near "clear" at character 1

2022-11-03 17:07:30.703 PKT [5221] STATEMENT:  clear

2022-11-03 17:08:01.281 PKT [5221] LOG:  statement: select

2022-11-03 17:11:31.760 PKT [5221] LOG:  statement: show log_statement;

2022-11-03 17:11:52.884 PKT [5221] LOG:  statement: select

残念ながら、残りの行は削除されます。私は純粋なLinuxツールに基づいたソリューションが欲しいです。外部ツールが利用できず、およびawkを使用しましたsed。

答え1

POSIX awkを使用してください。

$ awk -v ORS= '/^[^[:space:]]/{print sep; sep=RS} {sub(/^[[:space:]]+|[[:space:]]+$/," "); print} END{print sep}' file
2022-11-03 16:15:54.330 PKT [3421] LOG:  statement: select * from customersabc;
2022-11-03 16:15:54.330 PKT [3421] ERROR:  relation "customersabc" does not exist at character 15
2022-11-03 16:15:54.330 PKT [3421] STATEMENT:  select * from customersabc;
2022-11-03 16:58:26.796 PKT [5221] LOG:  statement: show log_statement;
2022-11-03 16:58:56.674 PKT [5221] LOG:  statement: show log_statement ;
2022-11-03 17:01:47.331 PKT [5221] LOG:  statement: SELECT d.datname as "Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", d.datcollate as "Collate", d.datctype as "Ctype", pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges" FROM pg_catalog.pg_database d ORDER BY 1;
2022-11-03 17:03:08.477 PKT [5221] LOG:  statement: SELECT d.datname as "Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", d.datcollate as "Collate", d.datctype as "Ctype", pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges" FROM pg_catalog.pg_database d ORDER BY 1;
2022-11-03 17:03:29.546 PKT [5221] LOG:  statement: SELECT d.datname as "Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", d.datcollate as "Collate", d.datctype as "Ctype", pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges" FROM pg_catalog.pg_database d ORDER BY 1;
2022-11-03 17:03:36.142 PKT [5221] LOG:  statement: show log_statement;
2022-11-03 17:07:30.703 PKT [5221] ERROR:  syntax error at or near "clear" at character 1
2022-11-03 17:07:30.703 PKT [5221] STATEMENT:  clear clear select ;

または、何らかの理由でデータ行間に空行が必要な場合：

$ awk -v ORS= '/^[^[:space:]]/{print sep; sep=RS RS} {sub(/^[[:space:]]+|[[:space:]]+$/," "); print} END{print sep}' file
2022-11-03 16:15:54.330 PKT [3421] LOG:  statement: select * from customersabc;

2022-11-03 16:15:54.330 PKT [3421] ERROR:  relation "customersabc" does not exist at character 15

2022-11-03 16:15:54.330 PKT [3421] STATEMENT:  select * from customersabc;

2022-11-03 16:58:26.796 PKT [5221] LOG:  statement: show log_statement;

2022-11-03 16:58:56.674 PKT [5221] LOG:  statement: show log_statement ;

2022-11-03 17:01:47.331 PKT [5221] LOG:  statement: SELECT d.datname as "Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", d.datcollate as "Collate", d.datctype as "Ctype", pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges" FROM pg_catalog.pg_database d ORDER BY 1;

2022-11-03 17:03:08.477 PKT [5221] LOG:  statement: SELECT d.datname as "Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", d.datcollate as "Collate", d.datctype as "Ctype", pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges" FROM pg_catalog.pg_database d ORDER BY 1;

2022-11-03 17:03:29.546 PKT [5221] LOG:  statement: SELECT d.datname as "Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", d.datcollate as "Collate", d.datctype as "Ctype", pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges" FROM pg_catalog.pg_database d ORDER BY 1;

2022-11-03 17:03:36.142 PKT [5221] LOG:  statement: show log_statement;

2022-11-03 17:07:30.703 PKT [5221] ERROR:  syntax error at or near "clear" at character 1

2022-11-03 17:07:30.703 PKT [5221] STATEMENT:  clear clear select ;

答え2

次のスクリプトを使用して問題を解決できました。

awk -v ORS="" '                 
    /^\r?$/ {next}              
    f && !/^\t/ {print "\n"}     
    {
        sub(/\r$/, " ")          
        print                   
        if ($0 ~ /\r/) f = 0    
        else f = 1             
    }
    END {
        print "\n"              
    }
' db_logs.txt > output.txt 
awk 'BEGIN{FS="\t+"; OFS="\t"} {$1=$1; print}' output.txt > out.txt
awk '{ gsub(/ /," "); print }' out.txt > oyt.txt 
awk '{ gsub(/^[ \t]+|[ \t]+$/, ""); print }' oyt.txt > new.txt

関連情報