{ "cells": [ { "cell_type": "code", "execution_count": 8, "metadata": { "collapsed": true }, "outputs": [], "source": [ "import re" ] }, { "cell_type": "code", "execution_count": 9, "metadata": { "collapsed": true }, "outputs": [], "source": [ "from dateutil import parser" ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "collapsed": true }, "outputs": [], "source": [ "lines = open('./dbloglines1.txt').read()" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "['2018-12-05 17:22:56 UTC:ec2-52-215-15-208.eu-west-1.compute.amazonaws.com(43913):bot@wl_us_ultra:[31705]:LOG: duration: 40618.950 ms statement: ',\n", " '\\t',\n", " '\\t',\n", " '\\tselect',\n", " '\\t distinct',\n", " '\\t t.spyid,',\n", " '\\t t.primary_artist_spyid,',\n", " \"\\t t.album_data->>'release_date',\",\n", " \"\\t coalesce(t.data->>'popularity', '0')::int\",\n", " '\\t from spy_tracks t',\n", " '\\t join spy_playlist_track spt on spt.track_spyid = t.spyid',\n", " '--',\n", " '2018-12-05 17:22:57 UTC:ec2-52-215-15-208.eu-west-1.compute.amazonaws.com(43913):bot@wl_us_ultra:[31705]:LOG: duration: 1218.363 ms statement: ',\n", " '\\twith artist as (',\n", " '\\t select',\n", " '\\t groupkey as id,',\n", " '\\t arid,',\n", " '\\t scid,',\n", " '\\t eid,',\n", " '\\t inid,',\n", " '\\t spyid,',\n", " \"\\t to_jsonb(p.*) || jsonb_build_object('query_key', p.groupkey) as artist\",\n", " \"\\t from setf_artist_profiles(('spy')::text, variadic (ARRAY['053q0ukIDRgzwTr4vNSwab', '3ytV7vc4ZuwGgwaOuWvkk8', '1afjj7vSBkpIjkiJdSV6bV', '6aAQxKW3u9K01sGfdMm5DD', '6L34dW6SKMSDaGIfYDU19j', '6VoRokuki5Bj7BEB2gZ3BF', '6L34dW6SKMSDaGIfYDU19j', '4VuARVJiWGsH2msKTdsKdQ', '6L34dW6SKMSDaGIfYDU19j', '0wwDZj4zgZORjTGhTujAKx', '2KbikmQKGiUPR2ARs1h0sq', '2kS0jWMkkFBL0mrl0VotD0', '6L34dW6SKMSDaGIfYDU19j', '6L34dW6SKMSDaGIfYDU19j', '6L34dW6SKMSDaGIfYDU19j', '6L34dW6SKMSDaGIfYDU19j', '2Z9KL8Zmqx5Sg3cd7Fldhl', '6L34dW6SKMSDaGIfYDU19j', '6L34dW6SKMSDaGIfYDU19j', '7AzjETXRUKNRSJHMW9GIqd'])::text[]) p\",\n", " '--',\n", " '2018-12-05 17:23:00 UTC:ec2-52-215-15-208.eu-west-1.compute.amazonaws.com(43913):bot@wl_us_ultra:[31705]:LOG: duration: 2775.806 ms statement: ',\n", " '\\twith',\n", " '\\tartistids as (',\n", " '\\t select array_agg(distinct primary_artist_spyid) ids',\n", " '\\t from spy_tracks',\n", " \"\\t where spyid = any(ARRAY['08ZCgkLKpcgrcSPCHN2v8J', '1bZNFcnyCmlnbs337baR0b', '1VYTP4prOc62xQe1KWf3S5', '0VWTSWZVask2dSMShSphvM', '1JmVHDcU1ipRKFj4HeHyPn', '4ulEYBkdzlWdoMH2uNkP1I', '3XbxOKMhF5DnOYue7JMsYm', '5R1Tv8SO02AEUhpNCSK3TO', '1NfmjmrsVCsNRqBOgbwlKd', '4G0Ntye3QubS2Ys8GMg8vy', '10CdS6lWzNxOaIX1PCuFW5', '15DPWEQ73JEW3pfyU6EcmR', '16D4613VA9RHe72Jq2VnOs', '2UkLrrYuDlnVTWPOqVt5uI', '5J3XCWnWJYLnpH5gQNhysf', '5qNoStht8VZlb0cg7ttmQB', '7sdAhKvVLk5S4HTCgi7S3A', '7tx5GjRyu3AnnjSuhzMlXl', '0Qjis1ImpONgbuDxAIzOlX', '30QNbMNYxPL78w2jLZdRdv'])\",\n", " '\\t),',\n", " '\\t',\n", " '\\ttrack_data as (',\n", " '\\t SELECT',\n", " '\\t t.spyid,',\n", " '--',\n", " '2018-12-05 17:24:26 UTC:ec2-52-215-15-208.eu-west-1.compute.amazonaws.com(43916):bot@wl_us_ultra:[321]:LOG: duration: 11103.018 ms statement: ',\n", " '\\t',\n", " '\\t',\n", " '\\tselect',\n", " '\\t distinct',\n", " '\\t t.spyid,',\n", " '\\t t.primary_artist_spyid,',\n", " \"\\t t.album_data->>'release_date',\",\n", " \"\\t coalesce(t.data->>'popularity', '0')::int\",\n", " '\\t from spy_tracks t',\n", " '\\t join spy_playlist_track spt on spt.track_spyid = t.spyid',\n", " '--',\n", " '2018-12-05 17:24:28 UTC:ec2-52-215-15-208.eu-west-1.compute.amazonaws.com(43917):bot@wl_us_ultra:[401]:LOG: duration: 1182.255 ms statement: ',\n", " '\\twith artist as (',\n", " '\\t select',\n", " '\\t groupkey as id,',\n", " '\\t arid,',\n", " '\\t scid,',\n", " '\\t eid,',\n", " '\\t inid,',\n", " '\\t spyid,',\n", " \"\\t to_jsonb(p.*) || jsonb_build_object('query_key', p.groupkey) as artist\",\n", " \"\\t from setf_artist_profiles(('a')::text, variadic (ARRAY[34375, 60, 176, 17, 885, 805, 905, 965, 624, 1008, 6266, 13782, 18398, 40182, 34811, 2348, 41908, 46165, 15892, 61638, 73295])::text[]) p\",\n", " '--',\n", " '2018-12-05 17:24:28 UTC:ec2-52-215-15-208.eu-west-1.compute.amazonaws.com(43916):bot@wl_us_ultra:[321]:LOG: duration: 2152.665 ms statement: ',\n", " '\\twith artist as (',\n", " '\\t select',\n", " '\\t groupkey as id,',\n", " '\\t arid,',\n", " '\\t scid,',\n", " '\\t eid,',\n", " '\\t inid,',\n", " '\\t spyid,',\n", " \"\\t to_jsonb(p.*) || jsonb_build_object('query_key', p.groupkey) as artist\",\n", " \"\\t from setf_artist_profiles(('spy')::text, variadic (ARRAY['6L34dW6SKMSDaGIfYDU19j', '6L34dW6SKMSDaGIfYDU19j', '21mKp7DqtSNHhCAU2ugvUw', '0906u9DO6IhSeFljQ7AXHZ', '3w6zswp5THsSKYLICUbDTZ', '1lDaPfGRA6miVIOjZbY50n', '3dH7pcBScIJQboDyMzUzez', '6L34dW6SKMSDaGIfYDU19j', '3MNnSV5hDd2UzZzgqD8xlU', '6L34dW6SKMSDaGIfYDU19j', '0LX2VNf5w4iOHW1yyIqb74', '2KjAo6wVc9d2WcxdxSArpV', '5DD5GZd4ElmQTy9NleMvKJ', '4yA7QuT08bPTnwJXKQSubo', '6qgnBH6iDM91ipVXv28OMu', '462yq5vpZnO172v3nK9ibv', '5oDtp2FC8VqBjTx1aT4P5j', '0BKx4UjPvXVGY15u0xFbbi', '6qgnBH6iDM91ipVXv28OMu', '4VuARVJiWGsH2msKTdsKdQ'])::text[]) p\",\n", " '--',\n", " '2018-12-05 17:24:32 UTC:ec2-52-215-15-208.eu-west-1.compute.amazonaws.com(43916):bot@wl_us_ultra:[321]:LOG: duration: 3648.820 ms statement: ',\n", " '\\twith',\n", " '\\tartistids as (',\n", " '\\t select array_agg(distinct primary_artist_spyid) ids',\n", " '\\t from spy_tracks',\n", " \"\\t where spyid = any(ARRAY['4miiqu2dxOhBguQDHtqssH', '5rOxVHDRjkdhn6aKhpmKvg', '7BdBAeVMiVHbszGoN7WsAt', '0JrICQtDkevyFIvzvqqXyV', '0UlguQTHxZSh4NKDMpQATl', '2ng98WyIOWGTCm71TXB4gi', '3ABwQntK8i49EGOd3SSj7Q', '3uTNli5uz5rNOqD6qZFi8L', '42CfzZxF5yJqbUS2FuKlrc', '6NNtJ7QHF0PzhNK736iwyF', '4glBRF3uJCf3bTG4hhIhpn', '08GxTIp0Ohhl3OJqX3SlYT', '12d0oildmCSVKRW7SEHaZP', '148PHifIfj7HESkf4HmYJG', '439X8jGytErRiPnaoUJHju', '4nZ5SGV3u5fc7gc4Q1tcDX', '5LvVslXgRVlR0IE7mbfbsz', '5WRLwhSkG5GCBteS77OXYd', '08M1S4uwtmPM0jIO1qNyhx', '1SDWa2NAPvVjLhIXDM9Zgi'])\",\n", " '\\t),',\n", " '\\t',\n", " '\\ttrack_data as (',\n", " '\\t SELECT',\n", " '\\t t.spyid,',\n", " '--',\n", " '2018-12-05 17:25:02 UTC:ec2-52-215-15-208.eu-west-1.compute.amazonaws.com(43918):bot@wl_us_ultra:[651]:LOG: duration: 11092.290 ms statement: ',\n", " '\\t',\n", " '\\t',\n", " '\\tselect',\n", " '\\t distinct',\n", " '\\t t.spyid,',\n", " '\\t t.primary_artist_spyid,',\n", " \"\\t t.album_data->>'release_date',\",\n", " \"\\t coalesce(t.data->>'popularity', '0')::int\",\n", " '\\t from spy_tracks t',\n", " '\\t join spy_playlist_track spt on spt.track_spyid = t.spyid',\n", " '--',\n", " '2018-12-05 17:25:04 UTC:ec2-52-215-15-208.eu-west-1.compute.amazonaws.com(43918):bot@wl_us_ultra:[651]:LOG: duration: 1751.579 ms statement: ',\n", " '\\twith artist as (',\n", " '\\t select',\n", " '\\t groupkey as id,',\n", " '\\t arid,',\n", " '\\t scid,',\n", " '\\t eid,',\n", " '\\t inid,',\n", " '\\t spyid,',\n", " \"\\t to_jsonb(p.*) || jsonb_build_object('query_key', p.groupkey) as artist\",\n", " \"\\t from setf_artist_profiles(('spy')::text, variadic (ARRAY['1aWJsBQa67l72j1VT3D6Ow', '4cUfFbVZGSsJWdbfmkdxYq', '7C3Cbtr2PkH2l4tOGhtCsk', '4eYnorQRhVHT2KBl2UyHHd', '2wJ4vsxWd7df7dRU4KcoDe', '3dH7pcBScIJQboDyMzUzez', '7C3Cbtr2PkH2l4tOGhtCsk', '7KFOc3T4Xo8DVZt4PWw2qN', '3dH7pcBScIJQboDyMzUzez', '4PCeJ2EIn3YtzYYRsHHWyy', '4VuARVJiWGsH2msKTdsKdQ', '4xnMDfgEmXZEEDdITKcGuE', '7iBY1RLWDV5zX9NDNQxurm', '4VuARVJiWGsH2msKTdsKdQ', '0LX2VNf5w4iOHW1yyIqb74', '6LEeAFiJF8OuPx747e1wxR', '31Ua7zSTJxegjyd49ujbSA', '5cqU2KGOvMlVg3K6J1SkNs', '77IKXFvO7SpWrq8hflrUXc', '0fiWOxhsBsQQvFDtxUQWo0'])::text[]) p\",\n", " '--',\n", " '2018-12-05 17:25:08 UTC:ec2-52-215-15-208.eu-west-1.compute.amazonaws.com(43918):bot@wl_us_ultra:[651]:LOG: duration: 3742.929 ms statement: ',\n", " '\\twith',\n", " '\\tartistids as (',\n", " '\\t select array_agg(distinct primary_artist_spyid) ids',\n", " '\\t from spy_tracks',\n", " \"\\t where spyid = any(ARRAY['21qvRFmtuNS5D2EWQsSOZL', '48N3ixP9ZuaBPnETKIayI0', '48o9xqLcWr5sPqNAJF9ft1', '5Op8IapCIAejzCsFB5Aat4', '64BirkPW9F4XgzoLAiOBCP', '6gKW1mSSWSRPC37mjlMmQG', '1A4tabNlyLKLOHQW2XpjaU', '3Cbaa4ZmODhKEGSkMsbkOO', '71chDzMntV8pjuOaLuDPaY', '2wIuiNxHAuGWBuPuGm0ZXo', '3Rq0ElVcisouAl71RtnTUS', '4IeRBzdV0W6p74CMZjOpEE', '5fpTIIkao9OkzE0EYLqBqQ', '5PdzMDbwPlqzXuauAarup3', '0xoUDiOLXqp9qB3S9FkvUI', '22Fvc0gkf7ZCNQEQ2Oxj6D', '2cytBOLpwFRX7J9URCrFIe', '4sds3550uCpVAE5JYZO8Us', '6BKu8kCqUoQy0BFgnOYMJr', '6k5B2DslCuvAtgeG09E85Q'])\",\n", " '\\t),',\n", " '\\t',\n", " '\\ttrack_data as (',\n", " '\\t SELECT',\n", " '\\t t.spyid,',\n", " '--',\n", " '2018-12-05 17:25:12 UTC:ec2-52-215-15-208.eu-west-1.compute.amazonaws.com(43919):bot@wl_us_ultra:[774]:LOG: duration: 5092.506 ms statement: ',\n", " '\\tselect',\n", " '\\t distinct',\n", " '\\t t.scid,',\n", " '\\t t.artist_scid,',\n", " \"\\t coalesce(t.data ->> 'playback_count', '0')::int\",\n", " '\\tfrom sc_tracks t',\n", " '\\tjoin sc_users u ON t.artist_scid = u.scid',\n", " '\\twhere u.active',\n", " '\\t and u.snooze_until <= CURRENT_DATE ',\n", " \"\\t and coalesce(u.data ->> 'followers_count', '0')::int < 1000000\",\n", " '--',\n", " '2018-12-05 17:25:13 UTC:ec2-52-215-15-208.eu-west-1.compute.amazonaws.com(43919):bot@wl_us_ultra:[774]:LOG: duration: 1068.942 ms statement: ',\n", " '\\twith artist as (',\n", " '\\t select',\n", " '\\t groupkey as id,',\n", " '\\t arid,',\n", " '\\t scid,',\n", " '\\t eid,',\n", " '\\t inid,',\n", " '\\t spyid,',\n", " \"\\t to_jsonb(p.*) || jsonb_build_object('query_key', p.groupkey) as artist\",\n", " \"\\t from setf_artist_profiles(('sc')::text, variadic (ARRAY[5958931, 5958931, 5958931, 5958931, 5958931, 5958931, 5958931, 5958931, 5958931, 5958931, 5958931, 5958931, 5958931, 5958931, 5958931, 5958931, 449909790, 112285333, 31524382, 509619213])::text[]) p\",\n", " '--',\n", " '2018-12-05 17:25:31 UTC:ec2-52-215-15-208.eu-west-1.compute.amazonaws.com(43920):bot@wl_us_ultra:[903]:LOG: duration: 1404.831 ms statement: ',\n", " '\\twith artist as (',\n", " '\\t select',\n", " '\\t groupkey as id,',\n", " '\\t arid,',\n", " '\\t scid,',\n", " '\\t eid,',\n", " '\\t inid,',\n", " '\\t spyid,',\n", " \"\\t to_jsonb(p.*) || jsonb_build_object('query_key', p.groupkey) as artist\",\n", " \"\\t from setf_artist_profiles(('sc')::text, variadic (ARRAY[24883142, 178682293, 246362065, 193051429, 335422929, 204698, 184440064, 29550459, 2982659, 1630175, 33472378, 540897018, 102435699, 266315025, 116806998, 29902258, 6052170, 65481352, 414495486, 32687879])::text[]) p\",\n", " '--',\n", " '2018-12-05 17:25:52 UTC:ec2-52-215-15-208.eu-west-1.compute.amazonaws.com(43921):bot@wl_us_ultra:[1165]:LOG: duration: 1631.395 ms statement: ',\n", " '\\twith artist as (',\n", " '\\t select',\n", " '\\t groupkey as id,',\n", " '\\t arid,',\n", " '\\t scid,',\n", " '\\t eid,',\n", " '\\t inid,',\n", " '\\t spyid,',\n", " \"\\t to_jsonb(p.*) || jsonb_build_object('query_key', p.groupkey) as artist\",\n", " \"\\t from setf_artist_profiles(('sc')::text, variadic (ARRAY[2170912, 284618037, 14625993, 3871229, 3178645, 357378557, 2704433, 180710414, 179256009, 35321221, 11997233, 125927687, 174712214, 56681963, 4615165, 7700015, 23767778, 122417495, 333251975, 818321])::text[]) p\",\n", " '--',\n", " '2018-12-05 17:26:11 UTC:ec2-52-215-15-208.eu-west-1.compute.amazonaws.com(43922):bot@wl_us_ultra:[1270]:LOG: duration: 1581.801 ms statement: ',\n", " '\\twith artist as (',\n", " '\\t select',\n", " '\\t groupkey as id,',\n", " '\\t arid,',\n", " '\\t scid,',\n", " '\\t eid,',\n", " '\\t inid,',\n", " '\\t spyid,',\n", " \"\\t to_jsonb(p.*) || jsonb_build_object('query_key', p.groupkey) as artist\",\n", " \"\\t from setf_artist_profiles(('sc')::text, variadic (ARRAY[80354251, 197117857, 9195820, 855080, 235638983, 48421338, 357378557, 20139082, 20960469, 329190, 399166893, 2804083, 4634, 201832012, 24731324, 13473387, 20405927, 143532116, 724640, 144098428])::text[]) p\",\n", " '--',\n", " '2018-12-05 17:26:13 UTC:ec2-52-215-15-208.eu-west-1.compute.amazonaws.com(43923):bot@wl_us_ultra:[1296]:LOG: duration: 1148.555 ms statement: ',\n", " '\\twith artist as (',\n", " '\\t select',\n", " '\\t groupkey as id,',\n", " '\\t arid,',\n", " '\\t scid,',\n", " '\\t eid,',\n", " '\\t inid,',\n", " '\\t spyid,',\n", " \"\\t to_jsonb(p.*) || jsonb_build_object('query_key', p.groupkey) as artist\",\n", " \"\\t from setf_artist_profiles(('sc')::text, variadic (ARRAY[5958931, 5958931, 5958931, 5958931, 5958931, 5958931, 5958931, 5958931, 5958931, 5958931, 5958931, 5958931, 5958931, 5958931, 5958931, 5958931, 24883142, 246362065, 184440064, 2982659])::text[]) p\",\n", " '--',\n", " '2018-12-05 17:26:16 UTC:ec2-52-215-15-208.eu-west-1.compute.amazonaws.com(43924):bot@wl_us_ultra:[1323]:LOG: duration: 1125.188 ms statement: ',\n", " '\\twith artist as (',\n", " '\\t select',\n", " '\\t groupkey as id,',\n", " '\\t arid,',\n", " '\\t scid,',\n", " '\\t eid,',\n", " '\\t inid,',\n", " '\\t spyid,',\n", " \"\\t to_jsonb(p.*) || jsonb_build_object('query_key', p.groupkey) as artist\",\n", " \"\\t from setf_artist_profiles(('sc')::text, variadic (ARRAY[29902258, 14625993, 179256009, 11997233, 4615165, 818321, 9195820, 235638983, 20139082, 20960469, 20405927, 148161100, 2089849, 24145368, 40351414, 268962756])::text[]) p\",\n", " '--',\n", " '2018-12-05 17:29:06 UTC:ec2-52-215-15-208.eu-west-1.compute.amazonaws.com(43933):bot@wl_us_ultra:[2628]:LOG: duration: 16094.050 ms statement: ',\n", " '\\tselect',\n", " '\\t spy_tracks.spyid,',\n", " '\\t first(spy_tracks.primary_artist_spyid) as artist_id,',\n", " \"\\t first(spy_tracks.data->'name') tune,\",\n", " \"\\t first(spy_tracks.data->'artists'->0->'name') artist,\",\n", " \"\\t first(spy_tracks.data->'external_urls'->>'spotify') link,\",\n", " '\\t first(pop.vals) as vals,',\n", " \"\\t COALESCE(first(countries.iso2), '00') as country_code,\",\n", " '\\t current_date as_of,',\n", " '\\t first(spy_tracks.first_seen) first_seen,',\n", " '--',\n", " '2018-12-05 17:34:22 UTC:ec2-52-215-15-208.eu-west-1.compute.amazonaws.com(43940):bot@wl_us_ultra:[5526]:LOG: duration: 3144.341 ms statement: ',\n", " '\\twith artist as (',\n", " '\\t select',\n", " '\\t groupkey as id,',\n", " '\\t arid,',\n", " '\\t scid,',\n", " '\\t eid,',\n", " '\\t inid,',\n", " '\\t spyid,',\n", " \"\\t to_jsonb(p.*) || jsonb_build_object('query_key', p.groupkey) as artist\",\n", " \"\\t from setf_artist_profiles(('a')::text, variadic (ARRAY[2598, 3595, 16326])::text[]) p\",\n", " '--',\n", " '2018-12-05 17:35:40 UTC:ec2-52-215-15-208.eu-west-1.compute.amazonaws.com(43926):bot@wl_us_ultra:[1381]:LOG: duration: 558964.348 ms statement: ',\n", " '\\t',\n", " '\\t',\n", " '\\tselect',\n", " '\\t distinct',\n", " '\\t t.spyid,',\n", " '\\t t.primary_artist_spyid,',\n", " \"\\t t.album_data->>'release_date',\",\n", " \"\\t coalesce(t.data->>'popularity', '0')::int\",\n", " '\\t from spy_tracks t',\n", " '\\t join spy_playlist_track spt on spt.track_spyid = t.spyid',\n", " '--',\n", " '2018-12-05 17:37:21 UTC:ec2-52-215-15-208.eu-west-1.compute.amazonaws.com(43930):bot@wl_us_ultra:[1892]:LOG: duration: 593676.900 ms statement: ',\n", " '\\t',\n", " '\\t',\n", " '\\tselect',\n", " '\\t distinct',\n", " '\\t t.spyid,',\n", " '\\t t.primary_artist_spyid,',\n", " \"\\t t.album_data->>'release_date',\",\n", " \"\\t coalesce(t.data->>'popularity', '0')::int\",\n", " '\\t from spy_tracks t',\n", " '\\t join spy_playlist_track spt on spt.track_spyid = t.spyid',\n", " '--',\n", " '2018-12-05 17:37:25 UTC:ec2-52-215-15-208.eu-west-1.compute.amazonaws.com(43929):bot@wl_us_ultra:[1863]:LOG: duration: 604377.981 ms statement: ',\n", " '\\t',\n", " '\\t',\n", " '\\tselect',\n", " '\\t distinct',\n", " '\\t t.spyid,',\n", " '\\t t.primary_artist_spyid,',\n", " \"\\t t.album_data->>'release_date',\",\n", " \"\\t coalesce(t.data->>'popularity', '0')::int\",\n", " '\\t from spy_tracks t',\n", " '\\t join spy_playlist_track spt on spt.track_spyid = t.spyid',\n", " '--',\n", " '2018-12-05 17:38:09 UTC:ec2-52-215-15-208.eu-west-1.compute.amazonaws.com(43932):bot@wl_us_ultra:[2376]:LOG: duration: 581884.698 ms statement: ',\n", " '\\t',\n", " '\\t',\n", " '\\tselect',\n", " '\\t distinct',\n", " '\\t t.spyid,',\n", " '\\t t.primary_artist_spyid,',\n", " \"\\t t.album_data->>'release_date',\",\n", " \"\\t coalesce(t.data->>'popularity', '0')::int\",\n", " '\\t from spy_tracks t',\n", " '\\t join spy_playlist_track spt on spt.track_spyid = t.spyid',\n", " '--',\n", " '2018-12-05 17:38:50 UTC:ec2-52-215-15-208.eu-west-1.compute.amazonaws.com(43936):bot@wl_us_ultra:[2994]:LOG: duration: 561939.758 ms statement: ',\n", " '\\t',\n", " '\\t',\n", " '\\tselect',\n", " '\\t distinct',\n", " '\\t t.spyid,',\n", " '\\t t.primary_artist_spyid,',\n", " \"\\t t.album_data->>'release_date',\",\n", " \"\\t coalesce(t.data->>'popularity', '0')::int\",\n", " '\\t from spy_tracks t',\n", " '\\t join spy_playlist_track spt on spt.track_spyid = t.spyid',\n", " '--',\n", " '2018-12-05 17:42:39 UTC:ec2-52-215-15-208.eu-west-1.compute.amazonaws.com(43945):bot@wl_us_ultra:[9669]:LOG: duration: 2093.420 ms statement: ',\n", " '\\twith',\n", " '\\tactivity as (',\n", " '\\t select',\n", " '\\t extract(epoch from newer.seen_at)::int as \"time\",',\n", " '\\t newer.followee_eid as \"artist_id\",',\n", " '\\t newer.influencer_eid as \"influencer_id\",',\n", " '\\t 0 as \"type\",',\n", " '\\t COALESCE(',\n", " '\\t array_agg(',\n", " '\\t array[extract(epoch from older.seen_at)::int, older.influencer_eid]',\n", " '--',\n", " '2018-12-05 17:42:41 UTC:ec2-52-215-15-208.eu-west-1.compute.amazonaws.com(43946):bot@wl_us_ultra:[9705]:LOG: duration: 1111.730 ms statement: ',\n", " '\\twith ',\n", " '\\tartists as (',\n", " '\\t select distinct',\n", " '\\t te.eid as \"id\",',\n", " '\\t te.name,',\n", " \"\\t te.twitter_data->'screen_name'::text as username,\",\n", " \"\\t (twitter_data->>'followers_count')::integer total_followers,\",\n", " \"\\t concat('https://twitter.com/', te.twitter_data->>'screen_name') as url,\",\n", " \"\\t te.twitter_data->>'profile_image_url_https' as thumbnail,\",\n", " '\\t te.first_seen',\n", " '--',\n", " '2018-12-05 17:43:00 UTC:ec2-52-215-15-208.eu-west-1.compute.amazonaws.com(43947):bot@wl_us_ultra:[9836]:LOG: duration: 5901.908 ms statement: ',\n", " '\\twith ',\n", " '\\tlikes as (',\n", " '\\t select',\n", " '\\t seen_at as \"time\",',\n", " '\\t influencer_scid as \"influencer_id\",',\n", " '\\t artist_scid as \"artist_id\",',\n", " '\\t \\'Like\\'::text as \"type\",',\n", " '\\t track_scid as track_id',\n", " '\\t from sc_likes ',\n", " '\\t join sc_tracks on sc_tracks.scid = track_scid',\n", " '--',\n", " '2018-12-05 17:43:03 UTC:ec2-52-215-15-208.eu-west-1.compute.amazonaws.com(43947):bot@wl_us_ultra:[9836]:LOG: duration: 2973.883 ms statement: ',\n", " '\\twith ',\n", " '\\tlikes as (',\n", " '\\t select',\n", " '\\t seen_at as \"time\",',\n", " '\\t influencer_scid as \"influencer_id\",',\n", " '\\t artist_scid as \"artist_id\",',\n", " '\\t \\'Like\\'::text as \"type\",',\n", " '\\t track_scid as track_id',\n", " '\\t from sc_likes ',\n", " '\\t join sc_tracks on sc_tracks.scid = track_scid',\n", " '--',\n", " '2018-12-05 17:54:28 UTC:ec2-52-215-15-208.eu-west-1.compute.amazonaws.com(43959):bot@wl_us_ultra:[15888]:LOG: duration: 1279.364 ms statement: ',\n", " '\\twith artist as (',\n", " '\\t select',\n", " '\\t groupkey as id,',\n", " '\\t arid,',\n", " '\\t scid,',\n", " '\\t eid,',\n", " '\\t inid,',\n", " '\\t spyid,',\n", " \"\\t to_jsonb(p.*) || jsonb_build_object('query_key', p.groupkey) as artist\",\n", " \"\\t from setf_artist_profiles(('a')::text, variadic (ARRAY[34375, 60, 176, 17, 885, 805, 905, 965, 624, 1008, 6266, 13782, 18398, 40182, 34811, 2348, 41908, 46165, 15892, 61638, 73295])::text[]) p\",\n", " '--',\n", " '2018-12-05 17:59:55 UTC:ec2-52-215-15-208.eu-west-1.compute.amazonaws.com(43964):bot@wl_us_ultra:[18637]:LOG: duration: 1088.383 ms statement: ',\n", " '\\twith artist as (',\n", " '\\t select',\n", " '\\t groupkey as id,',\n", " '\\t arid,',\n", " '\\t scid,',\n", " '\\t eid,',\n", " '\\t inid,',\n", " '\\t spyid,',\n", " \"\\t to_jsonb(p.*) || jsonb_build_object('query_key', p.groupkey) as artist\",\n", " \"\\t from setf_artist_profiles(('a')::text, variadic (ARRAY[34375, 60, 176, 17, 885, 805, 905, 965, 624, 1008, 6266, 13782, 18398, 40182, 34811, 2348, 41908, 46165, 15892, 61638, 73295])::text[]) p\"]" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "lines.splitlines()" ] }, { "cell_type": "code", "execution_count": 34, "metadata": { "collapsed": true }, "outputs": [], "source": [ "data = []" ] }, { "cell_type": "code", "execution_count": 35, "metadata": { "collapsed": true }, "outputs": [], "source": [ "for line in lines.splitlines():\n", " if 'LOG: duration:' in line:\n", " dt = parser.parse(line[:23])\n", " dur = float(line.split('LOG: duration:')[-1].split(' ms')[0].strip())\n", " data.append([dt, dur, ''])\n", " elif line != '--':\n", " data[-1][-1] += line + \"\\n\"" ] }, { "cell_type": "code", "execution_count": 36, "metadata": { "collapsed": true, "scrolled": false }, "outputs": [], "source": [ "import pandas as pd" ] }, { "cell_type": "code", "execution_count": 37, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
012
02018-12-05 17:22:56+00:0040618.950\\t\\n\\t\\n\\tselect\\n\\t distinct\\n\\t t.spyid,...
12018-12-05 17:22:57+00:001218.363\\twith artist as (\\n\\t select\\n\\t groupkey...
22018-12-05 17:23:00+00:002775.806\\twith\\n\\tartistids as (\\n\\t select array_agg...
32018-12-05 17:24:26+00:0011103.018\\t\\n\\t\\n\\tselect\\n\\t distinct\\n\\t t.spyid,...
42018-12-05 17:24:28+00:001182.255\\twith artist as (\\n\\t select\\n\\t groupkey...
52018-12-05 17:24:28+00:002152.665\\twith artist as (\\n\\t select\\n\\t groupkey...
62018-12-05 17:24:32+00:003648.820\\twith\\n\\tartistids as (\\n\\t select array_agg...
72018-12-05 17:25:02+00:0011092.290\\t\\n\\t\\n\\tselect\\n\\t distinct\\n\\t t.spyid,...
82018-12-05 17:25:04+00:001751.579\\twith artist as (\\n\\t select\\n\\t groupkey...
92018-12-05 17:25:08+00:003742.929\\twith\\n\\tartistids as (\\n\\t select array_agg...
102018-12-05 17:25:12+00:005092.506\\tselect\\n\\t distinct\\n\\t t.scid,\\n\\t t.art...
112018-12-05 17:25:13+00:001068.942\\twith artist as (\\n\\t select\\n\\t groupkey...
122018-12-05 17:25:31+00:001404.831\\twith artist as (\\n\\t select\\n\\t groupkey...
132018-12-05 17:25:52+00:001631.395\\twith artist as (\\n\\t select\\n\\t groupkey...
142018-12-05 17:26:11+00:001581.801\\twith artist as (\\n\\t select\\n\\t groupkey...
152018-12-05 17:26:13+00:001148.555\\twith artist as (\\n\\t select\\n\\t groupkey...
162018-12-05 17:26:16+00:001125.188\\twith artist as (\\n\\t select\\n\\t groupkey...
172018-12-05 17:29:06+00:0016094.050\\tselect\\n\\t spy_tracks.spyid,\\n\\t first(spy...
182018-12-05 17:34:22+00:003144.341\\twith artist as (\\n\\t select\\n\\t groupkey...
192018-12-05 17:35:40+00:00558964.348\\t\\n\\t\\n\\tselect\\n\\t distinct\\n\\t t.spyid,...
202018-12-05 17:37:21+00:00593676.900\\t\\n\\t\\n\\tselect\\n\\t distinct\\n\\t t.spyid,...
212018-12-05 17:37:25+00:00604377.981\\t\\n\\t\\n\\tselect\\n\\t distinct\\n\\t t.spyid,...
222018-12-05 17:38:09+00:00581884.698\\t\\n\\t\\n\\tselect\\n\\t distinct\\n\\t t.spyid,...
232018-12-05 17:38:50+00:00561939.758\\t\\n\\t\\n\\tselect\\n\\t distinct\\n\\t t.spyid,...
242018-12-05 17:42:39+00:002093.420\\twith\\n\\tactivity as (\\n\\t select\\n\\t ext...
252018-12-05 17:42:41+00:001111.730\\twith \\n\\tartists as (\\n\\t select distinct\\n...
262018-12-05 17:43:00+00:005901.908\\twith \\n\\tlikes as (\\n\\t select\\n\\t seen_...
272018-12-05 17:43:03+00:002973.883\\twith \\n\\tlikes as (\\n\\t select\\n\\t seen_...
282018-12-05 17:54:28+00:001279.364\\twith artist as (\\n\\t select\\n\\t groupkey...
292018-12-05 17:59:55+00:001088.383\\twith artist as (\\n\\t select\\n\\t groupkey...
\n", "
" ], "text/plain": [ " 0 1 \\\n", "0 2018-12-05 17:22:56+00:00 40618.950 \n", "1 2018-12-05 17:22:57+00:00 1218.363 \n", "2 2018-12-05 17:23:00+00:00 2775.806 \n", "3 2018-12-05 17:24:26+00:00 11103.018 \n", "4 2018-12-05 17:24:28+00:00 1182.255 \n", "5 2018-12-05 17:24:28+00:00 2152.665 \n", "6 2018-12-05 17:24:32+00:00 3648.820 \n", "7 2018-12-05 17:25:02+00:00 11092.290 \n", "8 2018-12-05 17:25:04+00:00 1751.579 \n", "9 2018-12-05 17:25:08+00:00 3742.929 \n", "10 2018-12-05 17:25:12+00:00 5092.506 \n", "11 2018-12-05 17:25:13+00:00 1068.942 \n", "12 2018-12-05 17:25:31+00:00 1404.831 \n", "13 2018-12-05 17:25:52+00:00 1631.395 \n", "14 2018-12-05 17:26:11+00:00 1581.801 \n", "15 2018-12-05 17:26:13+00:00 1148.555 \n", "16 2018-12-05 17:26:16+00:00 1125.188 \n", "17 2018-12-05 17:29:06+00:00 16094.050 \n", "18 2018-12-05 17:34:22+00:00 3144.341 \n", "19 2018-12-05 17:35:40+00:00 558964.348 \n", "20 2018-12-05 17:37:21+00:00 593676.900 \n", "21 2018-12-05 17:37:25+00:00 604377.981 \n", "22 2018-12-05 17:38:09+00:00 581884.698 \n", "23 2018-12-05 17:38:50+00:00 561939.758 \n", "24 2018-12-05 17:42:39+00:00 2093.420 \n", "25 2018-12-05 17:42:41+00:00 1111.730 \n", "26 2018-12-05 17:43:00+00:00 5901.908 \n", "27 2018-12-05 17:43:03+00:00 2973.883 \n", "28 2018-12-05 17:54:28+00:00 1279.364 \n", "29 2018-12-05 17:59:55+00:00 1088.383 \n", "\n", " 2 \n", "0 \\t\\n\\t\\n\\tselect\\n\\t distinct\\n\\t t.spyid,... \n", "1 \\twith artist as (\\n\\t select\\n\\t groupkey... \n", "2 \\twith\\n\\tartistids as (\\n\\t select array_agg... \n", "3 \\t\\n\\t\\n\\tselect\\n\\t distinct\\n\\t t.spyid,... \n", "4 \\twith artist as (\\n\\t select\\n\\t groupkey... \n", "5 \\twith artist as (\\n\\t select\\n\\t groupkey... \n", "6 \\twith\\n\\tartistids as (\\n\\t select array_agg... \n", "7 \\t\\n\\t\\n\\tselect\\n\\t distinct\\n\\t t.spyid,... \n", "8 \\twith artist as (\\n\\t select\\n\\t groupkey... \n", "9 \\twith\\n\\tartistids as (\\n\\t select array_agg... \n", "10 \\tselect\\n\\t distinct\\n\\t t.scid,\\n\\t t.art... \n", "11 \\twith artist as (\\n\\t select\\n\\t groupkey... \n", "12 \\twith artist as (\\n\\t select\\n\\t groupkey... \n", "13 \\twith artist as (\\n\\t select\\n\\t groupkey... \n", "14 \\twith artist as (\\n\\t select\\n\\t groupkey... \n", "15 \\twith artist as (\\n\\t select\\n\\t groupkey... \n", "16 \\twith artist as (\\n\\t select\\n\\t groupkey... \n", "17 \\tselect\\n\\t spy_tracks.spyid,\\n\\t first(spy... \n", "18 \\twith artist as (\\n\\t select\\n\\t groupkey... \n", "19 \\t\\n\\t\\n\\tselect\\n\\t distinct\\n\\t t.spyid,... \n", "20 \\t\\n\\t\\n\\tselect\\n\\t distinct\\n\\t t.spyid,... \n", "21 \\t\\n\\t\\n\\tselect\\n\\t distinct\\n\\t t.spyid,... \n", "22 \\t\\n\\t\\n\\tselect\\n\\t distinct\\n\\t t.spyid,... \n", "23 \\t\\n\\t\\n\\tselect\\n\\t distinct\\n\\t t.spyid,... \n", "24 \\twith\\n\\tactivity as (\\n\\t select\\n\\t ext... \n", "25 \\twith \\n\\tartists as (\\n\\t select distinct\\n... \n", "26 \\twith \\n\\tlikes as (\\n\\t select\\n\\t seen_... \n", "27 \\twith \\n\\tlikes as (\\n\\t select\\n\\t seen_... \n", "28 \\twith artist as (\\n\\t select\\n\\t groupkey... \n", "29 \\twith artist as (\\n\\t select\\n\\t groupkey... " ] }, "execution_count": 37, "metadata": {}, "output_type": "execute_result" } ], "source": [ "pd.DataFrame(data)" ] }, { "cell_type": "code", "execution_count": 38, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\t\n", "\t\n", "\tselect\n", "\t distinct\n", "\t t.spyid,\n", "\t t.primary_artist_spyid,\n", "\t t.album_data->>'release_date',\n", "\t coalesce(t.data->>'popularity', '0')::int\n", "\t from spy_tracks t\n", "\t join spy_playlist_track spt on spt.track_spyid = t.spyid\n", "\n" ] } ], "source": [ "print(data[21][2])" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.6.1" } }, "nbformat": 4, "nbformat_minor": 2 }