Configuring Geolocation database to WSO2 IS-Analytics using MySQL

• Download the Geolocation data from here
• Unzip the downloaded file
• Using MySQL, create a database named “GEO_LOCATION_DATA”

mysql> CREATE DATABASE GEO_LOCATION_DATA;
mysql > USE GEO_LOCATION_DATA;

• Create the tables by executing the script mysql.sql in the downloaded Geolocation Data/dbscripts directory

mysql> source path/to/file/mysql.sql;

• Restore data to BLOCKS and LOCATION tables

Importing Geolocation Data/data/BLOCKS.csv

LOAD DATA LOCAL INFILE ‘/path/to/file/BLOCKS.csv’
INTO TABLE BLOCKS FIELDS
TERMINATED BY ‘;’
ENCLOSED BY ‘“‘
LINES TERMINATED BY ‘\n’
IGNORE 1 LINES;

Importing Geolocation Data/data/LOCATION.csv

LOAD DATA LOCAL INFILE ‘/path/to/file/LOCATION.csv’
INTO TABLE LOCATION FIELDS
TERMINATED BY ‘;’
ENCLOSED BY ‘“‘
LINES TERMINATED BY ‘\n’
IGNORE 1 LINES;

• Download JDBC driver for MySQL and copy it to <IS_ANALYTICS_HOME>/repository/components/dropins directory.
• Configure geolocation-datasources.xml file in the <IS_ANALYTICS_HOME>/repository/conf/datasources directory as follows. (here the username and the password should be MySQL credentials)

<datasources-configuration xmlns:svns="http://org.wso2.securevault/configuration">
 <datasources>
 <datasource>
 <name>GEO_LOCATION_DATA</name>
 <description>The datasource for Geo Location database</description>
 <jndiConfig>
 <name>jdbc/GEO_LOCATION_DATA</name>
 </jndiConfig>
 <definition type="RDBMS">
 <configuration>
 <url>jdbc:mysql://localhost:3306/GEO_LOCATION_DATA</url>
 <username>wso2carbon</username>
 <password>wso2carbon</password>
 <driverClassName>com.mysql.jdbc.Driver</driverClassName>
 <maxActive>50</maxActive>
 <maxWait>60000</maxWait>
 <testOnBorrow>true</testOnBorrow>
 <validationQuery>SELECT 1</validationQuery>
 <validationInterval>30000</validationInterval>
 <defaultAutoCommit>false</defaultAutoCommit>
 </configuration>
 </definition>
 </datasource>
 </datasources>
</datasources-configuration>