Scripts to setup Extrac, Transform and Load (ETL) process for Maxmind GeoLite2 CSV files to Oracle database.
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
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.
Oracle database on Linux server. NOTE! Scripts are tested only using 11G XE database.
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
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;
/
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
;
This project is licensed under the MIT License - see the LICENSE file for details