<?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 Ramses Alexander Coraspe Valdez on Medium]]></title>
        <description><![CDATA[Stories by Ramses Alexander Coraspe Valdez on Medium]]></description>
        <link>https://medium.com/@coraspe-ramses?source=rss-5a425beaf13------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*Sq5as3UFUhTabBrKRyCJew.png</url>
            <title>Stories by Ramses Alexander Coraspe Valdez on Medium</title>
            <link>https://medium.com/@coraspe-ramses?source=rss-5a425beaf13------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Fri, 20 Nov 2026 09:25:42 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@coraspe-ramses/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[Working with large CSV files in Python from Scratch]]></title>
            <link>https://coraspe-ramses.medium.com/working-with-large-csv-files-in-python-from-scratch-134587aed5f7?source=rss-5a425beaf13------2</link>
            <guid isPermaLink="false">https://medium.com/p/134587aed5f7</guid>
            <category><![CDATA[python]]></category>
            <category><![CDATA[storage]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[csv]]></category>
            <category><![CDATA[big-data]]></category>
            <dc:creator><![CDATA[Ramses Alexander Coraspe Valdez]]></dc:creator>
            <pubDate>Wed, 21 Dec 2022 00:34:10 GMT</pubDate>
            <atom:updated>2022-12-31T14:27:44.635Z</atom:updated>
            <cc:license>http://creativecommons.org/licenses/by/4.0/</cc:license>
            <content:encoded><![CDATA[<h4>5 Techniques</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*BY1Yp1pRm7UHGzj1lusQ-w.png" /><figcaption>Large CSV files</figcaption></figure><p>I assume you have already had the experience of trying to open a large .csv file on your computer and it stopped working to the point of having to restart it. I<a href="https://github.com/jtablesaw/tablesaw/issues/136">t is obvious that trying to load files over 2gb into memory is still an extremely heavy task for current computers</a>. This article explains and provides some techniques that I have sometimes had to use to process very large csv files from scratch:</p><h3>Estimate count of rows</h3><p>Knowing the number of records or rows in your csv file in advance can help you to improve the partitioning strategy, or division of the file. The code below can help you estimate the number of records or rows contained in a very large csv file:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*VbHmkvwJAIdGbo3FUpqrLQ.png" /><figcaption>Count of rows estimation</figcaption></figure><pre>mport mmap<br>import os<br><br>class Csvnrows(object):    <br><br>    @staticmethod<br>    def estimate_csv_rows(filename, header = True):<br><br>        count_rows = 0<br><br>        with open(filename, mode=&quot;r&quot;, encoding = &quot;ISO-8859-1&quot;) as file_obj:<br><br>            with mmap.mmap(file_obj.fileno(), length=0, access=mmap.ACCESS_READ) as map_file:<br><br>                buffer = map_file.read(1&lt;&lt;13)<br>                file_size = os.path.getsize(filename)<br>                count_rows = file_size // (len(buffer) // buffer.count(b&#39;\n&#39;)) - (1 if header else 0) <br><br>        return count_rows</pre><p><strong>code</strong>: <a href="https://github.com/Wittline/csv-estimate-rows">https://github.com/Wittline/csv-estimate-rows</a></p><h3>Split or Partitioning</h3><p>The advantages offered by partitioning the file into chunks are varied, it may be that you only need to review or read a specific portion of the file, improve the searches on the file because there is a smaller portion of data to search for or file uploading, uploading multiple small files in parallel is often faster than uploading a single large file, there are various partitioning strategies:</p><p>If you still don’t know the number of records or rows in the file, then you can assume a number N that represents the number of rows in each chunk. The process can create multiple files, partitions, or chunks with N records each. This could cause the last chunk to be unbalanced or have a smaller number of records than the other partitions.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*g8o_HTxZRv0NbYwCsUn5cA.png" /><figcaption>Partitioning by chunks of size N</figcaption></figure><pre>import os<br>import gzip<br><br>class CSVSplit:<br><br>    def __init__(self, filepath, prefix, folder, header = True):<br>        self.filepath = filepath<br>        self.header = header<br>        self.prefix = prefix<br>        self.folder = folder<br><br>    def __split_process(self, filepath, chunk_size = 10):<br>        chunk = []<br>        with open(filepath) as f:<br><br>            if self.header:<br>                header = next(f)<br>                chunk.append(header)<br><br>            for line in f:<br>                chunk.append(line)<br>                if len(chunk) == chunk_size:<br>                    yield chunk<br><br>                    if self.header:<br>                        chunk = [header]<br>                    else:<br>                        chunk = []<br>        if chunk:<br>            yield chunk<br>    <br>    def split_chunks_size_n(self, chunk_size = 10, compress = False):<br><br>        chunk_number = 0              <br>        for chunk in self.__split_process(self.filepath, chunk_size):<br><br>            format = &#39;csv&#39;<br><br>            if compress:<br>                format = &#39;csv.gz&#39;<br><br>            t_filename = f&#39;{self.prefix}_{chunk_number}.{format}&#39;<br>            t_filepath = os.path.join(self.folder, t_filename)<br><br>            if compress:<br>                chunk_bytes = gzip.compress(&#39;&#39;.join(chunk).encode()[6:])<br>                with open(t_filepath, &#39;wb&#39;) as chunk_file:<br>                    chunk_file.write(chunk_bytes)<br>            else:<br>                with open(t_filepath, &#39;w&#39;) as file:<br>                    file.writelines(chunk)<br><br>            chunk_number += 1<br><br><br>csvspl = CSVSplit(&#39;csvData.csv&#39;, &#39;data&#39;, &#39;chunks/&#39;, header = True)<br>csvspl.split_chunks_size_n(100, compress = True)</pre><p>If you already know the number of records or rows in the file then it is easier to control or predict the number of partitions, in this case the number N could now be the number of partitions instead of the number of records per partition, in this example we will use the “<strong>estimate_csv_rows”</strong>, and a new function called <strong>“split_n_chunks”</strong>. You are now more likely to have balanced partitions.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*rZ4SCQLYYKjI-9AVtE1xNQ.png" /><figcaption>Partitioning by chunks of equal size</figcaption></figure><pre>import os<br>import gzip<br>import mmap<br><br>class CSVSplit:<br><br>    def __init__(self, filepath, prefix, folder, header = True):<br>        self.filepath = filepath<br>        self.header = header<br>        self.prefix = prefix<br>        self.folder = folder<br><br><br>    def __estimate_csv_rows(self, filename, header = True):<br><br>        count_rows = 0<br><br>        with open(filename, mode=&quot;r&quot;, encoding = &quot;ISO-8859-1&quot;) as file_obj:<br><br>            with mmap.mmap(file_obj.fileno(), length=0, access=mmap.ACCESS_READ) as map_file:<br><br>                buffer = map_file.read(1&lt;&lt;13)<br>                file_size = os.path.getsize(filename)<br>                count_rows = file_size // (len(buffer) // buffer.count(b&#39;\n&#39;)) - (1 if header else 0) <br><br>        print(&quot;count_rows:&quot;, count_rows)<br>        return count_rows        <br><br>    def __split_process(self, filepath, chunk_size = 10):<br>        chunk = []<br>        with open(filepath) as f:<br><br>            if self.header:<br>                header = next(f)<br>                chunk.append(header)<br><br>            for line in f:<br>                chunk.append(line)<br>                if len(chunk) == chunk_size:<br>                    yield chunk<br><br>                    if self.header:<br>                        chunk = [header]<br>                    else:<br>                        chunk = []<br>        if chunk:<br>            yield chunk<br>    <br>    def split_chunks_size_n(self, chunk_size = 10, compress = False):<br><br>        chunk_number = 0              <br>        for chunk in self.__split_process(self.filepath, chunk_size):<br><br>            format = &#39;csv&#39;<br><br>            if compress:<br>                format = &#39;csv.gz&#39;<br><br>            t_filename = f&#39;{self.prefix}_{chunk_number}.{format}&#39;<br>            t_filepath = os.path.join(self.folder, t_filename)<br><br>            if compress:<br>                chunk_bytes = gzip.compress(&#39;&#39;.join(chunk).encode()[6:])<br>                with open(t_filepath, &#39;wb&#39;) as chunk_file:<br>                    chunk_file.write(chunk_bytes)<br>            else:<br>                with open(t_filepath, &#39;w&#39;) as file:<br>                    file.writelines(chunk)<br><br>            chunk_number += 1<br><br><br>    def split_n_chunks(self, n_chunks = 10, compress = False):<br><br>        chunk_size = (self.__estimate_csv_rows(self.filepath, self.header) // n_chunks)<br>        print(&quot;chunk_size:&quot;, chunk_size)<br><br>        chunk_number = 0              <br>        for chunk in self.__split_process(self.filepath, chunk_size):<br><br>            format = &#39;csv&#39;<br><br>            if compress:<br>                format = &#39;csv.gz&#39;<br><br>            t_filename = f&#39;{self.prefix}_{chunk_number}.{format}&#39;<br>            t_filepath = os.path.join(self.folder, t_filename)<br><br>            if compress:<br>                chunk_bytes = gzip.compress(&#39;&#39;.join(chunk).encode()[6:])<br>                with open(t_filepath, &#39;wb&#39;) as chunk_file:<br>                    chunk_file.write(chunk_bytes)<br>            else:<br>                with open(t_filepath, &#39;w&#39;) as file:<br>                    file.writelines(chunk)<br><br>            chunk_number += 1            <br><br><br>csvspl = CSVSplit(&#39;csvData.csv&#39;, &#39;data&#39;, &#39;chunks/&#39;, header = True)<br>csvspl.split_n_chunks(20, compress = False)</pre><p>Another way to split or partition the file could be by using a condition based on the value of a column, but guaranteeing partitions of equal size would depend on the distribution of column values, a uniform distribution of values would cause balanced partitions, which the opposite would happen if the distribution of the values is skewed, this type of partition could greatly improve search times, check the new function <strong>“split_by_column”.</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*WnMsTeSrO05XBrqPqU6FeA.png" /><figcaption>Partitioning by column values</figcaption></figure><pre>    def __split__columns_process(self, filepath, column_name, chunk_size = 10):<br>        <br>        partitions = {}<br>        row_number = 0        <br>    <br>        with open(filepath) as f:<br>    <br>            if self.header:<br>                header = next(f)<br>                _header =  header.strip().split(self.sep)<br>    <br>            index_column = _header.index(column_name)<br>            del _header[index_column]<br>            _header = self.sep.join(_header) + &#39;\n&#39;<br>    <br>            for line in f:<br>                row = line.strip().split(self.sep)<br>                value = row[index_column]<br>                del row[index_column]<br>                if value not in partitions:<br>                    if self.header:<br>                        partitions[value] = [_header]<br>                    else:<br>                        partitions[value] = []<br>    <br>                partitions[value].append(self.sep.join(row) + &#39;\n&#39;)<br>                row_number += 1<br>                <br>                if row_number == chunk_size:<br>                    yield partitions<br>                    row_number = 0<br>                    partitions = {}                 <br>    <br>        if row_number &gt; 0:<br>            yield partitions<br>            <br>    def split_by_column(self, column_value, batches = 10):<br>    <br>        chunk_size = (self.__estimate_csv_rows(self.filepath, self.header) // batches)   <br>        files = {}        <br>        format = &#39;csv&#39;<br>    <br>        for chunk in self.__split__columns_process(self.filepath, column_value, chunk_size):<br>    <br>            with concurrent.futures.ThreadPoolExecutor() as executor:<br>    <br>                tasks = []<br>                for value in chunk:                    <br>    <br>                    sufix = value.strip(&#39;&quot;&#39;)                             <br>                    t_filepath = os.path.join(self.folder, f&#39;{self.prefix}_{sufix}.{format}&#39;)<br>                    <br>                    if t_filepath not in files:<br>                        rows = chunk[value]<br>                        files[t_filepath] = open(t_filepath, &#39;a&#39;)<br>                    else:<br>                        rows = chunk[value][1:]<br>                                <br>                    def write_rows(file, rows):<br>                        try:<br>                            file.writelines(rows)<br>                            file.flush()<br>                        except Exception as er:<br>                            print(er)<br>                    <br>                    tasks.append(executor.submit(write_rows, files[t_filepath], rows))<br>                <br>                concurrent.futures.wait(tasks)<br>    <br>        for file in files.values():<br>            file.close()<br><br><br>csvspl = CSVSplit(&#39;csvData.csv&#39;, &#39;data&#39;, &#39;chunks/&#39;, header = True, sep=&#39;,&#39;)<br>csvspl.split_by_column(&#39;country&#39;, 2)</pre><p><strong>code</strong>: <a href="https://github.com/Wittline/csv-splitter">https://github.com/Wittline/csv-splitter</a></p><h3>Shuffle rows</h3><p>Sometimes it is necessary to shuffle the rows of the file in cases where the the data is going to be used for training machine learning models, as a rule a percentage of the total number of rows is chosen to be able to train these models and use a strategy of shuffle could reduce the odds of falling into biases. Another example case that will be explained later is the inference of data types, the shuffle of rows helps to improve the inference times of data types for each column of the csv file.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*WmxrLACKOnCaeeCC_NVSqw.png" /><figcaption>Shuffle rows</figcaption></figure><pre>import argparse<br>import sys<br>import os<br>import time<br>import numpy as np<br>import mmap<br><br><br>class ShuffleCSV:<br><br>    def __init__(self, input_file, header = True, batch_size = 1000):<br>        self.input_file = input_file<br>        self.header = header<br>        self.batch_size = batch_size<br><br><br>    def shuffle_csv(self):    <br><br>        with open(self.input_file, mode=&quot;r&quot;, encoding = &quot;ISO-8859-1&quot;) as file_obj:<br>            with mmap.mmap(file_obj.fileno(), length=0, access=mmap.ACCESS_READ) as file_map:<br><br>                lines = file_map.read().decode(&quot;ISO-8859-1&quot;).splitlines()<br>                <br>                if self.header:<br>                    header = lines[0]<br>                                <br>                                <br>                lines = lines[1:]<br><br>                <br>                if self.batch_size &lt;= len(lines):<br>                    self.batch_size = len(lines)<br><br>                np.random.shuffle(lines)<br><br>        with open(self.input_file, &#39;w&#39;) as f:<br>            <br>            f.write(header + &#39;\n&#39;)<br><br>            for batch in range(0, len(lines), self.batch_size):                <br>                f.write(&#39;\n&#39;.join([line for line in lines[batch: batch + self.batch_size]]))<br><br>        return len(lines)</pre><p><strong>code: </strong><a href="https://github.com/Wittline/csv-shuffler">https://github.com/Wittline/csv-shuffler</a></p><h3>Data Types inference</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*d0YzSrSg4AEEa465GTP_8A.png" /><figcaption>Data Types inference or Schema inference</figcaption></figure><p>I already wrote an article about this: <a href="https://medium.com/itnext/building-a-schema-inference-data-pipeline-for-large-csv-files-7a45d41ad4df">One of the most important tasks in data analytics is assign, deduce or infer a data type to a column in a .csv file, this could be very complex with no prior knowledge about variables involved in the business or if the dataset is very large.</a></p><p><strong>code:</strong> <a href="https://github.com/Wittline/csv-schema-inference">https://github.com/Wittline/csv-schema-inference</a></p><h3>Columnar Storage</h3><p>This columnar storage format improves lookup times and resource usage very efficiently for queries, as you can easily check which micropartitions, partitions, or columns contain the desired data and only retrieve those subsets of data for further processing in a cheaper way.</p><p>There are some companies that use this technique to improve the response times of their queries. Query pruning is an optimization technique that helps to improve query performance by limiting the amount of data that is analyzed during execution. It all works by parsing the WHERE clause of a query and using metadata about the data in the tables being queried to determine which blocks of data can be safely skipped during the execution process. Query pruning can be especially useful in cases where a query is processing a large amount of data and only a small subset of the data needs to be returned. By limiting the amount of data that needs to be scanned, query pruning can help to significantly improve query performance and reduce the amount of resources required to execute the query.</p><p>Below I will show you three partitioning techniques using columnar storage that could help you to quickly query and process a very large csv file, the first function is <strong>“to_columnar”</strong>, this function creates N number of files, where N is the number of columns, each file contains information about a single column.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1tJpIN9VHo-3hluR99uiWg.png" /><figcaption>Columnar storage</figcaption></figure><pre>import concurrent.futures<br>import os<br>import gzip<br>import mmap<br>import concurrent.futures<br><br><br>class csvColumnar(object):<br><br>    def __init__(self, filepath, prefix, header = True, sep = &#39;,&#39;):<br>        self.filepath = filepath<br>        self.header = header<br>        self.prefix = prefix<br>        self.folder = os.path.splitext(self.filepath)[0]<br>        self.sep = sep<br><br><br>    def __estimate_csv_rows(self, filename, header = True):<br><br>        count_rows = 0<br><br>        with open(filename, mode=&quot;r&quot;, encoding = &quot;ISO-8859-1&quot;) as file_obj:<br><br>            with mmap.mmap(file_obj.fileno(), length=0, access=mmap.ACCESS_READ) as map_file:<br><br>                buffer = map_file.read(1&lt;&lt;13)<br>                file_size = os.path.getsize(filename)<br>                count_rows = file_size // (len(buffer) // buffer.count(b&#39;\n&#39;)) - (1 if header else 0) <br>        <br>        return count_rows<br><br>    <br>    def __clear_column_data(self, columns_names):<br>        return { column:[] for i, column in enumerate(columns_names)}<br><br><br>    def __split_process_columnar(self, filepath, chunk_size = 10):<br>                <br>        row_number = 0<br>        with open(filepath, &#39;r&#39;, encoding=&#39;utf-8-sig&#39;) as f:<br><br>            first_line = next(f)            <br>            columns_names = first_line.strip().split(&#39;,&#39;)            <br><br>            columns_data = self.__clear_column_data(columns_names)<br><br>            for line in f:<br>                row = line.strip().split(self.sep)<br>                for i, cell in enumerate(row):<br>                    columns_data[columns_names[i]].append(cell + &#39;\n&#39;)<br>                <br>                row_number += 1                            <br>                <br>                if row_number == chunk_size:<br>                    yield columns_data<br>                    row_number = 0<br>                    columns_data = self.__clear_column_data(columns_names)              <br><br>        if row_number &gt; 0:<br>            yield columns_data        <br><br>    <br><br>    def to_columnar(self, batches = 10, compress = False):<br><br>        chunk_size = (self.__estimate_csv_rows(self.filepath, self.header) // batches)                     <br>        files = {}<br>        format = &#39;csv.gz&#39; if compress else &#39;csv&#39;<br>        <br>        if not os.path.exists(self.folder):<br>            os.makedirs(self.folder)<br><br>        for chunk in self.__split_process_columnar(self.filepath, chunk_size):            <br><br>            with concurrent.futures.ThreadPoolExecutor() as executor:<br><br>                tasks = []<br>                for value in chunk:                    <br>                    <br>                    t_filepath = os.path.join(self.folder, f&#39;{self.prefix}_{value}.{format}&#39;)<br>                    <br>                    if t_filepath not in files:                        <br>                        files[t_filepath] = open(t_filepath, &#39;a&#39;)<br>                                <br>                    def write_rows(file, rows):<br>                        try:                            <br>                            file.writelines(rows)<br>                            file.flush()<br>                        except Exception as er:<br>                            print(er)<br>                    <br>                    tasks.append(executor.submit(write_rows, files[t_filepath], chunk[value]))<br>                <br>                concurrent.futures.wait(tasks)<br><br><br>csvspl = csvColumnar(&#39;csvData.csv&#39;, &#39;data&#39;, header = True, sep=&#39;,&#39;)<br>csvspl.to_columnar(batches=10,compress=False)</pre><p>Another possible technique would be the one used in the <strong>“to_micropartitions”</strong> function, If you provides the number <strong>M </strong>which is the <strong>number of partitions</strong> that are required, M partitions could be generated for each <strong>N</strong>, where <strong>N is the number of columns</strong> in the file, It will improves the searches against the file, if the file is sorted by a specific column, then you could have metadata about the <strong>MAX</strong> and <strong>MIN </strong>value for that column on each partition, this would help prune the query even more.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_vQrdP9uEJ6iH4xl8PJcFQ.png" /><figcaption>Micropartitions</figcaption></figure><pre>import concurrent.futures<br>import os<br>import gzip<br>import mmap<br>import concurrent.futures<br><br><br>class csvColumnar(object):<br><br>    def __init__(self, filepath, prefix, header = True, sep = &#39;,&#39;):<br>        self.filepath = filepath<br>        self.header = header<br>        self.prefix = prefix<br>        self.folder = os.path.splitext(self.filepath)[0]<br>        self.sep = sep<br><br><br>    def __estimate_csv_rows(self, filename, header = True):<br><br>        count_rows = 0<br><br>        with open(filename, mode=&quot;r&quot;, encoding = &quot;ISO-8859-1&quot;) as file_obj:<br><br>            with mmap.mmap(file_obj.fileno(), length=0, access=mmap.ACCESS_READ) as map_file:<br><br>                buffer = map_file.read(1&lt;&lt;13)<br>                file_size = os.path.getsize(filename)<br>                count_rows = file_size // (len(buffer) // buffer.count(b&#39;\n&#39;)) - (1 if header else 0) <br>        <br>        return count_rows<br><br>    <br>    def __clear_column_data(self, columns_names):<br>        return { column:[] for i, column in enumerate(columns_names)}<br><br><br>    def __split_process_columnar(self, filepath, chunk_size = 10):<br>                <br>        row_number = 0<br>        with open(filepath, &#39;r&#39;, encoding=&#39;utf-8-sig&#39;) as f:<br><br>            first_line = next(f)            <br>            columns_names = first_line.strip().split(&#39;,&#39;)            <br><br>            columns_data = self.__clear_column_data(columns_names)<br><br>            for line in f:<br>                row = line.strip().split(self.sep)<br>                for i, cell in enumerate(row):<br>                    columns_data[columns_names[i]].append(cell + &#39;\n&#39;)<br>                <br>                row_number += 1                            <br>                <br>                if row_number == chunk_size:<br>                    yield columns_data<br>                    row_number = 0<br>                    columns_data = self.__clear_column_data(columns_names)              <br><br>        if row_number &gt; 0:<br>            yield columns_data<br><br>    def to_micropartitions(self, sorted_column, number_partitions = 10, compress = False):<br>        <br>        chunk_size = (self.__estimate_csv_rows(self.filepath, self.header) // number_partitions)<br>        files = {}        <br>        format = &#39;csv.gz&#39; if compress else &#39;csv&#39;<br>        chunk_number = 0<br><br>        if not os.path.exists(self.folder):<br>            os.makedirs(self.folder)<br><br>        for chunk in self.__split_process_columnar(self.filepath, chunk_size):<br>                        <br>            with concurrent.futures.ThreadPoolExecutor() as executor:<br>                <br>                tasks = []<br>                for value in chunk:<br>                    <br>                    t_filepath = os.path.join(self.folder, f&#39;{value}_partition_{chunk_number}.csv&#39;)<br>                    <br>                    if t_filepath not in files:                            <br>                        files[t_filepath] = open(t_filepath, &#39;a&#39;)<br>                        <br>                    def write_rows(file, rows):<br>                        try:         <br>                            file.writelines(rows)<br>                            file.flush()<br>                        except Exception as er:<br>                            print(er)<br>                    <br>                    tasks.append(executor.submit(write_rows, files[t_filepath], chunk[value]))<br>            <br>                concurrent.futures.wait(tasks)<br>            <br>            chunk_number +=1    <br>        <br>        for file in files.values():<br>            file.close()</pre><p>One last and no less important strategy is to create the partitions by the unique values of a specific column, check the function in the code called: <strong>“to_columnar_micropartitions”</strong> this column could be considered as a new folder, and inside each folder there are N-1 files that would represent the records for each column as a unique file, this would generate unbalanced partitions, the image below shows a possible configuration of partitions if the values of column 1 are considered as partition, the WHERE would get only the necessary partitions and would not affect the searches much.</p><p>SELECT COL2, COL3<br>FROM csvdata<br>WHERE COL1 in (“PURPLE”, “BLUE”)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*NB6QYpSNjdICOVOn6hXLlg.png" /><figcaption>Columnar micropartitions</figcaption></figure><pre>import concurrent.futures<br>import os<br>import gzip<br>import mmap<br>import concurrent.futures<br><br><br>class csvColumnar(object):<br><br>    def __init__(self, filepath, prefix, header = True, sep = &#39;,&#39;):<br>        self.filepath = filepath<br>        self.header = header<br>        self.prefix = prefix<br>        self.folder = os.path.splitext(self.filepath)[0]<br>        self.sep = sep<br><br><br>    def __estimate_csv_rows(self, filename, header = True):<br><br>        count_rows = 0<br><br>        with open(filename, mode=&quot;r&quot;, encoding = &quot;ISO-8859-1&quot;) as file_obj:<br><br>            with mmap.mmap(file_obj.fileno(), length=0, access=mmap.ACCESS_READ) as map_file:<br><br>                buffer = map_file.read(1&lt;&lt;13)<br>                file_size = os.path.getsize(filename)<br>                count_rows = file_size // (len(buffer) // buffer.count(b&#39;\n&#39;)) - (1 if header else 0) <br>        <br>        return count_rows<br><br><br>    def __split_process_micropartition(self, filepath, column_name, chunk_size = 10):                    <br>        <br>        partitions = {}<br>        row_number = 0<br><br>        with open(filepath, encoding=&#39;utf-8-sig&#39;) as f:<br><br>            if self.header:<br>                header = next(f)<br>                _header =  header.strip().split(self.sep)<br><br>            index_column = _header.index(column_name)            <br><br>            for line in f:<br>                row = line.strip().split(self.sep)<br>                value = row[index_column]                <br>                if value not in partitions:<br>                    partitions[value] = { column:[] for column in _header }<br><br>                for i, column in enumerate(_header):<br>                    partitions[value][column].append(row[i])<br>                row_number += 1<br>                <br>                if row_number == chunk_size:<br>                    yield partitions<br>                    row_number = 0<br>                    partitions = {}                 <br><br>        if row_number &gt; 0:<br>            yield partitions <br><br><br>    def to_columnar_micropartitions(self, column_value, batches = 10, compress = False):<br><br>        chunk_size = (self.__estimate_csv_rows(self.filepath, self.header) // batches)   <br>        files = {}        <br>        format = &#39;csv.gz&#39; if compress else &#39;csv&#39;      <br><br>        for chunk in self.__split_process_micropartition(self.filepath, column_value, chunk_size):<br>            <br>            with concurrent.futures.ThreadPoolExecutor() as executor:<br><br>                tasks = []<br>                for value in chunk:<br><br>                    partition_directory = os.path.join(self.folder, f&#39;{value}&#39;)<br>                    if not os.path.exists(partition_directory):<br>                        os.makedirs(partition_directory)<br><br>                    for i, column in enumerate(chunk[value].keys()):                        <br>                        if column != column_value:<br>                            micropartition_path = os.path.join(partition_directory, f&quot;{column}.{format}&quot;)<br><br>                            if micropartition_path not in files:                        <br>                                files[micropartition_path] = open(micropartition_path, &#39;a&#39;)<br>                                    <br>                            def write_rows(file, rows):                                                               <br>                                try:                            <br>                                    file.write(&#39;\n&#39;.join(rows) + &#39;\n&#39;)<br>                                    file.flush()<br>                                except Exception as er:<br>                                    print(er)<br>                        <br>                            tasks.append(executor.submit(write_rows, files[micropartition_path], chunk[value][column]))<br>                                <br>                concurrent.futures.wait(tasks)        <br><br>        for file in files.values():<br>            file.close()</pre><p><strong>code: </strong><a href="https://github.com/Wittline/csv-columnar">https://github.com/Wittline/csv-columnar</a></p><h3>Summary</h3><p>There are many ways in which you can choose partitions or rearrange micropartitions and everything will depend on the level of granularity you want to use. In Fact, there are companies that currently using these techniques to improve their query times, because this is the best way to select which subset of the data in the disk or memory has the information you need, when we work with very large csv files on a personal computer, we must avoid uploading the entire file in memory at all costs, in most of the code shown in this article I am not placing the whole file In memory, It is only processing batches of the data, and I write the whole batch quickly to the hard drive, this to avoid misusing memory and reduce I/O operations.</p><p>Companies like <a href="https://www.teradata.com/Blogs/How-to-Enjoy-Hybrid-Partitioning-with-Teradata-Columnar"><strong>Teradata</strong> </a>and <a href="https://docs.snowflake.com/en/user-guide/tables-clustering-micropartitions.html"><strong>Snowflake</strong></a> use columnar storage and micropartitions to improve the performance and efficiency of their data storage. They are enabling faster queries, Query Pruning, and analysis of large data sets by storing and organizing data in a more efficient way.</p><p>I am preparing another article about how to run or perform queries against these partitions or micropartitions using python, be alert.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=134587aed5f7" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Designing and Planning an Event Store System]]></title>
            <link>https://coraspe-ramses.medium.com/designing-and-planning-an-event-store-system-be4df7519442?source=rss-5a425beaf13------2</link>
            <guid isPermaLink="false">https://medium.com/p/be4df7519442</guid>
            <category><![CDATA[cqrs]]></category>
            <category><![CDATA[event-sourcing]]></category>
            <category><![CDATA[data-architecture]]></category>
            <category><![CDATA[eventstore]]></category>
            <category><![CDATA[columnar-databases]]></category>
            <dc:creator><![CDATA[Ramses Alexander Coraspe Valdez]]></dc:creator>
            <pubDate>Sun, 11 Dec 2022 02:40:34 GMT</pubDate>
            <atom:updated>2022-12-11T02:42:17.070Z</atom:updated>
            <cc:license>http://creativecommons.org/licenses/by/4.0/</cc:license>
            <content:encoded><![CDATA[<h4>CQRS and Event Sourcing design patterns</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ZlHhKBHZJOT6k_OVxTfJXQ.png" /><figcaption>Event Store System Architecture</figcaption></figure><p>I am designing and building a <a href="https://towardsdatascience.com/designing-data-products-b6b93edf3d23">Data Product</a>, this will expect to receive a lot of inmutable notifications from different social network platforms, so, I will need to perform <strong><em>write</em></strong><em> </em>operations of these notifications fast, and sometimes I will have to perform <strong><em>read </em></strong>operations with complex agregations against this database, a little later I will create another article with details about the whole technology, but, for now I have a problem or rather a doubt, and this issue is called: <a href="https://www.hpe.com/us/en/what-is/data-storage.html"><strong><em>DATA STORAGE</em></strong></a><strong><em>. </em></strong>Although I am very clear about all concepts around the <a href="https://www.ibm.com/cloud/learn/cap-theorem"><strong><em>CAP Theorem</em></strong></a>, my main concern is selecting one among the <a href="https://db-engines.com/en/ranking"><em>hundreds of types of databases</em></a> currently in existence, and this last argument dragged me to write this article and concentrate on build my own <a href="https://developer.confluent.io/learn-kafka/event-sourcing/why-store-events/"><strong><em>Event-Based Storage</em></strong></a>.</p><p>This article is not about simply presenting a technical concepts, so <strong>it is important that you read some concepts that I have highlighted in bold in order to have some context</strong>, this article is based on explaining each of my decisions quickly.</p><h4>What is an Event Store System?</h4><p>An event store system is a kind of storage to permanent persist and manage a log of inmutable (cannot be modified or deleted) events or actions that have occurred in a system or application. This log is called the event stream, and it is typically stored in a append-only fashion, meaning that new kind of events can be added to the stream, this concept is very different from <a href="https://www.oracle.com/database/what-is-oltp/"><strong><em>OLTP applications</em></strong></a>, where events can be mutable, and most of the events are <a href="https://www.codecademy.com/article/what-is-crud"><strong><em>CRUD operations</em></strong></a>, which requires being more demanding with data consistency.</p><p>One of the <a href="https://stackoverflow.com/a/9216885">main advantages of using an event store system</a> is that it will allows applications to keep track, capture and store a whole history of all events that have occurred in the system by providing a detailed record of the state changes and actions that have taken place. This can be useful for various purposes, such as data analysis, debugging, auditing and more.</p><p>In the architecture shown above it is easy to recognize that there are three <a href="https://www.redhat.com/architect/14-software-architecture-patterns"><strong><em>architectural patterns</em></strong></a>, (<strong><em>Event Sourcing</em></strong>, <strong><em>CQRS</em></strong>, <strong><em>Database per Service</em></strong>) which coincidentally are the most common to use to design this type of system. Each of these events in the event stream is represented as a record in the database, and it typically includes a unique identifier, which is a timestamp, and the data associated with it. The event store system provides APIs for applications to <strong>WRITE </strong>new events to the stream and to <strong>READ </strong>events from the stream for processing or analysis.</p><h4><strong>Event Sourcing pattern</strong></h4><p>As I said before, I will be receiving many notifications from social media platforms, each notification is a post and has a context, these posts will be immutable facts, events or actions.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/515/1*dk8xC9WQ9GNk55MvZsuIMA.png" /><figcaption>Event Sourcing pattern</figcaption></figure><p>Having a common place to store events or facts has the following advantages:</p><ul><li>Temporary queries</li><li>Last status of a specific post</li><li>Audit a specific post in real time</li><li>Execute queries in parallel against the event log.</li><li>Multiple systems reading from the same event log.</li></ul><p>It is common to see this architectural pattern and <strong>CQRS </strong>working together.</p><h4>Command and Query Responsibility Segregation (CQRS) pattern</h4><p>This pattern practically means that the place where the data is written is different from the place where the data is read. Sometimes you will have <strong>more reads than writes</strong> or <strong>more writes than reads</strong>, then you could segregate both behaviors by creating an exact copy of the main database which would cause both services to scale independently and it could adapt to sudden different workloads.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/909/1*YHeSLUbknA-jlbdBN8G4fQ.png" /><figcaption>Command and Query Responsibility Segregation (CQRS) pattern</figcaption></figure><p>Not all services can read the last event from the event log at the same time, they will have a delay between reads, sometimes some services might be out of sync when there is a lot of data being written, a real consistency is reached when there is no writes occurring, and we call this <a href="https://en.wikipedia.org/wiki/Eventual_consistency"><strong><em>eventual consistency</em></strong></a><strong><em>.</em></strong></p><h4>Database per Service pattern</h4><p>In the architecture shown above it is hard to recognize this pattern, but it is there, in this case more granular control for scaling horizontally is required and decoupling databases also will improve the resiliency of your whole application and will ensure that a single database cannot be a single point of failure, and the last and no less important feature of this pattern will be that microservices have different security requirements, so there is no need to share data in the same database for different kind of services.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/271/1*dccx5DXyl5C3kMb7l1rg5w.png" /><figcaption>Database per Service pattern</figcaption></figure><p>The only challenge would be to perform complex queries that aggregate results from multiple microservices or databases.</p><h3>Proposed Architecture</h3><p>Now that we have an understanding of why these three patterns are present in the candidate architecture, then I am going to proceed by explaining the whole process, but first I will give a brief explanation of the meaning of some symbols in the proposed architecture.</p><h4>Transitions between Events and Queries</h4><p>Once a request is received to try to write an event to the <strong><em>Master Event Store</em></strong>, it will first be inserted and marked as <strong><em>pending event </em></strong>with the color <strong>gray </strong>in a common shared memory for all services. At the same time, there is another service called “<strong><em>produce</em>r</strong>” that will be in charge of looking for all the messages that are pending to be written (<strong><em>grey</em></strong>) and once they have been inserted in the Master Event Store these events will now be marked in the shared memory with a status indicating that the event already exists in the Master Event Store (<strong><em>blue</em></strong>). Another service called “<strong><em>consumer</em></strong>” responsible for maintaining the sync of the databases will take all the events that are in the memory with the <strong><em>blue </em></strong>status and once they are replicated in the <strong><em>Slave </em>Event Store</strong>, then they will be marked with a status (<strong><em>green</em></strong>), events in shared memory with a green status exist in both, Master and Slave Event Stores.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/307/1*icqGzSRvuBhPoUD-V_PoJw.png" /><figcaption>Transitions between Events and Queries</figcaption></figure><p>Once a request is received to try to read data from the Slave Event Store, it will first be inserted and marked as pending query with the color <strong>yellow </strong>in a common shared memory for all services. At the same time, there is another service called “<strong>query handler</strong>” that will be in charge of looking for all the queries that are pending to attend (<strong>yellow</strong>), the shared memory will now have events that are already sync in both Events store (<strong>green</strong>), and this can be taken advantage of by the “<strong>read services</strong>”, remember that we follow the CQRS pattern, once a query is requesting data for a specific event then it could ask to the shared memory first, this could serve as a cache layer to improve the overall latency for responses, if the event has already expired from memory or does not exist due to eventual consistency or by deletion (<strong>red</strong>), then it will search for the records or events directly in the Slave Event Store, all the attended queries will be marked with the color <strong>orange </strong>and will be cached for an suitable period of time.</p><h4>REST and gRPC Services</h4><p>I will use <a href="https://grpc.io/"><strong><em>gRPC</em></strong> </a>for internal communications between my microservices, and <a href="https://en.wikipedia.org/wiki/Representational_state_transfer"><strong><em>REST</em></strong> </a>as a endpoint to the outside world. I am proposing two REST services, one for writes and one for reads, both services can be built with <a href="https://fastapi.tiangolo.com/"><strong><em>FastAPI</em></strong></a> and they should have the ability to scale horizontally, many external platforms will be consuming this services in parallel.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/784/1*NSiaF2Bvjh6Ocd135Tgwzw.png" /><figcaption>Write service and Read Service as REST interfaces</figcaption></figure><p>The <strong>blue box</strong> with the word <strong>gRPC </strong>stands for the <strong>grpc client</strong>, and the <strong>red box</strong> with the <strong>word gRPC</strong> stands for the <strong>grpc server, </strong>as<strong> </strong>you can notice there is gRPC client interface in both REST services which will allow to the gRPC server knows when a new request should be attended</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/150/1*Eu-IOfkECsmTnEkzeVW1ng.png" /><figcaption>gRPC Client and gRPC Server</figcaption></figure><p>There will be three gRPC servers (<strong>publisher</strong>, <strong>consumer</strong>, <strong>query handler</strong>), each one running on its own serverless layer to allow horizontal scaling, but so far I have two different communication strategies:</p><ul><li><strong>Attend requests in batches: </strong>when requests are received via the REST interface, they can be placed directly in a shared memory, and the services “<strong>Publisher</strong>”, “<strong>Consumer</strong>” and “<strong>Query Handler</strong>” can read unattended requests from this shared memory in batches.</li><li><strong>Bidirectional request and response: w</strong>henever a request is received, it is placed in shared memory and the REST service invokes the “<strong>Producer</strong>” gRPC server with the request ID, the gRPC server takes the request that matches the ID from the shared memory and serves it. At the same time, this gRPC server could call the “<strong>Consumer</strong>” gRPC server to notify that there is a record in the shared memory that needs to be synchronized.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/911/1*dUh6YNES23RhWdTuaaFTtA.png" /><figcaption>REST and gRPC Services</figcaption></figure><h4>Columnar and Row-Based Data Storage</h4><p>The storage strategy will use a hybrid approach, <strong>row-based storage</strong> to speed up writes, more efficient data integrity checks, as the entire row of data can be checked to ensure that all the values are valid and consistent, and <strong>columnar-based storage</strong> to speed up reads, only the specific columns of data that are needed for a particular query, more efficient data compression, which can reduce the amount of space required to store the data, reduce storage costs and overall performance.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/716/1*QSZYSnNu2F0XDBB-JIUpAw.png" /><figcaption>Columnar and Row-Based Data Storage</figcaption></figure><p>The query handler service will act as a compute node in a serverless mode, the design of this kind of product will not require add an extra layer to distribute the computation in parallel in a MPP fashion, at each moment the service will be in charge of executing one query at time on its own machine and will distribute the computation between all its processors, the execution of the query will be multiprocessor for now, this is a simple MVP.</p><p>This is the first part of this article, I will be adding more details in the next ones and pieces of code from each section, for now I plan to use AWS S3 to store the data, because S3 offers high availability, but this would add more complexity to be able to create an efficient read and write approach, since objects in S3 are binary and immutable.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=be4df7519442" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Building a Schema Inference Data Pipeline for Large CSV files]]></title>
            <link>https://itnext.io/building-a-schema-inference-data-pipeline-for-large-csv-files-7a45d41ad4df?source=rss-5a425beaf13------2</link>
            <guid isPermaLink="false">https://medium.com/p/7a45d41ad4df</guid>
            <category><![CDATA[csv-file]]></category>
            <category><![CDATA[parallel-computing]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[big-data]]></category>
            <dc:creator><![CDATA[Ramses Alexander Coraspe Valdez]]></dc:creator>
            <pubDate>Sat, 09 Jul 2022 14:46:29 GMT</pubDate>
            <atom:updated>2022-07-20T19:06:24.867Z</atom:updated>
            <cc:license>http://creativecommons.org/licenses/by/4.0/</cc:license>
            <content:encoded><![CDATA[<h4><strong>A parallel implementation with python</strong></h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*fWVA5PpIJiAbXONX1mptbA.png" /></figure><p>One of the most important tasks in data analytics is assign, deduce or infer a data type to a column in a<a href="https://en.wikipedia.org/wiki/Comma-separated_values"> .csv file</a>, this could be very complex with no prior knowledge about variables involved in the business or if the dataset is very large. In this article I am going to build a data pipeline to infer data types of each column in a large csv file, It is also worth mentioning that some obstacles that could arise in order to build an acceptable tool:</p><ul><li>Is it necessary to iterate over all records and values in the dataset?</li><li>Going through value by value in a large dataset could take great deal of time in order to explore and assign a data type to all cells in the dataset. A good approach would be to parallelize and explore the dataset in chunks.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0zzMSJ-Tsw6g19VWWhvwFQ.png" /></figure><ul><li>If a value for a column has already been explored, checked and a data type was assigned to it, then it will not be necessary to do it again, every value in every column will be cached.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Q1_OJbTfLfBE3WErfhf5Rg.png" /></figure><ul><li>Initially we don’t know the nature of the records order, which could lead to iterate over biases and only seeing a group of data types in a small portion of the dataset.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*JCDBz1NgX6O4LSoeFKUsGA.png" /></figure><ul><li>We have to guarantee that there is a uniform distribution of the records, in this way I will take only a considerable portion of them to be able to infer with more precision and faster, so I won’t need the 100% of the records.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Rpa2valVFhq9C1Y39gozbw.png" /></figure><ul><li>Null values are not a data type, it is a feature of the column, in our case if there are a lot of null values in the column then we will assign it the <strong>STRING </strong>data type.</li></ul><p>I took into account all the obstacles mentioned above, They will be suitable as strategies to speed up the inference process. <strong>So let’s start coding</strong>.</p><h3>Data pipeline for Schema Inference</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*i_M9no-0cuMgtgtxQ2aFuQ.png" /><figcaption><strong>Data pipeline</strong>: Schema Inference for Large CSV files</figcaption></figure><ul><li>I will use <a href="https://realpython.com/python-mmap/"><strong><em>Memory mapping with mmap</em></strong></a>, This will improve I/O performance by avoiding several system calls and will reduce expensive data buffer transfers.</li><li>I will also estimate how many lines the file has, this will help me to get a portion of the file after the records have been evenly distributed, I already mentioned the arguments, I will use the formula:</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/294/1*cjI9LDDM52kqt3ACRFI8cA.png" /></figure><ul><li>Once the records have been uniformly distributed and a portion of these records has been taken, then I will proceed to parallelize the inference process, this process is responsible for splitting the dataset into chunks with equal size and it will assign each chunk to a single processor.</li><li>The result of the inference process for each chunk of the dataset will return a different schema, it is necessary to join the results in order to have a single schema, with a single schema from multiple results we can now deduce or infer which is the approximate data type for each column and also be able to detect which column had null values. The image below shows the process.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*a9LoZzHdf7g7PecfoAfWwA.png" /><figcaption><strong>Joining multiple schemas from different inference process running in parallel</strong></figcaption></figure><ul><li>The accuracy value will help us to deduce which data types will be rejected as a posibles final data types, it will only accept values greater than or equal to the accuracy value</li></ul><h3>Data pipeline code for Schema Inference</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Z93yCcCnaJ12plR0TLAb8A.png" /><figcaption><strong>Inference process</strong></figcaption></figure><h3>Let’s run a real example</h3><p>Check out my GitHub repository <a href="https://github.com/Wittline/csv-schema-inference"><strong>csv-schema-inference</strong></a> for more information about the code.</p><h4>Let’s check first the csv file</h4><p>The real one for this example has 1.000.000 records</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/155cc0aabfc04c796fa6806079853226/href">https://medium.com/media/155cc0aabfc04c796fa6806079853226/href</a></iframe><h4>Let’s install the library</h4><pre>pip install csv-schema-inference</pre><h4>Let’s import the “csv-schema-inference” library</h4><pre>from csv_schema_inference import csv_schema_inference</pre><h4><strong>Setting “csv-schema-inference” configuration</strong></h4><pre>csv_infer = csv_schema_inference.CsvSchemaInference(portion=0.7, max_length=100, acc = 0.8, seed=2, header=True, sep=&quot;,&quot;)</pre><pre>pathfile = &quot;/content/data.csv&quot;</pre><ul><li><strong>portion</strong>: Let’s notice that we will only use 70% of the total records of the original data set</li><li><strong>max-length:</strong> It will keep cached only 100 characters for STRING data types. There is no need to keep so much data in memory.</li><li><strong>acc: </strong>The accuracy value for this example will take into account only data types with at least 80% of presence</li></ul><h4>Running inference</h4><pre>aprox_schema = csv_infer.run_inference(pathfile)</pre><h4>Approximate data type inference for each column</h4><pre>csv_infer.pretty(aprox_schema)</pre><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/be384ee676b7d240e788b5fe1100db12/href">https://medium.com/media/be384ee676b7d240e788b5fe1100db12/href</a></iframe><p>The output above show which columns are <strong>nullable </strong>and what is the <strong>approximated data type.</strong></p><h4>Let’s see all the values found in a specific column and their frequency of occurrence.</h4><pre>result = csv_infer.get_schema_columns(columns = {&quot;disc_6&quot;})<br>csv_infer.pretty(result)</pre><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/f025eafa2aca1faae0ca0a6e913e6fb7/href">https://medium.com/media/f025eafa2aca1faae0ca0a6e913e6fb7/href</a></iframe><h4>Explore all posible data types for a specific columns</h4><pre>result = csv_infer.explore_schema_column(column = &quot;disc_6&quot;)</pre><pre>csv_infer.pretty(result)</pre><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/c5358bc552629b73a071fbf578eb9f0a/href">https://medium.com/media/c5358bc552629b73a071fbf578eb9f0a/href</a></iframe><p>89 % are INTEGER values and 10.06 % of are NULL values which are automatically represented as STRING. In this case the correct infered data type is INTEGER because is closest to the accuracy value.</p><h3><strong>Benchmark</strong></h3><p>The tests were done with 9 .csv files, 21 columns, different sizes and number of records, an average of 5 executions was calculated for each process, <strong>shuffle time</strong> and <strong>inferring time</strong>.</p><ul><li>file__20m.csv: 20 million records</li><li>file__15m.csv: 15 million records</li><li>file__12m.csv: 12 million records</li><li>file__10m.csv: 10 million records</li><li>And so on…</li></ul><p>If you want to know more about the shuffling process, you can check this other repository: <a href="https://github.com/Wittline/csv-shuffler">A tool to automatically Shuffle lines in .csv files</a>, the shuffling process will helps us to:</p><ul><li>Increase the probability of finding all the data types present in a single column.</li><li>Avoid iterate the entire dataset.</li><li>Avoid see biases in the data that may be part of its organic behavior and due to not knowing the nature of its construction.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/396/1*g02DG09H0zmQcuip0MmsAA.png" /><figcaption>Benchmark</figcaption></figure><h3>CONCLUSIONS</h3><ul><li>The Multiprocessing code can be improved by using a data structure that can be shared by all processes, although I have tried to do so I have not been able to achieve it, if you are able to modify the code to use a common data structure it would be great. I’m sure this will improve response times.</li><li>This code was tested with very large datasets, from 1,000,000 records to 20,000,000 records with an average of 20 columns in all cases, and the response times were acceptable.</li><li>I am very sure that if a data compression strategy is used to transfer the data chunks to the processors, the response times would improve, only if the compression time plus the decompression time is less than the time without compression.</li><li>Code is not ready for production, other date formats need to be taken into account in order to get more precision inferring.</li><li>What could be null values and what could be boolean values are features that might be parameterizable as well.</li></ul><blockquote>Check out my GitHub repository <a href="https://github.com/Wittline/csv-schema-inference"><strong>csv-schema-inference</strong></a> for more information about the code.</blockquote><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7a45d41ad4df" width="1" height="1" alt=""><hr><p><a href="https://itnext.io/building-a-schema-inference-data-pipeline-for-large-csv-files-7a45d41ad4df">Building a Schema Inference Data Pipeline for Large CSV files</a> was originally published in <a href="https://itnext.io/">ITNEXT</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Building, Preparing and Cleaning a Real Estate Dataset]]></title>
            <link>https://python.plainenglish.io/building-preparing-and-cleaning-a-real-estate-dataset-7f056e556bd0?source=rss-5a425beaf13------2</link>
            <guid isPermaLink="false">https://medium.com/p/7f056e556bd0</guid>
            <category><![CDATA[web-scraping]]></category>
            <category><![CDATA[fastapi]]></category>
            <category><![CDATA[web-scraping-tools]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[docker]]></category>
            <dc:creator><![CDATA[Ramses Alexander Coraspe Valdez]]></dc:creator>
            <pubDate>Tue, 14 Jun 2022 21:46:24 GMT</pubDate>
            <atom:updated>2022-07-11T14:14:36.289Z</atom:updated>
            <cc:license>http://creativecommons.org/licenses/by/4.0/</cc:license>
            <content:encoded><![CDATA[<h3>How to Build, Prepare, and Clean a Real Estate Dataset</h3><h4>Dockerizing a Python Script for Faster Web Scraping</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*cJiCCt7hc7uOM_zX4yXZ3A.png" /><figcaption>Docker environment</figcaption></figure><p>This article aims to show you <strong>how to dockerize a Python script</strong> for faster <a href="https://en.wikipedia.org/wiki/Web_scraping"><strong>web scraping</strong></a>, store the scraped data in an <a href="https://www.sqlite.org/index.html"><strong>SQLite</strong></a><strong> </strong>database, and consume and explore the extracted data using a <a href="https://fastapi.tiangolo.com/"><strong>FastAPI </strong></a>application.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*r9KkmybvwaJ-ZSCS.png" /><figcaption>Dockerfile</figcaption></figure><h3>The project consists of two processes:</h3><h4>Python script</h4><p>A Python script that is executed when the docker image is created, will be responsible for extracting data from the target website: <a href="http://www.metroscubicos.com/">www.metroscubicos.com</a> and will put it in an SQLite database, this script is called <strong>publicaciones.py</strong>, it receives the following arguments as parameters:</p><pre>python publicaciones.py -t 50 -s 48</pre><ul><li><strong>-t</strong> indicates the number of total elements to be scraped from the website.</li><li><strong>- s</strong> indicates the number of elements per page that the website contains, last parameter is not needed, by default, it uses the number 48. if the structure of the site changes in the future, then this parameter can be changed at your convenience.</li></ul><p>The dockerfile file will be in charge of running the below command when the docker Image is built.</p><pre>RUN python -u ./app/publicaciones.py -t 50 -s 48</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*eAH1lqFpRN-vC4jN.png" /><figcaption>Number of page and Elements by page</figcaption></figure><h4>FastAPI app</h4><p>Once the Docker image was built, the FastAPI app will be initialized, the dockerfile file is in charge of running the command:</p><pre>CMD [“python”, “./app/main.py”]</pre><p>It will expose a rest service, you can try to call it by using the address localhost as follows:</p><p><a href="http://127.0.0.1:8000/items/2"><strong>http://127.0.0.1:8000/items/2</strong></a></p><p>This example will return two records stored in the database <strong>metroscubicos.sqlite</strong> in the <strong>ESTATE </strong>table.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*34n6ilCbXzswC9U1.png" /><figcaption>Calling the rest API</figcaption></figure><h3>How to run</h3><p>In order to make this example work correctly please follow the next steps:</p><ul><li>Install <a href="https://www.stanleyulili.com/git/how-to-install-git-bash-on-windows/">git-bash for windows</a>, once installed, open git bash and download this repository, this will download the app folder and the <strong>Dockerfile </strong>file, and other files needed.</li></ul><pre>ramse@DESKTOP-K6K6E5A MINGW64 /c/documents/github<br>$ git clone https://github.com/Wittline/data-engineering-challenge-th.git</pre><ul><li>Install <a href="https://docs.docker.com/docker-for-windows/install/">Docker Desktop on Windows</a>, it will install docker-compose as well, docker-compose will allow you to run multiple container applications.</li><li>Once all the files needed were downloaded from the repository, Let’s run everything we will use the git bash tool again, go to the folder <strong>data-engineering-challenge-th,</strong> we will run the Dockerfile using the command:</li></ul><pre>ramse@DESKTOP-K6K6E5A MINGW64 ~/documents/github/data-engineering-challenge-th<br>docker build -t python-th . --progress=plain</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/609/0*VT_msGXnbeKfZtVQ.png" /></figure><ul><li>Once the above command was executed and finished, proceed with the container creation using the image: <strong>python-th</strong> and then start the container, using the below command:</li></ul><pre>ramse@DESKTOP-K6K6E5A MINGW64 ~/documents/github/data-engineering-challenge-th<br>docker run -p 8000:8000 python-th</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/604/0*gc5phKHyIf1FYcB1.png" /></figure><ul><li>Ready!. Web scraping process was executed and the fastApi App exposed, lets try looking into this url: <a href="http://127.0.0.1:8000/items/2"><strong>http://127.0.0.1:8000/items/2</strong></a></li><li>If you want stop everything, go and open another gitbash windows and use the below command:</li></ul><pre>ramse@DESKTOP-K6K6E5A MINGW64 ~/documents/github/data-engineering-challenge-th<br>docker container ls</pre><ul><li>Now use the below command using the CONTAINER ID shown in the last step, this will stop everything:</li></ul><pre>ramse@DESKTOP-K6K6E5A MINGW64 ~/documents/github/data-engineering-challenge-th<br>docker stop 7f2181fe515d</pre><ul><li>If you want to release your resources, then you can use the below command, this will delete everything:</li></ul><pre>ramse@DESKTOP-K6K6E5A MINGW64 ~/documents/github/data-engineering-challenge-th<br>docker system prune -a</pre><blockquote>Check out my GitHub repository <a href="https://github.com/Wittline/data-engineering-challenge-th">data-engineering-challenge-th</a> for more information about the project.</blockquote><p><em>More content at </em><a href="https://plainenglish.io/"><strong><em>PlainEnglish.io</em></strong></a><em>. Sign up for our</em><strong><em> </em></strong><a href="http://newsletter.plainenglish.io/"><strong><em>free weekly newsletter</em></strong></a><em>. Follow us on </em><a href="https://twitter.com/inPlainEngHQ"><strong><em>Twitter</em></strong></a><em> and </em><a href="https://www.linkedin.com/company/inplainenglish/"><strong><em>LinkedIn</em></strong></a><em>. Check out our </em><a href="https://discord.gg/GtDtUAvyhW"><strong><em>Community Discord</em></strong></a><em> and join our </em><a href="https://inplainenglish.pallet.com/talent/welcome"><strong><em>Talent Collective</em></strong></a><em>.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7f056e556bd0" width="1" height="1" alt=""><hr><p><a href="https://python.plainenglish.io/building-preparing-and-cleaning-a-real-estate-dataset-7f056e556bd0">Building, Preparing and Cleaning a Real Estate Dataset</a> was originally published in <a href="https://python.plainenglish.io/">Python in Plain English</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Building Real-time communication with Apache Spark through Apache Livy]]></title>
            <link>https://itnext.io/building-real-time-interactions-with-apache-spark-through-apache-livy-53169d87d012?source=rss-5a425beaf13------2</link>
            <guid isPermaLink="false">https://medium.com/p/53169d87d012</guid>
            <category><![CDATA[apache-livy]]></category>
            <category><![CDATA[big-data]]></category>
            <category><![CDATA[dockerizing]]></category>
            <category><![CDATA[docker]]></category>
            <category><![CDATA[apache-spark]]></category>
            <dc:creator><![CDATA[Ramses Alexander Coraspe Valdez]]></dc:creator>
            <pubDate>Sun, 12 Jun 2022 23:53:24 GMT</pubDate>
            <atom:updated>2022-07-09T01:12:44.116Z</atom:updated>
            <cc:license>https://creativecommons.org/licenses/by-nd/4.0/</cc:license>
            <content:encoded><![CDATA[<h4>Dockerizing and Consuming an Apache Livy environment</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/616/1*HbAb-TZbRbhwf4QGUlB3_w.png" /><figcaption>Dockerizing and Consuming an Apache Livy environment</figcaption></figure><p>We all know what Apache Spark is, There are two approaches to submit jobs to an Apache Spark cluster programmatically and each of them comes with some limitations in order to achieve a real time interaction, <strong>spark-submit</strong> and <strong>spark-shell</strong> are the only options available to submit spark apps to an Apache Spark Cluster, but, <strong><em>what would happen in the cases when you want to submit spark-jobs interactively from a web or mobile application</em></strong>?</p><p>There are cases where your Apache <strong><em>Spark cluster could be hosted in an on-premise infrastructure</em></strong>, and you would need many users consuming and running heavy aggregations against your organization’s data sources concurrently, from their mobile phones, web or desktop applications, create a “<strong>Spark-as-a-Service</strong>” environment to solve what I mentioned above <strong><em>is not as difficult as it sounds</em></strong>, one solution could be to expose your JDBC/ODBC data sources via <strong><em>Spark thrift server,</em></strong> another alternative would be to use <a href="https://livy.apache.org/">Apache Livy</a>.</p><p>I don’t know if Apache Livy should now be seen as a Workaround due to Apache Spark’s aggressive foray into the cloud with technologies like<a href="https://cloud.google.com/dataproc"> <strong>Google Cloud Dataproc</strong></a> or <a href="https://aws.amazon.com/es/emr/"><strong>AWS EMR</strong></a>, but, this article shows and explains a dockerized environment that <em>you can use as a template to quickly deploy a consumable Apache Livy environment</em>.</p><p>I will try to be brief explaining what Apache Livy is: <em>is a service that enables easy interaction with an Apache Spark cluster over a REST interface</em>, check the image below:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Wcq3vZnhKjJtkFHxMsa26A.png" /><figcaption>Basic example of an <strong>Apache Livy interface</strong></figcaption></figure><p>As you can see, In order to reproduce a real example we would need three components:</p><ol><li>Apache Spark Cluster</li><li>Apache Livy Server</li><li>Apache Livy Client</li></ol><p>As an additional component I would add <strong>docker </strong>for a faster implementation, and a <strong>PostgreSQL </strong>database server to simulate an external data source available for Apache Spark.</p><h3>let’s start</h3><h4><strong>Apache Spark Cluster</strong></h4><p>I will create a <strong>docker-compose.yaml</strong> file, and I will add three containers <strong>spark-master</strong>, <strong>spark-worker1</strong> and <strong>spark-worker2</strong> using the image: <strong>docker.io/bitnami/spark:2</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*YNbkTpMOIFGnMvq2dkv9zw.png" /><figcaption>Apache Spark Cluster on Docker</figcaption></figure><h4>Apache Livy Server</h4><p>Into the same <strong>docker-compose.yaml</strong> file, I will add an Apache Livy Server as a new container using the Dockerfile shown later:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*kVsYFgP4Ng1lxa6Xi4Y5XQ.png" /><figcaption>Apache Livy Server on Docker</figcaption></figure><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/ef7f734edeee81356845d918f23739d1/href">https://medium.com/media/ef7f734edeee81356845d918f23739d1/href</a></iframe><h4><strong>PostgreSQL </strong>database server</h4><p>I added to the <strong>docker-compose.yaml </strong>the container called <strong>pg_container </strong>using the image: <strong>postgres, </strong>our example executes a pyspark script that uses apache spark to interact with an external data source, this will be our external data source.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*H7mYM-HdMJAaYZZY_mpcbA.png" /><figcaption>PostgreSQL on docker</figcaption></figure><p><strong><em>Our docker-compose.yaml file is ready, check it below:</em></strong></p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/b03e630d4096ee324d7ece8f09b5b058/href">https://medium.com/media/b03e630d4096ee324d7ece8f09b5b058/href</a></iframe><h4>Apache Livy Client</h4><p>I found some classes to consume an Apache Livy REST interface, I made some slight adaptations to it, and released it as a Python package on <a href="https://pypi.org/"><strong>PyPi</strong></a><strong> </strong>with the name <a href="https://pypi.org/project/livyc/"><em>livyc</em></a><strong>. </strong>You can check the github repository here: <a href="https://github.com/Wittline/livyc"><strong>livyc</strong></a></p><p>In order to reproduce the experiment we would need to follow the next steps:</p><ul><li>Install <a href="https://docs.docker.com/docker-for-windows/install/"><strong><em>Docker Desktop on Windows</em></strong></a>, it will install Docker Compose as well, Docker Compose will allow you to run multiple container applications.</li><li>Install <a href="https://www.stanleyulili.com/git/how-to-install-git-bash-on-windows/"><strong><em>git-bash for windows</em></strong></a>, once installed, open git bash and download this repository, this will download the <strong><em>docker-compose.yaml</em></strong> file, and other files needed.</li></ul><pre>ramse@DESKTOP-K6K6E5A MINGW64 /c<br>$ git clone <a href="https://github.com/Wittline/docker-livy.git">https://github.com/Wittline/docker-livy.git</a></pre><ul><li>Once all the files needed were downloaded from the repository, let’s run everything. We will use the git bash tool again, go to the folder <strong><em>docker-livy </em></strong>and we will run the Docker Compose command:</li></ul><blockquote>Check out my GitHub repository <a href="https://github.com/Wittline/docker-livy">docker-livy</a> for more information about the project.</blockquote><pre>ramse@DESKTOP-K6K6E5A MINGW64 /c<br>$ cd docker-livy</pre><pre>ramse@DESKTOP-K6K6E5A MINGW64 /c/docker-livy<br>$ cd code</pre><pre>ramse@DESKTOP-K6K6E5A MINGW64 /c/docker-livy/code<br>$ cd apps</pre><pre>@DESKTOP-K6K6E5A MINGW64 /c/docker-livy/code/apps<br>$ docker-compose up -d --build</pre><ul><li>Wait for a minutes, and when the execution of the last command is finished, use the following command to check the status of all the containers.</li></ul><pre>docker ps</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Tc3PtOvny1YDlOzTX54GIQ.png" /><figcaption>docker ps</figcaption></figure><ul><li>If everything is up and running then we are going to move forward with other steps to interact with the Apache Livy interface using <a href="https://github.com/Wittline/livyc"><strong>livyc</strong></a></li><li><strong>Setiting a local environment with Google Colab</strong></li><li>Go to <strong><em>git-bash </em></strong>and put the next command:</li></ul><pre>ramse@DESKTOP-K6K6E5A MINGW64 ~<br><strong>jupyter notebook --NotebookApp.allow_origin=&#39;https://colab.research.google.com&#39; --port=8888 --NotebookApp.port_retries=0</strong></pre><ul><li>After the execution of the last command, copy the localhost URL, you will need it for colab</li><li>Go to <a href="https://colab.research.google.com/"><strong><em>Google Colab</em></strong></a></li><li>Create a new Notebook</li><li>Go to -&gt; Connect -&gt; “Connect to local runtime” -&gt; Paste the url copied from the last step and put it in Backend URL -&gt; connect</li><li>Upload the file <strong><em>test-livy.ipynb</em></strong>, and use it into your colab local env:</li><li>Run each cell and see the results on each steps.</li></ul><p>Note that among the actions taken in the <strong><em>test-livy.ipynb</em></strong> file, the postgres database is being populated with data using an external .csv file, this in order for apache spark to have data to interact with.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/90c1578a0746595021a2ddf634958644/href">https://medium.com/media/90c1578a0746595021a2ddf634958644/href</a></iframe><ul><li>If you want to monitorize all the session created in Apache Livy Server go to the address: <a href="http://localhost:8998/ui">http://localhost:8998/ui</a></li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*q946XNw3Zx6C33s6TWGYXQ.png" /><figcaption>Livy Sessions</figcaption></figure><blockquote>Check out my GitHub repository <a href="https://github.com/Wittline/docker-livy">docker-livy</a> for more information about the project.</blockquote><h4>Conclusions</h4><p>The Python Package <a href="https://github.com/Wittline/livyc"><strong>livyc</strong></a><strong> </strong>works well to submit pyspark scripts dynamically and asynchronously to the Apache Livy server, this in turn interacts with the Apache Spark Cluster in a transparent way, check this project and remember to check all the files before interacting with the jupyter notebook file.</p><blockquote>Check out my GitHub repository <a href="https://github.com/Wittline/docker-livy">docker-livy</a> for more information about the project.</blockquote><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=53169d87d012" width="1" height="1" alt=""><hr><p><a href="https://itnext.io/building-real-time-interactions-with-apache-spark-through-apache-livy-53169d87d012">Building Real-time communication with Apache Spark through Apache Livy</a> was originally published in <a href="https://itnext.io/">ITNEXT</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to build a DAG based Task Scheduling tool for Multiprocessor systems using python]]></title>
            <link>https://itnext.io/how-to-build-a-dag-based-task-scheduling-tool-for-multiprocessor-systems-using-python-d11a093a835b?source=rss-5a425beaf13------2</link>
            <guid isPermaLink="false">https://medium.com/p/d11a093a835b</guid>
            <category><![CDATA[task-scheduling]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[google-cloud]]></category>
            <category><![CDATA[big-data]]></category>
            <dc:creator><![CDATA[Ramses Alexander Coraspe Valdez]]></dc:creator>
            <pubDate>Tue, 07 Jun 2022 16:13:02 GMT</pubDate>
            <atom:updated>2022-06-09T23:58:34.960Z</atom:updated>
            <cc:license>http://creativecommons.org/licenses/by/4.0/</cc:license>
            <content:encoded><![CDATA[<p>Scheduling Big Data Workloads and Data Pipelines in the Cloud with pyDag</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ZfUdWxE6QPIysnXl9tuLcg.png" /><figcaption><strong>PyDag</strong></figcaption></figure><p>Much of the success of data driven companies of different sizes, from startups to large corporations, has been based on the good practices of their operations and the way how they keep their data up to date, they are dealing daily with <strong>variety</strong>, <strong>velocity </strong>and <strong>volume </strong>of their data, In most cases their strategies depend on those features. Some of the aims of the data team in this type of companies are:</p><ul><li>Design and deploy cost effective and scalable data architectures</li><li>Get insights from their data</li><li>Keep the business and operations up and running</li></ul><p>In order to achieve these aims the data team uses tools, most of these tools allow them to <strong>extract</strong>, <strong>transform </strong>and <strong>load </strong>data to other places or destination data sources, visualize data and convert data into information. It is very common to see ETL tools, <strong><em>task scheduling</em></strong>,<strong><em> job scheduling</em></strong> or <strong><em>workflow scheduling</em></strong> tools in these teams. It is worth mentioning that the terms: <strong><em>task scheduling</em></strong>, <strong><em>job scheduling</em></strong>, <strong><em>workflow scheduling</em></strong>, <strong><em>task orchestration</em></strong>, <strong><em>job orchestration</em></strong> and <strong><em>workflow orchestration</em></strong> are the same concept, what could distinguish them in some cases is the purpose of the tool and its architecture, some of these tools are just for orchestrate ETL processes and specify when they are going to be executed simply by using a pipeline architecture, others use DAG architecture, as well as offer to specify when the DAG is executed and how to orchestrate the execution of its tasks (vertices) in the correct order.</p><p>The advantage of this last architecture is that all the computation can be used on the machine where the DAG is being executed, giving priority to running some tasks (vetices) of the DAG in parallel.</p><blockquote>A graph is a collection of vertices (tasks) and edges (connections or dependencies between vertices). Therefore, a directed acyclic graph or DAG is a directed graph with no cycles.</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2gH-Pszet0ud8WxiPzjNww.png" /><figcaption>DAG</figcaption></figure><blockquote>A pipeline is a kind of DAG but with limitations where each vertice(task) has one upstream and one downstream dependency at most.</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ZlSIRWD56nSIt0_n-_mqXA.png" /><figcaption>Pipeline</figcaption></figure><p>These kind of tools has boomed in the past several years, offering common features:</p><ul><li>Automated Workflows</li><li>Scheduling or orchestration of tasks/jobs</li><li>They allow creation or automation of ETLs or data integration processes</li><li>Use of DAGs or Pipelines data structures</li><li>Some are <strong><em>Monolithic Architectures</em></strong> and others are <strong><em>Highly Distributed</em></strong>.</li><li>Some tools do not take advantage on multiprocessor machines and others do.</li><li>Others come with their own infrastructure and others allow you to use any infrastructure in the Cloud or On-premise.</li><li>Some allow you to write the code or script related to each Dag’s tasks and others are Drag and Drop components.</li></ul><blockquote>To summarize: <strong>Orchestration </strong>and <strong>Scheduling </strong>are some of the features that some ETL tools has.</blockquote><p>I wil mention some of them here:</p><h4><a href="https://airflow.apache.org/">Apache Airflow</a>, <a href="https://www.jamsscheduler.com/">Jamsscheduler</a>, <a href="https://www.hitachivantara.com/es-latam/products/data-management-analytics/pentaho.html">Pentaho</a>, <a href="https://www.prefect.io/">Prefect.io</a>, <a href="https://argoproj.github.io/">argo</a>, <a href="https://luigi.readthedocs.io/en/stable/index.html">luigi</a>, <a href="https://dask.org/">Dask</a>, <a href="https://www.matillion.com/">Matillion</a>, <a href="https://airbyte.com/">Airbyte</a>, <a href="https://dolphinscheduler.apache.org/">DolphinScheduler </a>, <a href="https://www.stonebranch.com/">Stonebranch</a>, <a href="https://nifi.apache.org/">Apache NIFI</a>, <a href="https://www.stitchdata.com/">Stitch</a>, <a href="https://dagster.io/">Dagster</a>, <a href="https://metaflow.org/">metaflow</a>, <a href="https://flyte.org/">Flyte</a>, <a href="https://ovsgroup.com/">One Virtual Source (OVS)</a>.</h4><p>There is big business involved in the use of these tools, from consultancy, expensive licenses and companies that maintain open source tools and has a delivery model in which a centrally hosted software is licensed to customers via a subscription plan.</p><h3>Problem Statement</h3><p>The goal of this article is to teach you how to design and build a simple DAG based Task Scheduling tool for Multiprocessor systems, which could help you reduce bill costs generated by this kind of technologies in your company or create your own and start up a profitable business based on this kind of tool.</p><h3>Functional requirements</h3><ul><li>It should be a Python module</li><li>By now the module only should receive a .json or .yaml file with the specifications of the tasks and their dependencies.</li><li>The tasks will be based on standalone scripts</li><li>The tool should work with any cloud or on-premise provider</li><li>The tool should bring up, shut down and stop infrastructure for itself in the selected cloud provider</li><li>The tool should take advantage of the computation on the machine running the DAG, so, some tasks can be executed in parallel and can be assigned to each processor independently and thus take advantage of the machine’s resources.</li><li>The tasks should not transfer data between them, nor states.</li><li>The DAG will show as successful state if and only if all tasks ran successfully.</li><li>The tool should display and assign status to tasks at runtime.</li></ul><h3>Let’s build pyDag step by step:</h3><h4>1. Chosen Infrastructure: Google Cloud Platform</h4><p>Although the library was built to accept any type cloud provider or on-premise infrastructures, in this case we will use <a href="https://cloud.google.com/">Google Cloud Platform</a> as the cloud provider, we will create three layers:</p><ul><li>A layer that will work as an <strong>ingestion layer</strong>, specifically for big data workloads, in this way we could move data from any external data source to our staging area, in this case we will use an Apache Spark cluster using <a href="https://cloud.google.com/dataproc">Google Cloud Dataproc</a>. Another option would be to use <a href="https://cloud.google.com/functions">Google Cloud Functions</a> as a data ingestion layer, but what if we need to make previous transformations before moving the data to a staging area? In this case, Google cloud functions has a very clear disadvantage compared to Google Cloud Dataproc,<a href="https://www.analyticsvidhya.com/blog/2020/10/data-engineering-101-data-sources-apache-spark/#:~:text=Spark%20has%20six%20%E2%80%9Ccore%E2%80%9D%20data,sources%20written%20by%20the%20community."> another advantage of Google Cloud Dataproc is that it can use a variety of external data sources</a>. <a href="https://livy.apache.org/">Apache livy</a> could be another option if you choose on-premise infrastructure.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Mz1p4nZOdRwV8maRneFCsQ.png" /><figcaption>1. Google Cloud Dataproc, 2. Apache Livy for on-premises infraestructure and Google Cloud functions discarded for data ingestion</figcaption></figure><ul><li>A layer that will work as a <strong>staging zone</strong> with <a href="https://cloud.google.com/bigquery">BigQuery</a>, distributed data storage, and data warehousing, all data from external sources will be moved using the ingestion layer to this layer. Everything to bigquery.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/170/1*AxPii3K1gIM_WXMmDQFaCA.png" /><figcaption><strong>BigQuery</strong></figcaption></figure><ul><li>A <strong>storage layer</strong> using <a href="https://cloud.google.com/storage">Google Cloud Storage</a>, which will take care of:</li></ul><p>▹<em>Store the SQL scripts that are executed on top of bigquery</em></p><p>▹<em>Store IAC scripts</em></p><p>▹<em>Store pySpark scripts for data ingestion from dataproc to bigquery</em></p><p>▹<em>Store the output logs of the Jobs that are launched to the dataproc cluster</em></p><p>▹<em>Store temporary data to be moved to bigquery from a dataproc job in a </em><strong><em>temporaryGcsBucket </em></strong><em>bucket.</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*nm3e3w1cSGha-OJ0oPyGmw.png" /><figcaption>Default <strong>Google Cloud Platform</strong> Infrastructure for <strong>pyDag</strong></figcaption></figure><h4>2. DAG data structure</h4><p>This step consists on creating a object class that contains the structure of the graph and some methods like adding vertices (tasks) to the graph, creating edges (dependencies) between the vertices (tasks) and perform basic validations such as detecting when the graph is generating a cycle.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*LsI0Jux5ddoXrBPq5b_urg.png" /><figcaption><strong>Dag data structure</strong></figcaption></figure><h4>3. Topological Sorting and Parallel Execution</h4><p>This is an interesting part, consider the problem of scheduling tasks which has dependencies between them, let’s suppose task “sendOrders” can only be done after task “getProviders” and “getItems” have been completed successfully. We can obtain such dependencies using a DAG which would contain an edge “getProviders”-&gt;”sendOrders” and edge “getItems”-&gt;”sendOrders”, so, by using the above example a Topological Sort algorithm would give us an order in which these tasks could be completed step by step respecting the correct order between them and their dependencies.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*pNUFUKs3RzIYXOPxEYmNDw.png" /><figcaption><strong>TopologicalSort</strong>, <strong>ParallelProcessor </strong>and <strong>Executor</strong></figcaption></figure><p>So, the Topological Sort Algorithm will be a method inside the pyDag class, it will be called “run”, this algorithm in each step will be providing the next tasks that can be executed in parallel. There is a lot of research on this kind of techniques, but I will take the quickest solution which is to apply topological sort to the DAG.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/410/1*uL-YjRQ4DQgYWD1kfLwvLQ.png" /><figcaption><strong>DAG using 4 processors, It will take three steps to complete the DAG</strong></figcaption></figure><p>I’ll use multiprocessing to execute fewer or equal number of tasks in parallel. Suppose that initially in the first iteration of the topological sort algorithm there is a number of non-dependent tasks that can be executed in parallel, and this number could be greater than the number of available processors in the computer, the ParallelProcessor class will be able to accept and execute these tasks using only one pool with the available processors and the other tasks are executed in a next iteration. The convenient thing is to send to the pyDag class how many tasks in parallel it can execute, this will be the number of non-dependent vertices(tasks) that could be executed at the same time.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/737/1*F5RgGky_sbPfmUv700y0gw.png" /><figcaption><strong>The possible states for a Task Instance in pyDag in a Multiprocessor system</strong></figcaption></figure><p>The executor class will help me to keep states and know what are the current states of each task in the DAG</p><h4>4. Engines and Handlers</h4><p>Each task is tied to an specific type of engine, in this way there can be versatility to be able to communicate tasks that are implemented in different technologies, and with any cloud provider, but before going deeper with this, let’s explain the basic structure of a task in pyDag:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/12a3d11bb2d02c0450810f7409d4600c/href">https://medium.com/media/12a3d11bb2d02c0450810f7409d4600c/href</a></iframe><p>As you can see in the structure of the .json file that represents the DAG, specifically for a task, the <strong><em>script </em></strong>property gives us all the information about a specific task. Using 5 levels of information:</p><blockquote>Location.Bucket.Folder.Engine.Script_name</blockquote><ul><li><strong><em>Location:</em></strong> it tells us in what type of platform the script is stored, by default everything is better to have it on the same type of cloud provider or location, It couldn’t necessarily be that way, some options could be: gcs, s3, local or database.</li><li><strong><em>Bucket: </em></strong>this will be the name of the bucket where the script is hosted, it will not affect if “gcs” or “s3” are chosen, if “local” is chosen, then this level and the next level will represent folders, and if it is a “database” this level and the next level will represent the database name and the table in the database. It is recommended to use the project name as the bucket name.</li><li><strong><em>Folder: </em></strong>it is a folder inside the bucket, or a local folder or a table in a database. It is recommended here to use the name of a module within the project.</li><li><strong><em>Engine:</em></strong> it tells us what type of engine the next level “<strong>Script_name</strong>” is going to use, for example if my <strong>engine = spark</strong> this implies that the script is executed on a Dataproc cluster, if my <strong>engine = bq</strong> this implies that my script will be executed in BigQuery, if my <strong>engine = iac</strong> this implies that my script will provide infrastructure on the selected cloud platform.</li><li><strong><em>Script_name: </em></strong>script name or file</li></ul><p><strong>Examples</strong>:</p><p><strong><em>“script” : “gcs.project-pydag.iac_scripts.iac.dataproc_create_cluster”</em></strong></p><p>The script called “<strong>dataproc_create_cluster</strong>” is hosted in GCS in the bucket “<strong>project-pydag</strong>” inside the folder : “<strong>iac_scripts</strong>” and its engine is: “<strong>iac</strong>”, this handle and set up infraestructure in the cloud.</p><p><strong><em>“script” : “gcs.project-pydag.module_name.spark.csv_gcs_to_bq”</em></strong></p><p>The script called “<strong>csv_gcs_to_bq</strong>” is hosted in GCS, in the bucket “<strong>project-pydag</strong>” inside the folder “<strong>module_name</strong>” and its engine is “<strong>spark</strong>” this means that the script will be executed in a Dataproc cluster.</p><p><strong><em>“script” : “gcs.project-pydag.module_name.bq.create_table”</em></strong></p><p>The script called “<strong>create_table</strong>” is hosted in GCS, in the bucket “<strong>project-pydag</strong>” inside the folder “<strong>module_name</strong>” and its engine is: “<strong>bq</strong>” this means that the script is going to be executed in a BigQuery cluster.</p><p><strong>The process of running a task is totally dynamic, and is based on the following steps:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*SCX9uIc6EdfyzkcmltJDRw.png" /><figcaption><strong>EngineHandler, ScriptHandler, Engine</strong></figcaption></figure><ul><li>Every time it is trying to run a task, <strong>pyDag </strong>call the inherited method “<strong>run</strong>” that belongs to the “<strong>Engine</strong>” class, internally this method uses two other inherited methods, “<strong>format_script</strong>” from the “<strong>script_handler</strong>” class and “<strong>run_script</strong>” from the “<strong>engine_handler</strong>” class.</li><li>Firstly, the “<strong>format_script</strong>” method obtains the script from its location, and returns the parameters that the script receives well formatted, and an object of the Engine class.</li><li>Once the data from the previous step is returned, the “<strong>run_script</strong>” method of the “<strong>engine_handler</strong>” will take care of creating the engine dynamically, and it will call the “<strong>run_script</strong>” method against the engine received, in this way we avoid adding more code to the library, and we only focus to design new engines, we have the flexibility that a task can use any type of technology on cloud or on-premise.</li></ul><p>This way of doing it could cause security issues in the future, but in a next version I will improve it</p><p>Engines are client applications that you should add to pyDag, In order to provide the technology you want to your tasks, the steps to add a new engine is by adding to the <strong>config.cfg</strong> file where your engine will be and adding your “<strong>clientclass.py</strong>” with a method called “<strong>run_script</strong>” which will be responsible for receiving the name of the script or the script string.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OuQrkX_18eyRE4_3Ylw5tg.png" /><figcaption>Three engines responsible for interacting with the infrastructure: <strong>BQClient</strong>, <strong>IACClient </strong>and <strong>DPClient</strong></figcaption></figure><p>By default pyDag offers three types of engines:</p><ul><li><strong>BQClient</strong>: it is the client that receives any sql script and execute it against <em>BigQuery</em></li><li><strong>DPClient</strong>: It is the <em>Google Cloud Datapro</em>c client, it receives a pyspark script and all its associated parameters and executes it against the cluster that you should indicate in the parameters.</li><li><strong>IACClient: </strong>it is a client that I invented, so, some tasks will only work as builders of infrastructure in the cloud, for example: create a dataproc cluster, delete a dataproc cluster or stop a dataproc cluster.</li></ul><p>A good exercise would be to create a Google Cloud Function engine, this way you could create tasks that only execute Python Code in the cloud.</p><h4>5. Reducing latency by keeping traffic locally</h4><p>If a DAG has 10 tasks and runs 4 times by day in production, this means we will fetch the string script 40 times in one day, just for a DAG, now what if your business or enterprise operations have 10 DAGs running with different intervals and each DAG has on average 10 tasks? There would be many unnecessary requests to your GCS bucket, creating costs and adding more execution time to the task, unnecessaryrequests could be cached locally using redis. The “<strong>script_handler</strong>” class will be responsible to keep scripts cached.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*57Y6nBK__VdD_epDU5PTaA.png" /><figcaption>Cache with redis</figcaption></figure><h4>6. pyDag’s Architecture for a Multiprocessor Machine.</h4><p>As we can see, an object of the <strong>pyDag </strong>class contains everything mentioned above, the architecture is almost ready. I will show you an whole overview of the architecture below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*jLpdZj4zOpRWSI4ISbFIbg.png" /><figcaption><strong>pyDag</strong></figcaption></figure><h4><strong>7. Let’s run an example</strong></h4><h4>GCP - API credentials in JSON</h4><ul><li>Go to the Google Cloud console</li><li>Create a new project</li></ul><p><strong><em>Follow the steps in this video to create Api Credentials in Json :</em></strong></p><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fwww.youtube.com%2Fembed%2FrWcLDax-VmM%3Ffeature%3Doembed&amp;display_name=YouTube&amp;url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DrWcLDax-VmM&amp;image=https%3A%2F%2Fi.ytimg.com%2Fvi%2FrWcLDax-VmM%2Fhqdefault.jpg&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=youtube" width="854" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/081622d65a9f72b4bab897c082126e6a/href">https://medium.com/media/081622d65a9f72b4bab897c082126e6a/href</a></iframe><h4>GCP - BigQuery</h4><ul><li>Go to BigQuery</li><li>Create a new dataset called: <strong><em>datasettest</em></strong></li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*UWgq-0kHlrFiizhejfV6zg.png" /><figcaption>BigQuery dataset: <strong><em>datasettest</em></strong></figcaption></figure><h4>GCP - Dataproc</h4><ul><li>Go to Dataproc</li><li>Now click on Enable API</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*nc_EFsB0xaCjgGoXVobGeA.png" /></figure><h4>LOCAL MACHINE</h4><ul><li>Install <a href="https://docs.docker.com/docker-for-windows/install/"><strong><em>Docker Desktop on Windows</em></strong></a>, it will install Docker Compose as well, Docker Compose will allow you to run multiple container applications.</li><li>Install <a href="https://www.stanleyulili.com/git/how-to-install-git-bash-on-windows/"><strong><em>git-bash for windows</em></strong></a>, once installed, open git bash and download this repository, this will download the <strong><em>docker-compose.yaml</em></strong> file, and other files needed.</li></ul><pre>ramse@DESKTOP-K6K6E5A MINGW64 /c<br>$ git clone <a href="https://github.com/Wittline/uber-expenses-tracking.git">https://github.com/Wittline/pyDag.git</a></pre><ul><li>Once all the files needed were downloaded from the repository, let’s run everything. We will use the git bash tool again, go to the folder <em>pyDag</em> and we will run the Docker Compose command:</li></ul><pre>ramse@DESKTOP-K6K6E5A MINGW64 /c<br>$ cd pyDag</pre><pre>ramse@DESKTOP-K6K6E5A MINGW64 /c/pyDag<br>$ cd code</pre><pre>ramse@DESKTOP-K6K6E5A MINGW64 /c/pyDag/code<br>$ cd apps</pre><pre>@DESKTOP-K6K6E5A MINGW64 /c/pyDag/code/apps<br>$ docker-compose up</pre><ul><li>Go to the logs folder and check the output</li></ul><p><strong>Let’s explain the example</strong></p><p>There are many configurations for the DAG that could work for this example, the most appropriate and the shortest is the second approach shown in the image below, I discarded the first approach, both approaches achieve the same goal, but, with the second approach there is more chances to take advantage of the parallelism and improve the overall latency.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qCXV8bQPq9MtYT3YLZ46dA.png" /><figcaption><strong>DAG First Approach</strong></figcaption></figure><p>This example is just to demonstrate that this tool can reach various levels of granularity, the example can be built in fewer steps, in fact using a single query against <em>BigQuery</em>, but it is a very simple example to see how it works.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6qmJ2FrUFxiwxIXNhy_ATw.png" /><figcaption><strong>Dag Second Approach</strong></figcaption></figure><ul><li><strong>startup_dataproc_1</strong>: Create a Dataproc cluster in GCP with the name: “<em>cluster-dataproc-pydag-2022&quot;.</em></li></ul><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/238b1ac339afbf0d90e47aed5feb4653/href">https://medium.com/media/238b1ac339afbf0d90e47aed5feb4653/href</a></iframe><ul><li><strong>create_table_final: </strong>Create the final table “<em>mytable3</em><strong>” </strong>in BigQuery<strong>, </strong>here<strong> </strong>we want the data chosen and cleaned.</li></ul><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/eb150a435cd379cb1a1ef402f5b510f5/href">https://medium.com/media/eb150a435cd379cb1a1ef402f5b510f5/href</a></iframe><ul><li><strong>create_table_stg_1: </strong>Create the table<strong> <em>“</em></strong><em>mytable1” </em>in BigQuery.</li></ul><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/8cc73d6107de152e9a61ec23605da31f/href">https://medium.com/media/8cc73d6107de152e9a61ec23605da31f/href</a></iframe><ul><li><strong>create_table_stg_2: </strong>Create the table<strong> <em>“</em></strong><em>mytable2” </em>in BigQuery.</li></ul><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/8cc73d6107de152e9a61ec23605da31f/href">https://medium.com/media/8cc73d6107de152e9a61ec23605da31f/href</a></iframe><ul><li><strong>initial_ingestion_1: </strong>This task will move the data from the .CSV file stored in Google cloud Storage to BigQuery, this script will be executed in the already created Dataproc cluster with the task: “<em>startup_dataproc_1”</em>. In this case the Dataproc cluster will work as a ingestion layer, the destination table is called “table_stg<em>”.</em></li></ul><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/9f29f24b6e6a6d9783dbc29af9b0a011/href">https://medium.com/media/9f29f24b6e6a6d9783dbc29af9b0a011/href</a></iframe><ul><li><strong>extract_from_stg_1:</strong>This task will move the data from the “<em>table_stg</em>” to “<em>mytable1</em>&quot; only will take records with the following conditions<strong>: </strong><em>‘year’:’2022&#39; and ‘category’:’cars’</em></li></ul><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/56c51bc6d8e9fe9b8723d95b989100e6/href">https://medium.com/media/56c51bc6d8e9fe9b8723d95b989100e6/href</a></iframe><ul><li><strong>extract_from_stg_2: </strong>This task will move the data from the “<em>table_stg</em>” to “<em>mytable2</em>” only will take records with the following conditions<strong>: </strong><em>‘year’:’2021&#39; and ‘category’:’food’.</em></li></ul><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/56c51bc6d8e9fe9b8723d95b989100e6/href">https://medium.com/media/56c51bc6d8e9fe9b8723d95b989100e6/href</a></iframe><ul><li><strong>insert_to_fact: </strong>This task will populate the data from<strong> </strong>“<em>mytable1</em>” and “<em>mytable2</em>” to “<em>mytable3&quot; using an UNION ALL.</em></li></ul><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/18e682827167e48191d97e96ab7270a8/href">https://medium.com/media/18e682827167e48191d97e96ab7270a8/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/670/1*OnQsft9yCpzoH0H9FGD7aA.png" /><figcaption><strong>Desired results</strong></figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/974/1*b0HUkec4Oq0ZMzp9QR85rw.png" /><figcaption><strong>Logs</strong></figcaption></figure><blockquote>Remember to add tasks to this example to turn off the Google Dataproc cluster and drop the staging tables that you will no longer use in BigQuery</blockquote><h3>Next steps</h3><p>In order to have an acceptable product with the minimum needed features, I will be working on adding the following:</p><ol><li>Centralized logs</li><li><em>A metadata database</em></li><li><em>Distributed Task Execution on multiple machines</em></li><li><em>Branching</em></li><li><em>DAG as a Service using an API REST.</em></li><li>GUI with drag and drop components</li></ol><h3>Conclusions</h3><p>You can clearly observe that in all cases there are two tasks taking a long time to finish “<strong>startup_dataproc_1</strong>” and “<strong>initial_ingestion_1</strong>” both related with the use of <em>Google DataProc</em>, one way to avoid the use of tasks that create Clusters in DataProc is by keeping an already cluster created and keeping it turned on waiting for tasks, with horizontally scaling, this is highly recommended for companies that has a high workloads by submitting tasks where there will be no gaps of wasted and time and resources.</p><p>You can see the effect of the caching in the executions, <strong><em>short tasks are shorter</em></strong><em> </em>in cases where the cache is turned on.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*sLwuKMew0qMktojpFvKijQ.png" /><figcaption>Gantt Chart: <strong>DAG behavior in pyDag with multiprocessors and cache</strong></figcaption></figure><p>Although the parallelism in tasks execution can be confirmed<em>, </em>we could assign a <strong><em>fixed number of processors per DAG</em></strong>, which represents the <em>max number of tasks that could be executed in parallel in a DAG</em> or <strong><em>maximum degree of parallelism</em></strong>, but this implies that sometimes there are processors that are being wasted, one way to avoid this situation is by assigning a<em> dynamic number of processors</em>, that only adapts to the number of tasks that need to be executed at the moment, in this way multiple DAGS can be executed on one machine and take advantage of processors that are not being used by other DAGS. The only issue with the above chart is that these results coming from one execution for each case, multiple executions should be done for each case and take an average time on each case, but I don’t have the enough budget to be able to do this kind of tests, the code is still very informal, and it’s not ready for production, I’ll be working on these details in order to release a more stable version.</p><blockquote>Check out my GitHub repository <a href="https://github.com/Wittline/pyDag">pyDag</a> for more information about the project</blockquote><h3>References</h3><ul><li><a href="https://github.com/victor-gil-sepulveda/pyScheduler">https://github.com/victor-gil-sepulveda/pyScheduler</a></li><li><a href="https://github.com/xianghuzhao/paradag">https://github.com/xianghuzhao/paradag</a></li><li><a href="https://medium.com/@ApacheDolphinScheduler/apache-dolphinscheduler-is-ranked-on-the-top-10-open-source-job-schedulers-wla-tools-in-2022-5d52990e6b57">https://medium.com/@ApacheDolphinScheduler/apache-dolphinscheduler-is-ranked-on-the-top-10-open-source-job-schedulers-wla-tools-in-2022-5d52990e6b57</a></li><li><a href="https://medium.com/@raxshah/system-design-design-a-distributed-job-scheduler-kiss-interview-series-753107c0104c">https://medium.com/@raxshah/system-design-design-a-distributed-job-scheduler-kiss-interview-series-753107c0104c</a></li><li><a href="https://dropbox.tech/infrastructure/asynchronous-task-scheduling-at-dropbox">https://dropbox.tech/infrastructure/asynchronous-task-scheduling-at-dropbox</a></li><li><a href="https://www.datarevenue.com/en-blog/airflow-vs-luigi-vs-argo-vs-mlflow-vs-kubeflow">https://www.datarevenue.com/en-blog/airflow-vs-luigi-vs-argo-vs-mlflow-vs-kubeflow</a></li><li><a href="https://link.springer.com/chapter/10.1007/978-981-15-5566-4_23">https://link.springer.com/chapter/10.1007/978-981-15-5566-4_23</a></li><li><a href="https://www.researchgate.net/publication/2954491_Task_scheduling_in_multiprocessing_systems">https://www.researchgate.net/publication/2954491_Task_scheduling_in_multiprocessing_systems</a></li><li><a href="https://conference.scipy.org/proceedings/scipy2015/matthew_rocklin.html">https://conference.scipy.org/proceedings/scipy2015/matthew_rocklin.html</a></li><li><a href="http://article.nadiapub.com/IJGDC/vol9_no9/10.pdf">http://article.nadiapub.com/IJGDC/vol9_no9/10.pdf</a></li></ul><blockquote>Check out my GitHub repository <a href="https://github.com/Wittline/pyDag">pyDa</a>g for more information about the project</blockquote><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d11a093a835b" width="1" height="1" alt=""><hr><p><a href="https://itnext.io/how-to-build-a-dag-based-task-scheduling-tool-for-multiprocessor-systems-using-python-d11a093a835b">How to build a DAG based Task Scheduling tool for Multiprocessor systems using python</a> was originally published in <a href="https://itnext.io/">ITNEXT</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Design, Development and Deployment of a simple Data Pipeline]]></title>
            <link>https://medium.com/geekculture/design-development-and-deployment-of-a-simple-data-pipeline-6f1d59d0fd6a?source=rss-5a425beaf13------2</link>
            <guid isPermaLink="false">https://medium.com/p/6f1d59d0fd6a</guid>
            <category><![CDATA[fastapi]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[docker-compose]]></category>
            <category><![CDATA[data-pipeline]]></category>
            <category><![CDATA[docker]]></category>
            <dc:creator><![CDATA[Ramses Alexander Coraspe Valdez]]></dc:creator>
            <pubDate>Sun, 05 Jun 2022 17:19:09 GMT</pubDate>
            <atom:updated>2022-06-08T05:57:02.026Z</atom:updated>
            <cc:license>http://creativecommons.org/licenses/by/4.0/</cc:license>
            <content:encoded><![CDATA[<p>Data Engineering technical challenge (part 1)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zYUgKMZ_OGTy9sEc21cHhA.png" /><figcaption>Dockerizing a simple data pipeline</figcaption></figure><p>This article is based on solving a technical challenge for the data engineering position, in the development of this article we will show all the steps to follow in order to design a quick solution to the challenge and then deployment it, we will follow the next steps and I will try to go straight to the point on each of them:</p><ol><li>Read and analyze requirements.</li><li>Ask ourselves questions about some requirements</li><li>Rule out quickly optional requirements</li><li>Design data architecture and choose technologies</li><li>Development</li><li>Deployment plan</li><li>Show results</li></ol><h3>Let’s start</h3><h3><strong>1. Read and analyze requirements.</strong></h3><p><strong>Let’s read some of them and highlight what matters:</strong></p><blockquote>You will create a mechanism to <strong>ingest data from one server to a postgres database</strong> and <strong>build a REST API to get the records from the database</strong>.</blockquote><ul><li>ingest data from one server to a postgres database</li><li>build a REST API to get the records from the database</li></ul><blockquote>The project consists to <strong>design the database</strong> based on the <strong>files attached</strong>, <strong>ingest the files attached into the postgres database and build a REST API to get the records from the database</strong>. At the end of this page, you will find <strong>docker-compose</strong> templates to get you started immediately with <strong>postgres </strong>and a <strong>centos instance where you can load the files</strong>. You can choose the programming language to develop the code.</blockquote><ul><li>design the database</li><li>files attached</li><li>ingest the files attached into the postgres database and build a REST API to get the records from the database</li><li>docker-compose</li><li>postgres</li><li>centos instance where you can load the files</li><li>You can choose the programming language to develop the code.</li></ul><blockquote><strong><em>The core functionality for this challenge is:</em></strong><br><strong>1.</strong> <strong>Design the E-R from the database</strong> and create the structure <strong>based on the files attached</strong>.<br><strong>2.</strong> <strong>Ingest the data from the centos server to the postgres database</strong>.<br><strong>3.</strong> At least the <strong>‘read’ request must be supported for the API</strong><br><strong>4.</strong> The <strong>server where the API is going to be deployed must have access only to the postgres database</strong>. And the <strong>centos server must have access only to the postgres database as well</strong>.</blockquote><blockquote><strong><em>Some optional functionalities you can implement:</em></strong><br><strong>1.</strong> Validate the column state has a length of 2 and only contains letters.<br><strong>2</strong>. Your <strong>code could run on Docker</strong> as well.<br><strong>3.</strong> Support more request than just ‘read’, like ‘create or ‘update’.<br><strong>4.</strong> Unit test and coverage of your code<br><strong>5. </strong>Implement CI/CD<br><strong>6.</strong> Implement a mechanism to create or update the schema</blockquote><ul><li>Design the E-R from the database … based on the files attached.</li><li>Ingest the data from the centos server to the postgres database</li><li>‘read’ request must be supported for the API</li><li>server where the API is going to be deployed must have access only to the postgres database</li><li>centos server must have access only to the postgres database as well.</li><li>code could run on Docker</li></ul><h3><strong>2. Ask ourselves questions about some requirements</strong></h3><ul><li>Does the centos instance needed?</li><li>At what level should the “<strong><em>column state has a length of 2 and only contains letters</em></strong>” validation be done?</li><li>Should the transfer of the data from the .csv file to the postgres database be automated or manual?</li><li>What kind of request “read”?</li><li>What are the requirements of the request to the API REST?</li></ul><h3><strong>3. </strong>Rule out quickly optional requirements</h3><p>I will rule out almost all optional requirements due to the additional times added by the automated scripts using a docker environment</p><h3><strong>4. Design data architecture and choose technologies</strong></h3><h4>Let’s visualize the dataset first</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*AdNWsrxLpWJMCDuQMOxuAQ.png" /><figcaption>Dataset</figcaption></figure><p>The image above shows us a dataset simple to understand, it has 11 columns and it is easy to recognize an entity-relationship data model for this dataset with 3 tables.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*K9AciAs9ShUOmlOkTfo9Rg.png" /><figcaption>E-R Model</figcaption></figure><p>Some technologies were already requested in the requirement, I will use 3 docker containers, Python FastApi, PostgreSQL the last one will serve as an ingestion layer using Python Scripts.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/558/1*Bsjp4_qgpq5JBkWbYhLyzg.png" /><figcaption>Architecture: Dockerizing a simple data pipeline</figcaption></figure><p>To summarize, once the docker container detects that the PostgreSQL database is ready to receive requests on the specified port, the data ingestion layer container will run the scripts to move the data from the .csv file to the database, at the same time the REST API container will start up and it will response when the database and data are ready to be consumed.</p><h3>5. Development</h3><ul><li>All the scripts involved in the pipeline for ingest the data to PostgreSQL are controlled by the class: <a href="https://gist.github.com/Wittline/4e7242fd44169b354155d93c0163c5b7#file-sql_queries-py"><strong>sql_queries.py</strong></a></li></ul><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/8b9a3c6872933d026bea732328cbc1f1/href">https://medium.com/media/8b9a3c6872933d026bea732328cbc1f1/href</a></iframe><ul><li>Class <strong>db_engine.py</strong> responsible of handle the connection with PostgreSQL</li></ul><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/9a6dec2df708865c86466a4467ab203e/href">https://medium.com/media/9a6dec2df708865c86466a4467ab203e/href</a></iframe><ul><li>The code below show the code in the fastapi container, the request will ask for <strong><em>company_id </em></strong>and <strong><em>department_id </em></strong>, it will return with the users belonging to this company and department.</li></ul><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/7243a6bd260966dea1c15020805d7228/href">https://medium.com/media/7243a6bd260966dea1c15020805d7228/href</a></iframe><h3><strong>6. </strong>Deployment plan</h3><ul><li>let’s start building the <strong>docker-compose.yaml</strong>: as you can see in the below code there are 3 containers, the <strong>ingestion </strong>container will wait until the <strong>db </strong>container is ready to receive requests</li></ul><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/aad0176e0c22ca32f0ce5a873149f71c/href">https://medium.com/media/aad0176e0c22ca32f0ce5a873149f71c/href</a></iframe><ul><li>let’s check the dockerfile for ingestion container, will be similar for almost all the containers, but it has an additional functionality, it will wait for PostgreSQL to be ready</li></ul><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/3400707e06f44a93fc27abcb425ba258/href">https://medium.com/media/3400707e06f44a93fc27abcb425ba258/href</a></iframe><h3><strong>7. Show results</strong></h3><ul><li>Install <a href="https://docs.docker.com/docker-for-windows/install/"><strong><em>Docker Desktop on Windows</em></strong></a>, it will install Docker Compose as well, Docker Compose will allow you to run multiple container applications.</li><li>Install <a href="https://www.stanleyulili.com/git/how-to-install-git-bash-on-windows/"><strong><em>git-bash for windows</em></strong></a>, once installed, open git bash and download this repository, this will download the <strong><em>docker-compose.yaml</em></strong> file, and other files needed.</li></ul><pre>ramse@DESKTOP-K6K6E5A MINGW64 /c<br>$ git clone <a href="https://github.com/Wittline/data-engineer-challenge.git">https://github.com/Wittline/data-engineer-challenge.git</a></pre><ul><li>Once all the files needed were downloaded from the repository, let’s run everything. We will use the git bash tool again, go to the folder <strong>data-engineer-challenge</strong> and we will run the Docker Compose command:</li></ul><pre>ramse@DESKTOP-K6K6E5A MINGW64 /c<br>$ cd data-engineer-challenge</pre><pre>ramse@DESKTOP-K6K6E5A MINGW64 /c/data-engineer-challenge<br>$ cd code</pre><pre>ramse@DESKTOP-K6K6E5A MINGW64 /c/data-engineer-challenge/code<br>$ cd apps</pre><pre>@DESKTOP-K6K6E5A MINGW64 /c/data-engineer-challenge/code/apps<br>$ docker-compose up</pre><p>After wait for a couple of minutes the final result of executing the above command should look like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ymnDkfOyYZZSWffExIqFhA.png" /><figcaption><strong><em>docker-compose execution</em></strong></figcaption></figure><ul><li>let’s check the API REST, go to your browser and search for: <a href="http://localhost:8080/docs#/default">http://localhost:8080/docs#/default</a></li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xYHx-3CTcXwmL0HytsatEQ.png" /></figure><ul><li>let’s try the request to the API REST</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qiPpxzPqx3LSiGztvGGsHg.png" /></figure><ul><li>let’s check the response from the API REST</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*FDRZ8FEO1p9CgdLcFBlMCg.png" /></figure><h3>Conclusions</h3><p>This code could work as a basic template for more complex technical challenges, and represents the basic structure of an organic data pipeline in a dockerized environment using only python in all containers.</p><blockquote>Check out my GitHub repository here: <a href="https://github.com/Wittline/data-engineer-challenge"><strong>data-engineer-challenge</strong></a><strong> </strong>for more information about the project.</blockquote><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6f1d59d0fd6a" width="1" height="1" alt=""><hr><p><a href="https://medium.com/geekculture/design-development-and-deployment-of-a-simple-data-pipeline-6f1d59d0fd6a">Design, Development and Deployment of a simple Data Pipeline</a> was originally published in <a href="https://medium.com/geekculture">Geek Culture</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to Build a Lossless Data Compression and Data Decompression Pipeline]]></title>
            <link>https://python.plainenglish.io/lossless-data-compression-and-data-decompression-pipeline-2f5391a6b7e7?source=rss-5a425beaf13------2</link>
            <guid isPermaLink="false">https://medium.com/p/2f5391a6b7e7</guid>
            <category><![CDATA[data-compression]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[big-data]]></category>
            <dc:creator><![CDATA[Ramses Alexander Coraspe Valdez]]></dc:creator>
            <pubDate>Wed, 20 Apr 2022 23:03:19 GMT</pubDate>
            <atom:updated>2022-04-25T05:50:25.471Z</atom:updated>
            <cc:license>http://creativecommons.org/licenses/by/4.0/</cc:license>
            <content:encoded><![CDATA[<h4>A parallel implementation of the bzip2 high-quality data compressor tool in Python.</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8ivAe25nHjfUVZZviWph0g.png" /><figcaption>A Parallel Implementation of the bzip2</figcaption></figure><p>The image above shows the architecture of a parallel implementation of the bzip2 data compressor with python, this data compression pipeline is using algorithms like <a href="https://en.wikipedia.org/wiki/Burrows%E2%80%93Wheeler_transform"><strong>Burrows-Wheeler transform</strong> (<strong>BWT</strong>)</a> and <a href="https://en.wikipedia.org/wiki/Move-to-front_transform"><strong>Move to front</strong> (<strong>MTF</strong>)</a> to improve the <a href="https://en.wikipedia.org/wiki/Huffman_coding"><strong>Huffman compression</strong></a>. But for now, this tool only will be focused on compressing <em>.csv</em> files and could be modified to process other files in tabular format.</p><p>Check the repo here:</p><p><a href="https://github.com/Wittline/wbz">GitHub - Wittline/wbz: A parallel implementation of the bzip2 data compressor in python, this data compression pipeline is using algorithms like Burrows-Wheeler transform (BWT) and Move to front (MTF) to improve the Huffman compression. For now, this tool only will be focused on compressing .csv files, and other files on tabular format.</a></p><h3>The need for compression</h3><p>When we talk about computation time we are also talking about money, data compression represents the most appropriate economic way to shorten the gap between content creators and content consumers, <strong><em>compressed files are obviously smaller and it is necessary to less money and time to transfer them and cost less money to store them, content creators pay less money to distribute their content, and content consumers pay less money to consume content.</em></strong> On the other hand, companies in all sectors need to find new ways to control the rapidly growing volume of their heterogeneous data generated every day, data compression and data decompression tools are related as one of the most viable solutions to these problems. In fact, <strong>data compression and decompression techniques are the DNA of many famous distributed systems</strong> and part of their success is due to the proper use of these techniques.</p><h3>Contextual Data Transforms</h3><p>Modern data compression tools and techniques are not based only on the use of a compression algorithm, in fact, the use of this is part of the final stage of a whole data compression pipeline, but before reaching the last one, there is a stage called contextual transformations that which is responsible for rearranging the sequence of symbols in the dataset, so, they will be more sensitive to statistical compression methods such as Huffman, in other words, they are artificial generators of redundancy. Two of the main algorithms that will be explained in this article are the BWT and the MTF.</p><p>It should be noted that the Huffman algorithm is widely used in many well-known data compression tools, the image below shows the data compression pipelines of some of them, clearly showing the phases of the contextual data transformation used in each tool.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8yxZopvs95ThgmE7FRXcVw.png" /><figcaption>Contextual Data Transformation for each data compression tool</figcaption></figure><p>For example, in bzip2, blocks can be decompressed independently, or they can be decompressed in parallel, which makes it a good tool to use for big data solutions with distributed computing frameworks like Apache Hadoop, Apache Spark, Apache Hive, Apache Kafka, Apache Flink, Apache beam, Apache Airflow. There is a considerable amount of studies in this regard, where the studies are based on improving the communication of these codecs with the main components of Hadoop. Internally these codecs use the contextual transformations mentioned in this article. The image below shows the codecs commonly used by Hadoop components, as mentioned before, two of them use Huffman in their data compression pipeline.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*uqZLasTwS16ecCHiUkal9A.png" /><figcaption>Hadoop codecs</figcaption></figure><h3>Explaining Lossless Data Compression pipeline</h3><p>Once the file has been read and split into chunks, each chunk and prior to processing with MTF must be processed by the BWT algorithm first. The goal of the BWT processing is to create more clusters of symbols, and group equal symbols. The chunk resulting from the BWT processing will be processed by the MTF algorithm. The MTF will take the cluster of symbols and will reduce the size of the alphabet of the whole chunk. All chunks will be gathered, this result is a sequence of symbols with lower entropy and will be easier for the Huffman compression to get an acceptable compression rate.</p><p>In the code, in the class <strong>Huffman, </strong>you can see one method called <strong><em>__encode_hufftable</em></strong>, which is responsible for coding the Huffman table in an optimal way along with the compressed sequence of symbols.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*FXZNeFvIaSHYfzpjtw5xTw.png" /><figcaption>Data Compression pipeline</figcaption></figure><h3>Explaining Lossless Data Decompression Pipeline</h3><p>In the code, in the class <strong>Huffman </strong>you can see one method called <strong><em>__decode_hufftable</em></strong>, it will help to rebuild the Huffman table using the initial sequence of symbols appended to the header of the compressed sequence.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qlFhSR2nw__2W2p_3oACKA.png" /><figcaption>Compressed file structure</figcaption></figure><p>Once the sequence was decoded using the Huffman table, it will be split into chunks, each chunk will be processed by the Inverse MTF and then by the Inverse BWT algorithm, once the process finishes all the chunks will be gathered and joined to create the original file.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*dz7aj2fBpAzOc6bVA-uEZg.png" /><figcaption>Data Decompression Pipeline</figcaption></figure><h3><strong>How to use the tool</strong></h3><p>The tool is called <a href="https://github.com/Wittline/wbz"><strong>WBZ</strong></a>, the first version only will be focused on compressing .csv files and I will be adding more features coming soon, the parameters are described as follow:</p><pre>python wbz.py <strong>-a</strong> encode <strong>-f</strong> &#39;C:\Users\...\data.csv&#39; <strong>-cs</strong> 20000 <strong>-ch</strong> &#39;;&#39;</pre><pre>python wbz.py <strong>-a</strong> decode <strong>-f</strong> &#39;C:\Users\...\data.wbz&#39; <strong>-cs</strong> 20000 <strong>-ch</strong> &#39;;&#39;</pre><ul><li><strong><em>-a</em></strong> is <em>action</em>, there are two actions: <strong>encode </strong>and <strong>decode</strong></li><li><strong><em>-f</em></strong> is <em>file path, </em>if your action is to <strong><em>encode </em></strong>make sure that the file path chosen is a .csv file, if your action is to <strong><em>decode </em></strong>make sure that your chosen file path is a file with extension .wbz</li><li><strong><em>-cs</em></strong> is <em>chunk size, the algorithm </em><a href="https://en.wikipedia.org/wiki/Burrows%E2%80%93Wheeler_transform"><strong><em>Burrows-Wheeler transform</em></strong><em> (</em><strong><em>BWT</em></strong><em>)</em></a><em> works with chunks size in bytes, with this parameter you would specify the number of bytes to be processed by each CPU.</em></li><li><strong><em>-ch </em></strong>is a <em>special character, each chunk encoded by the algorithm </em><a href="https://en.wikipedia.org/wiki/Burrows%E2%80%93Wheeler_transform"><strong><em>Burrows-Wheeler transform</em></strong><em> (</em><strong><em>BWT</em></strong><em>)</em></a><em> will contain a special character inside it, it will help to identify an index for decoding purposes, </em>The possible column separator characters in your .csv file could work as a special character, it is recommended to use a separator that is not used by your columns and that does not appear in the content of the columns, this feature will be removed in the next versions of this tool.</li></ul><p><em>The same </em><strong><em>chunk size</em></strong><em> and </em><strong><em>special character</em></strong><em> used to encode the file must be used to decode the file. The idea of keeping them as parameters is to be able to get a better trade-off of the speed in the encoding and decoding process and a better compression rate.</em></p><h3><strong>Performance</strong></h3><p>The tests were done with three .csv files of different sizes and varying the chunk size:</p><ul><li><strong><em>data_1000000</em></strong><em>: One million records (61 MB)</em></li><li><strong><em>data_500000</em></strong><em>: Half a million records (31 MB)</em></li><li><strong><em>data_250000</em></strong><em>: A quarter of a million records (16 MB)</em></li></ul><p>There is an improvement in the rate compression for larger chunk sizes.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ZsyjuUEk9EXqK7BGYqPEVQ.png" /><figcaption>the behavior of the rate compression varying the chunk size</figcaption></figure><p>The compression times increase with a logarithmic behavior when the size of the chunk is increased as well.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*HjdKYlzPK7ID9cnb3X7c8Q.png" /><figcaption>compression times</figcaption></figure><p>Regardless of the size of the file, the decompression times have a constant behavior and tend to be reduced when the size of the chunk increases as well.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9fgl9hDjTKOJEAq-EYP1Kg.png" /></figure><p><strong>Future versions of this tool will focus on:</strong></p><ul><li>Improve the compression times of Huffman and BWT encoding times.</li><li>Improve the encode of the Huffman table</li><li>Compression based on columns</li><li>Compress and decompress specific columns on the .csv file</li><li>Generate compressed chunks automatically for large files</li><li>Distributed compression and decompression</li></ul><p>Help me to improve this tool, check the repo here:</p><p><a href="https://github.com/Wittline/wbz">GitHub - Wittline/wbz: A parallel implementation of the bzip2 data compressor in python, this data compression pipeline is using algorithms like Burrows-Wheeler transform (BWT) and Move to front (MTF) to improve the Huffman compression. For now, this tool only will be focused on compressing .csv files, and other files on tabular format.</a></p><p>That’s it for now. Thank you for reading.</p><p><em>More content at </em><a href="https://plainenglish.io/"><strong><em>PlainEnglish.io</em></strong></a><em>. Sign up for our</em><strong><em> </em></strong><a href="http://newsletter.plainenglish.io/"><strong><em>free weekly newsletter</em></strong></a><em>. Follow us on </em><a href="https://twitter.com/inPlainEngHQ"><strong><em>Twitter</em></strong></a><em> and</em><strong><em> </em></strong><a href="https://www.linkedin.com/company/inplainenglish/"><strong><em>LinkedIn</em></strong></a><em>. Join our </em><a href="https://discord.gg/GtDtUAvyhW"><strong><em>community Discord</em></strong></a><em>.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=2f5391a6b7e7" width="1" height="1" alt=""><hr><p><a href="https://python.plainenglish.io/lossless-data-compression-and-data-decompression-pipeline-2f5391a6b7e7">How to Build a Lossless Data Compression and Data Decompression Pipeline</a> was originally published in <a href="https://python.plainenglish.io/">Python in Plain English</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Introduction to Apache Spark]]></title>
            <link>https://coraspe-ramses.medium.com/introducci%C3%B3n-a-apache-spark-50a106b2e01a?source=rss-5a425beaf13------2</link>
            <guid isPermaLink="false">https://medium.com/p/50a106b2e01a</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[distributed-systems]]></category>
            <category><![CDATA[big-data]]></category>
            <category><![CDATA[apache-spark]]></category>
            <dc:creator><![CDATA[Ramses Alexander Coraspe Valdez]]></dc:creator>
            <pubDate>Sun, 21 Nov 2021 16:29:56 GMT</pubDate>
            <atom:updated>2022-03-12T02:10:12.511Z</atom:updated>
            <content:encoded><![CDATA[<h4>Apache Spark</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/698/0*2WPceokO0a15hkob.png" /></figure><h3>APACHE SPARK</h3><p>Apache spark is a tool widely used by data engineers, data scientists, or machine learning engineers, it was designed and built in 2009 at UC Berkeley, it is the evolution of the old paradigm that used HADOOP with the Mapreduce algorithm, Apache spark it acts as a distributed system, spreading the workloads in the memory of different nodes of a cluster, it is simpler, it is 10 to 20 times faster than Mapreduce Hadoop, it is easier to use, it is modular, that is, it can be used for different workloads, and it focuses on speed and parallel computing instead of storage, and that’s the main difference from Apache Hadoop.</p><h3>MEMORY PROCESSING</h3><ul><li>The price of memory decreases each year, which doesn’t affect infrastructure expenses by putting more memory on each server in a cluster.</li><li>Many datasets fit into memory of a modern computers.</li><li>Memory is fast, using disk is not a good idea when you want to run small operations.</li><li>There is quantitative evidence about the speed of Apache Spark vs Hadoop Mapreduce</li></ul><h3>COMPONENTS</h3><p>The general overview of the architecture and its components is in the image below, all its components are divided into their APIs and Apache spark core</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*5TspU2DMSvM-Cdwb" /></figure><h3>DISTRIBUTED EXECUTION</h3><p>Spark is a distributed processing engine, all its components work in collaboration in a server cluster, it is important to understand how these components work together, image below (left section) shows an apache spark cluster in an standalone mode, for each Apache application Spark runs a driver and several executors, Here we will describe every single component and the process.</p><ul><li><strong>Driver</strong>: It is responsible for initiate the SparkSession, create the communication with the cluster manager, requesting resources (CPU and Memory) from the cluster manager to the different executors, all spark operations are being transformed into DAG calculations, and distributed throughout all executors.</li><li><strong>SparkSession</strong>: it is the object that will allow you to consume and access to all kinds of operations and data in Spark, it will allow you access to all the functionality of Apache Spark.</li><li><strong>Executor</strong>: The executors communicate with the driver, and they are responsible for executing tasks inside each worker, executors reserve memory and CPU in workers, it is totally dedicated to an application and ends when the application completes, an Spark Application consists of many executors often working in parallel, a worker node has a finite number of executors hosted at a given time, in case an application requires more executors and they are not available then they are scheduled to run after other executors finish their tasks and release resources.</li><li><strong>Clúster manager</strong>: it is responsible for managing the cluster resources together with the master node, it also monitors the worker nodes, the cluster manager can be separated from the master node in cases when an architecture using Yarn or Mesos is chosen, the image below shows In a Standalone mode architecture, the master node and the cluster manager perform the same functionality. In this mode, the master acts as its own cluster manager.</li></ul><h3>DEPLOYMENT MODES</h3><p>The deployment modes in an Apache Spark cluster specify where exactly the <strong><em>driver </em></strong>will be executed, there are two types:</p><ol><li><strong>Client Mode</strong>: The driver runs on the user’s local machine and the executors on the cluster.</li><li><strong>Cluster Mode</strong>: The driver and executors run in the cluster.</li></ol><p>There are several types of cluster manager:</p><ol><li><strong>Standalone cluster</strong></li><li><strong>Yarn</strong></li><li><strong>Mesos</strong></li></ol><p><strong>Finally, the image below (right section) explains the following:</strong></p><p>The driver converts the spark application into one or more jobs, and each job is a DAG, and the execution plan of the entire app is based on this concept, each node into the DAG is exactly one or more stages, these stages are created depending on what type of operation needs to be executed, and can be executed in parallel, at the same time each stage is composed of different tasks, and that represents the minimum unit of execution, each task is being processed by a single processor or CPU, and receives a specific partition of the data, an executor with 4 cores can process 4 or more tasks in parallel.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*VN1bFsGpARemwQZY" /></figure><p>The basic flow of an application in Spark consists of reading heterogeneous data from an external source, execute necessary transformations or computation to change its form, aggregations and then storing the result in a new structure, those results are loaded into a destination data source. The process of computing and storing the result of the computation in a data structure depends on the use of <strong>3 basic structured APIs.</strong></p><h3>APIs</h3><ul><li><strong>RDD</strong>: all the APIs in Spache Spark are transformed to RDD, everything in SPARK is an RDD, an RDD is resilient(fault tolerant), it is partitioned(data chunks), it is distributed (chunks are distributed across all executors of the cluster), and it is immutable (you cannot modify the content of a current RDD). In the image below, using an RDD you can indicate how many partitions to use, in this case the example uses 4 partitions that are assigned to 4 executors, there are 2 executors per worker node, each chunk of the dataset is assigned to each executor, in a RDD you can execute two types of operations <strong>Transformations </strong>and <strong>Actions</strong>, the first 4 lines of the code are transformations. The first MAP splits the dataset and assigns each chunk to each executor, the second MAP generates a KEY VALUE, a 1 is assigned for each word, the last line generates a <strong>Shuffle</strong>.</li><li><strong>DATAFRAME</strong>: It’s like a database table, where there is a schema defined for the table, each column has an assigned or inferred data type, so you can create an order at each step of the transformation process using aggregations and filters and that’s it, you will know what kind of data type is in each phase of the computation, it is easier for Spark to know what to do and how to do it, therefore there is a better translation from Dataframe to RDD which leads to a better execution plan.</li><li><strong>DATASET</strong>: is a collection of strongly typed <strong>JVM </strong>objects, and can only be used with Java or Scala, they are compile-time type-safe, in this case unlike the dataframe the types are not dynamically inferred or assigned at runtime as in Python or R, only at compile time.</li></ul><h3>RDD (DISADVANTAGES)</h3><p>You are not indicating to the process what to do and how to do it, using an RDD, Spark will not know if you are executing a join, filter, select or a specific aggregation, Spark only has access to a lambda expression using generic objects, it is impossible to know what data type is being operated, so it is more difficult for Spark to optimize the execution plan, Spark doesn’t know if there is another efficient way to express the computation using another order in the expression. This is only achieved with other types of APIs such as <strong>dataframes </strong>and <strong>datasets</strong>.</p><h3>WHEN TO USE AN RDD</h3><p>Although it is not recommended to use them, I would say that their use is appropriate if you need to tell Spark how exactly a query should be executed.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*QDSUvNW_HBhq1r37" /></figure><h3>SPARK SQL</h3><p>The process of building queries efficiently and generating code as compact as possible is the job of the Spark SQL engine. Spark SQL generates a compact and optimized final execution plan for the JVM, it also provides a bridge to and from external tools via JDBC and ODBC drivers. All Spark components are unified and a common abstraction is allowed for DataFrames and Datasets in Java/Scala, Python and R, this simplifies the fact of working with structured data. Spark Reads and writes structured data with a schema defined in a specific structured file format like: Json, csv, Avro, Paquet etc. and convert this data to temporary tables.</p><h3><strong>CATALYST OPTIMIZER</strong></h3><p>Spark SQL’s Catalyst Optimizer improves the performance of SQL queries. Catalyst automatically transforms relational queries to execute them more efficiently using techniques such as filtering and indexing, and ensuring that joins from data sources are performed in the most efficient order. Furthermore, its design allows the Spark community to implement and extend the optimizer with new functionality.</p><p>The Catalyst Optimizer takes a query and converts it to an execution plan, commonly the entire query goes through 4 transformation phases, check the image below:</p><ol><li><strong>Analysis</strong>: SparkSQL starts by generating an <strong>AST </strong>tree for the SQL query or the query that uses a dataframe, in this initial phase any column or table name is looked up in an internal catalog that contains all objects with column names, data types, functions and databases, after each of them are identified, it goes to the next phase.</li><li><strong>Logical Optimization</strong>: At this level the Catalyst optimizer will generate different plans and using the cost optimizer will assign a cost to each plan, these plans are presented as a trees with operators and are composed of: <strong>Expressions </strong>(Columns transformations, filters, conditions, joinings) and <strong>Relational operators</strong> (Filter, Join and DataFrame transformations).</li><li><strong>Physical Plan</strong>: In this phase Spark SQL generates one or more physical plans based on the optimized logical plan of the previous phase, it contains a more specific description of how things should happen using the operators already defined. A clear example would be: In the logical plan the Join operator is used and the physical plan decides which type of Join should be used, a <strong>SortMergeJoin </strong>or a <strong>BroadcastHashJoin</strong>.</li><li><strong>Code Generation</strong>: It is the final phase of optimization, up to this point efficient java bytecode has been generated to run on each machine, in this case Spark acts as a compiler. Code generation reduces the packing of primitive data types and, more importantly, avoids the costly use of polymorphic functions.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*AlflA2Q2Zt3l6Ohh" /></figure><h3><strong>TUNNING APACHE SPARK</strong></h3><p>Large workloads in Spark are called Batch jobs, commonly scheduled to run for a day, Apache Spark is able to control batches that can be measured in terabytes of data, and there is always a probability of failure in the execution of these jobs, Most of the time the success in executing these depends on the understanding you have about the main components of Spark: Driver, executor, and how the shuffles that occur in each executor are managed. Here are the most common techniques for optimizing workloads to reduce the probability of failure with Apache Spark:</p><h4>Dynamic resource allocation</h4><p>When you enable the “Dynamic resource allocation” option, the Driver requests more resources only when it is necessary, in this way the computing resources are better managed and more resources are only added in sudden peaks. There are scenarios where analytics are done on demand and a large number of SQL queries are expected to be received at specific times of the day, in these cases “<strong><em>Dynamic resource allocation</em></strong>” will take care of freezing them or add executors only when it is necessary.</p><ul><li><em>spark.dynamicAllocation.enabled</em></li><li><em>spark.dynamicAllocation.minExecutors</em></li><li><em>spark.dynamicAllocation.schedulerBacklogTimeout</em></li><li><em>spark.dynamicAllocation.maxExecutors</em></li><li><em>spark.dynamicAllocation.executorIdleTimeout</em></li></ul><h4>Scaling Spark Executor</h4><p>Enabling the previous option is not enough to guarantee the absence of fault, it is necessary to ensure that each new executor that is started has sufficient memory capacity. The amount of memory available for each executor is controlled by: <strong>spark.executor.memory</strong>, The image below shows how memory is distributed in an executor (Executor Memory Layout). Different types of queries require a different amount of memory.</p><ul><li><strong>Shuffle Memory</strong>: it is used for shuffles, sorts, joins and any kind of aggregations.</li><li><strong>User Memory</strong>: it is for storing user-generated caches, data structures, and partitions derived from dataframes.</li><li><strong>Reserved Memory</strong>: It would store internal Spark objects, it is reserved by the system.</li><li><strong>Memory Buffer</strong>: VM overheads.</li></ul><h4>Scaling External Shuffle Service</h4><p>During <strong>Map </strong>and <strong>Shuffle </strong>operations, spark writes and reads “<strong>shuffle files</strong>” from the hard drive, so there is quite a lot of IO, which consequently ends up in a bottleneck, for each seek of a shuffle, we return to open the same index file and read it. It is more efficient to avoid opening the same file several times and storing the data in a cache, by storing the index of the file with its information, this way we can also limit the number of reads on the cache.</p><h4><strong>Maximizing Spark Parallelism</strong></h4><p>Partitions are atomic units of parallelism, a simple task represented by a single thread must run on a CPU core and it processes a single partition, therefore, the partition level must be chosen based on the number of cores that an executor has, when more partitions are created than the existing cores in the executor then they will be waiting. Apache Spark reads data from disk in blocks of defined size, by default they are between 64 and 128 MB, a partition is represented as a collection of these blocks. By default the size of a partition can be set using: <strong>spark.sql.files.maxPartitionBytes</strong> or using the <strong>.repartition(numbers of partition)</strong> method to indicate the number of shuffle partitions, configure: <strong>spark.sql.shuffle.partitions</strong>, in this way it will indicate how many partitions are sent through the network to the executors.</p><h4>Cache and Persist:</h4><p>Dataframes are commonly used or read, when they are involved in transformations that are frequent in your data pipelines or ETLs can be cached or persisted to avoid creating them again.</p><ul><li><strong>Cache</strong>: stores partitions across all executors, as long as memory allows to store, a dataframe can be cached in fractions, but partitions cannot, if you have 4 partitions and only 2.5 of them fit in memory, only 2 will be cached.</li><li><strong>Persist </strong>unlike “cache” persist allows you to use one storage tier, there are 6 storage tiers.</li></ul><p>Before using any of these two techniques, make sure that the dataframes used in the ETLs are not too large, and regardless of their size, avoid caching or persisting dataframes that do not need frequent and expensive transformations.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*SththjKifSwE2062" /></figure><h4>JOINS</h4><p>One of the most common operations in advanced analytics are the JOIN operations (inner join, right join, left join, outer join), two dataframes in spark can be joined using a common column in both, in the same way as it happens in a relational database, all these operations generate a large amount of movement through the executors in Apache Spark, or in other words: Shuffle, in order to reduce this amount of data movement in an Apache Spark cluster there are 5 JOIN strategies:</p><ol><li><strong>Sort Merge Join</strong></li><li><strong>Broadcast Hash Join</strong></li><li><strong>Shuffle Hash Join</strong></li><li><strong>Broadcast Nedted Loop Join</strong></li><li><strong>Cartesian Product Join</strong></li></ol><p><strong>Sort Merge Join (SMJ)</strong> and <strong>Broadcast Hash Join (BHJ)</strong> are the most common to use:</p><p><strong>SORT MERGE JOIN (SMJ)</strong></p><p>All rows of a dataframe with the same KEY are placed in the same partition and in the same executor (Hash key partitioned), which causes an initial exchange between executors.</p><p>The phases are as follows:</p><ul><li>Both tables are partitioned using a JOIN KEY across all partitions in the cluster.</li><li>The data rows in each partition are sorted in parallel.</li><li>The union of the already partitioned and ordered data is done by iterating over each row and joining the common rows based on the KEY.</li></ul><p><strong>When to use the SMJ?</strong></p><ul><li>When both tables are considerably large.</li><li>If the data in the columns involved in the join has a nearly uniform distribution.</li></ul><p><strong>What if the data in the column is skewed?</strong></p><ul><li>Add more resources to the cluster.</li><li>Use the .repartition</li><li>Consider using an iterative Broadcast JOIN strategy.</li></ul><p><strong>BROADCAST HASH JOIN (BHJ)</strong></p><p>When you intend to do a JOIN between two dataframes and one of them is a small dataframe or one with few records, you should broadcast the small dataframe to all the executors of the cluster.</p><p>The phases are as follows:</p><ul><li>The small dataframe is broadcast through all executors.</li><li>The partitions of the largest dataframe are distributed evenly across all executors.</li><li>The join is made between the partition in each executor and the small dataframe based on the KEY.</li></ul><p><strong>When to use BHJ?</strong></p><ul><li>If one of the dataframes involved in the UNION is small.</li><li>If you want to considerably reduce the shuffle in the cluster.</li><li>If the KEYs involved in the JOIN are not sorted.</li></ul><p><strong>What if the dataframe is not small?</strong></p><ul><li>The Iterative Broadcast Join strategy could be used.</li><li>Use the Sort merge Join strategy.</li><li>Configure by default what is the maximum size allowed for the smallest dataframe using: <strong>spark.sql.autoBroadcastJoinThreshold</strong>, by default it is 10mb.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*S-MSeLAvqZ-x0CTq" /></figure><h3>APACHE SPARK PROVIDERS</h3><p>Some of the cloud technologies that offer the Apache Spark platform as a service:</p><ol><li><strong>Databricks</strong>: It was founded by the creators of Apache Spark, this same technology being the technological base of its business model, the community version (<a href="https://community.cloud.databricks.com/login.html">Community Edition</a>) can help you get started with this technology totally free.</li><li><strong>Google Dataproc</strong>: you can create fully managed and secure apache spark clusters.</li><li><strong>AWS EMR</strong>: is the best option, because Amazon is the execution provider of the databricks clusters, Elastic map Reduce offers you to create Apache Spark clusters and at the same time offers you options in which you can reduce the prices of the computation to only adapt to the necessary peaks, using spot instances on demand, and control via IAC the steps associated with an app running in a Spark environment, in the example below I attached a basic project where you can create an EMR cluster from your computer in AWS and control the level of granularity of the code for the entire application.</li></ol><p>That article explains the arguments why I separated the application into steps:</p><p><a href="https://aws.plainenglish.io/building-a-big-data-pipeline-with-pyspark-and-aws-emr-42bc8db9a38f">Build a Big Data Pipeline with PySpark and AWS EMR on EC2</a></p><p>This other example can help you to have an Apache Spark cluster in standalone mode, using docker.</p><p><a href="https://medium.com/geekculture/dockerizing-an-apache-spark-standalone-cluster-eeb7d3f8efeb">Dockerizing an Apache Spark Standalone Cluster</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=50a106b2e01a" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[A Text Analysis of Andres Manuel Lopez Obrador’s Speeches]]></title>
            <link>https://medium.com/geekculture/an-text-analysis-of-amlo-speeches-864dbdb15e9a?source=rss-5a425beaf13------2</link>
            <guid isPermaLink="false">https://medium.com/p/864dbdb15e9a</guid>
            <category><![CDATA[python]]></category>
            <category><![CDATA[méxico]]></category>
            <category><![CDATA[nlp]]></category>
            <category><![CDATA[mexico]]></category>
            <category><![CDATA[text-analytics]]></category>
            <dc:creator><![CDATA[Ramses Alexander Coraspe Valdez]]></dc:creator>
            <pubDate>Sat, 18 Sep 2021 04:39:14 GMT</pubDate>
            <atom:updated>2022-05-17T02:17:45.818Z</atom:updated>
            <cc:license>http://creativecommons.org/licenses/by/4.0/</cc:license>
            <content:encoded><![CDATA[<h4>Text analytics with python</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/863/1*_0z0Ah1r8thwr37pmmVGeg.png" /><figcaption>Text Analysis of Andres Manuel Lopez Obrador’s Speeches</figcaption></figure><p>This article proposes to analyze the text of the speeches, conferences and interviews of the current president of Mexico, and has an educational aim, there are no purposes of political interest in this document, you are free to interpret the data in your own way. I personally think that formalize this type of practices helps us to follow up on the political promises of the presidents of Latin America and could help us to make decisions in advance for our countries, however, what I intend to do is to show you a basic flow of text analysis using Python, visualize aggregated data and get insight at every step. Enjoy it.</p><h3>Data source</h3><p>The official AMLO website has a stenographic version of each speech, It is the punctual and faithful transcription about what was expressed verbally, we will take this URL for the experiments.</p><p><a href="https://lopezobrador.org.mx/">AMLO</a></p><h3>Web scraping with Python</h3><p>Today AMLO’s site is grouping the speeches into 667sections, and this number grows weekly, each section has a group of speeches by date, my scrapping technique is targeting each element of a group, which represents the speech, check the image below:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*cOO48HLn0n3YkgJC4MhlGQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2LzCMQj3aushblDvsfrVCQ.png" /><figcaption>Code to scraping amlo’s site</figcaption></figure><h3>Let’s start looking for insight</h3><p>The <strong>amlo_analysis.ipynb</strong> notebook contains all the analysis code, here we will explain each step taken and interpretation of each visualization.</p><h4>Reading the .csv file generated in the previous step</h4><p>Here we can see the speeches described for each box of each url with its dates, and this is what I want.</p><pre>df = pd.read_csv(&#39;C:/Users/ramse/Downloads/amlo_speechs.csv&#39;)<br>columns = [&#39;id_speech&#39;, &#39;date&#39;,&#39;title&#39;,&#39;url&#39;,&#39;content&#39;]<br>df = df[columns]<br>df[&#39;content&#39;] = df[&#39;title&#39;] + &#39; &#39; +  df[&#39;content&#39;]<br>df = df[df[&#39;content&#39;].notna()]<br>df</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*YFwcnHd2xHxPWNcm2IEn7w.png" /></figure><h4>Text Preprocessing</h4><ul><li>Convert to lowercase</li><li>Remove extra spaces, carriage change and tabs</li><li>Remove punctuation marks and question marks</li><li>Remove stopwords</li><li>Apply Stemming, Lematización and NER</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*uuW7ET0tfmc9kK5LT2Rmuw.png" /></figure><h4>Let’s delve into the count of words used by AMLO before and during his presidential term</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xDaZJur2529XY8GJ4vSTfA.png" /></figure><blockquote><em>The code above is used to generate the below visualization, there are three colors separating the chart vertically, which are the three periods of AMLO, He began its campaign in 2011, lost against Peña Nieto in December 2012, and he remained active in the mandate of President Peña Nieto, in 2018 he won the Mexican presidential elections and his number of words used per speech increased, as well as the vocabulary used.</em></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ZFWXdZ4pnWbQkl2lIhZFCA.png" /></figure><h4>Let’s group by Year and Month and visualize the word clouds for each presidential term</h4><blockquote>This could help us understand what were his plans and priorities over each period before and while his presidential term</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*jbcovgAy7iVBzxsLtv9GXQ.png" /></figure><blockquote>Before Peña Nieto</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qf6iZxXoJ0VX47Ce39wfSg.png" /></figure><blockquote>Peña Nieto’s presidential term</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*PYONz-hqZ3ts88Ig0r-PHQ.png" /></figure><blockquote>AMLO’s presidential term until September 2021</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*LrTfrtLgqgkLxHrxuYi6CQ.png" /></figure><blockquote>Although there are NLP techniques to detect topics, here we will not use these practices because this study involves political terms that are very common in the political environment of each country, in this case I will choose the topics and terms related to the topics manually</blockquote><ul><li><strong>Economy:</strong> gasto, remesas, ahorro, economía, finanzas, inflación, salarios, inversión, impuesto, austeridad</li><li><strong>Political institutions:</strong> pan, prd, pri, morena, pt</li><li><strong>General institutions:</strong> onu, inm, ine, oms, imss, cfe, cndh, inegi, sep, conacyt, insabi, sat, profeco</li><li><strong>Education: </strong>maestros,maestras, estudiante, educación, escuela, universidad, beca, reformaeducativa, libros, escuela, primaria, profesores</li><li><strong>Energetic:</strong> energética, aceite, gas, pemex, petróleo, gasolina, combustible</li><li><strong>Migration: </strong>migración, extranjeros, frontera, centroamericanos, deportaciones, centroamérica</li><li><strong>Alimentation:</strong> hambre, alimentación, canastabásica, comida, alimentos, desnutrición, nutrición, comida, pobreza</li><li><strong>Southern federative entities:</strong> campeche, chiapas, quintanaroo, tabasco, veracruz, yucatán</li><li><strong>Central federative entities:</strong> cdmx, edomex, guerrero, hidalgo, oaxaca, puebla, querétaro, tlaxcala</li><li><strong>North federal entities: </strong>aguascalientes, bajacalifornia, bajacaliforniasur, chihuahua, coahuila, colima, durango, guanajuato, guerrero, jalisco, michoacán, morelos, nayarit, nuevoleón, sanluispotosí, sinaloa, sonora, tamaulipas, zacatecas</li><li><strong>Health: </strong>bienestar, salud, médico, camas, vacuna, enfermedad, contagio, infección, pandemia, epidemia, covid, hospital, dosis, doctores, enfermeras, paramédicos, enfermeros, hospitalización</li><li><strong>Corruption:</strong> ayotzinapa, justicia, corrupción, extorsión, soborno, desaparición, secuestro, narcotráfico, homicidio, anticorrupción</li><li><strong>Political rivals:</strong> zedillo, salinas, peñanieto, anaya, meade, fox, calderon, margarita, videgaray, lozoya, chong, yunes</li><li><strong>Political allies:</strong> gordillo, buylla, tclouthier, mclouthier, clouthier, aliados, aliado, ebrard, sheinbaum, monreal, gatell, alcocer, mueller</li><li><strong>International people:</strong> trump, obama, biden</li><li><strong>Other problems:</strong> 4t, indígenas, avión, tren, huachicol, méxico, pueblo</li><li><strong>Countries:</strong> venezuela, chile, colombia, bolivia, argentina, cuba, honduras, nicaragua, canada, panama, españa, francia, alemania, china, rusia</li></ul><blockquote>Now we will see the frequency of each word by year into each Topic</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*faJSm1B6m6SwUWB5lu60lw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*S05cL_DsUeIybtkecp98nw.png" /></figure><blockquote>Look for and observe the following words: <strong>economía, pri, cndh, imss, escuela</strong> What are your thoughts?</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9lvJoUZWX_snAB8vvy5RkQ.png" /></figure><blockquote>Look for and observe the following words: <strong>migración, energética, pobreza, veracruz.</strong> What are your thoughts?</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xO2B21hFxZcTUf-fR66Ukw.png" /></figure><blockquote>Look for and observe the following words: <strong>cdmx, sonora, corrupción, vacuna. </strong>What are your thoughts?</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8yAa0TXooNhVJSdWsQ0-5A.png" /></figure><blockquote>Look for and observe the following words: <strong>ebrard, peñanieto, trump. </strong>What are your thoughts?</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/654/1*XphHPMuJ6GRgL4gRzb4WtA.png" /></figure><blockquote>Look for and observe the following words: <strong>china, españa, argentina. </strong>What are your thoughts? why?</blockquote><h4>Funny right? let’s dig into other types of insights, observe the evolution of the frequency over time is very important as well</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*IB6z3rQdxCNjDU07u5pHbQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Lr6r1TLcoIzkVaf6Mv5CLg.png" /></figure><blockquote>Apparently the use of the word “<strong>economía</strong>” in his speeches before his presidential term had relevance, but the use of this word is stronger in his current presidential term, also take a look at the word “<strong>impuesto</strong>”</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*QlKLb0EJNX0vbSRVJiFS2A.png" /></figure><blockquote>The most mentioned political party before his presidential term was “<strong>Morena</strong>”, at the same time he mentioned political parties rivals like “<strong>PRD</strong>” and “<strong>PRI</strong>”</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*UzAQimPmBOGH9WB1JKhRug.png" /></figure><blockquote><strong>CFE </strong>, <strong>IMSS </strong>and <strong>CNDH </strong>the most mentioned words related to general institutions</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*znOulXTFaDZo95vri-DFxw.png" /></figure><blockquote>“e<strong>ducación</strong>” has always been a word used since the beginning of his campaign, and now in his presidential term “e<strong>scuela</strong>” is the most relevant word related to education.</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*B-7yhICOoR15WzrvK8fTIQ.png" /></figure><blockquote>During the first years of Peña Nieto’s mandate, AMLO used the words “<strong>petróleo</strong>” and “<strong>energética</strong>” for a large period of time.</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*oA7beB-EuBjaYdBBLwWmGQ.png" /></figure><blockquote>The word “<strong>extranjeros</strong>” was common from the beginning of his campaign until today, however, lately “<strong>migración</strong>” and “<strong>frontera</strong>” appears stronger</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*EUiWIeLLmrRbBAk62vmwcg.png" /></figure><blockquote><strong>Veracruz </strong>and <strong>Tabasco</strong></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8gJZG5DmNTXRStJ1E08bJw.png" /></figure><blockquote><strong>“CDMX” </strong>is the most mentioned area in the center of the country, but there is a spiky rebound in the word “<strong>oaxaca” </strong>the last months of 2019</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*cd4LzVAp1pS3UQOwdebWLA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*LHFXHTQK-sFEmszH8TfaXg.png" /></figure><blockquote>The words “<strong>pobreza</strong>” and “<strong>alimentación</strong>” were very common since the beginning of his presidential campaign, and now both stronger</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1V5V6N4WdgC4DhpQj0MfJA.png" /></figure><blockquote>From the last months of 2020 until today, He’s been using the word “<strong>vacuna</strong>”, It was his priority.</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-mPOD2KGTOeJo37DT8F1Cg.png" /></figure><blockquote>“<strong>corrupción</strong>” is one of his favorite words</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*or2m4rR98G0YAC_fBjnLtw.png" /></figure><blockquote>From the beginning of his presidential campaign, “<strong>Peña nieto”</strong> was the target.</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XAna3z-VEnQXrlJfrUP_7A.png" /></figure><blockquote>Since the beginning of his presidential term “<strong>ebrard</strong>” and “<strong>sheinbaum</strong>” have been his political allies that he has mentioned the most.</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hhb6QaJq5BBwEaMHwUukHw.png" /></figure><blockquote><strong>“trump” </strong>very relevant and now “<strong>biden”, </strong>the dates make sense.</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*31vF-0FEtlru9z_XdbQg4Q.png" /></figure><blockquote>His other favorite word is “<strong>pueblo</strong>” but it is also worth mentioning that he has been using the words “<strong>avión </strong>“and “<strong>tren</strong>” a lot. why? :)</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*SO0XqsUWXi5um4-f55hC6w.png" /></figure><blockquote>Although <strong>China </strong>is the most mentioned country, it has an almost uniform distribution in the last two years, but lately other countries have come to light</blockquote><h3>Detecting nearest words to specific topics using Word Embeddings</h3><p>FastText is an improved version of the Word2Vec model, It is working with n-grams, thus, it tries to predict the source context words given a target word or center words. It generates better word embeddings for out of vocabulary words because even if the words are very rares their n-grams are still shared with other surrounding words. <strong>FasText is a good option for create Words Embeddings no dependent of the context</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*BXXE8AyaisrHQN4K3qDABg.png" /><figcaption>Word Embeddings</figcaption></figure><p>The code above generated the result below:</p><ul><li><strong>economía</strong>:[‘crecimiento’, ‘empleos’, ‘contrario’, ‘productivas’, ‘bajos’, ‘estabilidad’, ‘bienestar’, ‘crear’, ‘ingresos’, ‘peso’]</li><li><strong>educación</strong>:[‘reeducación’, ‘calidad’, ‘maestros’, ‘escolares’, ‘docentes’, ‘niveles’, ‘maestras’, ‘conocimientos’, ‘padres’, ‘deserción’]</li><li><strong>energética</strong>:[‘energético’, ‘industria’, ‘extranjeras’, ‘particulares’, ‘energéticas’, ‘pretendido’, ‘petróleo’, ‘extranjeros’, ‘energéticos’, ‘privatización’]</li><li><strong>migración</strong>:[‘unidos’, ‘cooperación’, ‘deportación’, ‘relación’, ‘centroamericanos’, ‘situación’, ‘relaciones’, ‘mexicanos’, ‘nacionalidades’, ‘centroamericana’]</li><li><strong>alimentación</strong>:[‘productos’, ‘precios’, ‘productores’, ‘acciones’, ‘amortización’, ‘consumos’, ‘fomento’, ‘importante’, ‘especial’, ‘fomentaremos’]</li><li><strong>salud</strong>:[‘médico’, ‘enfermedad’, ‘covid’, ‘hospital’, ‘especialización’, ‘médicas’, ‘médica’, ‘tratamientos’, ‘doctor’, ‘medicina’]</li><li><strong>corrupción</strong>:[‘políticos’, ‘acabar’, ‘impunidad’, ‘cortar’, ‘presupuesto’, ‘principal’, ‘escaleras’, ‘nación’, ‘honesto’, ‘funcionarios’]</li></ul><p>Nearest words seems to be quite well, but we will visualize to better understand what we are talking about</p><h4><strong>Visualizing Word </strong>Embeddings <strong>using t-SNE and PCA</strong></h4><p>The code below is used for further visualizations, It will help us visualize .gif animations of T-SNE with two components and different perplexities and PCA with three components. it will be easier to visually check the similarity between words.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*EQGiN5oEvqKZae-jcfe60Q.png" /></figure><blockquote>The below image shows 10 nearest words to each topic and perplexity 12</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*IB2X3i_O0dq5nhDhHDH-8Q.png" /></figure><blockquote>The below image shows 50 nearest words to each topic with multiple perplexities</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hDmeTQPOkoZEvlE6Tn9j7w.gif" /></figure><blockquote>The below image shows 10 nearest words to each topic with PCA (Three components)</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/856/1*050MKoRn46vYc4M-iqzICg.gif" /></figure><blockquote>The below image shows 200 nearest words to each topic with PCA (Three components)</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/856/1*jc7JYhIxJvIOclnK8Ptgcg.gif" /></figure><h3>Sentiment Analysis</h3><p>The code below will work for calculate sentiments scores for each speech and visualize them by presidential term, values close to 0 are negative and values close to 1 are positive, there is a threshold that I choosed for neutral speeches, for values greater than 0.4 and less than 0.6</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OmKcI2z_UGgNagxIakrHsg.png" /></figure><blockquote>The image below shows that during the presidential term of Peña nieto there was more density of amlo’s speeches with negative sentiments, however, since the beginning of his presidential term there is almost an uniformity between both sentiments.</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ca71PTotn5vTQ681BfcH8g.png" /></figure><blockquote><a href="https://github.com/Wittline/text-analysis-speeches-amlo">Check out my GitHub repository for more information about the project.</a></blockquote><h3>Summary</h3><ul><li>There is much more to add to this article, due to the lack of options for handling texts in spanish.</li><li>It is necessary to improve the lemmatization, stemming and Entity Recognition processes to be able to observe better patterns and more relevants nearby words in word embedings.</li><li>This study helps us to be able to follow up on the presidential campaign promises before and during of any presidential term, understand their vocabulary, segment topics and detect sentiments at time.</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=864dbdb15e9a" width="1" height="1" alt=""><hr><p><a href="https://medium.com/geekculture/an-text-analysis-of-amlo-speeches-864dbdb15e9a">A Text Analysis of Andres Manuel Lopez Obrador’s Speeches</a> was originally published in <a href="https://medium.com/geekculture">Geek Culture</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>