痛点直击

作为程序员,工作中碰到最多的就是日志,日志格式多数为csv。而最常见的需求就是将日志聚合,筛选得出想要的结果。
当然awk是最强大的工具,比如zcat *.gz | awk -F '1' '{h=strftime("%Y-%m-%d %H",$26);if($1=="unity"){a[h]++}}END{for( b in a) print b":"a[b]}'

寻求其他方案

sql是我能想到最好的工具。方案来源是pgsql的外部表,是不是存在一种很合适的建立在csv文件格式上的数据库。

存储过程

1,pgsql docker版
2,运行环境mac pro 2015
3,开发工具DataGrip
4,运行方法:select load_csv_file('target','csv文件',列数);(代码见附录)
5,整个存储过程的步骤:

  1. 创建临时表,表的字段数目由最后一个参数决定
  2. copy csv到临时表
  3. 将csv第一列取出,并分别作为各列的名称
  4. 重命名表

进一步

模拟一下使用流程:将下载文件移动到docker挂在目录,到sql中执行load csv file 存储过程。
进一步简化:利用mac 系统的自动操作,新建服务,并将服务附加到右键菜单中。那么可以再下载的csv文件上右键选择你的服务,一键导入数据库。
进一步扩展:可以做一个简单的上传文件的网页,将接收到的文件通过以上流程直接导入数据库,然后提供sql界面供查询。
load-csv-to-postgres.png

附录

--load csv file
CREATE OR REPLACE FUNCTION load_csv_file(
    target_table text,
    csv_path text,
    col_count integer)
    RETURNS void AS
$BODY$

declare

    iter integer; -- dummy integer to iterate columns with
    col text; -- variable to keep the column name at each iteration
    col_first text; -- first column name, e.g., top left corner on a csv file or spreadsheet

begin
    set schema 'public';

    create table temp_table ();

    -- add just enough number of columns
    for iter in 1..col_count
        loop
            execute format('alter table temp_table add column col_%s text;', iter);
        end loop;
    -- copy the data from csv file
    execute format('copy temp_table from %L with delimiter '','' csv ', csv_path);

    iter := 1;
    col_first := (select col_1 from temp_table limit 1);

    -- update the column names based on the first row which has the column names
    for col in execute format('select unnest(string_to_array(trim(temp_table::text, ''()''), '','')) from temp_table where col_1 = %L', col_first)
        loop
            raise notice 'alter table temp_table rename column col_% to ''%''',iter,col;
            execute format('alter table temp_table rename column col_%s to "%s"', iter, col);
            iter := iter + 1;
        end loop;

    -- delete the columns row
    raise notice 'delete from temp_table where "%s" = "%L"', col_first, col_first;
    execute format('delete from temp_table where "%s" = ''%s''', col_first, col_first);
    execute 'alter table "temp_table" owner to postgres';
    -- change the temp table name to the name given as parameter, if not blank
    if length(target_table) > 0 then
        execute format('alter table temp_table rename to %I', target_table);
    end if;

end

$BODY$
    LANGUAGE plpgsql VOLATILE
                     COST 100;
ALTER FUNCTION load_csv_file(text, text, integer)
  OWNER TO postgres;

https://stackoverflow.com/questions/2987433/how-to-import-csv-file-data-into-a-postgresql-table
https://sqlify.io/convert/csv/to/postgres 比较好的一种实现

文章目录