<?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 Aşkın TAMANLI on Medium]]></title>
        <description><![CDATA[Stories by Aşkın TAMANLI on Medium]]></description>
        <link>https://medium.com/@askintamanli?source=rss-a1ccf8e188c5------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*BxIR6qRmx8LdwXTG2BJjCw.jpeg</url>
            <title>Stories by Aşkın TAMANLI on Medium</title>
            <link>https://medium.com/@askintamanli?source=rss-a1ccf8e188c5------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 22:38:17 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@askintamanli/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[Fastest Methods to Bulk Insert a Pandas Dataframe into PostgreSQL]]></title>
            <link>https://medium.com/@askintamanli/fastest-methods-to-bulk-insert-a-pandas-dataframe-into-postgresql-2aa2ab6d2b24?source=rss-a1ccf8e188c5------2</link>
            <guid isPermaLink="false">https://medium.com/p/2aa2ab6d2b24</guid>
            <category><![CDATA[pandas-dataframe]]></category>
            <category><![CDATA[bulk-insert]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[postgresql]]></category>
            <dc:creator><![CDATA[Aşkın TAMANLI]]></dc:creator>
            <pubDate>Mon, 11 Dec 2023 05:47:44 GMT</pubDate>
            <atom:updated>2023-12-11T06:04:07.032Z</atom:updated>
            <content:encoded><![CDATA[<p>Hello everyone. There are a lot of methods to load data (pandas dataframe) to databases. We are going to compare methods to load pandas dataframe into database. We are going to use PostgreSQL (Local host and version: 16.1) for database.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*m7RzEsyDirrGpo5igQsutA.png" /></figure><p>If you want to work with me, firstly you should create a table in postgres database. You can find the data and all codes in my <a href="https://github.com/askintamanli/Fastest-Methods-to-Bulk-Insert-Pandas-Dataframe-into-PostgreSQL">github</a>.</p><pre>CREATE TABLE test (<br>    master_id UUID ,<br>    order_channel VARCHAR(50),<br>    last_order_channel VARCHAR(50),<br>    first_order_date DATE,<br>    last_order_date DATE,<br>    last_order_date_online DATE,<br>    last_order_date_offline DATE,<br>    order_num_total_ever_online DECIMAL(10, 2),<br>    order_num_total_ever_offline DECIMAL(10, 2),<br>    customer_value_total_ever_offline DECIMAL(10, 2),<br>    customer_value_total_ever_online DECIMAL(10, 2),<br>    interested_in_categories_12 VARCHAR(100)<br>);</pre><p><em>There is 319.121 rows in csv file (53.6 mb). Before apply each method I</em><em>TRUNCATE the table.</em></p><h4>1. Method (df.to_sql)</h4><p>The to_sql method using sqlalchemy is likely the first thing you think.</p><pre># import libraries<br>import pandas as pd<br>from sqlalchemy import create_engine<br>import time<br><br>df = pd.read_csv(&quot;flo_data_20k.csv&quot;) # read csv file from your local<br><br># Example: &#39;postgresql://username:password@localhost:5432/your_database&#39;<br>engine = create_engine(&#39;postgresql://postgres:postgresql@localhost:5432/postgres&#39;)<br><br>start_time = time.time() # get start time before insert<br><br>df.to_sql(<br>    name=&quot;test&quot;, # table name<br>    con=engine,  # engine<br>    if_exists=&quot;append&quot;, #  If the table already exists, append<br>    index=False # no index<br>)<br><br>end_time = time.time() # get end time after insert<br>total_time = end_time - start_time # calculate the time<br>print(f&quot;Insert time: {total_time} seconds&quot;) # print time</pre><p><em>Insert time: 30.48 seconds</em></p><h4>2. Method (execute_values)</h4><pre># import libraries<br>import pandas as pd<br>import psycopg2<br>from psycopg2.extras import execute_values<br>import time<br><br>df = pd.read_csv(&quot;flo_data_20k.csv&quot;) # read csv file from your local<br><br># Establish a connection to your PostgreSQL database<br>conn = psycopg2.connect(<br>    dbname=&#39;postgres&#39;,<br>    user=&#39;postgres&#39;,<br>    password=&#39;postgresql&#39;,<br>    host=&#39;localhost&#39;,<br>    port=&#39;5432&#39;<br>)<br><br>start_time = time.time() # get start time before insert<br><br>with conn.cursor() as c:<br>    execute_values(<br>        cur=c,<br>        sql=&quot;&quot;&quot;<br>            INSERT INTO test<br>            (master_id , order_channel , last_order_channel , first_order_date, last_order_date , last_order_date_online , last_order_date_offline, order_num_total_ever_online, order_num_total_ever_offline, customer_value_total_ever_offline, customer_value_total_ever_online, interested_in_categories_12)<br>            VALUES %s;<br>            &quot;&quot;&quot;,<br>        argslist=df.to_dict(orient=&quot;records&quot;),<br>        template=&quot;&quot;&quot;<br>            (<br>                %(master_id)s, %(order_channel)s, %(last_order_channel)s,<br>                %(first_order_date)s, %(last_order_date)s, %(last_order_date_online)s,<br>                %(last_order_date_offline)s, %(order_num_total_ever_online)s,<br>                %(order_num_total_ever_offline)s, %(customer_value_total_ever_offline)s,<br>                %(customer_value_total_ever_online)s, %(interested_in_categories_12)s<br>            )<br>            &quot;&quot;&quot;<br>    )<br>    conn.commit()<br><br>end_time = time.time() # get end time after insert<br>total_time = end_time - start_time # calculate the time<br>print(f&quot;Insert time: {total_time} seconds&quot;) # print time</pre><p><em>Insert time: 25.75 seconds</em></p><h4>3. Method (copy_expert and a string buffer)</h4><pre># import libraries<br>import pandas as pd<br>import time<br>from io import StringIO<br>import psycopg2<br><br>df = pd.read_csv(&quot;flo_data_20k.csv&quot;) # read csv file from your local<br><br># Establish a connection to your PostgreSQL database<br>conn = psycopg2.connect(<br>    dbname=&#39;postgres&#39;,<br>    user=&#39;postgres&#39;,<br>    password=&#39;postgresql&#39;,<br>    host=&#39;localhost&#39;,<br>    port=&#39;5432&#39;<br>)<br><br>start_time = time.time() # get start time before insert<br><br>sio = StringIO()<br>df.to_csv(sio, index=None, header=None)<br>sio.seek(0)<br>with conn.cursor() as c:<br>    c.copy_expert(<br>        sql=&quot;&quot;&quot;<br>        COPY test (<br>            master_id, <br>            order_channel, <br>            last_order_channel, <br>            first_order_date, <br>            last_order_date, <br>            last_order_date_online, <br>            last_order_date_offline,<br>            order_num_total_ever_online, <br>            order_num_total_ever_offline, <br>            customer_value_total_ever_offline,<br>            customer_value_total_ever_online, <br>            interested_in_categories_12<br>        ) FROM STDIN WITH CSV&quot;&quot;&quot;,<br>        file=sio<br>    )<br>    conn.commit()<br><br>end_time = time.time() # get end time after insert<br>total_time = end_time - start_time # calculate the time<br>print(f&quot;Insert time: {total_time} seconds&quot;) # print time</pre><p><em>Insert time: 9.12 seconds</em></p><h4>4. Method (copy_expert and csv)</h4><pre># import libraries<br>import pandas as pd<br>import time<br>from io import StringIO<br>import psycopg2<br>import csv<br><br>df = pd.read_csv(&quot;flo_data_20k.csv&quot;) # read csv file from your local<br><br># Establish a connection to your PostgreSQL database<br>conn = psycopg2.connect(<br>    dbname=&#39;postgres&#39;,<br>    user=&#39;postgres&#39;,<br>    password=&#39;postgresql&#39;,<br>    host=&#39;localhost&#39;,<br>    port=&#39;5432&#39;<br>)<br><br>start_time = time.time() # get start time before insert<br><br>sio = StringIO()<br>writer = csv.writer(sio)<br>writer.writerows(df.values)<br>sio.seek(0)<br>with conn.cursor() as c:<br>    c.copy_expert(<br>        sql=&quot;&quot;&quot;<br>        COPY test (<br>            master_id, <br>            order_channel, <br>            last_order_channel, <br>            first_order_date, <br>            last_order_date, <br>            last_order_date_online, <br>            last_order_date_offline,<br>            order_num_total_ever_online, <br>            order_num_total_ever_offline, <br>            customer_value_total_ever_offline,<br>            customer_value_total_ever_online, <br>            interested_in_categories_12<br>        ) FROM STDIN WITH CSV&quot;&quot;&quot;,<br>        file=sio<br>    )<br>    conn.commit()<br><br>end_time = time.time() # get end time after insert<br>total_time = end_time - start_time # calculate the time<br>print(f&quot;Insert time: {total_time} seconds&quot;) # print time</pre><p><em>Insert time: 8.15 seconds</em></p><h4>5. Method (df.to_sql with method=callable)</h4><pre># import libraries<br>import pandas as pd<br>from sqlalchemy import create_engine<br>import time<br>import csv<br>from io import StringIO<br><br>def psql_insert_copy(table, conn, keys, data_iter): #mehod<br>    &quot;&quot;&quot;<br>    Execute SQL statement inserting data<br><br>    Parameters<br>    ----------<br>    table : pandas.io.sql.SQLTable<br>    conn : sqlalchemy.engine.Engine or sqlalchemy.engine.Connection<br>    keys : list of str<br>        Column names<br>    data_iter : Iterable that iterates the values to be inserted<br>    &quot;&quot;&quot;<br>    # gets a DBAPI connection that can provide a cursor<br>    dbapi_conn = conn.connection<br>    with dbapi_conn.cursor() as cur:<br>        s_buf = StringIO()<br>        writer = csv.writer(s_buf)<br>        writer.writerows(data_iter)<br>        s_buf.seek(0)<br><br>        columns = &#39;, &#39;.join(&#39;&quot;{}&quot;&#39;.format(k) for k in keys)<br>        if table.schema:<br>            table_name = &#39;{}.{}&#39;.format(table.schema, table.name)<br>        else:<br>            table_name = table.name<br><br>        sql = &#39;COPY {} ({}) FROM STDIN WITH CSV&#39;.format(<br>            table_name, columns)<br>        cur.copy_expert(sql=sql, file=s_buf)<br><br><br>df = pd.read_csv(&quot;flo_data_20k.csv&quot;) # read csv file from your local<br><br># Example: &#39;postgresql://username:password@localhost:5432/your_database&#39;<br>engine = create_engine(&#39;postgresql://postgres:postgresql@localhost:5432/postgres&#39;)<br><br>start_time = time.time() # get start time before insert<br><br>df.to_sql(<br>    name=&quot;test&quot;,<br>    con=engine,<br>    if_exists=&quot;append&quot;,<br>    index=False,<br>    method=psql_insert_copy<br>)<br><br>end_time = time.time() # get end time after insert<br>total_time = end_time - start_time # calculate the time<br>print(f&quot;Insert time: {total_time} seconds&quot;) # print time</pre><p><em>Insert time: 7.84 seconds</em></p><p>Methods from fastest to slowest:<br>1– df.to_sql with method=callable (<em>7.84 seconds)<br>2- </em>copy_expert and csv (<em> 8.15 seconds</em>)<br>3- <em>25.75 seconds (9.12 seconds)<br>4- execute_values (25.75 seconds)<br>5- df.to_sql (30.48 seconds)</em></p><p>THANK YOU FOR READING</p><ul><li>askintamanli@hotmail.com</li><li><a href="https://github.com/askintamanli/Fastest-Methods-to-Bulk-Insert-Pandas-Dataframe-into-PostgreSQL">Github</a></li><li><a href="https://www.linkedin.com/in/askintamanli/">LinkedIn</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=2aa2ab6d2b24" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Data Engineer-Airflow Project-(Automate Daily Data) From Postgres to AWS S3]]></title>
            <link>https://medium.com/@askintamanli/data-engineer-airflow-project-automate-daily-data-from-postgres-to-aws-s3-1f7a24a7ce?source=rss-a1ccf8e188c5------2</link>
            <guid isPermaLink="false">https://medium.com/p/1f7a24a7ce</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[airflow]]></category>
            <category><![CDATA[aws]]></category>
            <category><![CDATA[postgres]]></category>
            <dc:creator><![CDATA[Aşkın TAMANLI]]></dc:creator>
            <pubDate>Wed, 26 Jul 2023 07:32:04 GMT</pubDate>
            <atom:updated>2023-07-26T07:32:04.468Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*orqh248LxZdpFw5zx2jIvQ.png" /></figure><p>Hello everyone. There is not too much case-study project about data engineer field. This is really good project to understand what does data engineer do in real life.</p><p>Today we’re gonna get data from Postgres database and upload the data to AWS S3 bucket. Airflow is gonna upload the data automatically to S3 bucket daily. (I run Airflow in Docker. If you don’t know how to run Airflow in Docker, you can read my <a href="https://medium.com/@askintamanli/run-airflow-in-docker-8c1b22a6d5b7">previously article</a>.)</p><h3>Requirements</h3><ul><li>Basic knowlage Airflow</li><li>Basic knowlage Docker</li><li>Intermediate level Python</li><li>Basic knowlage SQL</li><li>Basic knowlage AWS (S3)</li><li>Basic knowlage Postgres</li></ul><h3>To do list step by step</h3><ol><li>Create a Postgres database and table. Load data to table.</li><li>Create AWS S3 bucket.</li><li>Set Airflow connections for AWS and Postgres.</li><li>Create Airlfow DAG and Task.</li><li>Run the DAG.</li></ol><h4>1.1. Create Postgres database</h4><p>Open the pgAdmin page and create a new database named ‘CRM’.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/613/1*I4kO11gLatNJ6CI3iN4eSQ.png" /></figure><h4>1.2. Create table named ‘orders’</h4><p>Go to Tables and open Query Tool. We’re gonna write SQL statement for create ‘orders’ table.</p><pre>create table if not exists public.orders (<br> order_id character varying,<br> date date,<br> product_name character varying,<br> quantity integer,<br> primary key (order_id)<br>)</pre><h4>1.3. Load data to table</h4><p>Right click to ‘orders’ table and select ‘Import/Export data’. You can get the data from <a href="https://github.com/askintamanli/Data-Engineer-Airflow-Project-From-Postgres-to-AWS-S3">my github repository</a>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/697/1*BFu44ebXiCUN2rZKMG97NQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/699/1*6ST1JhanhE6w-0pZ22VxKA.png" /></figure><p>Than click OK button. Now let’s check our data. Open again Query Tool page and write this SQL statement. Make sure that run only this statement.</p><pre>SELECT * FROM public.orders</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XT_7Ik1ezqjgBjwNcjLlUA.png" /></figure><h4>2. Create AWS S3 Bucket</h4><p>Go to AWS S3 page and create a bucket. My bucket name is ‘<a href="https://s3.console.aws.amazon.com/s3/buckets/from-postgres-data?region=us-east-1">from-postgres-data</a>’.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*tldUVH8fnRXErVRoquND1w.png" /></figure><h4>3.1. Create Postgres connection</h4><p>We should create connections before create Airflow DAG.</p><p>Open the Airflow webserver → Admin → Connections →Create new connection</p><p>Connection Id: postgres_localhost (you can change it)<br>Connection Type: Postgres<br>Host: postgres<br>Schema: CRM (this space must be same with postgres database name)<br>Login: airflow (you can learn it from docker-compose.yaml file)<br>Password: airflow (you can learn it from docker-compose.yaml file)<br>Port: 5432 (you can learn port number from pgAdmin page)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*CLhHMai4SjPEM0kKo9I6tA.png" /></figure><p>Test it, if there is no problem save it.</p><h4>3.2. Create AWS S3 conneciton</h4><p>Open the Airflow webserver → Admin → Connections →Create new connection</p><p>Connection Id: s3_connection (you can change it)<br>Connection Type: Amazon Web Services<br>AWS Access Key ID: (You can learn it form AWS)<br>AWS Secret Access Key ID: (You can learn it form AWS)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XJ5RNqkQbmjC0PMTgfYXwA.png" /></figure><p>Test it, if there is no problem save it.</p><h4>4. Create Airflow DAG and Task</h4><p>This is the python file for DAG and Task. There is only 1 task in this sample. I want to make this esay to understand.</p><pre>from datetime import datetime,timedelta<br>import csv<br>import logging<br>from airflow import DAG <br>from airflow.operators.python import PythonOperator<br>from airflow.providers.postgres.hooks.postgres import PostgresHook<br>from airflow.providers.amazon.aws.hooks.s3 import S3Hook <br>from tempfile import NamedTemporaryFile<br><br>#Default arguments<br>default_args = {<br>    &#39;owner&#39;:&#39;askin_owner&#39;,<br>    &#39;retries&#39;:5,<br>    &#39;retry_Delay&#39;:timedelta(minutes=10)<br>}<br><br>#Main function<br># Meaning of &#39;ds_nodash&#39; is today&#39;s execution<br># Meaning of &#39;next_ds_nodash&#39; is next execution<br>def postgres_to_csv(ds_nodash , next_ds_nodash): <br><br>    # Postgres connection<br>    hook = PostgresHook(postgres_conn_id = &quot;postgres_localhost&quot;)<br>    conn = hook.get_conn()<br>    cursor = conn.cursor()<br>    # Get the data between today and next execution<br>    cursor.execute(&quot;select * from orders where date &gt;= %s and date &lt; %s&quot;,<br>                   (ds_nodash , next_ds_nodash))<br>    <br>    # Create temporary file and upload data to S3 bucket.<br>    with NamedTemporaryFile(mode=&quot;w&quot;,suffix=f&quot;{ds_nodash}&quot;) as f:<br>        csv_writer = csv.writer(f)<br>        csv_writer.writerow([i[0] for i in cursor.description])<br>        csv_writer.writerows(cursor)<br>        f.flush()<br>        cursor.close()<br>        conn.close()<br><br>        # S3 connection<br>        s3_hook = S3Hook(aws_conn_id = &quot;s3_connection&quot;)<br>        s3_hook.load_file(<br>            filename = f.name,<br>            key =  f&quot;orders/{ds_nodash}.txt&quot;,<br>            bucket_name = &quot;from-postgres-data&quot;,<br>            replace = True<br>        )<br>        logging.info(&quot;Orders file {ds_nodash} has been pushed to S3&quot;, f.name)<br><br># DAG<br>with DAG(<br>    dag_id = &quot;from_postgres_to_s3_v01&quot;, # Dag name<br>    default_args=default_args, # Default Arguments<br>    start_date=datetime(2023,7,20), # Start it from this date<br>    schedule_interval=&quot;@daily&quot; # Execute the task daily<br>)as dag:<br>    # TASK <br>    task1 = PythonOperator( # Task created by PythonOperator<br>        task_id = &quot;from_postgres_to_s3&quot;, # Task name<br>        python_callable=postgres_to_csv # Task function<br>    )<br>    task1</pre><h4>5.1. Run the DAG</h4><p>Open and login Airflow webserver. Than run the DAG.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*v0J4gPc9WslKhB0Lr05N2w.png" /></figure><p>ALL SUCCESS. Let’s check the S3 bucket.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/924/1*GxA0gOw_pyhRm2M65csqTw.png" /></figure><p>Today is July 25 and we set the start date July 20 in python file. So we have 20–24 July data. Tomorrow Airflow is going to upload data of 25 July automatically for us.</p><p>THANK YOU FOR READING</p><ul><li>askintamanli@hotmail.com</li><li><a href="https://github.com/askintamanli/Data-Engineer-Airflow-Project-From-Postgres-to-AWS-S3">Github</a></li><li><a href="https://www.linkedin.com/in/askintamanli/">LinkedIn</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1f7a24a7ce" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Run Airflow in Docker]]></title>
            <link>https://medium.com/@askintamanli/run-airflow-in-docker-8c1b22a6d5b7?source=rss-a1ccf8e188c5------2</link>
            <guid isPermaLink="false">https://medium.com/p/8c1b22a6d5b7</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[docker]]></category>
            <category><![CDATA[data-engineer]]></category>
            <category><![CDATA[airflow]]></category>
            <dc:creator><![CDATA[Aşkın TAMANLI]]></dc:creator>
            <pubDate>Mon, 24 Jul 2023 09:01:10 GMT</pubDate>
            <atom:updated>2023-07-24T09:01:10.754Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/752/1*AKRKeI_mw-ikOjdXgJUPbg.png" /></figure><h3>What is Docker ?</h3><p>Imagine you are a software engineer and developing projects. You know that you need libraries and programming languages to develop software and library versions. You may need more than one version for the same library and programming language. For example, you may need Python 2.8 and Python 3.1 at the same time on your computer. You can fix this, but it will be complex and difficult to understand. This is the biggest reason we use Docker.</p><p>Docker is a software platform that allows you to build, test, and deploy applications quickly. Docker packages software into standardized units called <a href="https://aws.amazon.com/containers/">containers</a> that have everything the software needs to run including libraries, system tools, code, and runtime. Using Docker, you can quickly deploy and scale applications into any environment and know your code will run.</p><p>Docker works by providing a standard way to run your code. Docker is an operating system for containers. Similar to how a <a href="https://aws.amazon.com/ec2/">virtual machine</a> virtualizes (removes the need to directly manage) server hardware, containers virtualize the operating system of a server. Docker is installed on each server and provides simple commands you can use to build, start, or stop containers.</p><h3>What is Airflow ?</h3><p><a href="https://www.qubole.com/developers/apache-airflow-on-qubole/">Apache Airflow</a> is an open-source tool to programmatically author, schedule, and monitor workflows. It is one of the most robust platforms used by Data Engineers for orchestrating workflows or pipelines. You can easily visualize your data pipelines’ dependencies, progress, logs, code, trigger tasks, and success status.</p><p>With Airflow, users can author workflows as <strong>Directed Acyclic Graphs (DAGs)</strong> of tasks. Airflow’s rich user interface makes it easy to visualize pipelines running in production, monitor progress, and troubleshoot issues when needed. It connects with multiple data sources and can send an alert via email or Slack when a task completes or fails. Airflow is distributed, scalable, and flexible, making it well-suited to handle the orchestration of complex business logic.</p><h4>Let’s run Airflow in Docker</h4><p>Operating System of our computer is Windows. So, Operating System of our docker conteiner is going to be Windows.</p><ol><li>Download “Docker Community Edition” and “Docker Compose” from Airflow official <a href="https://airflow.apache.org/docs/apache-airflow/stable/howto/docker-compose/index.html">website</a>. You can get the all source code form that website. Than start the Docker Desktop application.</li><li>Open cmd or powershell terminal on your computer. Let’s check our versions of Docker and Docker Compose.</li></ol><pre>docker --version<br>docker-compose --version</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/872/1*qxS2eQRx7dt0B4gTo4KUzA.png" /></figure><p>3. Everything seems alright. Create a folder on Desktop. Go to the file directory in terminal. My folder name is ‘run_airflow_in_docker’.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/876/1*7qK6q2XEAQ0dzRJAD8Cnfw.png" /></figure><p>4. We need to install docker-compose.yaml file. We need to run this code if you are using Windows.</p><pre>curl -LfO &quot;https://airflow.apache.org/docs/apache-airflow/2.6.2/docker-compose.yaml&quot;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/869/1*9FQGS_zDYQnTMQThKvUASw.png" /></figure><p>5. Next step is create a fex folders. We will need this folders for working with Airflow. We need to run this code if you are using Windows.</p><pre>mkdir .\dags <br>mkdir .\logs <br>mkdir .\plugins <br>mkdir .\config</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/872/1*YlG-CGuicMqo28pDEn-wGg.png" /></figure><p>Check the folder.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/869/1*oQfNBt3XjeeqDgLvY4Yfug.png" /></figure><p>6. Now we need to run database migrations and create the first user account.</p><pre>docker compose up airflow-init</pre><p>7. Most exiting step. Runing Airflow.</p><pre>docker compose up</pre><p>Open the Docker Desktop application and check the services.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*E-gAGG8r97AG5X35qwTcqw.png" /></figure><p>8. Let’s open the Airflow Webserver. For that go this URL: <a href="http://localhost:8080/">http://localhost:8080/</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/952/1*mr80N439mzbIQMdm9GdJWA.png" /></figure><p>Username: airflow<br>Password: airflow</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/952/1*sCYO1ehS4TKOHwiUJ5LIOg.png" /></figure><p>And boom. We’re in. Now, you can create DAG’s, Task’s, connections, etc..</p><p>THANK YOU FOR READING</p><ul><li>askintamanli@hotmail.com</li><li><a href="https://github.com/askintamanli">Github</a></li><li><a href="https://www.linkedin.com/in/askintamanli/">LinkedIn</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8c1b22a6d5b7" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Data Engineer-Airflow Project-Transfer File From S3 Bucket to Another S3 Bucket]]></title>
            <link>https://medium.com/@askintamanli/airflow-project-transfer-file-from-s3-bucket-to-another-s3-bucket-ac580f2da3e6?source=rss-a1ccf8e188c5------2</link>
            <guid isPermaLink="false">https://medium.com/p/ac580f2da3e6</guid>
            <category><![CDATA[python]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[docker]]></category>
            <category><![CDATA[airflow]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Aşkın TAMANLI]]></dc:creator>
            <pubDate>Tue, 18 Jul 2023 13:33:05 GMT</pubDate>
            <atom:updated>2023-07-25T09:25:22.240Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*EJsrBgyGTHXpRC-FREhixw.png" /></figure><p>Hello everyone! We will transfer the data where loacated in our AWS S3 Bucket to another S3 Bucket in this article.</p><p>Let’s dive in.</p><p>First of all, let’s create two AWS S3 Bucket named “airflow-project-source-bucket” and “airflow-project-destination-bucket”</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_Ip9nTIpzDQFgscl4Jmy2Q.png" /></figure><p>Create a folder named “data” for “airflow-project-source-bucket” and load your data in it.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*rsJZ3yDyAn1UXO1DHaw3Jw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*R1IV_TsncLlfwH9tT8E6MQ.png" /></figure><p>Now, open Airflow Webserver → Admin → Connections</p><p>Fill in the fields named Connection Id, Connection Type, AWS Access Key ID, AWS Secret Access Key and save. No need for other fields for now.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xHu0ONIX6ila_CRUGwemFg.png" /></figure><p>Good job. Next step is Airflow. We can create a dag with python.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/ec310ebe87cc9dac473165edb4e43c76/href">https://medium.com/media/ec310ebe87cc9dac473165edb4e43c76/href</a></iframe><p>Everything is ready. We can open the Airflow Webserver and trigger the DAG we just created.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zs17HGXnBEAQ4bCf9pCV3Q.png" /></figure><p>After you trigger the DAG, check the S3 Bucket named “airflow-project-destination-bucket”. You will see the data folder in there.</p><p>THANK YOU FOR READING</p><p>askintamanli@hotmail.com<br><a href="https://github.com/askintamanli/Airflow-with-Docker-from-AWS-S3-to-S3">Github</a><br><a href="https://www.linkedin.com/in/askintamanli/">LinkedIn</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ac580f2da3e6" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Data Engineer (ETL) Project Using Spark with AWS Glue (3/3) — Load Step]]></title>
            <link>https://awstip.com/data-engineer-etl-project-using-spark-with-aws-glue-3-3-load-step-ab62484e027a?source=rss-a1ccf8e188c5------2</link>
            <guid isPermaLink="false">https://medium.com/p/ab62484e027a</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[aws]]></category>
            <category><![CDATA[spark]]></category>
            <category><![CDATA[redshift]]></category>
            <category><![CDATA[etl]]></category>
            <dc:creator><![CDATA[Aşkın TAMANLI]]></dc:creator>
            <pubDate>Fri, 31 Mar 2023 16:01:44 GMT</pubDate>
            <atom:updated>2023-04-05T18:07:45.421Z</atom:updated>
            <content:encoded><![CDATA[<h3>Data Engineer (ETL) Project Using Spark with AWS Glue (3/3) — Load Step</h3><p>Hello everyone. Welcome to last episode of my Data Engineer (ETL) project series. If you did’not read first and second episode of this series, you should read them first. We are gonna load the data to AWS Redshift in this episode.</p><p><strong><em>First off all, I don’t recommend practice with me for this episode. Because it may cost much. But If you practice with me, delete Redshift Cluster after you done with it.</em></strong></p><h3>What is AWS Redshift ?</h3><p>Amazon Redshift is a fully managed, petabyte-scale data warehouse service in the cloud. Resources are automatically provisioned and data warehouse capacity is intelligently scaled to deliver fast performance for even the most demanding and unpredictable workloads. You don’t incur charges when the data warehouse is idle, so you only pay for what you use. You can load data and start querying right away in the Amazon Redshift query editor v2 or in your favorite business intelligence (BI) tool. Enjoy the best price performance and familiar SQL features in an easy-to-use, zero administration environment.</p><h4>What we gonna do step by step in this episode ?</h4><p>1- Create AWS Redshift Cluster</p><p>2- Load the transformed data from S3 to Redshift</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XEI6XwFpvjo_EhACXwW7sQ.jpeg" /></figure><h3>Okay, let’s dive in.</h3><p><strong>1- First of all, we should create another IAM Role for Redshift.</strong></p><blockquote><em>Go to AWS IAM → Roles → Create Role</em></blockquote><blockquote><em>Use cases for other AWS services : Select </em>Redshift — Customizable</blockquote><blockquote><em>Add permissions → Search and Select ‘AdministratorAccess’</em></blockquote><blockquote><em>Role name : ‘</em><a href="https://us-east-1.console.aws.amazon.com/iamv2/home?region=us-east-1#/roles/details/IAM-Role-etl-project"><em>IAM-Role-etl-project</em></a><em>-redshift’</em></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Ju5fCSP3HQpZcBr85HMa2A.png" /></figure><p><strong>1.1- Let’s create AWS Redshift Cluster.</strong></p><blockquote><em>Go to AWS Redshift → Clusters → Create cluster</em></blockquote><blockquote>Cluster identifier : ‘etl-project-cluster’</blockquote><blockquote>Node type : dc2.large (lowest price for per node)</blockquote><blockquote>Number of nodes : 1</blockquote><blockquote>Mark the box that load sample data</blockquote><blockquote>Configure the admin user name and password</blockquote><blockquote>Associated IAM roles → Select<strong> ‘</strong>I<a href="https://us-east-1.console.aws.amazon.com/iamv2/home?region=us-east-1#/roles/details/IAM-Role-etl-project">AM-Role-etl-project</a>-redshift’</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*K2ryY7ORYJQyqmABVj3E9w.png" /></figure><p><strong>1.2- We created a cluster. Let’s view our database with editor.</strong></p><blockquote>AWS Redshift → Cluster → ‘etl-project-cluster’ → Query Data v2</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Jr1P4dNmszD3m07xAX4XwQ.png" /></figure><p><em>‘dev’ and ‘sample_data_dev’ are database names. You can create notebook or editor page and you can run SQL codes.</em></p><p><strong>2.1 Everything looks good. Let’s load transformed data from s3 to ‘dev’ database. Firstly, we should create a table. Let’s write some SQL for create table.</strong></p><pre>CREATE TABLE etl_project_transformed_data_table(<br>    &quot;id&quot; INTEGER NULL,<br>    &quot;year_birth&quot; INTEGER NULL,<br>    &quot;education&quot; VARCHAR NULL,<br>    &quot;marital_status&quot; VARCHAR NULL,<br>    &quot;income&quot; INTEGER NULL,<br>    &quot;dt_customer&quot; DATE NULL<br>) ENCODE AUTO;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*krIakRpGH2kdoGrGRSxH4Q.png" /></figure><p><strong>2.2 Now, load the data.</strong></p><blockquote>Copy the S3 URI of transformed data which csv, paste it to ‘from’ field</blockquote><blockquote>Copy the ARN of IAM Role ‘IAM-Role-etl-project-redshift’, paste it IAM_ROLE field.</blockquote><pre>COPY etl_project_transformed_data_table<br>FROM &#39;s3://etl-project-for-medium/etl-project-for-medium-database/transformed_data/part-00000-6429f588-c5f4-4f6e-88df-b8bd3506113e-c000.csv&#39;<br>IAM_ROLE &#39;arn:aws:iam::835769464848:role/IAM-Role-etl-project-redshift&#39;<br>IGNOREHEADER 1<br>DELIMITER &#39;,&#39;;</pre><p><strong>2.3 Let’s check our table.</strong></p><pre>SELECT * FROM etl_project_transformed_data_table</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*SBmVVceN22aAYEFLSeWq4g.png" /></figure><p><strong>2.4 Cool. Let’s query the data.</strong></p><pre>SELECT education, COUNT(id), AVG(income)<br>FROM etl_project_transformed_data_table<br>GROUP BY education</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/593/1*t_imAN-qEXnlnQT6Y3lgiA.png" /></figure><h4>That’s it. This is the end of this project series. I really appreciate you for reading this series.</h4><ul><li>e-mail: askintamanli@hotmail.com</li><li><a href="https://www.linkedin.com/in/askintamanli/">LinkedIn</a></li><li><a href="https://github.com/askintamanli/Data-Engineer-ETL-Project-Using-Spark-with-AWS-Glue">Github</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ab62484e027a" width="1" height="1" alt=""><hr><p><a href="https://awstip.com/data-engineer-etl-project-using-spark-with-aws-glue-3-3-load-step-ab62484e027a">Data Engineer (ETL) Project Using Spark with AWS Glue (3/3) — Load Step</a> was originally published in <a href="https://awstip.com/">AWS Tip</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[ETL Project Using Spark with AWS Glue — Transform Step ( 2/3 )]]></title>
            <link>https://awstip.com/etl-project-using-spark-with-aws-glue-transform-step-2-3-de1e61d7415a?source=rss-a1ccf8e188c5------2</link>
            <guid isPermaLink="false">https://medium.com/p/de1e61d7415a</guid>
            <category><![CDATA[python]]></category>
            <category><![CDATA[aws]]></category>
            <category><![CDATA[spark]]></category>
            <category><![CDATA[etl]]></category>
            <category><![CDATA[data-engineer]]></category>
            <dc:creator><![CDATA[Aşkın TAMANLI]]></dc:creator>
            <pubDate>Mon, 27 Mar 2023 08:37:23 GMT</pubDate>
            <atom:updated>2023-04-05T18:07:42.305Z</atom:updated>
            <content:encoded><![CDATA[<h3><a href="https://medium.com/@askintamanli/etl-project-using-spark-with-aws-glue-extract-step-1-3-8710ce27264b?source=user_profile---------1----------------------------">Data Engineer (ETL) Project Using Spark with AWS Glue (2/3) — Transform Step</a></h3><p>Hello everyone. Welcome to 2. episode of my Data Engineer (ETL) project series. If you did’not read first episode of this series, you should read it first. We are gonna transform the data using Spark in this episode.</p><p><strong><em>First off all, I don’t recommend practice with me for this episode. Because it may cost much. But If you practice with me, don’t save the job an delete the job after you done with it.</em></strong></p><h3>What is Apache Spark ?</h3><p>Apache Spark is a data processing framework that can quickly perform processing tasks on very large data sets, and can also distribute data processing tasks across multiple computers, either on its own or in tandem with other distributed computing tools. Actually, we can use Apache Spark on many platform such as local, AWS(Glue, EMR, etc..), Azure, GCP, Kubernetes, etc..</p><h3>What we gonna do step by step in this episode ?</h3><p>1- Create Glue Studio Notebook.</p><p>2- Transform data using Spark.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*QImM4n5rN1Inyx1TjlNhbA.jpeg" /></figure><h4>Okay, let’s dive in.</h4><p><strong>1- Firstly we create ETL Job in AWS Glue.</strong></p><blockquote>Go to AWS Glue → Data Integration and ETL → Interactive Sessions → Notebooks</blockquote><blockquote>Job name: ‘etl-project-for-medium-job’</blockquote><blockquote>IAM Role → Choose the role that we created first episode of series → ‘<em>’IAM-Role-etl-project’</em></blockquote><blockquote>Kernel : Spark</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*W6SxK-37NtoY2U2agtWe5g.png" /></figure><p>2.1 Okay, let’s write some PySpark code for transform the data. Please check the codes, I wrote the codes explaining.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/f96ad27879a86a1c4e97572138f31053/href">https://medium.com/media/f96ad27879a86a1c4e97572138f31053/href</a></iframe><p>2.2 Let’s check our ‘transformed_bucket’.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*bI5Hj4Ft3SsXRKv6Aqmf2Q.png" /></figure><p>Everything looks good. Select the one of them and download. You will see the results.</p><h3>In the next episode, we are going to load transformed data from S3 to Redshift.</h3><p>THANK YOU FOR READING.</p><ul><li>e.mail : askintamanli@hotmail.com</li><li><a href="https://www.linkedin.com/in/askintamanli/">LinkedIn</a></li><li><a href="https://github.com/askintamanli/Data-Engineer-ETL-Project-Using-Spark-with-AWS-Glue">Github</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=de1e61d7415a" width="1" height="1" alt=""><hr><p><a href="https://awstip.com/etl-project-using-spark-with-aws-glue-transform-step-2-3-de1e61d7415a">ETL Project Using Spark with AWS Glue — Transform Step ( 2/3 )</a> was originally published in <a href="https://awstip.com/">AWS Tip</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[ETL Project Using Spark with AWS Glue — Extract Step ( 1/3 )]]></title>
            <link>https://awstip.com/etl-project-using-spark-with-aws-glue-extract-step-1-3-8710ce27264b?source=rss-a1ccf8e188c5------2</link>
            <guid isPermaLink="false">https://medium.com/p/8710ce27264b</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[spark]]></category>
            <category><![CDATA[aws]]></category>
            <category><![CDATA[aws-glue]]></category>
            <category><![CDATA[etl]]></category>
            <dc:creator><![CDATA[Aşkın TAMANLI]]></dc:creator>
            <pubDate>Sat, 25 Mar 2023 12:59:48 GMT</pubDate>
            <atom:updated>2023-04-05T18:07:34.419Z</atom:updated>
            <content:encoded><![CDATA[<h3>Data Engineer (ETL) Project Using Spark with AWS Glue (1/3) — Extract Step</h3><p>Hello everyone. Welcome my Data Engineer (ETL) project series. We will talk about ETL job using Apache Spark with AWS Glue. This series is gonna have 3 episodes and this is the first episode of this series. We are going to prepare the data for transform.</p><p><strong><em>First off all, If you going to practice with me, we are going to create AWS Glue Crawler and this will cost us average 0.09$. You can delete crawler after run.</em></strong></p><h4>What is AWS Glue ?</h4><p>AWS Glue is a serverless data integration service that makes it easy for analytics users to discover, prepare, move, and integrate data from multiple sources. You can visually create, run, and monitor extract, transform, and load (ETL) pipelines to load data into your data lakes.</p><h3>What we gonna do step by step in this episode ?</h3><p>1- Create IAM Role for whole project.</p><p>2- Create an S3 bucket and load data to the bucket from our local.</p><p>3- Create AWS Glue database and table.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xbo7amep6MOz5EJJHZgRmA.jpeg" /></figure><h4>Okay, let’s dive in.</h4><p><strong>1- Firstly we should create an IAM Role for this project.</strong></p><blockquote>Go to AWS IAM → Roles → Create Role</blockquote><blockquote>Use cases for other AWS services : Select Glue</blockquote><blockquote>Add permissions → Search and Select ‘AdministratorAccess’</blockquote><blockquote>Role name : ‘<a href="https://us-east-1.console.aws.amazon.com/iamv2/home?region=us-east-1#/roles/details/IAM-Role-etl-project">IAM-Role-etl-project</a>’</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ETYVp2XfrFvoWcnoTAsKEg.png" /></figure><p><strong>2.1- We create a bucket in AWS S3.</strong></p><blockquote>Go to AWS S3 → Buckets → Create bucket</blockquote><blockquote>Bucket name: ‘etl-project-for-medium’</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*q0-UmhGzSSzmUbiVNc_xkg.png" /></figure><p><strong>2.2- We create database folder.</strong></p><blockquote>AWS S3 → Buckets → ‘etl-project-for-medium’ → Create folder</blockquote><blockquote>Folder name: ‘etl-project-for-medium-database’</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*D3uQ1cYhjsf6ZD8QjBrcDA.png" /></figure><p><strong>2.3- We create 2 folder for raw data and transformed data.</strong></p><blockquote>AWS S3 → Buckets → ‘etl-project-for-medium’ → ‘etl-project-for-medium-database’ → 2 x Create folder</blockquote><blockquote>Folder name : ‘raw_data’</blockquote><blockquote>Folder name : ‘transformed_data’</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*NVbtxSVo8pxPgEgl5hWeyg.png" /></figure><p><strong>2.4- We upload our data from local to ‘raw_data’ bucket. You can get the data from my </strong><a href="https://github.com/askintamanli/ETL-Project-Using-Spark-with-AWS-Glue"><strong>github</strong></a><strong>.</strong></p><blockquote>AWS S3 → Buckets → ‘etl-project-for-medium’ → ‘etl-project-for-medium-database’ → ‘raw_data’ → Upload → Add Files → ‘marketing_campaign.csv’</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1INA1OkSlm1vy-CvDbclxg.png" /></figure><p>Okay, everything looks good in our bucket. Now, we should create Glue database and table. And load data to table from AWS S3.</p><p><strong>3.1- Firstly, we create a database.</strong></p><blockquote>Go to AWS Glue → Data Catalog → Databases → Add database</blockquote><blockquote>Database name: ‘etl-project-for-medium-database’</blockquote><blockquote>Location: Copy S3 URI of ‘etl-project-for-medium-database’ folder and paste it to location space.</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_bjyC8HJOrUakrseEjBI0A.png" /></figure><p><strong>3.2- We create a table in database we just created.</strong></p><blockquote>AWS Glue → Data Catalog → Databases → ‘etl-project-for-medium-database’ → Add tables using crawler</blockquote><blockquote>Crawler name: ‘etl-project-for-medium-crawler’</blockquote><blockquote>Data source S3 path: Choose the ‘raw_data’ bucket</blockquote><blockquote>IAM role → Choose IAM role → ’IAM-Role-etl-project’</blockquote><blockquote>Target database → Choose the ‘etl-project-for-medium-database’</blockquote><blockquote>Schedule : On demand</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*UZdEBlvTg7hz-yuUISzkvQ.png" /></figure><p><strong>3.3- Run the crawler.</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*nGpw73gmVP4porhQgPlI1g.png" /></figure><p><strong>3.4- Our crawler is successfully completed. Let’s check the table and schema of our table.</strong></p><blockquote>AWS Glue → Data Catalog → Databases → ‘etl-project-for-medium-database’ → raw_data</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*h7sk5jt5FPtoL2uU7th4UA.png" /></figure><p>We just created our table. Check the data types of columns of data. Everything looks good in our table.</p><h4>In the next episode, we are going to create AWS Glue job and transform the data using Apache Spark.</h4><p>THANK YOU FOR READING.</p><ul><li>e-mail: askintamanli@hotmail.com</li><li><a href="https://www.linkedin.com/in/askintamanli/">LinkedIn</a></li><li><a href="https://github.com/askintamanli/Data-Engineer-ETL-Project-Using-Spark-with-AWS-Glue">Github</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8710ce27264b" width="1" height="1" alt=""><hr><p><a href="https://awstip.com/etl-project-using-spark-with-aws-glue-extract-step-1-3-8710ce27264b">ETL Project Using Spark with AWS Glue — Extract Step ( 1/3 )</a> was originally published in <a href="https://awstip.com/">AWS Tip</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[RFM ANALYSIS]]></title>
            <link>https://medium.com/@askintamanli/rfm-analysis-aa6028913215?source=rss-a1ccf8e188c5------2</link>
            <guid isPermaLink="false">https://medium.com/p/aa6028913215</guid>
            <category><![CDATA[rfm]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[rfm-analysis]]></category>
            <category><![CDATA[crm]]></category>
            <dc:creator><![CDATA[Aşkın TAMANLI]]></dc:creator>
            <pubDate>Fri, 27 Jan 2023 00:24:52 GMT</pubDate>
            <atom:updated>2023-04-10T11:57:15.092Z</atom:updated>
            <content:encoded><![CDATA[<p>Hello everyone. I hope everyone is doing great. Today’s topic is RFM Analysis.</p><blockquote><strong><em>What</em></strong> is RFM Analysis ? <br><strong><em>Why</em></strong> we use RFM Analysis ?<br><strong><em>Where</em></strong> is RFM Analysis used ?<br><strong><em>How </em></strong>to use RFM Analysis for customer segmentation ?<br><strong>When</strong> is RFM Analysis used ?</blockquote><p>Let’s discuss these topics :)</p><h3><strong><em>WHAT ?</em></strong></h3><p>RFM analysis is a popular segmentation technique that studies data-driven customer behavior. According to this analysis, a classification is made and special marketing strategies can be developed by looking at the segmentation.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OEQHAHbee5sB55PsgvcBzg.png" /></figure><p>RFM = Recency + Frequency + Monetary</p><p>Recency: How recently the customer made a purchase ?<br>Frequency: Frequency of customer’s purchase ? <br>Monetary: How much money the customer spends on purchases ?</p><p>Generally, we give points 1 to 5 for every customer in each category.</p><h3>WHY ?</h3><p>Have you ever heard Pareto Principle. <br>“80% of your profits come from 20% of your customers.”</p><p>If we classify the customers, we can make special marketing strategies for each classifications.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*B2A4BP5tpcDz0JznfEh1jQ.png" /></figure><p>For an businesses, the knowledge and insight it has about each of its customers is the most valuable asset of the enterprise. The secret of future commercial success is understanding and analyzing data of customers and constantly transforming your business according to the results of the analysis. That’s why we use RFM Analysis.</p><h3>WHERE ?</h3><p>In all businesses that have customers.</p><h3>WHEN ?</h3><p>Let’s say your sales are going down or your sales are going straight. You want to do something different. You decided to make some offers for customers. Everything is okay for now but you realize that the offers are not compatible for each customer. It is the time that you can make customer segmentation with RFM Analysis.</p><h3>HOW ?</h3><p>In this article, I am gonna use python for RFM Analysis. This will be basic RFM Analysis, there is not gonna data prepering, data cleaning, vs..</p><p>Let’s begin.</p><pre>import pandas as pd<br>import datetime as dt<br><br>df_2010_2011 = pd.read_excel(&quot;online_retail_II.xlsx&quot; , sheet_name=&quot;Year 2010-2011&quot;)<br>df = df_2010_2011.copy()<br>#Read data, to dataframe and copy dataframe<br>df.head()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*tCY_FSY5sOdGJTuMm3APlg.png" /><figcaption><em>This dataset includes the sales of a UK-based online store between 01/12/2009–09/12/2011</em></figcaption></figure><pre>df = df.dropna() #General basic cleaning<br><br>df[&#39;TotalPrice&#39;] = df[&#39;Price&#39;]*df[&#39;Quantity&#39;] #Add a new column named TotalPrice by price * quantity<br><br>today = dt.datetime(2011,12,11) #date of today (2011-12-11)<br><br>df = df[df[&#39;TotalPrice&#39;] &gt; 0]  #Total price should more than 0<br><br>df_rfm = df.groupby(&quot;Customer ID&quot;).agg({ #Group by Customer ID<br>    &quot;InvoiceDate&quot; : lambda date: (today - date.max()).days, #Rececny<br>    &quot;Invoice&quot; : lambda num: len(num),                       #Frequency <br>    &quot;TotalPrice&quot; : lambda TotalPrice:TotalPrice.sum()})     #Monetary <br><br>df_rfm.columns = [&quot;Recency&quot; , &quot;Frequency&quot; , &quot;Monetary&quot;] #New column names for new dataframe<br><br>df_rfm.head()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/451/1*SFrAFRZy_hdGYYliYnFb8w.png" /></figure><pre>df_rfm[&quot;RecencyScore&quot;] = pd.qcut(df_rfm[&#39;Recency&#39;], 5, labels = [5,4,3,2,1])     #Recency score from Receny column<br>df_rfm[&quot;FrequencyScore&quot;] = pd.qcut(df_rfm[&#39;Frequency&#39;], 5, labels = [1,2,3,4,5]) #Frequency score from Frequency column<br>df_rfm[&quot;MonetaryScore&quot;] = pd.qcut(df_rfm[&#39;Monetary&#39;], 5, labels = [1,2,3,4,5])   #Monetary score from Monetary column<br><br>df_rfm[&quot;RFM_SCORE&quot;] = df_rfm[&#39;RecencyScore&#39;].astype(str) + df_rfm[&#39;FrequencyScore&#39;].astype(str) + df_rfm[&#39;MonetaryScore&#39;].astype(str)<br>#Find RFM Score by new columns we created<br>df_rfm.head()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*B-zjuUEmpUIKJfJZb0bjJA.png" /></figure><pre>classification = {    #From RFM score to segmentation<br>    r&#39;[1-2][1-2]&#39;: &#39;Hibernating&#39;,<br>    r&#39;[1-2][3-4]&#39;: &#39;At Risk&#39;,<br>    r&#39;[1-2]5&#39;: &#39;Can\&#39;t Loose&#39;,<br>    r&#39;3[1-2]&#39;: &#39;About to Sleep&#39;,<br>    r&#39;33&#39;: &#39;Need Attention&#39;,<br>    r&#39;[3-4][4-5]&#39;: &#39;Loyal Customers&#39;,<br>    r&#39;41&#39;: &#39;Promising&#39;,<br>    r&#39;51&#39;: &#39;New Customers&#39;,<br>    r&#39;[4-5][2-3]&#39;: &#39;Potential Loyalists&#39;,<br>    r&#39;5[4-5]&#39;: &#39;Champions&#39;<br>}<br><br>df_rfm[&#39;Classification&#39;] = df_rfm[&#39;RecencyScore&#39;].astype(str) + df_rfm[&#39;FrequencyScore&#39;].astype(str)<br>df_rfm[&#39;Classification&#39;] = df_rfm[&#39;Classification&#39;].replace(classification, regex=True)<br>#Defined classification dictionary to our dataframe<br>df_rfm.head()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XoszwXSrRpbZID99YsyugQ.png" /></figure><pre>df_rfm[[&quot;Classification&quot;, &quot;Recency&quot;,&quot;Frequency&quot;,&quot;Monetary&quot;]].groupby(&quot;Classification&quot;).agg([&quot;min&quot;,&quot;max&quot;,&quot;mean&quot;,&quot;sum&quot;,&quot;count&quot;]).round()<br>#GroupBy and aggregate</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*aNrvyGB4-rTmbLFAhv826g.png" /></figure><p>Okay, it’s ready. Let’s discuss and take action for couple of segment :)</p><h4><strong>1- Champions</strong></h4><blockquote>There are 606 person in this segment.<br>Average of last shopping was 6 days ago.<br>Average shopping frequency is 289 times.<br>Total income is 4.218.315 $</blockquote><h4>What can we do for “<em>Champions”</em> ?</h4><p>Champions are the best customers who buy last, most often, and generate the highest returns for the company. Communicating with people in the Champions segment will make them feel valued and appreciated. These customers probably make up a disproportionately high percentage of total revenues, so focusing on keeping them happy should be a top priority. For example, personal messages can be sent, such as congratulating birthdays. They may be early adopters of new products, they can help us promote our brand by communicating our new products free of charge.</p><h4>2- New Customers</h4><blockquote>There are 57 person in this segment.<br>Average of last shopping was 7 days ago.<br>Average shopping frequency is 8 times.<br>Total income is 206.266 $</blockquote><h4>What can we do for “<em>New Customers” </em>?</h4><p>New Customers is the newcomer to the company. Probably the least number of people in this segment. It can be beneficial to engage these customers by offering campaigns and special offers that will increase their loyalty to the company.</p><h4>3- <strong>Can’t Loose</strong></h4><blockquote>There are 84 person in this segment.<br>Average of last shopping was 144 days ago.<br>Average shopping frequency is 182 times.<br>Total income is 199.139 $</blockquote><h4>What can we do for “<em>Can’t Loose”</em><strong> </strong>?</h4><p>Can’t Loose is one of the segments that should be given importance. This segment is the segment we do not want to lose. It is a bad result that their last purchase was made long time ago. They actually shopping but don’t shopping again for a long time. We can earn them back by recommend new products, giving special promotions and money points. We can sending e-mails and text message. We can do surveys to find out what went wrong.</p><p>You can reach source code of this RFM Analysis from my <a href="https://github.com/askintamanli/Data-Science-RFM-Analysis-Project-W-TH-SQL-Python-Power-BI">Github</a>. <br><a href="https://archive.ics.uci.edu/ml/datasets/Online+Retail+II">Reach to data</a></p><p>THANK YOU FOR READING</p><p>askintamanli@hotmail.com<br><a href="https://github.com/askintamanli">Github</a><br><a href="https://www.linkedin.com/in/askintamanli/">LinkedIn</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=aa6028913215" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Types of Relationship in Database]]></title>
            <link>https://medium.com/@askintamanli/types-of-relationship-in-database-2e6132dda9a8?source=rss-a1ccf8e188c5------2</link>
            <guid isPermaLink="false">https://medium.com/p/2e6132dda9a8</guid>
            <category><![CDATA[database-design]]></category>
            <category><![CDATA[servers]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[sql-server]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Aşkın TAMANLI]]></dc:creator>
            <pubDate>Thu, 03 Mar 2022 19:14:28 GMT</pubDate>
            <atom:updated>2022-03-03T19:15:01.845Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/915/1*ji2mgRDNTQsmeJlCQfe2ng.png" /></figure><p>Hi everyone. In this article, I explain the types of relationship in database.</p><h3>1) One to One</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*DfzJeH_deuZDm8GtZDDcYg.png" /></figure><p>Let’s say we have two tables. If you look close to tables, you will notice that there is special details for each products in “PRODUCT DETAILS” table.</p><p>For example, we choose id=1 in “PRODUCTS” table and there is just one id=1 in “PRODUCT DETAILS” table. There is no other id=1.</p><p>So, we call “<strong><em>One to One</em></strong>” kind of this relationship.</p><h3>2) One to Many</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/947/1*tIN7Ms065WMGpJ9pVhrgyg.png" /></figure><p>Look at the tables. We have two tables again. You will ask me why we need extra table for category ? We would add “CATEGORY” part to “PRODUCTS” table, right ?</p><p>Linking the tables in this way will help us in the future. Think That way, if I want to change category of “Iphone 13 Pro Max”, I just change name category name from “CATEGORY” table. It’s so easy with that way ha ? :)</p><p>Each product can only have one category but each category can have more than one product. So, we call “<strong><em>One to Many</em></strong>” kind of this relationship.</p><h3>3) Many to Many</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/791/1*SyeFSGiaPbvvB2KIMjJfaQ.png" /></figure><p>We learned one to many relationship. One to many relationship is only one-way. May relationship is be bidirectional ? Why not :)</p><p>Each product can have more than one category and an also, each category can have more than one product. Like you see relationship may be bidirectional. If the relationship is bidirectional, we call “<strong><em>Many to Many</em></strong>” kind of this relationship.</p><p>THANK YOU FOR READING. I HOPE THIS INFORMATION WILL HELP YOU.</p><ul><li>askintamanli@hotmail.com</li><li><a href="https://github.com/askintamanli">Github</a></li><li><a href="http://linkedin.com/in/a%C5%9Fk%C4%B1n-tamanli-a244a91a5">LinkedIn</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=2e6132dda9a8" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Most Usefull Pandas Functions for Data Cleaning]]></title>
            <link>https://medium.com/@askintamanli/most-usefull-pandas-functions-for-data-cleaning-e66edcddbd74?source=rss-a1ccf8e188c5------2</link>
            <guid isPermaLink="false">https://medium.com/p/e66edcddbd74</guid>
            <category><![CDATA[pandas]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[pandas-dataframe]]></category>
            <category><![CDATA[data-cleaning]]></category>
            <category><![CDATA[data-analysis]]></category>
            <dc:creator><![CDATA[Aşkın TAMANLI]]></dc:creator>
            <pubDate>Fri, 21 Jan 2022 21:00:11 GMT</pubDate>
            <atom:updated>2022-03-03T19:25:44.298Z</atom:updated>
            <content:encoded><![CDATA[<h3>Most Useful Pandas Functions for Data Cleaning</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*RfD-MyPq5rEP71Nl7ItqFw.jpeg" /></figure><p>The dataframe we want to process may not always be clear. While we work with columns, may not have value of some rows. Similarly, while we work with rows, may not have value of some columns. In this case, may need to clean that none values.</p><p>Check the dataframe below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/574/1*ydaH9VysLfDH22SFXuAexw.png" /><figcaption>main dataframe</figcaption></figure><p>None values is shown with <strong>“NaN”</strong>.</p><h4>1) drop function (delete column or row)</h4><p>First of all, we have to learn how to delete some row or column. We call drop() function for delete.</p><blockquote><em>Let’s say “</em><strong><em>df</em></strong><em>” to our dataframe and delete “</em><strong><em>Age</em></strong><em>” column. Default axis value equals to zero. İf we want to delete column, we have to define one. </em><strong>“df.drop(“Name” , axis= 1)”</strong></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/328/1*e3gYB0XW7zPdcp4Dw3lCag.png" /><figcaption>result of “ <em>df.drop(“Name” , axis= 1)</em> ”</figcaption></figure><blockquote><em>Let’s delete index number 2.</em> <strong>“df.drop(2 , axis= 0) or df.drop(2)”</strong></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/545/1*vgcQArGKoY2WHjGEF9pQ4Q.png" /><figcaption>result of “ <em>df.drop(2)</em> ”</figcaption></figure><h4>2) isnull and notnull functions (find NaN and not NaN values)</h4><p>If we want to see null and not null values, we use this function.</p><blockquote><em>If value is null (NaN), </em><strong><em>isnull() </em></strong><em>function return True. We can use </em><strong><em>notnull() </em></strong><em>function for opposite results. Let’s see how it works. </em><strong><em>“</em>df.isnull()”</strong></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/430/1*CG6WPovqO9ad4K2BOu1Q0A.png" /><figcaption>result of “ <em>df.isnull()</em> ”</figcaption></figure><blockquote><em>Let’s see not null values in “</em><strong><em>Name</em></strong><em>” column. </em>“<strong>df[df[“Name”].notnull()]”</strong></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/566/1*ssdqZAjk6c3MQtVK5RNfdg.png" /><figcaption>result of “ <em>df[df[“Name”].notnull()]</em> ”</figcaption></figure><blockquote><em>We may need number of True or False values. We can learn this way for each column. </em><strong>“df.isnull().sum()”</strong></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/263/1*1jA9LqvFlTAtopCdsFEJCw.png" /><figcaption>result of “ <em>df.isnull().sum()</em> ”</figcaption></figure><blockquote><em>If we want to see total number of True or False number, we call the </em><strong><em>sum() </em></strong><em>function again.</em> <strong>“df.isnull().sum().sum()”</strong></blockquote><h4>3) dropna function (delete NaN values)</h4><p>We learned how to find NaN values and how to delete column or row. Let’s see how to delete NaN values from our dataframe. We should call <strong>dropna()</strong> function for delete NaN values.</p><blockquote>If row at least have one NaN value, dropna() function delete the row. <strong>”df.dropna()”</strong></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/543/1*knKfFAsAJs8xmwJZS7zhlw.png" /><figcaption>result of “ <em>df.dropna()</em> ”</figcaption></figure><blockquote>We can apply same method for columns.<strong>”<em>df.dropna(axis=1)</em>”</strong></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/489/1*r5aFGS87G_oHDtpD1AToAQ.png" /><figcaption>result of “ <em>df.dropna(axis=1) </em>”</figcaption></figure><blockquote><em>Actually dropna() function have “</em><strong><em>how</em></strong><em>” parametre and default parametre value equals to “</em><strong><em>any</em></strong><em>”. If only </em><strong><em>all </em></strong><em>row/column have NaN values, dropna() function delete the row/column. Let’s see for rows. </em><strong><em>“df.dropna(how=“all” ,axis=0)”</em></strong></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/562/1*jOCKGLN1sTW1JNb5T2lBag.png" /><figcaption>result of “ <em>df.dropna(how=”all”) </em>”</figcaption></figure><blockquote><em>We can operate on specific columns with “</em><strong><em>subset</em></strong><em>” parametre. </em><strong><em>df.dropna(subset= [“Name”,”Age&quot;])</em></strong></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/430/1*t-gQcaOkMLgOF3AX1uqQ9g.png" /><figcaption>result of “ <em>df.dropna(subset= [“Name”,”Age”]) </em>”</figcaption></figure><h4>4. fillna function (new word instead of NaN)</h4><blockquote>We can define new word instead of NaN with this function. <strong><em>“df.fillna(value=“NO INPUT” )”</em></strong></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/660/1*E4MZfPj0NtzhnTXhq-wIPA.png" /><figcaption>result of “ <em>df.fillna(value=“NO INPUT” ) </em>”</figcaption></figure><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/f9bf4c27b278738dd8386fcf72f7ad3c/href">https://medium.com/media/f9bf4c27b278738dd8386fcf72f7ad3c/href</a></iframe><p>THANK YOU FOR READING. I HOPE THIS INFORMATION WILL HELP YOU.</p><ul><li>askintamanli@hotmail.com</li><li><a href="https://github.com/askintamanli">Github</a></li><li><a href="http://linkedin.com/in/a%C5%9Fk%C4%B1n-tamanli-a244a91a5">LinkedIn</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e66edcddbd74" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>