04_Source_Data_and_Ingestion.ipynb

{"nbformat":4,"nbformat_minor":0,"metadata":{"colab":{"name":"04_Source_Data_and_Ingestion.ipynb","provenance":[],"collapsed_sections":[],"toc_visible":true},"kernelspec":{"name":"python3","display_name":"Python 3"},"language_info":{"name":"python"}},"cells":[{"cell_type":"markdown","metadata":{"id":"view-in-github"},"source":["<center><a href=\"https://colab.research.google.com/github/AlisonJD/RTACourse/blob/main/04_Source_Data_and_Ingestion.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a></center>"]},{"cell_type":"markdown","metadata":{"id":"MJDu0CyaUx9e"},"source":["![tb_Logo Navbar.jpg](data:image/jpeg;base64,/9j/4AAQSkZJRgABAQEATgBOAAD/4QBWRXhpZgAATU0AKgAAAAgABAEaAAUAAAABAAAAPgEbAAUAAAABAAAARgEoAAMAAAABAAIAAAITAAMAAAABAAEAAAAAAAAAAABOAAAAAQAAAE4AAAAB/9sAhAACAgICAgICAgICAwMCAwMEAwMDAwQGBAQEBAQGCQUGBQUGBQkICQcHBwkIDgsJCQsOEA0MDRATERETGBcYHx8qAQICAgICAgICAgIDAwIDAwQDAwMDBAYEBAQEBAYJBQYFBQYFCQgJBwcHCQgOCwkJCw4QDQwNEBMRERMYFxgfHyr/wgARCAB4AZADASIAAhEBAxEB/8QAHgABAAICAgMBAAAAAAAAAAAAAAkKBwgFBgEDBAL/2gAIAQEAAAAAn8AAAAAAAAAPX5/YAAAAAAAI49S9jtldrPIAAAAAADjILMbSL7zdyAaT7WdsAD1+v6Brb2DOQH5+P7gYsiEi84C5f5DgYRZsez1UZvN8gAhq0xsxiAnJk1QEfkBNvk6bHFGdpd6G61oIEdVbywJJ/pHtH1rGnK68yCcPpnIZ+9QO2R+6Yza4BkI7JqjzWZNKc/4m3g0rxr+oIrfOgMVOh3GBMhOQCESGKTWfSp9PNhaHfaPA3drN9Oq1PsxT9nuwBEntXi7gbYFd3KklNWbaaQrmoU5D9U+kW+eHjrjH0Y+IWSZDwdApzXCMoVD55sLRcWutU6ql1Kt7tJvvWEuKw+RtWueEpuWcYpsqSU1VLk/bqlMuksUUsMFvkY7hqjHxwuA5XB0CnNcIyhUPnmwtHVaI15qW3So+oZd+MT2DYatK7NKnNYajXypJTVbunKe8+sicecCVvkIgtfOlcdZGA6fTXsqSBVEZ5sLR1WiNealt0r107e12Id2IaoZLM2C6+VvODDKklNVu6crX8dPJBHq/b5GNITG6sjv2AIL9CbPUAEvmLdJp+sMVyrSf016dGLff6jD1Lxfr3MRJ5CjkHeqvbaVYer5Y1kn09spiOXVKULYUAAA00gd3tnAAAAHrjkkK5UAAAQH8/NzzAAAA8eQAAAAAAAAAAAAAAAP/xAAaAQEAAwEBAQAAAAAAAAAAAAAAAwQFAgEG/9oACAECEAAAAAAAAAt28zg758ASRglv6WZmJNKtB4RS6uXFfp+82LEe12+dqp9erxakp1r0tObv2ner+Zfu3Z+b8dbNflZyd/G08nTe4m7nzZkujmxi/Dy7g3MC1NTmVbMsNeSMAJkIAAAAAf/EABgBAQADAQAAAAAAAAAAAAAAAAABAgME/9oACAEDEAAAAAAAAApTWSJAIkEZZ66oyvMkxjrbLQrWcIdN1cbTWL2zi8QvnZs569QwsV1w2y2yN+fSNYy1kZ2Ith0Ui9V6Ra0SAFVgAAAAA//EAD8QAAEEAgECAgcEBwYHAQAAAAUDBAYHAQIIAAkRExASFCA3OHYVFiF1MDU2QHN0dxcxNERQVxgZIiUyM0Fg/9oACAEBAAESAP8AWSBUYHbauSpNoxbbKao6r6b6K6aKJ767p7Yxtrt/qHNSwsE5CHrkev4sxGmCBLWM2BN4ZvjeLSsmNx4+OUorzPs4PlNKRshh9tj/AMt4hzKrg+qg0PCyoV8ptrprho6RfNW7xDzPIW01VT/02WyQfDowclBTb1WAtmq7VxIzxCUnzEjKq+YRJO1njjaQT6Hxf1tTR9qgvjw8W9NcRmEwi0VnUykLlIabGMzDYRC6rr6v0tNYpFmTJfGvq5efoLZ7hHHSnZo7ghkiaMHmC+WpTFb2TCbeh4qd1+cSLRh/jbKDn9G9JjBeE8kCDVp5nj5eWZ0A9W1bMDLBw528fBL3L+5d0vxtUHMbAKvV5A/R9pagqC5MVLyODECVbGlt3g7bTUmI9929ZD0cuHztFs38ca+YhI40opokjIRqiym2NNNPes2/6YpptutZNjBQq+unr6sOVncjidggPuJUcbJrDMvdF35eQ2xPJJ6+js4q3abf5aExp1NZpEoey9b208aYB0Mjh7QUwZDWSWqTNo3Sat0/dn0iWh0FmktbNk3C4MCRLpN/+b7Yn+zsc6rWVrzmuYDNnbRNq6kMbFHFm3V/8Yrvrm1pWHKQaQFm70w7cijXbjpee0vRZBrYbJcYYkR9Y8gE/R94f9V0B/NSbrtpfN3Avy077vcu48204u8jbYSMFT8IODhyWrntbUDaURm0styXgCUfiziOqgRzb3+6J8qhP6mDdcfPj5SH1/Gvcn9rVrVY77TsScBY8zzrnZPNr92GpY37UwqiKFJeR1/BMhbHcE5NWt7S0zNfusBW8cfZjl05euF3jxwqu6W32UWW9Hb1hX315YVnqql67EHu8kbr3+R3PKiYuyuSlSmkjzNERBeOb56qfuV8b4ZVlaQ8zpK/tgFFQwZ7mJSoXNInF5mFwt9jnxDI0wxMO5TxwhEtlMLN6Sv7ZAF3wR/1SN2wu/4PpYECwQwA2fOB+Or95KVvxqER0zY2pb2M07WZM+qZ53UbfE8YVxBdZFiRPGzpyjm7ubXHygiK8elcnXIy1H8VwIPuw8ciL9NmTjU4Ett9vDD2u7NgNsxtvLa5lTA8AWz6ntNx23GaPgJSx5k1Jqxscq3SeZb90vi2quij4y1LCm+umVdN9FdNFE99d099cba7dXNzvoeiZ4/riarHVZIybtnDrSheXlT8kDZwFWqR/dwHZaP36/eH/VdAfzUm67aXzdwL8tO9Xhzh4/UETXjcpkTorL0MYy5Ax3uu8byz/RiYATUI1328Pb4FYkHtGOtZbXsoHno648ddHnV583KW48zNCB2BrIMnVRiBbTqgOXNSckikiEVxqbw6CtEXj7No2PHahgMkseWYdfdwGho4eYGd0DjIVJDxbTSX+1PHKTRHq+OWFL8ckmqdiH1dj7pLz2keoHuA1XyGsNvWsXicrGmnLR29Qc9Wz3DeNNSknYFaSPpOea77JOWUR7qvGuQkkRxsfLo0kpvjX2/uRyIDLeHWZJGDDMqAIHwjhkQ4+fHykPr+NejkF3E6yo0q8izaEyo7MUMb41a2t3KeSVie1MY6YZwcEr464bmTZmRkXJiQF3pQu429dw+93tBwr2mXXBYiyP4DxLCPNVfe5V9vO05ZPbqvVjM4olG1diEmww6hfa2uWbw2JTYdP4WgPkAVgbaoVVFnsDrCuIMTcILkY5Fg4J2vyQ+Ye+v6iynrtbfKw1+qjHXd9+H1N/UJHqlbfO0fL3k5i+mPvHgEUFDnFddunlDcAfSbvUBIBIt4kElr64uXDxxfMEbIApain++yQ83wnv8AM0NeEYd4fqawmQvWwOTsrOgYuza6m1eF8a4HSEM8FqKSAETjB43GjTbLcyJfuRj5DhTZuLU4y1dIXDjzSrAZiPFMrroNUF3TlXRJuinsqqreNiLWzb9jWMtvvsmdPO3jTHa9qbMD4+bzd+28s1OyW5Tx7w/6roD+ak3VM22epKZ7z2LJ65kSQcqNHr1R27uRt2g0rAIrio6NMeJBu65BcMLt44M0TczGMSMRVW1b4kHFzkhK+NllDZSJdLrRR2qk2k4QKXGHxAo+GeJuhBFkg/Yue638zYn6FFddoH9vbm/IBnXPH5R7r/KWvQIlgKcDGMo+dhi+bvMpA6h5H81p/NLKARdwT3JlFVyJnhbwgviheQAOdzocG2jCQko0WddzvlOYhyDKgIAVUZkyrDD+Wv6+ryZ2nKxcIgIByYk5DfOrZnr2m+RWQvt+ZTBsFvL8z7Klsgt6loRYXFmyArtkzdFhxtMfx8+PlIfX8a9EthMOnwlQFN4sKPBt/wAdmVt9rKiJl7SQrsmUgphT1ttEbZ7cXJasvanwmOITUAl622HhAaREPXI0swcMiLffKa7X3O1zC/uxxhRkSiXg5lcjJF8b+9d3wXt76KP+jj18AqO/p/GfRyQ+Ye+v6iynrtbfKw1+qjHXd9+H1N/UJHrhhCxFgcoacjB1to5EqGt37hv1zvi4uV8U7fQJN9FNhgrU2zUxnOM421z4bYz44yCcqPQYZ4r/AO1di3W367mlT5r3ka+lTJt5YOcsEzyWe0RZf4WrT7xx/wDW0uFpc6bKxVnF+zCbZx5RYwyxFxnVeQorY87iECCa+JWQF2YlvmLR0VDo1HogBQwgECDGopkn3h/1XQH81JuuGlZjra5J1fDzbbRwB+0FSpNDGMYxjGMeGMf3YncLBWLDZPBJM01cgTo1wNeJSMI6jMhOxt94e2iiLoa467eEwcy3iZW+XyuyjwNl+A337rfzNifoUV12gf29ub8gGdc8flHuv8pa9J6bKqaJ6fjvvtjXXFZ1/H6rr6J19G2abcMCGoMUteuV0vcznkjdchcq5UxtLSLBvt2lawGDKznFtuWmmx84a3AtF+u7PWIwtVcLtdBppg+AN6BnLjj58fKQ+v417tk0pU9uD8j7KgAU9pjTy0nHL/gHVFYtw5asJSUGkizpXVIBI6nnUY9dR4EUcs9f816OPsN/s5o2poTlLy3QuLDEXuvvXd8F7e+ij/o49fAKjv6fxn0ckPmHvr+osp67W3ysNfqox13ffh9Tf1CR67efziU1/HNejmP8rd6/SL/0Rb9mI5+Vs+u5/U2J3x6xM2Db1zkFJaFsZ4TWX/ZXyaq4+u48oSQJYjpTru72Z55SrKfZuP8AoaoOJYUS7U9Tfe26j1nv23rioQKzq0367w/6roD+ak3XbaMNBPLiAJPN9dMEWJkejt6LYMNJDadlH2G2uzElKjJBvt2vmC7PikGcLYzhN9IzTpDrut/M2J+hRXXaB/b25vyAZ1zx+Ue6/wApa9Mv8Y0/jJ+m72DgVdFujHWudXLSaHm6uO1oaZP+LurFDfXLkVLCzNzp13SC7QZxZcsF99cLlZQIZNccfPj5SH1/Gve5Nz3792say2W9cQF/7Kx9EjruGyrzNjAJvu62/vdVbwgN2JP45mJKLu4mzMMVj+fftga6NVZZYdilso9fRUyzbp9cZ+bPHPFDVmLlNli4/JI/GRoIqMbroukEHTZXVRusnqqkpyQ+Ye+v6iynrtbfKw1+qjHXd9+H1N/UJHrt5/OJTX8c16OY/wArd6/SL/0Rb9mI5+Vs+pPGRUui8iiZ5DC4Y0Ndin6U+h5msp9LIOWzsmajhl2LVUvW4j98WQUseSaeUReMxzTyO3jVOKr4zRJy9beUfmG+8tf+jvD/AKroD+ak3UKlh6By2OTeML5QPASLYsxWpXnpx2tSLjyRieh4bKvI0wTCcwu4PWMYgEgg9LyxrJbANM1h2CYwa/Mkh4gUzVdlHzlJmza8fKz1pylK4rTx09qCBkUiCndb+ZsT9Ciuu0D+3tzfkAzrnj8o91/lLXpl/jGn8ZP09ymoH1ccizMtRabaxidJanWK/A/lsz41TQwImabhasJP5GpTfXl5xg2C/b+L2hnsHled5fPLlsx5KS4QDgyTnSr4t52zFbj58fKQ+v417tzzrWuq1k0l0U9Qhq3yzGY222322332ztttnxztppvvvqnprnbfbONddaY4jGJL7JIbK1XFAc+CqIgDHgcYFNQkdGtx4dtr6qTf9Byy7aU50l52fcfmLYtGyjlUg5iqXDzlGq9ww0omYYX9b1fWoZpMB1L1cMsAZuPmI+MjRxdpePCnlHKLqt+TAaiJOwRebSAmNd9v2sp1UXHxCIWNHVwkj0kJN5sy7lNJWldMLrAZV8PcH34w0+cvkuF3EbkZWnJqsJvOaufiYsMVKbPn/XJyKSGb8frbiMUG7v5EVjjxkOY/8BfLn/ZQp1H0FmYEI1cp50cID2ySunXdWq1GIXiBsVglomwnAf13GKHrJ1cdxV5WrbXfyzhlBu93aNWo9o1YMkNEGTZHRBuj13MaKti7R1No1XDHUg3DuD2xLHDXhXcYC5c5uupXbOuCUaNhiql+dtq8K2PEHVahXE4gW6u+7FwA4d8opK/0GsKLl6C+2/qeZwz7ezekirG0bZeMitkIa+uIF9dxDjDfNxXyOlla1y9OR5OJDx+7ztqcerlpSX2iStCCuwDIkHYNmKvL2GyaxON9qQuFiVCknKDm6LBg14Hct03TffelimNNVdM7Z9F+0FB+RVfPYDNkN9Mevh0LKW127eS1aFHaYeHqzOO4329lLRLhdyjmRNAYwpSTMdlNsY2cSTt8yGtOI0zisRHYl95yYiFWKq0xwm5Sxq4aokZyoCTQKKmIIkQde5zYnv2jIgNesl/FsKS+0iGsBriX2WY1CxIVu6Vx6uXDinONcOq7RsZJY0NTLGuNsv8A91tTn1x+p6wZNWsycnsSIFht7Vnl/wAlnnJ61NpagwXGxAY0wKjg3tOUm7cG5lfRhlnQezb7RqOb/uxsuxj4cqdJK+WPHtFnrneE8d5fc0oJ2TZmVwgIs+UIYaRaKRuFB24GLiEBwxHHjhH92uvtw8pJ1aM6niL+IlsSI6+L4VqbtLTNyVaP7qnIoeAT3xusKiENjMAiwWFw8QgKjAltq0Ysf3ZZFFdPKS6WiiWc4znT/wDQ/wD/xABHEAACAgAEAgQKBQkFCQAAAAABAgMEAAUREhMhEDFBYRQgIjJRYnKChIUGIzBSkxUkQFBxc4GSsmODoaLTM0JDU2B0lKPD/9oACAEBABM/AP1zbmSCNpH81FaQgansGFOqkHqII/WKnkbthdIkPfHEdffxWsOsLe3ESUb+IxJH4FZPv1/I/wAmOF4fBublor1xxD/JiSN4n2tzGqSAMv7CP1aDoX4Y1WNfWc8h347N8zFtq+hV6lHYMI3Gn58geFFubFGIRTrXuxLPGtiaXXa+1hvQLgpxbjj155d0nP0a6fY/R6mlqDL515PFPLNLEGkTqdY923EQZGV08l4po3AaORDyZGAP2lmZIt5Xr03ka6YgsRySNpzJCqSfFyWuLeYNX1Kcd1d44449RopdhuxmMPguY0+Lrw2ki1ZWRtOToWX7CeRY01PUNW0GEtxMzM3IAANzJ8eSfjZjIvpjpwb53/guM5C04rSwc40igjLuY93leWUOKH5tGO4lPKYe0Tgczxb0611/xfC9SQwqERR3ADxpCVSV6Nd51jYjmFYrocflCziJi8cD5jVSy0aluZVS+g6MvoT3q+aw2JTJHKksKsOKwPlx+crYs8p6FV4Iq6cdP+HNLwtzJ9p7lLHy+XxcrryXPyZPRgWq9edIQzRK+ziK/mktjNIHqT5nNYsQ2DMkMujcGEQ+f9h774+Yw+JmdtIZZdOyCInfK3qoCcXtcoyv2hxVad/ZKJj6Ioct8n0Na1ay3eOJtxM5kkkduZZ2bUkntJ6fu/k+Bmgb8dk8eHLkemLtqo0SaS8XzNX6K+Vo8XhNCpHXl2Nxhqu5MWECTeDX4VsxcRQTtfa41GK+VpJD4VQmatLw2Mw1XcmMygFafjVtN/kKz8vKxlVVbT8WBBI3EDOmmMxy9K8GypGZX1dZWx9HYBfuwd05LJDC3qO4bFmhUmgjX0utWy8n8qnFJ9TFJoGMU8baPDKAeaOAwxlNdbM8K2pRAkhRmTyN7AE4lylAia9r6TYU6qynmCCOjKaKWoYRaQTRoztInl7CGxmNAVa8aSvwkTfvby37Bj3KWPl8uPo3AL1uv2hZ3do4YW9R3D4uZfWngRfTIKdiWX+VTjL5eIFcczHKvJopF7UcBh0ZZQSzD4PYd4k1dpE56xnGa01qrwp3MabCjvqdRilEJp9kkqwjYhK7jucYfKYwnEmcIup42MohFzNJYericPciRIex5GUHGaw0xV4VRN7BzDYdlbo+iVdLywSLyKPZleKuWHaFclcZpl8M9RO9zQmnl/yYoTLPXniZ30ZHTHzGHot0J8gohkOnObMY1kZfQ8cTqcfRuLS6U9e9PvlD98WzGYWJLVmVvS8spZmP7T4x7TmMxtTBfZFZPHnkt+HCtVg4zRkLAU4hCdFmW8J44MwgWyiSbK5G9Q+KxYwSTZdUjrO8ZcKdjFNV1AOmPmc2Pw8fDLgkDwOxmMBrLbAIIZod25V7Tj6ZX54b93j/AFnHeOCGzKpk9MujHGWTeF5bZkQbjEsuiskmnPZIqsRgt9TJVsvwktFeySq7b1b0argjXgtYjKpOPWibR17xhuuKzUkMMqH2WUjBOr+F5OfA98nrSoiye9hztVEQbmZieoAYfzkpb+HVjP7uFVXDDRxllLWrTT+uVe58e5Sw508EnzKq9RbQGh3NDv3qvaRj6WWp/D8xFj6zwngwxzPpL17pdpbGQTPaoxzP5kc4lSKSEt2F12nCv9TmFDXRiE6hPECWhfsOIuaTVrSCWKRe51IIx8Vax8Q+PjoMBtm8QSCTbrz01068Xp0qZZUL+ZUjnsFQ4gTRFij1ZVxluZJYaOWxDsjBjYI+Kr7JoqE+qw5ejrzUz6FpvUxWA81Rq8kjsQscaDm7sQowb1zf+74orcPfi5JqKFyqxbwmmybkkgtoeZQ7SQDj5jD0ZvTiuQ6/eCzKwDehhzGK7HNMq399aywlX3JQuPorIbFkIPv0pAk+/ujV8W4mhnicdaukgDKe4+K3WYKpXLUH7Na7Hx/gZej5dD0fM5sfh4+GXEo3RzDKq8mYiNx2o5h0YdBHlQWsvlWdHQ9hIBU9x6PWkjDHC8oxfi/Nrqe0WUSt7eNfZo3f/jgHRzPnB8HkKH7yQl3GNNwRrUojMjeqgO5u4Y+5WpxLDGv7dq49yliQbo5q2VQPeMMg7UmaII3Qw1+rnXbvX0Oh8pD2MAcDkOLUlaF/8Vx/ZUbLiBfchZFx8Vax8Q+PjoMd5xGoXiyIv1k8npkmfV3Pax6CdfzTLJTQr/8AqhXDDV4sty5EkYIewSzSHf7A6ANHkyzMkdtrnt4cyLs9o4+Yw+LdrDwuEf2NmPbNF7jDGasL1RIIU1d4p/JmQKzKPL34ofnMWnpO3ylHewHT1aXHhWWyf4ysx8f4GXo+XQ9HzObH4ePhlx8ot9Hu9H90uFGrnLLmlW4n9ErdyYJ0Q1c4Hgm6T1YpHWT3cevOTSp/xULLhl5flXNw1eL+SAS9HuUsN1cZ6Msqj3tnSvU0Nm3JKhGnc2PSiyiv/VFj4q1j4h8fHQY97pb70d6VTjtDyiK0v+WXo7WkjL2/6ITj5jD4wOqnwcnjOPalLc+0adEI4NjX0mSPQt/HUYzGPSGCgsytOvHXRWkMeuyPTVvsEG5nlnqSRooHaST0ZqXhnWfK66VTJFy0lSXZvXZhDqro43KwPoIx8zmx+Hj4ZcfKLfR7vR/dLj79a5G0Mi8+0q2F1QmSnKYxMnc+m5DhW3pEKNWOsdndIyFz3thho/DvKFpr/wCOiN0e5Sxt3Ks1WQSLvHahI0Ydox9J7seXivY08vgWLJSKeP7jKdcZO/Gy/KILC7JLPhK+RLPtP1Sx66NzbECl5Z552EccSKObM7HQDEfmPmE5Nm4ydzTyMRj4q1j4h8fHQY97pA+rF5VWK9B7Yk0lPdJishmmy25BqIryRdbpoxWZF5lcHNIRd0/7Mnj7vV2a4sRmKXMrtjRJL0kZ5xx6AJCrY+Yw+L2m7Z+riI9OzXee4YPMkntOFGpJPIADC6x37S9Y43/IQ/z4rIEQekntZj2seZP2PGjqXMummbfItPjFI5a/3E1Dph8udIfxm0TEksU7rapwrAzM8DOhL7N3I4S1SVbFS3elnhlUPMCA6NixJFI4gn2bH1hZ154qywxcGKWAIjE2HTE1mo6QifLbFdNVhlZubuB0RMqvPPIuiopkKqCceGUP9fBPmOkaqynTtB6B25lk+yrM2nfC0WI/OioRnjW5R+7gRmxGNqRxRAKiKOwKBoOitNBFwBaWqIieO6efw2xcsVZInS/WMQTSCV3DHsbGW7DmteI9UNupyd3X78QZWxmuXSZVXHeZ7/BjxT1loZK7jQzF3A49r0HzI+ivPWiQWYbFh3j0nkRuQcYszV5hNLDOzug4Ej4hdEed0txSkAylV5KuPC6P+v01gPCssvICEsQFu46Oh5OuPoxpZkkTs4tPXjxv6Roy4z6m2TVo17XeS/wsUCkUUVSnZExp0mtGP6mPznc6GQ4e1SZYKtW7FNLKQkxJCKvir1G5ZXSJG744/wCvDeRVqo3+/PKeSju6z2DFhPqar+ipE3m+2fK/RqGXizWd7MCWRFE6SeeFcBtwGLLAzx1QxkeexsJUTTudWA6houJV5TWpysl2eP8AdIFj19dv0f0RQIXbTv0GDyv2o5G1SONX/wBjEq8gzc9OoYhXmzdReVjzdz2sxJP6PDmcsBijtymRImSxCmnDUhcfRl5Ld+yvbG1ieOOOD2gHxXGiRRL3nUszHmznmzEk/o8ihl1U7gdD6CNR/wBRf//EADURAAIBAgMEBgkEAwAAAAAAAAECAwARBBIhBRMxQRAzUWFysRQgIiMyNVJxkTBAc4FCUOH/2gAIAQIBAT8A/eYRLuXPBfOphEqhpV0PA21NOYyfdqQO8+okbyGyIWPdRBBIIsRoQf0d1Jk3mQ7v6raevHBLL1cZI7eVR7MY6yvYdi1FBFCuVF/s1tN7yRp2Lf8APTFG0jgKpbmQOysZhFCp6PCb31tc1hJJoncRxZ/qFSbyWZrp71jqorcy5xHu2z2va2tqeGWLV4yB2mkhlkGZI2YcLgdBhlCbwxnJa9+VQ/LW8D0uHnZcyxMV+1EEEgixoz4j0TIYfd5QM/dSQyyAskZYDS4FCGUoZBGcg50kbyHKiFj3VDDLFiIN4hW7jjW0/ji8JrDS4NLbyI5vqPtCo5I5BeNwR3dOMfPiZTyBy/jpgmeGQFDqdDWOnkgWMxkAkkG4rZpJlmJ4kXpPmJ/kasZiWw7gRqM7C5Y9lYeX0uBxKBfVTWzZLO8ROhFx9xU0JGKaIf5OLfZq2i4SKOBefktYUqMCC4ugDEjuuaXaMucFgu7v8I5CtpRKVSdRrfKe/sp/lo8C+dbN6h/GfIVLjncNGiqIiMoHO1ArgsIGCjOQP7Y1h8a8sqxzAFSdCBax5VtP44vCegEqbqSD2ioJZVjTO2Y2vrW/QAlja1MSzFjxJv0roy/cVtFHdI8ik2Y3sL1szrJfDSfMT/I1bT69PAPM1szqpPHUUhilST6WvTQh8RFOOAU/8rGS72dyD7I9kf1WFTeYEJ9Qcfkmlw0zSCPdsDexuNBW0WCwxx8yR+BT/LR4F862Z1D+M+Q6JlOJwamMXNgwH2rBwSNOjFCFU3JItwrafxxeE9EKbyRV5cTUsyRcdW7BUkrym7HTkOXqx7RmRQpVWtwJ41DiXgd3RVu3EGhOwn39hnuTblrU874hw7gAgW0qDFyYdSqKpBN9aRHkJCKWPGwrM+HwYMh94FsPvy6ExciQbgKuWxF+etLtKZVsVVj2mpZXmcvIbmji5DB6PlXJYC/PSoMXJh1KIqkE316IMXLBotin0mpNoTOVsAFBBsOdu2p8Q+IKlwBYW06EkMYbL8R50STqf04J3gLFLXIsbipsRLORvG0HADQf6T//xAAoEQACAgEEAQIGAwAAAAAAAAABAgARMQMQEiEzIIEiMDJBUXFAUGH/2gAIAQMBAT8A/mOeqi8sCC/v6CQMn5Vi6vv1lgMmHV/AhYk2TNIdE7kgDsxH7PIxwpAs1BQUd9TktXfUDA4MLAZO3IXV9xvL7icl/I24rzvl3eIWAyZyF1fcJAyYzAq1GaWDHDnBhBGRugpRuyhh3NNQxNzVwsPi9oich3iMODCpqjoGK3wAzSFktH8nWeodIV/s0ibKweX3mrkQaYFE5n1vGQAErNLB3YCzU4n7egzSIBNzVwIfF7TS+k/uauRCLWpypSsQUojmtS/1Cy1dzSFsTB5feauRsp4ObjsOJF9maWDsxoQKTAoHpOkpjIGABnEceMVQooRkDZlgDsygz9YvYoC3KHSW4AFFCcBy5X3GQNnZkDfuDTURVC3WxAOfmMobMChcD+k//9k=)\n","#Principles of Real-Time Analytics Course"]},{"cell_type":"markdown","metadata":{"id":"yFW2t1QXpOQh"},"source":["#Source Data and Ingestion\n","The first step towards analyzing data is to load the data. Although this sounds like a trivial thing (doing some inserts is easy), it is actually a critical part of working with data at scale. The focus of this section is to understand the different data formats and how to ingest data properly to an analytics database.\n","\n","You will see how even the source of your data impacts on how you send it to your data systems: a bank might send information once a day, embedded software tracking a bus in NYC sends it every few seconds.\n"]},{"cell_type":"markdown","metadata":{"id":"n-6TtD2npW_m"},"source":["##Important Metrics\n","Key metrics for ingesting data are:\n"," - the speed of ingestion (**rate**)\n"," - how much data is being ingested (**size**).\n","\n","That’s it, how often the source pushes data and how large is the data it pushes. For example, a bus fleet management software could be sending 100-200 rows per second, 100 bytes each, another system could be sending 1 GB per hour; those cases need to be treated in different ways.\n","\n","The key as always is to try to process data in larger batches. It is usually faster to process 1M rows all at once than 10 rows in 100 000 different batches. The reason is clear, devices work much faster when working on large portions of data for reading and that’s also true for writing.\n","\n","That’s not the only reason, real-time analytics databases generally use algorithms to deal with data in the background. We will see that later.\n","\n","So, for example, a high-rate low-size use case, such as fleet management, usually needs to buffer data for a while and then insert the data. If you want a really low-latency value you may want to use any streaming solution for analytics. A system that sends 1 GB each hour is fine, there’s no need for buffering.  \n"]},{"cell_type":"markdown","metadata":{"id":"vybCkZHBpqP0"},"source":["##Types of Data Sources\n","People usually have a series of data sources and the problem is how to organize them in order to work with them. When you are dealing with large volumes of data the probability that you get data that is not as you expected is really high.\n","\n","Fundamentally there are three types of data source:\n"," - Streams or logs - event-driven data generated by an application, usually you use Kafka or similar when the data rate and volume is really high, for example, events coming from tracking user activity\n"," - Transactional data where you take a snapshot at a moment in time - web applications, for example banks, e-commerce and most web applications. You can add, change, delete and update data.\n"," - Raw data stored in files (for example, CSV or JSON). This can be considered to be a persistent log.\n"]},{"cell_type":"markdown","metadata":{"id":"oeLsUduvp73r"},"source":["##Data Formats\n","It’s not only the data format, it’s also the way the data is stored. For example, you can have a single CSV file, the same data but in multiple CSV files, a large compressed zip file, the same but gzipped, multiple gzip files…\n","\n","There are many different types of data files: \n","- format: CSV, JSON, Parquet, SQLite, Avro, Arrow…\n","- storage: XML, JSON, JSONL, CSV, TSV, CSV.zip, CSV.gz. \n","\n","In general, the important thing to remember is that working with large files, especially if they are compressed in zip is a huge pain. It is always preferable to divide your huge 5 GB CSV into 5 files of 1 GB, or even into 20 files and to compress with something streamable like gz. Then you can process them in batches; each file can be a partition, for example, data for January. Storing them compressed means saving money on storage and sending them through the network is faster (you should always compress data that goes through the network). That’s the way Parquet and other similar systems do this. \n","\n","Good practice here is:\n","- work with medium sized files, not a single large file nor many small files\n","- use compression that allows parallelization\n","- use open and standard formats\n","- use binary files when you can\n","partition data in a way that fits the business.\n","\n","Note that although CSV and JSON are the most popular data formats to move data between data systems they have the problem of non-deterministic data types. If the data is stored in a database the format will obviously be the internal one but the most common way to move that data out is usually to transform to CSV or JSON.\n"]},{"cell_type":"markdown","metadata":{"id":"eseYBOBTqRIH"},"source":["##Structured vs. Unstructured Data\n","When running real-time analytics, unstructured data generally means slow. Structured and homogeneous data is nearly always much, much faster than unstructured data. Therefore, we will focus here on structured data.  If we have unstructured data, such as events in JSON, what we generally do is load the unstructured data, keep it in a table and then create views where we extract the columns from the JSON that we need to work with. Thereby converting unstructured data into structured data.\n","\n","If you are looking for something that is very general for unstructured data then you need a technology like MongoDB or Elastic; this is not the right place for that. \n"]},{"cell_type":"markdown","metadata":{"id":"v6IXgBVKqZud"},"source":["##Events Tables and Dimension Tables\n","When dealing with large datasets there is a typical pattern that emerges every time with two main groups in the data: events tables and dimension tables. This is not something new, it’s usually called the [star schema](https://en.wikipedia.org/wiki/Star_schema). We don’t actually care about the names, we really do care about things working well no matter what the name is.\n","\n","Events tables (such as transactions in bank accounts) grow very fast while dimension tables (such as clients) grow much more slowly and are generally way smaller than events tables. In the bus fleet example, the bus positions will be events, while the route and driver properties will be dimensions.\n","\n","It is important to keep in mind what sort of table we are dealing with because this will condition decisions about storing data and preparing queries. From now on we will talk about\n"," - dimension tables: small (less than a few million items) which often change.\n"," - event tables: large and usually append only.\n"]},{"cell_type":"markdown","metadata":{"id":"Fg5uvRkJq5o8"},"source":["##Ingestion Patterns\n","When you have historic data stored somewhere then the ingestion pattern is clear. Push the CSV, JSON or whatever to the database and that’s it. The only thing that you need to take into account is ensuring that while the database is ingesting data it’s not using those resources to run queries. We will see how to try to fix this issue when we talk about cluster sizing.\n","\n","If you are loading data from some event stream (it doesn’t matter if it is Kafka or your own application) it’s easy: just capture the events you want to use, buffer them and dump them into a table using an insert or a copy in the same way you’d do in your transactional database. The main difference to a transactional database is that you should try to insert the data in batches rather than one by one.\n","\n","The problem comes when you have a more transactional database and the amount of data becomes unmanageable so you want to move it to an analytics database. This is a problem because you need to sync data between two systems that usually work in a different way.\n","\n","There are different patterns to load data from PostgreSQL, MySQL, Db2 tables or MongoDB, Elastic collections:\n","\n","- Incremental: send new data every X minutes/seconds to the analytics database. This is the easiest way but sometimes schemas on original databases save a snapshot, not the history (i.e, updates and deletes are executed not only appends). \n","\n","- Upsert: you send all the data for the last X hours every Y minutes/seconds. So new data will be inserted and old data will be updated. This case is useful when you have a table and you update data, that is what most of the regular web applications do. The problem with this approach is that analytics databases don’t usually support upserts or, at least, not in real-time.\n","\n","- Full replace: this is the brute force approach, dump all the data and load it on the analytics database. It’s usually a non-viable option for reasons related to data size. However, this works pretty well for dimension tables which are usually small (less than a couple of hundred MB most of the time).\n","\n","The reality is that most of the sync use cases are easily solved with a cron job, Airflow task or, if you are unlucky, a control-m job."]},{"cell_type":"markdown","metadata":{"id":"WDDfidTjGAml"},"source":["## [Course Outline](https://colab.research.google.com/github/AlisonJD/RTACourse/blob/main/01_Getting_Started.ipynb)\n","\n","\n","|Previous Notebook       |Next Notebook|\n","| :----------------- |:-------------|\n","|3. [Use the Right Database](https://colab.research.google.com/github/AlisonJD/RTACourse/blob/main/03_Use_the_Right_Database.ipynb)|5. [Data Storage Inside the Analytics Database](https://colab.research.google.com/github/AlisonJD/RTACourse/blob/main/05_Data_Storage_Inside_the_Analytics_Database.ipynb)|"]}]}