CHAPTER 2
ETL with Python
Every data science professional has to extract, transform, and load (ETL)
data from different data sources. In this chapter, we will discuss how
to perform ETL with Python for a selection of popular databases. For a
relational database, we’ll cover MySQL. As an example of a document
database, we will cover Elasticsearch. For a graph database, we’ll cover
Neo4j, and for NoSQL, we’ll cover MongoDB. We will also discuss the
Pandas framework, which was inspired by R’s data frame concept.
ETL is based on a process in which data is extracted from multiple
sources, transformed into specific formats that involve cleaning
enrichment, and finally loaded into its target destination. The following are
the details of each process:
1. Extract: During data extraction, source data is pulled
from a variety of sources and moved to a staging
area, making the data available to subsequent stages
in the ETL process. After that, the data undergoes
the cleaning and enrichment stage, also known as
data cleansing.
2. Transform: In this stage, the source data is matched
to the format of the target system. This includes
steps such as changing data types, combining fields,
splitting fields, etc.
© Sayan Mukhopadhyay, Pratip Samanta 2023 23
S. Mukhopadhyay and P. Samanta, Advanced Data Analytics Using Python,
[Link]
Chapter 2 ETL with Python
3. Load: This stage is the final ETL stage. Here, data
is loaded into the data warehouse in an automated
manner and can be periodically updated. Once
completed, the data is ready for data analysis.
The previous processes are important in any data analytics work. Once
the data goes through the ETL processes, then it becomes possible to
analysis the data, find insights, and so on.
We will discuss various types of ETL throughout this chapter. We
discussed in Chapter 1 that data is not an isolated thing. We need to load
data from somewhere, which is a database. We need to fetch the data from
some application, which is extraction. In this chapter and the next, we will
discuss various feature engineering that transforms the data from one form
to another.
MySQL
MySQLdb is an API in Python developed to work on top of the MySQL C
interface.
How to Install MySQLdb?
First you need to install the Python MySQLdb module on your machine.
Then run the following script:
#!/usr/bin/python
import MySQLdb
If you get an import error exception, that means the module was not
installed properly.
The following are the instructions to install the MySQL Python module:
$ gunzip [Link]
24
Chapter 2 ETL with Python
$ tar -xvf [Link]
$ cd MySQL-python-1.2.2
$ python [Link] build
$ python [Link] install
You can download the [Link] file from [Link]
downloads/connector/python/. You need to download it to your
working folder.
For Windows, please select the MySQL installer file from https://
[Link]/downloads/installer/. Once it’s downloaded, double-
click the file to install it and select MySQL Connector/Python as one of the
products to install. For details, you can visit [Link]
connector-python/en/connector-python-installation-[Link].
Database Connection
Before connecting to a MySQL database, make sure you do the following:
1. You need to access a database called TEST with the
sql "use test" command.
2. In TEST you need a table named STUDENT; use the
command sql "create table student(name
varchar(20), sur_name varchar(20),roll_
no int");.
3. STUDENT needs three fields: NAME, SUR_NAME, and
ROLL_NO.
4. There needs to be a user in TEST that has complete
access to the database.
If you do not do these steps properly, you will get an exception in the
next Python code.
25
Chapter 2 ETL with Python
INSERT Operation
The following code carries out the SQL INSERT statement for the purpose
of creating a record in the STUDENT table:
#!/usr/bin/python
import MySQLdb
# Open database connection
db = [Link]("localhost","user","passwd","TEST" )
# prepare a cursor object using cursor() method
cursor = [Link]()
# Prepare SQL query to INSERT a record into the database.
sql = """INSERT INTO STUDENT(NAME,
SUR_NAME, ROLL_NO)
VALUES ('Sayan', 'Mukhopadhyay', 1)"""
try:
# Execute the SQL command
[Link](sql)
# Commit your changes in the database
[Link]()
except:
# Rollback in case there is any error
[Link]()
# disconnect from server
[Link]()
READ Operation
The following code fetches data from the STUDENT table and prints it:
#!/usr/bin/python
import MySQLdb
# Prepare SQL query to INSERT a record into the database.
26
Chapter 2 ETL with Python
sql = "SELECT * FROM STUDENT "
try:
# Execute the SQL command
[Link](sql)
# Fetch all the rows in a list of lists.
results = [Link]()
for row in results:
fname = row[0]
lname = row[1]
id = row[2]
# Now print fetched result
Print( "name=%s,surname=%s,id=%d" % \
(fname, lname, id ))
except:
print "Error: unable to fecth data"
DELETE Operation
The following code deletes a row from TEST with id=1:
#!/usr/bin/python
import MySQLdb
# Prepare SQL query to DELETE required records
sql = "DELETE FROM STUDENT WHERE ROLL_NO =1"
try:
# Execute the SQL command
[Link](sql)
# Commit your changes in the database
[Link]()
except:
# Rollback in case there is any error
[Link]()
27
Chapter 2 ETL with Python
UPDATE Operation
The following code changes the lastname variable to Mukherjee, from
Mukhopadhyay:
#!/usr/bin/python
import MySQLdb
# Prepare SQL query to UPDATE required records
sql = "UPDATE STUDENT SET SUR_NAME="Mukherjee"
WHERE SUR_NAME="Mukhopadhyay"
try:
# Execute the SQL command
[Link](sql)
# Commit your changes in the database
[Link]()
except:
# Rollback in case there is any error
[Link]()
COMMIT Operation
The commit operation provides its assent to the database to finalize the
modifications, and after this operation, there is no way that this can be
reverted.
ROLL-BACK Operation
If you are not completely convinced about any of the modifications and
you want to reverse them, then you can apply the roll-back() method.
The following is a complete example of accessing MySQL data through
Python. It will give the complete description of the data stored in a CSV file
or MySQL database.
28
Chapter 2 ETL with Python
This code asks for the data source type, either MySQL or text. For
example, if MySQL asks for the IP address, credentials, and database name
and shows all tables in the database, it offers its fields once the table is
selected. Similarly, a text file asks for a path, and in the files it points to, all
the columns are shown to the user.
# importing files and reading config file
import MySQLdb
import sys
out = open('[Link]','w')
print ("Enter the Data Source Type:")
print( "1. MySql")
print ("2. Exit")
while(1):
data1 = [Link]().strip()
if(int(data1) == 1):
[Link]("source begin"+"\n"+"type=mysql\n")
# taking inputs from user
print ("Enter the ip:")
ip = [Link]().strip()
[Link]("host=" + ip + "\n")
print ("Enter the database name:")
db = [Link]().strip()
[Link]("database=" + db + "\n")
print ("Enter the user name:")
usr = [Link]().strip()
[Link]("user=" + usr + "\n")
print ("Enter the password:")
passwd = [Link]().strip()
[Link]("password=" + passwd + "\n")
29
Chapter 2 ETL with Python
# making connection to and executing query
connection = [Link](ip, usr, passwd, db)
cursor = [Link]()
query = ("show tables")
[Link](query)
data = [Link]()
tables = []
# appending data to the table
for row in data:
for field in row:
[Link]([Link]())
for i in range(len(tables)):
print( i, tables[i])
tb = tables[int([Link]().strip())]
[Link]("table=" + tb + "\n")
query = ("describe " + tb)
[Link](query)
data = [Link]()
columns = []
for row in data:
[Link](row[0].strip())
for i in range(len(columns)):
print( columns[i])
print "Not index choose the exact column names
seperated by coma"
cols = [Link]().strip()
[Link]("columns=" + cols + "\n")
[Link]()
[Link]()
[Link]("source end"+"\n")
30
Chapter 2 ETL with Python
print ("Enter the Data Source Type:")
print ("1. MySql") print ("2. Exit")
[Link]()
[Link]()
Before we go on to the topic of relational databases, let’s talk about
database normalization.
Normal Forms
Database normal forms are the principles to organize your data in an
optimum way.
Every table in a database can be in one of the normal forms that we’ll
go over next. For the primary key (PK) and foreign key (FK), you want to
have as little repetition as possible. The rest of the information should be
taken from other tables.
• First normal form (1NF)
• Second normal form (2NF)
• Third normal form (3NF)
First Normal Form
There are no repeating groups in the first normal form since only single
values are allowed at the intersection of each row and column.
To get to first normal form, remove the repetitive groups, and establish
two new relations to normalize a connection with a repeating group.
For unique identification, the new connection’s PK is a combination of
the old relation’s PK and a feature from the newly formed relation.
31
Chapter 2 ETL with Python
To demonstrate the procedure for 1NF, we’ll use the Student_Grade_
Report table, which comes from a School database.
Student_Grade_Report (StudentNo, StudentName, Major,
CourseNo, CourseName, InstructorNo, InstructorName,
InstructorLocation, Grade)
1. The recurring group in the Student Grade Report
table contains the course information. A student can
enroll in a variety of courses.
2. Get rid of the group that keeps repeating itself.
That’s each student’s course information in this
situation.
3. Determine your new table’s PK.
4. The attribute value must be identified uniquely by
the PK (StudentNo and CourseNo).
Student (StudentNo, StudentName, Major)
StudentCourse (StudentNo, CourseNo, CourseName, InstructorNo,
InstructorName, InstructorLocation, Grade)
Second Normal Form
The relation must first be in 1NF for the second normal form. If and only if
the PK contains a single feature, the relationship is automatically in 2NF.
If the connection contains a composite PK, then each nonkey property
must be completely reliant on the entire PK, not just a portion of it (i.e.,
there can’t be any partial augmentation or dependency).
A table must first be in 1NF before moving to 2NF.
1. As it has a single-column PK, the Student table is
already in 2NF.
32
Chapter 2 ETL with Python
2. When looking at the Student Course table, you can
observe that not all of the characteristics, especially
the course details, are completely dependent on the
PK. The grade is the sole attribute that is entirely
reliant on xxx.
3. Locate the new table containing the course details.
4. Determine the new table’s PK.
The three new tables are as follows:
Student (StudentNo, StudentName, Major)
CourseGrade (StudentNo, CourseNo, Grade)
CourseInstructor (CourseNo, CourseName, InstructorNo,
InstructorName, InstructorLocation)
Third Normal Form
The connection must be in second normal form to be in third normal form.
All transitive dependencies must be eliminated as well; a nonkey attribute
cannot be functionally reliant on another nonkey attribute.
This is the process for achieving 3NF:
1. From each table with a transitive relationship,
remove all dependent characteristics in a transitive
relationship.
2. Make a new table with the dependence eliminated.
3. Inspect new and updated tables to ensure that each
table has a determinant and that no tables have
improper dependencies.
33
Chapter 2 ETL with Python
Take a look at the four new tables:
Student (StudentNo, StudentName, Major)
CourseGrade (StudentNo, CourseNo, Grade)
Course (CourseNo, CourseName, InstructorNo)
Instructor (InstructorNo, InstructorName, InstructorLocation)
There should be no abnormalities in the third normal form at this
point. For this example, consider the dependency diagram in Figure 2-1.
As previously said, the first step is to eliminate repeated groupings.
Student (StudentNo, StudentName, Major)
StudentCourse (StudentNo, CourseNo, CourseName, InstructorNo,
InstructorName, InstructorLocation, Grade)
Figure 2-1. Dependency diagram
Review the dependencies in Figure 2-1, which summarizes the
normalization procedure for the School database.
The following are the abbreviations used in Figure 2-1:
• PD stands for partially dependent.
• TD stands for transitive dependence.
34
Chapter 2 ETL with Python
• FD stands for full dependency. (FD stands for
functional dependence in most cases. Figure 2-1 is the
only place where FD is used as an abbreviated form for
full dependence.)
A relational database is valuable when structured data and a strict
relationship between the fields are maintained. But what if you do not
have structured data in which a strict relationship between fields has been
maintained? That’s where Elasticsearch comes in.
Elasticsearch
You’ll find that data is often unstructured. Meaning, you may end up with
a mix of image data, sensor data, and other forms of data. To analyze this
data, we first need to store it. MySQL or SQL-based databases are not
good at storing unstructured data. So here we introduce a different kind of
storage, which is mainly used to handle unstructured textual data.
Elasticsearch is a Lucene-based database, which makes it is easy to
store and search text data. Its query interface is a REST API endpoint.
The Elasticsearch (ES) low-level client gives a direct mapping from
Python to ES REST endpoints. One of the big advantages of Elasticsearch
is that it provides a full-stack solution for data analysis in one place.
Elasticsearch is the database. It has a configurable front end called Kibana,
a data collection tool called Logstash, and an enterprise security feature
called Shield.
This example has features called cat, cluster, indices, ingest,
nodes, snapshot, and tasks that translate to instances of CatClient,
ClusterClient, IndicesClient, CatClient, ClusterClient,
IndicesClient, IngestClient, NodesClient, SnapshotClient,
NodesClient, SnapshotClient, and TasksClient, respectively. These
instances are the only supported way to get access to these classes and
their methods.
35
Chapter 2 ETL with Python
You can specify your own connection class, which can be used by
providing the connection_class parameter.
# create connection to local host using the ThriftConnection
Es1=Elasticsearch(connection_class=ThriftConnection)
Installation commands for Elastic Search are given here:
curl -fsSL [Link] |
sudo apt-key add -
echo "deb [Link] stable
main" | sudo tee -a /etc/apt/[Link].d/[Link]
sudo apt update
sudo apt install elasticsearch
You can start Elasticsearch in Ubuntu with these commands:
service elasticsearch start
service elasticsearch stop
You can check the status with these commands:
service elasticsearch status
# create connection that will automatically inspect the
cluster to get
# the list of active nodes. Start with nodes running on
'esnode1' and
# 'esnode2'
Es1=Elasticsearch(
['esnode1', 'esnode2'],
# sniff before doing anything
sniff_on_start=True,
# refresh nodes after a node fails to respond
sniff_on_connection_fail=True,
36
Chapter 2 ETL with Python
# and also every 30 seconds
sniffer_timeout=30
)
Different hosts can have different parameters (hostname, port number,
SSL option); you can use one dictionary per node to specify them.
# connect to localhost directly and
another node using SSL on port 443
# and an url_prefix. Note that ``port`` needs to be an int.
Es1=Elasticsearch([
{'host':'localhost'},
{'host':'othernode','port':443,'url_prefix':'es','use_
ssl':True},
])
SSL client authentication is also supported (see
Urllib3HttpConnection for a detailed description of the options); an
example is given here:
Es1=Elasticsearch(
['localhost:443','other_host:443'],
# turn on SSL
use_ssl=True,
# make sure we verify SSL certificates (off by default)
verify_certs=True,
# provide a path to CA certs on disk
ca_certs='path to CA_certs',
# PEM formatted SSL client certificate
client_cert='path to [Link]',
# PEM formatted SSL client key
client_key='path to [Link]'
)
37
Chapter 2 ETL with Python
Connection Layer API
Many classes are responsible for dealing with the Elasticsearch cluster.
Here, the default subclasses being utilized can be disregarded by handing
over parameters to the Elasticsearch class. Every argument belonging to
the client will be added onto Transport, ConnectionPool, and Connection.
As an example, if you want to use your own personal utilization of the
ConnectionSelector class, you just need to pass in the selector_class
parameter.
The entire API wraps the raw REST API with a high level of accuracy,
which includes the differentiation between the required and optional
arguments to the calls. This implies that the code makes a differentiation
between positional and keyword arguments; I advise you to use keyword
arguments for all calls to be consistent and safe. An API call becomes
successful (and will return a response) if Elasticsearch returns a 2XX
response. Otherwise, an instance of TransportError (or a more specific
subclass) will be raised. You can see other exceptions and error states in
exceptions. If you do not want an exception to be raised, you can always
pass in an ignore parameter with either a single status code that should be
ignored or a list of them.
from elasticsearch import Elasticsearch
es=Elasticsearch()
# ignore 400 cause by IndexAlreadyExistsException when creating
an index
[Link](index='test-index',ignore=400)
# ignore 404 and 400
[Link](index='test-index',ignore=[400,404])
38
Chapter 2 ETL with Python
Neo4j Python Driver
There are a variety of systems, such as network topology and social
networks. However, when difficulties are shown as a graph, they are
quickly resolved. Neo4j is a database that stores data in the form of a graph
and executes queries through a graphical interface. The Neo4j Python
driver is supported by Neo4j and connects with the database through the
binary protocol. It tries to remain minimalistic but at the same time be
idiomatic to Python.
pip install neo4j-driver
from neo4j.v1 import GraphDatabase, basic_auth
driver11 = [Link]("bolt://localhost", auth=basic_
auth("neo4j", "neo4j"))
session11 = [Link]()
[Link]("CREATE (a:Person {name:'Sayan',
title:'Mukhopadhyay'})")
result 11= [Link]("MATCH (a:Person) WHERE [Link] =
'Sayan' RETURN [Link] AS name, [Link] AS title")
for recordi n resul11t:
print("%s %s"% (record["title"], record["name"]))
[Link]()
neo4j-rest-client
The main objective of neo4j-rest-client is to make sure that the Python
programmers already using Neo4j locally through python-embedded
are also able to access the Neo4j REST server. So, the structure of the
neo4j-rest-client API is completely in sync with python-embedded. But,
a new structure is brought in so as to arrive at a more Pythonic style
and to augment the API with the new features being introduced by the
Neo4j team.
39
Chapter 2 ETL with Python
In-Memory Database
Another important class of databases is an in-memory database. This type
stores and processes the data in RAM. So, operations on the database are
fast, and the data is volatile. SQLite is a popular example of an in-memory
database. In Python you need to use the sqlalchemy library to operate on
SQLite. In Chapter 1’s Flask and Falcon example, I showed you how to
select data from SQLite. Here I will show how to store a Pandas data frame
in SQLite:
from sqlalchemy import create_engine
import sqlite3
conn = [Link]('[Link]')
[Link]('''CREATE TABLE if not exists multiplier
(domain CHAR(50),
low REAL,
high REAL);''')
[Link]()
db_name = "your db name ""
disk_engine = create_engine(db_name)
df.to_sql('scores', disk_engine, if_exists='replace')
MongoDB (Python Edition)
MongoDB is an open-source document database designed for superior
performance, easy availability, and automatic scaling. MongoDB makes
sure that object-relational mapping (ORM) is not required to facilitate
development. A document that contains a data structure made up of
field and value pairs is referred to as a record in MongoDB. These records
are akin to JSON objects. The values of fields may be comprised of other
documents, arrays, and arrays of documents.
40
Chapter 2 ETL with Python
{
"_id":ObjectId("01"),
"address": {
"street":"Siraj Mondal Lane",
"pincode":"743145",
"building":"129",
"coord": [ -24.97, 48.68 ]
},
"borough":"Manhattan",
Import Data into the Collection
mongoimport can be used to place the documents into a collection in a
database, within the system shell or a command prompt. If the collection
already exists in the database, the operation will discard the original
collection first.
mongoimport --DB test --collection restaurants --drop --file ~/
downloads/[Link]
The mongoimport command is joined to a MongoDB instance running
on localhost on port 27017. The --file option provides a way to import
the data; here it’s ~/downloads/[Link].
To import data into a MongoDB instance running on a different host
or port, the hostname or port needs to be mentioned specifically in the
mongoimport command by including the --host or --port option.
There is a similar load command in MySQL.
41
Chapter 2 ETL with Python
Create a Connection Using pymongo
To create a connection, do the following:
import MongoClient from pymongo.
Client11 = MongoClient()
If no argument is mentioned to MongoClient, then it will default to the
MongoDB instance running on the localhost interface on port 27017.
A complete MongoDB URL may be designated to define the
connection, which includes the host and port number. Let’s take a look at
an example.
First, install Mongo using this command: yum/apt install mongo.
Then, launch MongoDB using this command: service mongo start.
The following code makes a connection to a MongoDB instance that
runs on [Link] and port 27017:
Client11 = MongoClient("mongodb://myhostname:27017")
Access Database Objects
To assign the database named primer to the local variable DB, you can use
either of the following lines:
Db11 = [Link]
db11 = client11['primer']
Collection objects can be accessed directly by using the dictionary
style or the attribute access from a database object, as shown in the
following two examples:
Coll11 = [Link]
coll = db11['dataset']
42
Chapter 2 ETL with Python
Insert Data
You can place a document into a collection that doesn’t exist, and the
following operation will create the collection:
result=[Link].insert_one({<<your json >>)
Update Data
Here is how to update data:
result=[Link].update_one(
{"building": "129",
{"$set": {"[Link]": "MG Road"}}
)
Remove Data
To expunge all documents from a collection, use this:
result=[Link].delete_many({})
Cloud Databases
Even though the cloud has its own chapter, we’d like to provide you with an
overview of cloud databases, particularly databases for large data. People
prefer cloud databases when they want their systems to scale automatically.
Google Big Query is the greatest tool for searching your data. Azure
Synapsys has a similar feature; however, it is significantly more expensive.
You can store data on S3, but if you want to run a query, you’ll need Athena,
which is expensive. So, in modern practice, data is stored as a blob in S3,
and everything is done in a Python application. If there is an error in data
finding, this method takes a long time. Amazon Redish can also handle a
considerable quantity of large data and comes with a built-in BI tool.
43
Chapter 2 ETL with Python
Pandas
The goal of this section is to show some examples to enable you to begin
using Pandas. These illustrations have been taken from real-world
data, along with any bugs and weirdness that are inherent. Pandas is a
framework inspired by the R data frame concept.
Please find the CSV file at the following link:
[Link]
To read data from a CSV file, use this:
import pandas as pd
broken_df=pd.read_csv('fetaure_engineering_data.csv')
To look at the first three rows, use this:
broken_df[:3]
To select a column, use this:
broken_df[' MSSubClass ']
To plot a column, use this:
broken_df[' MSSubClass’ '].plot()
To get a maximum value in the data set, use this:
MaxValue= broken_df[' MSSubClass’].max() where MSSubClass is
the column header
There are many other methods such as sort, groupby, and orderby in
Pandas that are useful when playing with structured data. Also, Pandas has
a ready-made adapter for popular databases such as MongoDB, Google
Big Query, and so on.
44
Chapter 2 ETL with Python
One complex example with Pandas is shown next. In the X data frame
for each distinct column value, find the average value of the floor grouping
by the root column.
for col in [Link]:
if col != 'root':
avgs =
[Link]([col,'root'],as_index=False)['floor'].
aggregate([Link])
for i,row in [Link]():
k = row[col]
v = row['floor']
r = row['root']
[Link][(X[col] == k)
& (X['root'] == r),
col] = v2.
You can do any experiment in the Pandas framework with the data
given for classification and regression problems.
ETL with Python (Unstructured Data)
Dealing with unstructured data is an important task in modern data
analysis. In this section, I will cover how to parse emails, and I’ll introduce
an advanced research topic called topical crawling.
Email Parsing
See Chapter 1 for a complete example of web crawling using Python.
Like Beautiful Soup, Python has a library for email parsing. The
following is the example code to parse email data stored on a mail server.
The inputs in the configuration are the username and number of mails to
parse for the user.
45
Chapter 2 ETL with Python
In this code, you have to mention the email user, email folder, and
index of the mail-in config; code will write from the address to handle the
subject and the date of the email in the CSV file.
from [Link] import Parser
import os
import sys
conf = open([Link][1])
config={}
users={}
# parsing the config file
for line in conf:
if ("," in line):
fields = [Link](",")
key = fields[0].strip().split("=")[1].strip()
val = fields[1].strip().split("=")[1].strip()
users[key] = val
else:
if ("=" in line):
words = [Link]().split('=')
config[words[0].strip()] = words[1].strip()
[Link]()
# extracting information from user email
for usr in [Link]():
path = config["path"]+"/"+usr+"/"+config["folder"]
files = [Link](path)
for f in sorted(files):
if(int(f) > int(users[usr])):
users[usr] = f
path1 = path + "/" + f
46
Chapter 2 ETL with Python
data = ""
with open (path1) as myfile:
data=[Link]()
if data != "" :
parser = Parser()
email = [Link](data)
out = ""
out = out + str([Link]('From')) + "," +
str([Link]('To')) + "," + str([Link]
('Subject')) + "," + str([Link]
('Date')).replace(","," ")
if email.is_multipart():
for part in email.get_payload():
out = out + "," + str(part.get_
payload()).replace("\n"," ")
.replace(","," ")
else:
out = out + "," + str(email.get_
payload()).replace("\n"," ").
replace(","," ")
print out,"\n"
#updating the output file
conf = open([Link][1],'w')
[Link]("path=" + config["path"] + "\n")
[Link]("folder=" + config["folder"] + "\n")
for usr in [Link]():
[Link]("name="+ usr +",value=" + users[usr] + "\n")
[Link]()
Sample config file for above code.
47
Chapter 2 ETL with Python
path=/cygdrive/c/share/enron_mail_20110402/enron_
mail_20110402/maildir
folder=Inbox
name=storey-g,value=142
name=ybarbo-p,value=775
name=tycholiz-b,value=602
Topical Crawling
Topical crawlers are intelligent crawlers that retrieve information from
anywhere on the Web. They start with a URL and then find links present in
the pages under it; then they look at new URLs, bypassing the scalability
limitations of universal search engines. This is done by distributing
the crawling process across users, queries, and even client computers.
Crawlers can use the context available to infinitely loop through the links
with a goal of systematically locating a highly relevant, focused page.
Web searching is a complicated task. A large chunk of machine
learning work is being applied to find the similarity between pages, such as
the maximum number of URLs fetched or visited.
Crawling Algorithms
Figure 2-2 describes how the topical crawling algorithm works with its
major components.
48
Chapter 2 ETL with Python
Figure 2-2. Topical crawling described
The starting URL of a topical crawler is known as the seed URL. There
is another set of URLs known as the target URLs, which are examples of
desired output.
Another intriguing application of crawling is for a startup that wants
to uncover crucial keywords for every IP address. In the HTTP packet
header, they acquire the user’s browsing history from the Internet service
provider. After crawling the URL visited by that IP, they classify the words
in the text using name-entity recognition (Stanford NLP), which is easily
implementable by the RNN explained in Chapter 5. All name entities and
their types, such as names of people, locations, and organizations, are
recommended for the user.
import requests
from bs4 import BeautifulSoup
import nltk
49
Chapter 2 ETL with Python
from [Link] import word_tokenize
from [Link] import stopwords
from [Link] import StanfordNERTagger
import re
import json
import os
import socket
import struct
def ip2int(addr):
return [Link]("!I", socket.inet_aton(addr))[0]
def int2ip(addr):
return socket.inet_ntoa([Link]("!I", addr))
java_path = '/usr/bin/java'
[Link]['JAVAHOME'] = java_path
[Link]['STANFORD_MODELS'] = '/home/ec2-user/
[Link]'
[Link].config_java(java_path)
f = open("/home/ec2-user/[Link]")
res = []
stop_words = set([Link]('english'))
for line in f:
fields = [Link]().split(",")
url = fields[1]
ip = fields[-1]
print(ip)
print(url)
tags_del = None
if True:
50
Chapter 2 ETL with Python
try:
ip = ip2int(ip)
except:
continue
print(ip)
tagged = None
try:
code = [Link](url)
plain = [Link]
s = BeautifulSoup(plain)
tags_del = s.get_text()
if tags_del is None:
continue
no_html = [Link]('<[^>]*>', '', tags_del)
st = StanfordNERTagger('/home/ec2-user/english.
[Link]',
'/home/ec2-user/stanford-[Link]')
tokenized = word_tokenize(no_html)
tagged = [Link](tokenized)
except:
pass
if tagged is None:
continue
for t in tagged:
t = list(t)
t[0] = t[0].replace(' ', '')
t[-1] = t[-1].replace(' ', '')
print(t)
if t[0] in stop_words:
continue
unit = {}
51
Chapter 2 ETL with Python
unit["ip"] = ip
unit["word"] = t[0]
unit["name_entity"] = t[-1]
[Link](unit)
res_final = {}
res_final["result"] = res
#except:
# pass
#except:
# pass
with open('[Link]', 'w') as fp:
[Link](res, fp)
Summary
In this chapter, we discussed different kind of databases and their use
cases, and we discussed collecting text data from the Web and extracting
information from different types of unstructured data like email and
web pages.
52