{ "cells": [ { "metadata": {}, "cell_type": "markdown", "source": "# Setup" }, { "metadata": { "trusted": true }, "cell_type": "code", "source": "import pyathena\nimport pandas as pd\nimport matplotlib.pyplot as plt\nimport numpy as np\nimport json", "execution_count": 17, "outputs": [] }, { "metadata": { "trusted": true }, "cell_type": "code", "source": "conn = pyathena.connect(\n s3_staging_dir='s3://aws-athena-query-results-313962271029-us-east-1/boto/',\n schema_name='ddt',\n )\ncursor = conn.cursor()", "execution_count": 5, "outputs": [] }, { "metadata": { "trusted": true }, "cell_type": "code", "source": "def to_days(secs): return f\"{round(secs/60/60/24, 1)} days\"\n\ndef make_histo(intervals, weights, offset=0):\n plt.rcParams['figure.figsize'] = [18,12]\n plt.clf()\n plt.xticks(rotation='vertical')\n plt.hist([str(to_days(interval)) for interval in intervals[offset:]], 100, weights=weights[offset:])\n \n \ndef prep_data(h):\n intervals = []; weights = []\n for interval, weight in sorted((float(k), float(v)) for k,v in h.items()):\n intervals.append(interval)\n weights.append(weight)\n return intervals, weights", "execution_count": 6, "outputs": [] }, { "metadata": {}, "cell_type": "markdown", "source": "# Summary Stats" }, { "metadata": { "trusted": true }, "cell_type": "code", "source": "sql = \"\"\"\nselect\ncount(gid) as row_count,\ncount(distinct gid) as distinct_gid,\ncount(distinct uuid) as distinct_ccid,\nmin(gid_duration) as min_gid,\nmax(gid_duration) as max_gid,\navg(gid_duration) as avg_gid,\nstddev(gid_duration) as stddev_gid,\nvariance(gid_duration) as variance_gid,\napprox_percentile(gid_duration, 0.5) median_gid\n\nFROM match_table_gid_flat\"\"\"\n\n\nsummary_stats = pd.read_sql(sql, conn)\nsummary_stats", "execution_count": 7, "outputs": [ { "data": { "text/html": "
| \n | row_count | \ndistinct_gid | \ndistinct_ccid | \nmin_gid | \nmax_gid | \navg_gid | \nstddev_gid | \nvariance_gid | \nmedian_gid | \n
|---|---|---|---|---|---|---|---|---|---|
| 0 | \n1545461697 | \n1467211934 | \n1474928687 | \n-310.176 | \n1.171415e+08 | \n3.438625e+06 | \n7.971556e+06 | \n6.354570e+13 | \n1.101562 | \n
| \n | row_count | \ndistinct_gid | \ndistinct_ccid | \nmin_gid | \nmax_gid | \navg_gid | \nstddev_gid | \nvariance_gid | \nmedian_gid | \n
|---|---|---|---|---|---|---|---|---|---|
| 0 | \n65521234 | \n65389249 | \n64417794 | \n2.0 | \n143.769779 | \n36.366595 | \n30.477742 | \n8.025634e+07 | \n27.899259 | \n
| \n | uuid | \ngid | \nhash_value | \nhash_type | \n
|---|---|---|---|---|
| 0 | \n03f8fd10-80f9-43fa-93a7-ef281f2d69a6 | \nCAESEMQ-V2F2x6nWT6UC93PKFQQ | \n9e5b0ba9e12efd8943b64b9b64a79fee5c07f89afecd91... | \nsha256_lower | \n
| 1 | \n19fec253-fadd-4ecd-919b-c5e52168e138 | \nCAESENoNtwdN_GngybMiFQ56AiI | \n4384c9f788a4416a82587842f5443666a368b34f101c5d... | \nsha256_lower | \n
| 2 | \n19fec253-fadd-4ecd-919b-c5e52168e138 | \nCAESEOnyD_cPAAax48ztPJn5rDM | \n4384c9f788a4416a82587842f5443666a368b34f101c5d... | \nsha256_lower | \n
| 3 | \n19fec253-fadd-4ecd-919b-c5e52168e138 | \nCAESEE5_-qt-EU4EstPpFGGpKLc | \n4384c9f788a4416a82587842f5443666a368b34f101c5d... | \nsha256_lower | \n
| 4 | \n9d308bf9-147b-41f7-a772-95ee30796145 | \nCAESEJ1k2PHzrw0E2Mlniak5yXs | \n59c88feb665ac29d0da9e3d55fd1ec2bc0ea82488593f0... | \nsha256_lower | \n
| 5 | \nae169fa1-a1db-4b19-a535-f8de54ded3e7 | \nCAESEH2495FsZya7czKhdGtVL4o | \n08a48ae87f2f92c5901b873ab811dd19949f53c07d7eff... | \nsha256_lower | \n
| 6 | \n7fc2025c-b699-4aa5-ca42-9eb485f3ca50 | \nCAESEJQWPFgD6rSnFpWF9uJBYnA | \n29cbe07adb8d33db6d7f2ec172f38261bce2a8e8d962c9... | \nsha256_lower | \n
| 7 | \n7a2153b9-fa16-476c-8104-0ecbb714351d | \nCAESEJHSxhWZhUFKKl1NiUlDFEs | \n15afa00d578d122f3b937fe59d3571a850465b2d82d405... | \nsha256_lower | \n
| 8 | \n19d92277-f668-4ab7-8730-af5ad72adc70 | \nCAESED_oVDvaQZiIFT_BKb5DHrA | \nc57f6562f718bbf079786d2de4d22e1eda8b545690bfd6... | \nsha256_lower | \n
| 9 | \n96a22d82-26a8-45f6-c5ea-1c88b6b3d341 | \nCAESEEyEetVPINlc8FKdtLjXLKA | \n6b5e5e1a6b7aac7b7bddafdb36579c11c15f62b6be9742... | \nsha256_lower | \n
| \n | gid | \nuuid_count | \ndistinct_uuid | \nhashes | \n
|---|---|---|---|---|
| 0 | \nCAESEGxd8rNCTdCDkTFEsyCcvT4 | \n8 | \n1 | \n8 | \n
| 1 | \nCAESEHnNctUMuOUqY3R5jFRIMdg | \n4 | \n1 | \n4 | \n
| 2 | \nCAESEPeW5IewZe8YMnaJNHmtJVo | \n2 | \n1 | \n2 | \n
| 3 | \nCAESECVU56SIkPlf0gBF7R98IzQ | \n4 | \n1 | \n4 | \n
| 4 | \nCAESEFKzbbstcaJqIPIvKQflznU | \n3 | \n1 | \n3 | \n
| 5 | \nCAESEBrwGFI5LzxR8KLETDHz6Dc | \n2 | \n1 | \n2 | \n
| 6 | \nCAESEEjwKcVxxqVt-5_jE6E8NlA | \n4 | \n1 | \n4 | \n
| 7 | \nCAESEBnxmMiShYSw4JVAB6gu4mM | \n1 | \n1 | \n1 | \n
| 8 | \nCAESEL1L-SWjNKZmS0nInOjucQw | \n11 | \n1 | \n11 | \n
| 9 | \nCAESEL3vquEV5r5-TD0wJJPO9zQ | \n1 | \n1 | \n1 | \n
| 10 | \nCAESEKYpucradhQJ0aNnjL1y0tw | \n3 | \n1 | \n3 | \n
| 11 | \nCAESEHdymYkG3Sr9OjL3o3Oqg_0 | \n5 | \n1 | \n5 | \n
| 12 | \nCAESEJhi-zWONupb8IKh_qP-Ogg | \n2 | \n1 | \n2 | \n
| 13 | \nCAESEFERY1Qu2Q6MaTP_9E81Qjg | \n13 | \n1 | \n13 | \n
| 14 | \nCAESEIkr09AKtRQUcJ3l3_-N0PA | \n7 | \n1 | \n7 | \n
| 15 | \nCAESEIZgjySdKZCzjD1sdWPk4bc | \n4 | \n1 | \n4 | \n
| 16 | \nCAESENxdLO3TQHh7MYx-O0VxIzM | \n6 | \n1 | \n6 | \n
| 17 | \nCAESECP6Xrp2PK9CR09Q1nZ7Hxo | \n7 | \n1 | \n7 | \n
| 18 | \nCAESEJrNf3FRhqBHgXQ_h4Npr6c | \n7 | \n1 | \n7 | \n
| 19 | \nCAESEELGiiM3l0tgaXYcqwYyDfk | \n6 | \n1 | \n6 | \n
| 20 | \nCAESEOIur-a9pywojLvO8NNtiY8 | \n5 | \n1 | \n5 | \n
| 21 | \nCAESEBdu6g1Hi_JUEHYvv8HTO_s | \n3 | \n1 | \n3 | \n
| 22 | \nCAESENUYG-XWRzv2J9RjYxIM4sg | \n6 | \n1 | \n6 | \n
| 23 | \nCAESEAGfVFedFCXMNWLrjuQpiMQ | \n8 | \n1 | \n8 | \n
| 24 | \nCAESEAtJ-j9dvfpO3v4AMZdOI5Q | \n4 | \n1 | \n4 | \n
| 25 | \nCAESEBSr9y2EOWoTjr0aLq_TuDM | \n3 | \n1 | \n3 | \n
| 26 | \nCAESELgox9U-4Ne-yK3pK6wP_u4 | \n6 | \n1 | \n6 | \n
| 27 | \nCAESEAte4AaOFnLtmZQBIlbT9AQ | \n1 | \n1 | \n1 | \n
| 28 | \nCAESEEzpe6gFATYSanRu07PdRGs | \n2 | \n1 | \n2 | \n
| 29 | \nCAESEP8Xiyuq8_BWKkwtKXIfN6w | \n4 | \n1 | \n4 | \n
| ... | \n... | \n... | \n... | \n... | \n
| 970 | \nCAESEGwC08jm8lBmbt4XpjFG198 | \n1 | \n1 | \n1 | \n
| 971 | \nCAESENGV3Qu6PTEAEf4n4F-nhLs | \n1 | \n1 | \n1 | \n
| 972 | \nCAESEHOIxdl-Fey9aijUh6_Atow | \n1 | \n1 | \n1 | \n
| 973 | \nCAESEO-YJH4yVLLLr8_1Qc4X5V4 | \n5 | \n1 | \n5 | \n
| 974 | \nCAESEEyi_IT8pOuyt7i6NwzNcqU | \n1 | \n1 | \n1 | \n
| 975 | \nCAESEJS95ezGtFToklRK_LPZ3rM | \n6 | \n1 | \n6 | \n
| 976 | \nCAESENPjXStM0s1n2GeixFLqZZo | \n10 | \n1 | \n10 | \n
| 977 | \nCAESENEtZzYRwEmy6uN55v1SzcI | \n1 | \n1 | \n1 | \n
| 978 | \nCAESEOErQafo6xX7lbWHWZOQ_wI | \n1 | \n1 | \n1 | \n
| 979 | \nCAESEBxFNk4XqE0ubGtyIr322-E | \n9 | \n1 | \n9 | \n
| 980 | \nCAESEJRr5FdMCvfQxhYPlG4-Hbk | \n2 | \n1 | \n2 | \n
| 981 | \nCAESEOhIRS6Lb6cJngr8yxR_g60 | \n1 | \n1 | \n1 | \n
| 982 | \nCAESEDVPSVhHbn8oqQ8fB00KfrE | \n1 | \n1 | \n1 | \n
| 983 | \nCAESEMvwKW_m5DVF9cqs6sr-bUQ | \n1 | \n1 | \n1 | \n
| 984 | \nCAESELiK3-TYCYvVFh-S7UwMvHQ | \n6 | \n1 | \n6 | \n
| 985 | \nCAESEPxAoqzx7Jc_cBQ2rYvKwL4 | \n1 | \n1 | \n1 | \n
| 986 | \nCAESEDD5QZ2Jh4bQwfA2VNZwVxQ | \n1 | \n1 | \n1 | \n
| 987 | \nCAESEDRyIMe4MbqcLvapwI65V2Q | \n3 | \n1 | \n3 | \n
| 988 | \nCAESEAOecJNzO33XYJtImKnJGkE | \n1 | \n1 | \n1 | \n
| 989 | \nCAESEDjDe8wS0sOWmqDTyKjXICc | \n1 | \n1 | \n1 | \n
| 990 | \nCAESEBtma0Z5Zs4IpjEPMOzm7ZU | \n1 | \n1 | \n1 | \n
| 991 | \nCAESEDqDoJep0-bQ_kF_OLiZCxc | \n1 | \n1 | \n1 | \n
| 992 | \nCAESED6IqPfryzf-xugrCsVGqWQ | \n5 | \n1 | \n5 | \n
| 993 | \nCAESELiuXaZDx3O7FIYbRRIIBWU | \n2 | \n1 | \n2 | \n
| 994 | \nCAESEJhLOCpRwOrq58lo3TI2JH4 | \n20 | \n1 | \n20 | \n
| 995 | \nCAESEHrrI0CSAkQFYaN5u5ZfQdA | \n1 | \n1 | \n1 | \n
| 996 | \nCAESEOE3ReiHL_nP5of2_hr8Ru4 | \n1 | \n1 | \n1 | \n
| 997 | \nCAESEGBz6-YEsWnpyrvsMjV3k1M | \n5 | \n1 | \n5 | \n
| 998 | \nCAESEMZpQRrcjQKTc5lo4GngDKs | \n1 | \n1 | \n1 | \n
| 999 | \nCAESEPDmY4RELrq-XqxuYDlwOxo | \n1 | \n1 | \n1 | \n
1000 rows × 4 columns
\n| \n | hash_value | \nuuid_count | \ndistinct_uuid | \ngids | \n
|---|---|---|---|---|
| 0 | \n76dd90bb3a3760d3b137445be57d06d6527a6251e6a5f3... | \n44 | \n26 | \n44 | \n
| 1 | \ne2e7c29cb04850583f2ea0163a45f8f97fb5edef722253... | \n36 | \n33 | \n34 | \n
| 2 | \n5328afe44dadeb6701cbe4ea4062db8585f07d2d753502... | \n27 | \n22 | \n27 | \n
| 3 | \ndf0ee6b4b98a1c88f440effa7dd09af770fd1b2e8703ba... | \n8 | \n7 | \n8 | \n
| 4 | \n10fad05ddd847b4f311504475b09dd077e5fb06adfb476... | \n49 | \n34 | \n49 | \n
| \n | uuid | \ngid_count | \ndistinct_gid | \nhashes | \n
|---|---|---|---|---|
| 0 | \n7b20eb0e-0d69-40df-9351-3a898404e93a | \n3180 | \n318 | \n10 | \n
| 1 | \n72cd7536-612b-4b06-a346-014ff2cdb000 | \n2799 | \n311 | \n9 | \n
| 2 | \nf32555f1-47fa-4287-a4b3-59248704f8b0 | \n22932 | \n234 | \n98 | \n
| 3 | \n2a933e51-6091-46bd-b94e-30a12aa843f5 | \n2097 | \n233 | \n9 | \n
| 4 | \nbcd48057-2a30-47d9-c7d5-814380fa0aff | \n2290 | \n229 | \n10 | \n
| 5 | \nf8e803f9-867c-4529-c5c0-36bb79f56958 | \n852 | \n213 | \n4 | \n
| 6 | \n268f1d68-f9aa-42ec-b7e4-4d9639c16d5e | \n1236 | \n206 | \n6 | \n
| 7 | \n4ade15c6-1fe7-443f-c4c0-cae1691009c9 | \n606 | \n202 | \n3 | \n
| 8 | \n4fe6b0fc-6246-4ac8-9c0e-835e8798a319 | \n3618 | \n201 | \n18 | \n
| 9 | \nb291ae63-c5ed-4e51-ceab-044d7c717809 | \n594 | \n198 | \n3 | \n
| 10 | \n308e3581-5662-47e8-8b20-07cdba2bb812 | \n1728 | \n192 | \n9 | \n
| 11 | \n30a6620c-8b23-4f04-cfd5-bd09a83adb83 | \n561 | \n187 | \n3 | \n
| 12 | \n75922c42-2b95-47c5-c77a-a5e206f6507d | \n555 | \n185 | \n3 | \n
| 13 | \n3aafbb45-ec10-4c1b-bd76-f81ba72a2165 | \n740 | \n185 | \n4 | \n
| 14 | \nec471edd-429c-4edd-9600-287d26e5fc0f | \n534 | \n178 | \n3 | \n
| 15 | \nf2964bee-92e8-4fab-888e-86da448996b7 | \n1344 | \n168 | \n8 | \n
| 16 | \ne4bd4a0e-fe3f-4fe4-a6df-b1be28e7d77b | \n1008 | \n168 | \n6 | \n
| 17 | \n73fdc1f0-00c1-4fc1-b0e2-d3bc45873249 | \n1336 | \n167 | \n8 | \n
| 18 | \ncc899139-db7d-4e1c-ceae-465f08dc5212 | \n1320 | \n165 | \n8 | \n
| 19 | \n1306d68a-c3a8-4199-c11e-bf4c83c83a5e | \n162 | \n160 | \n2 | \n
| 20 | \n795e6229-c137-4387-c3c7-c5060cf131bc | \n636 | \n159 | \n4 | \n
| 21 | \n6d23f00f-b40b-4933-c9ff-9822e002c0fd | \n159 | \n159 | \n1 | \n
| 22 | \n0de4ad21-1595-48ca-c8d2-824fb8d45753 | \n628 | \n157 | \n4 | \n
| 23 | \n50b4f0e2-c040-4651-9b36-a63b6c7efe36 | \n1240 | \n155 | \n8 | \n
| 24 | \nd242a57b-9cc8-4af5-9574-42fdac05ead5 | \n462 | \n154 | \n3 | \n
| 25 | \n34cc083f-b7ed-4bf7-9535-b3afdb528178 | \n765 | \n153 | \n5 | \n
| 26 | \nca73964f-a8ce-4e91-b2fa-2155bebde14c | \n444 | \n148 | \n3 | \n
| 27 | \na5faa736-d424-4bf7-99a3-4e9e77955cc2 | \n6660 | \n148 | \n45 | \n
| 28 | \n2aa1090d-9233-4ede-9463-17ad6bb87c8e | \n2044 | \n146 | \n14 | \n
| 29 | \n3573d35b-0f72-4adf-ca5b-c2532cd50244 | \n715 | \n143 | \n5 | \n
| ... | \n... | \n... | \n... | \n... | \n
| 970 | \n9b3d597c-fb78-482a-c813-7ad2558887e6 | \n156 | \n52 | \n3 | \n
| 971 | \n3a2333d2-467f-4c04-967b-62937bc3d38b | \n104 | \n52 | \n2 | \n
| 972 | \nd8b2e041-3499-49cc-90c9-4c48c6a97a09 | \n416 | \n52 | \n8 | \n
| 973 | \nd6ba8323-7e87-4fdf-ae19-bb6a6c17c7ec | \n104 | \n52 | \n2 | \n
| 974 | \n88fe70b5-209b-43d8-9ad2-8114dbb25c30 | \n520 | \n52 | \n10 | \n
| 975 | \n2bb5efb7-5605-4a9e-ca29-4ad655f7fa9a | \n156 | \n52 | \n3 | \n
| 976 | \n7abf9508-27d2-4418-a09d-939878253b78 | \n312 | \n52 | \n6 | \n
| 977 | \n6d8f4f3f-7fe3-4192-c1d5-bfbd19694ee1 | \n208 | \n52 | \n4 | \n
| 978 | \nc2b88597-bcf4-4913-c97a-7ea2e908b415 | \n104 | \n52 | \n2 | \n
| 979 | \nbd082000-f6d8-4e26-9eae-7700740cf431 | \n312 | \n52 | \n6 | \n
| 980 | \n45c44fcd-0e75-4579-c516-4e9a2553bca9 | \n104 | \n52 | \n2 | \n
| 981 | \n4c6bf803-f137-455a-945e-61f9a37d58e4 | \n312 | \n52 | \n6 | \n
| 982 | \nb2d4f9b9-df93-472f-c7bd-cbce7c2ca5cb | \n52 | \n52 | \n1 | \n
| 983 | \n5b3de89e-32d3-465b-c68a-3e4f39e1028b | \n104 | \n52 | \n2 | \n
| 984 | \n46b6baca-d73d-4990-a586-27091a8161ab | \n260 | \n52 | \n5 | \n
| 985 | \ncc1d7aaa-fd66-43d4-ac6f-0f595ced8391 | \n364 | \n52 | \n7 | \n
| 986 | \n968dcbbe-a2bb-4679-a759-57a6d0c8bbe6 | \n468 | \n52 | \n9 | \n
| 987 | \n4f71d6b9-4f2f-4efc-cea9-7c9d0c7ad1b9 | \n572 | \n52 | \n11 | \n
| 988 | \n02fd0bc0-c9b4-428d-ac72-76ce381647fe | \n312 | \n52 | \n6 | \n
| 989 | \ne81796a8-cb2e-4fdc-cf56-85583d2237ee | \n260 | \n52 | \n5 | \n
| 990 | \ncf88152b-5ce8-4fa7-941a-23cd4365b5f2 | \n260 | \n52 | \n5 | \n
| 991 | \n44f98740-4fcd-463a-9fc6-43e682b7a7d2 | \n156 | \n52 | \n3 | \n
| 992 | \nf3f7863b-ce74-4beb-92ce-837eadaaa9b8 | \n364 | \n52 | \n7 | \n
| 993 | \n4d0ef89e-c20c-46b2-82c6-2719aaacb606 | \n156 | \n52 | \n3 | \n
| 994 | \ne049004c-3e0b-45bf-96b0-84a1ca98964c | \n364 | \n52 | \n7 | \n
| 995 | \n943ac0ee-e8cb-471c-93ab-aca7638df8c2 | \n104 | \n52 | \n2 | \n
| 996 | \ndcfe2b12-e62c-48dc-8952-2340fdc42310 | \n624 | \n52 | \n12 | \n
| 997 | \n9cbc968c-5874-42d1-b335-e786084227c6 | \n52 | \n52 | \n1 | \n
| 998 | \nac8fd198-2167-4632-b04d-1683ad320639 | \n624 | \n52 | \n12 | \n
| 999 | \nacc5ef96-b2b2-48c7-c85e-76c8afd81bcb | \n728 | \n52 | \n14 | \n
1000 rows × 4 columns
\n