Scripts for loading Github events into Citus

0_configuration.md

Server

I2 Eight Extra Large i2.8xlarge 244.0 GB 104 cpu-units 32 cores 6400 GB (8 * 800 GB SSD) 64-bit 10 Gigabit

Disk config

zpool create -f -o ashift=12 -O compression=lz4 -O exec=off -O recordsize=1024k wal xvdf
zpool create -f -o ashift=12 -O compression=lz4 -O exec=off -O recordsize=8k -O logbias=throughput dat xvdg xvdh xvdi xvdj xvdk xvdl xvdm

postgresql.conf

# Generated from http://pgtune.leopard.in.ua/, "data warehouse"
# profile.
max_connections = 100
shared_buffers = 61GB
effective_cache_size = 183GB
work_mem = 319815kB
maintenance_work_mem = 2GB
max_wal_size = 4GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 500

data size

[root@ip-172-31-1-16 ec2-user]# df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/xvda1      7.8G  2.2G  5.5G  29% /
devtmpfs        121G  148K  121G   1% /dev
tmpfs           121G  4.0K  121G   1% /dev/shm
wal             721G  2.0M  721G   1% /wal
dat             5.0T  617G  4.4T  13% /dat

1_query_results.sql

postgres=# SELECT created_at::date AS date, to_char(created_at::date, 'Day') as day,
postgres-#        repeat('#', (count(*)/10000)::int) AS num_push_events
postgres-# FROM github_events
postgres-# WHERE event_type = 'PushEvent' AND created_at >= date '2016-02-07'
postgres-# GROUP by date
postgres-# ORDER By date; --daily pushes
    date    |    day    |                                            num_push_events
------------+-----------+--------------------------------------------------------------------------------------------------------
 2016-02-07 | Sunday    | ################################################################
 2016-02-08 | Monday    | #################################################################################
 2016-02-09 | Tuesday   | ######################################################################################
 2016-02-10 | Wednesday | #######################################################################################
 2016-02-11 | Thursday  | ######################################################################################
 2016-02-12 | Friday    | #################################################################################
 2016-02-13 | Saturday  | ############################################################
 2016-02-14 | Sunday    | ###############################################################
 2016-02-15 | Monday    | #######################################################################################
 2016-02-16 | Tuesday   | ##############################################################################################
 2016-02-17 | Wednesday | ################################################################################################
 2016-02-18 | Thursday  | ##############################################################################################
 2016-02-19 | Friday    | ###########################################################################################
 2016-02-20 | Saturday  | ###################################################################
 2016-02-21 | Sunday    | ########################################################################
 2016-02-22 | Monday    | #################################################################################################
 2016-02-23 | Tuesday   | ##################################################################################################
 2016-02-24 | Wednesday | ##################################################################################################
 2016-02-25 | Thursday  | ##################################################################################################
 2016-02-26 | Friday    | #############################################################################################
 2016-02-27 | Saturday  | ##################################################################
 2016-02-28 | Sunday    | #######################################################################
 2016-02-29 | Monday    | #############################################################################################
 2016-03-01 | Tuesday   | ##############################################################################################
 2016-03-02 | Wednesday | ###################################################################################################
 2016-03-03 | Thursday  | ######################################################################################################
 2016-03-04 | Friday    | ################################################################################################
 2016-03-05 | Saturday  | #######################################################################
 2016-03-06 | Sunday    | #########################################################################
 2016-03-07 | Monday    | ##################################################################################################
(30 rows)

Time: 1360885.229 ms

postgres=# SELECT created_at::date AS date, to_char(created_at::date, 'Day') as day,
       repeat('#', (count(*)/10000)::int) AS num_push_events
FROM github_events
WHERE event_type = 'PushEvent' AND created_at >= date '2016-02-07'
GROUP by date
ORDER By date; --daily pushes
    date    |    day    |                                            num_push_events
------------+-----------+--------------------------------------------------------------------------------------------------------
 2016-02-07 | Sunday    | ################################################################
 2016-02-08 | Monday    | #################################################################################
 2016-02-09 | Tuesday   | ######################################################################################
 2016-02-10 | Wednesday | #######################################################################################
 2016-02-11 | Thursday  | ######################################################################################
 2016-02-12 | Friday    | #################################################################################
 2016-02-13 | Saturday  | ############################################################
 2016-02-14 | Sunday    | ###############################################################
 2016-02-15 | Monday    | #######################################################################################
 2016-02-16 | Tuesday   | ##############################################################################################
 2016-02-17 | Wednesday | ################################################################################################
 2016-02-18 | Thursday  | ##############################################################################################
 2016-02-19 | Friday    | ###########################################################################################
 2016-02-20 | Saturday  | ###################################################################
 2016-02-21 | Sunday    | ########################################################################
 2016-02-22 | Monday    | #################################################################################################
 2016-02-23 | Tuesday   | ##################################################################################################
 2016-02-24 | Wednesday | ##################################################################################################
 2016-02-25 | Thursday  | ##################################################################################################
 2016-02-26 | Friday    | #############################################################################################
 2016-02-27 | Saturday  | ##################################################################
 2016-02-28 | Sunday    | #######################################################################
 2016-02-29 | Monday    | #############################################################################################
 2016-03-01 | Tuesday   | ##############################################################################################
 2016-03-02 | Wednesday | ###################################################################################################
 2016-03-03 | Thursday  | ######################################################################################################
 2016-03-04 | Friday    | ################################################################################################
 2016-03-05 | Saturday  | #######################################################################
 2016-03-06 | Sunday    | #########################################################################
 2016-03-07 | Monday    | ##################################################################################################
(30 rows)

Time: 1329983.695 ms -- ~22 minutes
-- vs 1156.506 on citus

2_query_results.sql

SELECT created_at::date AS date, to_char(created_at::date, 'Day') as day,
       repeat('#', (count(*)/10000)::int) AS num_push_events
FROM github_events
WHERE event_type = 'PushEvent' AND created_at >= date '2016-01-01'
GROUP by date
ORDER By date; --ytd pushes
-- results
Time: 1385384.223 ms -- ~32 minutes
-- vs 1291.151ms on citus

3_query_results.sql

postgres=# SELECT count(*)
FROM github_events
WHERE event_type = 'PushEvent' AND created_at >= date '2016-01-01'
;

  count
----------
 54012489
(1 row)

Time: 1373687.583 ms -- ~22.8 minutes
-- vs 1291.151ms on citus

4_query_results.sql

SELECT date_trunc('month', created_at) as month,
hll_cardinality(hll_add_agg(hll_hash_bigint(repo_id))) as num_repos
-- originally count(distinct repo_id)
FROM github_events
WHERE event_type = 'PushEvent' and created_at >= date '2016-01-01'
GROUP BY month
ORDER BY month;
        month        |    num_repos
---------------------+------------------
 2016-01-01 00:00:00 | 1389612.73977303
 2016-02-01 00:00:00 | 1439173.76302682
 2016-03-01 00:00:00 |  543142.86674282
(3 rows)

Time: 1928009.760 ms -- ~32min
--vs 1684.341 on citus

5_query_results.sql

postgres=#
postgres=# SELECT jsonb_array_elements(payload->'commits')->'author'->>'name' as name,
postgres-#   count(*)
postgres-# FROM github_events
postgres-# WHERE repo @> '{"name":"postgres/postgres"}'
postgres-# GROUP BY 1 order by 2 desc; -- contributors
        name        | count
--------------------+-------
 Tom Lane           |  3580
 Robert Haas        |   828
 Alvaro Herrera     |   694
 Andres Freund      |   648
 Heikki Linnakangas |   626
 Noah Misch         |   539
 Peter Eisentraut   |   470
 Bruce Momjian      |   463
 Stephen Frost      |   273
 Andrew Dunstan     |   249
 Fujii Masao        |   237
 Michael Meskes     |   134
 Magnus Hagander    |   122
 Joe Conway         |    96
 Simon Riggs        |    90
 Teodor Sigaev      |    84
 Kevin Grittner     |    76
 Tatsuo Ishii       |    62
 Greg Stark         |    28
 Dean Rasheed       |    12
 Jeff Davis         |     8
(21 rows)

Time: 4119.225 ms
-- vs 332.715ms on citus

6_query_results.sql

postgres=# SELECT date_trunc('week', (created_at + '1 day'::interval)) - '1 day'::interval as week,
  sum((payload->>'distinct_size')::int) as num_commits
FROM github_events
WHERE event_type = 'PushEvent' AND
      created_at >= date '2015-03-08' AND
      repo @> '{"name":"postgres/postgres"}' AND
      payload @> '{"ref":"refs/heads/master"}'
GROUP BY week
ORDER BY week; --final commit numbers
        week         | num_commits
---------------------+-------------
 2015-03-08 00:00:00 |          90
 2015-03-15 00:00:00 |          70
 2015-03-22 00:00:00 |          87
 2015-03-29 00:00:00 |          92
 2015-04-05 00:00:00 |          59
 2015-04-12 00:00:00 |          60
 2015-04-19 00:00:00 |          61
 2015-04-26 00:00:00 |          98
 2015-05-03 00:00:00 |          85
 2015-05-10 00:00:00 |         168
 2015-05-17 00:00:00 |          95
 2015-05-24 00:00:00 |          72
 2015-05-31 00:00:00 |          44
 2015-06-07 00:00:00 |          78
 2015-06-14 00:00:00 |          42
 2015-06-21 00:00:00 |          60
 2015-06-28 00:00:00 |         100
 2015-07-05 00:00:00 |          74
 2015-07-12 00:00:00 |          56
 2015-07-19 00:00:00 |          50
 2015-07-26 00:00:00 |         125
 2015-08-02 00:00:00 |         109
 2015-08-09 00:00:00 |          92
 2015-08-16 00:00:00 |          38
 2015-08-23 00:00:00 |          38
 2015-08-30 00:00:00 |          52
 2015-09-06 00:00:00 |          68
 2015-09-13 00:00:00 |          64
 2015-09-20 00:00:00 |          56
 2015-09-27 00:00:00 |          95
 2015-10-04 00:00:00 |          88
 2015-10-11 00:00:00 |          50
 2015-10-18 00:00:00 |          54
 2015-10-25 00:00:00 |          31
 2015-11-01 00:00:00 |          55
 2015-11-08 00:00:00 |          51
 2015-11-15 00:00:00 |          50
 2015-11-22 00:00:00 |          30
 2015-11-29 00:00:00 |          40
 2015-12-06 00:00:00 |          56
 2015-12-13 00:00:00 |          73
 2015-12-20 00:00:00 |          44
 2015-12-27 00:00:00 |          55
 2016-01-03 00:00:00 |          91
 2016-01-10 00:00:00 |          33
 2016-01-17 00:00:00 |          62
 2016-01-24 00:00:00 |          51
 2016-01-31 00:00:00 |          89
 2016-02-07 00:00:00 |         108
 2016-02-14 00:00:00 |          58
Time: 60.536 ms
-- vs 304.354ms on citus

z_create_table.sql

CREATE TABLE github_events
(
    event_id bigint,
    event_type text,
    event_public boolean,
    repo_id bigint,
    payload jsonb,
    repo jsonb,
    actor jsonb,
    org jsonb,
    created_at timestamp
);

--SELECT master_create_distributed_table('github_events', 'created_at', 'append');

CREATE INDEX ON github_events (event_type);
CREATE INDEX ON github_events USING GIN (actor jsonb_path_ops);
CREATE INDEX ON github_events USING GIN (repo jsonb_path_ops);

z_load.rb

require "sequel"
DB = Sequel.connect("postgres:///", max_connections: 60)

ts = []
[2015,2016].each do |year|
  (1..12).each do |month|
    ts << Thread.new do
      (1..31).each do |day|
        (0..23).each do |hour|
          next if year==2016 && month >= 3 && day > 7
          begin
            puts "select load_github_events('#{year}-#{month}-#{day}', #{hour}, #{hour+1})"
            DB["select load_github_events('#{year}-#{month}-#{day}', #{hour}, #{hour+1});"].all
          rescue => e
            p e
          end
       end
     end
    end
  end
end
ts.map(&:join)

z_load_github2.sql

CREATE OR REPLACE FUNCTION load_github_events(events_date date, start_hour int, end_hour int) RETURNS void AS
$BODY$
BEGIN
    CREATE TEMPORARY TABLE input (data jsonb);

    /* Download, decompress, and filter JSON data */
    EXECUTE format('COPY input FROM PROGRAM ''curl -s http://data.githubarchive.org/%s-{%s..%s}.json.gz | zcat | grep -v "\\u0000"'''||
                   'CSV QUOTE e''\x01'' DELIMITER e''\x02''', events_date, start_hour, end_hour);

    /* Convert raw JSON to table format */
    insert into github_events (
                   SELECT (data->>'id')::bigint AS event_id,
                   (data->>'type')::text AS event_type,
                   (data->>'public')::boolean AS event_public,
                   (data->'repo'->>'id')::bigint AS repo_id,
                   data->'payload' AS payload,
                   data->'repo' AS repo,
                   data->'actor' AS actor,
                   data->'org' AS org,
                   (data->>'created_at')::timestamp AS created_at
                   from input
                );

    drop table input;
END;
$BODY$
LANGUAGE plpgsql;

z_load_github_events.sql

CREATE SEQUENCE IF NOT EXISTS stage_id;

CREATE OR REPLACE FUNCTION load_github_events(events_date date, start_hour int, end_hour int) RETURNS text AS
$BODY$
DECLARE
    stage_table text := 'stage_'||nextval('stage_id');
BEGIN
    CREATE TEMPORARY TABLE input (data jsonb);

    /* Download, decompress, and filter JSON data */
    EXECUTE format('COPY input FROM PROGRAM ''curl -s http://data.githubarchive.org/%s-{%s..%s}.json.gz | zcat | grep -v "\\u0000"'''||
                   'CSV QUOTE e''\x01'' DELIMITER e''\x02''', events_date, start_hour, end_hour);

    /* Convert raw JSON to table format */
    EXECUTE format('CREATE TABLE %I AS '||
                   'SELECT (data->>''id'')::bigint AS event_id, '||
                   '(data->>''type'')::text AS event_type, '||
                   '(data->>''public'')::boolean AS event_public, '||
                   '(data->''repo''->>''id'')::bigint AS repo_id, '||
                   'data->''payload'' AS payload, '||
                   'data->''repo'' AS repo, '||
                   'data->''actor'' AS actor, '||
                   'data->''org'' AS org, '||
                   '(data->>''created_at'')::timestamp AS created_at '||
                   'FROM input', stage_table);

    RETURN stage_table;
END;
$BODY$
LANGUAGE plpgsql;