<?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 Hashir Alam on Medium]]></title>
        <description><![CDATA[Stories by Hashir Alam on Medium]]></description>
        <link>https://medium.com/@hashir.hak49?source=rss-4b23311915f0------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*8BJS2IEdaZfqbyYlfoPyGg.jpeg</url>
            <title>Stories by Hashir Alam on Medium</title>
            <link>https://medium.com/@hashir.hak49?source=rss-4b23311915f0------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Fri, 20 Nov 2026 00:48:24 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@hashir.hak49/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[Real-time Data Streaming using Confluent Kafka & SingleStore]]></title>
            <link>https://medium.com/@hashir.hak49/real-time-data-streaming-using-confluent-kafka-singlestore-f1badc579da?source=rss-4b23311915f0------2</link>
            <guid isPermaLink="false">https://medium.com/p/f1badc579da</guid>
            <category><![CDATA[singlestore-memsql]]></category>
            <category><![CDATA[confluent-kafka]]></category>
            <category><![CDATA[kafka]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[kafka-producer]]></category>
            <dc:creator><![CDATA[Hashir Alam]]></dc:creator>
            <pubDate>Mon, 03 Jul 2023 10:59:13 GMT</pubDate>
            <atom:updated>2023-07-28T11:21:00.589Z</atom:updated>
            <content:encoded><![CDATA[<h3><strong>Summary</strong></h3><ul><li>Configure Confluent Kafka for cluster and topic and use it in python to produce data</li><li>Python producer to produce accelerometer data (ms) to a Kafka topic</li><li>Consume data into a row-store table using SingleStore pipeline</li><li>Create a procedure to dump row-store table into a historical column-store table once every day</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/754/1*MLqsqO6z6hOTjdJlybZIkw.png" /><figcaption>Pipeline Architecture</figcaption></figure><h3><strong>Confluent Kafka</strong></h3><p>First step will be to configure Confluent Kafka to create a cluster named MEMSQL_CLUSTER (SingleStore was previously named MemSQL) which can be done using a trial account. After creating a cluster API Keys need to be generated to be able to access the cluster within code using admin client in next section.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*LBd3ETPbZQCnGjySQoyiGA.png" /><figcaption>API Keys Portal View</figcaption></figure><p>Topic can be created using user interface on Confluent cloud portal given the properties for number of partitions, retention period for messages and so on.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*SeK4uf8vcvn15HNe_KUAdA.png" /><figcaption>Topic Creation Portal View</figcaption></figure><p>For code access to confluent kafka cluster, a configuration properties file can be created with necessary configuration to be used to access the cluster which looks like:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/846/1*g6YLqEJFby3PT-_Qm8S76w.png" /><figcaption>Configuration Properties</figcaption></figure><p>This can further be used create admin client and functions to create, read and alter topics and their properties. A function provided by Confluent Kafka is used to read the configuration properties file which can be fetched by going to Kafka client from left side and choosing python in our case.</p><pre>def read_ccloud_config(config_file):<br>    conf = {}<br>    with open(config_file) as fh:<br>        for line in fh:<br>            line = line.strip()<br>            if len(line) != 0 and line[0] != &quot;#&quot;:<br>                parameter, value = line.strip().split(&#39;=&#39;, 1)<br>                conf[parameter] = value.strip()<br>    return conf</pre><p>Admin client is created and functions to create and alter properties for a topic can done using Python as</p><pre>from confluent_kafka.admin import AdminClient, NewTopic, ConfigResource<br><br># create new topic and return results dictionary<br>def create_topic(admin, topic):<br>    new_topic = NewTopic(topic, num_partitions=6, replication_factor=3) <br>    result_dict = admin.create_topics([new_topic])<br>    for topic, future in result_dict.items():<br>        try:<br>            future.result()  # The result itself is None<br>            print(&quot;Topic {} created&quot;.format(topic))<br>        except Exception as e:<br>            print(&quot;Failed to create topic {}: {}&quot;.format(topic, e))<br><br># set max.message.bytes for topic<br>def set_max_size(admin, topic, max_k):<br>    config_dict = {&#39;max.message.bytes&#39;: str(max_k*1024)}<br>    resource = ConfigResource(&#39;topic&#39;, topic, config_dict)<br>    result_dict = admin.alter_configs([resource])<br>    result_dict[resource].result()<br><br># Create Admin client<br>config = read_ccloud_config(&quot;client.properties&quot;)<br>admin = AdminClient(config)<br>topic_name = &#39;acceleration_streamf&#39;<br>max_msg_k = 60<br><br>create_topic(admin, &#39;acceleration_streamf&#39;)<br>set_max_size(admin, topic_name, 60*2048)</pre><p>Next, A Python Producer will write the data into the acceleration_streamf topic as below. A Dataframe is used as a data source (Data is sourced from Kaggle)from which individual rows are written into the topic as JSON payload with date as key.</p><pre>from confluent_kafka import Producer<br>producer = Producer(config)<br><br>for idx in range(dft.shape[0]):<br>    x = dft.iloc[idx]<br>    y = x.to_json()<br>    producer.produce(&quot;acceleration_streamf&quot;, key=x[&#39;date&#39;].str, value=y)<br>    sleep(0.5)</pre><p>The data written can be viewed on Confluent portal in Topic.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*tj3fwITYKnWT0kAi-UFEbg.png" /><figcaption>Kafka Topic Messages</figcaption></figure><h3><strong>SingleStore</strong></h3><p>Next step is now to consume this data into a row-store table in SingleStore using a pipeline. The tasks include:</p><ul><li>Creation of real time consumption table with 2 persisted computed columns named as acceleration_w and gyro_w. <em>A row-store table is stored in memory in a SingleStore cluster in a row format.</em></li></ul><pre>create rowstore table accelerometer_realtime(<br>date date,<br>time varchar(50),<br>username varchar(50),<br>wrist int,<br>activity int,<br>acceleration_x float,<br>acceleration_y float,<br>acceleration_z float,<br>acceleration_w as sqrt(power(acceleration_x,2) + power(acceleration_y,2) + power(acceleration_z,2)) persisted float,<br>gyro_x float,<br>gyro_y float,<br>gyro_z float,<br>gyro_w as sqrt(power(gyro_x,2) + power(gyro_y,2) + power(gyro_z,2)) persisted float<br>);</pre><ul><li>Creation of procedure to read JSON data and convert it into tabular format into the table created. <em>A procedure can be called within a SingleStore pipeline using query type and :: is used to access keys within JSON object, $ converts values to string and % converts it into double data type.</em></li></ul><pre>DELIMITER // <br>CREATE or REPLACE PROCEDURE accelerometer_data_load(batch query(payload text)) <br>    AS <br>    BEGIN <br>        INSERT INTO accelerometer_realtime(date,time,username,wrist,activity,acceleration_x,acceleration_y,acceleration_z,gyro_x,gyro_y,gyro_z)<br>        SELECT payload::$date, payload::$time,payload::$username,payload::%wrist, payload::%activity,<br>                payload::%acceleration_x,payload::%acceleration_y,payload::%acceleration_z,<br>                payload::%gyro_x,payload::%gyro_y,payload::%gyro_z   <br>                FROM batch;<br>            <br>    END// <br>DELIMITER ;</pre><ul><li>Create pipeline to use this procedure to populate the real time consumption table</li></ul><pre>CREATE or REPLACE PIPELINE accelerometer_kafka_stream<br>  AS LOAD DATA KAFKA &#39;pkc-p11xm.us-east-1.aws.confluent.cloud:9092/acceleration_streamf&#39;  <br>CONFIG &#39;{&quot;sasl.username&quot;: &quot;TQEFQAHE6SWJI22T&quot;,<br>         &quot;sasl.mechanism&quot;: &quot;PLAIN&quot;,<br>         &quot;security.protocol&quot;: &quot;SASL_SSL&quot;,<br>         &quot;ssl.ca.location&quot;: &quot;/etc/pki/ca-trust/extracted/pem/tls-ca-bundle.pem&quot;}&#39;<br>CREDENTIALS &#39;{&quot;sasl.password&quot;: &quot;***********************************************&quot;}&#39;<br>INTO  PROCEDURE accelerometer_data_load;</pre><ul><li>Next is a procedure to dump this real time data from row-store table in a column-store table which contains the historical data and this procedure would run every day at midnight. <em>A column-store table is stored on disk in a sharded manner using a shard key in SingleStore, in absence of shard key it is stored in round robin fashion.</em></li></ul><pre>create table accelerometer_historical(<br>date date,<br>time varchar(50),<br>username varchar(50),<br>wrist int,<br>activity int,<br>acceleration_x float,<br>acceleration_y float,<br>acceleration_z float,<br>acceleration_w float,<br>gyro_x float,<br>gyro_y float,<br>gyro_z float,<br>gyro_w float,<br>shard key(date)<br>);<br><br><br>DELIMITER // <br>CREATE or REPLACE PROCEDURE accelerometer_data_dump() <br>    AS <br>    BEGIN <br>        INSERT INTO accelerometer_historical(date,time,username,wrist,activity,<br>        acceleration_x,acceleration_y,acceleration_z,acceleration_w,gyro_x,gyro_y,gyro_z,gyro_w)<br>        SELECT * from accelerometer_realtime;<br><br>        truncate table accelerometer_realtime;<br>            <br>    END// <br>DELIMITER ;</pre><ul><li>This second procedure can be orchestrated using tools like Apache Airflow etc.</li></ul><h3><strong>Pipeline in Action</strong></h3><p>After running the producer and consumer, data can be seen being populated into the topic and our table.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xabfcu1n0QdcOOk1oeF9oA.png" /><figcaption>Topic Usage Overview</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/1*_XuAQkJq-AzHsDBnuzHB4Q.gif" /><figcaption>SingleStore Table Query</figcaption></figure><p>So our overall pipeline reads data from a file into a dataframe and sends individual rows to Kafka topic twice every second (In a practical case, data from an IOT sensor can be sent directly into a Kafka topic in real-time) and a SingleStore pipeline consumes this data into a row-store table which is dumped every 24 hours into a historical table. Latency of this pipeline is observed to be between 200–500 ms.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f1badc579da" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[ETL Using Apache Nifi and AWS Glue — Part 1]]></title>
            <link>https://medium.com/@hashir.hak49/etl-using-apache-nifi-and-aws-glue-part-1-706c8e38c2be?source=rss-4b23311915f0------2</link>
            <guid isPermaLink="false">https://medium.com/p/706c8e38c2be</guid>
            <category><![CDATA[aws-sns]]></category>
            <category><![CDATA[batch-processing]]></category>
            <category><![CDATA[aws-s3]]></category>
            <category><![CDATA[apache-nifi]]></category>
            <category><![CDATA[data-lake]]></category>
            <dc:creator><![CDATA[Hashir Alam]]></dc:creator>
            <pubDate>Thu, 30 Mar 2023 14:02:11 GMT</pubDate>
            <atom:updated>2023-03-30T14:04:17.973Z</atom:updated>
            <content:encoded><![CDATA[<p><strong>Services used</strong>: Apache Nifi, AWS S3, CloudTrail, SNS and EventBridge.</p><h3>Summary</h3><ul><li>Data used is Chicago Crimes Dataset from Kaggle.</li></ul><p><a href="https://www.kaggle.com/datasets/currie32/crimes-in-chicago">Crimes in Chicago</a></p><ul><li>Nifi in a flow based ETL tool used to design data flows between software systems. We will be using it to ingest data from source which in my case in the local storage but I will be addressing commonly used methodologies to ingest data from SFTP and API.</li><li>After Ingestion Nifi will be used to pre-process this data and store it in the landing zone in AWS S3.</li><li>AWS Glue will then pickup the process further to source data from landing zone and apply transformation jobs and then ultimately migration the transformed data to AWS Redshift.</li><li>2 crawlers one for landing and one for redshift will be used to get data in the Glue data catalog.</li><li>Failure handling is done using EventBridge Rules to check for failures and send log information to SNS topic to be emailed to subscribers (developers).</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/624/1*S5-1zCjDR03rVEm5A8UiZw.png" /><figcaption><em>Fig. ETL Architecture</em></figcaption></figure><p>Taking a look at the Nifi flow (<em>Left side of the flow in Fig. Ingest</em>):</p><ul><li>Files are listed using ListFile processor.</li><li>UpdateAttribute processor then adds new attributes to the flow file to be used to route this file within the Nifi flow</li><li>Files are filtered using RouteOnAttribute processor to only use CSV files.</li><li>These CSV files then pass through another filter differentiate and route local and bucket files to their respective part of flow.</li><li>Local files are then routed to be fetched via FetchFile processor (<em>Upper side of flow in Fig. Process and Upload</em>).</li><li>Another updateAttribute processor adds object key as an attribute.</li><li>The object key is then used by PutS3Object processor to upload this raw file in S3 Raw Zone.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/468/1*9sQHhVrKns-HV0JEMfu_eg.png" /><figcaption>Fig. Ingest</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/629/1*_A2KonZfu9i2ICYGQ0xIoA.png" /><figcaption><em>Fig. Process and Upload</em></figcaption></figure><h3>Nifi Ingestion for Raw Data</h3><p>To look at this flow in detail, starting with Nifi data source, we use list file processor which lists all the files in Input Directory property of the processor. Listing can be scheduled according to the requirements to execute the Nifi flow, 5 sec run schedule here indicates the processor will look for new files in the directory every 5 seconds.</p><p>Moreover, nifi processors create a state based on timestamp for change data capture. The concurrent tasks option here is used to execute the processor in parallel for each flow file (normally used in processing and script execution tasks) to get usage of all nodes and threads in Nifi.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/1*Ng0uzZ6IEUbwFkX8y1Q6Ug.gif" /><figcaption>List Processor Settings and Properties</figcaption></figure><p><strong>SFTP as source</strong></p><p>In case of SFTP, ListSFTP processor is available where required properties (hostname, username, password, ppk etc.) can be used to access the server.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/447/1*oyZ8HO0DPc8UHwK7MtO0Ag.png" /><figcaption>ListSFTP Processor Properties</figcaption></figure><p><strong>API as source</strong></p><p>Lastly, to fetch data via API, normal practice is triggering an API call and parsing script run using python execute command processor and the trigger in this case is a Nifi generated empty flow file which can be scheduled, the output of API call script can be used as a flow file for the rest of the flow OR the output can be stored in an intermediate storage or even directly in Raw Zone and fetched using ListS3 and Fetchs3 processors in Nifi.</p><p>Moving on to the second processor updateAttribute (updateSrcInfo), it adds attributes to the flow files and the attributes added include <em>file.last.modified.date, src_type, src.absolute.path, src.entity</em>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/469/1*8d5V0Pxm_O96lmQhVJYYbA.png" /><figcaption>UpdateAttribute Processor Properties</figcaption></figure><p>Please note that Nifi expression language is used in 2 attributes, <em>file.last.modified.date</em> and <em>src.absolute.path</em>. This expression language can be viewed and used from:</p><p><a href="https://nifi.apache.org/docs/nifi-docs/html/expression-language-guide.html#functions">Apache NiFi Expression Language Guide</a></p><p>Moving on, next processor routeOnAttribute (Filtering_on_CSV) can be viewed as a filter to add conditions within the processor. Here, one condition to check if filename ends with ‘.csv’ is added and rest of the files are discarded from the flow using automatically terminate relationship setting of the processor.</p><p>Next, another routeOnAttribute processor is used this time to check the attribute we added in step 2 (updateSrcInfo) and it checks the src_type to route the file to respective part of the flow.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/1*D8KxHZFFDDH3bt8XW3HUGg.gif" /><figcaption>RouteOnAttribute Processor Properties</figcaption></figure><p>Since we have local files in the flow, local files are routed to FetchFile processor which reads these files from the source directory and the next we use updateAttribute again to add object key (path + filename to be stored in S3 bucket) which is then used by PutS3Object processor to upload this raw file in the Raw Zone of S3 bucket.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/417/1*7kpkOwop4FK203YVb6kfnw.png" /><figcaption>Object Key Processor Properties</figcaption></figure><p>Taking a look at processor properties, our object which we updated in <em>filename</em> variable is used and bucket name is used via pre-defined variable which can be done by right-clicking in Nifi and choosing variables.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/1*rCswaM-sBLqLejYKIYRcIw.gif" /><figcaption>Global Variables</figcaption></figure><p>Access and secret key to access AWS S3 bucket can be defined in the properties but a good practice is to use <em>AWSCredentialsProviderControllerService</em> which is essentially a controller variable defined globally in Nifi and can be used anywhere to provide credentials to access AWS Resources. Simply clicking on the variable will reroute to controller settings and respective keys can be defined in properties.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/1*o57xPJ4Zd2Wa-yz78mlBEw.gif" /><figcaption>Controller Service Properties</figcaption></figure><p>Firstly, there is a need to create the raw zone bucket in S3 before we use the processor to upload files. To create the bucket we just create buckets with default values where we define a region, block all public access and we keep bucket versioning off because we don’t want to keep versions of objects in our bucket.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/1*Z4fvbrquz4Qdml1vZYdCTA.gif" /><figcaption>S3 Bucket Creation</figcaption></figure><h3>Failure Handling using SNS and EventBridge</h3><p>Finally, we have a PutS3Object for our failure bucket where we create a failure bucket similarly to keep files which fail to be uploaded in Raw Zone. We need to create a mechanism to notify the developer whenever files are uploaded in failure bucket using AWS SNS. For this we first need to generate logs for our failure bucket.</p><p>AWS Cloudtrail is used to generate S3 logs. Create trail by going to CloudTrail, it will generate its own bucket in S3 to store logs:</p><ul><li>Choose Data Events</li><li>Choose S3 bucket</li><li>Choose logs to store (Read and Write).</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/1*jZWqsiz_H-tbyTdP9DO5fQ.gif" /><figcaption>CloudTrail Creation</figcaption></figure><p>Next, we create an SNS topic and add subscription to it for notification. We create SNS Topic with default parameters.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/1*EyFREbp_OxL3Z-WF4WI0uQ.gif" /><figcaption>SNS Topic Creation</figcaption></figure><p>Lastly, a rule is created using AWS EventBridge which check failure bucket for new file upload and sends the log to this newly created SNS Topic. For this:</p><ul><li>Create a rule to get object level logs via CloudTrail.</li><li>Event type is PutObject operation.</li><li>Bucket is failure bucket.</li><li>This will trigger this rule and send the log to target (SNS Topic).</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/1*_pT4ZejdTkrPigDAS5Iz7Q.gif" /><figcaption>EventBridge Rule Pattern</figcaption></figure><p>Finally, we subscribe to this topic using Email and enter the email address we wish to receive notification at, it will send a confirmation email for subscription.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*q6-YcCBFEE1mW3XwB9HMyg.png" /><figcaption>Email Subscription to SNS Topic</figcaption></figure><p>In case of object put in failure bucket, it will send a log email which will look like:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/802/1*zfYohFn-_PCXVWnXiLkLSQ.png" /><figcaption>SNS Email Notification</figcaption></figure><h3>Intermediate Data to Landing Zone</h3><p>Now that we have stored our raw data in raw zone, we:</p><ul><li>Fetch raw data from raw zone via Nifi using ListS3 processor.</li><li>Use same methodology as before to add attributes.</li><li>Filter CSV files (<em>Right side of the flow in Fig. Ingest</em> ).</li><li><em>RouteOnAttribute</em> routes it (<em>Lower side of flow in Fig. Process and Upload</em>) to FetchS3</li><li>Use a processor called <em>ExecuteCommand</em> where we execute a python pre-processing script to clean table columns in these CSV files and store them as parquet into out S3 Landing Zone.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/1*mpsl-LQhN7DqwJjxvBJ__Q.gif" /></figure><p>To call AWS Services in python script we use AWS SDK called boto3 to fetch and upload files from S3, the credentials for boto3 are defined in CMD using AWS CLI.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/1*88T8f6cV8nLfP2Wo2Mg_LA.gif" /></figure><p>The code used for pre-processing python script is below:</p><pre>s3_resource = boto3.resource(&#39;s3&#39;)<br><br>fileKey = str(serviceArguments[&#39;filename&#39;])<br>raw_bucket_name = str(serviceArguments[&#39;raw_bucket_name&#39;])<br>landing_bucket_name = str(serviceArguments[&#39;landing_bucket_name&#39;])<br><br>print(&quot;reading file&quot;)<br>obj = s3_resource.Object(raw_bucket_name, fileKey)<br>data = obj.get()[&#39;Body&#39;].read()<br>data = BytesIO(data)            <br>df = pd.read_csv(data)<br>print(&quot;file read&quot;)<br><br>df.drop(columns = [&#39;Unnamed: 0&#39;],inplace=True)<br>df.columns = df.columns.str.replace(&quot; &quot;,&quot;_&quot;)<br>print(df.columns)<br>print(&quot;file processed&quot;)<br><br>return_buffer = BytesIO()<br>df.to_csv(return_buffer, index=False)<br>ret_response = s3_resource.Object(landing_bucket_name, fileKey).put(Body=return_buffer.getvalue(), ServerSideEncryption = &#39;AES256&#39;)<br>    <br>    </pre><p>Files failing to pass through this script are put in failure bucket and SNS notifies the developer again using the trail and rule we previously defined.</p><p>In Part 2, AWS Glue is used to transform data and store it in processed zone and migrate transformed data into AWS Redshift using 2 Pyspark Jobs and this process is automated using Glue ETL Workflow.</p><p><a href="https://medium.com/@hashir.hak49/etl-using-apache-nifi-and-aws-glue-part-2-2146974e3069">ETL Using Apache Nifi and AWS Glue — Part 2</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=706c8e38c2be" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[ETL Using Apache Nifi and AWS Glue — Part 2]]></title>
            <link>https://medium.com/@hashir.hak49/etl-using-apache-nifi-and-aws-glue-part-2-2146974e3069?source=rss-4b23311915f0------2</link>
            <guid isPermaLink="false">https://medium.com/p/2146974e3069</guid>
            <category><![CDATA[aws]]></category>
            <category><![CDATA[aws-glue]]></category>
            <category><![CDATA[aws-redshift]]></category>
            <category><![CDATA[aws-s3]]></category>
            <category><![CDATA[aws-sns]]></category>
            <dc:creator><![CDATA[Hashir Alam]]></dc:creator>
            <pubDate>Thu, 30 Mar 2023 13:29:13 GMT</pubDate>
            <atom:updated>2023-03-30T14:03:55.829Z</atom:updated>
            <content:encoded><![CDATA[<h3>ETL Using Apache Nifi and AWS Glue — Part 2</h3><p><strong>Services used</strong>: AWS Glue, IAM, VPC, SNS, EventBridge and Redshift</p><p>Previously, data was taken into the Landing Zone S3 bucket after pre-processing via Nifi. SFTP Servers or API calls can be made directly in python scripts run by AWS Glue for which global parameters can be defined using AWS Systems Manager Parameter Store.</p><h3>Summary</h3><ul><li>First task is to create a crawler to crawl Landing Zone and get the data in Glue Data Catalog.</li><li>Data is Catalog will be queried using Athena.</li><li>First Pyspark job to transform data is run in Glue and transformed data is stored in Processed Zone in S3 bucket.</li><li>Redshift cluster is created and respective tables are created in “dev” database.</li><li>Second crawler is created to crawl Redshift into Glue Catalog.</li><li>Second Pyspark job maps and migrated data from Processed Zone into Redshift.</li><li>Lastly, EventBridge rule is created for job failure handling to send log information to SNS topic from Part 1.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/624/1*S5-1zCjDR03rVEm5A8UiZw.png" /><figcaption><em>Fig. ETL Architecture</em></figcaption></figure><h3>IAM Role for Glue</h3><p>While creating a crawler for Landing Zone, there is a need to define an IAM Role which will be able to access all required services. This role assumes on behalf of Glue as trusted entity with access for Glue, S3 and Redshift will be used to create Crawlers and Jobs.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qoZT9tKyAN5Pfc8Aw1IqfQ.png" /><figcaption>IAM Role</figcaption></figure><h3>Glue Crawler for Landing Zone</h3><p>Newly created role will be used in creating a crawler which will use landing zone S3 bucket as its source, a database in Glue also needs to be created for the crawler to create and store data for the Catalog. Database name “<em>chicago-datalake</em>” is created. Crawler properties include:</p><ul><li>Role is the TrainingRole we created previously.</li><li>Data Source is S3 Landing Bucket.</li><li>Database is “<em>chicago-datalake</em>” we just created.</li><li>Crawler is run on demand but will be scheduled in ETL Workflow later.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9fw3ZZ0Zg-PJdrSblOAv1w.png" /><figcaption>Landing Crawler Configuration</figcaption></figure><p>Running this crawler will populate landing zone data into Glue Data Catalog in database “<em>chicago-datalake</em>” which can be queried from Athena. <strong><em>Glue Crawler automatically detects different table schemas in source and creates the tables respectively.</em></strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/1*WNcYGfPXAQPMI_DvT7r8dQ.gif" /><figcaption>Query in Athena</figcaption></figure><h3>Glue Data Transformation Job</h3><p>Moving on, first Pyspark job to transform data from landing zone is created in Glue named DataTransform, for properties:</p><ul><li>TrainingRole is used which was previously created.</li><li>Glue 4.0 is chosen because we need Spark 3.3 to use Pyspark API for pandas.</li><li>Number of workers is reduced to 2 because we need to use minimum resources</li><li>Bookmark is enabled to keep job state just like processors in Nifi kept state based on timestamp for change data capture.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/1*pWxxFvwVcI2C-RRZ7xMVEQ.gif" /><figcaption>Transformation Job Configuration</figcaption></figure><p>The pyspark job is just a demo job which creates data and time columns and groups data into 3 separate entities or tables to be stored in Processed Zone, data is read into glue dynamic frame, converted in spark dataframe and then into Pandas dataframe to apply pandas functions (Pandas is used to be able to test out Glue 4.0, transformations can be done in spark dataframe). Many varieties of transformations can be done based on use case so feel free to play around!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/1*PXNhbpOJPOgR5R3SGObf4g.gif" /><figcaption>Processed Data in S3 Bucket</figcaption></figure><pre>import sys<br>from awsglue.transforms import *<br>from awsglue.utils import getResolvedOptions<br>from pyspark.context import SparkContext<br>from awsglue.context import GlueContext<br>from awsglue.job import Job<br>import pyspark.pandas as ps<br>from pyspark.sql.types import StructType,StructField, StringType, IntegerType, LongType, BooleanType, DoubleType<br><br><br>args = getResolvedOptions(sys.argv, [&quot;JOB_NAME&quot;])<br>sc = SparkContext()<br>glueContext = GlueContext(sc)<br>spark = glueContext.spark_session<br>job = Job(glueContext)<br>job.init(args[&quot;JOB_NAME&quot;], args)<br><br>catalog_df = glueContext.create_dynamic_frame.from_options(<br>    format_options={&quot;quoteChar&quot;: &#39;&quot;&#39;, &quot;withHeader&quot;: True, &quot;separator&quot;: &quot;,&quot;},<br>    connection_type=&quot;s3&quot;,<br>    format=&quot;csv&quot;,<br>    connection_options={&quot;paths&quot;: [&quot;s3://chicago-landing-zone&quot;], &quot;recurse&quot;: True},<br>    transformation_ctx=&quot;AmazonS3_node1677232296058&quot;,<br>)<br>df = catalog_df.toDF()<br>print(&quot;Spark columns&quot;)<br>print(df.columns)<br>pdf = df.toPandas()<br>print(&quot;Dataframe columns&quot;)<br>print(pdf.columns)<br>#df.show()<br>df.write.mode(&quot;append&quot;).parquet(&quot;s3://chicago-processed-zone/Chicago_crimes/crime_description&quot;)<br><br>pdf[&#39;crime_date&#39;] = pdf[&#39;Date&#39;].apply(lambda x: x.split(&quot; &quot;)[0])<br>pdf[&#39;crime_time&#39;] = pdf[&#39;Date&#39;].apply(lambda x: x.split(&quot; &quot;)[1])<br><br>df_by_date_district = pdf.groupby([&#39;crime_date&#39;,&#39;District&#39;]).count().reset_index()[[&#39;crime_date&#39;,&#39;ID&#39;,&#39;District&#39;]]<br>print(df_by_date_district.head())<br>sdf_by_date_district = spark.createDataFrame(df_by_date_district)<br>print(&quot;date district&quot;)<br>print(sdf_by_date_district.columns)<br>sdf_by_date_district.write.mode(&quot;append&quot;).parquet(&quot;s3://chicago-processed-zone/Chicago_crimes/date_district&quot;)<br><br>df_arrest_by_type = pdf.groupby([&#39;crime_date&#39;,&#39;Primary_Type&#39;,&#39;Ward&#39;]).count().reset_index()[[&#39;crime_date&#39;,&#39;Primary_Type&#39;,&#39;Ward&#39;,&#39;Arrest&#39;]]<br>print(df_arrest_by_type.head())<br>sdf_arrest_by_type = spark.createDataFrame(df_arrest_by_type)<br>print(&quot;arrest by type&quot;)<br>print(sdf_arrest_by_type.columns)<br>sdf_arrest_by_type.write.mode(&quot;append&quot;).parquet(&quot;s3://chicago-processed-zone/Chicago_crimes/arrest_by_type&quot;)<br><br><br><br>job.commit()</pre><h3>Redshift Cluster</h3><p>Before moving onto second Glue Job, Redshift cluster and respective tables need to be created in the cluster to migrated data into the datawarehouse. Redshift cluster is created with minimum resources (<strong><em>dc2.large 1 node</em></strong>) because Glue and Redshift is not a free service.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/842/1*JvHOvTS77-Nzwbcqko1vXQ.png" /><figcaption>Redshift Cluster Configuration</figcaption></figure><p>After the cluster is created, redshift query editor is used to access the database and create respective tables.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-qtEUrfYJNPEYS7Eq-PzXA.png" /><figcaption>Redsfhit Query Editor</figcaption></figure><p>Firstly, a connection for redshift is created in Glue to be able to access using crawler and glue job.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/1*mtlMc_39N6cDFWeKxO_SpA.gif" /><figcaption>Redshift Connection Configuration</figcaption></figure><h3>Glue Crawler for Redshift</h3><p>Next, second crawler for datawarehouse is created along with the database “<em>chicago-datawarehouse</em>” to be used to store crawler data in Catalog. Crawler properties include:</p><ul><li>Role is the TrainingRole we created previously.</li><li>Data Source is Redshift using the connection we just created.</li><li>Database is “<em>chicago-datawarehouse</em>” we just created.</li><li>Path is given to include all tables in database “dev”.</li><li>Crawler is run on demand but will be scheduled in ETL Workflow later.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/1*mtkvaNwSeZHI_7pwlFGVWA.gif" /><figcaption>Redshift Crawler Configuration</figcaption></figure><h3>S3 Gateway Endpoint</h3><p>Before this crawler is run, there is a need to create VPC endpoint for Glue to be able to migrate data from S3 bucket to redshift, gateway endpoint is used for S3 to allow Glue to establish private connection for migration. Without this endpoint in VPC and Route Table, Migration Job will fail to connect to the resources.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/1*koT2oJ1h-MRa7lxh-TYczg.gif" /><figcaption>S3 Gateway Endpoint Configuration</figcaption></figure><h3>Glue Data Migration Job</h3><p>Moving onto the second Spark Job for data migration, the properties are similar to that of first job with changes as:</p><ul><li>Glue 3.0 is used because there is no need for Spark 3.3 here.</li><li>Redshift connection is added in Job properties for it to able to access it, the same connection was used for second crawler.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/1*Y7-FYX_WMQ1zCOAbVIzaAg.gif" /><figcaption>Migration Job Configuration</figcaption></figure><p>Migration job simply reads data from respective sources in Processed Zone S3 Bucket, maps this data with respective data types and loads this data into the Redshift tables in “<em>chicago-datawarehouse</em>” and changes are reflected in Redshift cluster as well.</p><pre>import sys<br>from awsglue.transforms import *<br>from awsglue.utils import getResolvedOptions<br>from pyspark.context import SparkContext<br>from awsglue.context import GlueContext<br>from awsglue.job import Job<br><br>args = getResolvedOptions(sys.argv, [&quot;JOB_NAME&quot;])<br>sc = SparkContext()<br>glueContext = GlueContext(sc)<br>spark = glueContext.spark_session<br>job = Job(glueContext)<br>job.init(args[&quot;JOB_NAME&quot;], args)<br><br># Script generated for node Amazon S3<br>AmazonS3_node1677088761773 = glueContext.create_dynamic_frame.from_options(<br>    format_options={},<br>    connection_type=&quot;s3&quot;,<br>    format=&quot;parquet&quot;,<br>    connection_options={<br>        &quot;paths&quot;: [&quot;s3://chicago-processed-bucket/Chicago_crimes/date_district/&quot;],<br>        &quot;recurse&quot;: True,<br>    },<br>    transformation_ctx=&quot;AmazonS3_node1677088761773&quot;,<br>)<br><br># Script generated for node S3 bucket<br>S3bucket_node1 = glueContext.create_dynamic_frame.from_options(<br>    format_options={},<br>    connection_type=&quot;s3&quot;,<br>    format=&quot;parquet&quot;,<br>    connection_options={<br>        &quot;paths&quot;: [&quot;s3://chicago-processed-bucket/Chicago_crimes/crime_description/&quot;],<br>        &quot;recurse&quot;: True,<br>    },<br>    transformation_ctx=&quot;S3bucket_node1&quot;,<br>)<br><br># Script generated for node Amazon S3<br>AmazonS3_node1677088750738 = glueContext.create_dynamic_frame.from_options(<br>    format_options={},<br>    connection_type=&quot;s3&quot;,<br>    format=&quot;parquet&quot;,<br>    connection_options={<br>        &quot;paths&quot;: [&quot;s3://chicago-processed-bucket/Chicago_crimes/arrest_by_type/&quot;],<br>        &quot;recurse&quot;: True,<br>    },<br>    transformation_ctx=&quot;AmazonS3_node1677088750738&quot;,<br>)<br><br># Script generated for node Change Schema (Apply Mapping)<br>ChangeSchemaApplyMapping_node1677088763821 = ApplyMapping.apply(<br>    frame=AmazonS3_node1677088761773,<br>    mappings=[<br>        (&quot;crime_date&quot;, &quot;string&quot;, &quot;crime_date&quot;, &quot;string&quot;),<br>        (&quot;ID&quot;, &quot;bigint&quot;, &quot;ID&quot;, &quot;int&quot;),<br>        (&quot;District&quot;, &quot;double&quot;, &quot;District&quot;, &quot;int&quot;),<br>    ],<br>    transformation_ctx=&quot;ChangeSchemaApplyMapping_node1677088763821&quot;,<br>)<br><br># Script generated for node ApplyMapping<br>ApplyMapping_node2 = ApplyMapping.apply(<br>    frame=S3bucket_node1,<br>    mappings=[<br>        (&quot;ID&quot;, &quot;bigint&quot;, &quot;ID&quot;, &quot;int&quot;),<br>        (&quot;Case_Number&quot;, &quot;string&quot;, &quot;Case_Number&quot;, &quot;string&quot;),<br>        (&quot;Date&quot;, &quot;string&quot;, &quot;Date&quot;, &quot;string&quot;),<br>        (&quot;Block&quot;, &quot;string&quot;, &quot;Block&quot;, &quot;string&quot;),<br>        (&quot;IUCR&quot;, &quot;string&quot;, &quot;IUCR&quot;, &quot;string&quot;),<br>        (&quot;Primary_Type&quot;, &quot;string&quot;, &quot;Primary_Type&quot;, &quot;string&quot;),<br>        (&quot;Description&quot;, &quot;string&quot;, &quot;Description&quot;, &quot;string&quot;),<br>        (&quot;Location_Description&quot;, &quot;string&quot;, &quot;Location_Description&quot;, &quot;string&quot;),<br>        (&quot;Arrest&quot;, &quot;boolean&quot;, &quot;Arrest&quot;, &quot;boolean&quot;),<br>        (&quot;Domestic&quot;, &quot;boolean&quot;, &quot;Domestic&quot;, &quot;boolean&quot;),<br>        (&quot;Beat&quot;, &quot;bigint&quot;, &quot;Beat&quot;, &quot;long&quot;),<br>        (&quot;District&quot;, &quot;double&quot;, &quot;District&quot;, &quot;int&quot;),<br>        (&quot;Ward&quot;, &quot;double&quot;, &quot;Ward&quot;, &quot;int&quot;),<br>        (&quot;Community_Area&quot;, &quot;double&quot;, &quot;Community_Area&quot;, &quot;int&quot;),<br>        (&quot;FBI_Code&quot;, &quot;string&quot;, &quot;FBI_Code&quot;, &quot;string&quot;),<br>        (&quot;X_Coordinate&quot;, &quot;double&quot;, &quot;X_Coordinate&quot;, &quot;double&quot;),<br>        (&quot;Y_Coordinate&quot;, &quot;double&quot;, &quot;Y_Coordinate&quot;, &quot;double&quot;),<br>        (&quot;Year&quot;, &quot;double&quot;, &quot;Year&quot;, &quot;int&quot;),<br>        (&quot;Updated_On&quot;, &quot;string&quot;, &quot;Updated_On&quot;, &quot;string&quot;),<br>        (&quot;Latitude&quot;, &quot;double&quot;, &quot;Latitude&quot;, &quot;string&quot;),<br>        (&quot;Longitude&quot;, &quot;double&quot;, &quot;Longitude&quot;, &quot;string&quot;),<br>        (&quot;Location&quot;, &quot;string&quot;, &quot;Location&quot;, &quot;string&quot;),<br>    ],<br>    transformation_ctx=&quot;ApplyMapping_node2&quot;,<br>)<br><br># Script generated for node Change Schema (Apply Mapping)<br>ChangeSchemaApplyMapping_node1677088753142 = ApplyMapping.apply(<br>    frame=AmazonS3_node1677088750738,<br>    mappings=[<br>        (&quot;crime_date&quot;, &quot;string&quot;, &quot;crime_date&quot;, &quot;string&quot;),<br>        (&quot;Primary_Type&quot;, &quot;string&quot;, &quot;Primary_Type&quot;, &quot;string&quot;),<br>        (&quot;Ward&quot;, &quot;double&quot;, &quot;Ward&quot;, &quot;int&quot;),<br>        (&quot;Arrest&quot;, &quot;bigint&quot;, &quot;Arrest&quot;, &quot;int&quot;),<br>    ],<br>    transformation_ctx=&quot;ChangeSchemaApplyMapping_node1677088753142&quot;,<br>)<br><br># Script generated for node Amazon Redshift<br>AmazonRedshift_node1677088765596 = glueContext.write_dynamic_frame.from_catalog(<br>    frame=ChangeSchemaApplyMapping_node1677088763821,<br>    database=&quot;chicago-redshift&quot;,<br>    table_name=&quot;dev_public_chicago_crimes_by_date_district&quot;,<br>    redshift_tmp_dir=args[&quot;TempDir&quot;],<br>    transformation_ctx=&quot;AmazonRedshift_node1677088765596&quot;,<br>)<br><br># Script generated for node Redshift Cluster<br>RedshiftCluster_node3 = glueContext.write_dynamic_frame.from_catalog(<br>    frame=ApplyMapping_node2,<br>    database=&quot;chicago-redshift&quot;,<br>    table_name=&quot;dev_public_chicago_crimes_details&quot;,<br>    redshift_tmp_dir=args[&quot;TempDir&quot;],<br>    transformation_ctx=&quot;RedshiftCluster_node3&quot;,<br>)<br><br># Script generated for node Amazon Redshift<br>AmazonRedshift_node1677088755847 = glueContext.write_dynamic_frame.from_catalog(<br>    frame=ChangeSchemaApplyMapping_node1677088753142,<br>    database=&quot;chicago-redshift&quot;,<br>    table_name=&quot;dev_public_chicago_crimes_by_ward_arrest&quot;,<br>    redshift_tmp_dir=args[&quot;TempDir&quot;],<br>    transformation_ctx=&quot;AmazonRedshift_node1677088755847&quot;,<br>)<br><br>job.commit()</pre><p>Data is Redshift can be then be queried and used. Redshift connection can be made to Data Reporting tools like Tableau or Power BI, Redshift network settings for Public accessibility needs to be enabled and IP address can be added to inbound rules in VPC.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/1*xU5sWXTo78yiPSb0Iu5OqQ.gif" /></figure><h3>Glue ETL Workflow</h3><p>Now that Crawlers and Jobs are successfully created and executed, next in line is automating this process with failure handling using SNS and EventBridge rule. Glue ETL Workflow is used to automated where the workflow is triggered based on a schedule, I have set it for hourly but it can differ based on use case.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/1*xkpExmEdLMgo593bzV3RGQ.gif" /><figcaption>ETL Workflow Trigger</figcaption></figure><p>Scheduled trigger triggers landing crawler and transformation job. Further event based trigger on successful status of transformation job triggers migration job and finally the redshift crawler.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*geVhdj3sSh6_S8ojjSWFQQ.png" /><figcaption>Glue ETL Worflow</figcaption></figure><h3>Job Failure Handling</h3><p>For job failures, an EventBridge rule is created to check job status for both of Glue jobs and in case of failure it sends the log information to the SNS topic that was created in Part 1 which then emails the information to the developer.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/826/1*9oSs4DQNBUO11cXB6gVU1w.png" /><figcaption>Job Failure Rule Event Pattern</figcaption></figure><h3>ETL Summary</h3><p>Finally, the end to end pipeline consists of Apache Nifi which ingests data from the source to Raw Zone and then intermediate data to Landing Zone whenever new files are placed in source. Log information is emailed in case of failures.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3aCmK5TrMgr7GIyryAepKg.png" /><figcaption>Part 1 Architecture</figcaption></figure><p>ETL Workflow then on hourly basis transforms data in Landing Zone and places it in Processed Zone which is then migrated to Redshift. Log information is emailed in case of failures and Glue Catalog is maintained using the Crawlers for Landing S3 Bucket and Redshift.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*rQKTX9CsRfoLl7D0ji3JVw.png" /><figcaption>Part 2 Architecture</figcaption></figure><p>With this, we created a batch processing pipeline with only Apache Nifi used as the server managed by us. At times data source can be directly fed into the Glue Job instead of using Nifi to go serverless. We addressed steps including ingestion, transformation and migration. Data from Redshift can be visualized using AWS Quicksight or third party tools like Tableau, Power BI, Superset etc.</p><p><a href="https://medium.com/@hashir.hak49/etl-using-apache-nifi-and-aws-glue-part-1-706c8e38c2be">ETL Using Apache Nifi and AWS Glue— Part 1</a></p><p>Code:</p><p><a href="https://github.com/hasshir1/ETL-using-Nifi-and-AWS">GitHub - hasshir1/ETL-using-Nifi-and-AWS</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=2146974e3069" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[IBM Data Science Capstone Report (Road Collisions Analysis and Fatality Prediction)]]></title>
            <link>https://medium.com/@hashir.hak49/ibm-data-science-capstone-report-road-collisions-analysis-and-fatality-prediction-361269951a91?source=rss-4b23311915f0------2</link>
            <guid isPermaLink="false">https://medium.com/p/361269951a91</guid>
            <category><![CDATA[accident]]></category>
            <category><![CDATA[analysis]]></category>
            <category><![CDATA[exploratory-data-analysis]]></category>
            <category><![CDATA[collision-detection]]></category>
            <category><![CDATA[data-science]]></category>
            <dc:creator><![CDATA[Hashir Alam]]></dc:creator>
            <pubDate>Thu, 10 Sep 2020 10:44:28 GMT</pubDate>
            <atom:updated>2021-07-14T08:12:25.534Z</atom:updated>
            <content:encoded><![CDATA[<h3><strong>Data</strong></h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/694/1*r4YpqoZaRdIuSdMLUvsDdQ.jpeg" /></figure><p><strong>Business Problem &amp; Interest of Project</strong></p><p>With data of over 10,000 accidents occurring every year, authorities responsible for traffic rules and regulations as well as the companies looking forward to developing techniques or in route to research for development in infrastructures, traffic system or even vehicles can benefit from the model. Having data with the ability to predict the occurrence of an accident given certain circumstances or predicting the fatality of the accident if one is to occur can help develop better vehicles and system to save lives. Companies like Tesla look for ways to train their self driving cars and provide comfort to the consumer, on the other hand authorities like SPD work towards avoiding collisions and eventually give the public an updated and a better traffic rule.</p><p><strong>Data Labels (Classification Problem)</strong></p><p>First, we looked at the type of fatalities our data provides, there are two kinds of collision outcomes in our data and they are represented by ‘1’ which is encoded for the description ‘Prop damage’ that tells us either the vehicle or a physical damage to the surrounding was done and the second is ‘2’ which is encoded for the description ‘Injury’ that tells us an injury to the passengers, driver or the pedestrian occurred.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/401/1*E3gZvSBD-YnAaDCGRPqF_w.jpeg" /><figcaption>Labels to be predicted by the model</figcaption></figure><p><strong>Exploratory Data Analysis</strong></p><p>The numbers show us that over 6000 of the accidents were during dusk with slippery roads and many others were during dawn in the areas where snowing is frequent.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/284/1*Iw2bePz9QQmvBOajI3Ekkw.jpeg" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/410/1*p1Gd7-Bu7m2pRHaMrTgOtA.jpeg" /><figcaption>Correlation between Light and Road Conditions</figcaption></figure><p><strong>Project Goal</strong></p><p>Frequency of accidents occurring have decreased each year because of the updates and advancements in the traffic regulations and vehicle type, can our model lower the frequency of accidents? Well, that is our goal for the project.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/418/1*I06wLhXF9iBgbYxC2jvk8A.jpeg" /><figcaption>Frequency of accidents per year in USA</figcaption></figure><p><strong>Looking Deeper into the Locations</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/667/1*M9y0Y6pQ9jLqLJEasWl22A.jpeg" /></figure><p>First, we need to get the numbers for every location accident have occurred most and we can analyze the structure of the location and see what is making these accidents frequency. We can see that the highest number of accidents occurred near Battery St Tunnel.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*LDTUtvNqMCKw1Yvn1fDVYQ.jpeg" /><figcaption>Battery St Tunnel Seattle</figcaption></figure><p>The curving you see in the road is the major reason accidents have occurred and this location has a larger history of severe accidents even past 2004, a lot of work can be done to avoid collisions at this location. Speeding and absence of lights have been the main reasons for the accidents to occur.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/986/1*TISm6CGCuKXQJD9PgovDzw.jpeg" /><figcaption>Aurora Ave Seattle</figcaption></figure><p>Aurora Ave in Seattle is a busy road, over a million vehicles pass through this road every month. Even though it is a straight highway but the accidents have still occurred in large numbers in different areas at this road and that is because of weather and road conditions. Wet roads with high pedestrian as well as vehicle traffic have resulted in the collisions.</p><p><strong>Time Factor</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/267/1*4EySuOJ39g0jVJpV1vrkdQ.jpeg" /><figcaption>Accidents at different times of the day</figcaption></figure><p>For the same type of location, many of the accidents have occurred during late office hours after noon and many after close timings, even though the amount of traffic would arguably be the same at 8 am when office timings start but the number of accidents are more at the late hours and this is mainly because of speeding and traffic where only one of these aspects can be controlled.</p><p><strong>Model &amp; Results</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/438/1*KqWR-ZfsRFnUJ7zmAHrkYg.jpeg" /></figure><p>Upon creating and training the model, we were able to test our model with 33% of the dataset and that is over 60,000 accidents, where the input features were the conditions the accidents occurred at and we were able to get an accuracy of up to 83%.</p><p><strong>Discussion</strong></p><p>We saw that road conditions play a vital role where we thought driving under the influence of alcohol would be a bigger factor, we saw that the busiest and the roads with most curves have reported the largest number of accidents in Seattle, we saw that some weather conditions which do not seem harmful at first could play a larger role in vehicle collisions only because of the addition of smaller factors which many of us seem to disregard while driving.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=361269951a91" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>