<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Harsh Chaudhary on Medium]]></title>
        <description><![CDATA[Stories by Harsh Chaudhary on Medium]]></description>
        <link>https://medium.com/@chaudharyharsh575?source=rss-425dd0f8a083------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*r_FRPwSzxqg6gt9BrxH52Q.jpeg</url>
            <title>Stories by Harsh Chaudhary on Medium</title>
            <link>https://medium.com/@chaudharyharsh575?source=rss-425dd0f8a083------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 20:27:31 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@chaudharyharsh575/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[OLTP vs OLAP Systems]]></title>
            <link>https://medium.com/@chaudharyharsh575/oltp-vs-olap-systems-98c5cc619458?source=rss-425dd0f8a083------2</link>
            <guid isPermaLink="false">https://medium.com/p/98c5cc619458</guid>
            <category><![CDATA[olap]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[oltp-vs-olap]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[oltp]]></category>
            <dc:creator><![CDATA[Harsh Chaudhary]]></dc:creator>
            <pubDate>Sat, 24 Jun 2023 08:09:10 GMT</pubDate>
            <atom:updated>2023-06-24T08:09:10.686Z</atom:updated>
            <content:encoded><![CDATA[<p>Let’s say you built a food delivery app for your restaurant and used a database in the backend to store user information and order details. Your app grew to a couple thousand users because your mom cooks good food and the delivery boy is a young 20-year-olds filled with fire. You decided to really get into the business and make some money, so you run a SQL query against your database to find out what items the users kinda like so that you can make those specific recipes better 2 minutes passed and your query is still executing and users are now complaining that it is taking them forever to place their order.</p><p>This happened because you are trying to run analytical queries on an OLTP system (your operational database), but you should’ve used an OLAP system for it (considering that you are required to frequently analyze your data).</p><p>This is where OLAP and OLTP systems come into the picture.</p><h4>Online Transaction processing (OLTP)</h4><p>These systems are responsible for handling large volumes of database transactions (i.e. new insertions, updates, and deletions) in a quick and efficient manner and should have ACID properties. In the example above, you used an OLTP database to store orders in real-time.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/534/1*zmpncqcWJugr6cdxhKh-0Q.png" /></figure><p>The performance of an OLTP system is measured in terms of the number of transactions per second.</p><p>Row-oriented databases are well-suited for OLTP systems. Suppose that a customer ordered a pair of socks from Amazon. We’d need to create a new entry in our Orders table. Assuming that we were using a hard drive for secondary storage, because the data is stored in a row-oriented database, we could consecutively write every column of the row (i.e. order number, quantity, etc…) to disk. Thus, avoiding lifting the head to write the data in another sector, which would be the case in a column-oriented database (i.e. all the data for each column is stored consecutively in a series so writing data in different columns would result in having to go to different memory locations for each column series whereas, in a row based database, the data can be written consecutively).</p><p>OLTP systems should be highly available due to the fact that they insert or modify data quite frequently — this is expected as it is a transactional system. Additionally, frequent backups may also be required to maintain data integrity.</p><p>OLTP systems must be as responsive as possible and the response time should not exceed a few milliseconds. The queries executed on OLTP systems are fairly simple (insert, update, delete, etc.) and also space-efficient and thus not very hard to achieve low processing times.</p><h4>Online Analytical Processing (OLAP):</h4><p>Online Analytical Processing (or, OLAP, for short) is characterized by a relatively low volume of read-only transactions.</p><p>We quantify an OLAP system’s performance by query response times.</p><p>These systems are used to run analytical queries on the data. While designing such databases, we create a schema that supports the business function i.e. while creating the tables we will choose the specific columns that are required for the analysis and will create the schema in such a way that removes the need of having to perform complex joins and make the future analytical queries as easy and efficient as possible. The goal here is to make the job of the analysts easy by making the data relevant to their use case.</p><p>Columnar databases are well-suited for OLAP systems. Suppose an analyst wanted to determine the total quantity of products sold in the month of May. Unlike in a row-oriented database, which would require us to pass over the order records in their entirety, the needle of the hard drive could quickly scan all the timestamps (since they’re stored on disk consecutively) and select the ones with a month equal to May. Similarly, the needle could strictly read the values in the quantity column adding them up as it goes.</p><h3>Joining forces for ETL</h3><p>As an example, let’s consider the Extract-Transform-Load (ETL) process which is very common in the context of Data Engineering. The ETL tool will ingest (extract) data from various data sources that are typically OLTP systems, perform some processing over the extracted data (transformation) and send it to a destination system (load) that is typically an OLAP system (say a Data Warehouse) where you can finally perform your analytical queries and figure out how you can optimize your restaurant’s menu.</p><h3>Conclusion</h3><p>Let’s bring it all together, we discussed 4 core differences between OLTP &amp; OLAP systems:</p><h3>Queries</h3><ul><li>OLTP: Simple CRUD</li><li>OLAP: Complex aggregations</li></ul><h3>Performance</h3><ul><li>OLTP: transactions/unit of time</li><li>OLAP: unit of time/number of records</li></ul><h3>Orientation</h3><ul><li>OLTP: Row oriented</li><li>OLAP: Column-oriented</li></ul><h3>Schema</h3><ul><li>OLTP: Entity model in 3NF</li><li>OLAP: Star schema</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=98c5cc619458" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Modeling E-Commerce data (Python + Postgres)]]></title>
            <link>https://medium.com/@chaudharyharsh575/modeling-e-commerce-data-python-postgres-fd51ef413124?source=rss-425dd0f8a083------2</link>
            <guid isPermaLink="false">https://medium.com/p/fd51ef413124</guid>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[data-modeling]]></category>
            <category><![CDATA[etl]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Harsh Chaudhary]]></dc:creator>
            <pubDate>Mon, 19 Jun 2023 10:16:41 GMT</pubDate>
            <atom:updated>2023-06-19T10:16:41.620Z</atom:updated>
            <content:encoded><![CDATA[<p>.</p><p><a href="https://twitter.com/hvrshchaudhary">Follow me on Twitter</a>, <a href="https://www.linkedin.com/in/harsh-chaudhary-939321230/">LinkedIn</a></p><p>.</p><p>Link to dataset:</p><p>In this blog, I will show how I built a data model using Python &amp; <a href="https://app.diagrams.net/">Draw.io</a> and stored it in a Postgres database.</p><h4>Step 1: Getting the data</h4><p>I downloaded this dataset from <a href="https://www.kaggle.com/datasets/olistbr/brazilian-ecommerce?resource=download&amp;select=olist_sellers_dataset.csv">Kaggle</a>.</p><h4>Step 2: Loading the data into a Jupyter notebook</h4><p>read_csv() is a pandas library function that reads CSV files. To use it, you first have to import the pandas library (Here I imported it as pd, that is why pd.read_csv())</p><pre>customers = pd.read_csv(&quot;./olist_customers_dataset.csv&quot;)<br>order_items = pd.read_csv(&quot;./olist_order_items_dataset.csv&quot;)<br>order_payments = pd.read_csv(&quot;./olist_order_payments_dataset.csv&quot;)<br>orders = pd.read_csv(&quot;./olist_orders_dataset.csv&quot;)<br>products = pd.read_csv(&quot;./olist_products_dataset.csv&quot;)<br>sellers = pd.read_csv(&quot;./olist_sellers_dataset.csv&quot;)</pre><h4>Step 3: Getting the schema of our tables</h4><p>We use the dataFrame.info() function to get the schema.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/391/1*zIIiPooEZxd2w84qXKPs7Q.png" /></figure><p>Using the schema, we will make a diagram of all the tables. I used Draw.io for this purpose.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/866/1*uDcbBdzCQmZJZOgadHtQ3g.png" /><figcaption>Using this diagram we can easily understand what all information exists and how the tables are connected to each other.</figcaption></figure><h4>Step 4: Defining the use case of the data</h4><p>To know what data to put into the tables of our data model, we need to know what the data is going to be used for. This part is usually done by having a conversation with the users of the data (analysts, managers, scientists, etc). Once we know what will be the most relevant data for our business use case, then we can create a data model for that required data.</p><p>Let’s create a data model that supports the following analysis:</p><ul><li>What places contributed the most/least to product sales?</li><li>Which seller sold the most/least products?</li><li>Best and worst performing products.</li><li>Date for everything.</li></ul><h4>Step 5: Creating a star schema</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/623/1*w3TKSZI-kfDQrycGAQ_l4Q.png" /><figcaption>This will be the schema for our data model according to the given use case.</figcaption></figure><h4>Step 6: Extracting the required tables from the existing tables</h4><p>Let’s start with the fact table, from the default schema we can see that the columns that we need are distributed among different tables, so first we need to join those tables:</p><pre>help1 = pd.merge(order_payments, orders, on = &#39;order_id&#39;)<br>help2 = pd.merge(help1, order_items, on = &quot;order_id&quot;)</pre><p>help2 table will have all the columns that we require, so now we will simply extract those columns and put them into our fact table.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/902/1*xHV3E-L50_eKAaaNizeR0w.png" /><figcaption>Similarly we will get all our required tables</figcaption></figure><h4>Step 7: Extracting month and year from the timestamp</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/421/1*mbbOx7SsnP5hxxYdvi-pLQ.png" /><figcaption>This is the information about date and time that we have by default.</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/641/1*h4PtlWEt8_Rj7SoDZ3LNjg.png" /></figure><p>in the code above, the .to_datetime() function converts our column to a DateTime object, we can then easily use this column to extract month and year from it.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/435/1*Tsj3W27BxIVIUq-q0sLbHg.png" /></figure><h4>Step 8: Creating a database and connecting to it</h4><p>To communicate with a Postgres database using Python, we use the psycopg2 library.</p><pre>import psycopg2 # to connect to our database server</pre><p>Note that If you don’t have this library installed in your system, you first need to install it using</p><pre>pip install psycopg2 # you can type this in your jupyter notebook</pre><p>We will use the following code to create our database:</p><pre>try:<br>    conn = psycopg2.connect(&quot;host = localhost dbname = postgres user = postgres password = root150&quot;) #connecting to the default database<br>except psycopg2.Error as e :<br>    print(&quot;error, couldnt connect to database&quot;)   <br>    print(e)<br>conn.set_session(autocommit = True) # any changes we make from here will be updated automatically in the database<br>curr = conn.cursor()<br> <br>curr.execute(&quot;Create Database eCommerce&quot;)   # creating our database in postgres<br>conn.close()   # closing connection to the default database</pre><p>We will use the following code to connect to our newly created database:</p><pre>try:<br>    conn = psycopg2.connect(&quot;host = localhost dbname = ecommerce user = postgres password = root150&quot;) # connecting to our new database<br>except psycopg2.Error as e :<br>    print(&quot;error, couldnt connect to database&quot;)<br>    print(e)<br>conn.set_session(autocommit = True)<br>curr = conn.cursor() # creating a cursor to be able to communicate to our database</pre><h4>Step 9: Creating the tables inside the database</h4><p>For this, we need to write the SQL query to create the table and execute it using curr.execute()</p><p>Here’s how it is done:</p><pre>fact_create = (&quot;&quot;&quot;Create table if not exists fact_transactions(<br>order_id varchar, <br>customer_id varchar,<br>product_id varchar,<br>seller_id varchar,<br>payment_value float)&quot;&quot;&quot;) # storing our SQL query to create a table inside a variable<br>curr.execute(fact_create)</pre><p>Once done, we will have all our tables inside our postgres database:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/398/1*phjoosBoSqkE9zkxa7zmkw.png" /></figure><h4>Step 10: Inserting data into database tables:</h4><p>For this, we will write a SQL query to insert a record into the table and run the query for each row in the table. Here’s how it’s done:</p><pre>fact_insert = (&quot;&quot;&quot;insert into fact_transactions(<br>order_id , <br>customer_id ,<br>product_id ,<br>seller_id ,<br>payment_value)<br>values (%s, %s, %s, %s, %s)&quot;&quot;&quot;) # storing a SQL query to insert data into a table inside a variable<br><br>for i, row in fact_transactions.iterrows(): # iterrows returns index and information stored in a row of a data frame<br>    curr.execute(fact_insert, list(row)) # So we are reading each row from reviews dataframe and inserting it into reviews<br>                                            # table </pre><p>Once done, we will have all our records inserted in our database table:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/470/1*JrniBrIL0dqWgpm9SlQlzA.png" /><figcaption><strong>And it’s done.</strong></figcaption></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=fd51ef413124" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Add RDS as a data source — AWS Glue Studio]]></title>
            <link>https://medium.com/@chaudharyharsh575/add-rds-as-a-data-source-aws-glue-studio-8aac230b11c5?source=rss-425dd0f8a083------2</link>
            <guid isPermaLink="false">https://medium.com/p/8aac230b11c5</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[aws-glue]]></category>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[rds]]></category>
            <category><![CDATA[aws-glue-studio]]></category>
            <dc:creator><![CDATA[Harsh Chaudhary]]></dc:creator>
            <pubDate>Mon, 12 Jun 2023 15:36:20 GMT</pubDate>
            <atom:updated>2023-06-12T15:36:20.130Z</atom:updated>
            <content:encoded><![CDATA[<h3>Add RDS as a data source — AWS Glue Studio</h3><p>.</p><p><a href="https://twitter.com/hvrshchaudhary">Follow me on Twitter</a>, <a href="https://www.linkedin.com/in/harsh-chaudhary-939321230/">LinkedIn</a></p><p>.</p><p>For this tutorial, I have used the <a href="https://www.postgresqltutorial.com/postgresql-getting-started/postgresql-sample-database/">dvdrental</a> dataset. I started an RDS instance for Postgres, <a href="https://www.youtube.com/watch?v=0GpQJM7w6M8&amp;t=265s">connected</a> to it via PGadmin in my local machine, and downloaded the data in it.</p><p><strong>Make sure that you use Postgres version less than 14.</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/640/1*Duf7gPlp0I_rvF-93XSXrA.png" /><figcaption>Glue Studio cannot find a connection right now even though my RDS instance is up and running.</figcaption></figure><h4><strong>Step 1- Adding a database in Glue:</strong></h4><p>Go to glue catalog &gt; Databases &gt; Add database &gt; enter name, etc &gt; create.</p><h4><strong>Step 2- Creating a connection:</strong></h4><p>Go to connections &gt; Enter name &gt; Connection type: Amazon RDS &gt; Database engine: Postgresql</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/530/1*nOd-FtwGuczryFsuaxd0Ag.png" /><figcaption>Select your instance and fill out all the information and create the connection.</figcaption></figure><p>Now create a IAM role with AWSGlueServiceRole permission attached to it. After that, test the connection (Use the recently created IAM role for testing) to make sure it works.</p><p>If you are using a VPC (which you probably are), then you need to add a VPC endpoint to the S3 service. You might also need to do some minor debugging if the connection still not works (but you will eventually find the way).</p><h4>Step 3- Creating a crawler:</h4><p>Our crawler will pull the tables out of the database, create a catalog, and put the data into our glue database.</p><p>Go to Crawlers &gt; Create &gt; Name it &gt;</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/535/1*xPGBpLhTCB5egIoidTgUgA.png" /><figcaption>When asked to select a data source, select JDBC, choose your rds instance, in path: dvdrental is the name of my data base and all the tables are inside the public schema. and the % symbol means that pull all the tables from the specified location.</figcaption></figure><p>Then select the same IAM role that we just created &gt; Then choose the database you created in step 1 &gt; Now create and run the crawler.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/659/1*hVrVLhbsWROjTn7qFeWQlQ.png" /><figcaption>Once the crawler has run successfully, we are able to see that all the tables have been added to the catelog and database.</figcaption></figure><p>And we are done!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*GfEcA7mJTkc4ArsbRH2e1w.png" /><figcaption>Now if we go back to the glue studio, choose relational DB, choose our database,</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*muhsN_ybWuQuU-6pzkn_Tw.png" /><figcaption>Then we can choose from all the different tables from our database, and do our ETL.</figcaption></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8aac230b11c5" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[API data to S3 (Python + Airflow) — A high level overview]]></title>
            <link>https://medium.com/@chaudharyharsh575/building-an-end-to-end-data-pipeline-part-1-python-airflow-a-high-level-overview-820243bff9a3?source=rss-425dd0f8a083------2</link>
            <guid isPermaLink="false">https://medium.com/p/820243bff9a3</guid>
            <category><![CDATA[api]]></category>
            <category><![CDATA[s3]]></category>
            <category><![CDATA[airflow]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[python]]></category>
            <dc:creator><![CDATA[Harsh Chaudhary]]></dc:creator>
            <pubDate>Thu, 08 Jun 2023 15:15:11 GMT</pubDate>
            <atom:updated>2023-06-25T13:38:11.678Z</atom:updated>
            <content:encoded><![CDATA[<h3><strong>API data to S3(Python + Airflow) — A high level overview</strong></h3><p>.</p><p><a href="https://twitter.com/hvrshchaudhary">Follow me on Twitter</a>, <a href="https://www.linkedin.com/in/harsh-chaudhary-939321230/">LinkedIn</a></p><p>.</p><p>I have not explained any processes in this blog (ex- how to set up EC2 etc.), rather my aim is to show what all things need to be done in the process. Check out <a href="https://medium.com/towardsdev/building-an-end-to-end-data-pipeline-with-airflow-and-python-3bf60fb6986"><strong>Sai Parvathaneni’s blog</strong></a> where he walks you through this whole process. You can check out <a href="https://marclamberti.com/blog/airflow-dag-creating-your-first-dag-in-5-minutes/"><strong>this blog</strong></a> to get an idea of how to code your dag, <a href="https://marclamberti.com/blog/airflow-pythonoperator/"><strong>this blog</strong></a> will tell you how to use Python operators and <a href="https://marclamberti.com/blog/airflow-xcom/"><strong>this blog</strong></a> explains the xcom command.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/630/0*PD6oWdfcs0guTWUt.png" /><figcaption>End-to-End Data Pipeline with Airflow, Python, AWS EC2 and S3</figcaption></figure><p><strong>For this tutorial, we’ll use the </strong><a href="https://jsonplaceholder.typicode.com/"><strong>JSONPlaceholder API</strong></a><strong>, a free and open-source API that provides placeholder data in JSON format. The API can be accessed at </strong><a href="https://jsonplaceholder.typicode.com/"><strong>https://jsonplaceholder.typicode.com/</strong></a><strong>.</strong></p><h3>Steps:</h3><h4>1. Creating a Python script to extract &amp; transform the data:</h4><p>The code for this step can be found in this GitHub repository in the files <a href="https://github.com/hvrshchaudhary/Airflow_data_pipeline/blob/main/extract_data.py"><strong>extract_data.py</strong></a> &amp; <a href="https://github.com/hvrshchaudhary/Airflow_data_pipeline/blob/main/transform_data.py"><strong>transform_data.py</strong></a></p><h4>2. Setting up the EC2 instance:</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/272/1*olqDavThCEPj3CNQYMdwWQ.png" /><figcaption>Then I set up an ec2 instance with the following configuration, I also used a key pair.</figcaption></figure><p>After this, I added an inbound rule in the ec2 to allow the Airflow web interface (which runs on port 8080) to be accessible from outside the EC2 instance (my local machine).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Pt0yoZJrJFlFi6RlwaIraQ.png" /><figcaption>The custom TCP rule on port 8080 is responsible for this task.</figcaption></figure><h4>3. Creating an S3 bucket:</h4><p>Now we need to create an S3 bucket that will store the fetched data. In my bucket, I also enabled public access.</p><h4>4. Creating an IAM role for our EC2 instance:</h4><p>Now we need to create an IAM role to give the airflow server running in the ec2 instance access to our S3 bucket.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*sDWr7kFC6OJjNb9gocYr-Q.png" /><figcaption>These are the permissions that our EC2 instance will require</figcaption></figure><p>Then I attached this role to the instance.</p><p>Now, our EC2 instance has full access to the Amazon S3 bucket using the IAM role we created. This allows our instance to interact with the S3 bucket without the need to store the AWS access keys on the instance.</p><h4>5. Installing Python and Airflow on the EC2 instance:</h4><p>To do this, we first need to SSH into our instance using the key pair we created while creating our instance (You can also use ec2 instance connect to make things even easier).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/955/1*VPu6ToNyTFFNGIXD-DRYhw.png" /></figure><p>Now we will install Python, Airflow, and also ‘<em>Pandas’</em>, ‘boto3’, and ‘<em>s3fs’</em> libraries. The following commands do it all and also initialize the database, create a user and start the airflow webserver and scheduler.</p><pre>//Install Python, Pip, Pandas and s3fs<br>sudo yum install python3 -y<br>sudo yum install python3-pip -y<br>sudo yum groupinstall &quot;Development Tools&quot; -y<br>pip3 install pandas --user<br>pip3 install s3fs --user<br>python3 -m pip install boto3<br><br>//Install Airflow, intilize Airflow database, create new user for Airflow<br>pip3 install &quot;apache-airflow[celery,redis,postgres]&quot; --user<br>airflow db init<br>airflow users create \<br>    --username your_username \<br>    --firstname your_firstname \<br>    --lastname your_lastname \<br>    --role Admin \<br>    --email your_email@example.com<br><br>//Start Airflow webserver and scheduler<br>airflow webserver --port 8080<br>airflow scheduler</pre><h4>6. Creating the Airflow DAG:</h4><p>We will use the <a href="https://github.com/hvrshchaudhary/Airflow_data_pipeline/blob/main/data_pipeline_dag.py"><strong>data_pipeline_dag.py</strong></a> file to create the dag.</p><p>At this moment, all the required files are in my local computer, I need to transfer these files to the dags folder in airflow that is running on the ec2 instance.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/964/1*R6Rbd9BHyUQnvWSftYtHCA.png" /><figcaption>To do so, I used the scp command.</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/340/1*MFhMXvFARmiRP3jRfssp8A.png" /><figcaption>And it got the job done.</figcaption></figure><h4>7. Setting up AWS credentials on the EC2 instance</h4><p>To allow our EC2 instance to interact with S3, we need to configure the AWS CLI with our access key and secret key.</p><pre>aws configure</pre><p>Type this command and enter your AWS access key, secret key, default region, and default output format when prompted.</p><h4>8. Monitoring the pipeline.</h4><p>First, we will start the Airflow web server and scheduler on the EC2 instance using the last 2 lines of code in step 5.</p><p>Now we will navigate to the Airflow web UI on the EC2 instance (<a href="http://your-ec2-instance-public-ip:8080/">http://your-ec2-instance-public-ip:8080</a>) and find the ‘data_pipeline’ DAG. Turn on the DAG and Trigger the DAG manually. Click on the DAG to view its status and progress.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*TheSn4xUhPSerMOBrvAmyQ.png" /><figcaption>I did fail a couple of times before getting it to run.</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*dDax-FqGf6nCzBV5NwN0qg.png" /><figcaption>And once the dag is completed, we will finally have the data in our s3 bucket.</figcaption></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=820243bff9a3" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[ETL using AWS Lambda, S3 & Glue Explained]]></title>
            <link>https://medium.com/@chaudharyharsh575/etl-using-aws-lambda-s3-glue-explained-5bbba4368d23?source=rss-425dd0f8a083------2</link>
            <guid isPermaLink="false">https://medium.com/p/5bbba4368d23</guid>
            <category><![CDATA[etl]]></category>
            <category><![CDATA[aws]]></category>
            <category><![CDATA[etl-pipeline]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Harsh Chaudhary]]></dc:creator>
            <pubDate>Mon, 29 May 2023 01:48:00 GMT</pubDate>
            <atom:updated>2023-05-29T01:48:00.527Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/636/1*lAXNd1CvBMUTRILZf0PHEw.png" /></figure><h4>Prerequisites:</h4><ul><li>AWS IAM account (Don’t know what’s an IAM account &amp; how to set up one? Google it. Give Administrator access to your IAM account).</li><li>AWS CLI: <a href="https://aws.amazon.com/cli/">Download</a>. After downloading, configure it by entering access keys and region for your account.</li></ul><h4>Downloading data and uploading it to S3</h4><p><a href="https://www.kaggle.com/datasets/datasnaek/youtube-new?resource=download">Link to Dataset</a>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ZpmQIPL4-fp-ryjDK0TLeQ.png" /><figcaption>I’ve created an S3 bucket with blocked public access and default encryption enabled.</figcaption></figure><p><strong>To upload data to S3 using aws cli:</strong></p><ul><li>Navigate to the folder that contains the data via cmd.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/465/1*qodBpWPs6hLTmC3dk1TJkQ.png" /><figcaption>This is that folder for me.</figcaption></figure><ul><li>Use the following command to copy all the json files:</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/969/1*eAmKiAhVYphbKX1iCunuig.png" /></figure><p>Command:</p><pre>aws s3 cp . s3://data-lake-project-youtube-analysis/youtube/raw_statistics_reference_data/ --recursive --exclude &quot;*&quot; --include &quot;*.json&quot;</pre><p>where ‘data-lake-project-youtube-analysis’, is the name of my S3 bucket, /youtube/raw_sta…. is the exact folder in which I want these JSON files to be copied.</p><p>so use the name of your bucket instead.</p><ul><li>Use the following commands to copy the video files:</li></ul><pre>aws s3 cp CAvideos.csv s3://data-lake-project-youtube-analysis/youtube/raw_statistics/region=ca/<br>aws s3 cp DEvideos.csv s3://data-lake-project-youtube-analysis/youtube/raw_statistics/region=de/<br>aws s3 cp FRvideos.csv s3://data-lake-project-youtube-analysis/youtube/raw_statistics/region=fr/<br>aws s3 cp GBvideos.csv s3://data-lake-project-youtube-analysis/youtube/raw_statistics/region=gb/<br>aws s3 cp INvideos.csv s3://data-lake-project-youtube-analysis/youtube/raw_statistics/region=in/<br>aws s3 cp JPvideos.csv s3://data-lake-project-youtube-analysis/youtube/raw_statistics/region=jp/<br>aws s3 cp KRvideos.csv s3://data-lake-project-youtube-analysis/youtube/raw_statistics/region=kr/<br>aws s3 cp MXvideos.csv s3://data-lake-project-youtube-analysis/youtube/raw_statistics/region=mx/<br>aws s3 cp RUvideos.csv s3://data-lake-project-youtube-analysis/youtube/raw_statistics/region=ru/<br>aws s3 cp USvideos.csv s3://data-lake-project-youtube-analysis/youtube/raw_statistics/region=us/</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gv_qG6MfOc28Xo4BPdFmLQ.png" /><figcaption>We now have all the required data loaded into these folders.</figcaption></figure><p>Now we need to create a Glue catalog for this data that stores the metadata i.e. data about our data like the columns that it has, datatypes of columns etc.</p><p>To create a glue crawler, go to Aws Glue -&gt; crawlers -&gt; create crawler -&gt; choose name -&gt; choose data sources -&gt; add data source -&gt; S3 -&gt; Browse S3 -&gt; Navigate to the parent folder of the JSON files and select it for now.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*97mkXmPpbvpI7XirfMI-Kw.png" /></figure><p>click next, and now we need to choose an IAM role, so we need to create it.</p><p>So go to IAM console -&gt; Roles -&gt; create role -&gt; Aws service -&gt; choose Glue as the service -&gt; next -&gt; search and select glueServiceRole and S3FullAccess -&gt; next -&gt; give name -&gt; create role.</p><p>now go back to the glue dashboard and attach that role.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/917/1*Lx01AqJzwvObDbQq56utGQ.png" /></figure><p>Now create a new database for your raw data and choose it:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/982/1*5disb020PKBFBwMqIKGdRw.png" /><figcaption>Leave everything else as it is -&gt; click next and create.</figcaption></figure><p>You will figure everything out by yourself as you follow along.</p><p>Now run the crawler.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mxRVMny2V5wdJMQ8uiT-Sw.png" /><figcaption>Our crawler is now ready.</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*obCmr9Pg3r9sBcB9D7cBqQ.png" /><figcaption>This is the metadata of our tables. Let’s understand the structure of our data.</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*VXLSnuM3YxmU9pJ5erStGQ.png" /><figcaption>These are the three keys that we have in our JSON data.</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*bAuoH5XyCDPda2nCVPNTGg.png" /><figcaption>Where items is an array of objects that holds the data that we require.</figcaption></figure><p>Let’s try querying the data we just crawled using Athena.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4DJUiBLnJ4Xt-aXAxXf_nw.png" /><figcaption>Inside the Glue console: Go to tables -&gt; Choose your table -&gt; Click on Actions -&gt; Click view data</figcaption></figure><p>Note that in order to use Athena, we need to connect it to an S3 bucket where it can store the query results and whatnot. So we will create an athena_output folder inside our S3 bucker and connect it here. Click on edit settings in the Athena console where it is asking to connect to S3 and choose the folder you created in s3 for this purpose, and click on save.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*BKrQDfbNN7J0qykpIhpepQ.png" /></figure><p>Now if we try to run the query, we get the following error:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qGm0UALEY3xNpCwBV9a4Wg.png" /><figcaption>We get this error as our raw data is in JSON format, and Athena expects it to be in tabular form.</figcaption></figure><p>So we will have to transform this data.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lY7-JW9mvFM0z706P_YzNQ.png" /><figcaption>We will take the raw JSON data from our S3 bucket, write the transformation code inside a Lambda function and send JSON data to it. Then the Lambda function will transform the data and store it in a S3 bucket. Now we can create a crawler for this cleaned data and use Athena to query over it.</figcaption></figure><p>First, we need to create a Lambda function:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*TBVzmplTRbw2JuWC5lUXQw.png" /><figcaption>Go to Lambda console -&gt; Create a function -&gt; Name your function and choose Python 3.8 runtime.</figcaption></figure><p>We also need to attach an IAM role (so create it first) that gives S3 full Access, and AWSGlueServiceRole to our Lambda function and attach it to the function.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/967/1*1QShyRWjWbDDoS-opBQNmw.png" /><figcaption>Now, click on create function.</figcaption></figure><p>Let’s understand the code part by part:</p><pre>import awswrangler as wr<br>import pandas as pd<br>import urllib.parse<br>import os<br><br># Temporary hard-coded AWS Settings; i.e. to be set as OS variable in Lambda<br>os_input_s3_cleansed_layer = os.environ[&#39;s3_cleansed_layer&#39;]<br>os_input_glue_catalog_db_name = os.environ[&#39;glue_catalog_db_name&#39;]<br>os_input_glue_catalog_table_name = os.environ[&#39;glue_catalog_table_name&#39;]<br>os_input_write_data_operation = os.environ[&#39;write_data_operation&#39;]<br><br><br>def lambda_handler(event, context):<br>    # Get the object from the event and show its content type<br>    bucket = event[&#39;Records&#39;][0][&#39;s3&#39;][&#39;bucket&#39;][&#39;name&#39;]<br>    key = urllib.parse.unquote_plus(event[&#39;Records&#39;][0][&#39;s3&#39;][&#39;object&#39;][&#39;key&#39;], encoding=&#39;utf-8&#39;)<br>    try:<br><br>        # Creating DF from content<br>        df_raw = wr.s3.read_json(&#39;s3://{}/{}&#39;.format(bucket, key)) # Reads data from our spefied location, gets the location from bucket, key<br><br>        # Extract required columns:<br>        df_step_1 = pd.json_normalize(df_raw[&#39;items&#39;]) # Takes the list of dic<br>                                              # tionaries from items and convert <br>                                              # it into tabular format<br>        # Write to S3<br>        wr_response = wr.s3.to_parquet(<br>            df=df_step_1,<br>            path=os_input_s3_cleansed_layer,<br>            dataset=True,<br>            database=os_input_glue_catalog_db_name,<br>            table=os_input_glue_catalog_table_name,<br>            mode=os_input_write_data_operation<br>        )<br><br>        return wr_response<br>    except Exception as e:<br>        print(e)<br>        print(&#39;Error getting object {} from bucket {}. Make sure they exist and your bucket is in the same region as this function.&#39;.format(key, bucket))<br>        raise e</pre><p>We first have to specify the values for the OS variables in our lambda function so that it can use them whenever:</p><p>To do so, in the Lambda dashboard go to Configuration -&gt; Environment variables -&gt; Edit:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*R9qt2eBLZ2P8HexduDvVzQ.png" /></figure><p>s3_cleansed_layer stores the path of the s3 folder where you want the Lambda function to write the output.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*iodgZjohq5hQZveI3eIhAA.png" /><figcaption>For this I created a new folder inside my s3 bucket</figcaption></figure><p>So I will pass the path of that folder in this format: s3://bucketname/folderName/nestedFolderName and so on</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/717/1*BL4KBaWgu8OXE_wkr8JRCg.png" /><figcaption>We specify the names and that we want to append the data rather than overwriting it.</figcaption></figure><p>Let’s test this function:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/655/1*TeteE9jsy4je7vrkmfG-lA.png" /><figcaption>Click the arrow next to test and click “Configure test event”</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/715/1*vG7dwsthjyKWxTkYtGAwbg.png" /><figcaption>Then from the templates, choose S3 put.</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/460/1*gko_RyZESyPY101E--dzuw.png" /><figcaption>Now in the JSON file, corresponding to name and arn, replace example-bucket with the name of your bucket.</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/525/1*PDVydiFtkOoAUtALynrhVw.png" /></figure><p>We now need to add a key to this file. The key is the URI of the object we are using without the bucket name.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*YsVFvQa0_ZxgmzJ5sOU_Dg.png" /><figcaption>So we will go to the object and copy it’s URI</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/646/1*80v6fRPXoFTvsaJ6DJCSeQ.png" /><figcaption>Then we will paste the URI in the Key without the bucket name and save.</figcaption></figure><p>Let’s now test this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5EC_Jm3hC2gGUxcGLiuj_A.png" /><figcaption>And we are getting this error</figcaption></figure><p>To resolve this, we need to preinstall the awsWrangler library in a way into our lambda function. We will do so as the lambda function might not be able to install the library on time every time we run our code. We will accomplish this using lambda layers.</p><p>When a Lambda function runs, it creates a computing environment for the code it executes. Lambda layers are attached to compute environments and have all the packages and libraries installed that are required to run our Lambda function code.</p><p>To learn more about Aws Lambda, check out the <a href="https://aws.amazon.com/lambda/features/#:~:text=AWS%20Lambda%20is%20a%20serverless,cart%20on%20an%20ecommerce%20website.">Documentation</a>.</p><p>Here’s how we do it:</p><p>Scroll to the bottom in the Lambda console and click on add a layer -&gt; Click on specify ARN -&gt; Get the ARN from this <a href="https://aws-sdk-pandas.readthedocs.io/en/2.14.0/install.html#aws-lambda-layer">link</a> -&gt; Paste the ARN and click on Add.</p><p>Let’s test the function again:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/995/1*Virwy1I0AXwFce-gQFBidw.png" /><figcaption>And we get this error</figcaption></figure><p>We are getting this error because the timeout time right now is set too low (3s) for our use case, this can be increased to 15min. To increase it:</p><p>Go to Configuration -&gt; General configuration -&gt; Edit -&gt; Increase timeout to 3min and save</p><p>Let’s run the function again:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1003/1*pVqCCdQmTMdyWg5hUNcOyg.png" /><figcaption>It ran successfully.</figcaption></figure><p>The .parquet (tabular format) file is now stored at the specified location in s3 and a new table is created in Glue. Let’s view this table by clicking view table.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/932/1*v7TJuH2P2muSxq3sZgQf7A.png" /><figcaption>Perfect, and now we can easily query this data.</figcaption></figure><p>Let’s now create a crawler for our CSV files that contain the videos:</p><p>Aws glue -&gt; Crawlers -&gt; Create crawler -&gt; Name it -&gt; The data source here will be the folder that contains all the different region’s data folders -&gt; Attach the same IAM role that we attached to the previous crawler, the role has glueServiceRole and S3FullAccess -&gt; Choose the database you added for the raw data -&gt; Create crawler -&gt; Run crawler.</p><p>Let’s query this data using Athena:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/931/1*TmE5mYx1a8FNuLww65VUrw.png" /><figcaption>All the data of the csv files is stored in one table that is partitioned by region. This is because we stored the files in separate folders according to the region.</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/996/1*gR2LQW0OgI1h8d5GOvr7qQ.png" /><figcaption>We can now get information out of this data.</figcaption></figure><p>Note that id in b was in string format so we had to typecast it to integer. Ideally, we should already have this preprocessed, we can also change it by changing it in the catalog via the Glue console: Tables -&gt; select the required table -&gt; Scroll and click on edit schema -&gt; click on id and edit -&gt; change the datatype to bigint and save.</p><p>If we try to run the code again without the cast function, we will get the following error:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/974/1*2lQ5-OmG0rF9-eMt3hTIBA.png" /></figure><p>This happened because when we created the parquet, the id column already had the string datatype attached to it so changing the datatype in the catalog did not change the datatype in the parquet file.</p><p>To fix this, we will keep the change in the catalog -&gt; delete the existing cleaned parquet file -&gt; test the lambda function again to get a fresh parquet file. This will change it as our Lambda function appends the data into the existing schema structure.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/996/1*aP79gSllXdfx63aR2fy5_g.png" /><figcaption>We are now getting the desired output.</figcaption></figure><p>We now need to clean the video data that is stored in CSV files. For this, we will use glue ETL:</p><p>Go to Glue dashboard -&gt; Jobs(I am using legacy) -&gt; Add job -&gt; Name it -&gt; Add the glue s3 role that we created -&gt;</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/552/1*1RtvIVs9aTJm0xewXemy5Q.png" /><figcaption>Leave S3 path… and temp. directory as it is and click next</figcaption></figure><p>Then select the source data i.e. where your CSV files are stored -&gt; Next, Change schema, next -&gt;</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/812/1*mhZKvjDyg3mRPnmapEyhrw.png" /><figcaption>For target path, I created a new folder in my S3 bucket and pasted it’s URI here.</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/982/1*JQrwBfKxQ5CRlzi9_QU5Iw.png" /><figcaption>On the next page, make sure that the source and target datatype remain the same, you might have to convert long to bigint for some columns.</figcaption></figure><p>Finally, click on save job and edit script &amp; AWS will automatically generate a script for this use case but we have to optimize this script to get the desired result. The script we have right now will put all the files into a single folder, we don’t want that. We want each file to be stored in a separate folder according to it’s region so that we can have our table partitioned by region just like we did for the raw data.</p><p>To do this, make the following changes to this code:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/332/1*ItsdElWkBSleJxoD6iX6jg.png" /><figcaption>import dynamic frame.</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/893/1*d7127yN4TAw6ceukltwM7w.png" /><figcaption>add line 44 and 45 above datasink4</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/983/1*5Dko_SEghMMqhVHYd0tA9A.png" /><figcaption>In datasink4 -&gt; connection_options -&gt; right after path, within the curly braces, add the highlighted code.</figcaption></figure><p>Let’s now save and run the job:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*WHkiNzOHheHyXHraETOjAw.png" /><figcaption>Our job failed. This happened because in the files, we have data in different languages and the machine can’t understand that, to prevent this, we have to convert our data to UTF-8 format which is easily understandable by the machine. But we will not add that complexity and rather just exclude the problem-causing tables.</figcaption></figure><p>To fix this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*b6m77XC4fdO_PMbhSNK8NA.png" /><figcaption>1. Add the following line above datasource0</figcaption></figure><pre>predicate_pushdown = &quot;region in (&#39;ca&#39;,&#39;gb&#39;,&#39;us&#39;)&quot;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5Z43Wp78tfDxz0G3ei5TTQ.png" /><figcaption>in datasource0, add the given code next to transformation_ctx = “datasource0”</figcaption></figure><pre>, push_down_predicate = predicate_pushdown</pre><p>This code is responsible to filter out the locations that we are providing. Let’s save and run this job:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lrtZ2c7kBobIbqSXKtWZrA.png" /><figcaption>The job ran successfully and we now have our data in our destination folder in our S3 bucket.</figcaption></figure><p>Now we need to create another crawler for this folder.</p><p>After creating the crawler, we need to configure our lambda function to automatically process the data and store it in the desired location every time new data is added to the folder.</p><p>To do this, go to your function and click on add trigger -&gt; Select S3 -&gt; Choose bucket -&gt; All object create events -&gt; In prefix, add the path of the directory in which files will be uploaded, just don’t include the bucket name (in short, paste the key) -&gt; In suffix, add .json as we want to process the json files.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/636/1*-tKVLaWomUThHLCNmq0pcg.png" /><figcaption><strong>REMOVE THE SPACE IN THE PREFIX</strong> (behind raw)</figcaption></figure><p>Now we will delete all the data from our folder that currently has the raw JSON data as well as the one cleaned parquet file that was created while testing the lambda function.</p><p>After deleting the data, we will again upload the data to the raw folder just like we did at the beginning of this tutorial.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1013/1*xjCNX_X_rqT2_PMWdHEN-A.png" /><figcaption>As soon as we upload the data, the lambda function will get triggered and the cleaned data will land in our clean bucket location</figcaption></figure><p>Now we need to build a final reporting layer where data is stored according to the query that needs to run every day. This will reduce complexity.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/668/1*JcuitNMwv17Jyzhal1mnBQ.png" /><figcaption>This is the data that we require for our analysis.</figcaption></figure><p>So we need to make another ETL pipeline from our clean bucket to a reporting bucket that will provide this data directly to the end users without them having to do these joins.</p><p>To accomplish this, we will use the glue studio:</p><p>Go to Glue Studio -&gt; Glue Jobs -&gt; Visual with a source and target -&gt; Click create -&gt; Delete all the preconfigured stuff -&gt; Choose glue catalog as the data source -&gt; Choose cleaned_statistics_reference_data as one source -&gt; add another source, glue catalog, and choose the cleaned_statistics table.</p><p>Now go to transform and choose inner join, and in the condition, join category_id to id just like in SQL query.</p><p>We want to store this data in a separate bucket, so please create an analytical bucket.</p><p>Once the bucket is created, go back to the glue studio -&gt; add a target and choose your s3 bucket</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*V5SncUoRMTcgCZBEAm3bsA.png" /><figcaption>Choose the following settings</figcaption></figure><p>To add a database, we first need to create a database</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/442/1*KilmKLK7gQHgcKvdE41gBQ.png" /><figcaption>One way to do this is to execute a create database query inside Athena.</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/625/1*gOwauGaQd78kut-fs3eBjw.png" /><figcaption>Now choose that database and specify everything else.</figcaption></figure><p>Under job details, choose the IAM role that we are using for S3 and glue access, and click save, and once it is saved, run the job.</p><p>Once the job has run successfully, we will have our data stored in the analytical bucket partitioned by the specified keys:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*I0iOibYYyYwFHNKYs-LRMQ.png" /></figure><p>This data is now ready to use easily for data scientists and analysts.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5bbba4368d23" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[My first ETL project on AWS explained step by step]]></title>
            <link>https://medium.com/@chaudharyharsh575/my-first-etl-project-on-aws-explained-step-by-step-220fcaf799f3?source=rss-425dd0f8a083------2</link>
            <guid isPermaLink="false">https://medium.com/p/220fcaf799f3</guid>
            <category><![CDATA[data-warehouse]]></category>
            <category><![CDATA[etl]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[redshift]]></category>
            <category><![CDATA[aws]]></category>
            <dc:creator><![CDATA[Harsh Chaudhary]]></dc:creator>
            <pubDate>Mon, 01 May 2023 08:22:52 GMT</pubDate>
            <atom:updated>2023-05-01T08:22:52.957Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/668/1*v3_tH2lYPmVJGRSmiNG8RA.png" /></figure><p><strong>*This project is very helpful if you want to get a general understanding of the ETL process using AWS.</strong></p><h4>If you don’t understand something, Google it rather than giving up.</h4><h3>Table of contents:</h3><ul><li>Putting the data into an S3 bucket.</li><li>Using crawler and Athena to analyze data and build data model.</li></ul><p><a href="https://drive.google.com/drive/folders/1zSXkcJBfdaPZpiRu9xPXW2-KyOCNy0r-">You can download the data from this link</a></p><h4>1. Creating an S3 bucket and adding data to it:</h4><p>Creating a bucket is pretty straightforward, open the S3 console on AWS, click on the “Create bucket” button, pick a name and region close to you, and finally click on Create.</p><p>Next, we will have to create the folders in our bucket. The folder structure needs to be similar to the folder structure of the data that we downloaded. Include the csv folders only as we will be working with them.</p><p>You might have to download the enigma_jhu file from <a href="https://dj2taa9i652rf.cloudfront.net/">this </a>link as the one we have seems to be corrupt. Also, check all the CSV files in all folders, some have an extra extension after .csv, so you need to remove that extra extension.</p><p>Now, put all the CSV files into their respective folders.</p><ul><li>You will understand the UI by yourself as you navigate through it, everything there is pretty self-explanatory. I will specify if any step requires you to choose the non-default settings.</li></ul><h4>2. Creating a crawler for our data files:</h4><p>When we will use Athena to query our tables, Athena will find these tables with the help of the crawler we are about to create. You will get a better understanding once you actually do it.</p><ul><li>We can directly use SQL queries on the data files using Athena.</li></ul><p>We will create a crawler for</p><p>To create a crawler, go to AWS Glue -&gt; Crawlers -&gt; Create crawler -&gt; Name it -&gt; check “select one or more data sources to be crawled” -&gt; add data source -&gt; S3, in this account, browse the bucket and inside the bucket choose the folder in which the file to be crawled exists. enable “crawl all subfolders”.</p><p>Now we need an <a href="https://docs.aws.amazon.com/IAM/latest/UserGuide/id_roles.html">IAM role</a>, so to create it we will go to the IAM console -&gt; roles -&gt; Aws sevice -&gt; choose “Glue” and click next.</p><p>Now we will select the following permissions for our role: AmazonS3FullAccess, AWSGlueConsoleFullAccess, and AWSGlueServiceRole. Give this role a name, leave everything as default, and create the role.</p><p>This IAM role basically allows Glue to call other services on our behalf. We will now attach that role to Glue in the Glue console.</p><p>In the next step, we will simply create a database by following instructions and choose it. Leave everything as it is.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qIvkP0xK4OOybhHdzUTaMw.png" /><figcaption>We have our crawler ready, now we will run it.</figcaption></figure><p>Now we will go to AWS Athena. To work with Athena, we need to create an extra S3 bucket for it where it stores metadata and query results. So we need to create an S3 bucket and attach it to Athena.</p><p>First, create a bucket -&gt; go to Athena console -&gt; Edit settings -&gt; Choose the newly created bucket.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mMihWoXSYsZj7teoptaS-A.png" /><figcaption>We can now query our first table “enigma_jhud” using Athena</figcaption></figure><p>Now we have to create a crawler for every single table i.e. for every folder that has a csv file, so name the crawlers accordingly. For ex- enigma-nytimes-data-in-usa folders have separate folders for state and country, so make sure you make separate crawlers for both.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3sSTWwofbziQozS-_IeA4A.png" /><figcaption>All crawlers inside Glue are ready.</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*F_A2POo2DG2-kHXy7Smdaw.png" /><figcaption>We also have all our tables loaded into our Athena query editor.</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/378/1*ggUGM9BwlezHFNA5jIEqcA.png" /><figcaption>We can now run any SQL query on this data, but our aim here is to build a data model.</figcaption></figure><h4>3. Building a Data model:</h4><p>If you want to learn about how to build a star schema, you can check out this blog: <a href="https://medium.com/@chaudharyharsh575/heres-how-i-created-a-star-schema-using-data-distributed-across-tables-a81cb717412">Here’s how I created a Star-Schema using Data Distributed Across Tables</a></p><p>To build our data model, we need to know what columns are there in which tables. We can find this out by:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/445/1*J9eu0BoS9wOhqIRlm4AiuQ.png" /><figcaption>Click on the three dots of the required table and click “Generate table DDL”</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/391/1*tu7TCPEA6g6jHfl0JQMYeg.png" /><figcaption>This will give us information about the table structure.</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/602/1*hx-uWdsAFe5NcOurYgqYnQ.png" /><figcaption>Now we can copy these columns and paste them in draw.io for each table and visualize our data model.</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/666/1*1_PKNvxZgh_Zvv5wPnvKOw.png" /><figcaption>This is what our ‘loose’ data model looks like.</figcaption></figure><p>This tutorial is focused on ETL process so we won’t apply much effort in understanding the data model part.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/308/1*B61EaxVfHIz9Th2Tk7ea5A.png" /><figcaption>This is the Star Schema that we will be using.</figcaption></figure><h4>4. ETL using Python:</h4><p>First, we need to import some libraries and specify the following things (you need to install these libraries first to use them)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1016/1*mh2yzhOOQKJZZPvm_obT9A.png" /></figure><p><a href="https://docs.aws.amazon.com/powershell/latest/userguide/pstools-appendix-sign-up.html">This is how you find Access keys</a></p><p>S3 staging dir is the directory in your s3 bucket where you want to store your output, bucket_name is the name of that bucket, and s3 output directory is the output folder.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/358/1*Vx21Vr_2E0Cqmu3IfeFg_w.png" /></figure><p>Now, we need to connect to the Athena service which is inside our account:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/395/1*VK8Qxh7FXKCZwxymr2b0pw.png" /><figcaption>We will pass this as client in the next function</figcaption></figure><p>Now for data manipulation using Python, we need the datasets in Python. For this purpose, we will use the following function:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/783/1*yb4Lw2lwLntmV-MpGOyCLQ.png" /></figure><p>We will pass the following dictionary in the function above:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/740/1*xe2uIaQwDjOKF_h1IlmYnA.png" /></figure><p>Now we will call this function and will get a pandas DataFrame in return:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/893/1*YkEmF7JflhS9rL5vp3c6Ng.png" /></figure><p>We have now successfully loaded Our first table in Python. We now need to do the same for every single table that we have (Basically copy the same code for each table name.</p><p>If you checked the head of each table, you would have noticed that one of our tables has the following error:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/171/1*AcVaTl4CZRB4SvQNYK9Ehg.png" /><figcaption>The column names are misidentified as the first row</figcaption></figure><p>To fix this, we will use the following code:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/666/1*veWiKl3DcgG9a--el7j0RA.png" /><figcaption>And we have the fixed DataFrame</figcaption></figure><p>Now that we have all our tables, we will create our star schema inside Python by selecting the required columns and putting them into their corresponding tables inside our star schema.</p><p>Let’s build our factCovid table:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/906/1*p77cG-Ew8rV-2dPrAVk9pQ.png" /></figure><p>dimRegion table:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/683/1*6rjbADrMZvBxTo2GN4dx2g.png" /></figure><p>dimDate table:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/781/1*zZ98WZoWUUmPXPq4tDZ0-g.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/299/1*SWmppR82s3rhw1RB4InKVQ.png" /></figure><p>Now we need to store these datasets on an S3 bucket in the form of CSV files. To do so, we will use the following code for each table:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/873/1*mZGlsnQxXmGcdMXzWhgxLA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*pQW7OjRGjk2Squ49D6G-Yg.png" /><figcaption>Now we have all the required tables in the output_tables folder of out S3 bucket.</figcaption></figure><p>Now, we need to load this data into a Redshift cluster. For that, we will have to create these tables inside the Redshift cluster and use the copy command to copy all the data to these tables. Here’s a cool way to get the create table SQL query from a table in python:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/465/1*QiAvveCKsboYscbilKo1gg.png" /><figcaption>We will do this for each table to keep things simple.</figcaption></figure><p>Now, we need to create a Redshift cluster and connect it with our Python code.</p><p>Go to the Redshift console, read everything, and create the Redshift cluster. Once done with that, then we have to connect to our cluster using our Jupyter Notebook. Here’s the syntax for it:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/306/1*uCEt7jInezgSJV4E0lNzCQ.png" /><figcaption>Install the library if you don’t already have it.</figcaption></figure><ul><li>Make sure that you have public access enabled in your Redshift cluster. You can find this setting under network and security settings in your cluster.</li></ul><p>Once the connection is made, we need to create a cursor and execute our create table queries for all tables:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/370/1*_NZsPUstS8662VNK-KfQgA.png" /><figcaption>Do this for all tables.</figcaption></figure><p>Now we will use the copy command to copy the data from the table in S3 to the table in our Redshift cluster. Here’s how we will do it:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/709/1*QcaCsb6cZZLLLjLGmONyvQ.png" /><figcaption>We will do this for every single table.</figcaption></figure><p>We now have our data loaded to the Redshift cluster. I hope you learned something new.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=220fcaf799f3" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Here’s how I created a Star-Schema using Data Distributed Across Tables]]></title>
            <link>https://medium.com/@chaudharyharsh575/heres-how-i-created-a-star-schema-using-data-distributed-across-tables-a81cb717412?source=rss-425dd0f8a083------2</link>
            <guid isPermaLink="false">https://medium.com/p/a81cb717412</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[star-schema]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Harsh Chaudhary]]></dc:creator>
            <pubDate>Thu, 27 Apr 2023 06:01:15 GMT</pubDate>
            <atom:updated>2023-04-27T06:01:15.966Z</atom:updated>
            <content:encoded><![CDATA[<p>.</p><p><a href="https://twitter.com/hvrshchaudhary">Follow me on Twitter</a></p><p>.</p><h3>Set up:</h3><ul><li><a href="https://www.postgresqltutorial.com/postgresql-getting-started/postgresql-sample-database/">This dataset is used in this tutorial</a>.</li><li><a href="https://www.postgresqltutorial.com/postgresql-getting-started/load-postgresql-sample-database/">Follow the steps from this link to load the data to PostgreSQL</a> (Follow up to the cmd part).</li><li><a href="https://www.pgadmin.org/download/">Download and install PgAdmin for your os via this link</a>.</li></ul><p>Open PGadmin and click on databases &gt; dvdrental &gt; Schemas &gt; Tables and you will be able to see all the tables.</p><h4>This is the schema of the database that we have:</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/495/0*BOBgVEZPMvCanEoX.png" /><figcaption>All the data is distributed over different tables and each table has some relationship with some other table.</figcaption></figure><h4>What is a Star Schema and why use it?</h4><p>We can see in the above schema, that the data is spread across multiple different tables where some tables are redundant and some tables can be combined as they have the same type of data ex- address, city &amp; country table. By combining such tables, the database becomes more organized and easy to understand and analyze. It also reduces the query time as we now have to perform less number of JOINS.</p><h4>Here’s the star schema corresponding to the above schema:</h4><p>(you can create such diagrams using <a href="https://draw.io/">draw.io</a>)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/725/1*uMySWlVOk0LYm7tlhLIErw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/669/1*U2j3ShVYaBtJqNdKu3LnRA.png" /></figure><p>In the star schema, we have combined all the similar types of information into one single table.</p><p>The table at the center is called the <strong>fact table</strong>, it connects to all the other tables by having primary key columns of all the other tables. It also contains some information about data that is dependent on all the other dimension tables.</p><p>The tables on the sides are called <strong>dimension tables</strong>, each dimension table contains all the information about one subject.</p><p><strong>Now we will create each dimension table:</strong></p><p><a href="https://medium.com/@chaudharyharsh575/heres-how-you-create-tables-in-sql-f6ee4b15af0a">Here’s how you create a table in SQL</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/337/1*ASLchgiXAIMeUJKhJlrYsA.png" /><figcaption>Here’s the code to create one table</figcaption></figure><h4>Inserting data into tables</h4><p><strong>For the dimDate table:</strong> We need the year, month, week, etc but all we have is payment_date. To do this, we will use the <a href="https://www.postgresqltutorial.com/postgresql-date-functions/postgresql-extract/">EXTRACT</a> function of PostgreSQL which enables us to extract all this information from date.</p><p>for date_key, we will convert the date into integer format i.e. remove dashes and get only the last 3 non-zero digits from the year. We did this using the <a href="https://www.postgresqltutorial.com/postgresql-string-functions/postgresql-to_char/">TO_CHAR</a> function.</p><p>For is_weekend, we applied the condition to write true if day of week is 6 or 7 or else write false.</p><p>Here’s the code for that:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/836/1*Z4zc7BFRYQv8InMkRnJNqw.png" /></figure><p><a href="https://medium.com/@chaudharyharsh575/case-statement-in-sql-dfae6eece154">Case Statement in SQL</a>,</p><p>Similarly for the other tables, we will get all the columns from their respective tables by joining all the required tables:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/774/1*t2Q2NEe67qTRtTIIX2EoPw.png" /><figcaption>Inserting values into dimCustomer table</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/822/1*5s5bwZNlTktLyLyIgx5aBA.png" /><figcaption>Inserting values into dimmovie table</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/846/1*WUrTDRM354rcyDhg9rbSsw.png" /><figcaption>Inserting values into dimstore table</figcaption></figure><p><strong>We will now create our fact table that connects all the dimension tables:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/553/1*yqdYzugACjL4d_KXNa24WQ.png" /><figcaption>the references keyword specifies the parent table and parent table columns which are referenced by a foreign key (column) in the current table</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/585/1*kOsk8LmIML6v7gNdyvhRYA.png" /><figcaption>inserting values into the fact table</figcaption></figure><p>That completes our star schema.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a81cb717412" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Here’s how you leverage Data Analytics in a Distribution business]]></title>
            <link>https://medium.com/@chaudharyharsh575/heres-how-you-leverage-data-analytics-in-a-distribution-business-5978024c08d1?source=rss-425dd0f8a083------2</link>
            <guid isPermaLink="false">https://medium.com/p/5978024c08d1</guid>
            <category><![CDATA[distribution]]></category>
            <category><![CDATA[business-intelligence]]></category>
            <category><![CDATA[supply-chain]]></category>
            <category><![CDATA[data-analytics]]></category>
            <dc:creator><![CDATA[Harsh Chaudhary]]></dc:creator>
            <pubDate>Sun, 23 Apr 2023 16:11:59 GMT</pubDate>
            <atom:updated>2023-04-23T16:11:59.508Z</atom:updated>
            <content:encoded><![CDATA[<p>.</p><p><a href="https://twitter.com/hvrshchaudhary">Follow me on Twitter</a></p><p>.</p><h3>Table of contents</h3><ul><li>Introduction.</li><li>Few applications of Data Analytics in a Distribution business.</li><li>Conclusion.</li></ul><h3>Introduction</h3><p>Data analytics is the key to unlocking the valuable insights hidden in a business’s diverse range of data. McKinsey found that companies that analyze their customer data to find out what their customers are buying and why, far outperform their competitors on Sales, Sales Growth, Profit, and ROI metrics. Data analytics can help you analyze your existing customers’ buying behavior to uncover many hidden cross-sell and up-sell opportunities that may be much easier to capitalize on than finding and winning new customers.</p><h3>Applications of data analytics (distribution)</h3><h4>1. In Sales Growth:</h4><p>Data analytics helps you find simple up-sell opportunities that you might not be aware of without the tools to find them. For example: For products that are purchased together, some have sales that are rising, and some have sales that are falling. Researching this might signal, for example, that customers are substituting one product for another. With early-warning alerts like this, you can outpace your competitors by modifying your purchases to make sure you have stock of the products being switched to and find ways to clear out the products that customers are substituting away from.</p><h4>2. Evaluating inventory performance:</h4><p>Most distributors’ biggest investments are in their inventory. Data analytics lets you analyze which products are moving and which are not and comparing these to the cost of stocking the products and their margins can lead to many very powerful insights that can help you cut costs. For example, an inexpensive product with a low margin but that moves quickly could have a higher ROI than a more expensive product with a higher margin that ties up more capital and moves slower.</p><h4>3. Navigating supply chain issues:</h4><p>With help of Data analytics, you can evaluate suppliers on their ability to fill purchase orders correctly, how long it takes to supply the goods, terms, price, availability of the goods and relationships with delivery vendors. Data analytics can help you capture and assess the data to identify early warning signs such as if an otherwise reliable supplier’s delivery times are going up or the fill rate is going down. This information lets you take steps to protect your business and your customers.</p><p>There are a huge number of ways in which data analytics for distributors help make better, more-informed data-based decisions faster. Particularly in a challenging and rapidly changing environment, knowing when there are problems sooner than your competitors lets you take steps to address these and gain an advantage over your competitors.</p><h3>Conclusion:</h3><p>A data-driven approach will help you respond quickly to market challenges. It will help companies make decisions based on real numbers and predict outcomes in different areas of the business with greater accuracy. It’s the kind of tool you need to drive growth, beat the competition, and attract loyal customers. So, if you can use data to prove that the decisions you make can have a positive impact on business growth, it’s worth taking the time to analyze it.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5978024c08d1" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Here’s how I analyze data distributed over multiple files.]]></title>
            <link>https://medium.com/@chaudharyharsh575/heres-how-i-analyze-data-distributed-over-multiple-files-7ec3080488a2?source=rss-425dd0f8a083------2</link>
            <guid isPermaLink="false">https://medium.com/p/7ec3080488a2</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[data-analysis]]></category>
            <dc:creator><![CDATA[Harsh Chaudhary]]></dc:creator>
            <pubDate>Thu, 20 Apr 2023 05:55:27 GMT</pubDate>
            <atom:updated>2023-05-04T16:40:54.600Z</atom:updated>
            <content:encoded><![CDATA[<p>.</p><p><a href="https://twitter.com/hvrshchaudhary">Follow me on Twitter</a></p><p>.</p><h4>Set up:</h4><ul><li><a href="https://youtu.be/K45k-gNNzGo">Watch the start of this video to set up PostgreSql in your system.</a></li><li><a href="https://www.postgresqltutorial.com/postgresql-getting-started/postgresql-sample-database/">This dataset is used in this tutorial</a>.</li><li><a href="https://www.postgresqltutorial.com/postgresql-getting-started/load-postgresql-sample-database/">Follow the steps from this link to load the data to PostgreSQL</a> (Follow up to the cmd part).</li><li><a href="https://www.pgadmin.org/download/">Download and install PgAdmin for your os via this link</a>.</li></ul><p>Open pgAdmin and click on databases &gt; dvdrental &gt; Schemas &gt; Tables and you will be able to see all the tables.</p><p>This is the schema of the database that we have:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/495/1*WESqQje-N5hlCc7-M2Ah8A.png" /><figcaption>All the data is distributed over different tables and each table has some relationship with some other table.</figcaption></figure><p>We had a meeting with the stakeholders and we are asked to find out which city gave us the highest revenue.</p><p>The payment table contains information about revenue and the city table contains the city name, but both these tables have no direct connection. So to solve this problem, we will have to connect these two tables by joining all the table’s columns that lie on the path of these tables.</p><h4>So we will:</h4><ul><li>JOIN payment and customer table on customer ID.</li><li>JOIN customer and address table on address ID.</li><li>JOIN address and city table on city ID.</li><li>Then finally get the total revenue from each city.</li></ul><p>Here’s the code for all this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/490/1*MQhkI9-UufCZB5y50TAidQ.png" /><figcaption>Here’s the total revenue of cities in descending order.</figcaption></figure><p>I have already explained the working of each statement used in my previous blogs:</p><ul><li><a href="https://medium.com/@chaudharyharsh575/group-by-order-by-statements-in-sql-218644a9e7a8">Group by &amp; Order by.</a></li><li><a href="https://medium.com/@chaudharyharsh575/inner-outer-joins-in-sql-dd01b785819f">Join.</a></li><li><a href="https://medium.com/@chaudharyharsh575/select-from-statements-in-sql-9230115c6323">Select + From.</a></li><li><a href="https://medium.com/@chaudharyharsh575/aliasing-in-sql-5328a9359423">Alias.</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7ec3080488a2" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Building a Data Model and Deploying it to a Database (Python + Postgres)]]></title>
            <link>https://medium.com/@chaudharyharsh575/building-a-data-model-and-deploying-it-to-a-database-python-postgres-2a587e827eeb?source=rss-425dd0f8a083------2</link>
            <guid isPermaLink="false">https://medium.com/p/2a587e827eeb</guid>
            <category><![CDATA[python]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-modeling]]></category>
            <category><![CDATA[database]]></category>
            <dc:creator><![CDATA[Harsh Chaudhary]]></dc:creator>
            <pubDate>Tue, 18 Apr 2023 11:54:51 GMT</pubDate>
            <atom:updated>2023-04-18T11:54:51.373Z</atom:updated>
            <content:encoded><![CDATA[<p>.</p><p><a href="https://twitter.com/hvrshchaudhary">Follow me on Twitter</a></p><p>.</p><p>Today we will learn how to build and deploy a data model to a database using Python.</p><h4>Dataset</h4><p>I am using the <a href="https://www.kaggle.com/datasets/erikbruin/airbnb-amsterdam">Airbnb Amsterdam dataset</a> for this project as the data is distributed over multiple files and the files have some common ids/fields via which they can be joined.</p><h4>Data Model</h4><p>We will start by creating a data model. This process is usually done by communicating with the downstream users of the data (analysts, scientists, ML engineers, etc.) about the use case. That way, we filter and choose only the required data.</p><p>This is the data I have filtered and my data model:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/663/1*ADAizA49Qc_u9BigLRyIjg.png" /><figcaption>I created this using the draw.io website</figcaption></figure><h4>Now we will write Python commands to:</h4><ul><li>Create a database.</li><li>Import the files.</li><li>Select the features &amp; put them in a data frame.</li><li>Create tables inside the database.</li><li>Insert data from the data frame into tables.</li></ul><p><a href="https://github.com/hvrshchaudhary/Data_modelling_basic">Here’s the link to the GitHub repository</a>, all the code is explained via comments.</p><p>I have uploaded the file in both .py and .ipynb format as the latter was not being displayed properly at first.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=2a587e827eeb" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>