Maxmind GeoLite2 CSV to Oracle database

Scripts to setup Extrac, Transform and Load (ETL) process for Maxmind GeoLite2 CSV files to Oracle database.

Description

Scripts creates totally four users to database.

GEOLITE2_A and GEOLITE2_B are owners of tables where CSV data is loaded
GEOLITE2_OWNER owns object relating ETL logic
GEOLITE2_READER is for accesing data

User GEOLITE2_READER access active data schema

ETL Process

Data is loaded to user GEOLITE2_A or GEOLITE2_B tables depending which one isn't active. Before data load target table is truncated, constraint and indexes are droped. After succesful data load, synonyms are pointed to schema tables where data was loaded.

Prerequisites

Oracle database on Linux server. NOTE! Scripts are tested only using 11G XE database.

Installing

Prepare server

Create directories to database server

mkdir -p /opt/geolite2/data
mkdir /opt/geolite2/log
mkdir /opt/geolite2/script

Give privileges to dba group to server directories

chgrp dba /opt/geolite2/data
chgrp dba /opt/geolite2/log
chmod g+w /opt/geolite2/data
chmod g+w /opt/geolite2/log

Create dummy files to server

touch /opt/geolite2/data/GeoLite2-ASN-CSV.zip
touch /opt/geolite2/data/GeoLite2-City-CSV.zip

Change oracle as owner of dummy files

chown oracle /opt/geolite2/data/GeoLite2-ASN-CSV.zip
chown oracle /opt/geolite2/data/GeoLite2-City-CSV.zip

Place script download_data.sh to directory /opt/geolite2/script

Give execute privilege to dba group

chgrp dba /opt/geolite2/script/download_data.sh
chmod g+x /opt/geolite2/script/download_data.sh

Database

NOTE! Install script drops and recreates users GEOLITE2_A, GEOLITE2_B, GEOLITE2_OWNER and GEOLITE2_READER if exists. Database directories GEOLITE2_SCRIPT_DIR, GEOLITE2_LOG_DIR and GEOLITE2_DATA_DIR are dropped and recreated.

Script install.sql needs four arguments

Name of tablespace for GEOLITE2_OWNER and GEOLITE2_READER
Name of tablespace for data schema A
Name of tablespace for data schema B
Name of users temporary tablespace

Run install.sql script as SYS e.g.

@install.sql USERS USERS USERS TEMP

Change users GEOLITE2_OWNER and GEOLITE2_READER password

alter user geolite2_user identified by <password>
/
alter user geolite2_owner identified by <password>
/

Connect using user GEOLITE2_OWNER and run ETL process to populated data

begin
  dbms_scheduler.run_job('RUN_ETL_FLOW_JOB');
end;
/

Usage

Example query when connected using user GEOLITE2_READER

with src as (
  select '8.8.8.8' as ip
  from dual
)
, fn as (
  select ip_to_dec(ip) as ip_dec 
  from src
)
select src.ip
  ,t1.network_address as network
  ,(
    select autonomous_system_organization
    from asn_blocks x
    where 1 = 1
      and fn.ip_dec
    between x.network_start
      and x.network_end
  ) as autonomous_system_organization
  ,t2.continent_code
  ,t2.continent_name
  ,t2.country_iso_code as country_code
  ,t2.country_name
  ,t2.subdivision_1_iso_code as region_code
  ,t2.subdivision_1_name as region_name
  ,t2.city_name
  ,t1.postal_code as zip_code
  ,t1.latitude
  ,t1.longitude
  ,t2.metro_code
from city_blocks t1
join city_locations t2 on t1.geoname_id = t2.geoname_id
cross join src
cross join fn
where 1 = 1
  and fn.ip_dec
between t1.network_start
  and t1.network_end
;

License

This project is licensed under the MIT License - see the LICENSE file for details

jariolaine/GeoLite2