Analytics » Upload Tool » Uploading Data from Relational databases

 Uploading Data from Local Relational Databases

If you have any applications that stores data in any relational databases like MS Access, MySQL, SQL Server, Sybase, PostgreSQL, Oracle, SQLite, DB2, DB2 AS/400, SQLite, FireBird or any JDBC driver supported database, then you can use Upload Tool to pull data from your databases and upload the same into Virtutem Analytics.


In this section, we will discuss about how to upload data from your local databases into Virtutem Analytics. You can also read about how to configure the Upload tool to periodically upload/synchronize the data from your databases into Virtutem Analytics.

 Prerequisite

Downloading Upload Tool

Virtutem Analytics Upload Tool can be downloaded from this page

Installing and Configuring

You learn more on installing the upload tool and configuring the common parameters (like proxy settings etc.,) for the tool at  this page.

 Connect and Upload data from Local Relational Database

To upload data from local (in-house) relational databases your need to specify your local database connection settings in the configuration file   database_connection_params.conf   as described below.

 Specify Local Database Connection Settings

You need to specify the connection settings about your in-house database behind firewall in this file. The following screenshot illustrates a typical   database_connection_params.conf.


The example in the following screenshot illustrates the connection settings for a MySQL database.

The following are the parameters that are to be given in   database_connection_params.conf   file:

DBTYPE

Specify your local database type. e.g: MySQL, SQL Server, Oracle, Sybase etc.,. (Refer to the   next section   which contains the list of possible values)

HOSTNAME

Host name of your computer in which the database is running, i.e., the name of the computer where your database is running.

PORT

The port number used by the database server. (Refer to the   next section   to know the default port number for different databases).

USERNAME

Your local database login name.

PASSWORSD

Password for accessing your local database.

DBNAME

Your local database name from which the data is to be uploaded.

MDBFILENAME

In case you are going to upload data from MS Access database, then specify the complete path of MS Access database file name which contains the data.  
e.g., MDBFILENAME="C:MSACCESSmdbfiles" est.mdb.

SQLITEFILENAME

In case you are going to upload data from SQLite database, then specify the complete path of SQLite database file name which contains the data.
e.g., SQLITEFILENAME = C:SQLITEsqlitefilesSales-Database.db

FIREBIRDFILENAME

In case you are going to upload data from Firebird database, then specify the complete path of Firebird database file name which contains the data.
e.g., FIREBIRDFILENAME = C:FIREBIRD irebirdfilesmydb.fdb

Once you have configured the above setting, you need to provide the SQL SELECT queries that are to be executed in your local database to fetch data and upload the same into Virtutem Analytics. To learn about how to specify these queries and executing the upload tool   click here

 Default Settings for Different Databases

Default Settings for Different Databases

The following table captures the default connection settings that can be specified in the database_connections_params.conf   for different database vendor type. If you are not using the default port provided by the database, then you need to specify the corresponding port for connecting. In case you are using a database which is not listed in this table and it supports JDBC (Java Database Connectivity) driver connectivity, then you could configure the Upload tool for the same as said in the   Connecting to the JDBC Driver Supported Databases   section.

Database

DBTYPE

PORT (default)

FILE NAME

MySQL

mysql

3306

-

Oracle

oracle

1521

-

SQL Server

sqlserver

1433

-

Sybase

sybase

5000

-

PostgreSQL

postgresql

5432

-

DB2

db2

50000

-

DB2-AS/400

db2as400

50000

-

MS Access

msaccess

-

Specify the full path of the database from which you want to upload data in the  MDBFILENAME  parameter.
Example:

C:MSACCESSmdbfilesSales-Database.mdb

MS Access 2010

msaccess

Content Column 2

Specify the full path of the database from which you want to upload data in the  MDBFILENAME  parameter.
Example:
 
C:MSACCESSmdbfilesSales-Database.accdb

SQLite

sqlite

Content Column 2

Specify the full path of the database from which you want to upload data in the  SQLITEFILENAME parameter. 
Example:
C:SQLITEsqlitefilesSales-Database.db

Firebird

firebird

3050

Specify the full path of the database from which you want to upload data in the  FIREBIRDFILENAME parameter. 
Example:
C:FIREBIRD irebirdfilesmydb.fdb

Setting up JDBC Driver for MySQL

If you plan to use the Upload tool to fetch data from your local MySQL databases and upload it into Virtutem Analytics, then you need to setup the MySQL JDBC driver for the tool to work. For all other supported databases the corresponding JDBC drivers are bundled along with the tool, by default.


The following are the steps to setup the MySQL JDBC driver:

Once the JDBC driver is set, you can connect the Upload tool to any local MySQL database to fetch the required records and upload the same into Virtutem Analytics .

MS Access File path

In case you are going to upload data from MS Access, you need to specify the complete path of the MS Access database file which contains the data, for the parameter   MDBFILENAME  

e.g.,: MDBFILENAME="C:MSACCESSmdbfilesSales.mdb." No other parameter is required for MS Access database

Using SQL Server Windows Authentication

The   Windows Authentication   can also be used to connect to your local SQL Server database. To do this, you need to provide the database name followed by the Windows domain name separated by semi-colon ( ; ) for the parameter DBNAME in the   database_connections_params.conf   file.


Syntax


DBNAME=<your_database_name>;domain=<your_domain_name>


<your_database_name> - This is the database name in SQL Server to connect


<your_domain_name> - The Windows domain name that you use.


Example:   DBNAME=sqlserverdbname;domain=workgroup

 Connecting to the JDBC Driver Supported Databases

Apart from the default databases listed above, any database which supports JDBC (Java Database Connectivity) driver can also be connected using Virtutem Analytics Upload Tool. You could connect the databases using Upload Tool by simply providing the JDBC Driver Class and JDBC URL.


The following is the list of commonly used databases which can be connected through the Upload Tool using JDBC Driver. You can also connect to other database supporting JDBC driver using the method specified here.

Database

DBTYPE

Driver Class Name

Connection URL

PORT (default)

Teradata

teradata

com.teradata.jdbc.
TeraDriver

jdbc:teradata://<host>
[/ParameterName=Value,
ParameterName="
Value,.....]

1025

Informix

informix

com.informix.jdbc.
IfxDriver

jdbc:informix-sqli
://<host>
:<port>/<dbname>
:informixserver=<dbservername>

1533

HP Vertica

hpvertica

com.vertica.jdbc.Driver

jdbc:vertica://
<host>:<port>/
<dbname>

5433

Ingres

ingres

com.ingres.jdbc.
IngresDriver

jdbc:ingres://
<host>:<port>/
<dbname>

>21071

Greenplum

greenplum

org.postgresql.Driver

jdbc:postgresql://
<host>:<port>/
<dbname>

5342

SQL Anywhere

sqlanywhere

com.sybase.
jdbc4.jdbc.SybDriver

jdbc:sybase:Tds:
<host>:<port>?
ServiceName="<dbname>

2638

Derby

derby

org.apache.derby.
jdbc.ClientDriver

jdbc:derby:net://
<host>:<port>/
<dbname>

1527

H2

h2

org.h2.Driver

jdbc:h2:tcp://
<host>/<dbname>

9092

Cache

cache

com.intersys.jdbc.
CacheDriver

jdbc:Cache://
<host>:<port>/<namespace>

1972

Progress Openedge

progressopenedge

com.ddtek.jdbc.
openedge.
OpenEdgeDriver

jdbc:datadirect:
openedge://
<host>;
databaseName="<dbname>

9092

Cubrid

cubrid

cubrid.jdbc.driver.
CUBRIDDriver

jdbc:cubrid:
<host>:<port>:<dbname>:::

33000

Mimer SQL

mimersql

com.mimer.jdbc.Driver

jdbc:mimer:[//[username
[:password]
@]host[:port]] [/dbname]

1360

HSQLDB

hsqldb

org.hsqldb.jdbcDriver

jdbc:hsqldb
:hsql://<host>:
<port>/<alias>

9001

Mckoi

mckoi

com.mckoi.JDBCDriver

jdbc:mckoi:
//host:port/schema/

9157

FileMaker Pro

filemakerpro

com.filemaker.
jdbc.Driver

jdbc:filemaker://<host>/<dbname>

2399

The following are the steps to connect the database using JDBC Driver:

  • Copy the related JDBC driver(JDBC jar file) of the database into the   <UploadTool_Home>/lib   directory. You need to download the corresponding JDBC driver provided by the database vendor. Refer to the vendor's database documentation for details.

  • Open the   <UploadTool_Home>/conf/database_connection_params.conf   file and configure the following parameters:

DBTYPE

Specify your database name in this field (Eg., teradata or informix or vertica or greenplum etc.,)

DRIVERCLASSNAME

Specify the JDBC driver class name of the database (refer to the table above)

CONNECTIONURL

Specify database URL with or without username and password to establish connection with the local database (refer to the table above)

USERNAME

Specify the USERNAME to access the database. This is not required if you have specified the authentication details in CONNECTIONURL itself.

PASSWORD

Specify the PASSWORD to access the database. This is not required if you have specified the authentication details in CONNECTIONURL itself.

The following screenshot illustrates the connection settings for Teredata database. 

Once you have configured the above setting, you need to provide the SQL SELECT queries that are to be executed in your local database to fetch data and upload the same into Virtutem Analytics . To learn about how to specify these queries and executing the upload tool   click here.

 Specify Queries to be Executed on Relational databases

The   database_sql_queries.xml   file contains all the settings related to the SQL SELECT Queries that are to be executed in the database for fetching the required data, along with settings about the Virtutem Analytics database and table names into which the data is to be uploaded. To fetch data from your relational databases, you need to specify SQL SELECT query.  These queries will be executed by the upload tool in your local database to fetch the necessary records and upload the same into Virtutem Analytics according to the settings provided.


You can provide any number of SQL SELECT queries to execute. Each query is to be provided in a <Query> <Query> XML element. Multiple queries can be grouped within the <Queries> <Queries> element.


The following screenshot illustrates a sample SQL SELECT queries in the   database_sql_queries.xml   file.

The above example queries will fetch data from two tables and import the same into Virtutem Analytics  reporting database   MyDB . Data from the   dbtable1   will be appended into   mytable1   in Virtutem Analytics . And data from dbtable2   , where the employee number is less than 1000, will be added into   mytable2   after deleting the existing data.


The following is the SQL SELECT query format to be followed to fetch data from your relational database.

<Query   dbname ="virtutem_analytics_dbname"   tablename ="virtutem_analytics_tablename"   importtype =" APPEND / TRUNCATEADD / UPDATEADD "   matchingcols ="matching_cols_for_updateadd" selectcols ="columns_to_import_from_csv" skiptop ="number_of_rows_to_be_skipped">sql_query_ to_be_executed</Query>

As part of the <Query> node, you also need the provide details about the Virtutem Analytics database into which the data that is fetched from executing the query is to be uploaded. The following table explains all the parameters to be provided as part of the <Query> node.

Parameter

Description

dbname

The Virtutem Analytics database name into which the data is to be uploaded after executing the SQL Query.
Note:  
Ensure that the database is already available in   Virtutem Analytics   account. If it does not exist create the database before executing this upload to avoid failure.

tablename

The Virtutem Analytics table name into which the data is to be uploaded after executing the SQL Query.
Ensure that the specified table with similar column structure is already created in the Virtutem Analytics Reporting Database.
In case the table does not exist, then you can set the Upload Tool to create the table and then upload the data into this table using the   ZOHO_CREATE_TABLE parameter available in the   common_params.conf   file. Refer to the   Configure Import Settings   section to know more about the ZOHO_CREATE_TABLE parameter.

importtype

Set one of the following as   Import Type .

·   APPEND   - appends the data to the end of the table.

·   UPDATEADD   - updates existing data records and appends new data records. For this you need to configure   ZOHO_MATCHING_COLUMNS   in common_params.conf in the conf directory.

·   TRUNCATEADD   - Deletes the existing data and adds new data.

matchingcols

This is applicable only when the   importtype   is set to   UPDATEADD.  
Specify column ( or a combination of columns) whose value will uniquely identify each record in that table. If the record already exists in the table then it will be replaced with the new values in the data being uploaded. Otherwise the data will be added at the end of the table.

selectedcols

The column names separated by comma. Only these columns are uploaded from the resultant query data into the online database.
Leave this as   ""   (empty) if you want all the columns to be uploaded.

skiptop

The number of rows to be skipped from the top in the resultant query data before being uploaded.  
Leave this as   ""   (empty) if you want all the rows from the resultant query data to be uploaded.

sql_query

Specify the SQL SELECT query to be executed in the local database for fetching the necessary data.  
Please note that the '<' symbol in the criteria should be replaced with   &lt;   and '>' symbol in the criteria should be replaced with   &gt;  
Example:   select * from employee where age &gt; 25
This query fetches all the record from the employee table whose age is greater than 25

 Configure Import Settings

The configuration file   common_params.conf   in   /conf   directory provides parameters that Upload Tool uses while uploading data. These import settings help Virtutem Analytics properly understand the data being imported.


The following table lists the import parameters that can be configured in the   common_params.conf   file for uploading data from your local database.


Note : You will not find all the below given parameters present in the configuration file (except mandatory ones). In case you do not find them, add them into the file and provide the required value as specified below.

Parameter

Possible Values

Description

ZOHO_CREATE_TABLE

·   True

·   False

Optional
This parameter specifies whether to create the table or throw error, if the specified table does not exist in the database.

·   True  - New table will be created in the specified database with the said name and then the data will be imported. If the table already exists in the database, then the data will be imported into it.

·   False  - The data will be imported into the specified table. If the table does not exist in the specified database then it throws an error.

Default value is false.

LINES_TO_SEND

 

Optional 
Upload Tool splits the data into batches for uploading. This parameter specifies the number of row for each batch.
Default value is  5000 .

ZOHO_THOUSAND_SEPARATOR

·   0

·   1

·   2

·   3

Optional
This parameter specifies the thousand separator for the numeric data.
The following is the list of parameter values and the corresponding thousand separator.

·   0 - COMMA

·   1 - DOT

·   2 - SPACE

·   3 - SINGLE QUOTE

Default is 0.

ZOHO_DECIMAL_SEPARATOR

·   0

·   1

Optional 
This parameter specifies the decimal separator for the numeric data.

The following is the list of parameter values and the corresponding decimal separator.

·   0 - DOT

·   1 - COMMA

Default value is  0.

SET_EMPTY_FORNULL

·   True

·   False

This parameter is used to specify whether empty value or null should be added in Virtutem Analytics table when the uploaded database contains null value. 

·   True  -The null values in the local database server will be added as empty values in Virtutem Analytics table.

·   False  - The null values in the local database server will be added as null in Virtutem Analytics table.


Default value is  false .

ZOHO_CANOVERRIDE

·   True

·   False

Optional 
In case another import is triggered when this import is in process you can use this parameter to specify whether the new import can override this import.

·   True  - The tool will stop the current import process and overwrite the table with new import.

·   False  - The tool will not disturb the current import process and will cancel the new import request.

Default value is  false.

ZOHO_ON_IMPORT_ERROR

·   ABORT

·   SKIPROW

·   SETCOLUMNEMPTY

This parameter controls the action to be taken in case there is an error during import.

·   ABORT  - In case of any error, the import will be aborted.

·   SKIPROW  - In case of any error, the specific row(s) in which error occurred will be skipped and then continue importing the rest of the data.

·   SETCOLUMNEMPTY - . In case of any error, the value of the specific cell(s) in which error occurred will be set to empty and then continue importing the rest of the data.

Default value is SETCOLUMNEMPTY

ON_ERROR

·   0

·   1

Optional 
This parameter is used to execute the next query, if the current query executed or upload is failed.

·   0 - Tool will abort if any error occur

·   1 - Next query will get executed and upload

Default value is  0

ZOHO_TRUNCATE_COLNAMES

·   TRUE

·   FALSE

Optional
In case the column names exceeds 100 character, then you can use this parameter to specify whether to truncate the column names to 100 character or to abort the upload.

·   TRUE - Tool will truncate the column names to 100 character

·   FALSE - Tool will abort

Default value is  FALSE

 Executing the Upload Tool

Once you have configured the parameters related to the   SQL SELECT Queries   and   Import setting , you can run the UploadFromDB.bat/UploadFromDB.sh   file with the required parameters as explained in the following sections to perform the upload. After successful execution, the data will get uploaded to the specified table in Virtutem Analytics <UploadTool_Home>/logs/error0.log   file .  

 Note:

    In case there is any failure in executing any of the given SQL Query, then the upload tool will abort the execution of that query and the ones that follow. Whatever queries that had been successfully executed and uploaded into Virtutem Analytics till then will not be rolled back.

Uploading Data Using AuthToken( recommended option to avoid exposing your password )

In case you do not want to expose your Virtutem Account password or you have registered in Virtutem Analytics using other login such as Google Apps, Gmail, Facebook & Yahoo and do not have a Virtutem user credentials, then you can use AuthToken to upload data.

UploadFromDB.bat <virtutem_login_email_address> -A <authtoken>

·   User Email Address (virtutem_login_email_address):

Your Virtutem Analytics account login E-mail ID.

·   AuthToken:

Authentication Token to authenticate access your Virtutem Analytics Account.

 Follow the steps given below to generate a AuthToken:

  1. Login to your Virtutem Analytics account.

  1. Navigate to the below URL in a new tab.   https://accounts.virtutem.com/apiauthtoken/create?SCOPE=VirtutemAnalytics/reportsapi

  1. AuthToken for your account will be generated, as given below (the value for the parameter AUTHTOKEN in the response given below, is the acutal AuthToken for your account.

#Wed Feb 29 03:07:33 PST 2012
AUTHTOKEN=bad18eba1ff45jk7858b8ae88a77fa30
RESULT=TRUE

 Note:
    Authenticating the user to upload data using AuthToken is supported only in the latest versions (released on June 17th 2013 or after). It is strongly recommended to use latest Upload Tool. To download the latest version of the Upload Tool, click here.

To know about all supported parameters and the value to be specified,   click here

UploadFromDB.bat <zoho_login_email_address> <zohopassword>

·   User Email Address        (virtutem_login_email_address):

Your Virtutem Analytics account login E-mail ID.

·   Virtutem Password (zoho_password):

Your Virtutem Analytics ccount password.

 Important Note:

  • If you have enabled Tow Factor Authentication(TFA) for your Virtutem Account, then the above method of specifying Email Address and Password will not work. You need to disable TFA to use your Virtutem password in Upload Tool.
  • If you are executing multiple instances/installation of Upload Tool to upload data, then it is recommended to authenticate using AuthToken.
  • It is strongly recommended to use AuthToken in the Upload Tool by specifying -A <authtoken> instead of specifying <password> as mentioned in the above section.

To know about all supported parameters and the value to be specified,   click here.

Uploading Data by Shared Users

In case the data is been uploaded by a shared user, then it is mandatory to specify the database owner (or database administrator) in the command line as given below.

UploadFromDB.bat <zoho_login_email_address> -A <authtoken> -D <database_owner_login_e-mail_address>"

·   User Email Address(zoho_login_email_address:

Shared users Virtutem Analytics Login E-mail ID.

·   AuthToken:

Shared users' AuthToke. To know how to generate an AuthToken,   refer here . You can also authenticate using Virtutem Analytics account password.

·   Database Owner Login E-mail ID (database_owner_login_e-mail_address):

Virtutem Analytics Login E-mail ID of the database owner (or database administrator).

To know about all supported parameters and the value to be specified,   click here .

Note on Specifying Authentication Details:
  • You can also specify the authentication details such as Virtutem Analytics Login email id, AuthToken and Database owner login e-mail address in the   common_params.conf   file (as values for the parameters USER_EMAIL_ADDRESS, AUTHTOKEN and DBOWNER_EMAIL_ADDRESS respectively). To know more on this, click here .
  • Specifying the details in the common_params.conf file reduces the tedious process of repeating these details every time in your command line arguments.
  • In case you specify these values in the command line apart from specifying in the common_params.conf file, then the values provided in the command line will take precedence.

The following table describes in detail the parameters to be given as arguments when running the UploadFromDB.bat/sh   file:

Parameter Name

Description

User Email Address(zoho_login_email_address)

Your Virtutem Analytics account login E-mail ID.

AuthToken

AuthToken is a permanent token to authenticate a user to access his/her Virtutem Analytics account. Click here to know how to generate AuthToken.

Password (zohoreports_password)

Password for accessing your Virtutem Analytics account.

Database Owner Email Address (database_owner_login_e-mail_address)

In case of uploading data in shared database, specify the database owner (or database administrator) login E-mail ID.

 Setting up Periodic Upload / Synchronization

Using Upload Tool you can periodically schedule uploading data from your local database. With this mechanism, you can have the latest data from your application synced into Virtutem Analytics , and the reports that you create over this data stay current.


You can setup a periodic schedule for both the CSV Console mode utility as well as the Database Upload utility using the   Operating System Scheduler   feature as explained below.

Setting up Schedule in Windows Operating System

  • Click   Start -> Settings -> Control Panel ->Scheduled Tasks.
  • Click   Add Scheduled Task.   The " Schedule Task " wizard will open.
  • Click   Action > Create Task   to open the   Create Task   dialog.
  • Open   Action   tab and then click   New .
  • Click   Browse   button and select the upload tool command line batch file   UploadFromDB.bat   in the Program/Script field. Ensure that you have provided the necessary settings in the Upload tool configuration files.
  • In the   Add Argument   field enter the following command line arguments.

UploadFromDB.bat <zoho_login_email_address> -A <authtoken>

Example

UploadFromDB.bat eduardo@zillum.com bad18eba1ff45jk7858b8ae88a77fa30

  • Specify the time of schedule in the   Start in   field.
  • Click   OK   to save the task.

Setting up Schedule in Linux or Mac Operating System:

  • In Linux, you can use the   crontab   command for scheduling the migration process using the command line script   UploadFromDB.sh   (Checkout   Simple Help on Linux Crontab command )

  • Ensure you have provided the necessary setting in the Upload tool configuration files.

Points to consider, while scheduling periodic upload using Command line mode:

  • It is the users responsibility to ensure that the latest data is uploaded into Virtutem Analytics by setting the schedule interval accordingly . Also ensure that you have provided the appropriate SQL Select query to pull the data.

  • In case you have   deleted few records   in the tables in your local database being uploaded, the only option to remove these records from Virtutem Analytics database is to set the   Import Type   as   TRUNCATEADD . This will delete all the records in the corresponding table in Virtutem Analytics and then add the data newly fetched records from the local database into Virtutem Analytics .

  • In case you have modified few records in the tables in your local database being uploaded, then to get this modified in Virtutem Analytics database set the   Import Type   as   UPDATEADD   and specify the matching columns. The Upload Tool will compare the records in the corresponding table in Virtutem Analytics with the data being uploaded from your local database table based on the matching columns. If the record already exists in the Virtutem Analytics table then it will be replaced with the new values available from the local database. If not, it will be added as new records in Virtutem Analytics .

 Viewing the Data Online

  • Login to   http://analytics.virtutem.com
  • Click on the corresponding Database name under My Databases.
  • Click on the corresponding table at the Left to open the table and view the uploaded data.

 FAQ and Troubleshooting Tips

Frequently Asked Questions

Troubleshooting Tips