Analytics » Upload Tool » Uploading Data from 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.
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.
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.
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. |
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. |
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. |
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
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. |
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. |
SQLite | sqlite | Content Column 2 | Specify the full path of the database from which you want to upload data in the SQLITEFILENAME parameter. |
Firebird | firebird | 3050 | Specify the full path of the database from which you want to upload data in the FIREBIRDFILENAME parameter. |
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 .
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.
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. | jdbc:teradata://<host> | 1025 |
Informix | informix | com.informix.jdbc. | jdbc:informix-sqli | 1533 |
HP Vertica | hpvertica | com.vertica.jdbc.Driver | jdbc:vertica:// | 5433 |
Ingres | ingres | com.ingres.jdbc. | jdbc:ingres:// | >21071 |
Greenplum | greenplum | org.postgresql.Driver | jdbc:postgresql:// | 5342 |
SQL Anywhere | sqlanywhere | com.sybase. | jdbc:sybase:Tds: | 2638 |
Derby | derby | org.apache.derby. | jdbc:derby:net:// | 1527 |
H2 | h2 | org.h2.Driver | jdbc:h2:tcp:// | 9092 |
Cache | cache | com.intersys.jdbc. | jdbc:Cache:// | 1972 |
Progress Openedge | progressopenedge | com.ddtek.jdbc. | jdbc:datadirect: | 9092 |
Cubrid | cubrid | cubrid.jdbc.driver. | jdbc:cubrid: | 33000 |
Mimer SQL | mimersql | com.mimer.jdbc.Driver | jdbc:mimer:[//[username | 1360 |
HSQLDB | hsqldb | org.hsqldb.jdbcDriver | jdbc:hsqldb | 9001 |
Mckoi | mckoi | com.mckoi.JDBCDriver | jdbc:mckoi: | 9157 |
FileMaker Pro | filemakerpro | com.filemaker. | jdbc:filemaker://<host>/<dbname> | 2399 |
The following are the steps to connect the database using JDBC Driver:
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.
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. |
tablename | The Virtutem Analytics table name into which the data is to be uploaded after executing the SQL Query. |
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. |
selectedcols | The column names separated by comma. Only these columns are uploaded from the resultant query data into the online database. |
skiptop | The number of rows to be skipped from the top in the resultant query data before being uploaded. |
sql_query | Specify the SQL SELECT query to be executed in the local database for fetching the necessary data. |
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 · 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 |
ZOHO_THOUSAND_SEPARATOR | · 0 · 1 · 2 · 3 | Optional · 0 - COMMA · 1 - DOT · 2 - SPACE · 3 - SINGLE QUOTE Default is 0. |
ZOHO_DECIMAL_SEPARATOR | · 0 · 1 | Optional 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. |
ZOHO_CANOVERRIDE | · True · False | Optional · 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 · 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 · TRUE - Tool will truncate the column names to 100 character · FALSE - Tool will abort Default value is FALSE |
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 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. |
#Wed Feb 29 03:07:33 PST 2012
AUTHTOKEN=bad18eba1ff45jk7858b8ae88a77fa30
RESULT=TRUE
Note: |
---|
|
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: |
---|
|
To know about all supported parameters and the value to be specified, click here.
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: |
---|
|
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. |
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.
UploadFromDB.bat <zoho_login_email_address> -A <authtoken>
Example
UploadFromDB.bat eduardo@zillum.com bad18eba1ff45jk7858b8ae88a77fa30