DEV Community: Suttipong Kullawattana

How to setup pyspark with Jupyter Notebook for Data Engineer

Suttipong Kullawattana — Sat, 15 Oct 2022 13:55:36 +0000

I have to conclusion how to setup pyspark for building ETL pipeline with Jupyter Notebook by summary step like this.

First step, Install python 3.9.1 for use on python.

Second step, install scala $ brew install scala

Third step, start with $ pyspark

Fourth step, run data frame

Reference: apache-spark, Getting started with mongodb, pyspark and jupyter-notebook, How to install pyspark on mac

How to setup MongoDB with Jupyter Notebook

Suttipong Kullawattana — Sat, 15 Oct 2022 13:11:29 +0000

I have to conclusion how to setup MongoDB (NoSQL) with Jupyter Notebook by summary step like this.

First step, Install python 3.9.1 for use on python, install $ pip install pymongo.

Second step, Install homebrew with $ /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install.sh)"

Third step, Install Jupyter Notebook on Windows, MacOS or Linux and try to install $ pip install notebook in terminal and run $ jupyter notebook

Fourth step, Install MongoDB Community Edition on macOS mongoDB and MongoDB Compass

install $ brew tap mongodb/brew and $ brew install mongodb-community

Install MongoDB on Ubuntu

$ sudo apt update

$ sudo apt install wget curl gnupg2 software-properties-common apt-transport-https ca-certificates lsb-release

$ curl -fsSL https://www.mongodb.org/static/pgp/server-6.0.asc|sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/mongodb-6.gpg

$ echo "deb [ arch=amd64,arm64 ] https://repo.mongodb.org/apt/ubuntu focal/mongodb-org/6.0 multiverse" | sudo tee /etc/apt/sources.list.d/mongodb-org-6.0.list

$ wget http://archive.ubuntu.com/ubuntu/pool/main/o/openssl/libssl1.1_1.1.1f-1ubuntu2.16_amd64.deb

$ sudo dpkg -i ./libssl1.1_1.1.1f-1ubuntu2.16_amd64.deb

$ sudo apt update

$ sudo apt install mongodb-org

$ sudo systemctl enable --now mongod

Checking config of mongod.conf on development mode

$ sudo vi /etc/mongod.conf

#add this line
    security:
          authorization: enabled
# network interfaces
    net:
      port: 27017
      bindIp: 0.0.0.0  # Change to global

Restart service again with $ sudo service mongod restart

Create the “db” directory where MongoDB stores its database by $ mkdir -p /data/db

Make sure that the directory has the previlage to read and write from the directory by using chown (change owner) with $ chown -R 'id -un'/data/db

Start MongoDB command : $ brew services start mongodb-community

Using $ ps aux | grep -v grep |grep mongod, allow us to verify that MongoDB is running. You should see the current ststus of your mongod process.

On Ubuntu, You can followup setup directory like this.
$ sudo systemctl stop mongod.service
$ sudo mkdir -p /newdata/mongo
$ sudo chown -R mongodb:mongodb /newdata/mongo
$ sudo rsync -av /var/lib/mongodb /newdata/mongo
$ sudo mv /var/lib/mongodb /var/lib/mongodb.bak
$ sudo ln -s /newdata/mongo /var/lib/mongodb
$ sudo systemctl daemon-reload
$ sudo systemctl start mongod

Five step, Install mongosh for start mongosh, MongoDB Shell and create role user on database

$ mongosh
    test> use admin
    test> db.createUser({user: "myUserAdmin", pwd: "abc123",  roles: [ { role: "userAdminAnyDatabase", db: "admin" }, "readWriteAnyDatabase" ]})
    test> exit
$ mongosh -u mongdbuser -p --authenticationDatabase admin
Enter password: ********

write the code below

import pymongo
from pymongo import MongoClient

username='myUserAdmin'
password='abc123'
client = MongoClient('mongodb://%s:%s@127.0.0.1' % (username, password))
db=client['mongotestdb']

my_collection = db["patient_data"]

patient_record = {
   "Name": "Maureen Skinner",
   "Age": 87,
   "Sex": "F",
   "Blood pressure": [{"sys": 156}, {"dia": 82}],
   "Heart rate": 82
}

my_collection.insert_one(patient_record)

for item in my_collection.find():
    print(item)

Result on MongoDB Compass

Stop MongoDB command : $ brew services stop mongodb-community

Reference: MongoDB tutorial, mongosh, Building Data Pipeline | Spotify - Jupyter Notebook - MongoDB, Connecting MongoDB to Jupyter Notebook, Example Query NoSQL, Setting user account, Using MongoDB in python, Install Jupyter Notebook on Ubuntu

How to setup PostgreSQL with Jupyter Notebook

Suttipong Kullawattana — Sat, 15 Oct 2022 12:27:51 +0000

I have to conclusion how to setup PostgreSQL (RDBMS) on development environment with Jupyter Notebook by summary step like this.

First step, Install python 3.9.1 for use on python or how to uninstall python if you have any issue on python configuration, You can follow from link.

Second step, Install homebrew with $ /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install.sh)"

Third step, Install Jupyter Notebook on Windows, MacOS or Linux and try to install $ pip install notebook in terminal and run $ jupyter notebook

Fourth step, Install psycopg2 and try to install $ pip install psycopg2-binary on terminal.

If you have any install psycopg2 on Ubuntu, please follow step like this.
$ sudo apt-get update
$ pip install psycopg2-binary and then install
$ sudo apt-get install libpq-dev python-dev
$ sudo pip install psycopg2

Five step, Install PostgreSQL

Install PostgreSQL on Ubuntu
$ sudo apt update
$ sudo apt install postgresql postgresql-contrib
$ sudo systemctl start postgresql.service

Checking config of pg_hba.conf and postgresql.conf on development mode

$ sudo nano /etc/postgresql/14/main/pg_hba.conf
host all all 0.0.0.0/0 md5

$ sudo nano /etc/postgresql/14/main/postgresql.conf
listen_addresses = '*' # what IP address(es) to listen on;

Restart service again with $ sudo service postgresql restart

Six step, Create role user to super user by loggin on PgAdmin4
go to Object > Create > Login/Group Role

Create the "username" that was named in the psql.

How to create username, password for database on Ubuntu, you can followup like this.

$ sudo -u postgres psql
$ postgres=# create database mydb;
$ postgres=# create user myuser with encrypted password 'mypass';
$ postgres=# grant all privileges on database mydb to myuser;

Create password for user.
Give it all the rights and save information.

Try the password immediately in the psql terminal.

How to run and testing PostgreSQL on terminal.

$ export PGPASSWD=mypass
$ psql -h 127.0.0.1 -U myuser mydb
$ mydb=>

Example



$ mydb=> DROP TABLE IF EXISTS EMPLOYEE;
DROP TABLE
$ mydb=> CREATE TABLE CRICKETERS (
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255),
   Age INT,
   Place_Of_Birth VARCHAR(255),
   Country VARCHAR(255));
$ CREATE TABLE
$ mydb=> \dt
          List of relations
 Schema |    Name    | Type  | Owner
--------+------------+-------+--------
 public | cricketers | table | myuser
(1 rows)

Insert Data



$ mydb=> INSERT INTO CRICKETERS (First_Name, Last_Name, Age, Place_Of_Birth, Country) VALUES ('Shikhar', 'Dhawan', 33, 'Delhi', 'India');
$ mydb=> INSERT INTO CRICKETERS (First_Name, Last_Name, Age, Place_Of_Birth, Country) VALUES ('Kumara', 'Sangakkara', 41, 'Matale', 'Srilanka');
$ mydb=> INSERT INTO CRICKETERS (First_Name, Last_Name, Age, Place_Of_Birth, Country) VALUES ('Virat', 'Kohli', 30, 'Delhi', 'India');
$ mydb=> INSERT INTO CRICKETERS (First_Name, Last_Name, Age, Place_Of_Birth, Country) VALUES ('Rohit', 'Sharma', 32, 'Nagpur', 'India');

$ mydb=> SELECT * FROM cricketers;
 first_name | last_name  | age | place_of_birth | country
------------+------------+-----+----------------+----------
 Shikhar    | Dhawan     |  33 | Delhi          | India
 Kumara     | Sangakkara |  41 | Matale         | Srilanka
 Virat      | Kohli      |  30 | Delhi          | India
 Rohit      | Sharma     |  32 | Nagpur         | India
(4 rows)

How to exit PostgreSQL on Terminal of MacOS or Ubuntu Linux.

$ sudo -i -u postgres psql
$ postgres=# \q => to exit

write the code below



import psycopg2
from psycopg2 import Error

try:
    # Connect to an existing database
    connection = psycopg2.connect(user="myuser",
                                  password="mypass",
                                  host="127.0.0.1",
                                  port="5432",
                                  database="postgres")

    # Create a cursor to perform database operations
    cursor = connection.cursor()
    # Print PostgreSQL details
    print("PostgreSQL server information")
    print(connection.get_dsn_parameters(), "\n")
    # Executing a SQL query
    cursor.execute("SELECT version();")
    # Fetch result
    record = cursor.fetchone()
    print("You are connected to - ", record, "\n")

    #Doping EMPLOYEE table if already exists.
    cursor.execute("DROP TABLE IF EXISTS CRICKETERS")

    #Creating table as per requirement
    sql =''' 
    CREATE TABLE CRICKETERS (
    First_Name VARCHAR(255),
    Last_Name VARCHAR(255),
    Age INT,
    Place_Of_Birth VARCHAR(255),
    Country VARCHAR(255));
    '''
    cursor.execute(sql)
    print("Table created successfully........")
    connection.commit()
    #Closing the connection
    connection.close()

except (Exception, Error) as error:
    print("Error while connecting to PostgreSQL", error)
finally:
    if (connection):
       cursor.close()
       connection.close()
       print("PostgreSQL connection is closed")

Result of field in PgAdmin4

Reference : Connect To PostgreSQL Database Server, Install Jupyter Notebook on Ubuntu